С каждым годом популярность программы Excel только растет. Все больше и больше компаний России пользуются ей для того, чтобы вести таблицы и анализировать данные. Когда информации не так много, то найти нужные данные легко можно воспользовавшись простыми методами поиска. Например, если нажать комбинацию клавиш на виндовс «Ctrl+F» либо на мак «command+F», то будут выведены результаты. Но этот вариант подходит, когда нужен поиск, который выдаст точные совпадения и таблица небольшая.
Когда же таблица больших объемов и в ней большое количество информации и листов, то для извлечения нужного результата придется пользоваться удобными функциями Excel. Сейчас более подробно поговорим о том, как проводить поиск по данным таблицы, использую такую функцию, как ПОИСКПОЗ (MATCH).
На данном сайте вы найдете и другие статьи по работе с программой Excel.
Содержание
Синтаксис функции поискпоз
Вначале коротко упомянем, что делает функция ПОИСКПОЗ. Она начинает поиск нужного значения в указанном диапазоне, после чего будет возвращать относительное положение значения. Синтаксис выглядит следующим образом:
=ПОИСКПОЗ(искомое_значение,искомый_массив,[тип_совпадения])
Рассмотрим каждый аргумент по отдельности:
«искомое_значение» — является числовым или текстовым значением, которое нужно найти;
«искомый_массив» — здесь пользователю требуется указать диапазон ячеек в котором проводить поиск;
«тип_совпадения» — здесь нужно указать, каким образом программе искать совпадение (должно полностью соответствовать искомому значению или быть просто схожим). Всего можно указать здесь 3 варианта:
- 1 или ничего не указано. В таком случае программа будет искать значение, которое будет больше всего совпадать с указанным или полностью равным ему. Для правильной работы функции в таком случае требуется, чтобы пользователь отсортировал массив по порядку возрастания.
- 0 Будет найдено значение, которое будет точно равно ему. Особенно полезно указывать этот аргумент в тех случаях, когда необходимо использовать функции ИНДЕКС и ПОИСКПОЗ вместе.
- -1 Будет искать наименьшее значение, которое ближе всего с искомым значением или равняется ему. Для правильной работы функции в таком случае требуется, чтобы пользователь отсортировал массив по порядку убывания.
Рассмотрим небольшой пример. Допустим, что в нашей таблице имеется массив с товарами магазина.
Воспользуемся такой формулой, чтобы найти среди этого списка помидоры:
=ПОИСКПОЗ(«помидоры»;A1:A3;0)
Проверяем итоговый результат. Видим, что программа выдает правильный ответ.
ВАЖНО! Есть некоторые моменты, которые стоит учитывать во время работы с функцией ПОИСКПОЗ.
Рассмотрим их подробнее:
- В качестве ответа всегда будет возвращаться не значения, а его позиция в просматриваемом диапазоне;
- Функция «ПОИСКПОЗ» не учитывает регистры букв во время поиска;
- Когда у функции не получается отыскать соответствующее значение, будет возвращена ошибка «#Н/Д»;
- В случаях, когда в аргументе «тип_сопоставления» пользователь указывает 0 и ищет значение в формате текста, то есть возможность использовать подстановочные знаки. Например, с помощью звездочки (*) можно указать, что подойдет любая последовательность знаков в указанном месте, а с помощью вопросительного знака отметить, что на этом месте может быть любой одиночный знак. А если найти нужно результат, который содержит в себе такие знаки, как «?» или «*», то перед ними ставиться знак «~».
Точный поиск
В большинстве случаев эта функция используется для самого обычного точного поиска совпадения в нужном диапазоне. Например, чтобы найти какой-то текст или число. Рассмотрим принцип работы на небольшом примере. У нас есть таблица с именами работников компании.
Так как список большой и вручную искать каждого работника по отдельности не хочется, воспользуемся следующей формулой:
=ПОИСКПОЗ(«Алексей»;A1:A20;0)
Таким образом мы указали программе, что нам необходимо найти сотрудника по имени Алексей в диапазоне от А1 до А20. Как только программа найдет нужного нам человека, будет указан номер строки.
Проверяем и действительно видим, что Алексей находится на 14 месте в списке. Вот так просто можно воспользоваться точным поиском в Excel.
Поиск первой или последней текстовой ячейки
Теперь затронем более сложную задачу, которую можно выполнить функцией ПОИСКПОЗ. Предположим, что у нас есть диапазон с числами. В этом диапазоне есть также и текстовые значения.
Например, нам хочется найти местоположение первой встречающейся ячейки с текстом и последней в списке. Чтобы выполнить такую задачу, нам достаточно просто воспользоваться символом звездочки «*». Вот формула:
=ПОИСКПОЗ(«*»;A1:A13;0)
Программа начнет просматривать диапазон с начала и до конца. Как только будет обнаружено текстовое значение, нам сразу же вернется значение.
А чтобы найти текстовую ячейку с конца списка, нужно немного видоизменить формулу следующим образом:
=ПОИСКПОЗ(«*»;A1:A13;-1)
Мы просто изменили аргумент «тип_совпадения» с «0» на «-1». Теперь программа начинает поиск не сверху вниз, а наоборот снизу вверх.
ИНТЕРЕСНО! В этом случае функция будет игнорировать любые ячейки, которые являются пустыми, содержат в себе числа или другие данные, которые не являются текстом.
Поиск ближайшего числа или даты
Интересно, что функцией ПОИСКПОЗ можно воспользоваться для более сложным задач. Если вспомнить про аргумент «тип_совпадения», то он дает возможность находить самое ближайшее наименьшее или наибольшее значение. Напоминаем, что в таком случае, крайне важно отсортировать таблицу ппо возрастанию или наоборот по убыванию. Разберем на примере, как воспользоваться такой крайне удобной возможность.
Если говорить коротко, то в этом случае функция ПОИСКПОЗ чем-то схожа с интервальным просмотром функции «ВПР». Только в последнем случае пользователь может искать только ближайшее наименьшее и все. А «ПОИСКПОЗ» дает выбрать необходимый нам вариант.
Приведем еще один пример. У нас имеется таблица с ноутбуками, мы хотим найти тот ноутбук, цена которого равняется 100000 рублей.
Воспользуемся формулой:
=ПОИСКПОЗ(E3;B2:B7;1)
Указываем в аргументе «тип_совпадения» значение «1» и отсортируем таблицу по возрастанию.
Программа находит самую ближайшую по значению меньшую стоимость ноутбука. Это оказалась модель ASUS TUF Gaming.
А если указать значение «-1» и снова отсортировать таблицу, только в этот раз по убыванию, то программа отыщет ближайшую модель, которая более дорогая. Это модель Apple MacBook Air 13.
Связка функций поискпоз и индекс
В этой статье уже упоминалось, что пользоваться функцией «ПОИСКПОЗ» можно, объединяя ее с другими функциями. Разберем этот момент более подробно. Опытные пользователи нередко применяют ПОИСКПОЗ вместе с другой полезной функцией Индекс. Последняя может помочь в извлечении данных из указанного диапазона по указанному номеру строки и столбца. Можно сравнить реализацию с левым ВПР.
Вернемся к прошлому примеру. У нас получилось найти номер строки, в которой содержится верный ответ. Но если таблица больших размеров, то искать самостоятельно название модели будет непросто. Теперь же, когда мы умеем объединять функции вместе, прописываем в ближайшую ячейку следующую формулу:
=ИНДЕКС(A2:A7;E4)
Вот так легко и просто получиться отбирать нужные модели. Причем теперь, если мы просто изменим данные, которые требуется искать, программа автоматически будет подстраиваться под новый вариант.
Рассмотрим еще одну ситуацию – работа с датами. Интересно, что программа Excel умеет хранить и обрабатывать даты в виде чисел, а значит функция «ПОИСКПОЗ» будет отлично работать и с ними. Предположим, что в нашей таблице имеется информация о наших заказах и их дедлайнах. Попробуем с помощью небольшой формулы определить, как заказ нужно сдавать в ближайшее время:
=ПОИСКПОЗ(Е4;B2:B7;1)
В ячейке «Е4» отмечается сегодняшнее число. Для того, чтобы указать дату и не обновлять ее каждый раз, а программа делала это самостоятельно, просто указываем следующую функцию:
=ТДАТА()
Она будет автоматически обновлять значение в зависимости от того, какое сегодня число. Но в этом случае стоит убедиться, что на компьютере указана правильная дата.
Сделав все правильно, программа начнет поиск ближайшего схожего результата с сегодняшним числом. Например, мы видим, что в нашем случае, пользователю нужно взяться за выполнение заказа «Разработка REST API для приложения».
Как сделать поиск с несколькими условиями
Разберем последнюю ситуацию, где легко можно воспользоваться функций ПОИСКПОЗ. Главным минусом данной функции является тот момент, что она имеет некоторые ограничения в использовании. Например, она была создана для того, чтобы искать в данные в одномерном массиве. Это значит, что поиск может производить только по строке или столбцу.
Что делать в таком случае? Совместим функции и будем осуществлять поиск сразу с несколькими условиями. Вложим сразу две функции ПОИСКПОЗ в функцию ИНДЕКС. Таким образом пользователь сможет искать сразу по двум диапазонам и это будет нормально функционировать.
В качестве примера возьмем еще одну таблицу, в которой находиться хорошо знакомая нам таблица умножения.
Предположим, что нам нужно узнать сколько будет 8 умножить на 6. Делаем дополнительную ячейку, в которой будем указывать первый множитель, и еще одну, где будет указываться второй множитель. В ячейке «итоговое значение» необходимо ввести следующую формулу:
=ИНДЕКС(B2:K11;ПОИСКПОЗ(N4;A2:A11;0);ПОИСКПОЗ(N5;B1:K1;0))
Разберем структуру формулы. В функции ИНДЕКС находиться диапазон, с которым мы будем работать, а также две функции «ПОИСКПОЗ». После того, как с помощью функций ПОИСКПОЗ программа найдет нужные значения, функция ИНДЕКС получит нужные значения, по которым искать правильный ответ в диапазоне.
Например, в нашем случае мы ищем множитель 8, который находится в ячейке «A9», и второй множитель 6, находящийся в столбце «G1». Далее программе остается найти лишь точку соприкосновения и это ячейка «G9», где и находиться правильный ответ.
Преимущества функции «ПОИСКПОЗ»
Вот так просто можно реализовать на практике функцию «ПОИСКПОЗ». Интересно, что на первый взгляд может показаться, что эта функция довольно простая и вряд ли пригодиться на практике. Однако это не так, многие опытные пользователи отмечают пользу данной функции. Рассмотрим некоторые из преимуществ:
- Есть возможность искать данные справа налево. Похожая по функционалу функция «ВПР» не способна выполнять поиск в таком направлении. В то время, как сочетание «ИНДЕКС»+«ПОИСКПОЗ» легко справляется с такой задачей.
- Кроме того, у пользователя есть возможность без опаски удалять и добавлять новые столбцы/строки, не переживая о том, что формула сломается и придется все переделывать. Все дело в том, что функция «ПОИСКПОЗ» не требует того, чтобы указывать конкретный порядковый номер столбца.
- Отсутствуют какие-то ограничения на количество символов искомого значения. Если вспомнить про функцию «ВПР», то с ней нельзя превышать число символов выше 255, здесь же такой проблемы нет.
- Быстро обрабатывает данные. Все дело в том, что когда таблица большая, содержит в себе большое количество данных, формул и остальных значений – это большая нагрузка на компьютер. Функция «ВПР» требует больше производительности, в то время как сочетание «ИНДЕКС»+«ПОИСКПОЗ» снижает нагрузку и быстрее справляется с получением необходимого результата.
Видеоролик функции Поискпоз в Excel
Как мы сегодня увидели на деле функция ПОИСКПОЗ может быть очень полезной для пользователя в самых разных ситуациях. Она помогает сократить время на поиск информации и выполнение рутинных операций, а также увеличить скорость и эффективность работы.
