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

Posted by
/ / Leave a comment

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

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

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

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

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

 

Таблица А*
Оборот мин.Оборот макс.Коефициент продуктивност
20 000.0030 000.00100
30 000.0040 000.00120
40 000.0050 000.00140
50 000.0060 000.00160
60 000.0070 000.00160
70 000.0080 000.00180
90 000.00100 000.00200
100 000.00120 000.00220
120 000.00140 000.00230
140 000.00160 000.00240
160 000.00180 000.00260

 

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

 

Таблица Б*
МагазинОборотТърсена продуктивност от таблица А
145 600.00
235 750.00
324 564.00
455 780.00
574 320.00
698 045.00
7125 999.00
894 036.00
9145 000.00
10167 234.00
1156 780.00

 

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

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

 

excel-if

Фиг. 1 (кликнете, за да увеличите)

 

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

VLOOKUP

Фиг. 2 (кликнете, за да увеличите)

 

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

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

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

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

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

VLOOKUP

Фиг. 3

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

 

Фиг. 4 (кликнете, за да увеличите)

Фиг. 4 (кликнете, за да увеличите)

 

При „Приблизителното търсене“, функцията 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.

Още публикации по темата: