Способи виправлення помилки #N/A

Способи виправлення помилки #N/A

Помилка #N/A зазвичай означає, що формула не знаходить шуканого значення.

Найкраще рішення

Найчастіше появу помилки #N/A обумовлено тим, що формула не може знайти значення, на яке посилається функція VLOOKUP, HLOOKUP, LOOKUP або MATCH. Наприклад, шуканого значення немає у вихідних даних.

Шуканого значення не існує.  Клітинка E2 містить формулу =VLOOKUP(D2;$D$6:$E$8;2;FALSE)  Значення "Банан" не знайдено, тому формула повертає помилку #N/A.
Елемент не знайдено у вихідних даних

У даному випадку в таблиці підстановки немає елемента "Банан", тому функція VLOOKUP повертає помилку #N/A.

Рішення. Переконайтеся, що у вихідних даних є шукане значення, або використовуйте у формулі обробник помилок, наприклад функцію IFERROR. Наприклад, формула =IFERROR(ФОРМУЛА();0) означає:

  • =IF(якщо результатом обчислення формули є помилка, слід відобразити 0, в іншому випадку – показати результат формули)

У формулі можна використовувати "", щоб не відображалося нічого, або підставити власний текст: =IFERROR(ФОРМУЛА();"Повідомлення про помилку")

Не знаєте, як діяти далі або в чому проблема? Спробуйте знайти схожі запитання на форумі спільноти Microsoft Excel чи опублікуйте своє запитання.

Посилання на форум спільноти Excel

Якщо ви вирішили виправити формулу, скористайтеся наведеним нижче контрольним списком, у якому описано типові проблеми й способи їх вирішення.

Шукане значення та вихідні дані відносяться до різних типів. Наприклад, ви намагаєтеся використовувати посилання на функцію VLOOKUP як число, а вихідні дані збережено як текст.

Неправильні типи значень.  Приклад формули VLOOKUP, яка повертає помилку #N/A через те, що шуканий елемент має числовий формат, а таблиця підстановки – текстовий.
Помилка #N/A через різні типи даних

Рішення. Переконайтеся, що типи даних збігаються. Перевірте формати клітинок. Для цього виділіть діапазон клітинок, клацніть правою кнопкою миші, виберіть Формат клітинок > Число (або натисніть клавіші Ctrl+1) і за потреби змініть числовий формат.

Діалогове вікно "Формат клітинок" із відкритою вкладкою "Число" та виділеним пунктом "Текстовий"

Порада : Якщо потрібно примусово змінити формат для цілого стовпця, спочатку застосуйте потрібний формат, а потім виберіть Дані > Текст за стовпцями > Готово.

Початкові та кінцеві пробіли можна видалити за допомогою функції TRIM. У наведеному нижче прикладі у функції VLOOKUP використовується вкладена функція TRIM для видалення початкових пробілів з імен у клітинках A2:A7 та повернення назви відділу.

Використання функції VLOOKUP із вкладеною функцією TRIM у формулі масиву для видалення початкових і кінцевих пробілів.  Клітинка E3 містить формулу {=VLOOKUP(D2;TRIM(A2:B7);2;FALSE)}, для введення якої потрібно натиснути клавіші Ctrl+Shift+Enter.

{=VLOOKUP(D2;TRIM(A2:B7);2;FALSE)}

Примітка : Це формула масиву, і для її введення потрібно натиснути клавіші Ctrl+Shift+Enter. Excel автоматично бере формулу у фігурні дужки {}. Якщо ви спробуєте ввести їх вручну, Excel відобразить формулу як текст.

За замовчуванням функції, які шукають дані в таблицях, має бути відсортовано за зростанням. Проте у функцій VLOOKUP і HLOOKUP є аргумент точність_пошуку, який повідомляє функції, що потрібно шукати точний збіг, навіть якщо таблицю не відсортовано. Щоб знайти точний збіг, установіть для аргументу точність_пошуку значення FALSE. Пам’ятайте, що використання значення TRUE, яке повідомляє функції про те, що потрібно шукати приблизний збіг, може призвести до повернення не лише помилки #N/A, але й помилкових результатів, як видно в прикладі нижче.

Приклад використання функції VLOOKUP зі значенням TRUE для аргументу "точність_пошуку", при якому можливі помилкові результати.
Функція VLOOKUP повертає помилку через застосування аргументу приблизного збігу в невідсортованій таблиці

У цьому прикладі повертається не лише помилка #N/A для елемента "Банан", але й неправильна ціна для елемента "Груша". Такий результат викликає аргумент TRUE, який повідомляє функції VLOOKUP, що потрібно шукати не точний, а приблизний збіг. Тут немає близького збігу для елемента "Банан", а "Груша" за алфавітом передує елементу "Персик". У цьому випадку в разі використання функції VLOOKUP з аргументом FALSE буде відображатися правильна ціна для елемента "Груша", але для елемента "Банан" все одно буде вказано помилку #N/A, тому що в списку підстановки його немає.

Якщо ви використовуєте функцію MATCH, спробуйте змінити значення аргументу тип_зіставлення, щоб визначити порядок сортування таблиці. Щоб знайти точний збіг, установіть для аргументу тип_зіставлення значення 0 (нуль).

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

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

Приклад формули масиву з посиланнями на невідповідні діапазони, що викликає помилку #N/A.  Клітинка E2 містить формулу {=SUM(IF(A2:A11=D2;B2:B5))}, для введення якої потрібно натиснути клавіші Ctrl+Shift+Enter.

{=SUM(IF(A2:A11=D2;B2:B5))}

Щоб формула обчислювалася правильно, потрібно змінити її так, щоб обидва діапазони включали рядки 2–11.

{=SUM(IF(A2:A11=D2;B2:B11))}

Примітка : Це формула масиву, і для її введення потрібно натиснути клавіші Ctrl+Shift+Enter. Excel автоматично бере формулу у фігурні дужки {}. Якщо ви спробуєте ввести їх вручну, Excel відобразить формулу як текст.

Приклад введеного в клітинки значення #N/A, яке перешкоджає правильному обчисленню формули SUM.

У даному випадку для місяців із травня по грудень вказано значення #N/A, тому підсумок обчислити не вдається й замість нього відображається помилка #N/A.

Щоб виправити це, перевірте синтаксис формули для функції, яку використовуєте, і введіть усі необхідні аргументи у формулу, яка повертає помилку. Імовірно, для перевірки функції вам знадобиться використовувати редактор Visual Basic. Відкрити цей редактор можна на вкладці "Розробник" або за допомогою клавіш Alt+F11.

Щоб виправити це, переконайтеся, що книгу, яка містить користувацьку функцію, відкрито, а функція працює належним чином.

Щоб виправити це, переконайтеся, що аргументи функції правильні та розташовані в належному порядку.

Щоб виправити цю помилку, натисніть клавіші Ctrl+Atl+F9 для повторного обчислення аркуша.

Якщо ви не знаєте, які аргументи слід використовувати, вам допоможе майстер функцій. Виберіть клітинку у формулі, яка викликає у вас сумніви, потім відкрийте вкладку Формули та натисніть кнопку Вставити функцію.

Кнопка "Вставити функцію".

Excel автоматично запустить майстер.

Приклад діалогового вікна майстра функцій.

Клацніть будь-який аргумент, і Excel покаже вам відомості про нього.

Значення #N/A може принести користь. Значення #N/A часто використовуються в діаграмах із такими даними, як у наведеному нижче прикладі, оскільки ці значення не відображаються на діаграмі. У прикладах нижче показано, як виглядає діаграма з нульовими значеннями та значеннями #N/A.

Приклад лінійчатої діаграми, на якій відображаються нульові значення.

У попередньому прикладі нульові значення відображено у вигляді прямої лінії вздовж нижнього краю діаграми, а потім лінія різко піднімається вгору, щоб показати результат. У наведеному нижче прикладі замість нульових значень використовуються значення #N/A.

Приклад лінійчатої діаграми, на якій не відображаються значення #N/A.

Докладні відомості про помилку #N/A у певній функції див. в статтях нижче:

На початок сторінки

Потрібна додаткова довідка?

Ви завжди можете поставити запитання експерту в спільноті Tech (у розділі Excel), отримати підтримку в спільноті, що допомагає знайти відповіді на запитання, або запропонувати нову функцію чи вдосконалення на форумі Excel User Voice.

Див. також

Перетворення чисел із текстового формату на числовий

Функція VLOOKUP

HLOOKUP (функція HLOOKUP)

LOOKUP (функція LOOKUP)

MATCH (функція MATCH)

Огляд формул у програмі Excel

Способи уникнення недійсних формул

Виправлення поширених помилок у формулах за допомогою функції перевірки помилок

Сполучення клавіш у програмі Excel для Windows

Сполучення клавіш у програмі Excel для Mac

Функції Excel (за алфавітом)

Функції Excel (за категоріями)

Отримуйте нові функції раніше за інших
Приєднайтеся до оцінювачів Office

Ця інформація корисна?

Дякуємо за ваш відгук!

Дякуємо за відгук! Схоже, вам може стати в нагоді допомога одного з наших спеціалістів служби підтримки Office, з яким ми вас можемо з’єднати.

×