Excel VLOOKUP є потужним, але він не буде працювати, коли у вас є конкретні ситуації. Сьогодні ми подивимося, як усунути неполадки VLOOKUP.
VLOOKUP - це моя улюблена функція в Excel. Якщо ви можете зробити VLOOKUP, ви можете вирішити багато проблем в Excel. Але є речі, які можуть зіткнути VLOOKUP. Ця тема розповідає про декілька з них.
Але спочатку основи VLOOKUP простою англійською мовою.
Дані A: C надійшли з ІТ-відділу. Ви просили продажу за пунктом та датою. Вони дали вам номер товару. Вам потрібен Опис товару. Замість того, щоб чекати, поки ІТ-відділ повторно запустить дані, ви знайдете таблицю, показану у стовпці F: G.
Ви хочете, щоб VLOOKUP знайшов елемент у А2 під час пошуку в першому стовпці таблиці в $ F $ 3: $ G $ 30. Коли VLOOKUP знаходить відповідність у F7, ви хочете, щоб VLOOKUP повернув опис, знайдений у другому стовпці таблиці. Кожен VLOOKUP, який шукає точну відповідність, повинен закінчуватися False (або нулем, що еквівалентно False). Формула нижче налаштована належним чином.
Зверніть увагу, що ви використовуєте F4 для додавання знаків чотирьох доларів до адреси таблиці пошуку. Під час копіювання формули в стовпець D вам потрібна адреса таблиці пошуку, щоб залишатися незмінною. Є дві загальні альтернативи: Ви можете вказати цілі стовпці F: G як таблицю пошуку. Або ви можете назвати F3: G30 таким іменем, як ItemTable. Якщо ви використовуєте =VLOOKUP(A2,ItemTable,2,False), названий діапазон діє як абсолютна посилання.
Кожного разу, коли ви робите купу VLOOKUP, вам потрібно відсортувати стовпець VLOOKUP. Відсортуйте ZA, і будь-які помилки # N / A будуть зверху. У цьому випадку є один. Елемент BG33-9 відсутній у таблиці пошуку. Можливо, це друкарська помилка. Можливо, це абсолютно новий предмет. Якщо він новий, вставте новий рядок у будь-яку точку посередині таблиці пошуку та додайте новий елемент.
Цілком нормально мати кілька помилок # N / A. Але на малюнку нижче, точно така ж формула не повертає нічого, крім # N / A. Коли це трапляється, я бачу, чи зможу я розв’язати перший VLOOKUP. Ви шукаєте BG33-8, знайдений в А2. Почніть круїз через перший стовпець таблиці пошуку. Як бачите, значення відповідності явно вказане у F10. Чому ви можете це бачити, а Excel не бачить?
Перейдіть до кожної комірки та натисніть клавішу F2. Ось F10. Зверніть увагу, що курсор вставки з'являється відразу після 8.
Ось клітина A2 у режимі редагування. Курсор вставки знаходиться на пару пробілів від 8. Це знак того, що в якийсь момент ці дані зберігались у старому наборі даних COBOL. Повернувшись до COBOL, якби поле "Елемент" було визначено як 10 символів, і ви ввели лише 6 символів, COBOL заповнив би його 4 додатковими пробілами.
Рішення? Замість того, щоб шукати А2, шукайте TRIM(A2).
Функція TRIM () прибирає пробіли в кінці та в кінці. Якщо у вас є кілька пробілів між словами, TRIM перетворить їх в один пробіл. На малюнку нижче є пробіли перед і після обох назв в А1. =TRIM(A1)видаляє всі, крім одного пробілу в A3.
До речі, що, якби проблема полягала в пробілах у стовпці F замість стовпця A? Додайте стовпець функцій TRIM () до E, вказуючи на стовпець F. Скопіюйте їх та вставте як значення у F, щоб пошук знову почав працювати.
Інша дуже часта причина того, що VLOOKUP не працюватиме, показана тут. Стовпець F має дійсні числа. У колонці A є текст, схожий на цифри.
Виділіть усі стовпці A. Натисніть alt = "" + D, E, F. Це робить текст за замовчуванням стовпцем і перетворює всі текстові числа у дійсні числа. Пошук знову починає працювати.
Переглянути відео
- VLOOKUP вирішує багато проблем
- Поширені проблеми VLOOKUP:
- Якщо VLOOKUP починає працювати, але # N / A стає помітнішим: забув $ в таблиці пошуку
- Кілька # N / A: елементів, відсутніх у таблиці
- Жодна з функцій VLOOKUP не працює: перевірте наявність кінцевих пробілів
- Видаліть кінцеві пробіли за допомогою TRIM
- Номери та числа, що зберігаються як текст
- Виділіть обидва стовпці та використовуйте alt = "" + DEF
- Епізод включає жарт, який і бухгалтерам, і ІТ-спеціалістам здається смішним, але з різних причин
Стенограма відео
Дізнайтеся Excel з подкасту, серія 2027 - Усунення несправностей VLOOKUP!
Добре, підкастируючи всю цю книгу, клацніть “i” у верхньому правому куті, щоб перейти до списку відтворення!
VLOOKUP - це моя улюблена функція в усьому Excel, і я завжди розповідаю цей жарт на одному зі своїх семінарів, і це сміється, незалежно від того, чи є ви ІТ-спеціалістом чи ні. Я завжди кажу: «Ну, дивіться, ми маємо ці дані тут ліворуч, і я можу переглянути ці дані і сказати, що дані надійшли з ІТ-відділу, оскільки це саме те, про що я просив, але насправді не те, що мені потрібно. Я запитав дату та кількість товару, і вони дали мені дату та кількість номера товару, але вони не потрудились дати мені опис, так? " І бухгалтери з цього завжди сміються, бо це з ними відбувається постійно, а ІТ-хлопці кажуть: "Ну, я дав вам те, про що ви просили, це не моя вина!" Тому вони обидва вважають, що це смішно з різних причин, тому, знаєте, а ІТ-фактор зайнятий, він не може повернутися до переписування запиту,тож ми повинні щось зробити, щоб самі врятувати це.
І ось ця таблиця, яку я скопіював звідкись на моєму комп’ютері, простою англійською мовою, що VLOOKUP робить, це говорить: „Гей, у нас є номер товару W25-6“. У нас тут є таблиця, я хочу пройти через перший стовпець таблиці, поки не знайду номер цього елемента, а потім повернути щось із цього рядка. Добре, у цьому випадку я хочу 2-го стовпця з цього рядка. І тоді в кінці кожного VLOOKUP ми повинні поставити або FALSE, або 0. Тепер прямо тут, після того, як я вибрав діапазон, я натисну клавішу F4, а потім я хочу 2-й стовпець, а потім FALSE для точного матч. Ніколи не вибирайте приблизний збіг, ніколи, ніколи! Це не приблизний збіг, це дуже особлива річ, це не працює постійно. Якщо ви хочете перерахувати свої номери в Комісію з цінних паперів та бірж, неодмінно використовуйте,TRUE або просто залиште, FALSE вимкнено. Але кожен VLOOKUP, який ви коли-небудь створювали, повинен закінчуватися, FALSE або, 0, 0 коротший за FALSE, ви повинні помістити FALSE туди, але 0 - це те саме, що FALSE.
Гаразд, усунення неполадок, по-перше, коли ви робите цілу купу VLOOKUP, цілком нормально мати пару # N / As, так? І я завжди знаходжу # N / A, переходячи в Data, ZA, що підносить # N / As до верху, прямо там, BG33-9, або це друкарська помилка, або це абсолютно новий предмет, добре, і ми отримали щоб це зрозуміти. Отож тут праворуч у мене є нові дані, я їх виріжу, а потім Alt + IED, вставляю ці клітинки в середину, вона не повинна бути алфавітною, цю таблицю не потрібно сортувати. Коли ви використовуєте, FALSE версію, таблиці немає - ви можете просто помістити її куди завгодно, я не став в кінці, тому що мені не потрібно було переписувати формулу, я просто хочу, щоб формула робота. Добре, отже, пара # N / A, надзвичайно, надзвичайно нормальна, але перевірте це.
Коли ви починаєте з відповідей, які працюють, але потім # N / A починають з’являтися трохи часто, а згодом вони з’являються до кінця, це вірна ознака того, що ви не заблокували посилання на таблицю. Добре бачимо, отже, тут таблиця рухається, коли я копіюю формулу вниз, вірно, і тому мені пощастило вдарити кілька, які були в кінці списку. Але врешті-решт я доходжу до точки, коли вона дивиться сюди в абсолютно порожніх клітинках, і, звичайно, нічого не виявляється. Добре, отже, це перше, що ви отримуєте пару, яка працює, кілька # N / A, ще пару, які працюють, а потім усі # N / A - решту шляху - впевнений знак, що ви не поставили $ в.
Просто поверніться назад, F2 для режиму редагування, виберіть двокрапку, натисніть F4, що вкладе всі $, двічі клацніть, щоб збити це, і все знову почне працювати, добре. Наступна, точно така ж формула, формула ідеальна, BG33-8 бачите, ми не отримуємо нічого з цього правильно. Гаразд, я йду дивитись, BG33-8, привіт, ось він, він там, він червоний, я мав це бачити! Отже, я підійшов до цього з правого боку, натискаю F2 і спостерігаю за миготливою точкою вставки, і бачу, це відразу після 8, ось так, а потім я підійшов сюди і натиснув F2, є деякі проміжки там. За часів COBOL це дуже, дуже часто, вони сказали б: "Ви знаєте, дивіться, ми дамо вам 10 пробілів для номера товару, і якщо ви не введете всі 10 пробілів, вони будуть заповнювати . " І це дуже часто,і чудовим рішенням тут є позбавлення від тих провідних та кінцевих пробілів за допомогою функції TRIM. Замість A2 використовуйте TRIM (A2), двічі клацніть, щоб збити, що все-таки BG33-9 повернувся в іншу таблицю.
Гаразд, щоб ви розуміли, як працює TRIM, я набрав купу пробілів, Джон, купу пробілів, Дурран і купу пробілів, а потім об’єднав знак * до і після, щоб ви могли побачити, як це виглядає . Коли я прошу TRIM (P4), ми позбавляємося всіх провідних пробілів, усіх кінцевих просторів, а потім безліч внутрішніх просторів зменшуються до одного простору. Гаразд, щоб ви могли побачити, начебто уявіть собі, що вони позбавляються провідних і кінцевих просторів, будь-які подвоєні пробіли посередині стають єдиним таким простором. Тож TRIM, чудовий, чудовий інструмент у вашому арсеналі, добре, ось ще один справді поширений.
Якщо це не кінцеві пробіли, то найпоширенішим, що я бачив, є те, де тут ми маємо справжні числа, а тут ми маємо числа, що зберігаються як текст. І VLOOKUP не побачить, що це збіг, хоча 4399, це число, це текст, не працює. Найшвидший спосіб перетворити стовпець тексту в цифри, вибрати стовпець, 3 літери послідовно, alt = "" DEF, і раптом наші VLOOKUP знову починають працювати, чудова, чудова підказка приблизно з 1500 подкастів тому. Добре, отже, це найпоширеніші проблеми VLOOKUP, або ви забули $, або у вас є кінцеві пробіли, або у вас є цифри та числа, що зберігаються як текст. Усі ці поради містяться в цій книзі “MrExcel XL”, клацніть “i” у верхньому правому куті, і книгу можна придбати.
Добре, швидкий підсумок: VLOOKUP вирішує багато проблем, якщо VLOOKUP починає працювати, але # N / A! стає помітнішим, ви забули помістити $ в таблицю пошуку. Якщо є кілька # N / A, це лише елементи, відсутні в таблиці. Якщо жоден з VLOOKUP не працює, і це текст, перевірте наявність кінцевих пробілів, ви можете використовувати функцію TRIM. Якщо у вас є номери та числа, що зберігаються як текст, виберіть будь-який стовпець, той, у якому є текст, а потім виконайте alt = "" DEF, щоб усі вони повернулися до чисел.
Добре, привіт, я хочу подякувати вам за заїзд, ми побачимось наступного разу для чергової трансляції від!
Завантажити файл
Завантажте зразок файлу тут: Podcast2027.xlsx
