Так правильно використовується функція Excel
У вас є величезний обсяг даних і терміново потрібна інформація про певне значення, яке внесене до вашої таблиці? На цьому етапі має сенс скористатися функцією посилання Excel. Це пояснюється тим, що він дозволяє визначити необхідну інформацію за допомогою критерію пошуку та областей комірок, у яких потрібно шукати потрібне значення. Це працює автоматично і дозволяє швидко запитувати інформацію. Якщо ви хочете здійснити більш цілеспрямований пошук, вам слід скористатися вдосконаленими опорними функціями Vreference, Wreference або XVreference. Excel пропонує інші альтернативи для організації та пошуку даних.
Як використовується опорна функція Excel
Функція посилання Excel корисна для пошуку значення в стовпці або рядку, що знаходиться в тій же позиції в іншому рядку або стовпці. Посилання можна використовувати, з одного боку, у векторній версії, а з іншого - у матричній версії. Векторну версію використовують, коли потрібно шукати стовпець або рядок за певним критерієм пошуку. У матричній версії можна шукати кілька стовпців і рядків або весь аркуш. Однак для цієї мети є покращені функції Vreference і Wreference. Програма вмикає матричну версію лише з міркувань сумісності з іншими програмами електронних таблиць.
Як структурована довідкова функція Excel у векторній версії?
Функція посилання Excel складається з трьох аргументів і структурована таким чином: Посилання (критерій пошуку, вектор пошуку, [результат результату]).
- Критерій пошуку є обов’язковим аргументом. Тут вводиться значення, яке потрібно шукати. Значенням може бути число, текст, логічне значення, ім’я або друга опорна функція.
- Пошук вектор є другим необхідним аргументом формули. Тут вказується область стовпця або рядка, у якій слід шукати значення пошуку. Це також може бути текст, цифри або значення істинності.
- Результат вектор: Квадратні дужки навколо третього аргументу, вектора результату, вказують на те, що цей аргумент необов’язковий. На цьому етапі можна вказати іншу область, яка повинна містити стільки ж елементів, скільки другий аргумент.
Пошук у стовпцях - приклад
У вас є список номерів артикулів у колонці А з відповідними елементами одягу у колонці В. Ви шукаєте конкретний номер артикулу і хочете дізнатися, з яким елементом одягу йде номер статті.
Виберіть порожній рядок.
Вставте формулу = посилання ("ART7492031"; A2: A6; B2: B6).
Тому функція шукає номер статті у стовпці А та виводить результат зі стовпця В, наприклад "Загалом".
Синтаксис формули посилання у версії матриці містить менше аргументів
У матричній функції опорна функція Excel складається з посилання (критерій пошуку, матриця). Він не містить жодних необов’язкових аргументів, а скоріше два обов’язкових.
- Критерій пошуку є першим обов’язковим аргументом і може складатися з числа, тексту або логічних значень, імені або іншої довідкової функції.
- матриця - це діапазон клітинок, який використовується для порівняння. Числа, рядки або логічні значення також можна знайти тут.
Як згадувалося, рекомендується використовувати інші довідкові функції Excel Vreference та Wreference, оскільки вони мають кращі функціональні можливості. Наприклад, можна вказати номер стовпця або індекс стовпця, що містить повернене значення.
Пошук у стовпцях - векторна версія: приклад
Якщо у векторному варіанті передбачається та сама ситуація, що і у наведеному вище прикладі, дійте аналогічно.
Виберіть порожній рядок.
Вставте формулу = посилання ("ART7492031"; A2: B6).
Тому функція шукає номер товару в стовпцях А - стовпець В і в результаті виводить відповідний предмет одягу ("загальний").
Це слід враховувати при використанні довідкової функції
Значення в Пошук вектор обидві версії повинні бути розташовані в порядку зростання. Це означає, наприклад, для чисел -1, 0, 1, 2, 3, тощо для текстових значень A, B, C, тощо і для істинних цінностей не вірно слідом за правда. Текст не чутливий до регістру. Якщо за критерієм пошуку не вдається знайти значення, довідкова функція Excel приймає значення Пошук векторщо наближається до значення в критерії пошуку. Якщо значення критерію пошуку менше, ніж значення в області пошуку, Excel повертає значення помилки #NV.
Швидкий пошук за допомогою цих трьох довідкових функцій Excel
Користувачам, які не використовують функцію посилання Excel з міркувань сумісності, слід використовувати Sreference, Wreference або покращену версію XLOOKUP. Вони дозволяють більш точно шукати значення, використовуючи, наприклад, символи підстановки.
Шукайте стовпці або таблицю за допомогою Vreference
Довідкова функція Excel VLOOKUP шукає значення у рядках за допомогою матриці та індексу стовпців. Тут важливо, щоб дані були організовані таким чином, щоб значення пошуку знаходилося поруч із поверненим значенням. Однак значення також можна призначати таким чином, щоб вони належали до певних категорій чи груп. Ця процедура економить час при оцінці таблиці.
Vreference може не тільки здійснювати пошук по цілих таблицях, але також може поєднуватися з іншими функціями. Наприклад, можна шукати декілька критеріїв одночасно. Якщо ви хочете використовувати довідкову функцію Excel для кількох пов’язаних аркушів, ви можете отримати повідомлення про помилки, але їх можна швидко вирішити за допомогою налаштувань. Однак значення помилки буде #NV відображається, це помилка програми. Це повідомлення про помилку Excel можна виявити за допомогою інших формул.
Використовуйте посилання як довідкову функцію Excel і здійснюйте пошук по рядках
На відміну від VLOOKUP, HLOOKUP здійснює пошук по горизонталі. Тому ця довідкова функція Excel шукає стовпець за стовпцем у визначеному рядку за критерієм пошуку на аркуші. Функція HLOOKUP має такі функції:
- 3 обов'язкові аргументи (Критерій пошуку; Матриця; Покажчик рядка)
- 1 необов’язковий аргумент (Area_reference)
- Конкретний пошук у таблиці рівнів
- Використання символів підстановки як критерії пошуку
Пошук у всіх напрямках дозволяє XVreference
Microsoft 365 має покращену довідкову функцію Excel XLOOKUP, яка поєднує в собі функції VLOOKUP та HLOOKUP. За допомогою формули можна шукати аркуш за критерієм пошуку, незалежно від того, де стовпець результату. Ця довідкова функція дещо складніша з 6 аргументами, але вона дозволяє користувачам Excel детально та динамічно шукати у всіх напрямках.
Огляд інших матричних формул для оцінки таблиці
Чи є доцільним розгляд великого та нижнього регістру для оцінки даних? У цьому може допомогти більш складна формула масиву, яку не можуть виконувати опорні функції. Довідкові функції Excel - це не єдиний спосіб запитувати інформацію у стовпцях або рядках, які програма має у своєму репертуарі. Наприклад, функція індексу може виводити значення або посилання з комірок. Необхідні дані можна зчитати, вказавши змінне положення комірки або використовуючи номери стовпців та рядків.За посиланням функції порівняння користувачі Excel також можуть витягувати значення з перетинів рядків та стовпців. Діапазони комірок також можна використовувати для визначення останнього запису у списку Excel.
Інші функції Excel, які можуть допомогти в організації та оцінці інформації:
- Row () - це функція Excel, яку можна використовувати для встановлення номерів рядків на аркуші. Наприклад, можна пронумерувати списки Excel двома кроками. Ця формула може бути навіть вбудована в іншу формулу і може автоматично продовжувати нумерацію рядків, як тільки порожня клітинка отримує нове значення.
- Indirect () допомагає розділити та розташувати багаторядкові дані у стовпцях або зчитати вміст за адресами комірок.
- Адреса () - це ще одна матрична формула, за допомогою якої можна визначити адресу комірки певного значення. Інтегруючи інші функції, ви можете розширити функцію і, наприклад, визначити позицію найбільшого числа у списку Excel.
- У поєднанні з іншими формулами, гіперпосилання () може не тільки виводити точну позицію значення, але й одночасно зберігати посилання на позицію.
Довідкова функція Excel дозволяє швидко запитувати інформацію
Незалежно від того, чи потрібно визначати дані з певного стовпця, шуканих рядків або виводити значення результату для відповідного значення пошуку, опорні функції в Excel можна використовувати різними способами. Користувачі можуть вибирати між довідковою функцією Excel, сумісною з іншими програмами електронних таблиць, або більш широкими функціями Vreference або Wreference. Для просунутих користувачів XVerweis підходить для більш цілеспрямованого пошуку, який поєднує в собі функції SVerweis та Wreweis. Інші функції Excel можна використовувати для організації даних, наприклад, автоматична нумерація рядків або повернення позиції комірки значення.