Уроци на фотошоп, GIMP, MS Office, Scratch, HTML&CSS: Функциите SEARCH, RANK, IFERROR, VLOOKUP в Excel Function

Search Box/Поле за търсене

Този пример ви учи как да създадете свое собствено поле за търсене в Excel. Ако бързате, просто изтеглете файла в Excel.

Ето как изглежда електронната таблица. Ако въведете заявка за търсене в клетка B2, Excel търси в колона Е и резултатите се показват в колона B.

За да създадете това поле за търсене, изпълнете следните стъпки.
1. Изберете клетка D4 и вмъкнете функцията SEARCH, изпишете: =SEARCH($B$2,E4). Със знака за $ създавате абсолютна референция към клетка В2.
2. Кликнете два пъти върху десния ъгъл на клетка D4, за да копирате бързо функцията в другите клетки.
Резултатът е това:

Обяснение: функцията SEARCH намира позицията на поредицата букви или низ 'uni' в колоната Е. Функцията SEARCH е нечувствителна към малки и големи букви. За Tunisia, низът "uni" се намира на позиция 2. За United States, низът "uni" се намира на позиция 1. Колкото по-напред стои низът в търсената дума, толкова по-напред ще излезе в списъка с резултата от търсенето.
3. Както United States, така и United Kingdom връщат низа на позиция 1. За да върнете уникални стойности, които ще ни помогнат, когато използваме функцията RANK, леко променете формулата в клетка D4, както е показано по-долу. ИЛИ: =IFERROR(SEARCH($B$2,E4)+ROW()/100000,"")
4. Отново кликнете двукратно върху десния ъгъл на клетка D4, за да копирате бързо формулата до другите клетки.

ИМАТЕ:

Обяснение: Функцията ROW връща номера на реда на клетката. Ако разделяме номера на реда с голямо число(напр. 100000) и го добавим към резултата от функцията SEARCH, винаги имаме уникални стойности. Тези малки нараствания обаче няма да повлияят на класацията за търсене. Съединените щати имат стойност от 1,00006, а Великобритания има стойност 1,00009. Също така добавихме функцията IFERROR. Ако клетката съдържа грешка (не може да бъде намерен низ), да се показва празен низ ("").
5. Изберете клетка C4 и поставете функцията RANK , показана тук, като я вмъкнете като първи аргумент на функцията IFERROR, a като втори аргумент поставете "": за по-лесно копирайте следното в реда за функция f_x: =IFERROR(RANK(D4,$D$4:$D$197,1),"")

6. Кликнете два пъти върху десния ъгъл на клетка С4, за да копирате формулата бързо в другите клетки.
Обяснение: функцията RANK връща ранга на число в списък с числа. Както е тук RANK(D4,$D$4:$D$197,1) ако третият аргумент е 1, Excel връща първо най-малкото число, след това второто най-малко число и т.н. Тъй като добавихме функцията ROW, всички стойности в колоната са уникални. В резултат редиците в колона C също са уникални.
7. Почти сме готови. Ще използваме функцията VLOOKUP за връщане на страните, в които извършваме търсенето (първо застава държавата с най-нисък ранг (в случая с 1), на второ място 2 и т.н.). Сега изберете клетка B4 и въведете функцията VLOOKUP, показана по-долу:
=IFERROR(VLOOKUP(A4,$C$4:$E$197,3,FALSE),"")