Для тих з вас, хто добре володіє Excel, швидше за все, ви добре знайомі з VLOOKUP функція. VLOOKUP Функція використовується для пошуку значення в іншій комірці на основі відповідного тексту в одному рядку.
Якщо ви ще не знайомі з VLOOKUP функцію, ви можете ознайомитися з моїм попереднім дописом як користуватися VLOOKUP в Excel.
Зміст
Яким би потужним він не був, VLOOKUP має обмеження щодо того, як структурувати відповідну довідкову таблицю, щоб формула працювала.
Ця стаття покаже вам обмеження, де VLOOKUP не можна використовувати та запровадити іншу функцію в Excel під назвою ІНДЕКС-МАТЧ що може вирішити проблему.
ІНДЕКС ВІДПОВІДАЄ Приклад Excel
Використовуючи наступне приклад електронної таблиці Excel, у нас є список імені власників автомобілів та назви автомобіля. У цьому прикладі ми будемо намагатися схопити Ідентифікатор автомобіля на основі Модель автомобіля вказано під кількома власниками, як показано нижче:
На окремому аркуші називається Тип автомобіля, у нас є проста база даних автомобілів з Посвідчення особи, Модель автомобіля та Колір.
За допомогою цього налаштування таблиці VLOOKUP функція може працювати тільки в тому випадку, якщо дані, які ми хочемо отримати, знаходяться у стовпці праворуч від того, що ми намагаємось знайти (Модель автомобіля поле).
Іншими словами, з цією структурою таблиці, оскільки ми намагаємось зіставити її на основі Модель автомобіля, єдина інформація, яку ми можемо отримати - це Колір (Ні Посвідчення особи як Посвідчення особи стовпець розташований ліворуч від Модель автомобіля колонка.)
Це пояснюється тим, що з VLOOKUP значення підстановки повинно відображатися у першому стовпці, а стовпці підстановки - праворуч. У нашому прикладі жодна з цих умов не виконується.
Доброю новиною є те, ІНДЕКС-МАТЧ зможе допомогти нам у цьому. На практиці це насправді поєднує дві функції Excel, які можуть працювати окремо: ПОКАЗНИК функція і МАТЧ функція.
Однак для цілей цієї статті ми будемо говорити лише про їх поєднання з метою відтворення функції VLOOKUP.
Формула на перший погляд може здатися трохи довгою і лякаючою. Однак, коли ви кілька разів користуєтесь ним, ви вивчите синтаксис напам’ять.
Ось повна формула в нашому прикладі:
= INDEX (CarType! $ A $ 2: $ A $ 5, MATCH (B4, CarType! $ B $ 2: $ B $ 5,0))
Ось розподіл по кожному розділу
= ІНДЕКС ( - “=” позначає початок формули у клітинці та ПОКАЗНИК це перша частина функції Excel, яку ми використовуємо.
Тип автомобіля! $ A $ 2: $ A $ 5 - стовпці на аркуші Тип автомобіля де містяться дані, які ми хотіли б отримати. У цьому прикладі файл Посвідчення особи кожного Модель автомобіля.
МАТЧ ( - Друга частина функції Excel, яку ми використовуємо.
В4 - Клітинка, яка містить текст пошуку, який ми використовуємо (Модель автомобіля).
Тип автомобіля! $ B $ 2: $ B $ 5 - Колонки на аркуші Тип автомобіля з даними, які ми будемо використовувати для порівняння з текстом пошуку.
0)) - Щоб вказати, що текст пошуку має точно збігатися з текстом у відповідному стовпці (тобто Тип автомобіля! $ B $ 2: $ B $ 5). Якщо точне збіг не знайдено, формула повертається #Н/Д.
Примітка: запам’ятайте подвійну закриваючу дужку в кінці цієї функції “))” і коми між аргументами.
Особисто я відійшов від VLOOKUP і тепер використовую INDEX-MATCH, оскільки він здатний робити більше, ніж VLOOKUP.
ІНДЕКС-МАТЧ Функції також мають інші переваги порівняно з VLOOKUP:
- Швидші розрахунки
Коли ми працюємо з великими наборами даних, де сам розрахунок може зайняти багато часу через багато функцій VLOOKUP, Ви виявите, що як тільки ви заміните всі ці формули на INDEX-MATCH, загальний розрахунок буде обчислено швидше.
- Немає необхідності рахувати відносні стовпці
Якщо наша таблиця посилань містить ключовий текст, який ми хочемо шукати у стовпці C. а дані, які нам потрібно отримати, знаходяться у стовпці AQ, нам потрібно буде знати/порахувати, скільки стовпців знаходиться між стовпцем C та стовпцем AQ під час використання VLOOKUP.
За допомогою функцій INDEX-MATCH ми можемо безпосередньо вибрати стовпець індексу (тобто стовпець AQ), куди нам потрібно отримати дані та вибрати стовпець для відповідності (тобто стовпець C).
- Це виглядає більш складним
VLOOKUP сьогодні досить поширене явище, але мало хто знає про спільне використання функцій INDEX-MATCH.
Довший рядок у функції INDEX-MATCH допомагає вам виглядати експертом у роботі зі складними та розширеними функціями Excel. Насолоджуйтесь!
Ласкаво просимо на Help Desk Geek- блог, повний технічних порад від надійних технічних експертів. У нас є тисячі статей та посібників, які допоможуть вам вирішити будь -яку проблему. Наші статті були прочитані понад 150 мільйонів разів з моменту запуску у 2008 році.
Хотіти вразити з друзями та родиною чудовий технічні витівки?
Підпишіться на Help Desk Geek і щодня отримуйте чудові посібники, поради та хитрощі! Ми надсилаємо тільки корисні речі!
Ми також ненавидимо спам, відписуйтесь у будь -який час.
