Припустімо, що потрібно знайти розширення телефону співробітника за номером його емблеми або правильною ставкою комісійної винагороди за обсяг продажів. Пошук даних дає змогу швидко й ефективно знайти певні дані в списку та автоматично перевірити правильність даних. Після пошуку даних можна виконати обчислення або відобразити результати з повернутими значеннями. Існує кілька способів пошуку значень у списку даних і відображення результатів.
У цій статті
- Вертикальний пошук значень у списку за допомогою точного збігу
- Вертикальний пошук значень у списку з використанням приблизного збігу
- Пошук значень по вертикалі в списку з невідомим розміром за допомогою точного збігу
- Пошук значень по горизонталі в списку з використанням точного збігу
- Пошук значень по горизонталі в списку за допомогою приблизного збігу
Вертикальний пошук значень у списку за допомогою точного збігу
Для цього завдання можна скористатися функцією VLOOKUP або поєднанням функцій INDEX і MATCH.
Приклади функції VLOOKUP
Додаткові відомості див. у статті Функція VLOOKUP.
Приклади функцій INDEX і MATCH
Простою українською мовою це означає:
=INDEX(я хочу повернути значення із C2:C10;яке ЗБІГАЄТЬСЯ_ЗІ_ЗНАЧЕННЯМ("Капуста"; що розташоване десь у масиві B2:B10; де повернуте значення – перше значення, яке збігається з "Капуста"))
Формула шукає перше значення в діапазоні C2:C10, яке відповідає " Капуста " (у B7) і повертає значення в клітинці C7 (100), тобто перше значення, яке збігається з "Капуста".
Докладні відомості див. у статтях про функції INDEX і MATCH.
Вертикальний пошук значень у списку з використанням приблизного збігу
Для цього скористайтеся функцією VLOOKUP.
Важливо
Переконайтеся, що значення в першому рядку відсортовано за зростанням.
У наведеному вище прикладі функція VLOOKUP шукає ім'я студента, який здійснив 6 запізнень у діапазоні A2:B7. У таблиці немає запису для 6 запізнень, тому функція VLOOKUP шукає наступний за величиною збіг, менший за 6, і знаходить значення 5, пов'язане з ім'ям Дейв, і таким чином повертає значення Dave.
Додаткові відомості див. у статті Функція VLOOKUP.
Пошук значень по вертикалі в списку з невідомим розміром за допомогою точного збігу
Для цього завдання скористайтеся функціями OFFSET і MATCH.
Примітка.
Цей підхід доцільно використовувати, коли дані містяться в діапазоні зовнішніх даних, який оновлюється щодня. Ви знаєте, що ціну вказано в стовпці B, але невідомо, скільки рядків даних поверне сервер, а перший стовпець не відсортовано за алфавітом.
C1 – це верхня ліва клітинка діапазону (також називається початковою клітинкою).
MATCH("Апельсини";C2:C7;0) виявляє апельсини в діапазоні C2:C7. Не потрібно включати в діапазон початкову клітинку.
1 — це кількість стовпців праворуч від вихідної клітинки, звідки має бути повернуте значення. У нашому прикладі повернуте значення зі стовпця D, Продажі.
Пошук значень по горизонталі в списку з використанням точного збігу
Для цього завдання скористайтеся функцією HLOOKUP. Ось приклад:
Функція HLOOKUP шукає стовпець «Збут » і повертає значення з рядка 5 у вказаному діапазоні.
Докладні відомості див. у статті Функція HLOOKUP
Пошук значень по горизонталі в списку за допомогою приблизного збігу
Для цього завдання скористайтеся функцією HLOOKUP.
Важливо
Переконайтеся, що значення в першому рядку відсортовано за зростанням.
У наведеному вище прикладі функція HLOOKUP шукає значення 11 000 у рядку 3 у вказаному діапазоні. Функція не знаходить число 11 000, тому шукає наступне найбільше значення, менше за 1100, і повертає 10543.
Докладні відомості див. у статті Функція HLOOKUP