Пошук значень у списку даних

Пошук значень у списку даних

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

Виберіть дію

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

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

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

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

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

Створення формули підстановки за допомогою майстра підстановок (лише Excel 2007)

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

Щоб виконати це завдання, ви можете використати функцію VLOOKUP або сполучення функцій INDEX і MATCH.

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

Приклад 1. Функція VLOOKUP

Приклад 2. Функція VLOOKUP

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

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

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

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

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

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

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

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

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

Щоб виконати це завдання, ви можете використати функцію VLOOKUP.

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

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

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

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

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

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

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

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

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

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

Формула MATCH("Апельсини";C2:C7;0) шукає значення "Апельсини" в діапазоні C2:C7. У діапазон не слід включати початкову клітинку.

1 – це кількість стовпців, які потрібно відрахувати праворуч від початкової клітинки, щоб отримати стовпець, з якого повертається значення. У нашому прикладі значення повертається зі стовпця D (Продажі).

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

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

Щоб виконати це завдання, скористайтеся функцією HLOOKUP. Нижче наведено приклад цієї функції.

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

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

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

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

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

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

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

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

У прикладі вище функція HLOOKUP шукає значення 11 000 в рядку 3 вказаного діапазону. Значення 11 000 відсутнє, тому вона шукає наступне максимальне значення, яке менше 11 000, і повертає 10 543.

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

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

Створення формули підстановки за допомогою майстра підстановок (лише Excel 2007)

Примітка : Підтримку надбудови "Майстер підстановок" припинено в Excel 2010. Її замінено майстром функцій і функціями для роботи з посиланнями та масивами.

В Excel 2007 майстер підстановок створює формулу підстановки на основі даних аркуша, який містить назви рядків і стовпців. Майстер підстановок допомагає знайти значення в рядку, якщо відомо значення у стовпці, і навпаки. У створюваних формулах він використовує функції INDEX і MATCH.

  1. Клацніть клітинку в діапазоні.

  2. На вкладці Формули у групі Вирішення виберіть пункт Підстановка.

  3. Якщо команда Підстановка недоступна, потрібно завантажити надбудова майстра підстановок.

    Як завантажити надбудову майстра підстановок

  4. Натисніть кнопку Microsoft Office  Зображення кнопки Office , потім – Параметри Excel, а потім виберіть категорію Надбудови.

  5. У полі Керування виберіть пункт Надбудови Excel і натисніть кнопку Перейти.

  6. У діалоговому вікні Наявні надбудови встановіть прапорець поряд із пунктом Майстер підстановок і натисніть кнопку ОК.

  7. Виконайте вказівки майстра.

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

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

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

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

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

×