Функція ВПР не працює – способи усунення помилок Н / Д, ІМ’Я та ЗНАЧ

Цей урок пояснює, як швидко впоратися з ситуацією, коли функція ВПР (VLOOKUP) не хоче працювати в Excel 2013, 2010 2007 і 2003 а також, як виявити і виправити поширені помилки і подолати обмеження ВПР.

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

У цій статті Ви знайдете прості пояснення помилок # N / A (# Н / Д), #NAME? (# ІМ’Я?) І #VALUE! (# Значить!), Що з’являються при роботі з функцією ВПР, а також прийоми і способи боротьби з ними. Ми почнемо з найбільш частих випадків і найбільш очевидних причин, чому ВПР не працює, тому краще вивчати приклади в тому порядку, в якому вони наведені в статті.

Виправляємо помилку # Н / Д функції ВПР в Excel

У формулах з ВПР повідомлення про помилку # N / A (# Н / Д) – означає not available (Немає даних) – з’являється, коли Excel не може знайти шукане значення. Це може відбутися з кількох причин.

1. Шукане значення написано з помилкою

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

2. Помилка # Н / Д при пошуку наближеного збіги з ВПР

Якщо Ви використовуєте формулу з умовою пошуку наближеного збіги, тобто аргумент range_lookup (Інтервальний_просмотр) дорівнює TRUE (ІСТИНА) або не вказано, Ваша формула може повідомити про помилку # Н / Д в двох випадках:

  • Шукане значення менше найменшого значення в просматриваемом масиві.
  • Стовпець пошуку не впорядкований по зростанню.

3. Помилка # Н / Д при пошуку точного збігу з ВПР

Якщо Ви шукаєте точний збіг, тобто аргумент range_lookup (Інтервальний_просмотр) дорівнює FALSE (ЛОЖЬ) і точне значення, не знайдено, формула також повідомить про помилку # Н / Д. Більш докладно про те, як шукати точне і наближене збіг з функцією ВПР.

4. Стовпець пошуку не є крайнім лівим

Як Ви, мабуть, знаєте, одна з найзначніших обмежень ВПР це те, що вона не може дивитися вліво, отже, стовпець пошуку у Вашій таблиці повинен бути крайнім лівим. На практиці ми часто забуваємо про це, що призводить до не працюючого формулою і появи помилки # Н / Д.

Рішення: Якщо немає можливості змінити структуру даних так, щоб стовпчик пошуку був крайнім лівим, Ви можете використовувати комбінацію функцій ІНДЕКС (INDEX) і ПОИСКПОЗ (MATCH), як більш гнучку альтернативу для ВПР.

5. Числа форматовані як текст

Інше джерело помилки # Н / Д в формулах з ВПР – це числа в текстовому форматі в основній таблиці або в таблиці пошуку.

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

Найбільш очевидні ознаки числа в текстовому форматі показані на малюнку нижче:

Крім цього, числа можуть бути збережені у форматі General (Загальний). В такому випадку є тільки один помітний ознака – числа вирівняні по лівому краю комірки, в той час як стандартно вони вирівнюються по правому краю.

Рішення: Якщо це одиночне значення, просто клікніть по іконці помилки і виберіть Convert to Number (Конвертувати в число) з контекстного меню.

Якщо така ситуація з багатьма числами, виділіть їх і натисніть по виділеній області правою кнопкою миші. У контекстному меню виберіть Format Cells (Формат ячеек) > вкладка Number (Число) > формат Number (Числовий) і натисніть ОК.

6. На початку або в кінці стоїть пробіл

Це найменш очевидна причина помилки # Н / Д в роботі функції ВПР, оскільки візуально важко побачити ці зайві прогалини, особливо при роботі з великими таблицями, коли велика частина даних знаходиться за межами екрану.

Рішення 1: Зайві пропуски в основній таблиці (там, де функція ВПР)

Якщо зайві прогалини виявилися в основній таблиці, Ви можете забезпечити правильну роботу формул, уклавши аргумент lookup_value (Шукане_значення) в функцію TRIM (СЖПРОБЕЛИ):

= VLOOKUP (TRIM ($ F2), $ A $ 2: $ C $ 10,3, FALSE)
= ВПР (СЖПРОБЕЛИ ($ F2); $ A $ 2: $ C $ 10; 3; ЛОЖЬ)

Рішення 2: Зайві пропуски в таблиці пошуку (в стовпці пошуку)

Якщо зайві прогалини виявилися в стовпці пошуку – простими шляхами помилку # Н / Д у формулі з ВПР не уникнути. замість ВПР Ви можете використовувати формулу масиву з комбінацією функцій ІНДЕКС (INDEX), ПОИСКПОЗ (MATCH) і СЖПРОБЕЛИ (TRIM):

= INDEX ($ C $ 2: $ C $ 10, MATCH (TRUE, TRIM ($ A $ 2: $ A $ 10) = TRIM ($ F $ 2), 0))
= ІНДЕКС ($ C $ 2: $ C $ 10; ПОИСКПОЗ (ІСТИНА; СЖПРОБЕЛИ ($ A $ 2: $ A $ 10) = СЖПРОБЕЛИ ($ F $ 2); 0))

Так як це формула масиву, не забудьте натиснути Ctrl + Shift + Enter замість звичного Enter, щоб правильно ввести формулу.

Помилка # значить! в формулах з ВПР

У більшості випадків, Microsoft Excel повідомляє про помилку #VALUE! (# Значить!), Коли значення, використане в формулі, не підходить за типом даних. Що стосується ВПР, то зазвичай виділяють дві причини помилки # Значить!.

1. Шукане значення довше 255 символів

Будьте уважні: функція ВПР не може шукати значення, що містять більше 255 символів. Якщо шукане значення перевищує цю межу, то Ви отримаєте повідомлення про помилку # Значить!.

Рішення: Використовуйте зв’язку функцій ІНДЕКС + ПОИСКПОЗ (INDEX + MATCH). Нижче представлена ​​формула, яка відмінно впорається з цим завданням:

= INDEX (C2: C7, MATCH (TRUE, INDEX (B2: B7 = F $ 2,0), 0))
= ІНДЕКС (C2: C7; ПОИСКПОЗ (ІСТИНА; ІНДЕКС (B2: B7 = F $ 2; 0); 0))

2. Не вказано повний шлях до робочої книзі для пошуку

Якщо Ви витягаєте дані з іншої робочої книги, то повинні вказати повний шлях до цього файлу. Якщо говорити точніше, Ви повинні вказати ім’я робочої книги (включаючи розширення) в квадратних дужках [], далі вказати ім’я аркуша, а потім – знак оклику. Всю цю конструкцію потрібно укласти в апострофи, на випадок якщо ім’я книги або аркуша містить прогалини.

Ось повна структура функції ВПР для пошуку в іншій книзі:

= VLOOKUP (lookup_value, ‘[workbook name] sheet name’! Table_array, col_index_num, FALSE)
= ВПР (шукане_значення; ‘[імя_кнігі] імя_ліста’! Таблиця; номер_стовпчика; ЛОЖЬ)

Справжня формула може виглядати так:

= VLOOKUP ($ A $ 2, ‘[New Prices.xls] Sheet1’! $ B: $ D, 3, FALSE)
= ВПР ($ A $ 2; ‘[New Prices.xls] Sheet1’! $ B: $ D; 3; ЛОЖЬ)

Ця формула буде шукати значення осередки A2 в стовпці B на аркуші Sheet1 в робочій книзі New Prices і витягувати відповідне значення зі стовпця D.

Якщо будь-яка частина шляху до таблиці пропущена, Ваша функція ВПР не працюватиме і повідомить про помилку # Значить! (Навіть якщо робоча книга з таблицею пошуку в даний момент відкрита).

Для отримання додаткової інформації про функції ВПР, посилається на інший файл Excel, зверніться до уроку:.

3. Аргумент номер_стовпчика менше 1

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

Отже, якщо трапилося, що аргумент col_index_num (Номер_стовпчика) менше 1, функція ВПР також повідомить про помилку # Значить!.

Якщо ж аргумент col_index_num (Номер_стовпчика) більше кількості стовпців в заданому масиві, ВПР повідомить про помилку #REF! (# Посилаючись!).

Помилка # ІМ’Я? в ВВР

Найпростіший випадок – помилка #NAME? (# ІМ’Я?) – з’явиться, якщо Ви випадково напишіть з помилкою ім’я функції.

Рішення очевидно – перевірте правопис!

ВПР не працює (обмеження, застереження і рішення)

Крім досить складного синтаксису, ВПР має більше обмежень, ніж будь-яка інша функція Excel. Через ці обмежень, прості на перший погляд формули з ВПР часто призводять до несподіваних результатів. Нижче Ви знайдете рішення для кількох поширених сценаріїв, коли ВПР помиляється.

1. ВВР не чутлива до регістру

функція ВПР не розрізняє регістр і приймає символи нижнього і ВЕРХНЬОГО регістру як однакові. Тому, якщо в таблиці є кілька елементів, які різняться тільки регістром символів, функція ВПР поверне перший-ліпший елемент, не дивлячись на регістр.

Рішення: Використовуйте іншу функцію Excel, яка може виконати вертикальний пошук (VIEW, СУММПРОИЗВ, ІНДЕКС та ПОИСКПОЗ) в поєднанні з збігаючись, яка розрізняє регістр. Більш детально Ви можете дізнатися з уроку – 4 способи зробити ВВР з урахуванням регістра в Excel.

2. ВВР повертає перше знайдене значення

Як ви вже знаєте, ВПР повертає із заданого стовпчика значення, відповідне першому знайденому збігом з шуканим. Однак, Ви можете змусити її витягти 2-е, 3-е, 4-тю чи якусь іншу повторення значення, яке Вам потрібно. Якщо потрібно витягти все повторювані значення, Вам потрібно комбінація з функцій ІНДЕКС (INDEX), НАЙМЕНШИЙ (SMALL) і СТРОКА (ROW).

3. У таблицю був доданий або вилучений стовпець

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

Рішення: І знову на допомогу поспішають функції ІНДЕКС (INDEX) і ПОИСКПОЗ (MATCH). У формулі ІНДЕКС + ПОИСКПОЗ Ви окремо задаєте стовпці для пошуку і для вилучення даних, і в результаті можете видаляти або вставляти скільки завгодно стовпців, не турбуючись про те, що доведеться оновлювати всі пов’язані формули пошуку.

4. Посилання на осередки спотворилися при копіюванні формули

Цей заголовок вичерпно пояснює суть проблеми, правда?

Рішення: Завжди використовуйте абсолютні посилання на комірки (з символом $) Під час запису діапазону, наприклад $ A $ 2: $ C $ 100 або $ A: $ C. У рядку формул Ви можете швидко перемикати тип посилання, натискаючи F4.

ВПР – робота з функціями ЕСЛІОШІБКА і ЕОШИБКА

Якщо Ви не хочете лякати користувачів повідомленнями про помилки # Н / Д, # Значить! або # ІМ’Я?, можете показувати вільну позицію або власне повідомлення. Ви можете зробити це, помістивши ВПР в функцію ЕСЛІОШІБКА (IFERROR) в Excel 2013, 2010 і 2007 або використовувати зв’язку функцій ЯКЩО + ЕОШИБКА (IF + ISERROR) в більш ранніх версіях.

ВПР: робота з функцією ЕСЛІОШІБКА

синтаксис функції ЕСЛІОШІБКА (IFERROR) простий і каже сам за себе:

IFERROR (value, value_if_error)
ЕСЛІОШІБКА (значення; значеніе_еслі_ошібка)

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

Наприклад, ось така формула повертає порожню осередок, якщо шукане значення, не знайдено:

= IFERROR (VLOOKUP ($ F $ 2, $ B $ 2: $ C $ 10,2, FALSE), “”)
= ЕСЛІОШІБКА (ВВР ($ F $ 2; $ B $ 2: $ C $ 10; 2; ЛОЖЬ); “”)

Якщо Ви хочете показати власне повідомлення замість стандартного повідомлення про помилку функції ВПР, впишіть його в лапках, наприклад, так:

= IFERROR (VLOOKUP ($ F $ 2, $ B $ 2: $ C $ 10,2, FALSE), “Нічого не знайдено. Спробуйте ще раз!”)
= ЕСЛІОШІБКА (ВВР ($ F $ 2; $ B $ 2: $ C $ 10; 2; ЛОЖЬ); “Нічого не знайдено. Спробуйте ще раз!”)

ВПР: робота з функцією ЕОШИБКА

Так як функція ЕСЛІОШІБКА з’явилася в Excel 2007, при роботі в більш ранніх версіях Вам доведеться використовувати комбінацію ЯКЩО (IF) і ЕОШИБКА (ISERROR) ось так:

= IF (ISERROR (VLOOKUP формула), “Ваше повідомлення при помилку”, VLOOKUP формула)
= ЕСЛИ (ЕОШИБКА (ВВР формула); “Ваше повідомлення при помилку”; ВВР формула)

Наприклад, формула ЯКЩО + ЕОШИБКА + ВПР, аналогічна формулі ЕСЛІОШІБКА + ВПР, показаної вище:

= IF (ISERROR (VLOOKUP ($ F $ 2, $ B $ 2: $ C $ 10,2, FALSE)), “”, VLOOKUP ($ F $ 2, $ B $ 2: $ C $ 10,2, FALSE))
= ЕСЛИ (ЕОШИБКА (ВВР ($ F $ 2; $ B $ 2: $ C $ 10; 2; ЛОЖЬ)); “”; ВПР ($ F $ 2; $ B $ 2: $ C $ 10; 2; ЛОЖЬ))

На сьогодні все. Сподіваюся, цей короткий підручник допоможе Вам впоратися з усіма можливими помилками ВПР і змусить Ваші формули працювати правильно.

Ссылка на основную публикацию