Как сделать поискпоз excel
Добавил пользователь Алексей Ф. Обновлено: 04.10.2024
ПОИСКПОЗ(искомое_значение; просматриваемый_массив; [тип_сопоставления])
Возвращает относительную позицию в массиве элемента, соответствующего указанному значению с учетом указанного порядка.
- Искомое значение(обязательный аргумент) — значение, которое ищется в просматриваемом массиве;
- Просматриваемый массив(обязательный аргумент) — диапазон ячеек по которым ведется поиск;
- Тип сопоставления(необязательный аргумент) — вариант сопоставления искомого значения с просматриваемым массивом. Должен принимать значения 1, 0 или -1. По умолчанию равен 1.
- 1 — поиск наибольшего значения, которое меньше или равно искомому значению, просматриваемый массив должен быть упорядочен по возрастанию;
- 0 — поиск первого значения равного искомому значению, просматриваемый массив может быть не упорядочен;
- -1 — поиск наименьшего значения, которое больше или равно искомому значению, просматриваемый массив должен быть упорядочен по убыванию;
Пример использования функции ПОИСКПОЗ
Microsoft Excel – мощный аналитический инструмент и средство для работы с таблицами. Изучив доступные функции и команды, вы сможете выполнять сложные операции и оптимизировать большие базы данных. В данной инструкции пойдет речь о функции ПОИСКПОЗ, ее назначении и комбинациях с другими командами.
Мы рассмотрим следующие темы:
Назначение ПОИСКПОЗ
ПОИСКПОЗ – функция для определения позиции значения относительно выбранного диапазона ячеек или таблицы. После ввода формулы данная функция автоматически ищет совпадения в указанном массиве. Если совпадения по заданным параметрам найдены, то ПОИСКПОЗ возвращает номер позиции.
Правильное написание
Формула включает в себя следующие элементы:
- ПОИСКПОЗ() – указание самой функции;
- Искомое значение, позицию которого необходимо узнать. Можно указывать ссылки на ячейки или вписывать само значение.
- Диапазон данных – массив (таблица, строка, столбец) данных, среди которых будет осуществляться поиск;
- Тип сопоставления – уточнение того, какое значение предстоит искать (равное указанному в формуле, меньшее или большее).
Итак, финальный вариант формулы выглядит следующим образом:
Если вам необходимо одиночное использование функции, то она должна иметь строго указанный вид. В противном случае вы можете получить неверный результат или ошибку Н/Д.
Типы сопоставления
Рассмотрим подробнее разницу между несколькими типами сопоставления:
- 1 или без указания данного параметра – определяет наибольшее значение, которое максимально приближено к указанному в формуле;
- 0 – первое значение в диапазоне, которое равно искомому;
- -1 – наименьшее значение относительно искомого.
Данный аргумент необязателен к использованию в каждой формуле. Актуальным становится при работе с числовыми массивами, поскольку определить меньшее или большее текстовое значение невозможно.
Ввод команды в Excel
Теперь разберемся, как вводится формула на практике. Первый вариант добавления – это отдельное меню со списком функций:
- Сверху над основной таблицей находим иконку функции и жмем ее.
- Теперь появятся поля для ввода данных. Заполнять их можно вручную или указывать с помощью выделения ячеек таблицы.
Для редактирования формулы можно использовать верхнюю строку. С помощью кнопки креста удаляется введенная строка.
Примеры использования
Теперь рассмотрим несколько примеров использования ПОИСКПОЗ. Ниже представлены основные сценарии, когда может пригодиться команда.
Поиск по одному критерию
Начнем с простого варианта – это поиск с одним искомым значением. Работает по строкам и по столбцам. Также можно использовать массив в виде таблицы. Для нахождения позиции значения делаем следующее:
- Попробуем найти позицию значения 4. Для этого вписываем команду ПОИСКПОЗ и в скобках указываем адрес ячейки с цифрой. Вы можете вписать само значение, но тогда придется менять формулу каждый раз. А если указать ссылку, то пользователь сможет автоматизировать процесс.
- Ставим точку с запятой и выделяем весь массив. Он может состоять как из одного столбца, как в примере, так и из полноценной таблицы.
- Теперь указываем тип сопоставления. Если хотим найти точное совпадение, вписываем 0.
- Жмем клавишу [knopka]Enter[/knopka] и видим готовый результат.
- Если поменять значение в ячейке, ссылка на которую указана в начале формулы, то итоговое значение сменится.
Подобным образом функция работает и для текстовых значений. Видим таблицу с месяцами года. Допустим, хотим узнать расположение августа относительно данного массива. Так же указываем ссылку на ячейку с названием месяца, затем выделяем массив и добавляем тип сопоставления. Если речь идет о текстовых значениях, то необходимо ставить 0.
И снова получаем правильный результат. Теперь мы убедились, что функция работает исправно, поэтому можно применять ее в более сложных и объемных таблицах, где не получится решить данную задачу вручную.
Поиск по двум критериям
Этот пример включает в себя указание нескольких условий. Применяется для массивов с несколькими значениями. Для примера рассмотрим простую таблицу с автомобилями и годами выпуска. Мы видим, что несколько марок авто повторяются, но даты напротив них разные. Попробуем найти позицию автомобиля с конкретной датой выпуска.
Для последующей автоматизации поиска можно создать простую таблицу. В дальнейшем в ней можно менять только название машины и год выпуска.
Поскольку поиск будет осуществляться по двум признакам, придется использовать правила при работе с массивами. Начинаем вводить формулы с привычного ПОИСКПОЗ и указываем ссылку на ячейку с названием марки. После этого ставим знак & и указываем ссылку на ячейку с нужной датой выпуска.
Теперь нужно указать два диапазона – столбцы с марками и датами. Для этого выделяем первый и после знака & выделяем второй столбец. В конце ставим 0 для поиска точного значения.
Для вычисления позиции необходимо воспользоваться комбинацией [knopka]Ctrl[/knopka]+[knopka]Shift[/knopka]+[knopka]Enter[/knopka]. Так вы запустите выполнение функции в массиве.
Использование функции ПОИСКПОЗ через массив возможно не только по двум признакам. После указания каждого параметра необходимо ставить знак & и запускать поиск только указанной комбинацией клавиш.
Взаимодействие с другими операторами
Теперь рассмотрим пример использования функции ПОИСКПОЗ с оператором ИНДЕКС. На практике ее можно комбинировать со многими другими функциями, если знать, по каким принципам она работает.
ИНДЕКС – функция поиска совпадений по двум параметрам. Данная формула является аналогом вышеописанного примера, когда необходимо вернуть значение по нескольким условиям. ИНДЕКС используется только в случае двумерного поиска, в то время, когда ВПР, ГПР или ПРОСМОТР предназначены для одномерного.
Допустим, у нас есть таблица со статистикой продаж сети автосалонов по месяцам. Попробуем найти количество проданных автомобилей марки Audi в сентябре. Начинаем составление формулы с ИНДЕКС и указания диапазона всего массива.
Вторым аргументом будет функция ПОИСКПОЗ с указанием ссылки на ячейку с названием месяца и диапазона столбца массива:
Последний аргумент – еще одна ПОИСКПОЗ, но теперь с ссылками на название марки и строкой с автомобилями:
Жмем на [knopka]Enter[/knopka] и видим результат в соответствующей ячейке. Проверяем по нашей таблице.
Теперь можно менять значения в нашей таблице справа и получать новые значения:
Вы можете вычислить позицию каждой ячейки отдельно через ПОИСКПОЗ, а затем воспользоваться короткой функцией ИНДЕКС с указанием ссылок на расположение марки и месяца в таблице.
По представленному примеру описываемый оператор может использоваться во многих формулах. Например:
- ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ));
- ЕСЛИ(ЕНД(ПОИСКПОЗ(ИСТИНА;(СОВПАД))));
- ИНДЕКС(ПОИСКПОЗ(МИН(ЕСЛИ))) и другие.
Если вы знакомы с функционалом VBA для Microsoft Excel, то сможете создать надстройку для автоматического вычисления и поиска необходимых значений.
Видеоинструкция
Подробно и наглядно про данную возможность Excel рассказывается в представленном видеоролике.
Заключение
Мы рассмотрели основные случаи использования оператора ПОИСКПОЗ. Теперь вы сможете применять его по назначению и комбинировать с подходящими функциями.
Функция ПОИСКПОЗ используется, когда в рамках указанного массива требуется узнать номер ячейки (начиная сверху), соответствующей условию поиска. Функция ПОИСКПОЗ возвращает не само значение (оно указывается внутри формулы), а позицию в указываемом массиве (столбце).
Например, в столбце с данными нужно отыскать и указать номер ячейки, содержащей число 82.
Воспользуемся функцией ПОИСКПОЗ. Ее синтаксис следующий.
Искомое_значение – то значение, которое ищется в списке.
Просматриваемый_массив – столбец, где происходит просмотр данных.
С одной стороны, типы -1 и 1 позволяют искать неточное совпадение, чтобы расширяет возможности поиска, с другой, требует дополнительного действия (сортировки), что резко снижает уровень автоматизации. Поэтому в большинстве случаев выбирают 0 для точного соответствия.
Итак, в примере выше для поиска ячейки со значением 82 потребуется указать следующую функцию:
Ответом будет 5, т.к. число 82 находится в 5-й сверху ячейке. Разумеется, вместо числа 82 можно указать ссылку, где находится критерий поиска.
Функция ПОПИСКПОЗ раскрывается по полной при использовании в комплексе с другими формулами, т.к. номер ячейки сам по себе мало, что дает.
Сегодня мы узнаем, как используется связь таблиц с помощью комбинирования функций ИНДЕКС и ПОИСКПОЗ, а так же СМЕЩ и ПОИСКПОЗ в Excel. Представим, что имеется некая таблица следующего вида.
Необходимо найти по коду номенклатуры ее название. Все бы ничего, но название номенклатуры располагается слева от колонки с кодами, поэтому применить столь любимую многими функцию ВПР нельзя. Ну не работает она в левую сторону! Как вариант решения можно попробовать скопировать колонку с кодом в начало таблицы. Можно, но во избежание случайного удаления или искажения такие таблицы защищают от редактирования. Другими словами, в них нельзя добавлять столбцы или менять их местами. Как же быть? Выполнять поиск вручную? Вот в таких ситуациях и начинает работать функция ИНДЕКС и СМЕЩ совместно с ПОИСКПОЗ. Рассмотрим эти функции подробнее.
Данная функция позволяет найти порядковый номер элемента в списке. В ней по очереди, разделяя точкой с запятой в русской версии Windows или запятой в русифицированной версии, надо указать такие данные:
- Искомое значение. Это значение, которое мы будем искать в списке. Значение должно быть уникальным, иначе Excel найдет только первое!
- Диапазон списка. Может состоять только из одного столбца или одной сроки. Это важно! Если попробовать выделить две колонки или две строки, программа выдаст ошибку!
- Способ поиска. Тут надо указать вариант, согласно которому приложение выполнит поиск позиции для нашего искомого значения. Тут возможны следующие типы.
1 Будет найдено ближайшее к нашему значение, которое не превышает его, то есть НЕ БОЛЬШЕ нужного. Равным может быть, но не больше. Список должен располагаться ПО ВОЗРАСТАНИЮ.
-1 Excel найдет значение, которое самое близкое к заданному нами, но НЕ МЕНЬШЕ ЕГО. Список должен быть ПО УБЫВАНИЮ.
0 Запустится поиск ТОЧНОГО СООТВЕТСТВИЯ значения из списка заданному нами. Сортировка тут НЕ ТРЕБУЕТСЯ.
Наглядное применение ПОИСКПОЗ.
Посмотрите на скриншот.
Особое внимание обратите вот на что. Во второй и третьей формуле задан тип поиска равный 1 (единице). Однако если в первой из них Excel искал число, которое действительно присутствует в списке, то есть число 50, то он его порядковый номер и указал. А вот числа 68 в списке нет, поэтому он указал порядковый номер значения из списка, которое находится ближе всего к нужному нам, то есть к 68, но его не превышает. А это – число 60.
И еще одна тонкость. Если использовать тип поиска, или, как пишется в справке Excel по этой функции, тип сопоставления, единицу, и в качестве искомого задать значение, которое будет явно больше любого в списке, то Excel покажет номер самого последнего элемента. Таким образом можно узнать, какая строка является последней в таблице. Вот пример:
Функция ИНДЕКС в Excel и ее особенности.
Функция ИНДЕКС показывает значение на пересечении заданных строки и столбца в указанной таблице. При ее написании надо последовательно указать таблицу, номер строки в ней и номер столбца в ней. Из пересечения указанных строки и столбца Excel и возьмет нужные нам данные.
Отметим, что функция индекс, включенная в состав другой функции, начинает срабатывать как часть адреса. В следующем примере Excel рассчитывает сумму за первые 6 месяцев. Информацию о номере последнего месяца он берет из ячейки R4, закрашенной зеленым цветом.
Стоит поменять в ней значение, и результат функции СУММ поменяется.
Кроме этого, надо помнить, что кроме варианта работы с единым массивом, существует вариант написания функции ИНДЕКС с выбором для расчета нужной области из указанных. При написании такой функции надо указать
- Области, которые будут обработаны. Их пишут в отдельных скобках, разделяя точкой с запятой или запятой в зависимости от ваших настроек.
- Строка в выбранной области, которая интересует
- Столбец, на пересечении которого с указанной строкой надо взять значение
- Область из перечисленных в начале.
Результат работы может выглядеть так, как на рисунке.
В этом примере одна и та же формула позволили получить данные по сервисному обслуживанию за первый период, то есть за январь, по очереди по трем областям. Однако чаще все-таки используется вариант функции ИНДЕКС, который был рассмотрен в начале.
Функция СМЕЩ в Excel и т онкости ее применения.
. Функция СМЕЩ показывает значение ячейки или диапазона, которые смещен относительно указанного адреса на нужное количество строк и столбцов. Ее синтаксис следующий.
СМЕЩ(1;2;3;4;5)
- Ссылка, от которой отсчитывается перемещение.
- На сколько строк надо переместиться.
- Сколько столбцов надо отсчитать для перемещения.
- Объем строк в диапазоне, на которые перемещаемся. Указывать не обязательно.
- Количество столбцов, на которые перемещаемся. Тоже указывать не обязательно.
Если четвертый и пятый параметр не указан, то программа считает, что нам требуется значение только одной ячейки. На нее мы перемещаемся, когда отсчитываем от указанной первым аргументом ссылки строки и столбцы. Не забываем, что если двигаемся влево или вверх, то смещение по колонкам и строкам соответственно указываем отрицательное. Если же двигаемся вправо или вниз, то значения уже будут положительные.
В примере выше происходит перемещение от ячейки G11 сначала на 2 строчки вверх, затем на 4 колонки влево. В найденной ячейки находится число 10, которое и является результатом работы функции.
Вариант же, когда указывается диапазон, на который переходим, чаще используется как исходные данные для другой функции. В примере ниже с помощью функции СМЕЩ вычисляется диапазон, по которому затем рассчитывается сумма. Если быть честным, то данный вариант на практике бесполезен, проще было бы сразу указать столбец Е для вычисления. Однако он хорошо показывает возможности СМЕЩ.
И еще. Как и в случае с ИНДЕКС, внутри другой формулы результат работы СМЕЩ может использоваться как часть адреса.
Первый вариант создания “левого ВПР”. Комбинируем ИНДЕКС и ПОИСКПОЗ.
Теперь попробуем связать функцию ИНДЕКС и ПОИСКПОЗ. Смысл здесь в том, что количество строк и (или) столбцов для функции ИНДЕКС можно найти с помощью функции ПОИСКПОЗ. В частности, задача, с которой мы начали занятие, может быть решена в два хода. Вначале с помощью ПОИСКПОЗ находим номер нужной строчки, а уже зная ее, переходим в ней с помощью ИНДЕКС в нужную сторону.
Данные формулы конечно можно объединить в одну. Если в последней формуле вместо адреса К8 указать ее содержимое, то есть записать ПОИСКПОЗ(K6;C7:C86;0), то результат работы не изменится. Итоговая формула будет уже такой:
Такую комбинацию ИНДЕКС и ПОИСКПОЗ часто называют ЛЕВЫМ ВПР. Полученная комбинация работает аналогично ВПР, но из-за алгоритма она, во-первых, работает быстрее, а во-вторых, поиск заданного значения не привязан только к первому столбцу, и формула получается более универсальной и гибкой.
Второй вариант создания “левого ВПР”. Комбинируем СМЕЩ и ПОИСКПОЗ.
Аналогично комбинированию ИНДЕКС и ПОИСКПОЗ, мы можем использовать для извлечения нужного значения из таблицы комбинирование СМЕЩ и ПОИСКПОЗ. В полученной составной функции с помощью ПОИСКПОЗ находим перемещение по строкам и столбцам. Расписывать все это по отдельности второй раз уже станем, а сразу покажем формулу.
В следующем примере с помощью сцепления формул СМЕЩ и ПОИСКПОЗ рассчитывается общая стоимость заказа по каждой позиции. При этом цена автоматически изменяется в зависимости от заказанного количества в соответствии с условиями, указанными в заголовке.
Особенности связки СМЕЩ и ПОИСКПОЗ
Сразу хотелось бы отметить два обстоятельства, которые можно заметить в данном практическом примере использования функций. Если рассматривать функцию СМЕЩ, то видно, что при отсутствии перемещения по строкам, как и по столбцам, ноль писать не обязательно. Можно просто указать место для значения, отделив его точкой с запятой. Именно так сделано в данном примере после первого указания ячейки J2 внутри функции СМЕЩ. Что же касается функции ПОИСКПОЗ, то для указания списка вовсе не обязательно указывать диапазон с ним. Список можно указать и внутри функции в фигурных скобках. В свою очередь это снижает время на подготовку к работе.
Давайте теперь подведем итог. Мы научились для связывания таблиц и получения данных из одной из них для вставки в другую использовать функции ИНДЕКС, СМЕЩ, ПОИСКПОЗ, а также различные их комбинации. Полученные формулы позволяют с успехом заменить функцию ВПР, при этом работая более быстро и гибко.
На этом наше занятие можно считать законченным. Попробуйте сами применить полученные знания. Вначале поработайте на учебных материалах, а уже потом и в рабочих документах. Всем удачи и успехов в использовании Excel !
Читайте также: