Функція ПОИСКПОЗ в excel

Одним з найбільш затребуваних операторів серед користувачів Excel є функція ПОИСКПОЗ. В її завдання входить визначення номера позиції елемента в заданому масиві даних. Найбільшу користь вона приносить, коли застосовується в комплексі з іншими операторами. Давайте розберемося, що ж собою представляє функція ПОИСКПОЗ. і як її можна використовувати на практиці.

Застосування оператора ПОИСКПОЗ

Синтаксис оператора ПОИСКПОЗ виглядає так:

Тепер розглянемо кожен з трьох цих аргументів окремо.

«Тип зіставлення» вказує точний збіг потрібно шукати або неточне. Цей аргумент може мати три значення: «1». «0» і «-1». При значенні «0» оператор шукає тільки точний збіг. Якщо вказано значення «1». то в разі відсутності точного збігу ПОИСКПОЗ видає найближчий до нього елемент по спадаючій. Якщо вказано значення «-1». то в разі, якщо не виявлено точний збіг, функція видає найближчий до нього елемент по зростанню. Важливо, якщо ведеться запитом не знайдено точного значення, а приблизного, щоб проглядається масив був упорядкований по зростанню (тип зіставлення «1») або зменшенням (тип зіставлення «-1»).

Аргумент «Тип зіставлення» не є обов'язковим. Він може бути пропущеним, якщо в ньому немає потреби. В цьому випадку його значення за замовчуванням дорівнює «1». Застосовувати аргумент «Тип зіставлення». перш за все, має сенс тільки тоді, коли обробляються числові значення, а не текстові.

У разі, якщо ПОИСКПОЗ при заданих налаштуваннях не може знайти потрібний елемент, то оператор показує в осередку помилку «# Н / Д».

При проведенні пошуку оператор не розрізняє регістри символів. Якщо в масиві присутні кілька точних збігів, то ПОИСКПОЗ виводить в осередок позицію самого першого з них.

Спосіб 1: відображення місця елемента в діапазоні текстових даних

Давайте розглянемо на прикладі найпростіший випадок, коли за допомогою ПОИСКПОЗ можна визначити місце зазначеного елемента в масиві текстових даних. Дізнаємося, яку позицію в діапазоні, в якому знаходяться найменування товарів, займає слово «Цукор».

1. Виділяємо осередок, в яку буде виводитися обробляється результат. Клацаємо по значку «Вставити функцію» близько рядки формул.

Активується вікно аргументів оператора ПОИСКПОЗ. Як бачимо, в даному вікні по числу кількості аргументів є три поля. Нам належить їх заповнити.

Так як нам потрібно знайти позицію слова «Цукор» в діапазоні, то вбиваємо це найменування в поле «Шукане значення».

У третьому полі «Тип зіставлення» ставимо число «0». так як будемо працювати з текстовими даними, і тому нам потрібен точний результат.

Після того, як всі дані встановлені, тиснемо на кнопку «OK».

Програма виконує обчислення і виводить порядковий номер позиції «Цукор» у виділеному масиві в тій комірці, яку ми поставили ще на першому кроці даної інструкції. Номер позиції дорівнюватиме «4».

Спосіб 2: автоматизація застосування оператора ПОИСКПОЗ

Вище ми розглянули найпримітивніший випадок застосування оператора ПОИСКПОЗ. але навіть його можна автоматизувати.

1. Для зручності на аркуші додаємо ще два додаткових поля: «Заданий значення» і «Номер». В поле «Заданий значення» вбиваємо то найменування, яке потрібно знайти. Нехай тепер це буде «М'ясо». В поле «Номер» встановлюємо курсор і переходимо до вікна аргументів оператора тим же способом, про який йшла мова вище.

Після того, як ми зробили вищевказані дії, в поле «Номер» відобразиться позиція слова «М'ясо» в обраному діапазоні. В даному випадку вона дорівнює «3».

Даний спосіб хороший тим, що якщо ми захочемо дізнатися позицію будь-якого іншого найменування, то не потрібно буде кожен раз заново набирати або змінювати формулу. Досить просто в полі «Заданий значення» вписати нову шукане слово замість попереднього. Обробка і видача результату після цього відбудеться автоматично.

Спосіб 3: використання оператора ПОИСКПОЗ для числових виразів

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

Ставиться завдання знайти товар на суму реалізації 400 рублів або самий найближчий до цієї суми по зростанню.

1. Перш за все, нам потрібно впорядкувати елементи в стовпці «Сума» по спадаючій. Виділяємо дану колонку і переходимо у вкладку «Головна». Клацаємо по значку «Сортування і фільтр». який розташований на стрічці в блоці «Редагування». У списку вибираємо пункт «Сортування від максимального до мінімального».

Після того, як була сортування проведена, виділяємо клітинку, де буде виводитися результат, і запускаємо вікно аргументів тим же шляхом, про який йшла мова в першому способі.

В поле «Шукане значення» вбиваємо число «400». В поле «проглядається масив» вказуємо координати стовпчика «Сума». В поле «Тип зіставлення» встановлюємо значення «-1». так як ми виробляємо пошук рівного або більшого значення від шуканого. Після виконання всіх налаштувань тиснемо на кнопку «OK».

Результат обробки виводиться в попередньо зазначену осередок. Це позиція «3». Їй відповідає «Картопля». Дійсно, сума виручки від реалізації цього продукту найближча до числа 400 по зростанню і становить 450 рублів.

Аналогічним чином можна провести пошук і навколо позиції до «400» по спадаючій. Тільки для цього потрібно зробити фільтрацію даних по зростанню, а в полі «Тип зіставлення» аргументів функції встановити значення «1».

Спосіб 4: використання в поєднанні з іншими операторами

Найефективніше цю функцію використовувати з іншими операторами в складі комплексної формули. Найбільш часто її застосовують в зв'язці з функцією ІНДЕКС. Даний аргумент виводить в зазначену осередок вміст діапазону заданий по номеру його рядки або стовпці. Причому нумерація, як і щодо оператора ПОИСКПОЗ. виконується не відносно всього листа, а тільки всередині діапазону. Синтаксис цієї функції наступний:

При цьому, якщо масив одновимірний, то можна використовувати тільки один з двох аргументів: «Номер рядка» або «Номер стовпця».

Особливість зв'язки функцій ІНДЕКС та ПОИСКПОЗ полягає в тому, що остання може використовуватися як аргумент першої, тобто, вказувати на позицію рядка або стовпця.

Давайте поглянемо, як це можна зробити на практиці, використовуючи всю ту ж таблицю. У нас стоїть завдання вивести в додаткове поле листа «Товар» найменування товару, загальна сума виручки від якого дорівнює 350 рублям або найближчого до цього значення по спадаючій. Даний аргумент вказано в полі «Приблизна сума виручки на аркуші».

1. Відсортовуються елементи в стовпці «Сума виручки» по зростанню. Для цього виділяємо необхідний стовпець і, перебуваючи у вкладці «Головна». натискаємо по значку «Сортування і фільтр». а потім в меню натискаємо по пункту «Сортування від мінімального до максимального».

Виділяємо осередок в поле «Товар» і викликаємо Майстер функцій звичайним способом через кнопку «Вставити функцію».

Далі відкривається віконце, яке пропонує вибір варіанта оператора ІНДЕКС. для масиву або для посилання. Нам потрібен перший варіант. Тому залишаємо в цьому вікні всі настройки за замовчуванням і тиснемо на кнопку «OK».

В поле «Номер рядка» буде розташовуватися вкладена функція ПОИСКПОЗ. Її доведеться вбити вручну, використовуючи синтаксис, про який йдеться в самому початку статті. Відразу записуємо назву функції - «ПОИСКПОЗ» без лапок. Потім відкриваємо дужку. Першим аргументом даного оператора є «Шукане значення». Воно розташовується на аркуші в поле «Приблизна сума виручки». Вказуємо координати комірки, що містить число 350. Ставимо крапку з комою. Другим аргументом є «проглядається масив». ПОИСКПОЗ буде переглядати той діапазон, в якому знаходиться сума виручки і шукати найбільш наближену до 350 рублям. Тому в даному випадку вказуємо координати стовпчика «Сума виручки». Знову ставимо крапку з комою. Третім аргументом є «Тип зіставлення». Так як ми будемо шукати число рівне заданому або найближче менше, то встановлюємо тут цифру «1». Закриваємо дужки.

Третій аргумент функції ІНДЕКС «Номер стовпця» залишаємо порожнім. Після цього тиснемо на кнопку «OK».

Як бачимо, функція ІНДЕКС за допомогою оператора ПОИСКПОЗ в заздалегідь зазначену осередок виводить найменування «Чай». Дійсно, сума від реалізації чаю (300 рублів) найближче по спадаючій до суми 350 рублів з усіх наявних в оброблюваної таблиці значень.

Якщо ми змінимо число в поле «Приблизна сума виручки» на інше, то відповідно автоматично буде перераховано і вміст поля «Товар».

Як бачимо, оператор ПОИСКПОЗ є дуже зручною функцією для визначення порядкового номера зазначеного елемента в масиві даних. Але користь від нього значно збільшується, якщо він застосовується в комплексних формулах.

Ми раді, що змогли допомогти Вам у вирішенні проблеми.