Съвет
Опитайте да използвате новите функции 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.
Опитайте
Ако искате да експериментирате с функциите за търсене, преди да ги изпробвате върху своите данни, можете да използвате примерните данни по-долу.
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
Копирайте всички клетки в тази таблица и ги поставете в клетка 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 |
Вж. също
Карта за бързи справки: припомняне на VLOOKUP
Функции за търсене и препратки (справка)