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

Зміст

• Застосування оператора ПОИСКПОЗ
  • Спосіб 1: відображення місця елемента в діапазоні текстових даних
  • Спосіб 2: Автоматизація застосування оператора ПОИСКПОЗ
  • Спосіб 3: використання оператора ПОИСКПОЗ для числових виразів
  • Спосіб 4: використання в поєднанні з іншими операторами
• Питання та відповіді

Одним з найбільш затребуваних операторів серед користувачів 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

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