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

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

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

Виберіть дію

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

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

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

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

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

Створення формули підстановки за допомогою майстра підстановок (лише 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

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

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

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

×