Функції для пошуку та роботи з посиланнями в Excel

vlookup (впр)

vlookup(Lookup_value;Table_array;Col_index_num;Range_lookup) – шукає значення в крайньому лівому стовпчику таблиці і повертає значення клітинки, що знаходиться у зазначеному стовпці у тому ж рядку.

Ім’я аргументу
Опис аргументів функції
Lookup_value
(обов’язковий)
значення, за яким шукаємо збіг в першому стовпці іншої таблиці.
Table_array
(обов’язковий)
таблиця, у якій в першому стовпці здійснюється пошук потрібного значення.
Col_index_num
(обов’язковий)
номер стовпчика таблиці, з якого треба підставити значення.
Range_lookup
(не обов’язковий)
за необхідності можна задати TRUE (1), щоб шукати приблизне значення, або FALSE (0), щоб отримати точний збіг.

Зауваження:

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

Виникнення помилок #N/A:

  • в аргументі Range_lookup заданий параметр FALSE (точний пошук), але потрібне значення не найдене;
  • посилання на таблицю в якій знаходяться потрібні дані повинно бути абсолютним.

Приклади використання функції VLOOKUP.

Приклад №1: зробити автоматичне відображення цін на фрукти у відповідній клітинці в залежності від обраного значення зі списку.

Розв’язок:

  • починаємо складати формулу;
  • в якості першого аргументу функції VLOOKUP виступає значення що ми шукаємо – B18;
  • далі виділяємо електрону таблицю в якій знаходяться дані – В2:C13;
  • вказуємо номер стовпчика з якого будуть підставлятися дані – 2;
  • задаємо параметр пошуку – 0, для отримання точного збігу із значенням яке ми шукаємо;
  • для отримання результату натискаємо клавішу Enter.

=VLOOKUP(B18;C3:F15;2;0)

hlookup (гпр)

hlookup(Lookup_value;Table_array;Row_index_num;Range_lookup) – шукає значення в крайній верхній частині таблиці і повертає значення у тому ж стовпці з зазначеного рядка таблиці.

Ім’я аргументу
Опис аргументів функції
Lookup_value
(обов’язковий)
значення, яке має бути знайдено в першому рядку іншої таблиці.
Table_array
(обов’язковий)
таблиця, у якій в першому рядку здійснюється пошук потрібного значення
Row_index_num
(обов’язковий)
номер рядка таблиці, з якого треба підставити значення.
Range_lookup
(не обов’язковий)
за необхідності можна задати TRUE (1), щоб шукати приблизне значення, або FALSE (0), щоб отримати точний збіг.

Зауваження:

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

Виникнення помилок #N/A:

  • в аргументі Range_lookup заданий параметр FALSE (точний пошук), але потрібне значення не найдене;
  • посилання на таблицю в якій знаходяться потрібні дані повинно бути абсолютним.

Приклади використання функції HLOOKUP.

Приклад №1: визначити за кодом замовлення, на основі таблиці A1:IH4 – код клієнта.

Розв’язок:

  • починаємо складати формулу;
  • в якості першого аргументу функції HLOOKUP виступає значення що ми шукаємо – B9;
  • далі виділяємо електрону таблицю в якій знаходяться дані – A1:IH4;
  • вказуємо номер стовпчика з якого будуть підставлятися дані – 2;
  • задаємо параметр пошуку – 0, для отримання точного збігу із значенням яке ми шукаємо;
  • для отримання результату натискаємо клавішу Enter.

=HLOOKUP(B9;A1:IH4;2;0)

match (поискпоз)

match(Lookup_value;Lookup_array;Match_type) – виконує пошук вказаного значення в діапазоні клітинок та повертає його відносну позицію у цьому діапазоні.

Ім’я аргументу
Опис аргументів функції
Lookup_value
(обов’язковий)
значення, для якого визначається відносне положення в діапазоні даних.
Lookup_array
(обов’язковий)
діапазон даних, в якому здійснюється пошук потрібного значення.
Match_type
(не обов’язковий)
за необхідності вказуємо режим пошуку: -1 – шукає найменше значення, яке більше або дорівнює значенню аргументу (округлює шукане значення в більшу сторону); 0 – точний збіг; 1 – шукає найбільше значення, яке менше або дорівнює значенню аргументу (округлює шукане значення в меншу сторону).

Зауваження:

  • за замовчуванням у функції MATCH аргумент Match_type якщо його не вказати приймає значення – 1 (округлення в меншу сторону);
  • якщо в якості аргументу Match_type вказується значення 1 то діапазон даних повинен бути впорядкований за зростанням (A–Z);
  • якщо в якості аргументу Match_type вказується значення -1 то діапазон даних повинен бути впорядкований за спаданням (Z–A);

Приклади використання функції MATCH.

Приклад №1: визначити в діапазоні даних B3:B14 відносне положення елемента.

Розв’язок:

  • починаємо складати формулу;
  • в якості першого аргументу функції MATCH виступає значення для якого ми визначаємо його відносне положення в діапазоні даних – D6;
  • виділяємо діапазон даних в якому здійснюється пошук потрібного значення – В3:В14;
  • задаємо параметр пошуку – 0, для отримання точного збігу із значенням яке ми шукаємо;
  • для отримання результату натискаємо клавішу Enter.

=MATCH(B9;A1:IH4;0)

index (индекс)

index(Array;Row_num;Column_num) – повертає масив даних або значення з клітинки таблиці, ім’я клітинки задається номером рядка і номером стовпця.

Ім’я аргументу
Опис аргументів функції
Array
(обов’язковий)
діапазон клітинок або константа масиву.
Row_num
(обов’язковий)
вибирає рядок в масиві, з якої потрібно повернути значення.
Column_num
(не обов’язковий)
за необхідності вказуємо режим пошуку: -1 – шукає найменше значення, яке більше або дорівнює значенню аргументу (округлює шукане значення в більшу сторону); 0 – точний збіг; 1 – шукає найбільше значення, яке менше або дорівнює значенню аргументу (округлює шукане значення в меншу сторону).

Зауваження:

  • якщо масив містить тільки один рядок або один стовпець, відповідний аргумент “Row_num” або “Column_num” не є обов’язковим;
  • якщо масив містить більше одного рядка і одного стовпця, а з аргументів “Row_num” та “Column_num” заданий тільки один, функція INDEX повертає масив, що складається з цілого рядка або цілого стовпця;
  • якщо вказати в якості аргументу “Row_num” або “Column_num” значення 0 (нуль), функція INDEX поверне масив значень для цілого стовпця або цілого рядка відповідно;

Приклади використання функції INDEX.

Приклад №1: визначити назву фрукта, який знаходиться в діапазоні даних В3:В14 під певним номером.

Розв’язок:

  • починаємо складати формулу;
  • в якості першого аргументу функції INDEX задаємо масив даних – В3:В14;
  • далі вказуємо номер клітинки з якої треба повернути значення – 5;
  • для отримання результату натискаємо клавішу Enter.

=INDEX(B3:В14;5)

Приклад №2: визначити оцінку яку отримає учень з визначеного шкільного предмета.

Розв’язок:

  • починаємо складати формулу;
  • в якості першого аргументу функції INDEX задаємо масив даних – C4:F8;
  • вказуємо номер рядка в якому знаходиться ПІБ учня – 2;
  • вказуємо номер стовпчика в якому знаходиться оцінки учнів з предмету – 4;
  • для отримання результату натискаємо клавішу Enter.

=INDEX(C4:F8;2;4)

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

Приклад №3 (заміна функції VLOOKUP): налаштувати автоматичне відображення назви країни за відповідним кодом.

Розв’язок:

  • починаємо складати формулу;
  • в якості першого аргументу функції INDEX задаємо масив даних у якому містяться назви країн – В4:В207;
  • далі за допомоги функції MATCH шукаємо номер клітинки в діапазоні даних, тобто шукаємо номер рядка в якому міститься код країни:
    • вказуємо назву функції – MATCH;
    • перший її аргумент – шукане значення – G10;
    • другий аргумент – діапазон даних в якому відбувається пошук – E4:E207;
    • третій – режим пошуку – 0 (точне співпадіння);
  • для отримання результату натискаємо клавішу Enter.

=INDEX(В4:В207;MATCH(G10;E4:E207;0))

Розглянемо приклад коли на листі Excel є декілька електронних таблиць і треба з вказаної таблиці повернути необхідні дані. В цьому випадку синтаксис функції INDEX буде наступний:

index(Reference;Row_num;Column_num;Area_num)

Reference – посилання на один або більше діапазонів клітинок.
Area_num – діапазон, з якого будуть повернуті дані, нумерація діапазонів починається з 1.

Приклад №4: відобразити продажі у 2018 р. за III квартал у Центральному регіоні.

Розв’язок:

  • починаємо складати формулу;
  • в якості першого аргументу функції INDEX вказуємо у дужках через крапку з комою діапазони даних – (C4:F8;I4:L8;O4:R8);
  • вказуємо номер рядок в якому знаходиться дані з продажів у Центральному регіоні – 2;
  • вказуємо номер стовпчика в якому знаходиться дані з продажів за ІІІ квартал – 3;
  • вказуємо номер діапазону в якому знаходяться необхідні дані – 2;
  • для отримання результату натискаємо клавішу Enter.

=INDEX((C4:F8;I4:L8;O4:R8);2;3;2))

додаткові
матеріали