Пользователям пакета офисных приложений «MS Office» приходится сталкиваться с трудностями в понимании функций программ. Особенно это касается возможностей, представленных в «Microsoft Excel». Одной из них является функция «ПОИСКПОЗ».
Что это за функция, зачем используется
Функция возвращает в поле результата расположение, в котором находится искомое значение. Суть функции заключается в том, чтобы найти значение в выбранном промежутке ячеек и передать в поле вывода результата его расположение.
Если ввести слово «макароны», затем выбрать промежуток из адресов (например, N77:N90) и по формуле отправить запрос, то в качестве результата будет возвращен адрес ячейки, содержащей в себе это слово.
Особенности функции
Функция поиска имеет ряд особенностей:
- при использовании происходит возврат только относительного положения искомого значения;
- производит поиск вне зависимости от регистра;
- при наличии нескольких совпадений в массиве, возвращается лишь первое попавшееся;
- при отсутствии совпадений возвращается ошибка «#Н/З».
Синтаксис формулы
При использовании формулы важно понимать ее особенностей и точно соблюдать синтаксис.
Сама формула выглядит так: ПОИСКПОЗ (Значение_для_поиска; ПромежутокЗначений; Сопоставление).
Расшифруем ее параметры:
- «Значение_для_поиска» — искомый аргумент (число, слово, символ и т.п.).
- «ПромежутокЗначений» — массив из ячеек, в которых происходит поиск.
- «Сопоставление» — задает соответствие, по которому будет происходить поиск:
- «1»– поиск происходит до наибольшего. Массив при этом, должен быть упорядочен по возрастанию;
- «0»– для точного поиска;
- «-1»– противоположно «1», проводит поиск наименьшего. Массив должен быть упорядочен по убыванию.
ПОИСКПОЗ в Excel в примерах
По одному критерию
Для поиска значений в столбце возьмем, к примеру ПОИСКПОЗ («Дарья»; A1:A9; 1). При подстановке фразы расположенной в B1, и поиске в промежутке A1:A9, в место, где используется формула, будет возвращено его местоположение. Искомое слово находится в пятой ячейке, поэтому возвращено «5».
В этом случае формула =ПОИСКПОЗ(А3;В1:I1). Поскольку число 300 находится в третьем столбце, будет возвращена «3».
По двум столбцам
Для поиска значений в диапазоне ячеек по определенному условию воспользуемся таблицей автомобилей в отделах.
Названия авто и отделов дублируются в массиве, но не попарно. Отыщем первый попавшийся Форд:
Для того, чтобы конкретизировать запрос и найти авто из другого отдела нужно в ячейке B16 ввести основной критерий, а в C16 ввести критерий, которые сужает область, а в формуле, в «искомое значение» вписать номера ячеек c входными данными вот так: «B16&C16». Такие же манипуляции провести с массивом, в котором происходит поиск. То есть, дописать к основному «B2:B13» промежутку знак «&» и ввести второй промежуток «C2:C13».
Удерживая «CTRL+SHIFT+ENTER» произвести операцию.
Текстовые значения
Для такого типа операций обязательно нужно заключать слово в кавычки (например, “Слон”), иначе будет возвращена ошибка.
Числа
С числами все просто. Ввести число в “Искомое значение”, затем указать промежуток и запустить выполнение. В зависимости от типа сопоставления программа выдаст результат.
Подстановочные знаки
Среди прочего, есть возможности распознавания знаков. Происходит это вот так:
- «?» – является заменой для любого символа.
- «*» – замена любой последовательности символов.
Работает подстановка только в случае, если тип сопоставления равен «0».
Например, если нужно найти «Восток», в строке можно ввести «вост?к» и программа автоматически найдет совпадения и выдаст результат.
Для замены нескольких символов следует вводить в поле сочетание «В*к». Но в этом случае будут найдены все слова, которые начинаются на «В» и заканчиваются на «К».
Поиск с учетом регистра
Так как формула не различает изменения в регистре, следует внести некоторые изменения:
ПОИСКПОЗ (ИСТИНА; СОВПАД (ИскомоеЗначение;Просматриваемый_Массив); 0)
Суть формулы в том, что «СОВПАД» позволяет найти точные совпадения в таблице. Если найденный элемент в таблице соответствует критериям – его расположение возвращается в качестве результата. Для обработки требуется нажать комбинацию «Ctrl+Shift+Enter».
Нужные данные расположены в E1, промежуток, в котором его нужно найти — A2:A8.
Совмещение с ИНДЕКС
Ячейка, в которой используется функция «ИНДЕКС» принимает значение, которое она получает в результате использования «ПОИСКПОЗ».
Формула выглядит вот так:
=ИНДЕКС (столбец для возвращения, ПОИСКПОЗ (значение, столбец, 0)).
Например, имеется таблица населения нескольких стран:
Найдем население Нигерии с использованием формулы:
=ИНДЕКС (промежуток1; ПОИСКПОЗ («ИскомоеЗначение»; ПромежутокЗначений2; 0)), где:
- «промежуток1» — столбец с количеством населения (C2:C11);
- «промежуток2» — столбец с названием страны (B2:B11).
Далее, ПОИСКПОЗ проводит поиск «Искомое значение» (Нигерия) среди тех, что расположены в ячейках B2:B11. Будет возвращено «8», ведь в списке Нигерия находится на восьмом месте.
Число 7 передается во второй параметр функции «ИНДЕКС» и указывает на ячейку под соответствующим номером из промежутка (C8). Результат: