30 функцій Excel за 30 днів: LOOKUP

Вчора на марафоні 30 функцій Excel за 30 днів ми отримали задоволення від функції РЕПТ (ПОВТОРІТЬ), створюючи діаграми всередині клітинки та використовуючи їх для простого підрахунку. Сьогодні понеділок, і нам знову пора одягнути капелюх мислителя.

На 16 день марафону ми вивчатимемо функцію ПОГЛЯД (ПЕРЕГЛЯД). Це близький друг ВЛООКУП (VLOOKUP) і ПОСЛУГИ (GPR), але працює він трохи інакше.

Отже, вивчаємо теорію і перевіряємо функцію на практиці ПОГЛЯД (ПЕРЕГЛЯД). Якщо у вас є додаткова інформація чи приклади використання цієї функції, поділіться ними в коментарях.

Функція 16: ПОШУК

функція ПОГЛЯД (LOOKUP) повертає значення з одного рядка, одного стовпця або з масиву.

Як я можу використовувати функцію LOOKUP?

функція ПОГЛЯД (LOOKUP) повертає результат залежно від значення, яке ви шукаєте. З його допомогою ви зможете:

  • Знайдіть останнє значення в стовпці.
  • Знайдіть останній місяць з негативними продажами.
  • Перетворюйте досягнення студентів із відсотків у буквені оцінки.

LOOKUP синтаксису

функція ПОГЛЯД (LOOKUP) має дві синтаксичні форми – вектор і масив. У векторній формі функція шукає значення в заданому стовпці або рядку, а у формі масиву вона шукає значення в першому рядку або стовпці масиву.

Векторна форма має такий синтаксис:

LOOKUP(lookup_value,lookup_vector,result_vector)

ПРОСМОТР(искомое_значение;просматриваемый_вектор;вектор_результатов)

  • lookup_value (lookup_value) – може бути текстом, числом, логічним значенням, назвою або посиланням.
  • пошуковий_вектор (lookup_vector) – діапазон, що складається з одного рядка або одного стовпця.
  • результат_вектора (вектор_результату) – діапазон, що складається з одного рядка або одного стовпця.
  • діапазони аргументів пошуковий_вектор (вектор пошуку) і результат_вектора (result_vector) має бути однакового розміру.

Форма масиву має такий синтаксис:

LOOKUP(lookup_value,array)

ПРОСМОТР(искомое_значение;массив)

  • lookup_value (lookup_value) – може бути текстом, числом, логічним значенням, назвою або посиланням.
  • пошук виконується за розмірністю масиву:
    • якщо в масиві більше стовпців, ніж рядків, то пошук відбувається в першому рядку;
    • якщо кількість рядків і стовпців однакова або рядків більше, то пошук відбувається в першому стовпці.
  • функція повертає останнє значення зі знайденого рядка/стовпця.

Пастки ПРОСМОТР (ПЕРЕГЛЯД)

  • У функції ПОГЛЯД (ПЕРЕГЛЯД) немає опції пошуку точної відповідності, яка є в ВЛООКУП (VLOOKUP) і в ПОСЛУГИ (GPR). Якщо пошукове значення відсутнє, то функція поверне максимальне значення, яке не перевищує пошукове значення.
  • Масив або вектор, який шукається, має бути відсортований у порядку зростання, інакше функція може повернути неправильний результат.
  • Якщо перше значення в масиві/векторі, що шукається, більше, ніж шукане значення, тоді функція створить повідомлення про помилку #AT (#Н/Д).

Приклад 1: Пошук останнього значення в стовпці

У формі функції масиву ПОГЛЯД (LOOKUP) можна використовувати для пошуку останнього значення в стовпці.

Значення довідки Excel 9,99999999999999Є + 307 як найбільше число, яке можна записати в клітинку. У нашій формулі воно буде встановлено як потрібне значення. Передбачається, що таке велике число не буде знайдено, тому функція поверне останнє значення в стовпці D.

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

=LOOKUP(9.99999999999999E+307,D:D)

=ПРОСМОТР(9,99999999999999E+307;D:D)

30 функцій Excel за 30 днів: LOOKUP

Приклад 2: Знайдіть останній місяць із від’ємним значенням

У цьому прикладі ми будемо використовувати векторну форму ПОГЛЯД (ПЕРЕГЛЯД). Стовпець D містить значення продажів, а стовпець E містить назви місяців. У деякі місяці справи йшли не дуже добре, і в клітинках зі значеннями продажів з’являлися від’ємні числа.

Щоб знайти останній місяць з від'ємним числом, формула с ПОГЛЯД (LOOKUP) перевірить для кожного значення продажів, що воно менше ніж 0 (нерівність у формулі). Далі ділимо 1 в результаті ми отримуємо будь-яке з них 1або повідомлення про помилку # DIV / 0 (#РОЗДІЛ/0).

Оскільки шукане значення є 2 не знайдено, функція вибере останній знайдений 1і поверніть відповідне значення зі стовпця E.

=LOOKUP(2,1/(D2:D8<0),E2:E8)

=ПРОСМОТР(2;1/(D2:D8<0);E2:E8)

30 функцій Excel за 30 днів: LOOKUP

Пояснення: У цій формулі замість аргументу пошуковий_вектор (lookup_vector) замінений вираз 1/(D2:D8<0), який утворює в оперативній пам’яті комп’ютера масив, що складається з 1 і значення помилок # DIV / 0 (#РОЗДІЛ/0). 1 вказує на те, що відповідна клітинка в діапазоні D2:D8 містить значення менше ніж 0, і помилка # DIV / 0 (#DIV/0) – що більше або дорівнює 0. У результаті наше завдання - знайти останнього 1 у створений віртуальний масив і на основі цього поверніть назву місяця з діапазону E2:E8.

Приклад 3: Перетворення досягнень студентів із відсотків у буквені оцінки

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

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

=LOOKUP(C10,D4:D8,C4:C8)

=ПРОСМОТР(C10;D4:D8;C4:C8)

30 функцій Excel за 30 днів: LOOKUP

залишити коментар