Перейти до основного
Підтримка
Вхід
Способи уникнення недійсних формул

Способи уникнення недійсних формул

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

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

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

Виберіть пункт OK або натисніть клавішу ESC , щоб закрити повідомлення про помилку.

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

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

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

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

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 відображається дата, наприклад 2-Nov або 11/02/2009, замість ділення 11 на 2.

Щоб уникнути таких несподіваних результатів, завжди починайте функцію зі знака рівності. Наприклад, введіть: =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!.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Розділення однієї з них на іншу, яка має нульове значення (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 > введіть для Windows або Option > Command > v > v > введіть на комп'ютері Mac.

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

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

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

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

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

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

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

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

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

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

    Примітки.: 

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

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

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

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

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

Див. також

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

Виявлення помилок у формулах

Функції Excel (за алфавітом)

Функції Excel (за категоріями)

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

Удосконалення навичок роботи з Office
Ознайомтеся з навчальними матеріалами
Отримуйте нові функції раніше за інших
Приєднайтеся до оцінювачів Office

Ця інформація корисна?

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

×