Note: The other languages of the website are Google-translated. Back to English
Войти  \/ 
x
or
x
Регистрация  \/ 
x

or

Как использовать ИНДЕКС и ПОИСКПОЗ вместе в Excel

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

Что делают функции ИНДЕКС и ПОИСКПОЗ в Excel

Как использовать функции ИНДЕКС и ПОИСКПОЗ вместе


Что делают функции ИНДЕКС и ПОИСКПОЗ в Excel

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

Использование функции ИНДЕКС в Excel

Освободи Себя ИНДЕКС Функция в Excel возвращает значение в заданном месте в определенном диапазоне. Синтаксис функции ИНДЕКС следующий:

=INDEX(array, row_num, [column_num])
  • массив (обязательно) относится к диапазону, из которого вы хотите вернуть значение.
  • row_num (обязательно, если не указан column_num) относится к номеру строки массива.
  • column_num (необязательно, но обязательно, если row_num опущен) относится к номеру столбца массива.

Например, чтобы знать итоговая оценка Джеффа за экзамен, шестой студент в списке, вы можете использовать функцию ИНДЕКС следующим образом:

= ИНДЕКС (E2: E11; 6) >>> возвращается 60

соответствие индекса Excel 01

√ Примечание: диапазон E2: E11 здесь указан финальный экзамен, а число 6 находит результат экзамена 6-й студент.

Давайте проведем небольшой тест. Для формулы = ИНДЕКС (B2: E2,3), какое значение он вернет? --- Да, вернется Китай, 3 значение в заданном диапазоне.

Теперь мы должны знать, что функция ИНДЕКС может отлично работать с горизонтальными или вертикальными диапазонами. Но что, если нам нужно, чтобы он возвращал значение в большем диапазоне с несколькими строками и столбцами? Что ж, в этом случае мы должны применять и номер строки, и номер столбца. Например, чтобы узнать страна Эмили родом из с помощью INDEX мы можем найти значение с номером строки 8 и номером столбца 3 в ячейках от B2 до E11 следующим образом:

= ИНДЕКС (B2: E11,8,3) >>> возвращается Китай

соответствие индекса Excel 02

Согласно приведенным выше примерам, о функции ИНДЕКС в Excel вы должны знать, что:

  • Функция ИНДЕКС может работать с вертикальными и горизонтальными диапазонами.
  • Функция ИНДЕКС не чувствительна к регистру.
  • Номер строки идет перед номером столбца (если вам нужны оба числа) в формуле ИНДЕКС.

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

Теперь давайте сначала узнаем об основах функции ПОИСКПОЗ.


Использование функции ПОИСКПОЗ в Excel

Функция ПОИСКПОЗ в Excel возвращает числовое значение, расположение определенного элемента в заданном диапазоне. Синтаксис функции ПОИСКПОЗ следующий:

=MATCH(lookup_value, lookup_array, [match_type])
  • lookup_array (обязательно) относится к диапазону ячеек, в котором вы хотите, чтобы ПОИСКПОЗ для поиска.
  • match_type (по желанию), 1, 0 or -1:
  • 1(по умолчанию), ПОИСКПОЗ найдет наибольшее значение, которое меньше или равно искомое_значение. Значения в lookup_array должны быть размещены в порядке возрастания.
  • 0, ПОИСКПОЗ найдет первое значение, которое точно равно искомое_значение. Значения в lookup_array может быть в любом порядке. (В случаях, когда тип соответствия установлен на 0, можно использовать подстановочные знаки.)
  • -1, ПОИСКПОЗ найдет наименьшее значение, которое больше или равно искомое_значение. Значения в lookup_array должны быть размещены в порядке убывания.

Например, чтобы знать положение Веры в списке имён, вы можете использовать формулу MATCH следующим образом:

= ПОИСКПОЗ ("истинное"; C2: C11,0) >>> возвращается 4

соответствие индекса Excel 03

√ Примечание: функция ПОИСКПОЗ не чувствительна к регистру. Результат «4» означает, что имя «Вера» находится на 4-й позиции списка. «0» в формуле - это тип соответствия, при котором будет найдено первое значение в массиве поиска, которое точно равно значению поиска «Vera».

Знать позиция счета «96» в строке от B2 до E2, вы можете использовать MATCH следующим образом:

= ПОИСКПОЗ (96; B2: E2,0) >>> возвращается 4

соответствие индекса Excel 04

☞ Что мы должны знать о функции ПОИСКПОЗ в Excel:

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

Теперь, когда мы знаем об основных способах использования функций ИНДЕКС и ПОИСКПОЗ в Excel, давайте засучим рукава и приготовимся объединить эти две функции.


Как использовать функции ИНДЕКС и ПОИСКПОЗ вместе

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

Пример объединения ИНДЕКС и ПОИСКПОЗ

Пожалуйста, посмотрите пример ниже, чтобы понять, как мы можем комбинировать функции ИНДЕКС и ПОИСКПОЗ:

Например, чтобы знать Итоговая оценка Эвелин за экзамен, мы должны использовать формулу:

=INDEX(A2:D11,MATCH("evelyn",B2:B11,0),MATCH("final exam",A1:D1,0)) >>> возвращается 90

соответствие индекса Excel 05

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

соответствие индекса Excel 06

Как вы можете видеть выше, большой ИНДЕКС формула содержит три аргумента:

  • массив: A2: D11 сообщает ИНДЕКС, что нужно вернуть совпадающее значение из ячеек через A2 - D11.
  • row_num: МАТЧ («Эвелин»; B2: B11,0) сообщает ИНДЕКСу точную строку значения.
  • Что касается формулы ПОИСКПОЗ, мы можем объяснить ее так: чтобы вернуть позицию первого значения, которая в точности равна «evelyn» в ячейках от B2 до B11, в числовом значении, которое 5.
  • column_num: МАТЧ («выпускной экзамен», A1: D1,0) сообщает ИНДЕКСу точный столбец значения.
  • Что касается формулы ПОИСКПОЗ, мы можем объяснить ее так: чтобы вернуть позицию первого значения, которая в точности равна «заключительному экзамену» в ячейках от A1 до D1, в числовом значении, которое 4.

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

= ИНДЕКС (A2: D11,5,4)

В этом примере мы использовали жестко запрограммированные значения «evelyn» и «final exc». Однако в такой большой формуле нам не нужны жестко запрограммированные значения, поскольку нам придется изменять их каждый раз, когда мы собираемся искать что-то новое. В таких обстоятельствах мы можем использовать ссылки на ячейки, чтобы сделать формулу такой динамической:

= ИНДЕКС (A2: D11,СООТВЕТСТВИЕ(G2, B2: B11,0),СООТВЕТСТВИЕ(F3, A1: D1,0))

соответствие индекса Excel 07


ИНДЕКС и ПОИСКПОЗ, чтобы применить поиск слева

Теперь предположим, что вам нужно знать класс Эвелин, как мы можем использовать ИНДЕКС и ПОИСКПОЗ, чтобы узнать ответ? Если вы обратили внимание, вы должны заметить, что столбец класса находится слева от столбца имени, и это выходит за рамки возможностей другой мощной функции поиска Excel, VLOOKUP.

Фактически, возможность поиска слева является одним из аспектов, в котором комбинация ИНДЕКС и ПОИСКПОЗ превосходит ВПР.

Знать Класс Эвелин, все, что вам нужно сделать, это изменить значение в ячейке F3 на «Класс» и использовать ту же формулу, что и показанная выше, функции ИНДЕКС и ПОИСКПОЗ сразу же сообщат вам ответ:

=INDEX(A2:D11,MATCH(G2,B2:B11,0),MATCH(F3,A1:D1,0)) >>> возвращается A

соответствие индекса Excel 08

Если у вас есть установкаLED Kutools for Excel, профессиональное дополнение Excel, разработанное нашей командой, вы также можете применить левый поиск для указанных значений с его ПОСМОТРЕТЬ справа налево функция с несколькими щелчками мыши. Чтобы реализовать эту функцию, перейдите на Kutools вкладка в вашем Excel, найдите Формула группа и щелкните ПОСМОТРЕТЬ справа налево в раскрывающемся списке Супер ПОСМОТРЕТЬ. Вы увидите всплывающее диалоговое окно, подобное этому:

соответствие индекса Excel 09

Щелкните здесь, чтобы узнать о конкретных шагах, чтобы применить функцию поиска слева с помощью Kutools for Excel.


ИНДЕКС и ПОИСКПОЗ для применения двустороннего поиска

Теперь вы можете составить формулу комбинации ИНДЕКС и ПОИСКПОЗ со значениями динамического поиска для применения двустороннего поиска? Попрактикуемся в создании формул в ячейках G3, G4 и G5, как показано ниже:

соответствие индекса Excel 10

Вот ответы:

Ячейка G3: =INDEX(A2:D11,MATCH(G2,B2:B11,0),MATCH(F3,A1:D1,0))
Ячейка G4: =INDEX(A2:D11,MATCH(G2,B2:B11,0),MATCH(F4,A1:D1,0))
Ячейка G5: =INDEX(A2:D11,MATCH(G2,B2:B11,0),MATCH(F5,A1:D1,0))

√ Примечание: после применения формул вы можете легко получить информацию о любых учениках, изменив имя в ячейке G2.


ИНДЕКС и ПОИСКПОЗ для применения поиска с учетом регистра

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

=INDEX(return_range,MATCH(TRUE,EXACT("lookup_value1",range1),0),MATCH("lookup_value2",range2,0))
√ Примечание. Это формула массива, требующая ввода с помощью Shift + Ctrl + Enter. Пара фигурных скобок появится в строке формул.

Например, чтобы знать ДЖИММИ на экзаменеиспользуйте следующие функции:

соответствие индекса Excel 11

=INDEX(A2:C11,MATCH(TRUE,EXACT("JIMMY",A2:A11),0),MATCH("final exam",A1:C1,0)) >>> возвращается 86

Или вы можете использовать ссылки на ячейки:

=INDEX(A2:C11,MATCH(TRUE,EXACT(F2,A2:A11),0),MATCH(E3,A1:C1,0)) >>> возвращается 86
Примечание: не забудьте ввести Shift + Ctrl + Enter.


ИНДЕКС и ПОИСКПОЗ для применения поиска по нескольким критериям

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

=INDEX(return_range,MATCH(1,(lookup_value1=range1)*(lookup_value2=range2)*(…),0))
Примечание. Это формула массива, требующая ввода с помощью Shift + Ctrl + Enter. Пара фигурных скобок появится в строке формул.

Например, чтобы найти итоговый результат экзамена Коко из Индии класса А, формула имеет следующий вид:

соответствие индекса Excel 12

=INDEX(D2:D11,MATCH(1,(G2=A2:A11)*(G3=B2:B11)*(G4=C2:C11),0)) >>> возвращается 88
√ Примечание: не забудьте ввести Shift + Ctrl + Enter.

Ну а что делать, если постоянно забываешь использовать Shift + Ctrl + Enter завершить формулу, чтобы формула возвращала неверные результаты? Здесь у нас есть более сложная формула, которую вы можете дополнить одним простым Enter ключ:

=INDEX(return_range,MATCH(1,INDEX((lookup_value1=range1)*(lookup_value2=range2)*(…),0,1),0))

Для того же примера, приведенного выше, чтобы найти итоговый результат экзамена Коко из Индии класса А, формула, которая требует только обычного Enter хит выглядит следующим образом:

соответствие индекса Excel 13

=INDEX(D2:D11,MATCH(1,INDEX((G2=A2:A11)*(G3=B2:B11)*(G4=C2:C11),0,1),0)) >>> возвращается 88

Здесь мы не будем использовать жестко запрограммированные значения, так как нам понадобится универсальная формула в случае с несколькими критериями. Только так мы можем легко получить желаемый результат, изменив значения в ячейках G2, G3, G4 в приведенном выше примере.

Работы С Нами Kutools for Excel"■ Функция поиска по нескольким условиям, вы можете искать определенные значения с несколькими критериями за несколько щелчков мышью. Чтобы реализовать эту функцию, перейдите на Kutools вкладка в вашем Excel, найдите Формула группа и щелкните Поиск по нескольким условиям в раскрывающемся списке Супер ПОСМОТРЕТЬ. Затем вы увидите всплывающее диалоговое окно, как показано ниже:

соответствие индекса Excel 14

Щелкните здесь, чтобы узнать о конкретных шагах, чтобы применить функцию поиска с несколькими условиями с помощью Kutools for Excel.


ИНДЕКС и ПОИСКПОЗ для применения поиска по нескольким столбцам

Если у нас есть таблица Excel с разными столбцами, имеющими один заголовок, как показано ниже, как мы можем сопоставить имя каждого учащегося с его / ее классом с помощью ИНДЕКС и ПОИСКПОЗ?

соответствие индекса Excel 15

Здесь я покажу вам, как выполнить задачу с помощью нашего профессионального инструмента. Kutools for Excel, С этими Формула Помощник, вы можете быстро сопоставить учащихся с их классами, как показано ниже:

1. Выберите ячейку назначения, в которой вы хотите применить функцию.

2. Под Kutools вкладку, перейдите к Формула Помощник, нажмите Формула Помощник в раскрывающемся списке.

соответствие индекса Excel 16

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

соответствие индекса Excel 17

4. а. Щелкните 1-й Значок соответствия индекса Excelкнопка справа от Lookup_col для выбора ячеек, из которых вы хотите вернуть значение, т. е. имен классов. (Здесь вы можете выбрать только один столбец или строку.)
    б. Щелкните 2-й Значок соответствия индекса Excelкнопка справа от Table_rng для выбора ячеек, соответствующих значениям в выбранных Lookup_col, то есть имена студентов.
    c. Нажмите 3-й Значок соответствия индекса Excelкнопка справа от Lookup_value чтобы выбрать ячейку для поиска, то есть имя ученика, которое вы хотите сопоставить с его / ее классом.

соответствие индекса Excel 18

5. Нажмите ОК, вы увидите, что имя класса Джимми появилось в ячейке назначения.

соответствие индекса Excel 19

6. Теперь вы можете перетащить маркер заполнения вниз, чтобы заполнить классы других учащихся.

соответствие индекса Excel 20

Нажмите, чтобы загрузить Kutools for Excel, чтобы получить 30-дневную бесплатную пробную версию.



  • Бар Супер Формулы (легко редактировать несколько строк текста и формул); Макет для чтения (легко читать и редактировать большое количество ячеек); Вставить в отфильтрованный диапазон...
  • Объединить ячейки / строки / столбцы и хранение данных; Разделить содержимое ячеек; Объедините повторяющиеся строки и сумму / среднее значение... Предотвращение дублирования ячеек; Сравнить диапазоны...
  • Выберите Дубликат или Уникальный Ряды; Выбрать пустые строки (все ячейки пустые); Супер находка и нечеткая находка во многих рабочих тетрадях; Случайный выбор ...
  • Точная копия Несколько ячеек без изменения ссылки на формулу; Автоматическое создание ссылок на несколько листов; Вставить пули, Флажки и многое другое ...
  • Избранные и быстро вставляйте формулы, Диапазоны, диаграммы и изображения; Зашифровать ячейки с паролем; Создать список рассылки и отправлять электронные письма ...
  • Извлечь текст, Добавить текст, Удалить по позиции, Удалить пробел; Создание и печать промежуточных итогов по страницам; Преобразование содержимого ячеек в комментарии...
  • Суперфильтр (сохранять и применять схемы фильтров к другим листам); Расширенная сортировка по месяцам / неделям / дням, периодичности и др .; Специальный фильтр жирным, курсивом ...
  • Комбинируйте книги и рабочие листы; Объединить таблицы на основе ключевых столбцов; Разделить данные на несколько листов; Пакетное преобразование xls, xlsx и PDF...
  • Группировка сводной таблицы по номер недели, день недели и другое ... Показать разблокированные, заблокированные ячейки разными цветами; Выделите ячейки, у которых есть формула / имя...
вкладка kte 201905
  • Включение редактирования и чтения с вкладками в Word, Excel, PowerPoint, Издатель, доступ, Visio и проект.
  • Открывайте и создавайте несколько документов на новых вкладках одного окна, а не в новых окнах.
  • Повышает вашу продуктивность на 50% и сокращает количество щелчков мышью на сотни каждый день!
офисный дно
Say something here...
symbols left.
You are guest
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.

Be the first to comment.