для чего нужна функция поискпоз
Поиск значений с помощью функций ВПР, ИНДЕКС и ПОИСКПОЗ
Совет: Попробуйте использовать новые функции ПРОСМОТРX и XMATCH, а также улучшенные версии функций, описанные в этой статье. Эти новые функции работают в любом направлении и возвращают точные совпадения по умолчанию, что упрощает и упрощает работу с ними по сравнению с предшественниками.
Предположим, у вас есть список номеров офисов, и вам нужно знать, какие сотрудники работают в каждом из них. Таблица очень угрюмая, поэтому, возможно, вам кажется, что это сложная задача. С функцией подытов на самом деле это довольно просто.
Функции ВВ., а также ИНДЕКС и ВЫБОРПОЗ — одни из самых полезных функций в Excel.
Примечание: Мастер подметок больше не доступен в Excel.
Ниже в качестве примера по выбору вы можете найти пример использования в этой области.
В этом примере B2 является первым аргументом —элементом данных, который требуется для работы функции. В случае СРОТ ВЛ.В.ОВ этот первый аргумент является искомой значением. Этот аргумент может быть ссылкой на ячейку или фиксированным значением, таким как «кузьмина» или 21 000. Вторым аргументом является диапазон ячеек C2–:E7, в котором нужно найти и найти значение. Третий аргумент — это столбец в диапазоне ячеек, содержащий ищите значение.
Четвертый аргумент необязателен. Введите истина или ЛОЖЬ. Если ввести ИСТИНА или оставить аргумент пустым, функция возвращает приблизительное совпадение значения, указанного в качестве первого аргумента. Если ввести ЛОЖЬ, функция будет соответствовать значению, заведомо первому аргументу. Другими словами, если оставить четвертый аргумент пустым или ввести ИСТИНА, это обеспечивает большую гибкость.
В этом примере показано, как работает функция. При вводе значения в ячейку B2 (первый аргумент) в результате поиска в ячейках диапазона C2:E7 (2-й аргумент) выполняется поиск в ней и возвращается ближайшее приблизительное совпадение из третьего столбца в диапазоне — столбца E (третий аргумент).
Четвертый аргумент пуст, поэтому функция возвращает приблизительное совпадение. Иначе потребуется ввести одно из значений в столбец C или D, чтобы получить какой-либо результат.
Если вы хорошо разучились работать с функцией ВГТ.В.В., то в равной степени использовать ее будет легко. Вы вводите те же аргументы, но выполняется поиск в строках, а не в столбцах.
Использование индекса и MATCH вместо ВРОТ
При использовании функции ВПРАВО существует ряд ограничений, которые действуют только при использовании функции ВПРАВО. Это означает, что столбец, содержащий и look up, всегда должен быть расположен слева от столбца, содержащего возвращаемого значения. Теперь, если ваша таблица не построена таким образом, не используйте В ПРОСМОТР. Используйте вместо этого сочетание функций ИНДЕКС и MATCH.
В данном примере представлен небольшой список, в котором искомое значение (Воронеж) не находится в крайнем левом столбце. Поэтому мы не можем использовать функцию ВПР. Для поиска значения «Воронеж» в диапазоне B1:B11 будет использоваться функция ПОИСКПОЗ. Оно найдено в строке 4. Затем функция ИНДЕКС использует это значение в качестве аргумента поиска и находит численность населения Воронежа в четвертом столбце (столбец D). Использованная формула показана в ячейке A14.
Дополнительные примеры использования индексов и MATCH вместо В ПРОСМОТР см. в статье билла Https://www.mrexcel.com/excel-tips/excel-vlookup-index-match/ Билла Джилена (Bill Jelen), MVP корпорации Майкрософт.
Попробуйте попрактиковаться
Если вы хотите поэкспериментировать с функциями подытовки, прежде чем попробовать их с собственными данными, вот примеры данных.
Пример работы с ВЛОКОНПОМ
Скопируйте следующие данные в пустую таблицу.
Совет: Прежде чем врезать данные в Excel, установите для столбцов A–C ширину в 250 пикселей и нажмите кнопку «Перенос текста» (вкладка «Главная», группа «Выравнивание»).
Функция ПОИСКПОЗ
Функция ПОИСКПОЗ выполняет поиск указанного элемента в диапазоне ячеек и возвращает относительную позицию этого элемента в диапазоне. Например, если диапазон A1:A3 содержит значения 5, 25 и 38, то формула =ПОИСКПОЗ(25;A1:A3;0) возвращает значение 2, поскольку элемент 25 является вторым в диапазоне.
Совет: Функцией ПОИСКПОЗ следует пользоваться вместо одной из функций ПРОСМОТР, когда требуется найти позицию элемента в диапазоне, а не сам элемент. Например, функцию ПОИСКПОЗ можно использовать для передачи значения аргумента номер_строки функции ИНДЕКС.
Синтаксис
Аргументы функции ПОИСКПОЗ описаны ниже.
Искомое_значение. Обязательный аргумент. Значение, которое сопоставляется со значениями в аргументе просматриваемый_массив. Например, при поиске номера в телефонной книге имя абонента указывается в качестве искомого значения, а нужным значением будет номер телефона.
Аргумент искомое_значение может быть значением (числом, текстом или логическим значением) или ссылкой на ячейку, содержащую такое значение.
Просматриваемый_массив Обязательный аргумент. Диапазон ячеек, в которых производится поиск.
В приведенной ниже таблице описано, как функция находит значения в зависимости от аргумента тип_сопоставления.
Функция ПОИСКПОЗ находит первое значение, равное аргументу искомое_значение. Просматриваемый_массив может быть не упорядочен.
Функция ПОИСКПОЗ возвращает не само значение, а его позицию в аргументе просматриваемый_массив. Например, функция ПОИСКПОЗ(«б»; <"а»;»б»;»в «>;0) возвращает 2 — относительную позицию буквы «б» в массиве <"а";"б";"в">.
Функция ПОИСКПОЗ не различает регистры при сопоставлении текста.
Если функция ПОИСКПОЗ не находит соответствующего значения, возвращается значение ошибки #Н/Д.
Пример
Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.
Функция ПОИСКПОЗ() в EXCEL
history 9 января 2014 г.
Синтаксис функции
ПОИСКПОЗ ( искомое_значение ; просматриваемый_массив ; тип_сопоставления)
Функция ПОИСКПОЗ() не различает РеГИстры при сопоставлении текстов.
Если функция ПОИСКПОЗ() не находит соответствующего значения, то возвращается значение ошибки #Н/Д.
Поиск позиции в массивах с текстовыми значениями
Произведем поиск позиции в НЕ сортированном списке текстовых значений (диапазон B7:B13 )
Столбец Позиция приведен для наглядности и не влияет на вычисления.
Формула для поиска позиции значения Груши: =ПОИСКПОЗ(«груши»;B7:B13;0)
Формула находит первое значение сверху и выводит его позицию в диапазоне, второе значение Груши учтено не будет.
Чтобы найти номер строки, а не позиции в искомом диапазоне, можно записать следующую формулу: =ПОИСКПОЗ(«груши»;B7:B13;0)+СТРОКА($B$6)
Если искомое значение не обнаружено в списке, то будет возвращено значение ошибки #Н/Д. Например, формула =ПОИСКПОЗ(«грейпфрут»;B7:B13;0) вернет ошибку, т.к. значения «грейпфрут» в диапазоне ячеек B7:B13 нет.
В файле примера можно найти применение функции при поиске в горизонтальном массиве.
Поиск позиции в массиве констант
Поиск позиции с использованием подстановочных знаков
Если искомое значение точно не известно, то с помощью подстановочных знаков можно задать поиск по шаблону, т.е. искомое_значение может содержать знаки шаблона: звездочку (*) и знак вопроса (?). Звездочка соответствует любой последовательности знаков, знак вопроса соответствует любому одиночному знаку.
В качестве критерия можно задать «яблок*» и формула =ПОИСКПОЗ(«яблок*»;B53:B62;0) вернет позицию текстового значения, начинающегося со слова яблок (если она есть в списке).
Подстановочные знаки следует использовать только для поиска позиции текстовых значений и Типом сопоставления = 0 (третий аргумент функции).
Поиск позиций ВСЕХ текстовых значений, удовлетворяющих критерию
Функция ПОИСКПОЗ() возвращает только одно значение. Если в списке присутствует несколько значений, удовлетворяющих критерию, то эта функция не поможет.
Значение Груши находятся в позициях 2 и 5 списка. С помощью формулы массива
можно отсортировать найденные позиции, чтобы номера найденных позиций отображались в первых ячейках (см. файл примера ).
Поиск позиции в массивах с Числами
1. Произведем поиск позиции в НЕ сортированном списке числовых значений (диапазон B8:B14 )
Столбец Позиция приведен для наглядности и не влияет на вычисления.
Найдем позицию значения 30 с помощью формулы =ПОИСКПОЗ(30;B8:B14;0)
Формула ищет точное значение 30. Если в списке его нет, то будет возвращена ошибка #Н/Д.
2. Произведем поиск позиции в отсортированном по возрастанию списке числовых значений (диапазон B31:B37 )
Сортированные списки позволяют искать не только точные значения (их позицию), но и позицию ближайшего значения. Например, в списке на картинке ниже нет значения 45, но можно найти позицию наибольшего значения, которое меньше либо равно, чем искомое значение, т.е. позицию значения 40.
Это можно сделать с помощью формулы =ПОИСКПОЗ(45;B31:B37;1)
Обратите внимание, что тип сопоставления =1 (третий аргумент функции).
Функции ПОИСКПОЗ() и ИНДЕКС()
Функции ПОИСКПОЗ() и ИНДЕКС() часто используются вместе, т.к. позволяют по найденной позиции в одном диапазоне вывести соответствующее значение из другого диапазона. Рассмотрим пример.
Найдем количество заданного товара на определенном складе. Для этого используем формулу
Excel-plus
Пошаговые инструкции, как использовать функции, формулы и другие инструменты табличного редактора Microsoft Excel
Как работает функция ПОИСКПОЗ в Excel. Описание и примеры.
Как работает функция ПОИСКПОЗ в Excel. Описание и примеры.
В данной статье, на примерах, рассмотрим как работает функция ПОИСКПОЗ в Excel. Данная функция осуществляет поиск искомого значения в указном диапазоне ячеек. Как результат, функция возвращает позицию искомого значения в указанном диапазоне ячеек. При этом поиск с помощью функции ПОИСКПОЗ может осуществляться только в одном столбце или в одной строке.
Как вызвать функцию ПОИСКПОЗ в Excel.
Перед тем как вызвать функцию ПОИСКПОЗ необходимо определить ячейку, в которой она будет располагаться. Это можно сделать щелкнув левой клавишей мыши по выбранной ячейки. Далее нажимаем на кнопку Вставить функцию. Открывается диалоговое окно Вставка функции. Здесь в поле Выберите функцию: выбираем нужную нам функцию. Если в предложенном перечни функций интересующей нас функции нет, необходимо воспользоваться поиском.
Использование функции ПОИСКПОЗ для поиска позиции ячейки с текстовым значением.
У нас есть список имен. Данные имена расположены в одном столбце. Наша задача найти позицию (номер по порядку) определенного имени в этом списке. После того, как мы выбрали необходимую нам функцию в диалоговом окне Вставка функции, откроется диалоговое окно Аргументы функции.
В данном диалогом окне три поля:
0 (ноль) — точное совпадение — функция будет искать точное совпадение с указанным в пункте Искомое_значение значением. При этом функция вернет позицию первого найденного в анализируемом диапазоне совпадающего значения. Сортировка значений анализируемого диапазона при этом не важна.
1 (один) — меньше — функция будет искать наибольшее значение, которое меньше значения указанного в пункте Искомое_значение. Или равное ему. Значения в анализируемом диапазоне должны быть отсортированы по возрастанию.
— 1 (минус один) — больше — функция будет искать наименьшее значение, которое больше значения указанного в пункте Искомое_значение. Или равное ему. Значения в анализируемом диапазоне должны быть отсортированы по убыванию.
ВАЖНО: позиция (порядковый номер) искомого значения в анализируемом диапазоне является относительным, так как функция рассчитывает позицию, отсчитывая порядковый номер от начала анализируемого диапазона ячеек.
Нажимаем ОК.
Функция вернула значение 5. Это значит, что имя Петр находиться в пятой по счету ячейки, в столбце В2:В13. При этом отсчёт видеться от ячейки В2.
Данный список имен можно разместить в одной строке и применив функцию ПОИСКПОЗ, по такому же алгоритму, найти позицию (номер по порядку) того или иного значения.
Использование функции ПОИСКПОЗ для поиска позиции ячейки с числовым значением.
Тип_сопоставления: 0. Функция ПОИСКПОЗ в Excel.
Возьмем для примера столбец с случайным набором числовых значений. Это будет наш анализируемый диапазон (Просматриваемый_массив): В2;В13. Найдем позицию числа 55, это Искомое_значение. В поле Тип_сопоставления — поставим значение 0 — точное совпадение.
Нажимаем ОК.
Функция вернула значение 8. Это значит, что число 55 находиться в восьмой по счету ячейки.
Тип_сопоставления: 1. Функция ПОИСКПОЗ в Excel.
Теперь в поле Тип_сопоставления поставим значение: 1 — меньше. В поле Искомое _значение поставим число: 40. Просматриваемый_массив: В2:В13. Для корректной работы функции ПОИСКПОЗ, в данном случае, необходимо отсортировать Просматриваемый _массив по возрастанию.
Нажимаем ОК.
Функция вернула значение 6. Это позиция числа 32. Поскольку в анализируемом диапазоне нет числа 40, ПОИСКПОЗ вернула позицию числа, которое меньше числа 40 (Искомое_значение). При этом оно наибольшее среди чисел, которые меньше числа 40.
Тип_сопоставления: — 1. Функция ПОИСКПОЗ в Excel.
Теперь в поле Тип_сопоставления поставим значение: — 1 — больше. В поле Искомое _значение оставим число: 40. Просматриваемый_массив: В2:В13. Для корректной работы функции ПОИСКПОЗ, в данном случае, необходимо отсортировать Просматриваемый_массив по убыванию.
Нажимаем ОК.
Функция вернула значение 6. Это позиция числа 45. Поскольку в анализируемом диапазоне нет числа 40, ПОИСКПОЗ вернула позицию числа, которое больше числа 40 (Искомое_значение). При этом оно наименьшее среди чисел, которые больше числа 40.
Использование функции ПОИСКПОЗ с ссылкой на ячейку.
Вернемся к списку имен. В диалоговом окне Аргументы функции, в поле Искомое_значение, укажем не само значение, а ссылку на ячейку. Например, ячейку: С2. Просматриваемый _массив: В2:В13. Тип_сопоставления: 0.
Нажимаем ОК.
Теперь в ячейку С2 вводим значение (имя): Глеб.
Функция вернула нам позицию имени Глеб в анализируемом диапазоне ячеек. Таким образом можно менять Искомое_значение, при этом не меняя его в самой формуле функции ПОИСКПОЗ.
Поиск позиции элемента в списке с ПОИСКПОЗ (MATCH)
Основное назначение этой функции в том, чтобы искать позицию заданного элемента в наборе значений. Чаще всего она применяется для поиска порядкового номера ячейки в диапазоне, где лежит нужное нам значение.
Синтаксис этой функции следующий:
=ПОИСКПОЗ( Что_ищем ; Где_ищем ; Режим_поиска )
Давайте рассмотрим несколько полезных вариантов ее применения на практике.
Точный поиск
Поиск первой или последней текстовой ячейки
Если в качестве искомого значения задать звездочку, то функция будет искать первую ячейку с текстом и выдавать её позицию. Для поиска последней текстовой ячейки можно изменить третий аргумент Режим_поиска с нуля на минус 1:
Числа и пустые ячейки в этом случае игнорируются.
Поиск ближайшего числа или даты
Например, нам нужно выбрать генератор из прайс-листа для расчетной мощности в 47 кВт. Если последний аргумент задать равным 1 и отсортировать таблицу по возрастанию, то мы найдем ближайшую наименьшую по мощности модель (Зверь):
Связка функций ПОИСКПОЗ и ИНДЕКС
Так, в предыдущем примере получить не номер, а название модели генератора можно очень легко:
Ну, и поскольку Excel внутри хранит и обрабатывает даты как числа, то подобный подход на 100% работает и с датами. Например, мы можем легко определить на каком этапе сейчас находится наш проект:
Принципиальное ограничение функции ПОИСКПОЗ состоит в том, что она умеет искать только в одномерных массивах (т.е. строчке или столбце), но никто не запрещает использовать сразу два ПОИСКПОЗа вложенных в ИНДЕКС, чтобы реализовать двумерный поиск по строке и столбцу одновременно: