Помилка #N/A зазвичай означає, що формула не знаходить шуканого значення.
Найкраще рішення
Найпоширеніша причина помилки #N/A – функції XLOOKUP, VLOOKUP, HLOOKUP, LOOKUP або MATCH, якщо формула не може знайти значення, на яке посилається формула. Наприклад, шуканого значення немає у вихідних даних.
У даному випадку в таблиці підстановки немає елемента "Банан", тому функція VLOOKUP повертає помилку #N/A.
Вирішення. Переконайтеся, що значення підстановки існує у вихідних даних, або використайте у формулі обробник помилок, наприклад IFERROR. Наприклад, =IFERROR(FORMULA();0), яка говорить:
-
=IF(якщо результатом обчислення формули є помилка, слід відобразити 0, в іншому випадку – показати результат формули)
У формулі можна використовувати "", щоб не відображалося нічого, або підставити власний текст: =IFERROR(ФОРМУЛА();"Повідомлення про помилку")
Примітки.:
Якщо ви не знаєте, що робити на цьому етапі або яка допомога вам потрібна, ви можете знайти схожі запитання в спільноті Microsoft або опублікувати власне.
Якщо вам усе ще потрібна допомога з усуненням цієї помилки, наведений нижче контрольний список містить кроки з виправлення неполадок, які допоможуть з'ясувати, що могло виникнути у формулах.
Шукане значення та вихідні дані відносяться до різних типів. Наприклад, ви намагаєтеся використовувати посилання на функцію VLOOKUP як число, а вихідні дані збережено як текст.
Вирішення. Переконайтеся, що типи даних однакові. Щоб перевірити формати клітинок, виділіть клітинку або діапазон клітинок, клацніть їх правою кнопкою миші та виберіть пункт Формат клітинок > числовому форматі (або натисніть клавіші Ctrl+1) і за потреби змініть числовий формат.
Порада.: Якщо потрібно примусово змінити формат для цілого стовпця, спочатку застосуйте потрібний формат, а потім виберіть Дані > Текст за стовпцями > Готово.
Початкові та кінцеві пробіли можна видалити за допомогою функції TRIM. У наведеному нижче прикладі у функції VLOOKUP використовується вкладена функція TRIM для видалення початкових пробілів з імен у клітинках A2:A7 та повернення назви відділу.
=VLOOKUP(D2;TRIM(A2:B7);2;FALSE)
Примітка.: Формули динамічного масиву. Якщо у вас поточна версія Microsoft 365 і ви користувач каналу оновлень для учасників програми оцінювання Office із раннім доступом, ви можете ввести формулу у верхню ліву клітинку діапазону вихідних даних, а потім натиснути клавішу Enter, щоб підтвердити введення формули динамічного масиву. Інакше формулу знадобиться ввести по-старому, тобто спочатку вибрати діапазон вихідних даних, ввести формулу в його верхню ліву клітинку, а потім натиснути клавіші Ctrl+Shift+Enter, щоб підтвердити введення. Excel автоматично вставляє фігурні дужки на початку та в кінці формул. Докладні відомості про формули масивів див. в статті Приклади формул масивів і рекомендації.
За замовчуванням функції, які шукають дані в таблицях, має бути відсортовано за зростанням. Проте у функцій VLOOKUP і HLOOKUP є аргумент точність_пошуку, який повідомляє функції, що потрібно шукати точний збіг, навіть якщо таблицю не відсортовано. Щоб знайти точний збіг, установіть для аргументу точність_пошуку значення FALSE. Пам’ятайте, що використання значення TRUE, яке повідомляє функції про те, що потрібно шукати приблизний збіг, може призвести до повернення не лише помилки #N/A, але й помилкових результатів, як видно в прикладі нижче.
У цьому прикладі повертається не лише помилка #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))
Примітка.: Формули динамічного масиву. Якщо у вас поточна версія Microsoft 365 і ви користувач каналу оновлень для учасників програми оцінювання Office із раннім доступом, ви можете ввести формулу у верхню ліву клітинку діапазону вихідних даних, а потім натиснути клавішу Enter, щоб підтвердити введення формули динамічного масиву. Інакше формулу знадобиться ввести по-старому, тобто спочатку вибрати діапазон вихідних даних, ввести формулу в його верхню ліву клітинку, а потім натиснути клавіші Ctrl+Shift+Enter, щоб підтвердити введення. Excel автоматично вставляє фігурні дужки на початку та в кінці формул. Докладні відомості про формули масивів див. в статті Приклади формул масивів і рекомендації.
У даному випадку для місяців із травня по грудень вказано значення #N/A, тому підсумок обчислити не вдається й замість нього відображається помилка #N/A.
Щоб виправити це, перевірте синтаксис формули для функції, яку використовуєте, і введіть усі необхідні аргументи у формулу, яка повертає помилку. Імовірно, для перевірки функції вам знадобиться використовувати редактор Visual Basic. Відкрити цей редактор можна на вкладці "Розробник" або за допомогою клавіш Alt+F11.
Щоб виправити це, переконайтеся, що книгу, яка містить користувацьку функцію, відкрито, а функція працює належним чином.
Щоб виправити це, переконайтеся, що аргументи функції правильні та розташовані в належному порядку.
Щоб виправити цю помилку, натисніть клавіші Ctrl+Atl+F9 для повторного обчислення аркуша.
Якщо ви не знаєте, які аргументи слід використовувати, вам допоможе майстер функцій. Виберіть клітинку з потрібною формулою, а потім перейдіть на вкладку Формули та натисніть кнопку Вставити функцію.
Excel автоматично запустить майстер.
Клацніть будь-який аргумент, і Excel покаже вам відомості про нього.
Значення #N/A може принести користь. Значення #N/A часто використовуються в діаграмах із такими даними, як у наведеному нижче прикладі, оскільки ці значення не відображаються на діаграмі. У прикладах нижче показано, як виглядає діаграма з нульовими значеннями та значеннями #N/A.
У попередньому прикладі нульові значення відображено у вигляді прямої лінії вздовж нижнього краю діаграми, а потім лінія різко піднімається вгору, щоб показати результат. У наведеному нижче прикладі замість нульових значень використовуються значення #N/A.
Об'єкт Python не має визначеного представлення Excel. Об'єкт Python невідомий програмі Excel.
Потрібна додаткова довідка?
Ви завжди можете поставити запитання експерту в спільноті Tech у розділі Excel чи отримати підтримку в спільнотах.
Додаткові відомості
Перетворення чисел із текстового формату на числовий
Способи уникнення недійсних формул