Якщо ви деякий час читали поради Excel, ви незмінно знаходили того, хто говорить про використання функцій Excel INDEX () & MATCH () замість Excel VLOOKUP. Якщо говорити про себе, то завжди було надто важко спробувати засвоїти ДВІ нові функції одночасно. Але, це крутий фокус. Дайте мені п’ять хвилин, і я спробую пояснити це простою англійською мовою.
30-й другий огляд VLOOKUP
Скажімо, у вас є таблиця записів працівників. Перший стовпець - це номер працівника, а решта - різні дані про працівника. Щоразу, коли у вас на робочому аркуші є номер працівника, ви можете використовувати VLOOKUP, щоб повернути конкретну дату про працівника. Синтаксис - VLOOKUP (значення, діапазон даних, номер колон, FALSE). У програмі Excel сказано: "Перейдіть до діапазону даних. Знайдіть рядок, який має (значення) у першому стовпці діапазону даних. Поверніть (колонка) значення th з цього рядка. Після того, як ви зрозумієте, це дуже просто і потужно.
Проблема
Одного разу у вас виникла ситуація, коли у вас є ім’я працівника, але вам потрібен номер працівника. На наступному зображенні у вас є ім’я в A10 і вам потрібно знайти номер працівника в B10.
Коли ключове поле знаходиться праворуч від даних, які потрібно отримати, VLOOKUP не працюватиме. Якби лише VLOOKUP прийняв -1 як номер стовпця, проблем не було б. Але це не так. Одне загальне рішення - тимчасово вставити новий стовпець A, скопіювати стовпець імен у новий стовпець A, заповнити VLOOKUP, Вставити спеціальні значення, а потім видалити тимчасовий стовпець A. Професіонали Excel можуть, мабуть, робити цей рух уві сні.
Я пропоную вам прийняти виклик і спробувати використати цей одноетапний метод. Так, вам доведеться закріпити формулу на вашій стіні протягом декількох тижнів, але ви теж давно це робили з VLOOKUP, чи не так?
Я думаю, що причина настільки складна в тому, що ви використовуєте дві функції, які, ймовірно, ніколи раніше не використовували. Отже, дозвольте розкласти його на дві частини.
Спочатку функція INDEX (). Це жахливо названа функція. Коли хтось говорить "індекс", це не викликає в моїй свідомості нічого подібного до того, що робить ця функція. Індекс вимагає трьох аргументів.
=INDEX(data range, row number, column number)
Англійською мовою, Excel переходить до діапазону даних і повертає вам значення в перетині (рядка номер) рядка та (номера стовпця) го стовпця. Гей, подумай - це досить просто, так? =INDEX($A$2:$C$6,4,2)дасть вам значення в B5.
Застосовуючи INDEX () до нашої проблеми, ви можете зрозуміти , що повернути номер співробітника з діапазону, ви б використовувати це: =INDEX($A$2:$A$6,?,1). Насправді цей шматок здається настільки тривіальним, що здається марним. Але коли ви замінюєте знак запитання на функцію MATCH (), у вас є рішення.
Ось синтаксис:
=MATCH(Value, Single-column data range, FALSE)
Це говорить Excel: "Шукайте в діапазоні даних і повідомте мені відносний номер рядка, де ви знайдете відповідність (дані). Отже, щоб знайти, який рядок має співробітник в A10, ви б використали =MATCH(A10,$B$2:$B$6,FALSE). Так, це складніше, ніж Index , але це має бути прямо по алеї професіоналів VLOOKUP. Якщо A10 містить "Міллер, Боб", тоді ця МАТЧ поверне, що він знаходиться в 3-му рядку діапазону B2: B6.
Ось воно - функція MATCH () повідомляє функції Index, в який рядок шукати - все готово. Візьміть функцію Index, замініть наш знак запитання на функцію MATCH, і тепер ви можете зробити еквівалент VLOOKUP, коли ключове поле не знаходиться в лівій колонці. Ось функція для використання:
=INDEX($A$2:$A$6,MATCH(A10,$B$2:$B$6,FALSE),1)
Наклейка на моїй стіні насправді відображає її як два рядки. Спочатку я виписав пояснення для MATCH (). Нижче я написав пояснення для INDEX (). Потім я намалював форму воронки між ними, щоб вказати, що функція MATCH () потрапляє до 2-го аргументу функції INDEX ().
Кілька перших разів, коли мені доводилося робити одне з них, у мене виникало спокуса просто забити нову тимчасову колону А там, але замість цього я пройшов через біль. Це швидше і вимагає менше маніпуляцій. Отже, наступного разу, коли ви побажаєте, що можете поставити від’ємне число у функції VLOOKUP, спробуйте цю дивну комбінацію INDEX та MATCH, щоб вирішити ваші проблеми.
