Функція ПРОГЛЯД в Excel

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

Сьогодні я продемонструю вам багато варіантів використання.

Тож почнемо!

Функція ПРОГЛЯД

ПРОГЛЯД відносно нова функція, вона доступна тільки в новітніх версіях Excel.

Вона дуже схожа на функцію ПЕРЕГЛЯД, але з деякими змінами.

Але що вона робить? Вона швидко шукає значення в одному стовпчику, за заданими критеріями, і повертає значення з того ж рядка, але іншого стовпчика.

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

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

Як використовувати ПРОГЛЯД?

Чи можу я використовувати функцію ПРОГЛЯД?

Зараз ця функція доступна лише для тих, хто користується Office 365 за платною підпискою.

Тому, якщо ви користуєтеся іншими версіями, варіантів використовувати цю функцію у вас немає.

Якщо у вас вже оформлена платна підписка на Office 365, але функції все одно немає, вам потрібно активувати Office Insider.

Як тільки ви активуєте це, функція з'явиться у програмі.

Синтаксис

Синтаксис функції:

=ПРОСМОТРХ(критерій, діапазон_пошуку, діапазон_результатів, [значення_якщо_не_знайдено, тип_відповідності, [тип_пошуку])

Функція ПРОГЛЯД в Excel

Аргументи:

  1. критерій – критерій, з яким порівнюються осередки;
  2. діапазон_пошуку – діапазон осередків, з яким порівнюватиметься критерій;
  3. діапазон_результатів – діапазон осередків, з якого буде обрано результат;
  4. [значення_якщо_не_знайдено] — результат виконання функції, якщо збіги не знайдені;
  5. тип_відповідності – їх кілька:
    • 0 – повне порівняння;
    • -1 – повний збіг, але вибір саме найменшого значення;
    • 1 – повний збіг, але вибір саме найбільшого значення;
    • 2 – частковий збіг, цього можна досягти за допомогою операторів пошуку;
  • [тип_пошуку] – їх кілька:
    • 1 – стандартне значення (пошук зверху вниз);
    • -1 – Знизу вгору;
    • 2 – бінарне порівняння, діапазон повинен бути відсортований за зростанням;
    • -2 — бінарне порівняння, діапазон має бути відсортований за спаданням.

Варіанти використання

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

Почнемо!

Стандартний приклад виведення значення

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

Функція ПРОГЛЯД в Excel

Ми можемо зробити це так:

=ПРОГЛЯД(F2;A2:A15;B2:B15). (або XLOOKUP (F2, A2: A15, B2: B15) в англ. версії програми).

Функція ПРОГЛЯД в Excel

Готово!

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

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

Ось табличка, де необхідно «взяти» результат зі стовпчика лівіше:

Функція ПРОГЛЯД в Excel

А зробити це ми можемо так:

=ПРОГЛЯД(F2;D2:D15;A2:A15). (або =XLOOKUP(F2,D2:D15,A2:A15) в англ. версії програми).

Функція ПРОГЛЯД в Excel

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

Виведення кількох значень

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

Функція ПРОГЛЯД в Excel

Як це зробити:

=ПРОГЛЯД(F2;A2:A15;B2:D15). (або =XLOOKUP(F2,A2:A15,B2:D15) в англ. версії програми).

Функція ПРОГЛЯД в Excel

Готово!

Ця функція масиву, так що не вдасться видалити осередки з оцінками по другому та третьому предмету, не забувайте про це!

Але ми можемо видалити саму комірку, де була використана формула. Тоді видалятимуться всі ці три оцінки.

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

За декількома критеріями одночасно

Допустимо, у нас є таке завдання:

Функція ПРОГЛЯД в Excel

Отже, ми шукаємо ім'я у першому стовпчику, а назва предмета у першому рядку. Якщо нам буде необхідно поміняти що-небудь у нашій функції (наприклад, назва предмета), жодних проблем не буде. Ми буквально за пару секунд зробимо це та отримаємо правильний результат.

Ось формула:

=ПРОГЛЯД(G1;B1:D1;ПРОГЛЯД(F2;A2:A15;B2:D15))

Функція ПРОГЛЯД в Excel

Що тут відбувається?

Спочатку ми отримуємо масив із усіма оцінками конкретного учня.

У нашому випадку це {21,94,81}.

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

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

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

Якщо нічого не підійшло під критерій

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

Є навіть спеціальний аргумент [якщо_збіг_не_знайдено].

Отже, припустимо, у нас є така табличка:

Функція ПРОГЛЯД в Excel

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

Формула, для нашого завдання, набуде такого вигляду:

=ПРОГЛЯД(F2;A2:A15;B2:B15; “Did not appear”). (або =XLOOKUP(F2,A2:A15,B2:B15, “Did not appear”) в англ. версії програми).

Функція ПРОГЛЯД в Excel

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

Пошук по кількох діапазонах

Розглянемо наступний приклад.

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

Функція ПРОГЛЯД в Excel

Нам потрібно порівняти з нашим критерієм відразу в декількох діапазонах. Але як це зробити?

Отже, ось формула, яка порівнюватиме ім'я учня відразу з двома діапазонами осередків:

=ПРОГЛЯД(A12;A2:A8;B2:B8;ПРОГЛЯД(A12;F2:F8;G2:G8)). (або =XLOOKUP(A12,A2:A8,B2:B8,XLOOKUP(A12,F2:F8,G2:G8)) в англ. версії програми).

Функція ПРОГЛЯД в Excel

Що ми зробили? В аргументі [якщо_збіг_не_знайдено] ми ще раз викликали функцію ПРОГЛЯД для порівняння з іншим діапазоном. Все дуже просто.

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

Знайти останній збіг з критерієм

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

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

Функція ПРОГЛЯД в Excel

Завдання у тому, що вивести значення останнього прийнятого працювати людини.

Формула, яку ми використовуватимемо:

РОСМОТРХ(F1;$B$2:$B$15;$A$2:$A$15;;;-1). (або =XLOOKUP(F1,$B$2:$B$15,$A$2:$A$15,,,-1) в англ. версії програми).

А ця формула дасть останню дату прийому на роботу для кожного відділення компанії:

=ПРОГЛЯД(F1;$B$2:$B$15;$C$2:$C$15;;;-1). (або =XLOOKUP(F1,$B$2:$B$15,$C$2:$C$15,,,-1) в англ. версії програми).

Функція ПРОГЛЯД в Excel

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

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

Отже, у функції ПЕРЕГЛЯД 4 режими збігів (для порівняння, у функції ПЕРЕГЛЯД їх було всього 2).

Я розписував їх у розділі «Синтаксис».

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

Отже, нам потрібно вирахувати комісію продавця:

Функція ПРОГЛЯД в Excel

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

=ПРОГЛЯД(B2;$E$2:$E$6;$F$2:$F$6;0;-1)*B2. (або =XLOOKUP(B2,$E$2:$E$6,$F$2:$F$6,0,-1)*B2 в англ. версії програми).

Функція ПРОГЛЯД в Excel

Ми використовували тип пошуку -1, таким чином пошук буде виконуватись праворуч наліво.

І про правильне сортування можна забути!

Зміна порядку горизонтального пошуку

У минулому прикладі ми розглянули вертикальний пошук, а що ж із горизонтальним?

Те саме, ось приклад:

Функція ПРОГЛЯД в Excel

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

=ПРОГЛЯД(B7;B1:O1;B2:O2). (або =XLOOKUP(B7,B1:O1,B2:O2) в англ. версії програми).

Функція ПРОГЛЯД в Excel

Нічого складного!

Більш складний пошук (комбінації функцій)

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

Отже, у нас є така табличка:

Функція ПРОГЛЯД в Excel

Завдання полягає в тому, щоб знайти учня з максимальною кількістю балів, а також знайти всіх учнів, хто набрав понад 80 балів.

Формула, в такому разі, набуде наступного вигляду:

=ПРОГЛЯД(МАКС(ПРОГЛЯД(G1;$B$1:$D$1;$B$2:$D$15));ПРОГЛЯД(G1;$B$1:$D$1;$B$2:$D$15);$A $2:$A$15). (або =XLOOKUP(MAX(XLOOKUP(G1,$B$1:$D$1,$B$2:$D$15)),XLOOKUP(G1,$B$1:$D$1,$B$2:$D$15), $A$2:$A$15) в англ.версії програми).

Спочатку отримуємо масив усіх оцінок.

За допомогою цієї частини функції: ПРОГЛЯД (G1; $ B $ 1: $ D $ 1; $ B $ 2: $ D $ 15), ця частина формує масив з усіх оцінок з математики. А далі використовуємо функцію МАКС, щоб знайти максимальну оцінку.

А далі цей максимальний бал становить критерієм: ПРОГЛЯД (G1; $ B $ 1: $ D $ 1; $ B $ 2: $ D $ 15).

Загалом і все!

А тепер виводимо кількість учнів, які набрали понад 80 балів:

=РАХУНКИ(ПРОГЛЯД(G1;$B$1:$D$1;$B$2:$D$15);”>80″). (або =COUNTIF(XLOOKUP(G1,$B$1:$D$1,$B$2:$D$15),”>80″) в англ. версії програми).

Функція ПРОГЛЯД в Excel

Спочатку створюється масив даних з оцінками, а потім, результат, використовується у функції РАХУНКИ.

Оператори пошуку

Тут все так само як і в функціях ПРОГЛЯД та ІНДЕКС разом з ПОШУКПОЗ.

Майже так само. Невелика різниця таки є.

Вона полягає в тому, що в ПРОГЛЯД вам необхідно вказати той тип пошуку, який передбачає використання операторів пошуку.

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

Функція ПРОГЛЯД в Excel

Шукаємо капіталізацію компанії за її неповною назвою.

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

=ПРОГЛЯД(“*”&D2&”*”;$A$2:$A$11;$B$2:$B$11;;2). (або =XLOOKUP(“*”&D2&”*”,$A$2:$A$11,$B$2:$B$11,,2 в англ. версії програми).

Функція ПРОГЛЯД в Excel

Ми використовували оператор “*” і за допомогою нього показали нашій функції, що в кінці рядка може бути кілька символів, які ми не знаємо. Відповідно, функція шукала зазначені слова на початку рядка.

Якщо ви хочете використовувати оператори пошуку, вам потрібно вказати тип пошуку “2”. І ніяк інакше.

Виведення останнього значення

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

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

Функція ПРОГЛЯД в Excel

Наше завдання полягає в тому, щоб вивести ім'я останньої компанії у списку та її капіталізацію.

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

=ПРОГЛЯД(“*”;A2:A11;A2:A11;;2;-1). (або =XLOOKUP(“*”,A2:A11,A2:A11,,2,-1) в англ. версії програми).

А ця формула виведення капіталізації:

=ПРОГЛЯД(“*”;A2:A11;B2:B11;;2,-1). (або =XLOOKUP(“*”,A2:A11,B2:B11,,2,-1) в англ. версії програми).

Функція ПРОГЛЯД в Excel

Оскільки ми знову використовуємо оператор пошуку, не забудьте встановити тип пошуку – “2”.

Оскільки ми змінили порядок пошуку на “знизу вгору”, ми отримуємо перше значення з початку пошуку, а пошук виконується знизу вгору. Таким чином ми отримуємо останнє значення.

Як бути, якщо функції ПРОГЛЯД у вас немає?

На даний момент єдиний варіант – купити платну передплату на Office 365.

Якщо ви вже зробили це, просто активуйте Office Insider.

Клацніть на “Файл” і побачите “Office Insider”.

Функція ПРОГЛЯД в Excel

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

Мінус функції ПРОГЛЯД

Як кажуть, функція немає сумісності з іншими версіями Excel.

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

Функція досить нова, тому її ще де немає. Доведеться почекати, перш ніж вона буде у всіх «у ходу». Хоч вона і дуже зручна вже зараз.

Отже, ми розглянули багато прикладів, а також розібрали плюси та мінуси функції!

Сподіваюся, ця стаття була вам корисною!

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