Як використовується функція ПОИСКПОЗ в excel, зрозумілий excel
У цій статті ми поговоримо і розглянемо закладені можливості, і варіанти як застосовується функція ПОИСКПОЗ. Ця функція є в деяких випадках альтернативою, в деяких випадках, функції ВПР в можливостях вертикального пошуку значень. У деяких випадках функція ПОИСКПОЗ більш гнучка і має свої особливості, які зводять її на перше місце.
Сінтаксісфункціі ПОИСКПОЗ
Як видно з прикладу функція ПОИСКПОЗ має наступний синтаксис, який будемо розглядати більш, докладніше:
- 1 або ж без аргументу - буде шукати максимальне значення, яке дорівнює або ж менше шуканого. В обов'язковому порядку масив. який переглядає функція ПОИСКПОЗ. ви повинні впорядкувати по зростанню, від меншого до більшого.
- 0 - повертає перше ж значення, яке відповідає шуканого. Цей аргумент дозволяє зробити точний пошук.
- -1 - цей аргумент знайде саме найменше значення, яке дорівнює або більше, ніж значення, яке шукаєте. У цьому випадку дані потрібно впорядкувати за спаданням від великого до малого.
Може бути, користь, яку приносить функція ПОИСКПОЗ, може і здатися сумнівною, але зауважу що, ця функція шукає і повертає відносне положення в масиві значення, яке ми шукаємо. А це значення, є основним для аргументів функції ІНДЕКС. яку можна використовувати в зв'язці з функцією ПОИСКПОЗ.
Переваги функції ПОИСКПОЗ
Першим перевагою при використанні функції ПОИСКПОЗ і її комбінації з іншими функції (наприклад, функція ІНДЕКС), це можливість пошуку справа наліво. Це відмінна риса від функції ВПР, так як вона не може переглядати вліво.
Другою перевагою є можливість видаляти і додавати стовпчики в таблицю пошуку. Я маю на увазі, безпеку цього процесу для роботи функції ПОИСКПОЗ. видалення не впливає на коректність роботи формули. А ось формула ВВР може повертати помилки або просто не працювати, так як синтаксис формули вимагає вказувати весь діапазон, а також номер стовпчика для вилучення значення.
Третім перевагою - це відсутність обмеження на довжину значення, яке шукає функції ПОИСКПОЗ, в 255 символів. Обмеження для неї немає. А в функції ВПР, значення, яке ви шукаєте, і якщо воно перевищує зазначену величину, то ви отримаєте помилку # значить.
Приклади роботи функції ПОИСКПОЗ
Перейдемо від теорії до практики і розглянемо кілька прикладів, як працює функція ПОИСКПОЗ.
- Знайти СРЗНАЧ, МАКС, МІН за допомогою комбінації функцій ПОИСКПОЗ і ІНДЕКС
- Пошук функцією ПОИСКПОЗ з лівого боку
- Виробляємо пошук за кількома критеріями
Знайти СРЗНАЧ, МАКС, МІН за допомогою комбінації функцій ПОИСКПОЗ і ІНДЕКС
Функція ПОИСКПОЗ дозволяє вкладати в себе інші функції, і за допомогою цієї можливості ви можете знайти максимальне, мінімальне або самий найближчий до середнього значення (статистичні функції), приклади:
Кожна функція повертає потрібне нам значення.
Раніше я говорив, що функція ВПР не дружить з лівою стороною, а значить, будемо використовувати функцію ПОИСКПОЗ в тандемі з функцією ІНДЕКС. так як вони більш гнучкі і їм не грає ролі місце розташування стовпчика. Розглянемо на прикладі вибору, на якому місці по продажах варто «Білорусь», і будемо використовувати для цього наступну формулу:
Розглянемо формулу, більш, докладніше. По перше. формула ПОИСКПОЗ ( «Білорусь»; $ D $ 2: $ D $ 9; 0) знаходить положення країни в списку. По-друге. функцією ІНДЕКС діапазону $ I $ 2: $ I $ 9 з якого буде проводитися витяг значення. По-третє. з'єднаємо всі дві частини формули і отримаємо потрібний нам результат.
Увага! При вказівці діапазонів в функції ПОИСКПОЗ, для поліпшення і надійності формули бажано завжди використовувати абсолютні посилання.
Виробляємо пошук за кількома критеріями
Отже, зробимо пошук потрібних нам значень за кількома критеріями без додаткових рухів тіла, на відміну від функції ВПР якої потрібно створювати додатково допоміжний стовпчик. Будемо шукати за двома стовпчиків: «Країна» і «Продукт». складність полягає в тому, що продукт будуть поставлятися в різні країни і дані в таблицях, розташовані в довільному порядку.
Наступна формула вирішить нашу проблему:
Якщо розібрати детально, цю складнішу формулу, то описати можна так: почнемо з функції ПОИСКПОЗ, шукаємо ми значення 1. а масив нашого пошуку, є результатом множення, а саме, береться значення в першому стовпчику «Країна» в Таблица1 і порівнюємо з іменами всіх країн в табліце2. Якщо було знайдено збіг, формула повертає 1 або ІСТИНА. а якщо немає, то 0 або БРЕХНЯ. Після робимо те ж саме для стовпчика «Продукти». Після всього цього перемножуємо результати і якщо збігу знайдені результат буде 1. якщо ж ні, то отримаємо - 0. Отже, функція ПОИСКПОЗ повертає позицію в разі виконання обох критеріїв. Обов'язково використовуємо у формулі третій аргумент «3». так як була вказана вся таблиця і потрібно уточнення, з якого ж стовпчика потрібно витягти значення, в нашому випадку стовпчик був третім. Ще зауважу, що це формула масиву, тому ми укладаємо її в фігурні дужки і вводимо «гарячої» комбінацією Ctrl + Shift + Enter.
Хтось може сидіти в тіні сьогодні тільки тому, що хтось свого часу посадив дерево.
Уоррен Баффетт