Пошук значень за допомогою функцій VLOOKUP, INDEX або MATCH

Увага! : Цю статтю перекладено за допомогою служби машинного перекладу; див. застереження. Версію цієї статті англійською мовою див. тут для отримання довідки.

Скажімо, у вас є список номерів кабінетів, і вам потрібно знати, хто працює в кожному кабінеті. Але аркуш просто велетенський, що ж робити? Скористатися функцією підстановки. Функції VLOOKUP і HLOOKUP – дві найкорисніші функції, так само, як INDEX і MATCH.

Примітка : Якщо ви намагаєтеся знайти майстер підстановок, цієї функції більше немає у програмі Excel.

Нижче наведено швидке нагадування про те, як використовувати функцію VLOOKUP.

=VLOOKUP(B2;C2:E7;3;TRUE)

Перший аргумент (фрагмент даних, який потрібен для роботи функції) – це значення, яке потрібно знайти. Це може бути посилання на клітинку або конкретне значення, наприклад «коваль» або 21 000. Другий аргумент – це діапазон клітинок, у якому, на вашу думку, міститься значення, яке потрібно знайти. У цьому прикладі це C2–C7. Третій аргумент – це стовпець у цьому діапазоні клітинок, у якому міститься потрібне вам значення.

Четвертий аргумент додатковий. Ви можете ввести аргумент TRUE або FALSE. Якщо ви введете аргумент TRUE або залишите аргумент пустим, функція поверне приблизний збіг зі значенням, указаним у першому аргументі. Якщо ви введете аргумент FALSE, функція поверне точний збіг зі значенням у першому аргументі. Інакше кажучи, якщо залишити четвертий аргумент пустим або ввести аргумент TRUE, ви отримаєте більше гнучкості.

У цьому прикладі показано, як працює функція. Коли ви вводите значення у клітинці B2 (перший аргумент), функція VLOOKUP шукає у клітинках C2–E7 (другий аргумент) і повертає найближчий приблизний збіг із третього стовпця діапазону, стовпця E (третій аргумент).

Типове використання функції VLOOKUP

Четвертий аргумент пустий, отже, функція повертає приблизний збіг. Якщо не повертає, потрібно ввести одне зі значень у стовпці C або D, щоб отримати хоч якийсь результат.

Якщо ви опанували функцію VLOOKUP, використання функції HLOOKUP не здаватиметься складним. У цю функцію потрібно вводити такі самі аргументи, але вона знаходить значення в рядках, а не у стовпцях.

Спробуйте

Якщо вам потрібно попрактикуватися з функціями підстановки, перш ніж використовувати їх для роботи зі своїми даними, нижче наведено зразки даних. Різні користувачі полюбляють різні функції. Спробуйте їх усі та дізнайтеся, що вам підходить найбільше.

Функція VLOOKUP у роботі

Скопіюйте всі клітинки в цій таблиці та вставте їх у клітинку A1 на пустому аркуші Excel.

Підказка.    Перш ніж вставляти дані у програму Excel, установіть для ширини стовпців A–C значення 250 пікселів і виберіть команду Перенести текст (вкладка Основне, група Вирівнювання).

Густина

В'язкість

Температура

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 того самого рядка.

=VLOOKUP(1,A2:C10,2)

'=VLOOKUP(1,A2:C10,3,ІСТИНА)

Використовуючи приблизний збіг, шукає значення 1 у стовпці A, знаходить найбільше значення, менше або рівне 1, у стовпці A (яке дорівнює 0,946), а потім повертає значення зі стовпця С того самого рядка.

=VLOOKUP(1;A2:C10;3;ІСТИНА)

'=VLOOKUP(0,7;A2:C10;3;ХИБНІСТЬ)

Використовуючи точний збіг, шукає значення 0,7 у стовпці A. Оскільки у стовпці А відсутній точний збіг, результат повертає помилку.

=VLOOKUP(0,7;A2:C10;3;ХИБНІСТЬ)

'=VLOOKUP(0.1,A2:C10,2,ІСТИНА)

Використовуючи приблизний збіг, шукає значення 0,1 у стовпці A. Оскільки 0,1 менше за найменше значення у стовпці А, результат повертає помилку.

=VLOOKUP(0.1,A2:C10,2,ІСТИНА)

'=VLOOKUP(2,A2:C10,2,ІСТИНА)

Використовуючи приблизний збіг, шукає значення 2 у стовпці A, знаходить найбільше значення, менше або рівне 2, у стовпці A (яке дорівнює 1,29), а потім повертає значення зі стовпця B того самого рядка.

=VLOOKUP(2,A2:C10,2,ІСТИНА)

Функція HLOOKUP у роботі

Скопіюйте всі клітинки в цій таблиці та вставте їх у клітинку A1 на пустому аркуші Excel.

Підказка.    Перш ніж вставляти дані у програму Excel, установіть для ширини стовпців A–C значення 250 пікселів і виберіть команду Перенести текст (вкладка Основне, група Вирівнювання).

Осі

Опори

Гвинти

4

4

9

5

7

10

6

8

11

Формула

Опис

Результат

'=HLOOKUP("Вали", A1:C4, 2, ІСТИНА)

Пошук слова «Вали» в 1 рядку та повернення значення із 2 рядка, розташованого в тому самому стовпці (стовпець A).

=HLOOKUP("Вали";A1:C4;2;ІСТИНА)

'=HLOOKUP("Опори", A1:C4, 3, ХИБНІСТЬ)

Пошук слова «Опори» в 1 рядку та повернення значення із 3 рядка, розташованого в тому самому стовпці (стовпець B).

=HLOOKUP("Опори";A1:C4;3;ХИБНІСТЬ)

'=HLOOKUP("B", A1:C4, 3, ІСТИНА)

Пошук літери «В» в 1 рядку та повернення значення із 3 рядка, розташованого в тому самому стовпці. Оскільки точний збіг для літери «В» не знайдено, використовується найбільше значення в рядку 1, яке менше за «В»: «Вали», у стовпці A.

=HLOOKUP("B",A1:C4,3,ІСТИНА)

'=HLOOKUP("Гвинти", A1:C4, 4)

Пошук слова «Гвинти» в 1 рядку та повернення значення з 4 рядка, розташованого в тому самому стовпці (стовпець С).

=HLOOKUP("Гвинти";A1:C4;4)

'=HLOOKUP(3, {1,2,3;"а","б","в";"г","д","е"}, 2, ІСТИНА)

Пошук числа 3 в константі масиву із трьох рядків і повернення значення із 2 рядка, розташованого в тому самому стовпці (у цьому випадку, третьому). Константа масиву містить три рядки значень, кожен із рядків відокремлено крапкою з комою (;). Оскільки символ «с» знайдено в рядку 2 та в тому самому стовпці, що й 3, результат повертає символ «c».

=HLOOKUP(3;{1,2,3;"а","б","в";"г","д","е"};2;ІСТИНА)

Функції INDEX і MATCH у дії

У цьому прикладі разом використано функції INDEX і MATCH для повернення найпершого номера рахунка та його дати для кожного із п'яти міст. Оскільки дату повернуто як число, використовуємо функцію TEXT, щоб форматувати його як дату. Функція INDEX як аргумент фактично використовує результат виконання функції MATCH. Поєднання функцій INDEX і MATCH у кожній формулі використовується двічі – перший раз для повернення номера рахунка, другий – для повернення дати.

Скопіюйте всі клітинки в цій таблиці та вставте їх у клітинку A1 на пустому аркуші Excel.

Підказка.    Перш ніж вставляти дані у програму Excel, установіть для ширини стовпців A–D значення 250 пікселів і виберіть команду Перенести текст (вкладка Основне, група Вирівнювання).

Рахунок

Місто

Дата виставлення рахунка

Перший рахунок за містом із датою

3115

Чернігів

7/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")

3137

Чернігів

9/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")

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

Алушта

6/5/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

Додаткові відомості про функції підстановки

На початок сторінки

Примітка : Застереження про машинний переклад: Цю статтю перекладено комп’ютерною системою без втручання людини. Корпорація Майкрософт пропонує таку послугу, щоб іншомовні користувачі могли дізнаватися про продукти, служби й технології Microsoft. Оскільки статтю перекладено за допомогою служби машинного перекладу, вона може містити смислові, синтаксичні або граматичні помилки.

Отримуйте нові функції раніше за інших
Приєднайтеся до оцінювачів Office

Ця інформація корисна?

Дякуємо за ваш відгук!

Дякуємо за відгук! Схоже, вам може стати в нагоді допомога одного з наших спеціалістів служби підтримки Office, з яким ми вас можемо з’єднати.

×