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

Помилка #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, виберіть один із таких параметрів:
Крім того, може знадобитися дізнатися про деякі поширені функції, у яких виникає ця помилка, наприклад 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 і ви користувач каналу оновлень для учасників програми оцінювання Office із раннім доступом, ви можете ввести формулу у верхню ліву клітинку діапазону вихідних даних, а потім натиснути клавішу Enter, щоб підтвердити введення формули динамічного масиву. Інакше формулу знадобиться ввести по-старому, тобто спочатку вибрати діапазон вихідних даних, ввести формулу в його верхню ліву клітинку, а потім натиснути клавіші Ctrl+Shift+Enter, щоб підтвердити введення. Excel автоматично вставляє фігурні дужки на початку та в кінці формул. Докладні відомості про формули масивів див. в статті Приклади формул масивів і рекомендації.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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