Соответствие индекса: лучшая альтернатива Vlookup

ВПР - одна из тех функций, которые делают Excel отличным. Хотя поначалу многим он может показаться не интуитивным, вскоре он становится второй натурой и незаменим. Некоторые даже находят, что это меняет жизнь. У VLOOKUP есть свои проблемы, во-первых, он читает только слева направо. Если вам нужно найти значение слева от ссылочного столбца, вам придется переупорядочить рабочий лист, чтобы использовать эту функцию.

ВПР также охватывает множество столбцов, хотя на самом деле вы используете только два из них. стандартный синтаксис ссылается только на относительные значения столбцов, если вы измените таблицу, вам снова не повезло, и вам нужно будет немного реорганизовать и переписать функции, что может быть очень утомительным в больших книгах. Это также создает проблемы при копировании функции в другие столбцы.

Однако есть альтернатива - ИНДЕКС + ПОИСКПОЗ. Он не только повторяет функциональность, но и имеет свои преимущества и бонусы. Возможно, вы не слышали об этом раньше (особенно если вы не читали об Excel для развлечения), но к концу этой статьи вы сможете применить это на практике и пожинать плоды.

Разбивка INDEX + MATCH

Многие пользователи упускают из виду то, что функции Excel можно объединить в цепочку и объединить, чтобы сделать что-то еще более мощным, чем составные части. Каждая функция принимает аргумент, если вам нужно немного больше мощности или расширенной функциональности, эти аргументы могут быть сделаны из других функций.

ПОКАЗАТЕЛЬ

= ИНДЕКС (массив; номер строки)

Функция ИНДЕКС принимает пользовательский массив, набор значений, таких как столбец, и возвращает значение ячейки в этой конкретной позиции. Например, у меня есть столбец данных, в котором записаны позиции в гонке, проведенной в какой-то момент в прошлом. Я хочу знать, какой водитель занял третье место, чтобы найти это, я могу просто ввести,

= ИНДЕКС (B2: B6; 3)

и это вернет Расти Шеклфорда.

Просто, но полезно. Теперь у нас есть возможность возвращать значения в массиве.

СООТВЕТСТВИЕ

= ПОИСКПОЗ (значение поиска, массив поиска, тип соответствия)

MATCH () - еще одна простая функция, связанная с функцией ИНДЕКС, которую мы только что рассмотрели. Однако вместо возврата значения он возвращает число, обозначающее относительное положение значения в массиве. ПОИСКПОЗ требует следующих аргументов; значение, массив для поиска и тип соответствия. Тип соответствия обычно равен 0, но зависит от вашего использования. 0 означает, что вам нужно только точное совпадение, 1 или -1 означает, что если нет точного совпадения, вам нужно следующее ближайшее значение.

Используя приведенный выше пример гонки, я знаю гонщика, которого мне нужно найти, но я не знаю его места в итоговой таблице гонок. Поскольку позиция относительна, мне нужно убедиться, что я охватываю все результаты, начиная с первой позиции.

= ПОИСКПОЗ («Килгор Форель»; B2: B6; 0)

Это вернет значение 4. Поскольку мы стартовали сверху, мы знаем, что Траут финишировал четвертым в этой конкретной гонке.

ИНДЕКС МАТЧ

Может быть, вы видите, куда мы движемся сейчас, или, может быть, вы пропустили вперед, чтобы добраться до хороших вещей. Используя ИНДЕКС + ПОИСКПОЗ, мы можем объединить эти две простые функции, чтобы сделать что-то похожее, но более гибкое, чем наша верная ВПР. Не волнуйтесь, если вам нужно прочитать это несколько раз, поначалу это может быть немного сложно. Как только вы поймете, что происходит, очень легко восстановить это из компонентов.

ИНДЕКС возвращает определенное значение. Это основная функция ВПР при заданном наборе параметров; вы можете быстро найти нужное значение. Теперь единственное, что нам не хватает, - это способ найти позицию строки. Если вам нужно найти это вручную, вы потеряете полезность функции. Используя MATCH во втором аргументе, мы можем взять его результат, относительное положение, и передать его в функцию Index, решив эту проблему. Теперь у нас есть что-то, что делает то же самое, что и ВПР, только с немного большим набором текста.

Применение функции на практике

Используя приведенную ниже таблицу, нам интересно выяснить, какой тип продукта связан с конкретным идентификатором продукта.

Чтобы использовать INDEX MATCH, мы начинаем с INDEX, поскольку в конечном итоге мы хотим, чтобы возвращалось конкретное значение, а не позиция. Внутри ИНДЕКС мы включаем ПОИСКПОЗ, чтобы вернуть позицию для нашей функции ИНДЕКС.

= ИНДЕКС (A2: A6; ПОИСКПОЗ (8316; B2: B6,0))

Что здесь происходит, так это то, что я собираюсь искать в столбце A значение строки в столбце B, предоставленное функцией Match.

Внутри функции сопоставления я указываю, что мне нужно просмотреть коды идентификатора продукта в B2: B6 и отправить обратно позицию значения 8316. В этом случае оно находится в позиции 4, что эквивалентно записи

= ИНДЕКС (A2: A6; 4)

Excel запускает функцию и находит позицию в массиве указанного идентификационного номера. Он занимает эту позицию и ищет ту же строку в нашем столбце A, возвращая тип грузовика для конкретного идентификатора. Итак, в конце концов, мы получаем те же результаты с немного большей гибкостью во входных данных.

Перки функции

Сила INDEX + MATCH проистекает из меньшего выбора данных. Если у вас 15 столбцов, но вы используете только числа 1 и 15, зачем втягивать все остальное? Это может помочь ускорить выполнение больших наборов данных.

Вторая расширенная функциональность заключается в том, что Index может читать влево или вправо, независимо от того, где вы помещаете столбцы, INDEX безразлично, находится ли он слева или справа от вашего исходного массива, в отличие от VLOOKUP. Если вы снова посмотрите на приведенный выше пример, то это именно то, что мы сделали, работая справа налево. Эта функция также может служить в качестве проверки качества, поскольку вы должны вручную указать два столбца, которые вам нужны, а не вводить относительный номер столбца где-нибудь в пределах вашего выбора.

Нужно больше визуальных эффектов? Посмотрите запись экрана примера соответствия индекса, где мы вставляем конечные URL-адреса в шаблон копии объявления.

Заключение

Поиграйте с INDEX + MATCH. Это очень мощная функция, которая может улучшить ваши возможности поиска. Как только он щелкнет, я уверен, что вы будете проданы и никогда не оглянетесь назад. Когда вы пробуете что-то новое, всегда нужно научиться чему-то новому, поэтому постарайтесь использовать Index Match на некоторое время. Поначалу это будет неудобно, но вскоре вы почувствуете преимущества.

———-
Сообщение обновлено Дженной Келли (предыдущая дата публикации: 03.02.14)