ПЕРЕГЛЯД з текстом
Одним з основних критеріїв роботи з VLOOKUP є значення "lookup", яке має бути однаковим у клітинці результату, а також у головній таблиці даних, але іноді, хоча значення пошуку виглядає однаково в будь-якій комірці, все одно ми отримуємо значення помилки як # N / A !. Це пов’язано з тим, що формат значення vlookup повинен бути іншим у будь-якій комірці. Тож у цій статті ми покажемо вам, як працювати з текстовим форматом значення Vlookup.
Приклад VLOOKUP для тексту
Нижче наведено приклад Excel VLOOKUP для тексту.
Ви можете завантажити цей VLOOKUP для текстового шаблону Excel тут - VLOOKUP для текстового шаблону ExcelІноді числа зберігаються як текстові значення, і в таких випадках ми не можемо розглядати їх як числа через функціональність Excel. Наприклад, подивіться наведені нижче дані.
У наведених вище даних все виглядає як числа, але коли ми підсумовуємо це, ми повинні отримати загальне значення 3712054, але коли ми застосуємо функцію SUM excel, отримаємо нижченаведене число.
Причиною цього є те, що деякі числа зберігаються як текстові значення. Отже, як ми визначаємо текстові значення?
Ми можемо ідентифікувати текстові значення за допомогою функції ISNUMBER excel. Функція ISNUMBER поверне TRUE, якщо вибране значення комірки є числом, а то поверне FALSE.
Отже, у клітинках B5 і B6 ми отримали результат як FALSE, що означає, що номери комірок A5 і A6 зберігаються як текстові значення.
VLOOKUP вимагає точного формату номера
Наприклад, перегляньте наведені нижче дані, щоб застосувати функцію VLOOKUP.
З таблиці 1 нам потрібно застосувати функцію VLOOKUP, щоб отримати стовпець доходу, що приводить до таблиці 2.
Отже, застосуйте функцію VLOOKUP у таблиці 2.
Отримуємо наступний результат.
Результат функції VLOOKUP у клітинках E6 та E7 показує # N / A !.
Давайте розглянемо значення пошуку.
Зазвичай це відбувається в числах значень підстановки, основною причиною повинен бути формат чисел в обох таблицях, що не однакові. Тож у таких випадках нам потрібно визначити, в яких номерах таблиць зберігаються як текст.
Застосуйте функцію ISNUMBER для ідентифікації нечислових значень.
Як ми можемо бачити ISNUMBER ідентифіковано нечислове значення в таблиці 2.
Коли дані основної таблиці правильні, а номери таблиць результатів зберігаються як текст, нам потрібно спочатку перетворити відформатовані в тексті числа в числові значення, а потім застосувати VLOOKUP. Є кілька способів зробити це, нижче наведені методи.
Спосіб 1: Перетворіть відформатовані текстом цифри в числові значення за допомогою спеціальної вставки
Спочатку введіть номер 1 у будь-яку з комірок робочого аркуша та скопіюйте цю комірку.
Тепер виберіть значення ідентифікатора розетки в таблиці 2 і відкрийте діалогове вікно Вставити спеціальне.
Щоб відкрити вставити спеціальне діалогове вікно, натисніть ALT + E + S.
Ми отримаємо наступне діалогове вікно.
У спеціальному вікні вставки в якості опції виберіть “помножити”.
Натисніть ОК, він перетворить усі відформатовані в тексті цифри на числові значення, і VLOOKUP тепер автоматично отримає дані з таблиці 1.
Спосіб 2: Перетворення за допомогою функції VALUE
Функція VALUE використовується для перетворення відформатованих в тексті чисел у числові значення. Як ми бачимо, наш VLOOKUP не отримував дані через формат значення пошуку.
Щоб подолати цю проблему, яка застосовує функцію пошуку, нам потрібно вкласти функцію VALUE.
Подивіться на наведену вище формулу, я додав функцію пошуку функції VALUE. Оскільки ми застосували функцію VALUE всередині функції VLOOKUP, вона спочатку перетворить нечислові значення в числові значення, а потім VLOOKUP розгляне їх лише як числові значення.
Метод 3: Що робити, якщо цифри зберігаються як текст у головній таблиці
Ми бачили, як перетворити текстові значення в числа в таблиці результатів, але що таке числа, зберігаються як текстові значення в самій головній таблиці.
Як ми можемо на наведеному вище зображенні в основній таблиці (таблиця 1), значення зберігаються як текст. У таких випадках нам потрібно вкласти функцію TEXT для значення пошуку у функції VLOOKUP.
Проблема полягає в тому, що функція TEX перетворює навіть числові значення у текстові, тому деякі значення, які зберігаються як числові значення, не працюватимуть у цій функції. Для цього нам потрібно вкласти умову IFERROR у Excel.
Умова IFERROR перевіряє, чи є значення пошуку числовим чи ні, якщо воно числове, ми застосуємо звичайний ПЕРЕГЛЯД, або ми застосуємо функцію TEXT у excel.
Отож так, нам потрібні великі знання про формули для роботи з VLOOKUP на просунутому рівні та за різних сценаріїв.
Що слід пам’ятати
- Функція TRIM також перетворює текстові значення в числові.
- Спочатку нам потрібно перевірити, в яких номерах таблиць зберігаються як текст.
- ISNUMBER повертає TRUE, якщо вибране значення комірки є числом, інакше воно поверне FALSE.