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

У цьому розділі описані найпоширеніші причини отримання помилкових результатів під час використання функції ВПР. Надаються рекомендації щодо використання функцій ІНДЕКС та ПОШУКПОЗ замість неї.

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

Проблема: потрібне значення не знаходиться в першому стовпці аргументу таблиця

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

У наступній таблиці нам потрібно дізнатись кількість проданої капусти.

Помилка #Н/Д #N/A виникає, оскільки значення пошуку "Капуста" знаходиться у другому стовпці (Продукти) аргументу таблиця A2: C10. У цьому випадку Excel шукає значення у стовпці A, а не у стовпці B.

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

Спробуйте використовувати функції ІНДЕКС та ПОШУКПОЗ

Функції ІНДЕКС і ПОШКОЗ можна ефективно застосовувати в багатьох випадках, коли функція ВПР не дозволяє отримати потрібні результати. Основна їх перевага полягає в тому, що значення можна шукати в стовпці таблиці будь-якої позиції в таблиці підстановки. Функція ІНДЕКС повертає значення із зазначеної таблиці або діапазону відповідно до його позиції. Функція ПОШУКПОЗ повертає відносну позицію значення в діапазоні або таблиці. Використовуючи функції ІНДЕКС і ПОШУКПОЗ разом, можна знаходити значення в таблиці або масиві, вказавши відносну позицію значення.

Існує кілька переваг використання функцій ІНДЕКС та ПОШУКПОЗ замість ВПР.

• При використанні функцій ІНДЕКС і ПОШУКПОЗ значення, що повертається, не обов'язково повинно знаходитися в тому ж стовпці, що і стовпець підстановки. При використанні функції ВПР значення, що повертається, навпаки, має бути в зазначеному діапазоні. Чому це важливо? При використанні функції ВВР вам потрібно знати номер стовпця, що містить значення. Це може здатися не надто складним, але це серйозно ускладнює роботу, якщо використовується велика таблиця, в якій потрібно підрахувати кількість стовпців. Крім того, якщо додати або видалити стовпець. доведеться перерахувати стовпці та змінити значення аргументу номер_стовпця. При використанні функцій ІНДЕКС та ПОШУКПОЗ не потрібно підраховувати стовпці.
• При використанні функцій ІНДЕКС та ПОШУКПОЗ можна вказати рядок або стовпець (або рядок, і стовпець) у масиві. Це означає, що значення можна шукати по вертикалі та по горизонталі.
• За допомогою функцій ІНДЕКС та ПОШУКПОЗ можна знаходити значення у будь-якому стовпці. На відміну від функції ВПР, яка знаходить лише значення у першому стовпці таблиці, функції ІНДЕКС і ПОШУКПОЗ працюватимуть незалежно від цього, у якому стовпці перебуває значення.
• Це дозволяє використовувати динамічні посилання на стовпець, що містить значення, що повертається.Таким чином, ці функції будуть працювати, навіть якщо ви додаєте стовпці до таблиці. З іншого боку, ВПР не зможе знайти значення, якщо додати стовпець таблицю, оскільки ця функція використовує статичне посилання таблицю.
• Функції ІНДЕКС та ПОШУКПОЗ забезпечують гнучкіші можливості пошуку.Вони можуть знаходити точний збіг, а також значення більше або менше шуканого. ВПР шукає лише найближче (за умовчанням) чи точне значення. Крім того, функція ВПР передбачає, що перший стовпець у таблиці відсортований в алфавітному порядку, і повертає перший найближчий збіг, тому ви можете отримати не ті дані, які очікували.

Синтаксис

Щоб створити синтаксис для функцій ІНДЕКС або ПОШУКПОЗ, необхідно вкласти синтаксис функції ПОШУКПОЗ в аргумент масиву або посилання функції ІНДЕКС. Це виглядає так:

= ІНДЕКС (масив або посилання; ПОШУКПОЗ (пошукова_значення; масив; [тип_збігу])

Замінимо функцію ВПР у наведеному вище прикладі функціями ІНДЕКС та ПОШУКПОЗ. Синтаксис виглядатиме так:

=ІНДЕКС(C2:C10;ПОШУКПОЗ(B13;B2:B10;0))

=ІНДЕКС(повернути значення з C2:C10, яке буде відповідати ПОШУКПОЗ(перше значення "Капуста" в масиві B2:B10))

Формула шукає в C2:C10 перше значення, що відповідає значенню Капуста (B7), і повертає значення в комірці C7 (100).

Проблема: не знайдено точного збігу

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

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

Також можна використовувати функції ПЕЧСИМВ або СЖПРОБЕЛИ для очищення даних у осередках.

Проблема: потрібне значення менше, ніж найменше значення в масиві

Якщо для аргументу діапазон_пошуку задано значення ІСТИНА, а шукане значення менше найменшого значення масиві, повертається помилка #Н/Д. Функція шукає приблизний збіг у масиві і повертає найближче значення, яке менше шуканого.

У наведеному нижче прикладі потрібне значення дорівнює 100але в діапазоні B2:C10 немає значень менше 100, тому виникає помилка.

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

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

Якщо для аргументу діапазон_пошуку задано значення ІСТИНА, але з стовпців не відсортований за зростанням (від А до Я), повертається помилка #Н/Д.

• Змініть функцію ВПР так, щоб шукати точний збіг. Для цього вкажіть для аргументу діапазон_пошуку значення Брехня. Для значення брехня сортування не потрібне.
• Для пошуку значення в несортованій таблиці також можна використовувати функції ІНДЕКС і ПОШУКПОЗ.

Проблема: значення є великим числом з плаваючою комою

За наявності в осередках значень часу або великих десяткових чисел Excel повертає помилку "#Н/Д" через точність чисел з плаваючою комою. Числа з плаваючою комою включають цифри після десяткової коми. (Значення часу зберігаються в Excel у вигляді чисел з плаваючою комою.) Excel не може зберігати великі числа з плаваючою комою, тому для правильної роботи функції такі числа потрібно округлювати до 5 десяткових розрядів.

Рішення. Округліть числа до 5 десяткових розрядів за допомогою функції ОКРУГЛ.

Додаткові відомості

Ви завжди можете поставити запитання експерту в Excel Tech Community або отримати підтримку у спільнотах.

також

• Виправлення помилки #Н/Д
• ВПР: як позбутися помилок #Н/Д
• Арифметичні операції зі значеннями з плаваючою комою можуть видавати неточні результати в Excel
• Короткий довідник: функція ВПР
• Функція ВВР
• Повні відомості про формули в Excel
• Рекомендації, що дозволяють уникнути появи непрацюючих формул
• Пошук помилок у формулах
• Усі функції Excel (за абеткою)
• Функції Excel (за категоріями)

Функція ЕСНД для перевірки осередків на помилки НД в Excel

Функція ЕСНД в Excel призначена для перевірки даних, що вводяться (наприклад, результатів, що повертаються функціями) і повертає альтернативний результат, зазначений у вигляді другого аргументу, якщо формула, введена як перший аргумент, повертає код помилки #Н/Д, або результат виконання цієї формули якщо зазначена помилка не виникає.

Як виправити помилки НД у осередках таблиці Excel

Excel має функцію ЕНД, яка також виконує перевірку даних на наявність помилки #Н/Д.Однак, вона може повертати лише одне з двох можливих значень: ІСТИНА – якщо помилка #Н/Д виникла, і БРЕХНЯ, якщо помилки немає. В ЄСНД передбачений функціонал виконання альтернативної дії, тому вона більш зручна у використанні і дозволяє скоротити довжину формул, що записуються.

Приклад 1. У таблиці міститься ряд деяких числових значень. Створити формулу для пошуку будь-яких числових значень у цьому числовому ряду, яка у разі відсутності збігів відобразить зрозуміле рядовому користувачеві повідомлення замість коду помилки #Н/Д.

Вид таблиці даних:

У комірку C2 запишемо таку формулу:

Функція ПОШУКПОЗ використовується (в даному випадку) для пошуку точного збігу шуканого значення з наявними в масиві чисел. Якщо такий збіг відсутній, буде повернено код помилки #Н/Д. ЄСНД перехопить помилку та поверне текстовий рядок із поясненням.

Приклади пошуку значень:

Тепер за умови виникнення помилки НД формула автоматично виправляє на текстове значення "відсутня" в осередку Excel. Якщо значення в осередку B2 знайдено:

Через війну обчислення формули отримуємо відповідний результат.

Приклад виправлення помилок із кодом НД у формулах Excel

Приклад 2. У стовпці записані деякі дані, серед яких є коди помилок #Н/Д. Необхідно підсумовувати комірки з помилками #Н/Д та числовими значеннями.

Вида таблиці даних:

Для розрахунків використовуємо таку формулу масиву CTRL+SHIFT+Enter:

Функція ЕСНД переглядає масив даних (A2:A13) і під час знаходження коду помилки #Н/Д виводить число 0.

В результаті отримаємо:

Правила використання функції ЄСНД в Excel

Функція має наступний синтаксичний запис:

Опис аргументів (кожен обов'язковий для заповнення):

• значення - приймає дані, які будуть перевірені на наявність помилки #Н/Д. Може бути зазначений у вигляді посилання на комірку, вирази чи формули;
• значення_при_помилці – приймає дані, які будуть повернуті у випадку, якщо в значенні, що перевіряється, була виявлена ​​помилка #Н/Д.

1. Якщо в якості аргументу значення цієї функції було передано посилання на порожню комірку, результатом виконання функції буде числове значення 0. Наприклад, результатом виконання =ТІП(ЕСНД(A1;A2)) буде число 1 (відповідає числовому типу даних), якщо комірки A1 і A2 були порожніми.
2. Якщо ЄСПД отримує код помилки #Н/Д як перший аргумент і посилання на порожню комірку як другий, результатом її виконання також буде число 0.
3. Ще до появи цієї функції в Excel доводилося використовувати конструкцію формули: =ЯКЩО(ЕНД(перевірене_значення);якщо_помилка_є;якщо_помилки_ні).

• Створити таблицю
• Форматування
• Функції Excel
• Формули та діапазони
• Фільтр та сортування
• Діаграми та графіки
• Зведені таблиці
• Друк документів
• Бази даних та XML
• Можливості Excel
• Налаштування параметри
• Уроки Excel
• Макроси VBA
• Завантажити приклади