Специални формули за търсене

В допълнение към основните формули за търсене на данни, Excel съдържа няколко допълнителни формули, които ви позволяват да извършвате специализирано търсене на данни. Например, използвайки тези формули, можете да търсите точни стойности, стойности в колона в близост до първата колона на таблица, да извършвате търсене на регистър, да връщате стойност от множество справочни таблици и да извършвате други сложни търсения.

Намиране на точната стойност

Функции ВОЛОКУП и GPR не изискват точно съвпадение между справочната стойност и стойността в справочната таблица. Това понякога е приемливо, например, когато се търси данъчна ставка. В други ситуации може да се наложи да съответствате напълно на стойностите. Например, когато търсите номера на персонала на служител, трябва напълно да съответствате на номера.

За да намерите само точно съвпадащата стойност, използвайте същите функции ВОЛОКУП или GPR, но с незадължителен четвърти аргумент със стойността НЕВЯРНО.

Фигурата показва работен лист с таблица, която съдържа номера на персонала (колона C) и собственото и фамилното име на служителите (колона D). Справочната таблица е наречена Списък на служителите. Следващата формула, въведена в клетка B2, търси номера на персонала на служителя, посочен в клетка B1, и връща името на служителя, който има този номер на персонала.

От последния аргумент на функцията ВОЛОКУП има значението НЕВЯРНО, функцията се връща само ако е намерено точно съвпадение. В противен случай, ако стойността не бъде намерена, формулата връща грешка # N/A. Разбира се, търсенето на приблизително съвпадение на персонала на служителя в този случай няма никакъв смисъл. Забележете също така, че броят на персонала на служителите в колона В не е във възходящ ред, защото когато се използва булевото число като четвърти аргумент НЕВЯРНО не се изисква възходящо нареждане.

Ако искате вместо грешка # N/A друга информация се показва на екрана (в случай, че номерът на персонала на служителя не е намерен), използвайте функцията IFERROR. Синтаксисът му е както следва:

• стойността е произволен израз; обикновено формула, която има потенциал да върне грешка;
• value_if_error е всеки израз, съдържащ числа, низове, препратки към клетки или формули.

Следва формула вместо грешка # N/A показва текст не е намерен:

Намиране на стойността вляво

Функция ВОЛОКУП винаги търси стойност в първата колона на посочения диапазон. Но какво, ако трябва да намерите стойността не в първата, а в друга колона? Трябва да се отбележи, че третият аргумент на функцията ВОЛОКУП отрицателна стойност не може да бъде присвоена.

Нека разгледаме този проблем с прост пример. Да предположим, че искате да намерите средното темпо (колона Б, диапазон Средното) на един от играчите от колона С (диапазон Играч). Името на плейъра, който искате да намерите, се въвежда в клетката Wanted_player. В този случай функцията ВОЛОКУП няма да работи, тъй като данните са поставени неправилно.

Разбира се, данните са лесни за пренареждане, но понякога и това не е разрешено.

Един от начините за решаване на този проблем е използването на функцията Преглед, което приема два аргумента като аргументи. Следващата формула (в клетка F3) връща средното темпо на играча (от колона Б), чието име е въведено в клетката Wanted_player:

За да използвате функцията Преглед, диапазон на търсене (в този случай диапазон Играч) трябва да бъдат представени във възходящ ред. В допълнение към това ограничение, формулата има и друг малък проблем: при въвеждане на името на несъществуващ играч (с други думи, ако клетката Wanted_player съдържа стойност, която не е в диапазона Играч), формулата връща стойността на грешката.

По-ефективен начин е споделянето на функции ИНДЕКС и ТЪРСЕНЕ. Следващата формула работи точно по същия начин като предишната, с изключение на това, че връща грешка # N/A, ако търсеният плейър не е намерен. Друго предимство на тази формула е, че имената на играчите не трябва да бъдат сортирани.

Търсене с изключение на регистъра

Помислете за проблема с намирането на текст в таблица с данни. Основни функции за намиране на данни в Excel (Преглед, ВОЛОКУП и GPR) не са чувствителни към малки и големи букви. Следователно, в резултат на използването на тази функция за търсене на текст в таблицата бюджет формулата ще върне всеки низ, който съответства на този текст, например: БЮДЖЕТ, Бюджет или Бюджет.

Работният лист, показан на фигурата, съдържа два диапазона: първият включва клетки D2: D7 (с име Обхват01), втората - клетки E2: E7 (с името Обхват 02). Текстът, който искате да намерите, се съдържа в клетка B1 (с име Word).

Следващата формула на масив, въведена в клетка B2, намира посочената стойност в диапазона Обхват01, регистър на буквите и връща съответната стойност от диапазона Обхват 02.

Тази формула намира думата КУЧЕ (всички главни букви) и връща съответната стойност 300. На свой ред, стандартната формула за търсене, използваща функцията Преглед, връща 400.

Забележка

Не забравяйте да използвате клавишите, когато въвеждате формула на масив .

Намиране на стойност в множество таблици

Работният лист може да съдържа произволен брой таблици за търсене на данни. В такива случаи е необходимо да въведете информация във формата, в която таблица ще се извършва търсенето. Фигурата показва пример за използване на множество таблици на един работен лист.

Работната книга, показана в примера, съдържа два диапазона: диапазона G3: H9 (име Таблица01) и обхват J3: K8 (име Таблица 02) и изчислява комисионната на мениджърите на фирмата. Размерът на комисионната на всеки отделен мениджър зависи от два фактора: стажът на мениджъра в дадена фирма (колона Б) и обема на продадените от него стоки (колона В). Колона D съдържа формули за намиране на размера на комисионната в съответната таблица. Например клетка D2 съдържа следната формула: