Applies ToExcel за Microsoft 365 Excel за уеб Excel 2024 Excel 2021 Excel 2019 Excel 2016

Съвет: Опитайте да използвате новите функции 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-ти аргумент).

Типично използване на функцията VLOOKUP

Четвъртият аргумент е празен, така че функцията връща приблизително съвпадение. Ако няма, трябва да въведете една от стойностите в колоните C или D, за да получите някакъв резултат.

Когато знаете достатъчно за VLOOKUP, функцията HLOOKUP е също толкова лесна за използване. Въвеждате същите аргументи, но то търси в редове вместо в колони.

Използване на INDEX и MATCH вместо VLOOKUP

Има някои ограничения при използването на VLOOKUP – функцията VLOOKUP може да търси стойност само от ляво надясно. Това означава, че колоната, съдържаща стойността, по която търсите, винаги трябва да е разположена отляво на колоната, съдържаща върнатата стойност. Сега, ако вашата електронна таблица не е създадена по този начин, не използвайте VLOOKUP. Вместо това използвайте комбинацията от функциите INDEX и MATCH.

Този пример показва малък списък, в който стойността, която искаме да намерим – Чикаго – не се съдържа в най-лявата колона. Така че не можем да използваме VLOOKUP. Вместо това ще използваме функцията MATCH, за да намерим Чикаго в диапазона B1:B11. Стойността се намира в ред 4. След това функцията INDEX ще използва тази стойност като аргумент за търсене и ще намери населението на Чикаго в 4-тата колона (колона D). Използваната формула е показана в клетка A14.

Използване на INDEX и MATCH за търсене на стойност

За повече примери за използване на 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

Карта за бързи справки:

търсене и препратки VLOOKUP (справка)

Използване на аргумента table_array във функция VLOOKUP

Нуждаете ли се от още помощ?

Искате ли още опции?

Разгледайте ползите от абонамента, прегледайте курсовете за обучение, научете как да защитите устройството си и още.

Общностите ви помагат да задавате и отговаряте на въпроси, да давате обратна връзка и да получавате информация от експерти с богати знания.