Багато людей намагаються використовувати Excel як базу даних. Незважаючи на те, що він може працювати як база даних, деякі завдання, які були б дуже простими в програмі баз даних, досить складні в Excel. Одне з цих завдань - узгодження двох списків на основі загального поля; це можна легко досягти за допомогою програми Excel VLOOKUP. Ви знайдете функцію VLOOKUP надзвичайно корисною, тому перегляньте приклад того, коли і як використовувати цю функцію нижче.
Скажімо, ваше туристичне агентство щомісяця надсилає вам звіт про всі місця, які подорожували ваші співробітники. Звіт використовує коди аеропортів замість назв міст. Було б корисно, якщо б ви могли легко ввести справжню назву міста, а не просто код.
В Інтернеті ви знайдете та імпортуєте список із зазначенням назви міста для кожного коду аеропорту.
Але як ви отримуєте цю інформацію про кожен запис у звіті?
- Використовуйте функцію VLOOKUP. VLOOKUP розшифровується як “Вертикальний пошук”. Він може бути використаний будь-коли, коли у вас є список даних із ключовим полем у крайньому лівому стовпці.
- Почніть вводити функцію,
=VLOOKUP(. Наберіть Ctrl + A, щоб отримати допомогу з функцією. - VLOOKUP потребує чотирьох параметрів. Перший - це код міста в оригіналі звіту. У цьому прикладі це буде комірка D4
- Наступним параметром є діапазон з таблицею пошуку. Виділіть діапазон. Обов’язково використовуйте F4, щоб діапазон був абсолютним. (Абсолютне посилання має знак долара перед номером стовпця і номером рядка. Коли формула скопійована, посилання буде продовжувати вказувати на I3: J351.
- 3-й параметр повідомляє Excel, у якому стовпці знаходиться назва міста. В діапазоні I3: J351 назва міста знаходиться у стовпці 2. Введіть 2 для цього параметра.
- Четвертий параметр повідомляє Excel, чи відповідає «близьке» збіг. У цьому випадку це не так, тому введіть False.
- Натисніть OK, щоб заповнити формулу. Перетягніть маркер заповнення, щоб скопіювати формулу вниз.
- Оскільки ви ретельно ввели абсолютні формули, ви можете скопіювати стовпець E у стовпець D, щоб отримати місто призначення. У цьому випадку всі вильоти здійснюються з міжнародного аеропорту Пірсон у Торонто.
Незважаючи на те, що цей приклад спрацював ідеально, коли глядачі використовують VLOOKUP, зазвичай це означає, що вони збігаються зі списками, що надійшли з різних джерел. Коли списки надходять з різних джерел, завжди можуть бути незначні відмінності, через які списки важко зрівняти. Ось три приклади того, що може піти не так і як їх виправити.
-
В одному списку є тире, а в іншому - ні. Використовуйте
=SUBSTITUTE()функцію для видалення тире. Коли ви вперше спробуєте VLOOKUP, ви отримаєте помилки N / A.Щоб видалити тире за допомогою формули, використовуйте формулу ЗАМІНИ. Використовуйте 3 аргументи. Перший аргумент - це комірка, що містить значення. Наступним аргументом є текст, який ви хотіли б змінити. Остаточний аргумент - текст заміщення. У цьому випадку ви хочете змінити тире на нуль, тому формула така
=SUBSTITUTE(A4,"-","").Ви можете обернути цю функцію у VLOOKUP, щоб отримати опис.
-
Цей тонкий, але дуже поширений. В одному списку є порожній пробіл після введення. Використовуйте = TRIM (), щоб видалити зайві пробіли. Коли ви спочатку вводите формулу, виявляєте, що всі відповіді є помилковими. Ви точно знаєте, що значення є у списку, і з формулою все виглядає нормально.
Одне стандартне, що потрібно перевірити, - це перейти до комірки зі значенням пошуку. Натисніть F2, щоб перевести комірку в режим редагування. Потрапивши в режим редагування, ви бачите, що курсор знаходиться на відстані від останньої літери. Це вказує на те, що у записі є пробіл.
Щоб вирішити проблему, використовуйте функцію TRIM.
=TRIM(D4)видалить пробіли, що проходять, і кінцеві пробіли, а всі внутрішні подвійні пробіли замінить єдиним пробілом. У цьому випадку TRIM чудово працює, щоб видалити кінцевий простір.=VLOOKUP(TRIM(D4),$I$3:$J$351,2,FALSE)- це формула. -
Я згадував бонусну пораду у примітках до шоу: як замінити результат # N / A для відсутніх значень на пустий. Якщо значення пошуку немає в таблиці пошуку, VLOOKUP поверне помилку N / A.
Ця формула використовує
=ISNA()функцію для виявлення, якщо результатом формули є помилка N / A. Якщо ви отримаєте помилку, другий аргумент у функції IF вкаже Excel вкласти будь-який потрібний текст.=IF(ISNA(VLOOKUP(D4,$I$3:$J$351,2,FALSE)),"Invalid Code",VLOOKUP(D4,$I$3:$J$351,2,FALSE))
VLOOKUP дозволяє економити час при зіставленні списків даних. Не поспішайте вивчати основні способи використання, і ви зможете виконувати набагато потужніші завдання в Excel.
