зміст
Раніше ми пояснювали новачкам, як користуватися основними функціями VLOOKUP (англ. VLOOKUP, абревіатура розшифровується як «функція вертикального пошуку»). А досвідченим користувачам показали кілька складніших формул.
І в цій статті ми постараємося надати інформацію про ще один метод роботи з вертикальним пошуком.
Ви можете запитати: «Навіщо це потрібно?». А це потрібно для того, щоб показати всі можливі методи пошуку. Крім того, численні обмеження VLOOKUP часто заважають отримати бажаний результат. У цьому відношенні INDEX( ) MATCH( ) є набагато більш функціональними та різноманітними, а також вони мають менше обмежень.
Основи INDEX MATCH
Оскільки мета цього посібника — показати, наскільки гарна ця функція, ми Розглянемо основну інформацію щодо принципів його роботи. І ми покажемо приклади, а також розглянемо чому, це краще VLOOKUP ().
Синтаксис і використання функції INDEX
Ця функція допомагає знайти потрібне значення серед заданих областей пошуку на основі номера стовпця чи рядка. Синтаксис:
=ІНДЕКС(масив, номер рядка, номер стовпця):
- масив – область, в якій здійснюватиметься пошук;
- номер рядка – номер рядка, який шукається у вказаному масиві. Якщо номер рядка невідомий, необхідно вказати номер стовпця;
- номер стовпця – номер стовпця, який буде знайдений у вказаному масиві. Якщо значення невідоме, потрібно вказати номер рядка.
Приклад простої формули:
=ІНДЕКС(A1:S10,2,3;XNUMX;XNUMX)
Функція шукатиме в діапазоні від A1 до C10. Цифри показують, з якого рядка (2) і стовпця (3) відобразити бажане значення. Результатом буде комірка C2.
Досить просто, правда? Але коли ви працюєте з реальними документами, ви навряд чи матимете інформацію про номери стовпців або комірок. Для цього і призначена функція MATCH().
Синтаксис і використання функції MATCH
Функція MATCH() шукає потрібне значення та показує його приблизне число у вказаній області пошуку.
Синтаксис searchpos() виглядає так:
=MATCH(значення для пошуку, масив для пошуку, тип відповідності)
- пошукове значення – число або текст, який потрібно знайти;
- шуканий масив – область, де здійснюватиметься пошук;
- тип відповідності – вказує, чи шукати точне значення або значення, найближчі до нього:
- 1 (або значення не вказано) – повертає найбільше значення, що дорівнює або менше зазначеного значення;
- 0 – показує точний збіг із шуканим значенням. У комбінації INDEX() MATCH() майже завжди знадобиться точний збіг, тому пишемо 0;
- -1 – показує найменше значення, яке більше або дорівнює значенню, указаному у формулі. Сортування здійснюється в порядку спадання.
Наприклад, в діапазоні B1:B3 зареєстровані Нью-Йорк, Париж, Лондон. Формула нижче покаже число 3, оскільки Лондон займає третє місце в списку:
=EXPOSE(Лондон;B1:B3,0;XNUMX)
Як працювати з функцією INDEX MATCH
Напевно, ви вже почали розуміти, за яким принципом побудована спільна робота цих функцій. Коротше кажучи, тоді INDEX() шукає потрібне значення серед указаних рядків і стовпців. І MATCH() показує числа цих значень:
=INDEX(стовпець, з якого повертається значення, MATCH(значення для пошуку, стовпець для пошуку, 0))
Все ще важко зрозуміти, як це працює? Можливо, приклад краще пояснить. Припустімо, що у вас є список світових столиць і їх населення:
Щоб дізнатися чисельність населення певної столиці, наприклад, столиці Японії, скористаємося такою формулою:
=ІНДЕКС(C2:C10; MATCH(Японія; A2:A10,0;XNUMX))
Пояснення:
- Функція MATCH() шукає значення «Японія» в масиві A2:A10 і повертає число 3, оскільки Японія є третім значенням у списку.
- Ця цифра йде наномер рядка” у формулі INDEX() і вказує функції надрукувати значення з цього рядка.
Отже, наведена вище формула стає стандартною формулою ІНДЕКС(C2:C10,3;XNUMX). Формула шукає від клітинок C2 до C10 і повертає дані з третьої клітинки в цьому діапазоні, тобто C4, оскільки зворотний відлік починається з другого рядка.
Не хочете прописувати назву міста у формулі? Потім запишіть його в будь-яку комірку, скажімо F1, і використовуйте його як посилання у формулі MATCH(). І ви отримуєте формулу динамічного пошуку:
=ИНДЕКС(С2:С10, ПОИСКПОЗ( )(F1,A2:A10,0))
Важливо! Кількість рядків в масив INDEX() має збігатися з кількістю рядків у розглянутий масив в MATCH(), інакше ви отримаєте неправильний результат.
Зачекайте, чому б просто не використати формулу VLOOKUP()?
=VLOOKUP(F1; A2:C10; 3; False)
Який сенс витрачати час, намагаючись зрозуміти всі ці складності INDEX MATCH?
У цьому випадку не має значення, яку функцію використовувати. Це лише приклад, щоб зрозуміти, як функції INDEX() і MATCH() працюють разом. Інші приклади покажуть, на що здатні ці функції в ситуаціях, коли VLOOKUP не працює.
INDEX MATCH або VLOOKUP
Вирішуючи, яку формулу пошуку використовувати, багато хто погоджується, що INDEX() і MATCH() значно перевершують VLOOKUP. Однак багато людей все ще використовують VLOOKUP(). По-перше, VLOOKUP() простіше, а по-друге, користувачі не до кінця розуміють усі переваги роботи з INDEX() і MATCH(). Без цих знань ніхто не погодиться витрачати свій час на вивчення складної системи.
Ось основні переваги INDEX() і MATCH() над VLOOKUP():
- Пошук справа наліво. VLOOKUP() не може здійснювати пошук справа наліво, тому значення, які ви шукаєте, завжди мають бути в крайніх лівих стовпцях таблиці. Але INDEX() і MATCH() можуть впоратися з цим без проблем. Як це виглядає на практиці: як знайти шукане значення зліва, розповість ця стаття.
- Безпечне додавання або видалення стовпців. Формула VLOOKUP() показує неправильні результати під час видалення або додавання стовпців, оскільки VLOOKUP() потребує точного номера стовпця для успішного виконання. Природно, коли стовпці додаються або видаляються, їх кількість також змінюється.
А у формулах INDEX() і MATCH() вказується діапазон стовпців, а не окремі стовпці. У результаті ви можете безпечно додавати та видаляти стовпці без потреби кожного разу оновлювати формулу.
- Немає обмежень щодо обсягів пошуку. Під час використання VLOOKUP() загальна кількість критеріїв пошуку не має перевищувати 255 символів, інакше ви отримаєте #VALUE! Отже, якщо ваші дані містять велику кількість символів, INDEX() і MATCH() є найкращим варіантом.
- Висока швидкість обробки. Якщо ваші столи відносно невеликі, ви навряд чи помітите якусь різницю. Але, якщо таблиця містить сотні або тисячі рядків, і, відповідно, є сотні і тисячі формул, INDEX () і MATCH () впораються набагато швидше, ніж VLOOKUP (). Справа в тому, що Excel оброблятиме лише стовпці, зазначені у формулі, а не всю таблицю.
Вплив VLOOKUP() на продуктивність буде особливо помітним, якщо ваш робочий аркуш містить велику кількість таких формул, як VLOOKUP() і SUM(). Для аналізу кожного значення в масиві потрібні окремі перевірки функцій VLOOKUP(). Тож Excel доводиться обробляти величезну кількість інформації, а це значно сповільнює роботу.
Приклади формул
З корисністю цих функцій ми вже розібралися, тож можемо переходити до найцікавішого: застосування знань на практиці.
Формула пошуку справа наліво
Як уже згадувалося, VLOOKUP не може виконати цей спосіб пошуку. Отже, якщо бажаних значень немає в крайньому лівому стовпці, VLOOKUP() не дасть результату. Функції INDEX() і MATCH() більш універсальні, і розташування значень не грає великої ролі для їх роботи.
Наприклад, до лівої частини таблиці ми додамо стовпець рангів і спробуємо з’ясувати, який ранг за чисельністю населення займає столиця Нашої країни.
У клітинку G1 ми записуємо значення, яке потрібно знайти, а потім використовуємо таку формулу для пошуку в діапазоні C1:C10 і повертаємо відповідне значення з A2:A10:
=ИНДЕКС(А2:А10, ПОИСКПОЗ(G1,C1:C10,0))
Підказка. Якщо ви плануєте використовувати цю формулу для кількох комірок, переконайтеся, що ви виправили діапазони за допомогою абсолютної адресації (наприклад, $A$2: $A$10 і $C$2: 4C$10).
INDEX БІЛЬШ ЕКСПОЗИРОВАНО БІЛЬШ ЕКСПОЗИРОВАНО для пошуку в стовпцях і рядках
У наведених вище прикладах ми використовували ці функції як заміну VLOOKUP() для повернення значень із попередньо визначеного діапазону рядків. Але що, якщо вам потрібно зробити матричний або двосторонній пошук?
Звучить складно, але формула для таких обчислень схожа на стандартну формулу INDEX() MATCH(), лише з однією відмінністю: формулу MATCH() необхідно використовувати двічі. Перший раз, щоб отримати номер рядка, а другий раз, щоб отримати номер стовпця:
=INDEX(масив, MATCH(значення вертикального пошуку, стовпець пошуку, 0), MATCH(значення горизонтального пошуку, рядок пошуку, 0))
Давайте розглянемо таблицю нижче і спробуємо скласти формулу INDEX() EXPRESS() EXPRESS() щоб відобразити демографічні дані в певній країні за вибраний рік.
Цільову країну вказано в клітинці G1 (вертикальний пошук), а цільовий рік – у клітинці G2 (горизонтальний пошук). Формула буде виглядати так:
=ИНДЕКС(B2:D11, ПОИСКПОЗ(G1,A2:A11,0), ПОИСКПОЗ(G2,B1:D1,0))
Як працює ця формула
Як і будь-які інші складні формули, їх легше зрозуміти, якщо розбити їх на окремі рівняння. І тоді ви зможете зрозуміти, що робить кожна окрема функція:
- MATCH(G1;A2:A11,0;XNUMX) – шукає значення (G1) в діапазоні A2:A11 і показує номер цього значення, в нашому випадку це 2;
- ПОШУК(G2;B1:D1,0;XNUMX) – шукає значення (G2) у діапазоні B1:D1. У цьому випадку результат був 3.
Знайдені номери рядків і стовпців надсилаються у відповідне значення у формулі INDEX():
=ІНДЕКС(B2:D11,2,3;XNUMX;XNUMX)
В результаті ми маємо значення, яке знаходиться в комірці на перетині 2 рядків і 3 стовпців в діапазоні B2:D11. І формула показує потрібне значення, яке знаходиться в комірці D3.
Пошук за кількома умовами за допомогою INDEX і MATCH
Якщо ви читали наш посібник із VLOOKUP(), ви, ймовірно, пробували кілька формул пошуку. Але цей спосіб пошуку має одне суттєве обмеження – необхідність додавання допоміжної колонки.
Але хороша новина така За допомогою INDEX() і MATCH() ви можете шукати кілька умов без необхідності редагувати або змінювати робочий аркуш.
Ось загальна формула пошуку з кількома умовами для INDEX() MATCH():
{=ИНДЕКС(диапазон поиска, ПОИСКПОЗ(1,условие1=диапазон1)*(условвие2=диапазон2),0))}
Примітка: цю формулу слід використовувати разом із комбінацією клавіш CTRL+SHIFT+ENTER.
Припустімо, вам потрібно знайти значення, яке ви шукаєте, виходячи з 2 умов: Покупець и Товар.
Для цього потрібна така формула:
=ИНДЕКС(С2:С10, ПОИСКПОЗ(1,(F1=A2:A10)*(F2=B1:B10),0))
У цій формулі C2:C10 — це діапазон, у якому здійснюватиметься пошук, F1 - ця умова, A2:A10 — це діапазон для порівняння стану, F2 – умова 2, V2:V10 – діапазон для порівняння умови 2.
Не забудьте натиснути комбінацію в кінці роботи з формулою CTRL+SHIFT+ENTER – Excel автоматично закриє формулу фігурними дужками, як показано в прикладі:
Якщо ви не хочете використовувати формулу масиву для своєї роботи, то додайте ще один INDEX() до формули та натисніть ENTER, це буде виглядати як у прикладі:
Як працюють ці формули
Ця формула працює так само, як стандартна формула INDEX() MATCH(). Щоб шукати кілька умов, ви просто створюєте кілька умов False і True, які представляють правильні та неправильні окремі умови. І тоді ці умови застосовуються до всіх відповідних елементів масиву. Формула перетворює аргументи False і True на 0 і 1 відповідно і виводить масив, де 1 є відповідними значеннями, знайденими в рядку. MATCH() знайде перше значення, яке відповідає 1, і передасть його у формулу INDEX(). А воно, в свою чергу, поверне вже потрібне значення в зазначеному рядку з потрібного стовпця.
Формула без масиву залежить від здатності INDEX() обробляти їх самостійно. Другий INDEX() у формулі відповідає false (0), тому він передає весь масив із цими значеннями у формулу MATCH().
Це досить розлоге пояснення логіки цієї формули. Для отримання додаткової інформації читайте статтю "INDEX MATCH з кількома умовами».
AVERAGE, MAX і MIN в INDEX і MATCH
Excel має власні спеціальні функції для пошуку середніх, максимумів і мінімумів. Але що, якщо ви хочете отримати дані з клітинки, пов’язаної з цими значеннями? В цьому випадку AVERAGE, MAX і MIN потрібно використовувати разом з INDEX і MATCH.
INDEX MATCH і MAX
Щоб знайти найбільше значення в стовпці D і відобразити його в стовпці C, скористайтеся формулою:
=ИНДЕКС(С2:С10, ПОИСКПОЗ(МАКС(D2:D10),D2:D10,0))
INDEX MATCH і MIN
Щоб знайти найменше значення в стовпці D і відобразити його в стовпці C, скористайтеся такою формулою:
=ИНДЕКС(С2:С10,ПОИСКПОЗ(МИН(D2:D10),D2:D10,0))
ІНДЕКС ПОШУКУ та ЗМІЯ
Щоб знайти середнє значення в стовпці D і відобразити це значення в C:
=ИНДЕКС(С2:С10,ПОИСКПОЗ(СРЗНАЧ(D2:D10),D2:D10,-1))
Залежно від того, як записуються ваші дані, третій аргумент функції MATCH() дорівнює 1, 0 або -1:
- якщо стовпці відсортовані за зростанням, встановіть 1 (тоді формула обчислить максимальне значення, яке менше або дорівнює середньому значенню);
- якщо сортування відбувається за спаданням, то -1 (формула виведе мінімальне значення, яке більше або дорівнює середньому);
- якщо пошуковий масив містить значення, яке точно дорівнює середньому, тоді встановіть для нього значення 0.
У нашому прикладі сукупність відсортована в порядку спадання, тому ми ставимо -1. І результат – Токіо, оскільки значення населення (13,189 000) є найближчим до середнього значення (XNUMX).
VLOOKUP() також може виконувати такі обчислення, але лише у вигляді формули масиву: VLOOKUP із AVERAGE, MIN і MAX.
INDEX MATCH і ESND/IFERROR
Ви, напевно, вже помітили, що якщо формула не може знайти потрібне значення, вона видає помилку # N / A. Ви можете замінити стандартне повідомлення про помилку на щось більш інформативне. Наприклад, задайте аргумент у формулі У XNUMX-му:
=ЕСНД(ИНДЕКС(С2:С10,ПОИСКПОЗ(F1,A2:A10,0)),значение не найдено)
За допомогою цієї формули, якщо ви введете дані, яких немає в таблиці, форма видасть вам зазначене повідомлення.
Якщо ви хочете відловити всі помилки, то крім У XNUMX-му може бути використаний ПОМИЛКА:
=IFERROR(INDEX(C2:C10;MATCH(F1;A2:A10,0;XNUMX)), «Щось пішло не так!»)
Але пам’ятайте, що маскування помилок таким чином не є гарною ідеєю, оскільки стандартні помилки повідомляють про порушення у формулі.
Сподіваємось, наш посібник із використання функції INDEX MATCH() був для вас корисним.