Из этого туториала Вы узнаете, как использовать комбинацию ИНДЕКС и ПОИСКПОЗ для выполнения поиска в Excel и Google Таблицах.
ИНДЕКС и МАТЧ, идеальная пара
Давайте подробнее рассмотрим некоторые способы комбинирования функций ИНДЕКС и ПОИСКПОЗ. Функция ПОИСКПОЗ предназначена для возврата относительной позиции элемента в массиве, в то время как функция ИНДЕКС может извлекать элемент из массива с заданной конкретной позицией. Эта синергия между ними позволяет им выполнять практически любой тип поиска, который может вам понадобиться.
Комбинация ИНДЕКС / ПОИСКПОЗ исторически использовалась как замена функции ВПР. Одна из основных причин - возможность поиска влево (см. Следующий раздел).
Примечание: новая функция XLOOKUP теперь может выполнять поиск влево.
Посмотрите налево
Давайте воспользуемся этой таблицей баскетбольной статистики:
Мы хотим найти номер игрока Боба. Поскольку номер игрока находится слева от столбца имени, мы не можем использовать ВПР.
Вместо этого мы могли бы выполнить базовый запрос MATCH для вычисления строки Боба
= ПОИСКПОЗ (H2; B2: B5; 0)
Это будет искать точное совпадение слова «Боб», и поэтому наша функция вернет число 2, поскольку «Боб» находится в числе 2nd позиция.
Затем мы можем использовать функцию INDEX, чтобы вернуть Player #, соответствующий строке. А пока давайте просто введем вручную «2» в функцию:
= ИНДЕКС (A2: A5; 2)
Здесь INDEX будет ссылаться на A3, поскольку это 2nd ячейку в диапазоне A2: A5 и вернуть результат 42. Для нашей общей цели мы можем затем объединить эти два в:
= ИНДЕКС (A2: A5; ПОИСКПОЗ (H2; B2: B5; 0))
Преимущество здесь в том, что мы смогли вернуть результат из столбца слева от того места, где мы искали.
Двухмерный поиск
Давайте посмотрим на нашу таблицу раньше:
Однако на этот раз мы хотим получить конкретную статистику. Мы выяснили, что хотим найти Rebounds в ячейке H1. Вместо того, чтобы писать несколько операторов ЕСЛИ, чтобы определить, из какого столбца получить результат, вы можете снова использовать функцию ПОИСКПОЗ. Функция ИНДЕКС позволяет указать значение строки и значение столбца. Мы собираемся добавить сюда еще одну функцию ПОИСКПОЗ, чтобы определить, какой столбец нам нужен. Это будет выглядеть как
= ПОИСКПОЗ (H1; A1: E1; 0)
Наша ячейка в H1 - это раскрывающийся список, в котором мы можем выбрать, какую категорию мы хотим искать, а затем наше ПОИСКПОЗ определяет, к какому столбцу в таблице принадлежит этот столбец. Давайте добавим этот новый элемент в нашу предыдущую формулу. Обратите внимание, что нам нужно настроить первый аргумент, чтобы он был двухмерным, поскольку нам больше не нужен результат только из столбца A.
= ИНДЕКС (A2: E5; ПОИСКПОЗ (H2; B2: B5; 0); ПОИСКПОЗ (H1; A1: E1; 0))
В нашем примере мы хотим найти Rebounds для Чарли. Наша формула будет оценивать это так:
= ИНДЕКС (A2: E5; ПОИСКПОЗ ("Чарли"; B2: B5; 0), ПОИСКПОЗ ("Подборы"; A1: E1, 0)) = ИНДЕКС (A2: E5, 3, 4) = D4 = 6
Теперь мы создали гибкую настройку, которая позволяет пользователю извлекать любое значение из нашей таблицы без необходимости писать несколько формул или переходить операторы IF.
Несколько разделов
Он используется не часто, но у INDEX есть пятый аргумент, с помощью которого можно определить, какой площадь внутри аргумента один для использования. Это означает, что нам нужен способ передать несколько областей в первый аргумент. Вы можете сделать это, используя дополнительный набор круглых скобок. Этот пример покажет, как вы можете получать результаты из разных таблиц на листе с помощью INDEX.
Вот макет, который мы будем использовать. У нас есть статистика по трем различным четвертям игры.
В ячейках H1: H3 мы создали раскрывающиеся списки проверки данных для различных вариантов. Выпадающий список для квартала происходит от J2: J4. Мы будем использовать это для другого оператора MATCH, чтобы определить, какую область использовать. Наша формула в H4 будет выглядеть так:
= ИНДЕКС ((A3: E6, A10: E13, A17: E20), ПОИСКПОЗ (H2, B3: B6, 0), ПОИСКПОЗ (H1, A2: E2, 0), ПОИСКПОЗ (H3, J2: J4, 0))
Мы уже обсуждали, как работают две внутренние функции ПОИСКПОЗ, поэтому давайте сосредоточимся на первом и последнем аргументах:
= ИНДЕКС ((A3: E6, A10: E13, A17: E20),…, ПОИСКПОЗ (H3, J2: J4, 0))
Мы дали функции ИНДЕКС несколько массивов в первом аргументе, заключив их все в круглые скобки. Другой способ сделать это - использовать Формулы - Определить имя. Вы можете определить имя под названием «MyTables» с определением
= ИНДЕКС (MyTable, MATCH (H2, Table1347 [Name], 0), MATCH (H1, Table1347 [#Headers], 0), MATCH (H3, J2: J4,0))
Вернемся ко всему утверждению. Наши различные функции ПОИСКПОЗ сообщают функции ИНДЕКС, где именно искать. Сначала мы определим, что «Чарли» - это 3rd ряд. Затем нам нужно «Подборы», то есть 4th столбец. Наконец, мы определили, что хотим получить результат от 2nd стол. Формула будет вычисляться следующим образом:
= ИНДЕКС ((A3: E6, A10: E13, A17: E20), ПОИСКПОЗ (H2, B3: B6, 0), ПОИСКПОЗ (H1, A2: E2, 0), ПОИСКПОЗ (H3, J2: J4, 0)) = ИНДЕКС ((A3: E6, A10: E13, A17: E20), 3, 4, 2) = ИНДЕКС (A10: E13, 3, 4) = D13 = 14
Как мы упоминали в начале этого примера, вы можете размещать таблицы на одном листе. Если вы можете написать правильные способы сообщить вашему INDEX, из какой строки, столбца и / или области вы хотите получить данные, INDEX будет вам очень полезен.
Таблицы Google -ИНДЕКС И МАТЧ
Все приведенные выше примеры работают в Google Таблицах точно так же, как и в Excel.
