Як шукати кілька значень в Excel: крок за кроком

Під час роботи з великими наборами даних у Microsoft Excel може бути складно знайти кілька значень, які відповідають кільком умовам. Деякі вбудовані функції, наприклад VLOOKUP, спочатку були розроблені для роботи з окремими значеннями.

Кілька можна об’єднати для пошуку кількох значень. Вони включають функції VLOOKUP, INDEX, MATCH і IF. Поточні версії Excel можуть використовувати динамічні масиви, тоді як старі версії використовують формули масивів.

Ця стаття покаже вам, як саме використовувати ці функції у формулах, які знаходять кілька значень у ваших даних.

Ходімо!

Зміст

• Як використовувати VLOOKUP із кількома значеннями
  • Що таке формули масиву?
  • Приклад VLOOKUP
  • Останні версії Excel
  • Старіші версії
  • Точна відповідність проти приблизної відповідності
  • Докладніше про VLOOKUP
• Як використовувати INDEX і MATCH для пошуку кількох значень
  • Як використовувати INDEX і MATCH разом у Excel 365
  • Робота зі старими версіями Excel
• Як використовувати функцію IF для пошуку кількох значень
  • Поточні версії Excel
  • Старіші версії Excel
• Як використовувати SUMPRODUCT для кількох критеріїв
• 3 поширені помилки з функціями пошуку
  • 1. Помилки #N/A
  • 2. #REF! Помилки
  • 3. Циркулярні помилки
• Як використовувати IFERROR з функціями пошуку
• 7 передових методів пошуку
  • 1. Пошук відносної позиції
  • 2. Функція SMALL
  • 3. Функція ROW
  • 4. Функція FILTER
  • 5. Допоміжні стовпці
  • 6. Динамічні масиви
  • 7. Power Query
• Заключні думки

Як використовувати VLOOKUP із кількома значеннями

Функція часто використовується для пошуку окремих значень у діапазоні даних. Однак ви також можете шукати кілька збігів у Excel за допомогою цієї формули пошуку.

За замовчуванням він повертає лише перше знайдене відповідне значення. Однак ви можете змінити функцію для повернення кількох значень за допомогою формули масиву.

Що таке формули масиву?

Формула масиву — це формула, яка може виконувати обчислення з масивами даних. Її називають формулою масиву, оскільки вона може повертати масив результатів, а не одне значення.

Під час створення формули масиву можна виконати кілька простих кроків:

1. Виберіть діапазон комірок для пошуку.

2. Введіть формулу в рядок формул.

3. Натисніть Ctrl + Shift + Enter, щоб завершити.

Синтаксис формули масиву схожий на синтаксис звичайної формули, але включає фігурні дужки {} навколо формули. Фігурні дужки вказують на те, що формула є формулою масиву і повертатиме масив значень.

Приклади в цій статті покажуть, як правильно використовувати формули масивів.

Приклад VLOOKUP

У нашому прикладі п’ять елементів у стовпці A аркуша:

• Яблуко

• Банан

• Хліб

• Морква

• вишня

Завдання — перевірити, чи є в цьому списку три фрукти: яблуко, банан і вишня.

Останні версії Excel

Синтаксис VLOOKUP залежить від того, яку версію Excel ви використовуєте.

Найновіші версії ( Excel 365 або Excel 2021 ) підтримують динамічні масиви. Ця функція дозволяє формулам повертати кілька результатів, які «переливаються» в сусідні клітинки.

Ось синтаксис (формула починається зі знака рівності):

=VLOOKUP(пошукове_значення, масив_таблиці, номер_індексу_стовпця, [діапазон_пошуку])

• lookup_value: значення, яке ви хочете знайти.

• table_array: уся таблиця даних, у якій потрібно виконати пошук.

• col_index_num: номер стовпця таблиці в table_array, який містить дані, які потрібно повернути.

• діапазон_пошуку: необов'язковий. Визначає, чи потрібна вам точна чи приблизна відповідність.

У нашому конкретному прикладі використовується ця формула:

=VLOOKUP({“яблуко”,”банан”,”вишня”},A1:A5,1,НЕПРАВДА)

Коли формула вводиться в клітинку B1, результати переливаються в клітинки C1 і D1. На цьому малюнку показано приклад у дії:

Старіші версії

Якщо ви користуєтеся старішою версією Excel, яка не підтримує динамічні масиви (наприклад, Excel 2019 або ранішою), вам потрібно буде використовувати дещо інший підхід із формулою масиву.

Виконайте такі дії:

1. Клацніть клітинку, у якій потрібно відобразити результати для першого елемента (наприклад, стовпець B).

2. Введіть таку формулу, не натискаючи Enter: =VLOOKUP({“яблуко”,”банан”,”вишня”}, A1:A5, 1, FALSE)

3. Натисніть Ctrl + Shift + Enter, щоб перетворити це на формулу масиву.

4. Скопіюйте клітинку B1 і вставте її в клітинку нижче (або скористайтеся маркерами заповнення).

Коли ви використовуєте Ctrl + Shift + Enter, Excel додає фігурні дужки навколо формули. Це означає, що це формула масиву.

Точна відповідність проти приблизної відповідності

За замовчуванням функція VLOOKUP використовує приблизний збіг. Це означає, що він поверне найближчу відповідність, яку зможе знайти, навіть якщо значення клітинок не є точним.

Якщо ви хочете виконати точний збіг, ви можете встановити для аргументу range_lookup значення FALSE.

Майте на увазі, що приблизні збіги найкраще працюють із упорядкованими числовими значеннями. Зазвичай це не підходить, коли значення комірки є текстом.

Докладніше про VLOOKUP

Якщо ви хочете дізнатися більше про цю універсальну функцію, перегляньте ці статті:

Тепер, коли ви налаштувалися на функцію VLOOKUP, давайте подивимося на дві інші функції, які можуть робити те, що вони роблять по-іншому: INDEX і MATCH.

Як використовувати INDEX і MATCH для пошуку кількох значень

Ви можете об’єднати функції INDEX і MATCH, щоб знайти кілька значень у кількох рядках.

Функція INDEX в Excel повертає значення або посилання на клітинку в межах указаного діапазону.

=ІНДЕКС(масив, номер_рядка, [номер_стовпця])

• масив: діапазон комірок для пошуку значення.

• row_num: номер рядка в масиві, з якого потрібно повернути значення.

• column_num: (необов’язковий) номер стовпця в масиві, з якого повертається значення. Якщо опущено, функція повертає весь рядок.

Функція MATCH в Excel повертає позицію значення в межах указаного діапазону.

=MATCH(пошукове_значення, пошуковий_масив, [тип_збігу])

• lookup_value: значення, яке потрібно шукати в lookup_array.

• lookup_array: Діапазон комірок, у яких потрібно шукати lookup_value.

• match_type: (необов’язковий) тип відповідності, який буде виконано. Якщо опущено, функція виконує точний збіг.

Як використовувати INDEX і MATCH разом у Excel 365

Щоб використовувати INDEX і MATCH разом для пошуку кількох значень у Excel, потрібно використовувати формулу масиву.

Працюючи з попередніми зразками даних, це формула в Excel 365:

=ІНДЕКС(A1:A5, MATCH({"яблуко","банан","вишня"}, A1:A5, 0))

Наведений вище приклад розбивається так:

• ІНДЕКС: повертає значення комірки у вказаному діапазоні на основі заданого номера рядка та стовпця. У цьому випадку він поверне значення з діапазону A1:A5.

• A1:A5: це визначений діапазон таблиці, у якому ви шукаєте значення та з якого буде повернено результат.

• MATCH: шукає вказаний елемент у діапазоні клітинок і повертає відносну позицію цього елемента в діапазоні.

• {“apple”,”banana”,”cherry”}: це константа масиву, що містить значення, які ви хочете знайти.

• A1:A5: це діапазон, де MATCH шукатиме значення з константи масиву.

• 0: це тип відповідності для функції MATCH. У цьому випадку це 0, що означає, що ви шукаєте точну, а не близьку відповідність.

 На цьому малюнку показана формула в дії:

Робота зі старими версіями Excel

Якщо ви використовуєте старіший файл Excel, який не підтримує динамічні масиви (наприклад, Excel 2019 або ранішої версії), вам потрібно буде використовувати інший підхід.

Оскільки старіші версії не підтримують «розлив» формул у суміжні комірки, вам потрібно буде розділити використання на три окремі формули.

Виконайте такі дії:

1. Клацніть клітинку, у якій потрібно отримати результат для першого елемента (наприклад, клітинка B1)

2. Введіть наведену нижче формулу:

3. =ІНДЕКС(A1:A5; MATCH(“яблуко”; A1:A5; 0))

4. Натисніть Enter, щоб виконати формулу.

5. Введіть цю формулу в клітинку B2: =INDEX(A1:A5, MATCH(“банан”, A1:A5, 0))

6. Введіть цю формулу в клітинку B3: =ІНДЕКС(A1:A5, MATCH("вишенька", A1:A5, 0))

На цьому малюнку показано посилання на клітинку B3:

Функції INDEX і MATCH — не єдині, які дозволяють знайти кілька значень. У наступному розділі ми розглянемо, як можна використовувати функцію IF як альтернативу.

Як використовувати функцію IF для пошуку кількох значень

Інший спосіб пошуку значень у кількох клітинках на основі певних критеріїв — використання функції IF разом з іншими функціями.

Функція IF дозволяє тестувати кілька умов і повертати різні результати залежно від результату цих перевірок.

Наприклад, припустімо, що у вас є таблиця даних про продажі зі стовпцями для продуктів і продажів. Ви хочете знайти та підсумувати суму продажів для двох із трьох продуктів.

Поточні версії Excel

Щоб знайти суму в стовпці «Продажі», де товаром є «Яблуко» або «Банан», за допомогою функції ЯКЩО, можна використати формулу масиву з функціями ЯКЩО, СУММА та АБО.

Якщо ваші дані починаються з комірки A1, скористайтеся такою формулою:

=SUM(IF((A2:A4=”Яблуко”)+(A2:A4=”Банан”), B2:B4, 0))

Розділ (A2:A4=”Яблуко”)+(A2:A4=”Банан”) створює масив зі значенням 1, якщо клітинка в діапазоні A2:A4 містить “Яблуко” або “Банан”, і 0 в іншому випадку .

Оператор IF перевіряє кожен елемент аргументу масиву. Якщо значення дорівнює 1 (тобто продукт є або «Яблуко», або «Банан»), воно приймає відповідне значення в стовпці Продажі (діапазон B2:B4); інакше потрібно 0.

Функція SUM додає значення функції IF, фактично підсумовуючи вартість продажів для «Apple» і «Banana».

На цьому малюнку показано дію формули в діапазоні пошуку:

Старіші версії Excel

У Excel 2019 або раніших версіях вам потрібно використовувати формулу масиву. Виконайте такі дії:

1. Введіть формулу, але не натискайте Enter.

2. Натисніть Ctrl + Shift + Enter, щоб зробити його формулою масиву.

Excel додасть фігурні дужки {} навколо формули, вказуючи, що це формула масиву.

Далі ми розглянемо, як ви можете використовувати SUMPRODUCT для пошуку кількох значень на основі ваших критеріїв. Ходімо!

Як використовувати SUMPRODUCT для кількох критеріїв

Функція SUMPRODUCT також дозволяє шукати кілька значень на основі кількох критеріїв.

Оскільки для цього не потрібно використовувати формулу масиву, синтаксис є однаковим незалежно від версії Excel.

Використовуючи ті самі дані, що й у попередньому прикладі, формула виглядає так:

=SUMPRODUCT((A2:A4=”Яблуко”)+(A2:A4=”Банан”), B2:B4)

Розділ (A2:A4=”Яблуко”)+(A2:A4=”Банан”) створює масив зі значенням 1, якщо клітинка в діапазоні A2:A4 містить “Яблуко” або “Банан”, і 0 в іншому випадку .

Функція SUMPRODUCT множить елементи масиву на відповідні елементи в стовпці Sales (діапазон B2:B4). Потім він додає отримані значення, фактично підсумовуючи вартість продажів як для «Яблука», так і для «Банану».

Наведена нижче формула показує це в дії:

Функції Excel чудові, коли вони працюють належним чином, але іноді можуть виникати помилки. У наступному розділі ми розглянемо деякі поширені помилки та способи їх вирішення.

3 поширені помилки з функціями пошуку

Функції пошуку іноді можуть повертати помилки, усунення яких може бути неприємним і займати багато часу. Три найпоширеніші помилки, з якими ви зіткнетеся:

1. #N/A Помилки

2. #REF! Помилки

3. Циркулярні помилки

1. Помилки #N/A

Помилка #N/A виникає, коли шукане значення не можна знайти в масиві пошуку.

Є кілька причин, чому ця помилка може виникати, зокрема:

• пошукове значення написано з помилкою або неправильне.

• масив пошуку не відсортовано за зростанням.

• значення пошуку немає в наборі даних.

Якщо значення пошуку немає в наборі даних, це корисна інформація. Однак недосвідчені користувачі Excel можуть подумати, що #N/A означає, що з формулою щось пішло не так. У наступному розділі показано, як зробити це більш зручним для користувача.

2. #REF! Помилки

#REF! помилка виникає, коли масив пошуку або масив повернення видаляється або переміщується.

Цю помилку можна виправити, оновивши посилання на клітинки у функції пошуку.

3. Циркулярні помилки

Коли ви поєднуєте функції в складні формули, Excel може повідомити вам, що у вас є циклічне посилання.

Вам буде легше їх дослідити, скориставшись нашим посібником із .

Як використовувати IFERROR з функціями пошуку

Функція IFERROR — це корисний інструмент для обробки помилок у функціях пошуку. Це дозволяє вказати значення або формулу для повернення, якщо функція пошуку повертає помилку.

Синтаксис функції IFERROR такий:

=ЯКЩОПОМИЛКА(значення, значення_якщо_помилка)

· значення: значення або формула, яку потрібно оцінити.

· Значення_якщо_помилка: значення або формула для повернення, якщо перший аргумент повертає помилку.

Наприклад, скажімо, у вас є функція VLOOKUP, яка шукає кілька значень у таблиці. На малюнку нижче одне зі значень не існує в діапазоні шуканих даних.

Як бачите, відображається помилка #N/A, яка може заплутати недосвідчених користувачів Excel.

Натомість ви можете використовувати IFERROR, щоб відобразити порожню комірку або повідомлення «Не знайдено» з таким синтаксисом:

=ЯКЩОПОМИЛКА(VLOOKUP(пошукове_значення, масив_таблиці, індекс_стовпця, ЛОЖЬ), «Не знайдено»)

У цьому прикладі, якщо функція VLOOKUP повертає помилку, функція IFERROR натомість поверне повідомлення «Не знайдено».

На цьому малюнку показана формула в дії. Стовпець B містить відсутнє значення, а стовпці C і D знайшли збіги.

Наразі ми охопили багато, і ви нарешті готові вивчити більш просунуті методи пошуку, що є темою наступного розділу.

7 передових методів пошуку

Пошук кількох значень у Excel може бути складним завданням, особливо під час роботи з великими наборами даних. Ви можете зіткнутися з проблемами продуктивності через повільну обробку.

Існує сім розширених методів пошуку, які можна використовувати, щоб зробити процес простішим і ефективнішим.

• Пошук відносної позиції

• Функція SMALL

• Функція ROW

• Функція FILTER

• Допоміжні колони

• Динамічні масиви

• Power Query

1. Пошук відносної позиції

Одним із найпростіших є використання пошуку відносної позиції. Це включає в себе визначення зміщення рядка та стовпця від даної комірки, щоб знайти потрібне значення.

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

=ЗМІЩ(комірка; 2; 3)

2. Функція SMALL

Ще одна корисна техніка для пошуку кількох значень — це використання функції SMALL. Ця функція повертає n-те найменше значення в діапазоні клітинок.

Комбінуючи цю функцію з іншими функціями пошуку, такими як INDEX і MATCH, ви можете шукати кілька значень на основі певних критеріїв.

Наприклад, наступна формула шукає друге найменше значення в діапазоні клітинок, які відповідають певній умові:

=ІНДЕКС(дані, MATCH(МАЛИЙ(ЯКЩО(критерії, діапазон), 2), діапазон, 0))

3. Функція ROW

Функцію ROW також можна використовувати для пошуку кількох значень у Excel. Ця функція повертає номер рядка даної клітинки, який можна використовувати для посилання на клітинки в таблиці даних.

Наприклад, наступна формула шукає значення в таблиці на основі унікального ідентифікатора:

=INDEX(дані, MATCH(унікальний_ідентифікатор, дані[унікальний_ідентифікатор], 0), номер_індексу_стовпця)

4. Функція FILTER

Функція FILTER недоступна в старіших версіях Excel.

У Excel 365 ви можете використовувати його для фільтрації діапазону клітинок на основі певних критеріїв і повертати лише значення, які відповідають цим критеріям. Ось синтаксис і три аргументи:

=ФІЛЬТР(масив, включити, [якщо_порожній])

• масив: конкретні дані, які потрібно відфільтрувати.

• include: Аргумент include — це критерії або умови, які потрібно застосувати до масиву.

• [if_empty] (необов’язковий): значення, яке повертається, якщо жоден рядок або стовпець не відповідає критеріям, указаним у аргументі include.

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

=SUM(FILTER(B2:B4, (A2:A4=”Яблуко”)+(A2:A4=”Банан”)))

На цьому малюнку показано, скільки рядків збігається, а також суму різних значень:

5. Допоміжні стовпці

Ви можете використовувати допоміжний стовпець, щоб об’єднати кілька полів у такі функції, як VLOOKUP.

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

6. Динамічні масиви

Як ви дізналися з попередніх прикладів, користувачі Microsoft 365 можуть скористатися перевагами динамічних масивів для пошуку кількох значень у Excel.

Dynamic arrays allow you to return multiple values from a single formula, making it easier to lookup large amounts of data.

7. Power Query

Power Query is a powerful tool in Excel that can be used to return values based on multiple criteria.

For example, this video finds messy data in a spreadsheet and cleans it up.

Final Thoughts

That wraps up our deep dive into the art of looking up multiple values in Excel! Once you’ve mastered the VLOOKUP, INDEX, MATCH, and array formulas, you’ll find yourself breezing through complex data sets like a hot knife through butter.

The key is to understand the syntax and the logic behind each formula. Keep in mind that these formulas can be complex, so it’s important to take your time and test your formulas thoroughly before relying on them for important data analysis.

Excel — потужний інструмент, але він настільки хороший, наскільки хороший користувач. Тож продовжуйте вдосконалювати ці навички, продовжуйте експериментувати, і незабаром ви станете майстром багатозначного пошуку. До наступного разу продовжуйте обчислювати ці цифри та змушуйте Excel виконувати важку роботу за вас!