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 (третій аргумент).

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

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

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

Використання функцій INDEX і MATCH замість функції VLOOKUP

Використання функції VLOOKUP має певні обмеження: функція VLOOKUP може шукати лише значення зліва направо. Це означає, що стовпець зі значенням, яке ви шукали, завжди має розташовуватися ліворуч від стовпця, що містить повернуте значення. Тепер, якщо електронну таблицю не створено таким чином, не використовуйте функцію VLOOKUP. Натомість використовуйте комбінацію функцій INDEX і MATCH.

У цьому прикладі показано невеликий список, у якому значення, яке потрібно знайти в Чикаго, розташовано не в крайньому лівому стовпці. Отже, не можна використовувати функцію VLOOKUP. Замість цього ми знайдемо Харків у діапазоні B1:B11 за допомогою функції MATCH. Його можна знайти в рядку 4. Після цього функція INDEX використовує це значення як аргумент підстановки та знаходить у 4-му стовпці (стовпець D) сукупність для Чернігова. Використана формула відображається у клітинці A14.

Використання функцій INDEX і MATCH для пошуку значення

Додаткові приклади використання функцій INDEX і MATCH замість VLOOKUP див. в статті https://www.mrexcel.com/excel-tips/excel-vlookup-index-match/ Біллом Джеленом( Bill Jelen, Microsoft MVP).

Спробуйте

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

Приклад функції VLOOKUP на роботі

Скопіюйте наведені нижче дані в пусту електронну таблицю.

Порада.: Перш ніж вставляти дані в Програму 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 того самого рядка.

2,17

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

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

100

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

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

#N/A

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

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

#N/A

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

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

1,71

Приклад HLOOKUP

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

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

Осі

Опори

Гвинти

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)

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

5

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

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

11

=HLOOKUP(3;{1,2,3;"a","b","c";"d","e","f"};2;TRUE)

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

c

Приклади 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

функції підстановки та посилання

оновлювачі VLOOKUP

Використовуйте аргумент table_array у функції VLOOKUP

Потрібна додаткова довідка?

Потрібні додаткові параметри?

Ознайомтеся з перевагами передплати, перегляньте навчальні курси, дізнайтесь, як захистити свій пристрій тощо.

Спільноти допомагають ставити запитання й відповідати на них, надавати відгуки та дізнаватися думки висококваліфікованих експертів.