Як порівняти два стовпці в Excel за допомогою VLOOKUP

Під час роботи з великими наборами даних у Excel вам може знадобитися порівняти два стовпці, щоб знайти подібності чи відмінності.

VLOOKUP — потужна функція, яка дозволяє шукати відповідні дані між двома стовпцями. Ця функція дозволяє шукати значення в одному стовпці, які з’являються в іншому.

У цій статті показано, як порівняти два стовпці за допомогою VLOOKUP, щоб ви могли ефективно аналізувати свої дані.

Зміст

• Основи функції Vlookup
  • Функція Vlookup
• Підготовка ваших даних для VLOOKUP
  • Форматування даних
• Як використовувати VLOOKUP для порівняння двох стовпців
  • Обробка інших помилок
• Альтернативи VLOOKUP для порівняння стовпців
  • 1. Використання функцій індексу та відповідності
  • 2. Використання XLOOKUP в Excel
• П’ять порад щодо покращення продуктивності VLOOKUP
  • 1. Обмежте діапазон пошуку
  • 2. Використовуйте абсолютні посилання
  • 3. Відсортуйте дані
  • 4. Використовуйте подвійний VLOOKUP
  • 5. Використовуйте умовне форматування
• Розширене використання функції VLOOKUP
• Наше заключне слово

Основи функції Vlookup

Припустімо, у вас є електронна таблиця з двома списками елементів у стовпцях A та B. Ви хочете знайти елементи зі списку 1, які також відображаються в списку 2.

Ви можете собі уявити, що робота вручну зі списками з тисяч елементів займе дуже багато часу. На щастя, на допомогу приходить VLOOKUP!

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

Функція Vlookup

Ось базовий синтаксис функції VLOOKUP:

=VLOOKUP(пошукове_значення, масив_таблиці, номер_індексу_стовпця, [діапазон_пошуку])

Це розбивка елементів:

• lookup_value : значення, яке потрібно шукати в першому стовпці table_array.

• table_array : діапазон комірок, що містять дані, у яких потрібно виконати пошук.

• col_index_num : номер стовпця в table_array, з якого потрібно повернути значення.

• діапазон_пошуку : необов'язковий. Це TRUE (приблизний збіг) або FALSE (точний збіг). Типовим значенням є TRUE.

Підготовка ваших даних для VLOOKUP

Перш ніж використовувати VLOOKUP для порівняння двох, потрібно підготувати дані.

Створіть два окремих стовпці на аркуші, де ви хочете порівняти значення. У цій статті для наших прикладів використовуються стовпці A та B.

Ось зразки даних, використаних у цій статті:

Це зразок таблиці із заголовком, який потрібно додати до розділу під назвою «Підготовка даних для VLOOKUP»:

Список 1 Список 2
Пункт 1 Пункт
3 Пункт 2
Пункт 4 Пункт 3 Пункт 1
Пункт 4 Пункт 5

Форматування даних

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

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

Також радимо видалити будь-які повторювані значення або порожні клітинки, щоб мінімізувати ризик помилок.

Щоб видалити повторювані значення:

1. Виберіть стовпець.

2. Перейдіть на вкладку Дані у верхній стрічці.

3. Натисніть «Видалити дублікати» в розділі «Інструменти даних».

Кнопку може бути трохи важко побачити. Цей малюнок допоможе вам:

Щоб видалити порожні клітинки:

1. Виберіть стовпець.

2. Перейдіть на вкладку «Головна» у верхній стрічці.

3. Розгорніть меню «Знайти та вибрати».

4. Виберіть «Перейти до спеціального».

5. Виберіть «Заготовки» з параметрів.

6. Натисніть «Видалити» в розділі «Комірки».

Як використовувати VLOOKUP для порівняння двох стовпців

Коли дані будуть підготовлені, ви можете написати формулу VLOOKUP, щоб порівняти два стовпці в Excel і визначити збіги. Виконайте такі дії:

1. Виберіть клітинку в новому стовпці, де потрібно відобразити результати порівняння (наприклад, клітинку C2).

2. Введіть таку формулу: =VLOOKUP(A2; B:B; 1; FALSE))

3. Натисніть Enter, щоб застосувати формулу.

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

5. Перетягніть формулу вниз із C2, щоб скопіювати її на стільки клітинок, скільки потрібно.

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

Зверніть увагу, що пункт 2 відображається як #N/A. Excel повідомляє нам, що збіг непридатний, тобто його неможливо знайти.

Це правильно, і це корисна інформація про відсутні значення. Однак деякі користувачі Excel можуть подумати, що виникла проблема з даними або помилка функції.

Хорошою практикою є показ іншої індикації того, що відповідності не знайдено. Це може бути просто бланк.

Для цього поєднайте функцію VLOOKUP із функцією IFNA таким чином:

=IFNA(ВПР(A1, B:B, 1, ЛОЖЬ), “”)

Функція IFNA виявляє помилку #N/A і замінює вихідні дані порожнім простором («»). Ви також можете використовувати функцію ISNA або мати додаткову логіку за допомогою функції IF.

Обробка інших помилок

Функція VLOOKUP іноді може генерувати інші помилки в результатах порівняння. #REF ! Помилка в Excel — це справжня проблема з вашими даними.

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

Переконайтеся, що діапазон пошуку, який ви маєте на увазі, охоплює всі значення, які ви хочете порівняти.

Альтернативи VLOOKUP для порівняння стовпців

Існує дві основні альтернативні функції пошуку під час порівняння двох стовпців у Excel для пошуку збігів.

1. Використання функцій індексу та відповідності

Замість використання VLOOKUP ви можете порівняти два стовпці в Excel, поєднавши функції INDEX і MATCH.

Цей метод забезпечує більш гнучкий спосіб пошуку даних і особливо корисний під час роботи з несуміжними стовпцями або коли індекс стовпця може змінитися.

Ось синтаксис, який слід додати до стовпця результатів:

=INDEX(повернутий_діапазон, MATCH(пошукове_значення, пошуковий_діапазон, 0))

• Діапазон_повернення : діапазон комірок, які містять дані, які потрібно повернути.

• Lookup_value : значення, яке ви хочете знайти в діапазоні пошуку.

• Lookup_range : діапазон комірок, у якому потрібно знайти шукане значення.

Використовуючи ті самі дані в попередніх прикладах, ми замінюємо формулу VLOOKUP таким чином:

=(ІНДЕКС(B:B, MATCH(A2, B:B, 0)))

Функція MATCH повертає відносну позицію шуканого значення в діапазоні пошуку, а функція INDEX повертає відповідне значення з діапазону.

Результати тих самих даних будуть такими ж, як у VLOOKUP, який ми використовували раніше. На цьому малюнку показано використовувані функції:

Ви також можете замінити помилку #N/A спеціальним повідомленням. Ось приклад використання функції IFERROR.

=ЯКЩОПОМИЛКА(ІНДЕКС(B:B; MATCH(A2; B:B; 0)), «Не знайдено»)

2. Використання XLOOKUP в Excel

Для користувачів Excel 365 або Excel 2019 XLOOKUP є альтернативою VLOOKUP логічного тесту для пошуку спільних значень у двох стовпцях.

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

Синтаксис XLOOKUP такий:

=XLOOKUP(пошукове_значення, діапазон_пошуку, діапазон_повернення, [якщо_не_знайдено], [режим_збігу], [режим_пошуку])

• lookup_value : значення, яке ви хочете знайти в діапазоні пошуку.

• діапазон_пошуку : діапазон комірок, у якому потрібно знайти значення пошуку.

• діапазон_повернення : діапазон комірок, які містять дані, які потрібно повернути.

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

XLOOKUP знайде шукане значення в діапазоні пошуку та поверне відповідне значення з діапазону.

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

Ось приклад із двома списками в першому та третьому стовпцях електронної таблиці:

Формула XLOOKUP виглядає так:

=XLOOKUP(A2;C:C;C:C;””)

На цьому малюнку показано результат із порівнянням у стовпці D. Перше значення присутнє, але друге відсутнє.

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

Як бонус, я покажу вам, як використовувати XLOOKUP для порівняння між рядками. Якщо у вас є два списки в рядках 1 і 2, найпростіше використання формули виглядає так:

=XLOOKUP(B1; 2:2; 2:2; “”)

На цьому малюнку показано результати у двох рядках.

Завдяки вдосконаленій функціональності та гнучкості XLOOKUP є чудовою альтернативою VLOOKUP для порівняння списків у Excel для визначення збігів.

П’ять порад щодо покращення продуктивності VLOOKUP

Оптимізація функції VLOOKUP може допомогти уникнути тривалого очікування та покращити загальну швидкість реагування аркуша Excel.

Ось шість порад і підказок, які можна застосувати, щоб покращити продуктивність VLOOKUP в Excel.

1. Обмежте діапазон пошуку

Я використовував цілі стовпці в прикладах, щоб спростити їх. Якщо ви працюєте з великим обсягом даних, вам слід уникати цього. Використання повних стовпців може сповільнити процес обчислення Excel.

Натомість спробуйте використовувати точний діапазон, необхідний для ваших даних (наприклад, A1:A100). Це зменшує кількість комірок, які має оцінити функція VLOOKUP.

2. Використовуйте абсолютні посилання

Указуючи діапазон даних (наприклад, комірки від B2 до B5), використовуйте абсолютні посилання. Це гарантує, що формула є узгодженою, а дані існують, коли ви копіюєте їх у кілька клітинок.

Ось приклад:

=ВПР(A2; $B$2:$B$5; 1; ЛОЖЬ)

3. Відсортуйте дані

Якщо ви знаєте, що дані в стовпці пошуку відсортовано в порядку зростання, ви можете використовувати VLOOKUP із значенням TRUE або 1 для аргументу "range_lookup".

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

4. Використовуйте подвійний VLOOKUP

Ви можете використовувати дві функції VLOOKUP, щоб прискорити процес пошуку в Excel.

Перший VLOOKUP визначить, чи існує пошукове значення, встановивши «col_index_num» на 1 і «range_lookup» на TRUE.

Якщо він повертає TRUE, другий VLOOKUP отримує потрібне значення з 'range_lookup', встановленим на TRUE.

5. Використовуйте умовне форматування

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

Ви знайдете меню умовного форматування в групі стилів на вкладці «Головна».

Розширене використання функції VLOOKUP

Т-тести парних вибірок використовуються для порівняння середніх двох пов’язаних вибірок. У цьому відео показано їх використання в Excel.

Якщо у вас є додаткові дані або змінні, на які вам потрібно посилатися під час аналізу, ви можете скористатися VLOOKUP, щоб отримати необхідні значення з іншої таблиці або аркуша.

Наприклад, ви можете використовувати VLOOKUP для отримання демографічної інформації або умов лікування для кожного парного спостереження у вашому наборі даних.

Наше заключне слово

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

Ви також бачили приклади інших функцій пошуку та посилань, таких як XLOOKUP, INDEX і MATCH. Додавання цих елементів до ваших навичок ще більше посилить ваші можливості аналізу даних.

Продовжуйте практикувати та вдосконалювати свої навички VLOOKUP, і незабаром ви станете експертом із порівняння стовпців у Excel, заощаджуючи час і підвищуючи продуктивність.