Порада.: Спробуйте скористатися новими функціями 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 (третій аргумент).
Четвертий аргумент пустий, тому функція повертає приблизний збіг. Якщо не повертає, потрібно ввести одне зі значень у стовпці C або D, щоб отримати хоч якийсь результат.
Якщо ви вмієте працювати з функцією VLOOKUP, функція HLOOKUP не менш проста у використанні. Ви вводите ті самі аргументи, але пошук виконується в рядках, а не в стовпцях.
Використання функцій INDEX і MATCH замість функції VLOOKUP
Використання функції VLOOKUP має певні обмеження: функція VLOOKUP може шукати лише значення зліва направо. Це означає, що стовпець зі значенням, яке ви шукали, завжди має розташовуватися ліворуч від стовпця, що містить повернуте значення. Тепер, якщо електронну таблицю не створено таким чином, не використовуйте функцію VLOOKUP. Натомість використовуйте комбінацію функцій INDEX і MATCH.
У цьому прикладі показано невеликий список, у якому значення, яке потрібно знайти в Чикаго, розташовано не в крайньому лівому стовпці. Отже, не можна використовувати функцію VLOOKUP. Замість цього ми знайдемо Харків у діапазоні B1:B11 за допомогою функції MATCH. Його можна знайти в рядку 4. Після цього функція INDEX використовує це значення як аргумент підстановки та знаходить у 4-му стовпці (стовпець D) сукупність для Чернігова. Використана формула відображається у клітинці A14.
Додаткові приклади використання функцій 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 |
