Функція ПОИСКПОЗ в програмі Microsoft Excel

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

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

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

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

=ПОИСКПОЗ(искомое_значение;просматриваемый_массив;[тип_сопоставления])

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

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

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

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

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

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

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

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

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

1. Виділяємо осередок, в яку буде виводитися обробляється результат. Клацаємо по значку «Вставити функцію» близько рядки формул.
2. Виробляється запуск Майстра функцій. Відкриваємо категорію «Повний алфавітний перелік» або «Посилання та масиви». У списку операторів шукаємо найменування «ПОИСКПОЗ». Знайшовши і виділивши його, тиснемо на кнопку «OK» в нижній частині вікна.
3. Активується вікно аргументів оператора ПОИСКПОЗ. Як бачимо, в даному вікні по числу кількості аргументів є три поля. Нам належить їх заповнити.

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

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

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

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

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

урок: Майстер функцій в Ексель

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

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

1. Для зручності на аркуші додаємо ще два додаткових поля: «Заданий значення» і «Номер». В поле «Заданий значення» вбиваємо то найменування, яке потрібно знайти. Нехай тепер це буде «М'ясо». В поле «Номер» встановлюємо курсор і переходимо до вікна аргументів оператора тим же способом, про який йшла мова вище.
2. У вікні аргументів функції в поле «Шукане значення» вказуємо адресу осередки, в якій вписано слово «М'ясо». У полях «проглядається масив» і «Тип зіставлення» вказуємо ті ж самі дані, що і в попередньому способі - адреса діапазону і число «0» відповідно. Після цього тиснемо на кнопку «OK».
3. Після того, як ми зробили вищевказані дії, в поле «Номер» відобразиться позиція слова «М'ясо» в обраному діапазоні. В даному випадку вона дорівнює «3».
4. Даний спосіб хороший тим, що якщо ми захочемо дізнатися позицію будь-якого іншого найменування, то не потрібно буде кожен раз заново набирати або змінювати формулу. Досить просто в полі «Заданий значення» вписати нову шукане слово замість попереднього. Обробка і видача результату після цього відбудеться автоматично.

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

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

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

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

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

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

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

урок: Сортування і фільтрація даних в Excel

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

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

=ИНДЕКС(массив;номер_строки;номер_столбца)

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

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

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

1. Відсортовуються елементи в стовпці «Сума виручки» по зростанню. Для цього виділяємо необхідний стовпець і, перебуваючи у вкладці «Головна», натискаємо по значку «Сортування і фільтр», а потім в меню натискаємо по пункту «Сортування від мінімального до максимального».
2. Виділяємо осередок в поле «Товар» і викликаємо Майстер функцій звичайним способом через кнопку «Вставити функцію».
3. У вікні Майстра функцій в категорії «Посилання та масиви» шукаємо найменування «ІНДЕКС», виділяємо його і тиснемо на кнопку «OK».
4. Далі відкривається віконце, яке пропонує вибір варіанта оператора ІНДЕКС: для масиву або для посилання. Нам потрібен перший варіант. Тому залишаємо в цьому вікні всі настройки за замовчуванням і тиснемо на кнопку «OK».
5. Відкривається вікно аргументів функції ІНДЕКС. В поле «Масив» вказуємо адресу того діапазону, де оператор ІНДЕКС буде шукати назву продукції. У нашому випадку - це стовпець «Найменування товару».

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

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

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

урок: Функція ІНДЕКС в Excel

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

• ← Обчислення NPV в Microsoft Excel
• Функція ІНДЕКС в програмі Microsoft Excel →