Способи виправлення помилки #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). Значення банана не знайдено, тому формула повертає помилку #N/A. У цьому випадку в таблиці підстановки немає "Банан", тому функція VLOOKUP повертає помилку #N/A.

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

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

За допомогою символу "" можна нічого не відображати або замінити власний текст: =IFERROR(FORMULA();"Повідомлення про помилку")

Примітка.

Якщо вам потрібна допомога з помилкою #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 не відображаються на діаграмі. Нижче наведено приклади того, як виглядає діаграма з 0 і #N/A.

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