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

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

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

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

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

Изучите онлайн-курсы по науке о данных на upGrad

Синтаксис

Синтаксис функций ПОИСКПОЗ:

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

Например, это функция ПОИСКПОЗ: =ПОИСКПОЗ(E4,C2:C12,1)

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

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

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

Наши учащиеся также читают: Изучайте Python онлайн бесплатно

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

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

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

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

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

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

Ознакомьтесь с нашими программами по науке о данных в США

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

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

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

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

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

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

Это вернет значение «2», поскольку это позиция строки, содержащей ближайшее число к «25». Точного совпадения не было, поэтому функция идентифицировала число 22, так как оно имеет меньшее значение, чем «25», и потому что мы использовали «1» в качестве нашего Match_type . Кроме того, мы использовали «A1: A3» в качестве нашего Lookup_array , так как это диапазон, содержащий числа. Если бы массив был отсортирован в порядке убывания, нам пришлось бы использовать '-1' в этой функции для Match_type .

В случае отрицательного приблизительного типа совпадения будет идентифицировано число больше '22' или любое значение Lookup_value . Однако, если мы используем «-1», а массив не в порядке убывания, функция сопоставления вернет ошибку.

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

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

=ПОИСКПОЗ("Файлы cookie",A1:A3)

Когда мы находим текст, мы должны хранить Lookup_value внутри « », чтобы функция могла определить положение текста. Регистр не имеет значения в функциях ПОИСКПОЗ, так как даже без использования файлов cookie (C в верхнем регистре) и с использованием файлов cookie (c в нижнем регистре) мы все равно можем найти совпадающую позицию. Однако функции ПОИСКПОЗ можно комбинировать с функцией ТОЧНОЕ, чтобы сделать функцию чувствительной к регистру и точно найти точную комбинацию алфавитов.

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

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

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

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

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

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

Теперь давайте возьмем пример, когда мы не численно объявляем Lookup_value в формуле. Мы также можем указать положение другой ячейки, чтобы использовать это значение в качестве активного Lookup_value в формуле. Поскольку значение, занимающее D1, равно 35, мы также можем использовать эту формулу:

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

Здесь просто D1 заменяет «35» в формуле.

Прочтите наши популярные статьи о науке о данных в США

Вывод:

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

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

Если вы уже делаете карьеру в этой области, но не знаете, что делать дальше, вы можете найти одни из лучших услуг наставничества от отраслевых экспертов в upGrad .

Если вы хотите получить более глубокие знания о Python, мы рекомендуем онлайн- программу профессиональных сертификатов upGrad в области науки о данных и бизнес-аналитики Университета Мэриленда, который входит в число 100 лучших университетов мира.