Пошук Значень
Свайпніть щоб показати меню
Функції пошуку вирішують одну з найпоширеніших задач у роботі з електронними таблицями: дані знаходяться в одному місці, а потрібно отримати пов’язану інформацію з іншого місця на основі відповідного значення.
Ви задаєте функції значення для пошуку, вказуєте, де шукати, і що повертати у разі знаходження збігу. Excel виконує пошук автоматично для кожного рядка щоразу, коли змінюються дані.
=XLOOKUP(lookup_value; lookup_array; return_array; [if_not_found])
Точний збіг
Точний збіг вимагає, щоб значення для пошуку повністю відповідало запису в довідковій таблиці — це значення за замовчуванням у XLOOKUP.
Приблизний збіг
Приблизний збіг знаходить найближче значення, яке не перевищує шукане, і використовується для багаторівневих систем, таких як податкові шкали або пороги знижок.
Ця поведінка залежить від параметра match_mode. Вищезазначене твердження є точним лише для match_mode = -1, коли функція повертає наступне менше значення, якщо точного збігу не знайдено. Для інших налаштувань match_mode результат визначається іншими правилами пошуку.
У функції XLOOKUP приблизний збіг контролюється аргументом match_mode — четвертим необов’язковим аргументом після if_not_found. Встановлення значення -1 повідомляє Excel: «якщо точного збігу не знайдено, повернути наступне менше значення».
=XLOOKUP(lookup_value; lookup_array; return_array; [if_not_found]; -1)
0 | -1 | 1 | 2 |
|---|---|---|---|
Exact match | Next smaller | Next larger | Wildcard |
Default. Returns error if value not found exactly. Use for text categories. | If no exact match, returns result for the largest value ≤ lookup value. Use for tiered systems. | If no exact match, returns result for the smallest value ≥ lookup value. | Allows * and ? as wildcards in the lookup value for partial text matching. |
Для приблизного збігу масив для пошуку має бути відсортований у порядку зростання. Несортована таблиця призведе до неправильних результатів без попередження.
VLOOKUP
VLOOKUP досі використовується у мільйонах електронних таблиць, і ви регулярно з ним стикатиметесь. Його основне обмеження — структурне: значення для пошуку завжди має бути у першому стовпці масиву таблиці, а повернення значення відбувається за номером стовпця. Якщо вставити стовпець між стовпцем пошуку та стовпцем повернення, цей номер стане неправильним — VLOOKUP поверне некоректні дані без жодного попередження. XLOOKUP напряму посилається на стовпець повернення, тому не має цієї проблеми.
=VLOOKUP(search_key; range; index; [is_sorted])
VLOOKUP досі працює, і ви зустрінете його у спадкованих файлах. Потрібно вміти його читати. Але для будь-яких формул, які ви створюєте самостійно, використовуйте XLOOKUP — він надійніший, зрозуміліший і чіткіше працює з приблизним співпадінням.
- Отримання місячного бюджету за категорією
Створіть довідкову таблицю під розділом Summary зі стовпцями Category та Monthly Budget.
Введіть такі значення:
- Rent — 1,500.00
- Groceries — 400.00
- Utilities — 250.00
- Transport — 200.00
- Dining — 150.00
Відформатуйте ці значення як валюту.
У таблиці Expenses отримайте бюджет для кожного рядка за допомогою:
=XLOOKUP(B9;I12:I20;J12:J20)
Це поверне бюджет, що відповідає кожній категорії.
- Обробка відсутніх категорій
Оновіть формулу, щоб уникнути помилок, якщо категорію не знайдено:
=XLOOKUP(B9;I12:I20;J12:J20;"No budget defined")
Це робить таблицю зрозумілішою та підсвічує відсутні відповідності.
- Фіксація довідкової таблиці
Зафіксуйте діапазони пошуку за допомогою F4, щоб вони не зміщувалися при копіюванні формули.
- Застосування приблизного співпадіння для рівнів знижок
Створіть нову таблицю зі стовпцями Spending thresholds та Discount values.
Введіть такі значення:
- 0 — 0
- 500 — 5
- 1000 — 10
- 2000 — 20
Відформатуйте значення знижок як відсотки.
Далі розрахуйте рівень знижки для кожної витрати за допомогою:
=XLOOKUP(D9;12:M16;N12:N16;"No tier";-1)
Дякуємо за ваш відгук!
Запитати АІ
Запитати АІ
Запитайте про що завгодно або спробуйте одне із запропонованих запитань, щоб почати наш чат
