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

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

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

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

Изучите курсы по науке о данных в лучших университетах мира. Участвуйте в программах Executive PG, Advanced Certificate Programs или Master Programs, чтобы ускорить свою карьеру.

Изучите наши популярные курсы по науке о данных

Синтаксис

Синтаксис функций ПОИСКПОЗ следующий:

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

Это пример функции ПОИСКПОЗ:

=ПОИСКПОЗ(E4,C2:C12,1)

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

• Lookup_value: Это значение является обязательным и должно быть точным значением или значением, ближайшим к тому, что вы ищете в массиве. Например, если вы ищете число близкое к 2000 внутри диапазона, используйте 2000 в качестве контрольной точки для функции сопоставления. Для частичного совпадения используйте подстановочные знаки, такие как «*», которые помогают найти любую комбинацию символов или последовательность, и «?» который соответствует одному символу.
• Lookup_array: представляет целевой диапазон или массив для поиска позиции соответствующего значения. Например, от C2 до C20 — это диапазон, и внутри функции он будет представлен как «C2:C20». Это тоже обязательный компонент функции.
• Match_type: в этом аргументе есть три параметра в формуле ПОИСКПОЗ в Excel, но значение по умолчанию равно 1. Есть три параметра:

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

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

=ИНДЕКС(C4:F8,ПОИСКПОЗ(G5,B4:B10,ЛОЖЬ),2)

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

=ИНДЕКС(B5:H9,ПОИСКПОЗ(I5,D5:D9,1),ПОИСКПОЗ(I8,B4:D4,1))

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

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

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

В этом примере показаны значения в диапазоне от A1 до A3, расположенные в порядке возрастания.

Итак, если мы хотим найти соответствующее значение для «120», мы будем использовать эту формулу ПОИСКПОЗ в Excel :

=ПОИСКПОЗ(120,A1:A3,1)

Это будет то, что будет возвращено: 2

Результатом будет значение «2», так как это позиция строки с ближайшим числом к ​​«120». Ни одно число точно не соответствует 120, и поэтому функция идентифицирует его, поскольку оно имеет меньшее значение, чем «120», и поскольку «1» использовалось в качестве Match_type. «A1: A3» — это Lookup_array, который представляет собой диапазон с числами. Если бы массив был отсортирован в порядке убывания, в функции для Match_type использовалось бы значение '-1'.

Когда дело доходит до отрицательного приблизительного типа соответствия, будет отображаться число больше 120 или любое Lookup_value. Но в функции сопоставления возникла бы ошибка, если используется «-1», а массив находится в порядке возрастания.

В следующем примере попробуем найти позицию «Камера» среди трех слов в ячейках в диапазоне A1:A3.

Это будет формула:

=ПОИСКПОЗ("Камера",A1:A3)

Для поиска текста всегда держите Lookup_value внутри «», чтобы определить положение текста с помощью функции. Функция ПОИСКПОЗ не чувствительна к регистру. Соответствующее положение все еще можно найти без использования «Камера» с C в верхнем регистре и с использованием «камеры», где C в нижнем регистре.

Чтобы сделать функции ПОИСКПОЗ чувствительными к регистру, ее можно объединить с функцией ТОЧНОЕ, чтобы найти точную комбинацию алфавита. Когда Match_type равен «0» или для ТОЧНЫХ совпадений всегда используйте подстановочные знаки в Lookup_value.

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

В этом примере мы будем использовать 25 в качестве Lookup_value.

Это будет формула:

=ПОИСКПОЗ(25;B1:B3;-1)

Это будет то, что будет возвращено: 1

30 будет совпадающим значением, потому что оно больше 25, несмотря на то, что и 30, и 20 ближе всего к 25. Функция ПОИСКПОЗ выдаст «#Н/Д», если есть проблема с порядком массива.

В любом случае, когда значение Lookup_value не объявлено в формуле численно. Также можно указать положение другой ячейки для использования этого значения в качестве активного Lookup_value.

Поскольку значение ячейки D1 ​​равно 25, следует использовать следующую формулу:

=ПОИСКПОЗ(D1,B1:B3,-1)

D1 заменяет «25» в формуле.

Читайте наши популярные статьи о науке о данных

Вывод

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

Программа Executive Post Graduate Program в области науки о данных — отличный выбор, если вы ищете курс премиум-класса, чтобы узнать о ПОИСКПОЗ и других функциях Excel.