Перейти до основного
Підтримка
Вхід
Вхід за допомогою облікового запису Microsoft
Увійдіть або створіть обліковий запис.
Вітаємо,
Виберіть інший обліковий запис.
У вас є кілька облікових записів
Виберіть обліковий запис, за допомогою якого потрібно ввійти.
Способи уникнення недійсних формул

Якщо програмі Excel не вдалось обчислити формулу, може з’явитися таке повідомлення про помилку:

Зображення діалогового вікна "Ця формула містить помилку" в програмі Excel

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

Порада.: Існує кілька поширених функцій, у яких можуть виникнути проблеми. Докладні відомості див. в статті COUNTIF, SUMIF, VLOOKUP або IF. Список функцій також можна переглянути тут.

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

Зображення кнопки "Скасувати" в рядку формул

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

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

Excel кидає різноманітні помилки фунта (#), такі як #VALUE!, #REF!, #NUM, #N/A, #DIV/0!, #NAME?, і #NULL!, щоб указати, що щось у формулі не працює належним чином. Наприклад, #VALUE! через неправильне форматування або непідтримувані типи даних в аргументах. Або відобразиться #REF! якщо формула посилається на клітинки, які було видалено або замінено на інші дані. Інструкції з виправлення неполадок різняться для кожної помилки.

Примітка.: #### – це помилка, яка не пов’язана з синтаксисом формули. Вона вказує на те, що стовпець не досить широкий, щоб відобразити вміст клітинки. Просто перетягніть межу стовпця, щоб розширити його, або послідовно виберіть елементи Основне > Формат > Автодобір ширини стовпця.

Зображення, що ілюструє вибір елементів "Основне > Формат > Автодобір ширини стовпця"

Перегляньте будь-яку з наведених нижче статей, які відповідають похибці фунта, яку ви бачите:

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

Діалогове вікно з повідомленням про недійсне посилання в програмі Excel

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

Крім того, можна зробити так, щоб це діалогове вікно не відображалося під час запуску програми. Для цього перейдіть до розділу Параметри > > Додаткові > Загальні та зніміть прапорець Запитувати про оновлення автоматичних посилань.

Зображення параметра "Запитувати про оновлення автоматичних зв’язків"

Увага!: Якщо ви вперше працюєте з недійсними посиланнями у формулах, якщо вам потрібен освіжник для вирішення проблем із недійсними посиланнями або якщо ви не знаєте, чи оновлювати посилання, див. статтю Керування оновленням зовнішніх посилань (посилань).

Якщо у формулі не відображається значення, зробіть ось що:

  • Переконайтеся, що в excel налаштовано відображення формул в електронній таблиці. Для цього перейдіть на вкладку Формули та в групі Аудит формули натисніть кнопку Показати формули.

    Порада.: Також можна скористатися сполученням клавіш Ctrl +' (клавіша над клавішею табуляції). Після цього стовпці автоматично розширяться для відображення формул, але не хвилюйтеся, якщо повернутися до звичайного подання, розмір стовпців буде змінюватися.

  • Якщо наведений вище крок усе одно не вирішує проблему, можливо, клітинку відформатовано як текст. Щоб змінити формат, можна клацнути клітинку правою кнопкою миші та послідовно вибрати елементи Формат клітинок > Загальний (або натиснути сполучення клавіш Ctrl+1), а потім послідовно натиснути клавіші F2 > Enter.

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

    Зображення діалогового вікна, яке можна відкрити, вибравши елементи "Дані > Текст за стовпцями"

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

  1. Перейдіть на вкладку Файл , натисніть кнопку Параметри, а потім виберіть категорію Формули .

  2. У розділі Параметри обчислення в області Обчислення робочої книги виберіть параметр автоматично.

    Зображення параметрів обчислення "автоматично" та "вручну"

Докладні відомості про обчислення див. в статті Змінення переобчислення, ітерації або точності формули.

Циклічне посилання відбувається, коли формула посилається на клітинку, у якій вона розташована. Виправлення – перемістити формулу до іншої клітинки або змінити синтаксис формули на такий, що дозволяє уникнути циклічного посилання. Проте в деяких випадках циклічні посилання можуть виявитися корисними, оскільки вони забезпечують обчислення функцій у кілька ітерацій (повторюване обчислення, яке триває, доки не виконається певна числова умова). У таких випадках потрібно ввімкнути параметр Видалити або дозволити циклічне посилання.

Докладні відомості про циклічні посилання див. в статті Видалення або дозвіл циклічного посилання.

Якщо запис не починається зі знака рівності, він не вважається формулою й не обчислюється. Це поширена помилка.

Якщо ввести щось на кшталт SUM(A1:A10), в Excel замість результату формули відображатиметься текстовий рядок SUM(A1:A10). Крім того, якщо ввести 11/2, у програмі Excel замість ділення 11 на 2 відображається дата, наприклад 2 листопада або 02.11.2009.

Щоб уникнути таких несподіваних результатів, завжди починайте функцію зі знака рівності. Наприклад, введіть =SUM(A1:A10) і =11/2.

Коли у формулі є функція, для її належного функціонування потрібно закрити всі відкривні дужки. Переконайтеся, що кожна дужка має відповідну пару. Наприклад, формула =IF(B5<0);"Неприпустима",B5*1,05) не працюватиме, оскільки є дві закривні дужки, але лише одна відкривна дужка. Правильна формула виглядає так: =IF(B5<0,"Неприпустимо",B5*1,05).

Функції Excel мають аргументи (значення, необхідні для роботи функції). Лише кілька функцій, наприклад PI або TODAY, не мають аргументів. Перегляньте синтаксис формули, який відображається, якщо почати вводити ім’я функції, і переконайтеся, що введено всі потрібні аргументи.

Наприклад, функція UPPER приймає тільки один рядок тексту або посилання на клітинку як аргумент: =UPPER("привіт") або =UPPER(C2).

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

Знімок екрана, на якому показано довідкову панель інструментів

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

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

Не вводьте у формулах числа, відформатовані за допомогою знаків долара ($) або десяткових роздільників (,), оскільки знаки долара вказують на абсолютні посилання , а коми – це роздільники аргументів. Замість $1000 введіть у формулі лише число 1000.

Якщо в аргументах використовуються відформатовані числа, ви отримаєте неочікувані результати обчислення, але також може відобразитися помилка #NUM! . Наприклад, якщо ввести формулу =ABS(-2,134), щоб знайти абсолютне значення -2134, програма Excel відобразить #NUM! оскільки функція ABS приймає лише один аргумент, і вона бачить -2 та 134 як окремі аргументи.

Примітка.: Символ валюти можна додати до результату формули пізніше, а для обчислення використовувати лише числа без жодних символів і знаків (константи). Зазвичай не варто додавати константи до формул, тому що їх важко знайти, якщо потрібно оновити систему пізніше, і вони більш схильні до введення даних неправильно. Набагато краще розташувати константи в клітинках, де вони відкриті та легко на них посилаються.

Формула може не повернути потрібні результати, якщо тип даних клітинки не можна використовувати в обчисленнях. Наприклад, якщо в клітинку, відформатовану як текст, введено просту формулу =2+3, у програмі Excel обчислення введених даних може не відбутися. У клітинці відобразиться лише =2+3. Щоб виправити таку помилку, змініть для клітинки тип даних Текстовий на Загальний

  1. Виділіть клітинку.

  2. Натисніть кнопку Основне та клацніть стрілку, щоб розгорнути групу Числовий або Числовий формат (або натисніть клавіші Ctrl+1). Потім виберіть Загальні.

  3. Натисніть клавішу F2, щоб перейти в режим редагування клітинки, а потім натисніть клавішу Enter, щоб прийняти формулу.

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

Зазвичай у формулах за оператор множення виступає символ x, але в Excel для множення ми використовуємо зірочку (*). Якщо ви помножили у формулі константи, використовуючи символ x, програма Excel відобразить повідомлення про помилку й запропонує автоматично виправити неприйнятний символ на зірочку (*).

Вікно повідомлення з пропозицією замінити у формулі з множенням символ x на зірочку (*)

Однак, якщо ви використовуєте посилання на клітинки, Excel поверне #NAME? помилку #REF!.

Помилка #ІМ’Я? під час використання x з посиланнями на клітинки замість * для множення

Текст у формулах потрібно брати в лапки.

Наприклад, у формулі ="Сьогодні " & TEXT(TODAY();"dddd, dd mmmm") текстовий рядок "Сьогодні " поєднується з результатами функцій TEXT і TODAY, і в клітинці повертається введене слово та сьогоднішня дата, наприклад Сьогодні понеділок, 30 травня.

У формулі "Сьогодні " є пробіл перед кінцевою лапкою, щоб надати пустий пробіл між словами "Сьогодні є" та "Понеділок, 30 травня". Без лапок навколо тексту у формулі може відображатися помилка #NAME?.

В одну формулу можна вкласти (помістити) щонайбільше 64 рівні функцій.

Наприклад, формула =IF(SQRT(PI())<2;"Менше двох!";"Більше двох!") має 3 рівні функцій; Функція PI вкладено у функцію SQRT, яка, у свою чергу, вкладено у функцію IF.

Якщо посилання має вказувати на значення або клітинки з іншого аркуша, а ім’я цього аркуша містить небуквенний символ (наприклад, пробіл), це ім’я потрібно взяти в одинарні лапки (').

Наприклад, щоб повернути значення з клітинки D3 на аркуші "Квартальні дані" в книзі, введіть ="Квартальні дані"! D3. Без лапок навколо імені аркуша формула відображає помилку #NAME?.

Ви також можете вибрати значення або клітинки на іншому аркуші, щоб посилатися на них у формулі. Потім у програмі Excel імена аркушів буде автоматично взято в лапки.

Якщо посилання вказує на значення або клітинки з іншої книги, ім’я цієї книги потрібно взяти в квадратні дужки ([]), після яких указується ім’я аркуша, що містить значення або клітинки.

Наприклад, щоб посилатися на клітинки A1–A8 на аркуші "Збут" у книзі "Операції за 2 квартали", відкритій у програмі Excel, введіть =[Q2 Operations.xlsx]Продажі! A1:A8. Без квадратних дужок у формулі відображається помилка #REF!..

Якщо книгу не відкрито в програмі Excel, потрібно вказати повний шлях до файлу.

Приклад: =ROWS('C:\Мої документи\[Операції за 2-й квартал.xlsx]Збут'!A1:A8).

Примітка.: Якщо повний шлях містить символи пробілів, потрібно взяти шлях в одинарні лапки (на початку шляху й після імені аркуша перед знаком оклику).

Порада.: Найпростіший спосіб отримати шлях до іншої книги – відкрити іншу книгу, а потім ввести =, а потім перейти до іншої книги за допомогою клавіш Alt + клавіша табуляції . Виберіть будь-яку клітинку на аркуші, а потім закрийте вихідну книгу. Формула оновиться автоматично, після чого в ній з’явиться повний шлях до файлу та ім’я аркуша, записані в правильному форматі. Ви навіть можете копіювати та вставляти шлях і використовувати його будь-де.

Ділення клітинки на іншу клітинку з нульовим (0) або без значення призводить до помилки #DIV/0!..

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

=IF(B1;A1/B1;0)

Ця формула означає таке: ЯКЩО(B1 існує, тоді A1 ділиться на B1, інакше функція повертає 0).

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

Якщо формули заміняються їхніми результатами, перевірте наступні відомості про помилки та можливі рішення.

  • Якщо формула посилається на клітинки, які було видалено або замінено на інші дані, і якщо вона повертає помилку #REF!.виберіть клітинку з #REF! помилку #REF!. У рядку формул виберіть #REF! і видаліть його. Потім знову введіть діапазон для формули.

  • Якщо визначене ім'я відсутнє, а формула, яка посилається на це ім'я, повертає помилку #NAME?, визначте нове ім'я, яке посилається на потрібний діапазон, або змініть формулу для посилання безпосередньо на діапазон клітинок (наприклад, A2:D8).

  • Якщо відсутній аркуш, а формула, яка посилається на нього, повертає помилку #REF!, виправити це, на жаль, неможливо. Аркуш, який було видалено, не можна відновити.

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

    Наприклад, якщо формула має вигляд =[Книга1.xlsx]Аркуш1'!A1, а файл Книга1.xlsx більше не існує, значення з цієї книги все ще будуть доступні. Однак якщо відредагувати та зберегти формулу, яка посилається на цю книгу, у програмі Excel відкриється діалогове вікно Оновити значення з пропозицією ввести ім’я файлу. Натисніть кнопку Скасувати, а потім переконайтеся, що ці дані не втрачено, замінивши формули, які посилаються на відсутню книгу, на результати формул.

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

Наприклад, можна скопіювати отримане значення формули до клітинки на іншому аркуші. Або ви можете видалити значення, які використовувалися у формулі після копіювання отриманого значення до іншої клітинки на аркуші. Обидві ці дії спричиняють неприпустиму помилку посилання на клітинку (#REF!) для відображення у клітинці призначення, оскільки на клітинки, які містять значення, які ви використовували у формулі, більше не можна посилатися.

Цю помилку можна уникнути, вставивши отримані значення формул без формули в клітинки призначення.

  1. На аркуші виділіть клітинки з результатами обчислення, які потрібно скопіювати.

  2. На вкладці Основне в групі Буфер обміну натисніть кнопку Копіювати Зображення кнопки.

    Зображення стрічки Excel

    Сполучення клавіш: Ctrl+C.

  3. Клацніть верхню ліву клітинку в область вставлення.

    Порада.: Щоб перемістити або скопіювати виділений фрагмент до іншого аркуша або книги, виберіть іншу вкладку аркуша або перейдіть до іншої книги, а потім виділіть верхню ліву клітинку області вставлення.

  4. На вкладці Основне в групі Буфер обміну натисніть кнопку Вставити Зображення кнопки, а потім виберіть пункт Вставити значення або натисніть клавіші Alt > E > S > V > Enter для Windows або Option > Command > V > V > Enter на комп'ютері Mac.

Процес обчислення складної або вкладеної формули можна простежити.

  1. Виберіть формулу, яку потрібно проаналізувати.

  2. Виберіть формули > обчислити формулу.

    Група "Аудит формули" на вкладці "Формули"

  3. Натисніть кнопку Обчислити , щоб перевірити значення підкресленого посилання. Результат обчислення відображається курсивом.

    Діалогове вікно "Обчислення формули"

  4. Якщо підкреслена частина формули – це посилання на іншу формулу, виберіть крок із кроку, щоб відобразити іншу формулу в полі Обчислення . Натисніть кнопку Крок із виходом , щоб повернутися до попередньої клітинки та формули.

    Кнопка Крок у буде недоступна, коли посилання з'явиться у формулі вдруге або якщо формула посилається на клітинку в іншій книзі.

  5. Продовжуйте, доки не буде обчислено всі частини формули.

    Засіб обчислення формули не обов'язково розповість, чому формулу пошкоджено, але він допоможе вказати, де саме. Цей засіб дуже зручно використовувати у великих формулах, де без нього іноді важко локалізувати проблему.

    Примітки.: 

    • Деякі частини функцій IF і CHOOSE не обчислюватимуться, а в полі Обчислення може відображатися помилка #N/A.

    • Пусті посилання відображаються в полі Обчислення як нульові значення (0).

    • Деякі функції переобчислюються під час кожного змінення аркуша. Для цих функцій, зокрема RAND, AREAS, INDEX, OFFSET, CELL, INDIRECT, ROWS, COLUMNS, NOW, TODAY і RANDBETWEEN, у діалоговому вікні Обчислення формули можуть відображатися результати, відмінні від фактичних результатів у клітинці на аркуші.

Потрібна додаткова довідка?

Ви завжди можете поставити запитання експерту в спільноті Tech у розділі Excel чи отримати підтримку в спільнотах.

Порада.: Якщо ви власник малого бізнесу та шукаєте додаткові відомості про те, як налаштувати Microsoft 365, відвідайте сторінку Довідка та навчання для малого бізнесу.

Додаткові відомості

Огляд формул в Excel

Допомога та навчання з Excel

Потрібна додаткова довідка?

Потрібні додаткові параметри?

Ознайомтеся з перевагами передплати, перегляньте навчальні курси, дізнайтесь, як захистити свій пристрій тощо.

Спільноти допомагають ставити запитання й відповідати на них, надавати відгуки та дізнаватися думки висококваліфікованих експертів.

Чи ця інформація була корисною?

Наскільки ви задоволені якістю мови?
Що вплинуло на ваші враження?
Натиснувши кнопку "Надіслати", ви надасте свій відгук для покращення продуктів і служб Microsoft. Ваш ІТ-адміністратор зможе збирати ці дані. Декларація про конфіденційність.

Дякуємо за відгук!

×