Как использовать ИНДЕКС и ПОИСКПОЗ вместе в Excel
При работе с таблицами Excel вы можете постоянно находить ситуации, в которых вам нужно искать значение. В этом руководстве мы покажем вам, как применять комбинацию функций ИНДЕКС и ПОИСКПОЗ для выполнения горизонтального и вертикального поиска, двустороннего поиска, поиска с учетом регистра и поиска, отвечающего нескольким критериям.
Что делают функции ИНДЕКС и ПОИСКПОЗ в Excel
Как использовать функции ИНДЕКС и ПОИСКПОЗ вместе
- Пример объединения ИНДЕКС и ПОИСКПОЗ
- ИНДЕКС и ПОИСКПОЗ, чтобы применить поиск слева
- ИНДЕКС и ПОИСКПОЗ для применения двустороннего поиска
- ИНДЕКС и ПОИСКПОЗ для применения поиска с учетом регистра
- ИНДЕКС и ПОИСКПОЗ для применения поиска по нескольким критериям
- ИНДЕКС и ПОИСКПОЗ для применения поиска по нескольким столбцам
Что делают функции ИНДЕКС и ПОИСКПОЗ в Excel
Прежде чем использовать функции ИНДЕКС и ПОИСКПОЗ, давайте сначала убедимся, что мы знаем, как ИНДЕКС и ПОИСКПОЗ могут помочь нам в поиске значений.
Использование функции ИНДЕКС в Excel
Освободи Себя ИНДЕКС Функция в Excel возвращает значение в заданном месте в определенном диапазоне. Синтаксис функции ИНДЕКС следующий:
- массив (обязательно) относится к диапазону, из которого вы хотите вернуть значение.
- row_num (обязательно, если не указан column_num) относится к номеру строки массива.
- column_num (необязательно, но обязательно, если row_num опущен) относится к номеру столбца массива.
Например, чтобы знать итоговая оценка Джеффа за экзамен, шестой студент в списке, вы можете использовать функцию ИНДЕКС следующим образом:
= ИНДЕКС (E2: E11; 6) >>> возвращается 60
√ Примечание: диапазон E2: E11 здесь указан финальный экзамен, а число 6 находит результат экзамена 6-й студент.
Давайте проведем небольшой тест. Для формулы = ИНДЕКС (B2: E2,3), какое значение он вернет? --- Да, вернется Китай, 3 значение в заданном диапазоне.
Теперь мы должны знать, что функция ИНДЕКС может отлично работать с горизонтальными или вертикальными диапазонами. Но что, если нам нужно, чтобы он возвращал значение в большем диапазоне с несколькими строками и столбцами? Что ж, в этом случае мы должны применять и номер строки, и номер столбца. Например, чтобы узнать страна Эмили родом из с помощью INDEX мы можем найти значение с номером строки 8 и номером столбца 3 в ячейках от B2 до E11 следующим образом:
= ИНДЕКС (B2: E11,8,3) >>> возвращается Китай
Согласно приведенным выше примерам, о функции ИНДЕКС в Excel вы должны знать, что:
- Функция ИНДЕКС может работать с вертикальными и горизонтальными диапазонами.
- Функция ИНДЕКС не чувствительна к регистру.
- Номер строки идет перед номером столбца (если вам нужны оба числа) в формуле ИНДЕКС.
Однако для действительно большой базы данных с несколькими строками и столбцами нам, безусловно, неудобно применять формулу с точным номером строки и номером столбца. И это когда мы должны совместить использование функции ПОИСКПОЗ.
Теперь давайте сначала узнаем об основах функции ПОИСКПОЗ.
Использование функции ПОИСКПОЗ в Excel
Функция ПОИСКПОЗ в Excel возвращает числовое значение, расположение определенного элемента в заданном диапазоне. Синтаксис функции ПОИСКПОЗ следующий:
- lookup_array (обязательно) относится к диапазону ячеек, в котором вы хотите, чтобы ПОИСКПОЗ для поиска.
- match_type (по желанию), 1, 0 or -1:
- 1(по умолчанию), ПОИСКПОЗ найдет наибольшее значение, которое меньше или равно искомое_значение. Значения в lookup_array должны быть размещены в порядке возрастания.
- 0, ПОИСКПОЗ найдет первое значение, которое точно равно искомое_значение. Значения в lookup_array может быть в любом порядке. (В случаях, когда тип соответствия установлен на 0, можно использовать подстановочные знаки.)
- -1, ПОИСКПОЗ найдет наименьшее значение, которое больше или равно искомое_значение. Значения в lookup_array должны быть размещены в порядке убывания.
Например, чтобы знать положение Веры в списке имён, вы можете использовать формулу MATCH следующим образом:
= ПОИСКПОЗ ("истинное"; C2: C11,0) >>> возвращается 4
√ Примечание: функция ПОИСКПОЗ не чувствительна к регистру. Результат «4» означает, что имя «Вера» находится на 4-й позиции списка. «0» в формуле - это тип соответствия, при котором будет найдено первое значение в массиве поиска, которое точно равно значению поиска «Vera».
Знать позиция счета «96» в строке от B2 до E2, вы можете использовать MATCH следующим образом:
= ПОИСКПОЗ (96; B2: E2,0) >>> возвращается 4
☞ Что мы должны знать о функции ПОИСКПОЗ в Excel:
- Функция ПОИСКПОЗ возвращает позицию значения поиска в массиве поиска, а не само значение.
- Функция ПОИСКПОЗ возвращает первое совпадение в случае дублирования.
- Как и функция ИНДЕКС, функция ПОИСКПОЗ может работать с вертикальными и горизонтальными диапазонами.
- MATCH также не чувствителен к регистру.
- Если поисковое значение формулы MATCH имеет текстовую форму, заключите его в кавычки.
Теперь, когда мы знаем об основных способах использования функций ИНДЕКС и ПОИСКПОЗ в Excel, давайте засучим рукава и приготовимся объединить эти две функции.
Как использовать функции ИНДЕКС и ПОИСКПОЗ вместе
В этой части мы поговорим о различных обстоятельствах использования функций ИНДЕКС и ПОИСКПОЗ для удовлетворения различных потребностей.
Пример объединения ИНДЕКС и ПОИСКПОЗ
Пожалуйста, посмотрите пример ниже, чтобы понять, как мы можем комбинировать функции ИНДЕКС и ПОИСКПОЗ:
Например, чтобы знать Итоговая оценка Эвелин за экзамен, мы должны использовать формулу:
=INDEX(A2:D11,MATCH("evelyn",B2:B11,0),MATCH("final exam",A1:D1,0)) >>> возвращается 90
Что ж, поскольку формула может показаться сложной, давайте рассмотрим каждую ее часть.
Как вы можете видеть выше, большой ИНДЕКС формула содержит три аргумента:
- массив: 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, VLOOKUP.
Фактически, возможность поиска слева является одним из аспектов, в котором комбинация ИНДЕКС и ПОИСКПОЗ превосходит ВПР.
Знать Класс Эвелин, все, что вам нужно сделать, это изменить значение в ячейке F3 на «Класс» и использовать ту же формулу, что и показанная выше, функции ИНДЕКС и ПОИСКПОЗ сразу же сообщат вам ответ:
=INDEX(A2:D11,MATCH(G2,B2:B11,0),MATCH(F3,A1:D1,0)) >>> возвращается A
Если у вас есть установкаLED Kutools for Excel, профессиональное дополнение Excel, разработанное нашей командой, вы также можете применить левый поиск для указанных значений с его ПОСМОТРЕТЬ справа налево функция с несколькими щелчками мыши. Чтобы реализовать эту функцию, перейдите на Kutools вкладка в вашем Excel, найдите Формула группа и щелкните ПОСМОТРЕТЬ справа налево в раскрывающемся списке Супер ПОСМОТРЕТЬ. Вы увидите всплывающее диалоговое окно, подобное этому:
ИНДЕКС и ПОИСКПОЗ для применения двустороннего поиска
Теперь вы можете составить формулу комбинации ИНДЕКС и ПОИСКПОЗ со значениями динамического поиска для применения двустороннего поиска? Попрактикуемся в создании формул в ячейках G3, G4 и G5, как показано ниже:
Вот ответы:
Ячейка 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 функция к вашим формулам следующим образом:
√ Примечание. Это формула массива, требующая ввода с помощью Shift + Ctrl + Enter. Пара фигурных скобок появится в строке формул.
Например, чтобы знать ДЖИММИ на экзаменеиспользуйте следующие функции:
=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.
ИНДЕКС и ПОИСКПОЗ для применения поиска по нескольким критериям
При работе с большой базой данных с несколькими столбцами и заголовками строк всегда сложно найти что-то, что удовлетворяет нескольким условиям. В этом случае для поиска нескольких критериев воспользуйтесь приведенной ниже формулой:
√ Примечание. Это формула массива, требующая ввода с помощью Shift + Ctrl + Enter. Пара фигурных скобок появится в строке формул.
Например, чтобы найти итоговый результат экзамена Коко из Индии класса А, формула имеет следующий вид:
=INDEX(D2:D11,MATCH(1,(G2=A2:A11)*(G3=B2:B11)*(G4=C2:C11),0)) >>> возвращается 88
√ Примечание: не забудьте ввести Shift + Ctrl + Enter.
Ну а что делать, если постоянно забываешь использовать Shift + Ctrl + Enter завершить формулу, чтобы формула возвращала неверные результаты? Здесь у нас есть более сложная формула, которую вы можете дополнить одним простым Enter ключ:
Для того же примера, приведенного выше, чтобы найти итоговый результат экзамена Коко из Индии класса А, формула, которая требует только обычного Enter хит выглядит следующим образом:
=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 с разными столбцами, имеющими один заголовок, как показано ниже, как мы можем сопоставить имя каждого учащегося с его / ее классом с помощью ИНДЕКС и ПОИСКПОЗ?
Здесь я покажу вам, как выполнить задачу с помощью нашего профессионального инструмента. Kutools for Excel, С этими Формула Помощник, вы можете быстро сопоставить учащихся с их классами, как показано ниже:
1. Выберите ячейку назначения, в которой вы хотите применить функцию.
2. Под Kutools вкладку, перейдите к Формула Помощник, нажмите Формула Помощник в раскрывающемся списке.
3. выберите Поиск по типу формулы, затем нажмите Индексирование и сопоставление по нескольким столбцам.
4. а. Щелкните 1-й 
кнопка справа от Lookup_col для выбора ячеек, из которых вы хотите вернуть значение, т. е. имен классов. (Здесь вы можете выбрать только один столбец или строку.)
б. Щелкните 2-й кнопка справа от Table_rng для выбора ячеек, соответствующих значениям в выбранных Lookup_col, то есть имена студентов.
c. Нажмите 3-й кнопка справа от Lookup_value чтобы выбрать ячейку для поиска, то есть имя ученика, которое вы хотите сопоставить с его / ее классом.
5. Нажмите ОК, вы увидите, что имя класса Джимми появилось в ячейке назначения.
6. Теперь вы можете перетащить маркер заполнения вниз, чтобы заполнить классы других учащихся.
Лучшие инструменты для работы в офисе
Kutools for Excel решает большинство ваших проблем и увеличивает вашу производительность на 80%
- Бар Супер Формулы (легко редактировать несколько строк текста и формул); Макет для чтения (легко читать и редактировать большое количество ячеек); Вставить в отфильтрованный диапазон...
- Объединить ячейки / строки / столбцы и хранение данных; Разделить содержимое ячеек; Объедините повторяющиеся строки и сумму / среднее значение... Предотвращение дублирования ячеек; Сравнить диапазоны...
- Выберите Дубликат или Уникальный Ряды; Выбрать пустые строки (все ячейки пустые); Супер находка и нечеткая находка во многих рабочих тетрадях; Случайный выбор ...
- Точная копия Несколько ячеек без изменения ссылки на формулу; Автоматическое создание ссылок на несколько листов; Вставить пули, Флажки и многое другое ...
- Избранные и быстро вставляйте формулы, Диапазоны, диаграммы и изображения; Зашифровать ячейки с паролем; Создать список рассылки и отправлять электронные письма ...
- Извлечь текст, Добавить текст, Удалить по позиции, Удалить пробел; Создание и печать промежуточных итогов по страницам; Преобразование содержимого ячеек в комментарии...
- Суперфильтр (сохранять и применять схемы фильтров к другим листам); Расширенная сортировка по месяцам / неделям / дням, периодичности и др .; Специальный фильтр жирным, курсивом ...
- Комбинируйте книги и рабочие листы; Объединить таблицы на основе ключевых столбцов; Разделить данные на несколько листов; Пакетное преобразование xls, xlsx и PDF...
- Группировка сводной таблицы по номер недели, день недели и другое ... Показать разблокированные, заблокированные ячейки разными цветами; Выделите ячейки, у которых есть формула / имя...
- Включение редактирования и чтения с вкладками в Word, Excel, PowerPoint, Издатель, доступ, Visio и проект.
- Открывайте и создавайте несколько документов на новых вкладках одного окна, а не в новых окнах.
- Повышает вашу продуктивность на 50% и сокращает количество щелчков мышью на сотни каждый день!
You are guest
or post as a guest, but your post won't be published automatically.
Be the first to comment.
