Найти позицию в Excel

Обновлено: 05.07.2024

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

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

Функция ИНДЕКС

Функция ИНДЕКС в Excel фантастически гибкая и мощная, и вы найдете ее в огромном количестве формул Excel, особенно в сложных формулах. Но что на самом деле делает INDEX? В двух словах, ИНДЕКС извлекает значение в заданном месте в диапазоне. Например, предположим, что у вас есть таблица планет в нашей Солнечной системе (см. ниже), и вы хотите получить название четвертой планеты, Марса, с помощью формулы. Вы можете использовать ИНДЕКС следующим образом:

ИНДЕКС возвращает значение в 4-й строке диапазона.

Что делать, если вы хотите получить диаметр Марса с помощью ИНДЕКС? В этом случае мы можем указать как номер строки, так и номер столбца, а также предоставить больший диапазон. В приведенной ниже формуле ИНДЕКС используется весь диапазон данных в B3:D11 с номером строки 4 и номером столбца 2:

ИНДЕКС извлекает значение в строке 4 столбца 2.

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

В этот момент вы можете подумать: "Ну и что? Как часто вы действительно знаете положение чего-либо в электронной таблице?"

Совершенно верно. Нам нужен способ определить положение вещей, которые мы ищем.

Войдите в функцию ПОИСКПОЗ.

Функция ПОИСКПОЗ

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

ПОИСКПОЗ возвращает 3, так как "Персик" является третьим элементом. ПОИСКПОЗ не чувствителен к регистру.

ПОИСКПОЗ не имеет значения, является ли диапазон горизонтальным или вертикальным, как вы можете видеть ниже:

Тот же результат с горизонтальным диапазоном, ПОИСКПОЗ возвращает 3.

Важно! Последний аргумент функции ПОИСКПОЗ — это тип соответствия. Тип соответствия важен и определяет, будет ли совпадение точным или приблизительным. Во многих случаях вы захотите использовать ноль (0), чтобы обеспечить точное соответствие. Тип соответствия по умолчанию равен 1, что означает приблизительное совпадение, поэтому важно указать значение. Подробнее см. на странице ПОИСКПОЗ.

ИНДЕКС и ПОИСКПОЗ вместе

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

Допустим, мы хотим написать формулу, которая возвращает количество продаж за февраль для данного продавца. Из приведенного выше обсуждения мы знаем, что можем указать INDEX номер строки и столбца для получения значения.Например, чтобы получить число продаж Frantz за февраль, мы предоставляем диапазон C3:E11 со строкой 5 и столбцом 2:

Но мы, очевидно, не хотим жестко кодировать числа. Вместо этого нам нужен динамический поиск.

Как мы это сделаем? Функция ПОИСКПОЗ, конечно. ПОИСКПОЗ отлично подойдет для поиска нужных нам позиций. Работая шаг за шагом, давайте оставим столбец жестко закодированным как 2 и сделаем динамический номер строки. Вот измененная формула с функцией ПОИСКПОЗ, вложенной в ИНДЕКС вместо 5:

Сделав еще один шаг вперед, мы будем использовать значение из H2 в ПОИСКПОЗ:

ПОИСКПОЗ находит "Франц" и возвращает 5 в ИНДЕКС для строки.

1. ИНДЕКС требует числовых позиций.
2. ПОИСКПОЗ находит эти позиции.
3. ПОИСКПОЗ вложен в ИНДЕКС.

Теперь займемся номером столбца.

Двусторонний поиск с ИНДЕКСОМ и ПОИСКПОЗОМ

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

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

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

Полностью динамический двусторонний поиск с ИНДЕКСОМ и ПОИСКПОЗОМ.

Первая формула ПОИСКПОЗ возвращает 5 для ИНДЕКС в качестве номера строки, вторая формула ПОИСКПОЗ возвращает 3 для ИНДЕКС в качестве номера столбца. После запуска ПОИСКПОЗ формула упрощается до:

и ИНДЕКС правильно возвращает 10 525 долларов США, объем продаж Frantz в марте.

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

Видео: отладка формулы с помощью F9 (чтобы увидеть возвращаемые значения ПОИСКПОЗ)

Поиск слева

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

Поиск с учетом регистра

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

Примечание. Это формула массива, которую необходимо вводить с помощью клавиш Ctrl + Shift + Enter, за исключением Excel 365.

Ближайшее соответствие

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

Примечание. Это формула массива, которую необходимо вводить с помощью клавиш Ctrl + Shift + Enter, за исключением Excel 365.

Поиск по нескольким критериям

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

Примечание. Это формула массива, которую необходимо вводить с помощью клавиш Ctrl + Shift + Enter, за исключением Excel 365.

Другие примеры ИНДЕКС + ПОИСКПОЗ

Вот еще несколько основных примеров ИНДЕКС и ПОИСКПОЗ в действии, каждый из которых имеет подробное объяснение:

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

Синтаксис функции ПОИСКПОЗ()

Функция ПОИСКПОЗ() имеет следующий синтаксис:

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

• искомое_значение — это значение, которое вы хотите найти в списке. Это необходимо для работы функции.
• lookup_array — это диапазон ячеек, содержащих список. Это необходимо для работы функции.
• match_type – это необязательное значение, которое определяет тип искомого соответствия. Он может иметь три возможных значения: -1, 0 и 1. Если его не указать, Excel примет значение 1.

Очень важно понять параметр match_type

Понимание параметра match_type является ключом к эффективному использованию функции ПОИСКПОЗ.

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

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

В этом примере показано, как использовать функцию ПОИСКПОЗ, если ваш список не содержит дубликатов.

Функция ПОИСКПОЗ в действии — поиск совпадений в списке с дубликатами

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

Использование ПОИСКПОЗ в несортированном списке, который содержит повторяющиеся значения

• Если ваш список содержит дубликаты, но не отсортирован, наиболее надежный способ использования ПОИСКПОЗ — установить для параметра match_type значение 0.
  • Это вернет позицию первого экземпляра match_type в вашем списке.
  • Кроме того, он возвращает результат только в случае точного совпадения. Установка match_type на 1 или -1, когда ваш список не отсортирован, будет возвращать непредсказуемые, а иногда и неправильные результаты.

  Использование ПОИСКПОЗ в списке, отсортированном по возрастанию с повторяющимися значениями

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

    Использование ПОИСКПОЗ в списке, отсортированном в порядке убывания с повторяющимися значениями

    
    

    • Если ваш список отсортирован в порядке убывания, вы должны установить для match_type значение -1.
    • В следующем примере используется тот же набор чисел, что и в двух предыдущих примерах:
      
    • Обратите внимание, что в этом примере список отсортирован и число 52 занимает позиции 5, 6 и 7 в списке.
    • Функция ПОИСКПОЗ с типом соответствия, для которого задано значение -1, вернула 7. Как и в предыдущем примере, это связано с тем, что последний экземпляр 52 в списке находится в позиции 7.
    • Еще раз помните, что ПОИСКПОЗ может возвращать только одно значение из списка.
      • Если вы хотите, чтобы функция ПОИСКПОЗ возвращала позицию первого экземпляра числа 52, можно установить для параметра match_type значение 0.
      • Не существует надежного способа заставить ПОИСКПОЗ вернуть позицию второго экземпляра числа 52 в списке.

      Обзор функции ПОИСКПОЗ

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

      
      

      Возвращает позицию символа в тексте.

      Рационально

      Найти положение буквы в тексте.

      Описание

      Функция НАЙТИ в Excel отображает числовое положение символа в заданном тексте. Начальная позиция строки может меняться в зависимости от третьего аргумента [Start_num]. Посмотрите обучающее видео по функции поиска в Excel.

      Например, чтобы найти второе вхождение числа 8, функция =НАЙТИ(8,128128,4) возвращает 6, начните поиск 8 с 4-й цифры, которая возвращает 6, т. е. с 6-й позиции на рисунке.

      Синтаксис

      = НАЙТИ(найти_текст, внутри_текста, [начальный_номер])

      Параметры

      find_text — искомый символ
      within_text — строка, содержащая символ
      start_num [Необязательно] — определяет символ для начала поиска.

      Возвращаемое значение

      Логический результат (Истина или Ложь)

      Ключевые примечания

      • В функции НАЙТИ первым символом в аргументе within_text является символ номер 1 (позиция по умолчанию).
      • Функция НАЙТИ чувствительна к регистру, но допускает подстановочные знаки.
      • Используйте функцию ПОИСК Excel, если пользователь хочет выполнить поиск без учета регистра.
      1. start_num не больше нуля
      2. start_num больше, чем длина within_text
      3. find_text не отображается в within_text
      4. С учетом регистра

      Давайте рассмотрим примеры:

      • Если вы хотите найти символ или алфавит в строке, не беспокоясь об индексе, вам не нужно заполнять аргументы start_num, как в примере 1.
      • Вы также можете найти подстановочный знак или специальный символ или индекс значения в слове.
      • Если вы хотите найти вхождение или последовательность после первого вхождения, вы можете объединить две формулы,

      Например, если вы собираетесь увидеть 2-е, последнее или еще n-е событие, вы можете использовать =НАЙТИ(B10,C10, (НАЙТИ(B9,C9,D9)+1 или n-е место)). Формула возвращает ту же позицию персонажа, но со 2-й или разными позициями. (Скачайте рабочую тетрадь для ясности).

      Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Excel Starter 2010 Еще. Меньше

      Функция ИНДЕКС возвращает значение или ссылку на значение из таблицы или диапазона.

      
      

      Есть два способа использования функции ИНДЕКС:

      Если вы хотите вернуть значение указанной ячейки или массива ячеек, см. раздел Форма массива.

      Если вы хотите вернуть ссылку на указанные ячейки, см. форму ссылки.

      Форма массива

      Описание

      Возвращает значение элемента в таблице или массиве, выбранное с помощью индексов номеров строк и столбцов.

      Используйте форму массива, если первый аргумент ИНДЕКС является константой массива.

      Синтаксис

      ИНДЕКС(массив, номер_строки, [номер_столбца])

      Форма массива функции ИНДЕКС имеет следующие аргументы:

      Обязательный массив. Диапазон ячеек или константа массива.

      Если массив содержит только одну строку или столбец, соответствующий аргумент номер_строки или номер_столбца является необязательным.

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

      номер_строки Обязательно, если не указан номер_столбца. Выбирает строку в массиве, из которой нужно вернуть значение. Если номер_строки опущен, требуется номер_столбца.

      номер_столбца Необязательно. Выбирает столбец в массиве, из которого нужно вернуть значение. Если номер_столбца опущен, требуется номер_строки.

      Примечания

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

      Если задать для row_num или column_num значение 0 (ноль), ИНДЕКС возвращает массив значений для всего столбца или строки соответственно. Чтобы использовать значения, возвращенные в виде массива, введите функцию ИНДЕКС как формулу массива.

      Примечание. Если у вас установлена ​​текущая версия Microsoft 365, вы можете ввести формулу в верхнюю левую ячейку выходного диапазона, а затем нажать клавишу ВВОД, чтобы подтвердить формулу как формулу динамического массива. В противном случае формулу необходимо ввести как устаревшую формулу массива, сначала выбрав выходной диапазон, введите формулу в верхнюю левую ячейку выходного диапазона, а затем нажмите CTRL+SHIFT+ENTER для подтверждения. Excel вставляет фигурные скобки в начале и в конце формулы. Дополнительные сведения о формулах массивов см. в разделе Рекомендации и примеры формул массивов.

      Примеры

      Пример 1

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

      Скопируйте данные примера из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы формулы отображали результаты, выберите их, нажмите F2, а затем нажмите клавишу ВВОД.

      Описание

      Значение на пересечении второй строки и второго столбца в диапазоне A2:B3.

      Значение на пересечении второй строки и первого столбца в диапазоне A2:B3.

      Пример 2

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

      Примечание. Если у вас установлена ​​текущая версия Microsoft 365, вы можете ввести формулу в верхнюю левую ячейку выходного диапазона, а затем нажать клавишу ВВОД, чтобы подтвердить формулу как формулу динамического массива. В противном случае формулу необходимо ввести как устаревшую формулу массива, сначала выбрав две пустые ячейки, введите формулу в верхнюю левую ячейку выходного диапазона, а затем нажмите CTRL+SHIFT+ENTER для подтверждения. Excel вставляет фигурные скобки в начале и в конце формулы. Дополнительные сведения о формулах массивов см. в разделе Рекомендации и примеры формул массивов.

      Описание

      Значение найдено в первой строке, втором столбце массива. Массив содержит 1 и 2 в первой строке и 3 и 4 во второй строке.

      Значение найдено во второй строке, во втором столбце массива (тот же массив, что и выше).

      
      Справочная форма

      Описание

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

      Синтаксис

      ИНДЕКС(ссылка, номер_строки, [номер_столбца], [номер_области])

      Справочная форма функции ИНДЕКС имеет следующие аргументы:

      ссылка обязательна. Ссылка на один или несколько диапазонов ячеек.

      Если вы вводите несмежный диапазон для ссылки, заключите ссылку в круглые скобки.

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

      номер_строки Обязательно. Номер строки в ссылке, из которой возвращается ссылка.

      номер_столбца Необязательно. Номер столбца в ссылке, из которого возвращается ссылка.

      Например, если ссылка описывает ячейки (A1:B4,D1:E4,G1:H4), area_num 1 – это диапазон A1:B4, area_num 2 – это диапазон D1:E4, а area_num 3 – это диапазон G1. :H4.

      Примечания

      После того, как reference и area_num выбрали определенный диапазон, row_num и column_num выбирают конкретную ячейку: row_num 1 — это первая строка в диапазоне, column_num 1 — это первый столбец и так далее. Ссылка, возвращаемая ИНДЕКСом, представляет собой пересечение row_num и column_num.

      Если задать для row_num или column_num значение 0 (ноль), ИНДЕКС возвращает ссылку для всего столбца или строки соответственно.

      Результат функции ИНДЕКС является эталоном и интерпретируется как таковой другими формулами. В зависимости от формулы возвращаемое значение ИНДЕКС может использоваться как ссылка или как значение. Например, формула ЯЧЕЙКА("ширина",ИНДЕКС(A1:B2,1,2)) эквивалентна ЯЧЕЙКЕ("ширина",B1). Функция CELL использует возвращаемое значение INDEX в качестве ссылки на ячейку. С другой стороны, такая формула, как 2*ИНДЕКС(A1:B2,1,2), преобразует возвращаемое значение ИНДЕКС в число в ячейке B1.

      Примеры

      Скопируйте данные примера из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы формулы отображали результаты, выберите их, нажмите F2, а затем нажмите клавишу ВВОД.

      Читайте также:

        
      • Как поставить плюс или минус в Word
        
      • Как найти папку в Outlook
        
      • Как удалить вкладки в Excel
        
      • Как вставить электронную почту в электронную почту в Outlook
        
      • B 1 в Excel что это такое