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

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

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

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

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

Чи використовуєте ви правильні роздільники списку у формулі?

Формули з кількома аргументами використовують роздільники списків, щоб відділити ці аргументи. Роздільник може відрізнятися залежно від локалізації ОС і параметрів Excel. Найпоширенішими роздільниками списку є кома "," і крапка з комою ";".

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

Докладні відомості див. в статті Помилки формул, якщо роздільник списку настроєно неправильно

Помилка фунта (#) у формулах Excel?

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

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

• Виправлення помилки #NUM! помилки
• Виправлення помилки #VALUE! помилки
• Виправлення помилки #N/A
• Виправлення помилки #DIV/0! помилки
• Виправлення помилки #REF! помилки
• Виправлення помилки #NAME?
• Виправлення #NULL! помилки

Виправлення недійсних посилань у формулах Excel

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

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

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

У формулі замість значення в Excel відображається синтаксис

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

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

  Порада.

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

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

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

  

Увімкнути автоматичне обчислення книги, якщо формула не обчислення у програмі Excel

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

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

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

   

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

Одне або кілька циклічних посилань у формулі

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

Щоб дізнатися більше про циклічні посилання, перегляньте статтю Видалення або дозвіл циклічного посилання.

Знак рівності (=) на початку формули

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

Якщо ввести щось на кшталт SUM(A1:A10), в Excel замість результату формули відображатиметься текстовий рядок SUM(A1:A10). Якщо ввести 11/2, замість результату ділення 11 на 2 в Excel відображатиметься дата, наприклад "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 (за категоріями).

Обробка неформатованих чисел у формулах 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 відобразить повідомлення про помилку й запропонує автоматично виправити неприйнятний символ на зірочку (*).

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

Текст у формулах без лапок

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

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

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

Більше 64 функцій у формулі

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

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

Назви аркушів без одинарних лапок

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

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

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

Виправлення шляхів до зовнішньої книги у формулах Excel

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

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

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

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

Ділення числових значень на нуль

Якщо розділити клітинку на іншу клітинку, яка містить нуль (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. На вкладці Основне в групі Буфер обміну натисніть кнопку Копіювати .
   
   Сполучення клавіш: Ctrl+C.

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

   Порада.

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

4. На вкладці Основне в групі Буфер обміну натисніть кнопку Вставити , а потім виберіть пункт Вставити значення або натисніть клавіші Alt > E > S > V > Enter для Windows або Option > Command > 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 або отримати підтримку в спільнотах.

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

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

Excel допомагає & навчання