VLOOKUP vs IF в Excel или какво да правим, когато имаме ограничение в броя на вложените IF функции?

Може би не всички знаят, че може да използваме функцията VLOOKUP вместо няколко вложени функции IF в Microsoft Excel.

Предимството й е, че:

• Дължината на формулата е малка;
• Може да надвиши максималния брой вложени IF функции (от Excel 2007, техния брой е 64, но вероятността за грешка нараства с броя вложени IF функции).

Преди време имах следния казус:

Дадена ни е таблица с определени минимални и максимални обороти в магазин. В зависимост от оборота имаме коефициент на продуктивност – Таблица А

 

Таблица А*
Оборот мин.	Оборот макс.	Коефициент продуктивност
20 000.00	30 000.00	100
30 000.00	40 000.00	120
40 000.00	50 000.00	140
50 000.00	60 000.00	160
60 000.00	70 000.00	160
70 000.00	80 000.00	180
90 000.00	100 000.00	200
100 000.00	120 000.00	220
120 000.00	140 000.00	230
140 000.00	160 000.00	240
160 000.00	180 000.00	260

 

В друга таблица имаме информация за наличния оборот в различни магазини – Таблица Б.

 

Таблица Б*
Магазин	Оборот	Търсена продуктивност от таблица А
1	45 600.00
2	35 750.00
3	24 564.00
4	55 780.00
5	74 320.00
6	98 045.00
7	125 999.00
8	94 036.00
9	145 000.00
10	167 234.00
11	56 780.00

 

Задачата ни е да определим съответния коефициент на продуктивност. Така например, магазин номер 1 има коефициент на продуктивност 140, защото оборота му е между 40 000 и 50 000, магазин номер 2 – има коефициент 100 и т.н.

Един от начините е да използваме вложена функция IF.

 

 

Както се вижда от Фигура 1, формулата е прекалено дълга и вероятността за грешка е голяма.
Вместо това може да използваме далеч, по-кратка и също толкова вярна функция VLOOKUP:

 

Ето как работи:

1. Таблица А* ще наречем база с данни. Базата данни, трябва да е създадена със следните колони – минимален оборот и коефициент на продуктивност.
Реално, колоната с максимален оборот не е нужна, но ако обърнете внимание, всяка нова стъпка на минимален оборот е равна на максималния оборот от предходния коефициент за продуктивност. Така например минималния оборот за коефициент 120 е равен на максималния на коефициент 100. Минималния на 140 е равен на максималния на 120. и т.н.

a. В базата данни (Таблица А*) минималните обороти трябва да са сортирани по възходящ ред. Това е задължително условие при използване на VLOOKUP с „приблизително търсене“, за което ще стане дума малко по-късно.
b. Колоната с минималния оборот трябва да се намира от ляво на колоната с коефициентите.

2. Таблица Б* е таблицата, в която трябва да въведем съответните коефициенти. За нея нямаме предварителни изисквания.

3. Започваме въвеждане на функцията в клетка G3: =VLOOKUP(. Когато отворя прозореца на функцията, използвайки бутон f_x или Formulas->Insert Function, следва да въведем аргументите й:

a. В полето Lookup_Value: избираме клетката с оборота от първия магазин на Таблица Б*
b. В полето Table_array: маркираме колони от А до С. Тъй като търсим оборот от Таблица Б*, който да отговаря на минимален оборот от Таблица А*
c. В полето Col_index_num: въвеждаме числото 3 – това е третата маркирана колона и точно от нея искаме резултат.
d. В полето Range_lookup: въвеждаме 1 или TRUE (равносилни са и означава „приблизително търсене“).

 

 

При „Приблизителното търсене“, функцията VLOOKUP, обхожда всяка от клетките от първата маркирана колона от базата данни (в нашия случай това е колона А). При това обхождане сравнява всяка от стойностите, докато не намери максимално близка, но ненадвишаваща я.

Например: Първата търсена стойност е 45 600 (клетка F3). Казали сме на VLOOKUP да търси тази стойност в колона А. Excel проверява клетка А1, вижда, че е празна и слиза да провери клетка А2. Тази клетка е текстова, а ние търсим числова, така че слиза да провери клетка А3. Клетка А3 = 20 000, а стойността, която търсим е 45 600.

45 600 е по-висока стойност от 20 000 и слиза да провери клетка А4 = 30 000, аналогично А5 = 40 000 и стига до клетка А6 = 50 000, чиято стойност вече надвишава търсената от нас 45 600. Тук е моментът, в който се връща една стъпка назад и казва, че най-близката ненадвишаваща стойност се намира в клетка А5 = 40 000. Клетка А5 се намира на ред 5, а в третото поле на функцията (col_index_num) сме посочили, че искаме резултат от третата маркирана колона. Резултатът е стойността от клетката на ред 5 и третата маркирана колона – C5 = 140.

Внимание: При „приблизително търсене“ е задължително колоната в която търсим (колона А) да е сортирана по възходящ ред (от А към Z)!

Повече за подобни тънкости в работата с Microsoft Excel, както и за други продукти на Microsoft Office, може да научите в курсовете на ITraining.