Applies ToExcel для Microsoft 365 Вебпрограма Excel Excel 2024 Excel 2021 Excel 2019 Excel 2016

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

У цій статті

Пошук значень у списку по вертикалі за допомогою точного збігу

Для цього можна скористатися функцією VLOOKUP або комбінацією функцій INDEX і MATCH.

Приклади VLOOKUP

=VLOOKUP (B3;B2:E7;2;FALSE)

Функція VLOOKUP шукає Фонтану в першому стовпці (стовпець B) у table_array B2:E7 і повертає Олів'є з другого стовпця (стовпець C) table_array.  Значення False повертає точний збіг.

=VLOOKUP (102;A2:C7;2;FALSE)

Функція VLOOKUP шукає точний збіг (FALSE) прізвища для 102 (lookup_value) у другому стовпці (стовпець B) у діапазоні A2:C7 і повертає функцію Fontana.

Докладні відомості див. в статті Функція VLOOKUP.

Приклади INDEX і MATCH

Функції INDEX і MATCH можна використовувати замість функції VLOOKUP.

Простою українською мовою це означає:

=INDEX(я хочу повернути значення із C2:C10;яке ЗБІГАЄТЬСЯ_ЗІ_ЗНАЧЕННЯМ("Капуста"; що розташоване десь у масиві B2:B10; де повернуте значення – перше значення, яке збігається з "Капуста"))

Формула шукає перше значення в клітинці C2:C10, яке відповідає "Капуста " (у клітинці B7) і повертає значення в клітинці C7 (100), яке є першим значенням, яке відповідає "Капуста".

Докладні відомості див. в статті Функції INDEX і MATCH.

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

Пошук значень у списку по вертикалі за допомогою приблизного збігу

Для цього скористайтеся функцією VLOOKUP.

Увага!: Переконайтеся, що значення в першому рядку відсортовано за зростанням.

Приклад формули VLOOKUP, яка шукає приблизний збіг

У наведеному вище прикладі функція VLOOKUP шукає ім'я учня, який має 6 символів у діапазоні A2:B7. У таблиці немає запису для 6 значень, тому функція VLOOKUP шукає наступний найвищий збіг, нижчий за 6, і знаходить значення 5, пов'язане з іменем Дейва, і таким чином повертає Дейва.

Докладні відомості див. в статті Функція VLOOKUP.

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

Пошук значень по вертикалі у списку невідомого розміру за допомогою точного збігу

Для виконання цього завдання використовуйте функції OFFSET і MATCH.

Примітка.: Використовуйте цей підхід, коли дані розташовано в діапазоні зовнішніх даних, який оновлюється щодня. Ви знаєте, що ціна міститься в стовпці B, але ви не знаєте, скільки рядків даних поверне сервер, а перший стовпець не відсортовано за алфавітом.

Приклад функцій OFFSET і MATCH

C1 – це верхня ліва клітинка діапазону (її також називають початковою клітинкою).

MATCH("Апельсини",C2:C7,0) шукає апельсини в діапазоні C2:C7. Початкову клітинку не слід включати до діапазону.

1 – це кількість стовпців праворуч від початкової клітинки, звідки має бути повернуте значення. У нашому прикладі повернуте значення отримано зі стовпця D, Sales.

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

Пошук значень по горизонталі в списку за допомогою точного збігу

Для виконання цього завдання використовуйте функцію HLOOKUP. Ось приклад:

Приклад формули HLOOKUP, яка шукає точний збіг

Функція HLOOKUP шукає стовпець "Продажі " та повертає значення з рядка 5 у вказаному діапазоні.

Докладні відомості див. в статті Функція HLOOKUP.

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

Пошук значень у списку по горизонталі за допомогою приблизного збігу

Для виконання цього завдання використовуйте функцію HLOOKUP.

Увага!: Переконайтеся, що значення в першому рядку відсортовано за зростанням.

Приклад формули HLOOKUP, яка шукає приблизний збіг

У наведеному вище прикладі функція HLOOKUP шукає значення 11000 у рядку 3 у вказаному діапазоні. Він не може знайти 11000 і, отже, шукає наступне найбільше значення менше 1100 і повертає 10543.

Докладні відомості див. в статті Функція HLOOKUP.

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

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

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

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

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