Съвет: Опитайте да използвате новите функции XLOOKUP и XMATCH , подобрени версии на функциите, описани в тази статия. Тези нови функции работят в произволна посока и връщат точни съвпадения по подразбиране, което ги прави по-лесни и по-удобни за използване от предшествениците им.
Да предположим, че имате списък с номерата на местоположенията на офисите и трябва да знаете кои са служителите във всеки един офис. Електронната таблица е огромна, така че може би смятате, че това е трудна задача. Всъщност е доста лесно да се направи с функция за търсене.
Функциите VLOOKUP и HLOOKUP, заедно с INDEX и MATCH, са някои от най-полезните функции в Excel.
Забележка: Функцията "Съветник за справки" вече не е налична в Excel.
Ето пример как да използвате VLOOKUP.
=VLOOKUP(B2;C2:E7;3;TRUE)
В този пример B2 е първият аргумент – елемент от данните, необходими на функцията, за да работи. За VLOOKUP този първи аргумент е стойността, която искате да намерите. Този аргумент може да бъде препратка към клетка или фиксирана стойност, например "тодоров" или 21 000. Вторият аргумент е диапазонът от клетки C2-:E7, в който се търси стойността, която искате да намерите. Третият аргумент е колоната в този диапазон от клетки, съдържаща стойността, която търсите.
Четвъртият аргумент е по желание. Въведете ИЛИ TRUE, или FALSE. Ако въведете TRUE или оставите аргумента празен, функцията връща приблизително съвпадение на стойността, която задавате в първия аргумент. Ако въведете FALSE, функцията ще съвпада със стойността, предоставена от първия аргумент. С други думи, оставянето на четвъртия аргумент празен или въвеждането на TRUE ви дава по-голяма гъвкавост.
Този пример ви показва как работи функцията. Когато въведете стойност в клетка B2 (първи аргумент), VLOOKUP претърсва клетките в диапазона C2:E7 (2-ри аргумент) и връща най-близкото приблизително съвпадение от третата колона в диапазона – колона E (3-ти аргумент).
Четвъртият аргумент е празен, така че функцията връща приблизително съвпадение. Ако няма, трябва да въведете една от стойностите в колоните C или D, за да получите някакъв резултат.
Когато знаете достатъчно за VLOOKUP, функцията HLOOKUP е също толкова лесна за използване. Въвеждате същите аргументи, но то търси в редове вместо в колони.
Използване на INDEX и MATCH вместо VLOOKUP
Има някои ограничения при използването на VLOOKUP – функцията VLOOKUP може да търси стойност само от ляво надясно. Това означава, че колоната, съдържаща стойността, по която търсите, винаги трябва да е разположена отляво на колоната, съдържаща върнатата стойност. Сега, ако вашата електронна таблица не е създадена по този начин, не използвайте VLOOKUP. Вместо това използвайте комбинацията от функциите INDEX и MATCH.
Този пример показва малък списък, в който стойността, която искаме да намерим – Чикаго – не се съдържа в най-лявата колона. Така че не можем да използваме VLOOKUP. Вместо това ще използваме функцията MATCH, за да намерим Чикаго в диапазона B1:B11. Стойността се намира в ред 4. След това функцията INDEX ще използва тази стойност като аргумент за търсене и ще намери населението на Чикаго в 4-тата колона (колона D). Използваната формула е показана в клетка A14.
За повече примери за използване на INDEX и MATCH вместо VLOOKUP вж. статията , https://www.mrexcel.com/excel-tips/excel-vlookup-index-match/ от Бил Желен, MVP на Microsoft.
Опитайте
Ако искате да експериментирате с функциите за търсене, преди да ги изпробвате със собствените си данни, ето някои примерни данни.
VLOOKUP Example at work
Копирайте следните данни в празна електронна таблица.
Съвет: Преди да поставите данните в Excel, задайте ширина от 250 пиксела на колоните от A до C и след това щракнете върху Текст на повече редове (раздел Начало, група Подравняване).
|
Плътност |
Вискозитет |
Температура |
|
0,457 |
3,55 |
500 |
|
0,525 |
3,25 |
400 |
|
0,606 |
2,93 |
300 |
|
0,675 |
2,75 |
250 |
|
0,746 |
2,57 |
200 |
|
0,835 |
2,38 |
150 |
|
0,946 |
2,17 |
100 |
|
1,09 |
1,95 |
50 |
|
1,29 |
1,71 |
0 |
|
Формула |
Описание |
Резултат |
|
=VLOOKUP(1;A2:C10;2) |
Използвайки приблизително съвпадение, търси стойност 1 в колона A, намира най-голямата стойност, по-малка или равна на 1, в колона A, която е 0,946, и връща стойността от колона B в същия ред. |
2,17 |
|
=VLOOKUP(1;A2:C10;3;TRUE) |
Използвайки приблизително съвпадение, търси за стойност 1 в колона A, намира най-голямата стойност, по-малка или равна на 1, в колона A, която е 0,946, и връща стойността от колона C в същия ред. |
100 |
|
=VLOOKUP(0,7;A2:C10;3;FALSE) |
Използвайки точно съвпадение, търси стойността 0,7 в колона A. Тъй като там няма точно съвпадение, връща се грешка. |
#N/A |
|
=VLOOKUP(0,1;A2:C10;2;TRUE) |
Използвайки приблизително съвпадение, търси стойността 0,1 в колона A. Тъй като стойността е по-малка от най-малката в колона А, се връща грешка. |
#N/A |
|
=VLOOKUP(2;A2:C10;2;TRUE) |
Използвайки приблизително съвпадение, търси стойността 2 в колона A, намира най-голямата стойност, по-малка или равна на 2, в колона A, която е 1,29, и връща стойността от колона B в същия ред. |
1,71 |
HLOOKUP Example
Копирайте всички клетки в тази таблица и ги поставете в клетка A1 на празен работен лист в Excel.
Съвет: Преди да поставите данните в Excel, задайте ширина от 250 пиксела на колоните от A до C и след това щракнете върху Текст на повече редове (раздел Начало, група Подравняване).
|
Мостове |
Лагери |
Болтове |
|
4 |
4 |
9 |
|
5 |
7 |
10 |
|
6 |
8 |
11 |
|
Формула |
Описание |
Резултат |
|
=HLOOKUP("Мостове"; A1:C4; 2; TRUE) |
Търси "Мостове" в ред 1 и връща стойността от ред 2, която е в същата колона (колона A). |
4 |
|
=HLOOKUP("Лагери"; A1:C4; 3; FALSE) |
Търси "Лагери" в ред 1 и връща стойността от ред 3, която е в същата колона (колона B). |
7 |
|
=HLOOKUP("B"; A1:C4; 3; TRUE) |
Търси "B" в ред 1 и връща стойността от ред 3, която е в същата колона. Тъй като не е намерено точно съвпадение за "B", използва се най-голямата стойност в ред 1, която е по-малка от "B": "Мостове", в колона A. |
5 |
|
=HLOOKUP("Болтове"; A1:C4; 4) |
Търси "Болтове" в ред 1 и връща стойността от ред 4, която е в същата колона (колона C). |
11 |
|
=HLOOKUP(3; {1\2\3;"a"\"b"\"c";"d"\"e"\"f"}; 2; TRUE) |
Търси числото 3 в масив от константи с три реда и връща стойността от ред 2 в същата (в този случай третата) колона. В масива от константи има три реда със стойности, като всеки ред се разделя с точка и запетая (;). Тъй като "c" е намерено в ред 2 и в същата колона като 3, връща се "c". |
c |
Примери за INDEX и MATCH
Този последен пример използва заедно функциите INDEX и MATCH, за да върне номера на най-ранната фактура и съответната дата за всеки от петте града. Тъй като датата се връща като число, използваме функцията TEXT, за да я форматираме като дата. Функцията INDEX всъщност използва резултата от функцията MATCH като свой аргумент. Комбинацията от функциите INDEX и MATCH се използва два пъти във всяка формула – веднъж за връщане на номера на фактурата, а след това за връщане на датата.
Копирайте всички клетки в тази таблица и ги поставете в клетка A1 на празен работен лист в Excel.
Съвет: Преди да поставите данните в Excel, задайте ширина от 250 пиксела на колоните от A до D и след това щракнете върху Текст на повече редове (раздел Начало, група Подравняване).
|
Фактура |
Град |
Дата на фактурата: |
Най-стара фактура по градове, с дата |
|
3115 |
Атланта |
4/7/12 |
="Атланта = "&INDEX($A$2:$C$33;MATCH("Атланта";$B$2:$B$33;0);1)& "; Дата на фактурата: " & TEXT(INDEX($A$2:$C$33;MATCH("Атланта";$B$2:$B$33;0);3);"m/d/yy") |
|
3137 |
Атланта |
4/9/12 |
="Остин = "&INDEX($A$2:$C$33;MATCH("Остин";$B$2:$B$33;0);1)& "; Дата на фактурата: " & TEXT(INDEX($A$2:$C$33;MATCH("Остин";$B$2:$B$33;0);3);"m/d/yy") |
|
3154 |
Атланта |
11.4.12 |
="Далас = "&INDEX($A$2:$C$33;MATCH("Далас";$B$2:$B$33;0);1)& "; Дата на фактурата: " & TEXT(INDEX($A$2:$C$33;MATCH("Далас";$B$2:$B$33;0);3);"m/d/yy") |
|
3191 |
Атланта |
21.4.12 |
="Ню Орлиънс = "&INDEX($A$2:$C$33;MATCH("Ню Орлиънс";$B$2:$B$33;0);1)& "; Дата на фактурата: " & TEXT(INDEX($A$2:$C$33;MATCH("Ню Орлиънс";$B$2:$B$33;0);3);"m/d/yy") |
|
3293 |
Атланта |
25.4.12 |
="Кричим = "&INDEX($A$2:$C$33;MATCH("Кричим";$B$2:$B$33;0);1)& "; Дата на фактурата: " & TEXT(INDEX($A$2:$C$33;MATCH("Кричим";$B$2:$B$33;0);3);"m/d/yy") |
|
3331 |
Атланта |
27.4.12 |
|
|
3350 |
Атланта |
28.4.12 |
|
|
3390 |
Атланта |
1.5.12 |
|
|
3441 |
Атланта |
2.5.12 |
|
|
3517 |
Атланта |
8.5.12 |
|
|
3124 |
Остин |
9.4.12 |
|
|
3155 |
Остин |
11.4.12 |
|
|
3177 |
Остин |
19.4.12 |
|
|
3357 |
Остин |
28.4.12 |
|
|
3492 |
Остин |
5.6.12 |
|
|
3316 |
Далас |
25.4.12 |
|
|
3346 |
Далас |
28.4.12 |
|
|
3372 |
Далас |
1.5.12 |
|
|
3414 |
Далас |
1.5.12 |
|
|
3451 |
Далас |
2.5.12 |
|
|
3467 |
Далас |
2.5.12 |
|
|
3474 |
Далас |
4.5.12 |
|
|
3490 |
Далас |
5.5.12 |
|
|
3503 |
Далас |
8.5.12 |
|
|
3151 |
Ню Орлиънс |
9.4.12 |
|
|
3438 |
Ню Орлиънс |
2.5.12 |
|
|
3471 |
Ню Орлиънс |
4.5.12 |
|
|
3160 |
Кричим |
18.4.12 |
|
|
3328 |
Кричим |
26.4.12 |
|
|
3368 |
Кричим |
29.4.12 |
|
|
3420 |
Кричим |
1.5.12 |
|
|
3501 |
Кричим |
6.5.12 |
