Намиране на най-близка стойност в Excel

Една от най-често срещаните задачи в Excel е търсенето на стойност в определена база данни и извличането на търсения резултат, отговарящ на желаните критерии.

Затова и едни от най-често използваните функции в Excel са функциите от категория Lookup&Reference, сред които най-използваните са: VLOOKUP, LOOKUP, INDEX и MATCH или комбинацията от тях. Чрез тях можем да търсим:

• точно съвпадение;
• най-близката, но винаги по-малка или равна стойност;
• най-близката, но винаги по-голяма или равна стойност на търсената.

В тази статия ще покажем как да намерите най-близката стойност до търсената от Вас, извън тези ограничения.

За целите на текущата демонстрация ще използваме като пример таблица с продажби. Целта е да извлечем името на служителя с най-близката до търсената от нас продажба, в клетка F2.

Функция „ABS“

Най-напред ще използваме функцията ABS, чрез която ще намерим абсолютната стойност на разликата между продажбите в базата данни и подадената от нас.

Тъй като маркираме цялата колона с продажби и търсим разликата на всяка една от тях спрямо търсената от нас в клетка F2, в паметта на Ексел се генерира масив от данни, представляващ всяка абсолютната стойност на тези разлики, т.е. има следния вид:

{350;245;1480;7450;1600;1900;320;4530;4500;2300;2520;6400…..}

Функция „MIN“

Така въведената от нас функция ще вградим в тялото на функцията MIN, за да намерим най-малката абсолютна стойност между тези разлики. Тъй като работим с области от клетки и Excel трябва да обходи всяка една от тях, ще превърнем функцията във функция за масив, като посредством клавишната комбинация CTR+SHIFT+ENTER добавим къдрави скоби.

Важно: Никога не въвеждайте къдравите скоби ръчно от клавиатурата! Ако влезете в режим на преглед на функцията тези къдрави скоби изчезват, за това не забравяйте да потвърждавате записа в клетката посредством клавишна комбинация CTR+SHIFT+ENTER.

Резултатът до момента трябва да има следния изглед:

Функция „MATCH“

За да намерим позицията като ред на тази абсолютна стойност, ще използваме функцията MATCH, в която ще вградим вече направеното до момента. Резултатът трябва да има следния изглед:

Работният прозорец на функцията MATCH има следния изглед:

Не забравяме да превърнем функцията във формула с масив с клавишната комбинация CTR+SHIFT+ENTER.

Функция „INDEX“

До тук намерихме, че най-близката стойност на търсената от нас продажба се намира на 13-та позиция като ред от нашата база данни. Остава да намерим и кой е служителят, който я е извършил, чрез функцията INDEX, чрез маркиране на колоната, от която искаме да извлечем резултат.

И отново не забравяме да добавим скобите за масив с вече познатата клавишна комбинация – CTR+SHIFT+ENTER.

Voila!

Крайният резултат на примера от демонстрацията можете да свалите от тук:

За повече информация относно обученията ни по Microsoft Excel – посетете тук