В Excel много функций - их около 450+.
И многие из них просто потрясающие. Объем работы, которую вы можете выполнить с помощью нескольких формул, все еще удивляет меня (даже после того, как вы использовали Excel более 10 лет).
И среди всех этих удивительных функций выделяется комбинация функций ИНДЕКС ПОИСКПОЗ.
Я большой поклонник комбинации INDEX MATCH, и я неоднократно разъяснял ее.
Я даже написал статью о сопоставлении индекса и ВПР, которая вызвала небольшую дискуссию (вы можете проверить раздел комментариев, чтобы увидеть фейерверк).
И сегодня я пишу эту статью, посвященную исключительно Index Match, чтобы показать вам несколько простых и сложных сценариев, в которых вы можете использовать эту мощную комбинацию формул и выполнить работу.
Примечание: В Excel есть и другие формулы поиска, такие как ВПР и ГПР, и они великолепны. Многие люди считают, что ВПР проще в использовании (и это тоже правда). Я считаю, что INDEX MATCH - лучший вариант во многих случаях. Но так как людям это сложно, к нему все реже привыкают. Поэтому я пытаюсь упростить его с помощью этого урока.Теперь, прежде чем я покажу вам, как комбинация ИНДЕКС ПОИСКПОЗ меняет мир аналитиков и исследователей данных, позвольте мне сначала познакомить вас с отдельными частями - функциями ИНДЕКС и ПОИСКПОЗ.
Функция ИНДЕКС: поиск значения на основе координат
Самый простой способ понять, как работает функция Index, - это представить ее как спутник GPS.
Как только вы сообщите спутнику координаты широты и долготы, он будет точно знать, куда идти и найти это место.
Таким образом, несмотря на ошеломляющее количество комбинаций широты и долготы, спутник точно знает, где искать.
Я быстро нашел место работы, и вот что у меня получилось.
Во всяком случае, хватит географии.
Точно так же, как спутнику нужны координаты широты и долготы, функции ИНДЕКС в Excel потребуется номер строки и столбца, чтобы знать, о какой ячейке вы говорите.
И это функция Excel INDEX в кратком изложении.
Так что позвольте мне дать вам определение простыми словами.
Функция ИНДЕКС будет использовать номер строки и номер столбца, чтобы найти ячейку в заданном диапазоне и вернуть в ней значение.
Сама по себе ИНДЕКС - очень простая функция, не имеющая никакой полезности. В конце концов, в большинстве случаев вы вряд ли знаете номера строк и столбцов.
Но…
Тот факт, что вы можете использовать его с другими функциями (подсказка: ПОИСКПОЗ), которые могут найти номер строки и номер столбца, делает ИНДЕКС чрезвычайно мощной функцией Excel.
Ниже приведен синтаксис функции ИНДЕКС:
= ИНДЕКС (массив; номер_строки; [номер_столбца]) = ИНДЕКС (массив; номер_строки; [номер_столбца]; [номер_площади])
- множество - а диапазон ячеек или константа массива.
- row_num - номер строки, из которой должно быть извлечено значение.
- [col_num] - номер столбца, из которого должно быть извлечено значение. Хотя это необязательный аргумент, но если row_num не указан, его необходимо указать.
- [area_num] - (Необязательно) Если аргумент массива состоит из нескольких диапазонов, это число будет использоваться для выбора ссылки из всех диапазонов.
Функция ИНДЕКС имеет 2 синтаксиса (только FYI).
Первый используется в большинстве случаев. Второй используется только в сложных случаях (например, при трехстороннем поиске), который мы рассмотрим в одном из примеров далее в этом руководстве.
Но если вы новичок в этой функции, просто запомните первый синтаксис.
Ниже приведено видео, в котором объясняется, как использовать функцию ИНДЕКС.
Функция MATCH: находит позицию по искомому значению
Возвращаясь к моему предыдущему примеру долготы и широты, ПОИСКПОЗ - это функция, которая может найти эти позиции (в мире электронных таблиц Excel).
Проще говоря, функция ПОИСКПОЗ в Excel может найти положение ячейки в диапазоне.
И на каком основании он будет определять положение ячейки?
На основе поискового значения.
Например, если у вас есть список, показанный ниже, и вы хотите найти в нем позицию имени «Отметить», то вы можете использовать функцию ПОИСКПОЗ.
Функция возвращает 3, так как это позиция ячейки с именем Mark.
Функция ПОИСКПОЗ начинает поиск сверху вниз для поискового значения (которым является «Отметка») в указанном диапазоне (в данном примере это A1: A9). Как только он находит имя, он возвращает позицию в этом конкретном диапазоне.
Ниже приведен синтаксис функции ПОИСКПОЗ в Excel.
= ПОИСКПОЗ (искомое_значение, искомое_массив, [тип_соответствия])
- lookup_value - Значение, для которого вы ищете совпадение в lookup_array.
- lookup_array - Диапазон ячеек, в котором вы ищете lookup_value.
- [match_type] - (Необязательно) Это указывает, как Excel должен искать подходящее значение. Может принимать три значения: -1, 0 или 1.
Общие сведения об аргументе типа соответствия в функции MATCH
Есть еще одна вещь, которую вам нужно знать о функции ПОИСКПОЗ, - это то, как она просматривает данные и находит положение ячейки.
Третий аргумент функции ПОИСКПОЗ может быть 0, 1 или -1.
Ниже приводится объяснение того, как работают эти аргументы:
- 0 - это будет искать точное совпадение значения. Если найдено точное совпадение, функция ПОИСКПОЗ вернет позицию ячейки. В противном случае он вернет ошибку.
- 1 - находит наибольшее значение, которое меньше или равно искомому значению. Чтобы это работало, диапазон данных необходимо отсортировать в порядке возрастания.
- -1 - находит наименьшее значение, которое больше или равно искомому значению. Чтобы это работало, диапазон данных необходимо отсортировать в порядке убывания.
Ниже приведено видео, в котором объясняется, как использовать функцию ПОИСКПОЗ (вместе с аргументом типа соответствия).
Подводя итог и выразив это простыми словами:
- ИНДЕКС требует положение ячейки (номер строки и столбца) и дает значение ячейки.
- MATCH находит позицию, используя поисковое значение.
Давайте объединим их, чтобы создать электростанцию (ИНДЕКС + МАТЧ)
Теперь, когда у вас есть базовое представление о том, как функции ИНДЕКС и ПОИСКПОЗ работают по отдельности, давайте объединим эти две функции и узнаем обо всех чудесных вещах, на которые она способна.
Чтобы лучше понять это, у меня есть несколько примеров, в которых используется комбинация INDEX MATCH.
Я начну с простого примера, а затем покажу вам несколько сложных вариантов использования.
Щелкните здесь, чтобы загрузить файл примера
Пример 1: простой поиск с использованием комбинации INDEX MATCH
Давайте сделаем простой поиск с помощью INDEX / MATCH.
Ниже представлена таблица, в которой у меня есть оценки за десять студентов.
По этой таблице я хочу найти оценки для Джима.
Ниже приведена формула, с помощью которой можно легко это сделать:
= ИНДЕКС ($ A $ 2: $ B $ 11, ПОИСКПОЗ ("Джим", $ A $ 2: $ A $ 11,0), 2)
Если вы думаете, что это легко сделать с помощью функции ВПР, вы правы! Это не лучшее использование потрясающих возможностей INDEX MATCH. Несмотря на то, что я фанат INDEX MATCH, это немного сложнее, чем VLOOKUP. Если все, что вам нужно сделать, это получить данные из столбца справа, я рекомендую вам использовать ВПР.
Причина, по которой я показал этот пример, который также легко можно сделать с помощью ВПР, заключается в том, чтобы показать вам, как работает INDEX MATCH в простой настройке.
Теперь позвольте мне показать преимущество INDEX MATCH.
Предположим, у вас есть те же данные, но они хранятся не в столбцах, а в строках (как показано ниже).
Знаете что, вы все еще можете использовать комбинацию ИНДЕКС ПОИСКПОЗ, чтобы получить оценки Джима.
Ниже приведена формула, которая даст вам результат:
= ИНДЕКС ($ B $ 1: $ K $ 2,2; ПОИСКПОЗ («Джим», $ B $ 1: $ K $ 1,0))
Обратите внимание, что вам нужно изменить диапазон и переключить части строки / столбца, чтобы эта формула работала и для горизонтальных данных.
Это невозможно сделать с помощью ВПР, но вы все равно можете легко сделать это с помощью ГПР.
Комбинация INDEX MATCH может легко обрабатывать как горизонтальные, так и вертикальные данные.Щелкните здесь, чтобы загрузить файл примера
Пример 2: поиск слева
Это встречается чаще, чем вы думаете.
Часто вам может потребоваться получить данные из столбца, который находится слева от столбца, который имеет значение подстановки.
Что-то вроде того, что показано ниже:
Чтобы узнать о продажах Майкла, вам нужно выполнить поиск слева.
Если вы думаете о ВПР, позвольте мне остановиться прямо сейчас.
Функция ВПР не предназначена для поиска и получения значений слева.
Можете ли вы по-прежнему делать это с помощью ВПР?
Да, ты можешь!
Но это может превратиться в длинную и уродливую формулу.
Поэтому, если вы хотите выполнить поиск и получить данные из столбцов слева, вам лучше использовать комбинацию INDEX MATCH.
Ниже приведена формула, по которой будет получен номер продаж Майкла:
= ИНДЕКС ($ A $ 2: $ C $ 11; ПОИСКПОЗ ("Майкл"; C2: C11,0); 2)
Пример 3: двусторонний поиск
До сих пор мы видели примеры, в которых мы хотели получить данные из столбца, смежного со столбцом, имеющим значение подстановки.
Но в реальной жизни данные часто занимают несколько столбцов.
INDEX MATCH может легко обрабатывать двусторонний поиск.
Ниже представлены данные об оценках учащихся по трем различным предметам.
Если вы хотите быстро получить оценки учащегося по всем трем предметам, вы можете сделать это с помощью INDEX MATCH.
Приведенная ниже формула даст вам оценки Джиму для всех трех предметов (скопируйте и вставьте в одну ячейку и перетащите, чтобы заполнить другие ячейки, или скопируйте и вставьте в другие ячейки).
= ИНДЕКС ($ B $ 2: $ D $ 11, ПОИСКПОЗ ($ F $ 3, $ A $ 2: $ A $ 11,0), ПОИСКПОЗ (G $ 2, $ B $ 1: $ D $ 1,0))
Позвольте мне также быстро пояснить эту формулу.
В формуле ИНДЕКС в качестве диапазона используется B2: D11.
Первое ПОИСКПОЗ использует имя (Джим в ячейке F3) и выбирает его позицию в столбце имен (A2: A11). Он становится номером строки, из которой необходимо извлечь данные.
Вторая формула ПОИСКПОЗ использует имя субъекта (в ячейке G2), чтобы получить позицию этого конкретного имени субъекта в B1: D1. Например, математика - 1, физика - 2, а химия - 3.
Поскольку эти позиции ПОИСКПОЗ передаются в функцию ИНДЕКС, она возвращает результат на основе имени ученика и имени предмета.
Эта формула является динамической, что означает, что если вы измените имя учащегося или имена субъектов, она все равно будет работать и получать правильные данные.
Одним из замечательных преимуществ использования ИНДЕКС / ПОИСКПОЗ является то, что даже если вы поменяете имена испытуемых местами, это будет по-прежнему давать правильный результат.Пример 4: Значение подстановки из нескольких столбцов / критериев
Предположим, у вас есть набор данных, показанный ниже, и вы хотите получить отметки для «Mark Long».
Поскольку данные находятся в двух столбцах, я не могу найти Марка и получить данные.
Если я сделаю это таким образом, я собираюсь получить данные оценок для Марка Фроста, а не для Марка Лонга (потому что функция ПОИСКПОЗ даст мне результат для МАРКИ, которую она встречает).
Один из способов сделать это - создать вспомогательный столбец и объединить имена. Когда у вас есть вспомогательный столбец, вы можете использовать ВПР и получать данные о отметках.
Если вы хотите узнать, как это сделать, прочтите это руководство по использованию ВПР с несколькими критериями.
Но с комбинацией ИНДЕКС / ПОИСКПОЗ вам не нужен вспомогательный столбец. Вы можете создать формулу, которая обрабатывает несколько критериев в самой формуле.
Приведенная ниже формула даст результат.
= ИНДЕКС ($ C $ 2: $ C $ 11, ПОИСКПОЗ ($ E $ 3 & "|" & $ F $ 3, $ A $ 2: A11 & "|" & $ B $ 2: $ B $ 11,0))
Позвольте мне быстро объяснить, что делает эта формула.
Часть формулы MATCH объединяет поисковое значение (Mark и Long), а также весь поисковый массив. Когда $ A $ 2: A11 & ”|” & $ B $ 2: $ B $ 11 используется в качестве массива поиска, он фактически проверяет значение поиска по объединенной строке имени и фамилии (разделенной вертикальной чертой).
Это гарантирует, что вы получите правильный результат без использования вспомогательных столбцов.
Вы также можете выполнить этот вид поиска (где есть несколько столбцов / критериев) с помощью ВПР, но вам нужно использовать вспомогательный столбец. Комбинация INDEX MATCH позволяет сделать это немного проще без каких-либо вспомогательных столбцов.Пример 5: Получить значения из всей строки / столбца
В приведенных выше примерах мы использовали функцию ИНДЕКС для получения значения из определенной ячейки. Вы указываете номер строки и столбца, и он возвращает значение в этой конкретной ячейке.
Но вы можете сделать больше.
Вы также можете использовать функцию ИНДЕКС для получения значений из всей строки или столбца.
А чем это может быть полезно, спросите вы!
Предположим, вы хотите узнать общий балл Джима по всем трем предметам.
Вы можете использовать функцию ИНДЕКС, чтобы сначала получить все оценки Джима, а затем использовать функцию СУММ, чтобы получить общую сумму.
Посмотрим, как это сделать.
Ниже приведены оценки всех студентов по трем предметам.
Приведенная ниже формула даст мне общий балл Джима по всем трем предметам.
= СУММ (ИНДЕКС ($ B $ 2: $ D $ 11, ПОИСКПОЗ ($ F $ 4, $ A $ 2: $ A $ 11,0), 0))
Позвольте мне объяснить, как работает эта формула.
Хитрость здесь в том, чтобы использовать 0 в качестве номера столбца.
Когда вы используете 0 в качестве номера столбца в функции ИНДЕКС, он вернет все значения строки. Точно так же, если вы используете 0 в качестве номера строки, он вернет все значения в столбце.
Таким образом, приведенная ниже часть формулы возвращает массив значений - {97, 70, 73}
ИНДЕКС ($ B $ 2: $ D $ 11, ПОИСКПОЗ ($ F $ 4, $ A $ 2: $ A $ 11,0), 0)
Если вы просто введете эту формулу выше в ячейку Excel и нажмете клавишу ВВОД, вы увидите # ЗНАЧ! ошибка. Это потому, что он возвращает не одно значение, а массив значений.
Но не волнуйтесь, массив значений все еще существует. Вы можете проверить это, выбрав формулу и нажав клавишу F9. Он покажет вам результат формулы, которая в данном случае представляет собой массив из трех значений - {97, 70, 73}
Теперь, если вы заключите эту формулу ИНДЕКС в функцию СУММ, она даст вам сумму всех оценок, поставленных Джимом.
Вы также можете использовать то же самое, чтобы получить высшую, низшую и среднюю оценки Джима.
Так же, как мы сделали это для студента, вы также можете сделать это для предмета. Например, если вы хотите получить средний балл по предмету, вы можете оставить номер строки равным 0 в формуле ИНДЕКС, и он предоставит вам все значения столбцов этого предмета.
Щелкните здесь, чтобы загрузить файл примера
Пример 6. Найдите оценку учащегося (метод приблизительного сопоставления)
До сих пор мы использовали формулу MATCH, чтобы получить точное соответствие искомого значения.
Но вы также можете использовать его для приблизительного совпадения.
Что, черт возьми, такое приблизительное совпадение?
Позволь мне объяснить.
Когда вы ищете такие вещи, как имена или идентификаторы, вы ищете точное совпадение. Но иногда вам нужно знать диапазон, в котором лежат ваши значения поиска. Обычно так бывает с числами.
Например, будучи классным руководителем, вы можете узнать, какая оценка у каждого ученика по предмету, и оценка будет определяться на основе этой оценки.
Ниже приведен пример, в котором я хочу поставить оценку для всех учеников, и оценка будет решена на основе таблицы справа.
Таким образом, если ученик получает меньше 33 баллов, он получает оценку F, а если он / она получает меньше 50, но больше 33, это E и так далее.
Ниже приведена формула, которая сделает это.
= ИНДЕКС ($ F $ 3: $ F $ 8; ПОИСКПОЗ (B2; $ E $ 3: $ E $ 8,1); 1)
Позвольте мне объяснить, как работает эта формула.
В функции ПОИСКПОЗ мы использовали 1 в качестве аргумента [match_type]. Этот аргумент вернет наибольшее значение, которое меньше или равно искомому значению.
Это означает, что формула MATCH проходит через диапазон меток, и как только она находит диапазон меток, который равен или меньше значения меток поиска, она останавливается на этом и возвращает свою позицию.
Таким образом, если значение метки поиска равно 20, функция ПОИСКПОЗ вернет 1, а если оно равно 85, она вернет 5.
И функция ИНДЕКС использует это значение позиции для получения оценки.
ВАЖНО: Чтобы это работало, ваши данные должны быть отсортированы в порядке возрастания. В противном случае вы можете получить неверные результаты.Обратите внимание, что вышеуказанное также можно сделать с помощью приведенной ниже формулы ВПР:
= ВПР (B2; $ E $ 3: $ F $ 8,2; ИСТИНА)
Но функция ПОИСКПОЗ может пойти дальше, когда дело доходит до приблизительного совпадения.
Вы также можете иметь данные по убыванию и можете использовать комбинацию ИНДЕКС ПОИСКПОЗ, чтобы найти результат. Например, если я изменю порядок в таблице оценок (как показано ниже), я все равно смогу найти оценки учащихся.
Для этого все, что мне нужно сделать, это изменить аргумент [match_type] на -1.
Ниже приведена формула, которую я использовал:
= ИНДЕКС ($ F $ 3: $ F $ 8; ПОИСКПОЗ (B2; $ E $ 3: $ E $ 8; -1); 1)ВПР также может приблизить совпадение, но только если данные отсортированы в порядке возрастания (но не работает, если данные отсортированы в порядке убывания).
Пример 7: поиск с учетом регистра
До сих пор все поиски, которые мы выполняли, не учитывали регистр.
Это означает, что неважно, было ли искомое значение Джимом, Джимом или Джимом. Вы получите тот же результат.
Но что, если вы хотите, чтобы поиск был чувствительным к регистру.
Обычно это происходит, когда у вас есть большие наборы данных и возможность повторения или различных имен / идентификаторов (с той лишь разницей, что это так)
Например, предположим, что у меня есть следующий набор данных об учениках, где есть два ученика с именем Джим (с той лишь разницей, что один введен как Джим, а другой - как Джим).
Обратите внимание, что есть два студента с одинаковым именем - Джим (ячейка A2 и A5).
Поскольку обычный поиск не работает, вам нужно выполнять поиск с учетом регистра.
Ниже приведена формула, которая даст вам правильный результат. Поскольку это формула массива, вам нужно использовать Control + Shift + Enter.
= ИНДЕКС ($ B $ 2: $ B $ 11; ПОИСКПОЗ (ИСТИНА; ТОЧНО (D3; A2: A11); 0); 1)
Позвольте мне объяснить, как работает эта формула.
Функция EXACT проверяет точное совпадение искомого значения (в данном случае это «jim»). Он перебирает все имена и возвращает ЛОЖЬ, если совпадение не совпадает, и ИСТИНА, если совпадение.
Таким образом, вывод функции EXACT в этом примере - {ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ИСТИНА; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ}
Обратите внимание, что есть только одно ИСТИНА, когда функция ТОЧНАЯ нашла идеальное совпадение.
Затем функция ПОИСКПОЗ находит позицию ИСТИНА в массиве, возвращаемом функцией ТОЧНОСТЬ, которая в этом примере равна 4.
Как только у нас есть позиция, функция ИНДЕКС использует ее для поиска меток.
Пример 8: Найдите ближайшее совпадение
Давайте теперь немного продвинемся.
Предположим, у вас есть набор данных, в котором вы хотите найти человека, у которого опыт работы наиболее близок к требуемому (указан в ячейке D2).
Хотя формулы поиска не предназначены для этого, вы можете комбинировать их с другими функциями (такими как MIN и ABS), чтобы сделать это.
Ниже приведена формула, которая найдет человека с опытом, наиболее близким к требуемому, и вернет имя человека. Обратите внимание, что опыт должен быть самым близким (который может быть как меньше, так и больше).
= ИНДЕКС ($ A $ 2: $ A $ 15, ПОИСКПОЗ (МИН (ABS (D2-B2: B15)), ABS (D2- $ B $ 2: $ B $ 15), 0))
Поскольку это формула массива, вам нужно использовать Control + Shift + Enter.
Уловка в этой формуле состоит в том, чтобы изменить значение поиска и массив поиска, чтобы найти минимальную разницу в опыте между требуемыми и фактическими значениями.
Прежде чем я объясню формулу, давайте разберемся, как это сделать вручную.
Вы пройдете по каждой ячейке в столбце B и найдете разницу в опыте между тем, что требуется, и тем, что есть у человека. Когда у вас есть все различия, вы найдете минимальное и получите имя этого человека.
Именно это мы и делаем с этой формулой.
Позволь мне объяснить.
Значение поиска в формуле MATCH - MIN (ABS (D2-B2: B15)).
Эта часть дает вам минимальную разницу между данным опытом (который составляет 2,5 года) и всем остальным опытом. В этом примере он возвращает 0,3.
Обратите внимание, что я использовал ABS, чтобы убедиться, что ищу самое близкое (которое может быть больше или меньше заданного опыта).
Теперь это минимальное значение становится нашим значением поиска.
Массив поиска в функции ПОИСКПОЗ - это ABS (D2- $ B $ 2: $ B $ 15).
Это дает нам массив чисел, из которого вычтено 2,5 (необходимый опыт).
Итак, теперь у нас есть значение поиска (0,3) и массив поиска ({6,8; 0,8; 19,5; 21,8; 14,5; 11,2; 0,3; 9,2; 2; 9,8; 14,8; 0,4; 23,8; 2,9}).
Функция ПОИСКПОЗ находит в этом массиве позицию 0,3, которая также является позицией имени человека, имеющего самый близкий опыт.
Этот номер позиции затем используется функцией ИНДЕКС для возврата имени человека.
Связанное чтение: Найти ближайшее совпадение в Excel (примеры с использованием формул поиска)
Щелкните здесь, чтобы загрузить файл примера
Пример 9: Использование INDEX MATCH с подстановочными знаками
Если вы хотите найти значение при частичном совпадении, вам необходимо использовать подстановочные знаки.
Например, ниже представлен набор данных с названием компании и ее рыночной капитализацией, и вы хотите получить рыночную капитализацию. данные для трех компаний справа.
Поскольку это не точные совпадения, в этом случае вы не сможете выполнять регулярный поиск.
Но вы все равно можете получить нужные данные, используя звездочку (*), которая является подстановочным знаком.
Ниже приведена формула, которая предоставит вам данные путем сопоставления названий компаний из основного столбца и получения для них значения рыночной капитализации.
= ИНДЕКС ($ B $ 2: $ B $ 10, ПОИСКПОЗ (D2 & ”*”, $ A $ 2: $ A $ 10,0), 1)
Позвольте мне объяснить, как работает эта формула.
Поскольку нет точного совпадения значений поиска, я использовал D2 & ”*” в качестве значения поиска в функции ПОИСКПОЗ.
Звездочка - это подстановочный знак, который представляет любое количество символов. Это означает, что Apple * в формуле будет означать любую текстовую строку, которая начинается со слова Apple и может иметь любое количество символов после него.
Так когда Яблоко* используется в качестве значения поиска, и формула MATCH ищет его в столбце A, она возвращает позицию «Apple Inc.», поскольку она начинается со слова Apple.
Вы также можете использовать подстановочные знаки для поиска текстовых строк, между которыми находится значение поиска. Например, если вы используете * Apple * в качестве значения поиска, он найдет любую строку, в которой есть слово «яблоко».
Примечание: этот метод хорошо работает, когда у вас есть только один экземпляр соответствия. Но если у вас есть несколько экземпляров соответствия (например, Apple Inc и Apple Corporation, то функция ПОИСКПОЗ вернет позицию только первого совпадающего экземпляра.
Пример 10: трехсторонний поиск
Это расширенное использование INDEX MATCH, но я все же расскажу о нем, чтобы показать вам силу этой комбинации.
Помните, я сказал, что функция ИНДЕКС имеет два синтаксиса:
= ИНДЕКС (массив; номер_строки; [номер_столбца]) = ИНДЕКС (массив; номер_строки; [номер_столбца]; [номер_площади])
Пока что во всех наших примерах мы использовали только первый.
Но для трехстороннего поиска вам нужно использовать второй синтаксис.
Позвольте мне сначала объяснить, что означает трехсторонний вид.
При двустороннем поиске мы используем формулу ИНДЕКС ПОИСКПОЗ, чтобы получить оценки, когда у нас есть имя учащегося и имя предмета. Например, получение оценок Джима в математике - это двусторонний поиск.
Трехсторонний вид добавит ему еще одного измерения. Например, предположим, что у вас есть набор данных, показанный ниже, и вы хотите узнать оценку Джима по математике на промежуточном экзамене, тогда это будет трехсторонний поиск.
Ниже приведена формула, которая даст результат.
= ИНДЕКС (($ B $ 3: $ D $ 7, $ B $ 11: $ D $ 15, $ B $ 19: $ D $ 23), ПОИСКПОЗ ($ F $ 5, $ A $ 3: $ A $ 7,0), ПОИСКПОЗ (G $ 4 , $ B $ 2: $ D $ 2,0), (IF (G $ 3 = "Unit Test", 1, IF (G $ 3 = "Mid Term", 2,3))))
Приведенная выше формула проверяет три вещи - имя студента, предмет и экзамен. Найдя нужное значение, он возвращает его в ячейку.
Позвольте мне объяснить, как работает эта формула, разбив ее на части.
- массив - ($ B $ 3: $ D $ 7, $ B $ 11: $ D $ 15, $ B $ 19: $ D $ 23): Вместо использования одного массива в данном случае я использовал три массива в скобках.
- row_num - ПОИСКПОЗ ($ F $ 5, $ A $ 3: $ A $ 7,0): Функция ПОИСКПОЗ используется для поиска позиции имени учащегося в ячейке $ F $ 5 в списке имени учащегося.
- col_num - ПОИСКПОЗ (G $ 4, $ B $ 2: $ D $ 2,0): Функция ПОИСКПОЗ используется для поиска позиции имени субъекта в ячейке $ B $ 2 в списке имен субъектов.
- [area_num] - ЕСЛИ (G $ 3 = «Модульный тест», 1, IF (G $ 3 = «Среднесрочный период», 2,3)): Значение номера области сообщает функции ИНДЕКС, какой из трех массивов использовать для выборки значения. Если экзамен представляет собой единичный термин, функция ЕСЛИ вернет 1, а функция ИНДЕКС будет использовать первый массив для получения значения. Если экзамен среднесрочный, формула ЕСЛИ вернет 2, в противном случае - 3.
Это продвинутый пример использования INDEX MATCH, и вы вряд ли найдете ситуацию, когда вам придется его использовать. Но все же полезно знать, на что способны формулы Excel.
Щелкните здесь, чтобы загрузить файл примера
Почему INDEX / MATCH лучше, чем VLOOKUP?
Либо это?
Да, в большинстве случаев.
Я представлю свой случай через некоторое время.
Но прежде чем я это сделаю, позвольте мне сказать вот что - ВПР - чрезвычайно полезная функция, и мне она нравится. Он может многое делать в Excel, и я сам время от времени им пользуюсь. Сказав это, это не значит, что не может быть ничего лучше, а INDEX / MATCH (с большей гибкостью и функциональностью) лучше.
Поэтому, если вы хотите выполнить базовый поиск, вам лучше использовать ВПР.
ИНДЕКС / МАТЧ - это ВПР на стероидах. И как только вы изучите INDEX / MATCH, вы всегда можете предпочесть его использование (особенно из-за его гибкости).
Не вдаваясь в подробности, позвольте мне быстро объяснить, почему INDEX / MATCH лучше, чем VLOOKUP.
ИНДЕКС / ПОИСКПОЗ может смотреть влево (а также вправо) от искомого значения
Я рассмотрел это в одном из приведенных выше примеров.
Если у вас есть значение, которое находится слева от значения поиска, вы не можете сделать это с помощью ВПР.
По крайней мере, не только с помощью ВПР.
Да, вы можете комбинировать ВПР с другими формулами и делать это, но это становится сложным и беспорядочным.
INDEX / MATCH, с другой стороны, предназначен для поиска везде (будь то влево, вправо, вверх или вниз)
INDEX / MATCH может работать с вертикальными и горизонтальными диапазонами
Опять же, при полном уважении к ВПР, он не предназначен для этого.
В конце концов, V в VLOOKUP означает вертикальное.
ВПР может проходить только по вертикальным данным, в то время как ИНДЕКС / ПОИСКПОЗ может проходить по данным как по вертикали, так и по горизонтали.
Конечно, есть функция ГПР для горизонтального просмотра, но тогда это не ВПР… верно?
Мне нравится, что комбо INDEX MATCH достаточно гибкое, чтобы работать как с вертикальными, так и с горизонтальными данными.
ВПР не работает с данными по убыванию
Когда дело доходит до приблизительного совпадения, ВПР и ИНДЕКС / ПОИСКПОЗ находятся на одном уровне.
Но INDEX MATCH имеет значение, поскольку он также может обрабатывать данные в порядке убывания.
Я показываю это в одном из примеров в этом руководстве, где нам нужно найти оценку учащихся на основе таблицы оценок. Если таблица отсортирована в порядке убывания, функция ВПР не будет работать (но ПОИСКПОЗ будет).
INDEX / MATCH может быть немного быстрее
Я буду честен. Я сам не проводил этот тест.
Я полагаюсь на мудрость мастера Excel - Чарли Кида.
Разница в скорости VLOOKUP и INDEX / MATCH едва заметна, когда у вас небольшие наборы данных. Но если у вас тысячи строк и много столбцов, это может стать решающим фактором.
В своей статье Чарли Кид утверждает:
«В худшем случае метод INDEX-MATCH работает примерно так же быстро, как VLOOKUP; в лучшем случае это намного быстрее ".
ИНДЕКС / ПОИСКПОЗ не зависит от фактического положения столбца
Если у вас есть набор данных, показанный ниже, когда вы получаете оценку Джима по физике, вы можете сделать это с помощью ВПР.
И для этого вы можете указать номер столбца как 3 в ВПР.
Все в порядке.
Но что, если я удалю столбец Math.
В этом случае формула ВПР нарушится.
Почему? - Поскольку было жестко запрограммировано использование третьего столбца, и когда я удаляю столбец между ними, третий столбец становится вторым столбцом.
В этом случае лучше использовать INDEX / MATCH, так как вы можете сделать номер столбца динамическим с помощью MATCH. Поэтому вместо номера столбца он проверяет имя субъекта и использует его для возврата номера столбца.
Конечно, вы можете сделать это, объединив VLOOKUP с MATCH, но если вы все равно комбинируете, почему бы не сделать это с помощью INDEX, который намного более гибок.
При использовании INDEX / MATCH вы можете безопасно вставлять / удалять столбцы в своем наборе данных.
Несмотря на все эти факторы, есть причина, по которой ВПР так популярна.
И это серьезная причина.
ВПР проще в использовании
ВПР принимает не более четырех аргументов. Если вы можете осмыслить эти четыре, то все готово.
А поскольку большинство базовых вариантов поиска также обрабатываются с помощью ВПР, она быстро стала самой популярной функцией Excel.
Я называю это королем функций Excel.
С другой стороны, ИНДЕКС / ПОИСКПОЗ немного сложнее использовать. У вас может возникнуть зависание, если вы начнете его использовать, но для новичка VLOOKUP гораздо проще объяснить и изучить.
И это не игра с нулевой суммой.
Так что, если вы новичок в мире поиска и не знаете, как использовать ВПР, лучше научитесь этому.
У меня есть подробное руководство по использованию ВПР в Excel (с множеством примеров)
В этой статье я не собираюсь противопоставлять две потрясающие функции. Я хотел показать вам мощь комбинации INDEX MATCH и все замечательные вещи, на которые она способна.
Надеюсь, вы нашли эту статью полезной.
Сообщите мне свои мысли в разделе комментариев, и, если вы обнаружите какую-либо ошибку в этом руководстве, сообщите мне.
