Комбінація функцій ІНДЕКС та ПОШУКПОЗ в Excel

В Excel є понад 400 різних функцій.

Часто, ці функції беруть він основну частину роботи, вам залишається їх правильно використовувати, далі вони зроблять все розрахунки самі.

А найдивовижніше – комбінації цих функцій. Одна з найпопулярніших комбінацій – функції ІНДЕКС та ПОШУКПОЗ.

Мені особисто дуже подобається комбінація цих функцій, я дуже часто користуюся їй.

Ця стаття буде присвячена лише комбінації цих функцій. Ви можете сказати, але навіщо використовувати цю комбінацію, якщо є функція ПЕРЕГЛЯД? І так, дійсно, ПЕРЕГЛЯД непогана функція, але вона навіть близько не дає тих можливостей, порівняно з ІНДЕКС і ПОШУКПОЗ.

Отже, спочатку давайте розглянемо що це за функції окремо.

ІНДЕКС

Найпростіший варіант пояснити – використати приклад. Якщо ви розумієте, що таке ширина і довгота на карті — вам буде дуже просто уявити. Функція індексу просто шукає значення за заданими точками даних. Грубо кажучи, ви повідомляєте координати місця, яке потрібно знайти і вона знаходить його.

Як із адресою квартири, наприклад. Як би багато не було адрес, якщо ви повідомите повну — вас завжди можна буде знайти.

Отже, щоб знайти вас потрібна повна адреса, а щоб знайти щось в Excel потрібна адреса рядка та стовпчика.

Так працює ця функція.

Ось приклад, рядок 9, стовпчик 5 видасть такий результат:

Комбінація функцій ІНДЕКС та ПОШУКПОЗ в Excel

Функція досить проста і її рідко де можна застосувати, але…

Ми, звичайно ж, можемо комбінувати її з іншими функціями! У такому разі вона буде дуже доречною

Ось синтаскіс:

= ІНДЕКС (діапазон; рядок; стовпчик)

  • Діапазон – діапазон осередків, серед якого буде виконано пошук;
  • Рядок/стовпчик – аргументу може бути відразу 2, а може бути тільки 1, наприклад, рядок.
  • Можна також вказувати додаткові діапазони.

Найчастіше додаткові діапазони не використовуються, але буває.

ПОШУКПОЗ

А ця функція знаходить адресу осередку за заданими критеріями.

Як вона це робить?

За збігами

Наприклад, у вас є наступна табличка і ви шукаєте осередок, в якому є слово “Mark”, це буде виглядати так:

Комбінація функцій ІНДЕКС та ПОШУКПОЗ в Excel

Результатом виконання функції буде “3”, т.к. слово знаходиться в 3 осередку.

Важливо знати, що функція перевіряє кожну комірку зверху вниз, порівнює її з критерієм, вказаним під час виклику, і дає відповідь.

Ось синтаксис

=ПОШУКПОЗ(критерій;діапазон;тип)

  • Критерій – критерій, з яким виконуватиметься порівняння кожного з осередків;
  • Діапазон – діапазон осередків, серед якого виконуватиметься порівняння;
  • Тип — лише три типи, розглянемо далі.

Тип

Отже, що таке тип?

Пояснення:

  • Якщо тип “0” – функція шукатиме точний збіг;
  • Якщо тип «1» — функція шукатиме найбільше значення, щоб викликати функцію з таким аргументом, обов'язково відсортуйте дані зростання;
  • Якщо тип «-1» — функція шукатиме найменше значення, щоб викликати функцію з таким аргументом, обов'язково відсортуйте дані зі спадання.

Грубо кажучи:

  • Функції ІНДЕКС «на вході» потрібна адреса комірки, результатом буде її значення;
  • А функція ПОШУКПОЗ шукає адресу потрібного осередку за критеріями.

Комбінація

Отже, тепер ви знаєте, що робить кожна з цих функцій. Саме час перейти до спільного використання, тобто, до комбінації.

Оскільки найкращий спосіб щось пояснити — показати приклади, я так і вчиню.

У мене заготовлено для вас 10 прикладів використання і почнемо ми, звичайно, з простого.

Перший приклад використання – ПОШУК

Отже, здійснимо простий пошук.

Допустимо, у нас є така табличка:

Комбінація функцій ІНДЕКС та ПОШУКПОЗ в Excel

Припустимо, що нам потрібно знайти оцінки Джима.

Нижче наведено формулу, за допомогою якої це можна легко зробити:

=ІНДЕКС($A$2:$B$11;ПОШУКПОЗ(“Jim”,$A$2:$A$11;0);2)

Комбінація функцій ІНДЕКС та ПОШУКПОЗ в Excel

І так, справді. Тут можна просто використовувати функцію ПЕРЕГЛЯД. Але краще відразу звикати використовувати більш «гнучкі» методи. Ця комбінація – одна з таких.

Це був простий приклад і завдання легко виконати за допомогою використання звичайної функції ПРОГЛЯД.

А тепер давайте подивимося в чому переваги нашої комбінації, порівняно з функцією ПЕРЕГЛЯД.

Допустимо, тепер у нас така сама табличка, але в іншому форматі:

Комбінація функцій ІНДЕКС та ПОШУКПОЗ в Excel

Отже, як у цій ситуації використовувати нашу комбінацію?

Ось формула:

=ІНДЕКС($B$1:$K$2;2;ПОШУКПОЗ(“Jim”;$B$1:$K$1;0))

Комбінація функцій ІНДЕКС та ПОШУКПОЗ в Excel

Якщо будете використовувати такий самий метод, не забудьте змінити діапазон.

У такому разі використовувати ПЕРЕГЛЯД буде неможливо.

А комбінація функцій ІНДЕКС та ПОШУКПОЗ легко справляється з поставленим завданням.

Отримуємо дані зі стовпчика зліва

Ця ситуація зустрічається дуже часто.

Завдання: вивести дані зі стовпчика ліворуч від стовпчика зі значеннями.

Як на картинці:

Комбінація функцій ІНДЕКС та ПОШУКПОЗ в Excel

Нам потрібно вивести продаж продавця «Майкл».

Функція ПЕРЕГЛЯД, спочатку, не призначена для таких завдань. Але чи можна якось за допомогою неї виконати це завдання?

Можна, але формула буде величезною та незрозумілою.

Найкраще, у такій ситуації, використати нашу комбінацію.

Ось формула:

=ІНДЕКС($A$2:$C$11;ПОШУКПОЗ(“Michael”;C2:C11;0);2)

Комбінація функцій ІНДЕКС та ПОШУКПОЗ в Excel

Функція ПЕРЕГЛЯД виводить значення тільки зі стовпчика праворуч від стовпчика зі значеннями

Отримуємо дані з кількох стовпчиків одночасно

Отже, це були приклади, коли ми робили дії тільки з одним стовпчиком. Але що якщо потрібно робити ці ж дії, але з кількома стовпчиками відразу? Адже функція ПЕРЕГЛЯД явно, у такій ситуації, нам не «помічник».

А ось комбінація функцій ІНДЕКС та ПОШУКПОЗ все ще справляється із завданням.

Припустимо, у нас є така табличка, нам потрібно вивести оцінки одразу з усіх предметів:

Комбінація функцій ІНДЕКС та ПОШУКПОЗ в Excel

Давайте використовуємо нашу комбінацію та виконаємо завдання!

Формула виглядатиме так:

=ІНДЕКС($B$2:$D$11;ПОШУКПОЗ($F$3;$A$2:$A$11;0);ПОШУКПОЗ(G$2;$B$1:$D$1;0))

Комбінація функцій ІНДЕКС та ПОШУКПОЗ в Excel

Як це працює?

Функція ІНДЕКС визначає діапазон.

Перша функція ПОШУКПОЗ шукає позицію значення імені поточного учня.

Друга функція ПОШУКПОЗ шукає позицію значення з різних предметів і віддає нам.

Все це передається в ІНДЕКС, і вона виводить нам ім'я та оцінки учня.

Найзручніше те, що функція залишається динамічною. Тобто, якщо, наприклад, оцінки учнів зміниться, функція зробить перерахунок.

І навіть якщо ви поміняєте місцями оцінки або предмети, функція зробить перерахунок і результат все одно залишиться правильним.

Отримуємо дані виконуючи пошук за кількома критеріями одночасно

Допустимо, у нас є така табличка:

Комбінація функцій ІНДЕКС та ПОШУКПОЗ в Excel

Потрібно вивести оцінки тільки для учня з ім'ям “Mark” та прізвищем “Long”.

Ім'я та прізвище знаходяться у різних стовпчиках, що ж робити?

Як варіант – можна створити стовпчик і перенести туди одразу і ім'я, і прізвище, а потім виконати розрахунки. Але це досить довго.

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

Ось формула:

=ІНДЕКС($C$2:$C$11;ПОШУКПОЗ($E$3&”|”&$F$3;$A$2:A11&”|”&$B$2:$B$11;0))

Комбінація функцій ІНДЕКС та ПОШУКПОЗ в Excel

Як це працює?

Функція ПОШУКПОЗ як би поєднує значення імені та прізвища, а потім порівнює їх із зазначеним критерієм.

І жодні додаткові стовпчики для розрахунків не потрібні.

Це завдання можна виконати за допомогою функції ПРОГЛЯД, але вам потрібен буде додатковий стовпчик для розрахунків.

Отримуємо дані з кількох стовпчиків і підсумовуємо їх

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

Але якщо потрібно обчислити значення відразу кількох стовпчиків, для осередків, які відповідають критеріям, а потім ще й підсумовувати їх? Чи можна зробити це в одній формулі?

Звичайно ж так, як я говорив раніше, комбінація функцій ІНДЕКС та ПОШУКПОЗ дуже гнучка, давайте подивимося як це зробити.

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

Спочатку ми отримаємо значення всіх оцінок, а потім просто складемо їх.

Ось табличка:

Комбінація функцій ІНДЕКС та ПОШУКПОЗ в Excel

Ось формула:

=СУМ(ІНДЕКС($B$2:$D$11;ПОШУКПОЗ($F$4;$A$2:$A$11;0);0))

Комбінація функцій ІНДЕКС та ПОШУКПОЗ в Excel

Як це працює?

Зверніть увагу, номер стовпчика, у нашому випадку, “0”.

Суть у тому, що якщо ми зробили так, то функція поверне всі значення, а це те, що нам і потрібно.

Так, створюється масив значень із трьох оцінок.

Якщо ми залишимо його так, то буде помилка. Оскільки функція призначена виведення масиву як результат.

Але він, звичайно ж, нікуди не дівається. А просто залишається у пам'яті Excel. Відповідно ми можемо працювати з ним, просто вивести на екран не можемо.

Отже, просто використовуємо функцію СУМ.

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

Неповна відповідність

Отже, раніше ми шукали повністю співпадаючий критерій за допомогою функції пошуку. А тепер розглянемо ту ситуацію, коли критерій не повністю відповідатиме значенню в осередку.

Але як це не повністю?

Текстові значення завжди повна відповідність, запам'ятайте це. А от цифри можуть і не повністю відповідати.

Як завжди, поясню все на прикладі.

Допустимо, у вас є учні. Вони складали іспит і отримали бали, а тепер із цих балів потрібно поставити їм оцінки.

Ось приклад:

Комбінація функцій ІНДЕКС та ПОШУКПОЗ в Excel

Якщо учень набрав від 0 до 33 балів – оцінка F тощо.

Формула виглядатиме так.

=ІНДЕКС($F$3:$F$8;ПОШУКПОЗ(B2;$E$3:$E$8;1);1)

Комбінація функцій ІНДЕКС та ПОШУКПОЗ в Excel

Як це працює?

Зверніть увагу, що ми використовували тип «1» у функції пошуку.

Як тільки функція знайде значення, що дорівнює або менше, вона поверне адресу осередку з оцінкою.

Якщо учень набрав 85 балів – функція поверне 5. А індекс вже підставить адресу осередку з оцінкою B і поставить її учневі.

Не забудьте, оскільки ми використовуємо тип “1”, оцінки мають бути відсортовані за зростанням.

Те, що ми описали вище, можна отримати за допомогою функції ПРОГЛЯД

=ПРОГЛЯД(B2;$E$3:$F$8;2;ІСТИНА)

Якщо ми змінимо порядок на спадний, щодо оцінок — результат залишиться тим самим.

Комбінація функцій ІНДЕКС та ПОШУКПОЗ в Excel

Тільки не забудьте змінити тип у функції пошуку на «-1».

Формула:

=ІНДЕКС($F$3:$F$8;ПОШУКПОЗ(B2;$E$3:$E$8;-1);1)

Враховуємо регістр

Досі ми не звертали уваги на регістр. Але що, якщо в задачі необхідно враховувати його?

Зараз поясню що це означає: припустимо у функції ПОШУКПОЗ у нас є критерій ДЖИМ. У такому разі «ДЖІМ», «Джим» та «джим» будуть правильними варіантами. Але якщо вам потрібно зробити так, щоб правильним варіантом був тільки, наприклад, «Джим»?

Вам може здатися, що це абсолютно марна інформація, але, повірте мені, при роботі з великими обсягами даних буває не таке.

Допустимо, у нас є така табличка:

Комбінація функцій ІНДЕКС та ПОШУКПОЗ в Excel

Як ви можете помітити, Джим тут два, один з великої літери, другий з маленької.

То як же нам розділити їх і вибрати якогось певного?

Це формула масиву, тому підтверджуємо введення CTRL+SHIFT+ENTER:

=ІНДЕКС($B$2:$B$11;ПОШУКПОЗ(ІСТИНА;СПІЛ(D3;A2:A11);0);1)

Комбінація функцій ІНДЕКС та ПОШУКПОЗ в Excel

Як це працює?

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

Шукаємо найближче до критерію чисельне значення

Отже, з цього моменту буде трохи складніше.

Допустимо, у вас є така табличка:

Комбінація функцій ІНДЕКС та ПОШУКПОЗ в Excel

Точного збігу за необхідним досвідом роботи ви не знайдете, але вам потрібно знайти найкращого кандидата, грубо кажучи, у нього повинен бути найближчий до необхідного досвіду роботи.

Ось формула, яка виконає це завдання:

=ІНДЕКС($A$2:$A$15;ПОШУКПОЗ(МІН(ABS(D2-B2:B15)),ABS(D2-$B$2:$B$15),0))

Комбінація функцій ІНДЕКС та ПОШУКПОЗ в Excel

Отже, ми звичайно ж розглянемо як формула дає нам потрібний результат, але, щоб зрозуміти все детальніше – вам потрібно зрозуміти як би ви робили це руками.

Як би я зробив? Я б брав кожного кандидата окремо та вважав різницю між його досвідом роботи та необхідним. Вийшла б табличка (масив) даних, після я знайшов би того, чий досвід найбільш близький до необхідного і вибрав би його.

Те саме робить наша комбінація функцій.

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

Комбінація функцій спільно з операторами пошуку

Ще один цікавий приклад – використання нашої комбінації функцій разом із операторами пошуку («*», «?» тощо).

Допустимо, у нас є така табличка:

Комбінація функцій ІНДЕКС та ПОШУКПОЗ в Excel

Нам потрібно, виходячи з імені обчислити Mkt Cap. Але, як ви могли помітити, імена компаній збігаються не повністю. У стовпчику зліва написані їхні повні назви, а справа звичні людям.

Але дані ми без проблем отримаємо, просто використовуватимемо для цього оператор («*»).

Ось формула:

=ІНДЕКС($B$2:$B$10;ПОШУКПОЗ(D2&”*”;$A$2:$A$10;0);1)

Комбінація функцій ІНДЕКС та ПОШУКПОЗ в Excel

Як це працює?

Оскільки ми шукаємо повний збіг, а часткове, ми використовуємо функцію ПОШУКПОЗ разом із («*»).

Цей оператор означає те, що функція ПОШУКПОЗ шукатиме Apple і будь-яке продовження рядка.

І таким нехитрим чином вона набуває значення Apple Inc, тому що знайшла Apple і продовження рядка.

Якщо ви хочете знайти рядок, де Apple буде в кінці – використовуйте Apple, а якщо не знаєте де конкретно знаходиться слово, або воно знаходиться посередині, то можна використовувати Apple.

Пошук за кількома критеріями

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

Що це означає?

Зі звичайним пошуком все більш-менш зрозуміло, але що за багатосторонній пошук?

Одразу приклад, припустимо, нам потрібно не тільки дізнатися оцінки учня з конкретного предмета, але й конкретизувати це, наприклад, на проміжному іспиті?

Комбінація функцій ІНДЕКС та ПОШУКПОЗ в Excel

Формула виглядатиме так:

=ІНДЕКС(($B$3:$D$7;$B$11:$D$15;$B$19:$D$23);ПОШУКПОЗ($F$5;$A$3:$A$7;0);ПОШУКПОЗ(G$4 ;$B$2:$D$2;0);(ЯКІ(G$3=”Unit Test”;1;ЯКЛИ(G$3=”Mid Term”;2;3))))

Комбінація функцій ІНДЕКС та ПОШУКПОЗ в Excel

Ця формула виконувала пошук відразу за 3 критеріями: імені учня, предмету та типу іспиту.

Як це працює?

  • ($B$3:$D$7;$B$11:$D$15;$B$19:$D$23): три діапазони для пошуку всіх даних;
  • ПОШУКПОЗ($F$5;$A$3:$A$7;0): Пошук учня на ім'я;
  • ПОШУКПОЗ(G$4,$B$2:$D$2,0): Пошук предмета за назвою;
  • Основна хитрість знаходиться в цій частині формули. Якщо іспит першого типу, повернеться один, а потім ІНДЕКС буде використовувати перший масив даних. Якщо другий тип, то повернеться два і таке інше.

Особисто мені таке зустрічалося рідко, але все одно вам краще знати, як працювати з цим.

Чому комбінація функцій ІНДЕКС і ПОШУКПОЗ набагато краще, ніж функція ПРОГЛЯД

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

І справді, найчастіше нашу комбінацію використовувати набагато зручніше.

Я також іноді використовую і функцію ПЕРЕГЛЯД, вона дуже проста і швидко видає результат, звичайно якщо завдання підходить за всіма умовами для використання функції ПЕРЕГЛЯД. Але у складніших ситуаціях, ваш вихід — комбінація функцій ІНДЕКС та ПОШУКПОЗ.

Однак, якщо необхідно зробити звичайний пошук — найкраще підійде ПЕРЕГЛЯД.

Комбінація ІНДЕКС і ПОШУКПОЗ це як функція ПРОГЛЯД тільки більш «прокачена»

Отже, пройдемося з причин, чому наша комбінація краща.

Їй все одно де потрібно шукати в стовпчику ліворуч або праворуч.

Якщо вам потрібно вивести значення ліворуч від того стовпчика, в якому ви шукали – ви не зробите це за допомогою ПЕРЕГЛЯД.

І так ви можете комбінувати ПЕРЕГЛЯД з будь-якими іншими функціями і все-таки знайти за допомогою неї значення, але запевняю вас, ви витратите купу часу даремно.

Наша комбінація не проблема заголовки в рядках

А ось для функції ПЕРЕГЛЯД це велика проблема.

Перегляд працює лише з вертикальними табличками і ніяк інакше.

Перегляд не може виконуватися, якщо дані відсортовані за спаданням

Одне скажу на захист ПЕРЕГЛЯД, в області неповної відповідності критерію вони на одному рівні.

Однак і тут перша функція має мінус. Вона не може працювати з даними, які відсортовані за спаданням.

Для комбінації функцій ІНДЕКС та ПОШУКПОЗ немає різниці де розташовані дані

Наприклад, у вас є така табличка:

Комбінація функцій ІНДЕКС та ПОШУКПОЗ в Excel

Якщо ви для пошуку значень у ній використовуєте ПЕРЕГЛЯД, а потім видаляєте якийсь стовпчик — все відразу ж ламається. Просто тому, що функція працює за таким принципом.

А ось якщо ви виводите значення за допомогою ІНДЕКС + ПОШУКПОЗ – жодних проблем не буде, тому що ви можете зробити адресу стовпчика динамічною.

Також звичайно можна зробити і комбінуючи ПОШУКПОЗ і ПРОГЛЯД, але у цієї комбінації все одно набагато менше можливостей.

Але функція ПРОГЛЯД набагато простіше

Функція ПРОГЛЯД одна з найпопулярніших в Excel. Я думаю це тому, що вона дуже проста. Максимум у ній – 4 аргументи, жодних проблем при виклику бути не може. Мінуси є – це правда, але якщо ви робите звичайний пошук – використання цієї функції це найоптимальніший варіант.

Набагато швидше вивчити і використовувати ПЕРЕГЛЯД для новачка, в цій суперечці немає.

Отже, ми розглянули досить багато прикладів сьогодні, а також я розповів вам у чому різниця між ПРОГЛЯД та комбінації функцій ІНДЕКС та ПОШУКПОЗ.

Сподіваюся, що ця стаття допомогла вам!

nBook
Додати коментар