1.2.4Посилання й масиви - Lookup & Reference - hlookup (гпр)
Функція ГПР шукає значення у верхньому рядку таблиці або масиву (див. рис. Рисунок 6). Ця функція використовується, коли значення, що порівнюються розташовані у верхньому рядку таблиці даних, а повернені — на кілька рядків нижче. Якщо порівнювані значення знаходяться в стовпці зліва від шуканих даних, слід використовувати функцію ВПР.
Синтаксис функції ГПР:
Рисунок 6 – Приклад використання функції ГПР
ГПР(шукане_значення;таблиця;номер_рядка;інтервальний_перегляд),
де
шукане_значення — значення, яке потрібно знайти в першому рядку таблиці. «Шукане_значення» може бути значенням, посиланням або текстовим рядком;
таблиця – це таблиця з інформацією, в якій шукаються дані. Можна використовувати посилання на інтервал або ім'я інтервалу. Особливості даних таблиці:
значення в першому рядку аргументу «таблиця» можуть бути текстом, числами або логічними значеннями.
якщо аргумент «інтервальний_перегляд» має значення ИСТИНА, то значення в першому рядку аргументу «таблиця» повинні бути розташовані в порядку зростання: ...–2, –1, 0, 1, 2 ..., A—Z, ЛОЖЬ, ИСТИНА. В протилежному випадку функція ГПР може видати неправильний результат. Якщо ж аргумент «інтервальний_перегляд» має значення ЛОЖЬ, сортування для аргументу «таблиця» не обов'язкова.
текстові рядки вважаються еквівалентними незалежно від регістра букв.
дані можна впорядкувати за зростанням, зліва направо. Для цього необхідно виділити ці дані і вибрати у меню Данные команду Сортировка. Потім натиснути кнопку Параметры та вибрати столбцы диапазона. Після цього натиснути кнопку ОК. У полі Сортировать по вибрати рядок в списку і встановити перемикач по возрастанию;
номер_рядка — номер рядка в масиві «таблиця», з якої буде повернене значення, що порівнюється. Якщо значення аргументу «номер_рядка» дорівнює 1, то повертається значення з першого рядка аргументу «таблиця», якщо воно дорівнює 2 — з другого рядка і так далі Якщо значення аргументу «номер_ рядка» менше 1, функція ГПР повертає значення помилки #ЗНАЧ!; якщо воно більше, ніж кількість рядків в аргументі «таблиця», повертається значення помилки # ССЫЛ!;
інтервальний_перегляд — логічне значення, що визначає, яку відповідність повинна шукати функція ГПР — точну або приблизну. Якщо цей аргумент має значення ИСТИНА або опущений, то повертається приблизно відповідне значення; за відсутності точної відповідності повертається найбільше із значень, менших, ніж «шукане_значення». Якщо цей аргумент має значення ЛОЖЬ, функція ГПР шукає точну відповідність. Якщо воно не знайдене, повертається значення помилки #Н/Д.
1.2.5Посилання й масиви - Lookup & Reference - match (поискпоз)
Функція ПОИСКПОЗ повертає відносне положення елемента масиву, який відповідає вказаному значенню в заданому порядку (див. рис. Рисунок 7). Цю функцію варто використовувати, коли потрібно знайти позицію елемента в діапазоні, а не сам елемент.
Синтаксис функції ПОИСКПОЗ
ПОИСКПОЗ(шукане_значення;масив_що_переглядається;тип_зіставлення),
де
шукане_значення — значення, яке використовується для пошуку значення в таблиці:
шукане_значення — це значення, що зіставляється зі значеннями в аргументі що масив_що_переглядається. Наприклад, у випадку пошуку тривало-допустимого струму кабелю переріз кабелю буде шуканим значенням, а потрібним значенням буде тривало-допустимий струм;
ш
укане_значення
може бути значенням (числом, текстом
або логічним значенням) або посиланням
на комірку, що містить таке значення;
Рисунок 7 – Приклад використання функції ПОИСКПОЗ
масив_що_переглядається — неперервний інтервал комірок, що можуть містити шукані значення. Масив_що_переглядається може бути масивом або посиланням на масив;
тип_зіставлення — число –1, 0 або 1. Аргумент тип_зіставлення вказує, яким чином Microsoft Excel зіставляє шукане_значення зі значеннями в аргументі масив_що_переглядається.
якщо тип_зіставлення дорівнює 1, то функція ПОИСКПОЗ знаходить найбільше значення, що менше або дорівнює значенню аргументу шукане_значення. Масив_що_переглядається повинен бути впорядкований за зростанням: ..., –2, –1, 0, 1, 2, ..., A—Z, ЛОЖЬ, ИСТИНА;
ЯКЩО тип_зіставлення дорівнює 0, то функція ПОИСКПОЗ знаходить перше значення, яке дорівнює аргументу шукане_значення. Масив_що_переглядається може бути не впорядкований.
якщо тип_зіставлення дорівнює –1, то функція ПОИСКПОЗ знаходить найменше значення, що більше або дорівнює значенню аргументу шукане_значення. Масив_що_переглядається повинен бути впорядкований за зменшенням: ИСТИНА, ЛОЖЬ, Z—A, ..., 2, 1, 0, –1, –2, ... і т. д.
якщо аргумент тип_зіставлення пропущений, то вважається, що він дорівнює 1.
Функція ПОИСКПОЗ повертає не саме значення, а його позицію в аргументі масив_що_переглядається, а не саме значення. Наприклад, ПОИСКПОЗ("б";{"а";"б";"в"};0) повертає 2 — відносну позицію букви «б» у масиві {"а";"б";"в"}.
Функція ПОИСКПОЗ не розрізняє регістри при порівнянні текстів.
Якщо функція ПОИСКПОЗ не знаходить відповідне значення, то повертається значення помилки #Н/Д.
Якщо тип_зіставлення дорівнює 0 і шукане_значення є текстом, то шукане_значення може містити знаки шаблону: зірочку (*) і знак питання (?). Зірочка відповідає будь-якій послідовності знаків, знак питання — будь-якому одиночному знаку. Якщо потрібно знайти сам знак питання або зірочку, перед ними слід ввести знак тильди (~).