Способи уникнення недійсних формул в 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]Продажі! Відповідь 1:А8. Без квадратних дужок формула відображає помилку #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. On the Home tab, in the Clipboard group, select .
   
   Сполучення клавіш: 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 або отримати підтримку в спільнотах.

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

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

Довідка Excel & навчання