INDEX-MATCH став більш популярним інструментом для Excel, оскільки він усуває обмеження функції VLOOKUP і його простіше використовувати. Функція INDEX-MATCH в Excel має низку переваг перед функцією VLOOKUP:
- INDEX і MATCH більш гнучкі та швидші, ніж Vlookup
- Можна виконати горизонтальний пошук, вертикальний пошук, двосторонній пошук, пошук ліворуч, пошук з урахуванням регістру та навіть пошук на основі кількох критеріїв.
- У відсортованих даних INDEX-MATCH працює на 30% швидше, ніж VLOOKUP. Це означає, що у більшому наборі даних 30% швидше має більше сенсу.
Давайте почнемо з детального опису кожного INDEX і MATCH.
Функція INDEX
Функція INDEX в Excel є дуже потужним і водночас гнучким інструментом, який отримує значення в заданому місці в діапазоні. Іншими словами, він повертає вміст комірки, визначений зміщенням рядків і стовпців.
Синтаксис:
=INDEX(reference, [row], [column])>
Параметри:
- довідка: масив комірок для зсуву. Це може бути окремий діапазон або цілий набір даних у таблиці даних. рядок [необов’язковий]: кількість зміщених рядків. Це означає, що якщо ми виберемо діапазон посилань таблиці як A1:A5, тоді комірка/вміст, який ми хочемо витягти, знаходиться на якій вертикальній відстані. Тут для рядка A1 буде 1, для рядка A2 = 2 і так далі. Якщо ми надамо рядок = 4, він витягне A4. Оскільки рядок є необов’язковим, тому, якщо ми не вказуємо номер рядка, він витягує цілі рядки в діапазоні посилань. У цьому випадку це від A1 до A5. стовпець [необов’язковий]: кількість зміщених стовпців. Це означає, що якщо ми виберемо діапазон посилань таблиці як A1:B5, то клітинка/вміст, який ми хочемо витягти, знаходиться на горизонтальній відстані. Тут для A1 рядок буде 1, а стовпець буде 1, для B1 рядок буде 1, але стовпець буде 2 аналогічно для A2 рядок = 2 стовпець = 1, для B2 рядок = 2 стовпець = 2 і так далі. Якщо ми надамо рядок = 5 і стовпець 2, тоді буде витягнуто B5. Оскільки цей стовпець необов’язковий, тому, якщо ми не вказуємо номер рядка. тоді він витягне весь стовпець у діапазоні посилання. Наприклад, якщо ми надамо рядок = 2 і стовпець як порожні, тоді буде витягнуто (A2:B2). Якщо ми не вкажемо рядок і стовпець, тоді буде витягнуто всю довідкову таблицю, яка є (A1:B5).
Довідкова таблиця: Наведену нижче таблицю буде використано як довідкову таблицю для всіх прикладів функції ІНДЕКС. Перша клітинка знаходиться на B3 (ЇЖА), а остання діагональна клітинка знаходиться на F10 (180).
приклади: Нижче наведено кілька прикладів функцій Index.
Випадок 1: Рядки та стовпці не згадуються.
Вхідна команда: =ІНДЕКС(B3:C10)
Випадок 2: Згадуються лише рядки.
Вхідна команда: =ІНДЕКС(B3:C10;2)
Випадок 3: Згадуються як рядки, так і стовпці.
Вхідна команда: =ІНДЕКС(B3:D10;4;2)
Випадок 4: Згадуються лише стовпці.
Вхідна команда: =ІНДЕКС(B3 : D10 ; ; 2)
Проблема з функцією INDEX: Проблема з функцією INDEX полягає в тому, що для даних, які ми шукаємо, потрібно вказати рядки та стовпці. Припустімо, що ми маємо справу з набором даних машинного навчання з 10 000 рядків і стовпців, тоді буде дуже важко шукати та витягувати дані, які ми шукаємо. Тут з’являється концепція функції відповідності, яка ідентифікує рядки та стовпці на основі певної умови.
Функція MATCH
Він отримує позицію елемента/значення в діапазоні. Це менш досконала версія VLOOKUP або HLOOKUP, яка повертає лише інформацію про місцезнаходження, а не фактичні дані. MATCH не враховує регістр і не має значення, горизонтальний чи вертикальний діапазон.
Синтаксис:
=MATCH(search_key, range, [search_type])>
Параметри:
- search_key: значення для пошуку. Наприклад, 42, Коти або I24. діапазон: одновимірний масив для пошуку. Це може бути або один рядок, або один стовпець. Наприклад->A1:A10, A2:D2 тощо. search_type [необов’язковий]: метод пошуку. = 1 (за замовчуванням) знаходить найбільше значення, менше або дорівнює пошуковому_ключу, коли діапазон відсортовано в порядку зростання.
- = 0 знаходить точне значення, коли діапазон не відсортований.
- = -1 знаходить найменше значення, більше або дорівнює пошуковому_ключу, коли діапазон відсортовано в порядку спадання.
Номер рядка або номер стовпця можна знайти за допомогою функції відповідності та використовувати їх у функції індексу, тому, якщо є якісь подробиці про елемент, можна отримати всю інформацію про елемент, знайшовши рядок/стовпець елемента за допомогою відповідності потім вкладення його у функцію індексу.
Довідкова таблиця: Наведену нижче таблицю буде використано як довідкову таблицю для всіх прикладів функції MATCH. Перша клітинка знаходиться на B3 (ЇЖА), а остання діагональна клітинка знаходиться на F10 (180)
приклади: Нижче наведено кілька прикладів функції MATCH-
Випадок 1: Тип пошуку 0, це означає точну відповідність.
Вхідна команда: =MATCH(Південна Індія;C3:C10;0)
Випадок 2: Тип пошуку 1 (за замовчуванням).
Вхідна команда: =MATCH(Південна Індія,C3:C10)
Підручник мовою програмування java
Випадок 3: Тип пошуку -1.
Вхідна команда: =MATCH(Південна Індія,C3:C10,-1)
ПОКАЖЧИК-ВІДПОВІДНІСТЬ Разом
У попередніх прикладах статичні значення рядків і стовпців надавалися у функції INDEX. Припустімо, що немає попередніх відомостей про позицію рядків і стовпців, тоді позицію рядків і стовпців можна надати за допомогою функції MATCH. Це динамічний спосіб пошуку та вилучення значення.
Синтаксис:
=INDEX(Reference Table , [Match(SearchKey,Range,Type)/StaticRowPosition], [Match(SearchKey,Range,Type)/StaticColumnPosition])>
Довідкова таблиця: Буде використана наступна довідкова таблиця. Перша клітинка знаходиться на B3 (ЇЖА), а остання діагональна клітинка знаходиться на F10 (180)
приклад: Припустимо, завдання полягає в тому, щоб знайти вартість Masala Dosa. Відомо, що колонка 3 відображає вартість предметів, але позиція рядка Masala Dosa невідома. Проблему можна розділити на два етапи -
Крок 1: Знайдіть положення Масала Доса за формулою:
=MATCH('Masala Dosa',B3:B10,0)> Тут B3:B10 представляє стовпець Food, а 0 означає точну відповідність. Він поверне номер рядка Masala Dosa.
Крок 2: Знайдіть вартість Masala Dosa. Використовуйте функцію INDEX, щоб знайти вартість Masala Dosa. Підставивши наведений вище запит функції MATCH у функцію INDEX у місці, де потрібна точна позиція Masala Dosa, а номер стовпця вартості дорівнює 3, що вже відомо.
=INDEX(B3:F10, MATCH('Masala Dosa', B3:B10 , 0) ,3)>
Два способи пошуку разом із INDEX-MATCH
У попередньому прикладі позиція стовпця атрибута Cost була жорстко закодована. Отже, це не було повністю динамічно.
Випадок 1: Припустімо, що немає інформації про номер стовпця Cost, тоді його можна отримати за формулою:
=MATCH('Cost',B3:F3,0)> Тут B3:F3 представляє стовпець заголовка.
Випадок 2: Якщо рядок і значення стовпця надаються за допомогою функції MATCH (без надання статичного значення), це називається двостороннім пошуком. Його можна досягти за допомогою формули:
=INDEX(B3:F10, MATCH('Masala Dosa',B3:B10, 0) , MATCH('Cost' ,B3:F3 ,0))>
Пошук зліва
Однією з ключових переваг INDEX і MATCH над функцією VLOOKUP є можливість виконувати пошук зліва. Це означає, що можна отримати позицію рядка елемента за допомогою будь-якого атрибута праворуч, а значення іншого атрибута ліворуч можна отримати.
Наприклад, купимо їжу, вартість якої повинна бути 140 рупій. Побічно ми говоримо, що купуйте Biryani. У цьому прикладі відома вартість 140 рупій, потрібно видобути їжу. Оскільки стовпець Вартість розміщено праворуч від стовпця Їжа. Якщо застосовано VLOOKUP, він не зможе здійснювати пошук у лівій частині стовпця Вартість. Ось чому за допомогою VLOOKUP неможливо отримати назву їжі.
Щоб подолати цей недолік, можна використати функцію INDEX-MATCH.
Крок 1: Витягніть першу позицію рядка Cost 140 Rs за формулою:
=MATCH(140, D3:D10,0)>
Тут D3: D10 представляє стовпець «Вартість», у якому виконується пошук номера рядка «Вартість 140 рупій».
Крок 2: Після отримання номера рядка наступним кроком є використання функції INDEX для отримання назви їжі за формулою:
=INDEX(B3:B10, MATCH(140, D3:D10,0))>
Тут B3:B10 представляє стовпець їжі, а 140 – вартість продукту харчування.
Пошук з урахуванням регістру
Сама по собі функція MATCH не чутлива до регістру. Це означає, що існує назва їжі DHOKLA і функція MATCH використовується з таким пошуковим словом:
- Дхокла
- dhokla
- DhOkLA
All поверне позицію рядка DHOKLA. Однак функцію EXACT можна використовувати з INDEX і MATCH для виконання пошуку з урахуванням верхнього та нижнього регістру.
Точна функція: Функція Excel EXACT порівнює два текстові рядки, враховуючи символи верхнього та нижнього регістру, і повертає TRUE, якщо вони однакові, і FALSE, якщо ні. EXACT чутливий до регістру.
приклади:
- EXACT(DHOKLA,DHOKLA): Це поверне True. EXACT(DHOKLA,Dhokla): Це поверне значення False. EXACT(DHOKLA,dhokla): Це поверне значення False. EXACT(DHOKLA,DhOkLA): Це поверне значення False.
приклад: Скажімо, завдання полягає в пошуку типу їжі Dhokla, але з урахуванням регістру. Це можна зробити за допомогою формули-
=INDEX(C3:C10, MATCH(TRUE , EXACT('Dhokla', B3:B10) ,0))> Тут функція EXACT поверне True, якщо значення в стовпці B3:B10 збігається з Dhokla з тим самим регістром, інакше вона поверне False. Тепер функція MATCH застосовуватиметься до стовпця B3:B10 і шукатиме рядок із точним значенням TRUE. Після цього функція INDEX отримає значення стовпця C3:C10 (стовпець типу їжі) у рядку, який повертає функція MATCH.
Пошук за кількома критеріями
Однією з найскладніших проблем у Excel є пошук на основі кількох критеріїв. Іншими словами, пошук, який відповідає більш ніж одному стовпцю одночасно. У наведеному нижче прикладі функції INDEX і MATCH і логічна логіка використовуються для зіставлення в 3 стовпцях-
- харчування.
- Вартість.
- Кількість.
Щоб отримати загальну вартість.
приклад: Скажімо, завдання полягає в тому, щоб обчислити загальну вартість макаронів де
- Їжа: макарони. Вартість: 60. Кількість: 1 шт.
Отже, у цьому прикладі існує три критерії для виконання відповідності. Нижче наведено кроки для пошуку на основі кількох критеріїв-
Крок 1: Спочатку зіставте стовпчик їжі (B3:B10) із макаронами за формулою:
'PASTA' = B3:B10>
Це перетворить значення B3:B10 (стовпець їжі) як логічні. Це правда, де їжа — це макарони, а інше — хибно.
Крок 2: Після цього зіставте критерії вартості таким чином:
60 = D3:D10>
Це замінить значення D3:D10 (стовпець вартості) на логічні. Це True, де Cost=60, інакше False.
крок 3: Наступним кроком є відповідність третім критеріям, тобто кількість = 1, у такий спосіб:
1 = E3:E10>
Це замінить стовпець E3:E10 (стовпець кількості) на True, де кількість = 1, інакше значення буде False.
крок 4: Помножте результат першого, другого та третього критеріїв. Це буде перетин усіх умов і перетворення логічного значення True/False як 1/0.
крок 5: Тепер результатом буде стовпець із 0 і 1. Тут скористайтеся функцією MATCH, щоб знайти номер рядка стовпців, які містять 1. Оскільки, якщо стовпець має значення 1, це означає, що він задовольняє всім трьом критеріям.
Крок 6: Отримавши номер рядка, використовуйте функцію INDEX, щоб отримати загальну вартість цього рядка.
=INDEX(F3:F10, MATCH(1, ('Pasta'=B3:B10) * (60=D3:D10) * (1=E3:E10) , 0 ))> Тут F3:F10 представляє стовпець загальних витрат.