Перейти до основного
Підтримка
Вхід
Вхід за допомогою облікового запису Microsoft
Увійдіть або створіть обліковий запис.
Вітаємо,
Виберіть інший обліковий запис.
У вас є кілька облікових записів
Виберіть обліковий запис, за допомогою якого потрібно ввійти.

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

У цій статті

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

Для цього можна скористатися функцією 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.

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

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

Примітка.: Надбудову майстра підстановок припинено в Excel 2010. Цю функцію замінено майстром функцій і доступними функціями підстановки та довідковими функціями (довідка).

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

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

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

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

    Завантаження надбудови майстра підстановок

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

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

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

  7. Дотримуйтеся вказівок майстра.

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

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

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

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

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

Чи ця інформація була корисною?

Наскільки ви задоволені якістю мови?
Що вплинуло на ваші враження?
Натиснувши кнопку "Надіслати", ви надасте свій відгук для покращення продуктів і служб Microsoft. Ваш ІТ-адміністратор зможе збирати ці дані. Декларація про конфіденційність.

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

×