ПОИСКПОЗ в Excel

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

Мы разглядим последующие темы:

предназначение и описание ПОИСКПОЗ;
синтаксис формулы;
пошаговая {инструкция} по набору;
примеры использования;
композиции с иными командами в «Экселе».

Предназначение ПОИСКПОЗ

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

На британском написание команды в «Эксель» смотрится как MATCH. Почаще всего употребляется в качестве вспомогательной функции вместе с ИНДЕКС, ВПР, ВЫБОР и иными. Но мы разглядим и одиночное внедрение.

Правильное написание

Формула включает в себя последующие элементы:

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

ПОИСКПОЗ

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

Если для вас нужно одиночное внедрение функции, то она обязана иметь строго обозначенный вид. В неприятном случае вы сможете получить неправильный итог либо ошибку Н/Д.

Типы сравнения

Разглядим подробнее разницу меж несколькими типами сравнения:

1 либо без указания данного параметра – описывает наибольшее значение, которое очень приближено к обозначенному в формуле;
0 – 1-ое значение в спектре, которое равно разыскиваемому;
-1 – меньшее значение относительно искомого.

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

Ввод команды в Excel

Сейчас разберемся, как вводится формула на практике. 1-ый вариант прибавления – это отдельное меню со перечнем функций:

Сверху над главный таблицей находим иконку функции и нажимаем ее.

В открывшемся окне необходимо избрать категорию «Ссылки и массивы», потом отыскать подходящий вариант в перечне и надавить «ОК».

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

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

Примеры использования

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

Поиск по одному аспекту

Начнем с обычного варианта – это поиск с одним разыскиваемым значением. Работает по строчкам и по столбцам. Также можно употреблять массив в виде таблицы. Для нахождения позиции значения делаем последующее:

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

Интересно почитать: Функция что если в excel примеры

Ставим точку с запятой и выделяем весь массив. Он может состоять как из 1-го столбца, как в примере, так и из настоящей таблицы.

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

Нажимаем кнопку [knopka]Enter[/knopka] и лицезреем готовый итог.

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

Схожим образом функция работает и для текстовых значений. Лицезреем таблицу с месяцами года. Допустим, желаем выяснить размещение августа относительно данного массива. Так же указываем ссылку на ячейку с заглавием месяца, потом выделяем массив и добавляем тип сравнения. Если идет речь о текстовых значениях, то нужно ставить 0.

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

Поиск по двум аспектам

Этот пример включает в себя указание нескольких критерий. Применяется для массивов с несколькими значениями. Для примера разглядим ординарную таблицу с авто и годами выпуска. Мы лицезреем, что несколько марок авто повторяются, но даты напротив их различные. Попробуем отыскать позицию кара с определенной датой выпуска.

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

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

Сейчас необходимо указать два спектра – столбцы с марками и датами. Для этого выделяем 1-ый и опосля знака & выделяем 2-ой столбец. В конце ставим 0 для поиска четкого значения.

Для вычисления позиции нужно пользоваться композицией [knopka]Ctrl[/knopka]+[knopka]Shift[/knopka]+[knopka]Enter[/knopka]. Так вы запустите выполнение функции в массиве.

Функция поискпоз в excel примеры с несколькими условиями

Microsoft Excel Функция ПОИСКПОЗ делает поиск определенного значения в спектре ячеек и возвращает относительное положение этого значения.

Синтаксис

=MATCH ( lookup_value , lookup_array , [ match_type ])

аргументы

Lookup_value (нужные): Конкретное значение, которое вы желаете сравнить в массиве look_up;
Этот аргумент быть может числом, текстом, логическим значением либо ссылкой ячейки на значение (число, текст либо логическое значение).

Lookup_array (нужные): Спектр ячеек, содержащий разыскиваемое значение.

Match_type (необязательный): Тип соответствия, которое будет делать функция. Он состоит из 3-х видов:

0 — Находит 1-ое значение, буквально соответственное lookup_value
1 — либо опущено. Если четкое значение соответствия не быть может найдено, Match отыщет наибольшее значение, которое меньше либо равно значению look_up.
Значения в аргументе массива look_up должны быть в порядке возрастания..
-1 — Находит меньшее значение, которое больше либо равно значению look_up.
Значения в аргументе массива look_up должны быть в порядке убывания..

Интересно почитать: Excel функция дата

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

Примечания к функциям

1. Функция ПОИСКПОЗ не чувствительна к регистру.
2. Функция Match возвратит значение ошибки # N / A, если совпадение не будет найдено.
3. Функция ПОИСКПОЗ дозволяет употреблять знаки подстановки в аргументе lookup_value для ориентировочного совпадения.

Примеры

Пример 1: функция ПОИСКПОЗ для четкого совпадения

Пожалуйста, сделайте последующее, чтоб возвратить позицию Чарльза Монагана в спектре B3: B7.

Изберите пустую ячейку, введите в нее формулу ниже и нажмите кнопку Enter, чтоб получить итог.
=MATCH(D4,B3:B7,0)

Внимание: В формуле D4 содержит поисковое значение; B3: B7 это спектр ячеек, содержащий разыскиваемое значение; количество 0 значит, что вы ищете четкое значение соответствия.

Пример 2: функция ПОИСКПОЗ для ориентировочного совпадения

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

Как показано на снимке экрана ниже, вы желаете отыскать позицию числа 2500 в спектре B3: B7, но в перечне нет 2500, тут я покажу для вас, как возвратить позицию самого огромного значения, которое меньше 2500 в перечне.

Изберите пустую ячейку, введите в нее приведенную ниже формулу и нажмите кнопку Enter, чтоб получить итог.
=MATCH(D4,B3:B7,1)

Потом он возвратит позицию числа 2000, которое является большим значением, наименьшим 2500 в перечне.

Примечание. В этом случае все значения в перечне B3: B7 должны быть отсортированы в порядке возрастания, в неприятном случае будет возвращена ошибка # N / A.
Чтоб возвратить позицию меньшего значения (гласит 3000), которое больше 2500 в перечне, примените эту формулу:
=MATCH(D4,B3:B7,-1)

Внимание: Все значения в перечне B3: B7 должны быть отсортированы по убыванию в случае возврата ошибки # N / A.

Пример 3: функция ПОИСКПОЗ для соответствия подстановочным знакам в функции ПОИСКПОЗ

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

Как показано на снимке экрана ниже, для получения позиции значения, которое начинается с «привет», сделайте последующее.

Изберите пустую ячейку, введите в нее формулу ниже (вы сможете поменять D4 конкретно на «привет *») и нажмите кнопку Enter. Он возвратит позицию первого значения совпадения.
=MATCH(D4,B3:B7,0)

Чаевые: Функция ПОИСКПОЗ не чувствительна к регистру.

Больше примеров

Наилучшие инструменты для работы в кабинете

Kutools for Excel — поможет для вас выделиться из толпы

Желаете стремительно и идеально делать свою ежедневную работу? Kutools for Excel дает массивные расширенные функции 300 (объединение книжек, сумма по цвету, разделение содержимого ячеек, дата преобразования и так дальше . ) и экономия 80% времени вам.

Приближенное и четкое совпадение с несколькими аспектами

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

Пример формулы в G7:

Целью данной формулы является отыскать размер кошки, если известен ее вес.

Интересно почитать: Самые нужные функции в excel

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

Это делается с помощью обычный функции ЕСЛИ:

Она заходит в функцию ПОИСКПОЗ как массив. Значение поиска для совпадений происходит от G6, который содержит вес (7 кг в примере).

Направьте внимание, что совпадение настроено для ориентировочного совпадения, установив тип_сопоставления = 1, это необходимо сортировки С5:С10.

ПОИСКПОЗ возвращает позицию веса в массиве, и передается ИНДЕКСУ как номер строчки. Поисковый_массив для ИНДЕКСА размеры в D5:D10, так ИНДЕКС получает размер, соответственный положению генерируемого совпадением (номер 6 в показанном примере).

Базисный ИНДЕКС ПОИСКПОЗ с приближенным сравнением

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

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

Базисный ИНДЕКС ПОИСКПОЗ, четкое совпадение

= ИНДЕКС( данные; ПОИСКПОЗ( значение; поиск_столбца ; ЛОЖЬ); столбец )

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

Направьте внимание, что крайний аргумент имеет значение 0, что принуждает ПОИСКПОЗ отыскать четкое совпадение.

ИНДЕКС с одним столбцом

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

= ИНДЕКС (E5:E9; ПОИСКПОЗ (H4; B5:B9;0)) // реализации

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

Чувствительное к регистру совпадение

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

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

Чтоб добавить чувствительность к регистру, мы используем функцию СОВПАД:

Которая получает массив правда/ересь значения:

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

ПОИСКПОЗ потом получает позицию первого отысканного настоящего значения: 4.

Четкий поиск соответствия с ИНДЕКС и ПОИСКПОЗ

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