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

Застосовується до

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel 2024 Excel 2024 для Mac Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2016 Excel для iPad Excel Web App Excel для iPhone Excel для планшетів Android Excel для телефонів Android Excel для Windows Phone 10 Excel Mobile

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

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

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

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

Вирішення. Переконайтеся, що шукане значення наявне у вихідних даних, або використайте у формулі обробник помилок, як-от IFERROR. Наприклад, =IFERROR(FORMULA();0), наприклад:

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

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

Примітка.

Якщо вам потрібна довідка з помилки #N/A для конкретної функції, як-от VLOOKUP або INDEX/MATCH, виберіть один із цих варіантів:
Виправлення помилки #N/A у функції VLOOKUP
Виправлення помилки #N/A у функціях INDEX/MATCH
Також було б корисно дізнатися про деякі поширені функції, де виникає ця помилка, наприклад XLOOKUP,VLOOKUP,HLOOKUP, LOOKUP або MATCH.

Якщо ви не знаєте, що робити на цьому етапі або яка допомога вам потрібна, ви можете пошукати схоже запитання в спільноті Microsoft або опублікувати власне запитання.

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

Неправильні типи значень

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

Неправильні типи значень. Приклад формули VLOOKUP, яка повертає помилку #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)

Примітка.

Формули динамічного масиву. Якщо у вас поточна версія Microsoft 365 і ви використовуєте канал оцінювання з раннім доступом, введіть формулу у верхню ліву клітинку діапазону вихідних даних, а потім натисніть клавішу Enter , щоб підтвердити, що формула динамічного масиву. Інакше формулу знадобиться ввести по-старому, тобто спочатку вибрати діапазон вихідних даних, ввести формулу в його верхню ліву клітинку, а потім натиснути клавіші Ctrl+Shift+Enter, щоб підтвердити введення. Excel автоматично вставляє фігурні дужки на початку та в кінці формул. Докладні відомості про формули масивів див. в статті Приклади формул масивів і рекомендації.

Використання методу приблизного або точного збігу (TRUE/FALSE)

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

Приклад використання функції VLOOKUP з аргументом TRUE range_lookup може призвести до помилкових результатів. У цьому прикладі повертається не лише помилка #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))

Примітка.

Якщо ви ввели в клітинки значення #N/A або NA() вручну, тому що не мали даних, замініть це значення на фактичні дані, коли вони з'являться. Доки ви цього не зробите, формули, які звертаються до цих клітинок, не зможуть обчислити значення та повернуть помилку #N/A.

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

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

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

Введена вами користувацька функція недоступна.

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

Використовується макрос із функцією, яка повертає значення #N/A

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

Під час змінення захищеного файлу, який містить такі функції, як CELL, у клітинках виводяться помилки #N/A

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

Потрібна допомога з аргументами функції?

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

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

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

Використання значення #N/A у діаграмах

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

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