Способи виправлення помилки #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(ФОРМУЛА();"Повідомлення про помилку")

Примітки.:

Якщо вам потрібна допомога з помилкою #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.

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

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

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

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

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

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

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

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

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

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

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

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