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

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

Примітка : У цій статті описано методи, які можуть допомогти виправити помилки у формулах. Це не вичерпний список методів усунення всіх можливих помилок. Відомості про конкретні помилки можна знайти у відповідях на форумі спільноти Microsoft Excel, де ви також можете поставити власне запитання.

Посилання на форум спільноти Excel

Введення простої формули

Формули – це рівняння, які використовуються для обчислення значень на аркуші. Формула починається знаком рівності (=). Наприклад, наведена нижче формула додає 3 до 1.

=3+1

Формула може також містити всі або деякі з таких елементів: функції, посилання, оператори та константи.

Елементи формули

Елементи формули

  1. Функції: входять до складу Excel і здійснюють певні обчислення за допомогою формул. Наприклад, функція PI() повертає значення числа пі: 3,142...

  2. Посилання: посилаються на окремі клітинки або діапазони клітинок. Посилання A2 повертає значення клітинки A2.

  3. Константи: числа або текстові значення, введені безпосередньо у формулу, наприклад 2.

  4. Оператори: оператор ^ (символ кришки) підносить число до степеня, а оператор * (зірочка) – множить числа. Використовуйте символи "+" і "–", щоб додавати й віднімати числа, і "/", щоб ділити їх.

    Примітка : Деякі функції потребують так звані аргументи – значення, за допомогою яких певні функції виконують обчислення. Аргументи записуються в дужках () функції. Функції PI аргументи непотрібні, тому вони не вводяться. Деякі функції потребують один або кілька аргументів і можуть підтримувати додаткові аргументи. Залежно від мовних параметрів системи, аргументи слід розділяти комою або крапкою з комою (;).

Наприклад, функції SUM потрібен лише один аргумент, але взагалі вона може вмістити до 255 аргументів.

Функція SUM

=SUM(A1:A10) – приклад функції з одним аргументом.

=SUM(A1:A10;C1:C10) – приклад функції з кількома аргументами.

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

Що слід перевірити

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

Кожна функція має починатися зі знака рівності (=)

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

Усі відкривні й закривні дужки мають відповідати одна одній

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

Для позначення діапазону використано двокрапку

Посилаючись на діапазон клітинок, ставте двокрапку (:), щоб розділити посилання на першу й останню клітинки в діапазоні. Наприклад, правильно писати =SUM(A1:A5), а не =SUM(A1 A5), інакше буде повернути помилку #NULL!.

Введено всі обов’язкові аргументи

Деякі функції мають обов’язкові аргументи. Крім того, переконайтеся, що не введено забагато аргументів.

Введено аргументи належного типу

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

Кількість рівнів вкладення функцій не може перевищувати 64

У функцію можна ввести або вкласти не більше 64 рівнів функцій.

Імена інших аркушів узято в одинарні лапки

Якщо формула містить посилання на значення або клітинки з інших аркушів чи книг, а ім’я іншої книги або аркуша містить пробіл чи інші неалфавітні символи, це ім’я необхідно взяти в одинарні лапки ( ' ), наприклад ='Квартальні дані'!D3, або ='123'!A1.

Якщо формула містить посилання на аркуш, після його імені має стояти знак оклику (!)

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

Вказано шлях до зовнішніх книг

Переконайтеся, що кожне зовнішнє посилання містить ім’я книги та шлях до неї.

Посилання на книгу містить ім’я книги та має братися у квадратні дужки ([Книга.xlsx]). Посилання також має містити ім’я аркуша у книзі.

Якщо книга, на яку потрібно послатися, не відкрита в Excel, посилання не неї все одно можна додати до формули. Укажіть повний шлях до файлу, наприклад ось так =ROWS('C:\Мої документи\[Операції за II квартал.xlsx]Продажі'!A1:A8). Ця формула повертає номер рядків у діапазоні, який містить клітинки A1–A8 в іншій книзі (8).

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

Числа введено без форматування

Не форматуйте числа у формулах. Наприклад, якщо потрібно ввести значення "1 000 ₴", у формулі введіть 1000. Якщо ввести кому чи пробіл як частину числа, програма Excel інтерпретує це як символ роздільника. Якщо потрібно, щоб числа відображалися з роздільниками тисяч або мільйонів чи символами грошових одиниць, форматуйте клітинки після введення чисел.

Наприклад, якщо потрібно додати 3100 до значення в клітинці A3, і ви вводите формулу =SUM(3;100;A3), програма Excel додає числа 3 й 100, а потім додає суму до значення з клітинки A3, замість додавання 3100 й A3, для чого слід записати формулу так: =SUM(3100;A3). Або якщо ви вводите формулу =ABS(-2;134), програма Excel відображає помилку, оскільки функція ABS приймає лише один аргумент: =ABS(-2134).

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

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

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

  1. Клацніть елементи Файл > Параметри > Формули.

    У програмі Excel 2007 натисніть кнопку Microsoft Office Зображення кнопки Office , а потім виберіть елементи Параметри Excel > Формули.

  2. У розділі Перевірка помилок установіть прапорець Увімкнути фонову перевірку помилок. Усі знайдені помилки позначатимуться трикутником у верхньому лівому куті клітинки.

    Клітинка з помилкою у формулі
  3. Щоб змінити колір трикутника, який позначає клітинку з помилкою, виберіть бажаний колір у меню Позначати помилки цим кольором.

  4. У розділі Правила перевірки помилок установіть або зніміть прапорці для таких правил:

    • Клітинки з формулами, які повертають помилку: у формулі не виявлено очікуваного синтаксису, аргументів або типів даних. Значенням помилки можуть бути #DIV/0!, #N/A, #NAME?, #NULL!, #NUM!, #REF! і #VALUE!. Кожна з цих помилок виникає через різні причини та може виправлятися різними способами.

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

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

      • У клітинку обчислюваного стовпця введено дані, відмінні від формули.

      • У клітинку обчислюваного стовпця введено формулу, а потім натиснуто клавіші Ctrl +Z або на панелі швидкого доступу натиснуто кнопку Скасувати Зображення кнопки .

      • Введіть нову формулу в обчислюваний стовпець, який вже містить один або кілька винятків.

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

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

    • Клітинки, у яких рік зображено лише 2 цифрами: клітинка містить текстове подання дати, у якій може бути неправильно інтерпретовано століття під час використання у формулі. Наприклад, у формулі =YEAR("01.01.31") рік може бути інтерпретовано як 1931 або 2031. Використовуйте це правило, щоб перевіряти сумнівні дати в текстовому поданні.

    • Числа в текстовому форматі або перед якими стоїть апостроф: клітинка містить числа, збережені як текст. Зазвичай це трапляється внаслідок імпорту даних з інших джерел. Числа, збережені як текст, можуть призвести до неочікуваних результатів сортування, тому краще перетворити їх на числовий формат. '=SUM(A1:A10) відобразиться як текст.

    • Формули, не узгоджені з іншими формулами в області: формула не відповідає шаблону інших суміжних із нею формул. У багатьох випадках формули, суміжні з іншими формулами, відрізняються лише посиланнями, що використовуються. У наведеному нижче прикладі з чотирма суміжними формулами програма Excel відобразить помилку поруч із формулою =SUM(A10:C10) у клітинці D4, оскільки суміжні формули змінюються на один рядок, а ця клітинка – на 8 рядків. Тому програма Excel очікує формулу =SUM(A4:C4).

      Програма Excel відображає помилку, коли формула не відповідає шаблону суміжних формул.

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

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

      Наприклад, після застосування цього правила поруч із формулою =SUM(D2:D4) відобразиться помилка, оскільки клітинки D5, D6 і D7 суміжні з клітинками, на які посилається формула, і з клітинкою, яка містить формулу (D8), і ці клітинки містять дані, на які має посилатися формула.

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

    • Формула посилається на пусті клітинки: формула містить посилання на пусту клітинку. Це може призвести до неочікуваних результатів, як у наведеному нижче прикладі.

      Припустімо, що потрібно обчислити середнє значення чисел у наведеному нижче стовпці. Якщо третя клітинка пуста, то її не включено до обчислення й результат становить 22,75. Якщо третя клітинка містить 0, результат становить 18,2.

      Програма Excel відображає помилку, коли формула посилається на пусті клітинки.
    • Неприпустимі дані в таблиці: таблиця містить помилку перевірки. Перегляньте настройку перевірки для клітинки. Для цього на вкладці Дані в групі Знаряддя даних натисніть кнопку Перевірка даних.

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

  2. Якщо аркуш обчислюється вручну, натисніть клавішу F9, щоб виконати обчислення повторно.

    Якщо не відображається діалогове вікно Контроль помилок, перейдіть на вкладку Формули та виберіть елементи Аудит формули > Контроль помилок.

  3. Щоб знову перевірити пропущені раніше помилки, клацніть елементи Файл > Параметри > Формули.

    У розділі Перевірка помилок натисніть кнопки Скинути пропущені помилки > OK.

    Перевірка помилок

    Примітка : Скидання пропущених помилок впливає на всі помилки на всіх аркушах активної книги.

    Порада : Для зручності розташуйте діалогове вікно Контроль помилок під рядком формул.

    Перемістіть вікно "Перевірка помилок" під рядок формул.
  4. Натисніть одну із кнопок у правій частині діалогового вікна. Доступні дії різняться залежно від типу помилки.

  5. Натисніть кнопку Далі.

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

  1. Натисніть кнопку Контроль помилок Піктограма "Перевірка помилок" , яка з’явиться поруч із клітинкою, і виберіть потрібну команду. Доступні команди залежать від типу помилки, а перший рядок у контекстному меню описує помилку.

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

    Перемістіть вікно "Перевірка помилок" під рядок формул.

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

У таблиці нижче наведено посилання на статті, у яких докладно описано ці помилки, і короткий опис для початку роботи.

Стаття

Опис

Виправлення помилки ####

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

Наприклад, формула, яка віднімає дату в майбутньому від дати в минулому, як-от =15.06.2008-01.07.2008, у результаті отримає від’ємне значення дати.

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

# помилка

Виправлення помилки #DIV/0!

Програма Excel відображає цю помилку, коли число ділиться на нуль (0) або на клітинку, яка не містить значення.

Порада : Додайте обробник помилки, як у наведеному нижче прикладі, тобто =IF(C2;B2/C2;0)

Щоб виявляти помилки, можна використовувати функції обробки помилок, як-от IF.

Виправлення помилки #N/A

Програма Excel відображає цю помилку, коли функція або формула не має доступу до значення.

Якщо ви використовуєте таку функцію, як VLOOKUP, перевірте, чи шукане значення відповідає чомусь у діапазоні пошуку. Найчастіше це не так.

Спробуйте скористатися функцією IFERROR, щоб не отримувати помилку #N/A. Наприклад, ось так:

=IFERROR(VLOOKUP(D2;$D$6:$E$8;2;TRUE);0)

Помилка #N/A

Виправлення помилки #NAME?

Ця помилка відображається, коли програма Excel не розпізнає текст у формулі. Наприклад, можливо, неправильно вказано ім’я діапазону або ім’я функції.

Примітка : Перевірте, чи правильно написано ім’я формули. У цьому прикладі формулу SUM написано неправильно. Видаліть букву "e", щоб програма Excel відобразила потрібне значення.

Програма Excel відображає помилку #NAME?, коли ім’я функції введено неправильно.

Виправлення помилки #NULL!

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

Примітка : Переконайтеся, що діапазони правильно розділено. Області C2:C3 та E4:E6 не перетинаються, тому формула =SUM(C2:C3 E4:E6) поверне помилку #NULL!. Поставивши крапку з комою між діапазонами C та E, ви виправите її =SUM(C2:C3;E4:E6)

Помилка #NULL!

Виправлення помилки #NUM!

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

Можливо, ви використовуєте функцію з ітераціями, як-от IRR або RATE. У такому разі помилка #NUM! може виникнути, якщо функція не може знайти результату. Див. інструкції з вирішення цієї проблеми у відповідному розділі довідки.

Виправлення помилки #REF!

Програма Excel відображає цю помилку, коли посилання на клітинку недійсне. Наприклад, можливо, видалено клітинки, на які посилалися інші формули, або вставлено клітинки поверх тих, на які посилалися інші формули.

Можливо, ви випадково видалили рядок або стовпець. У нашому прикладі видалено стовпець B у формулі =SUM(A2;B2;C2) і ось результат.

Натисніть кнопку Скасувати (або клавіші Ctrl+Z), щоб скасувати видалення, перебудуйте формулу або використайте посилання на неперервний діапазон, як-от =SUM(A2:C2), що автоматично оновиться, якщо видалити стовпець B.

Програма Excel відображає помилку #REF!, коли посилання на клітинку не дійсне.

Виправлення помилки #VALUE!

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

Можливо, ви використали математичні оператори (+, –, *, /, ^) з даними різних типів. Якщо так, натомість використайте функцію. У нашому прикладі формула =SUM(F2:F5) допоможе вирішити проблему.

Помилка #VALUE!

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

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

Цю панель інструментів можна переміщати або пристиковувати як будь-яку іншу панель. Наприклад, ви можете закріпити її в нижній частині вікна. Ця панель інструментів дає змогу відслідковувати такі властивості клітинки: 1) книга, 2) аркуш, 3) ім’я (якщо клітинка входить до іменованого діапазону), 4) адреса клітинки, 5) значення та 6) формула.

Примітка : Для кожної клітинки можна мати лише одне контрольне значення.

Додавання клітинок до вікна контрольного значення

  1. Виберіть клітинки, які потрібно відстежувати.

    Щоб вибрати на аркуші всі клітинки з формулами, на вкладці Основне в групі Редагування натисніть кнопку Знайти й виділити (або натисніть клавіші Ctrl+G на ПК чи Control+G на комп’ютері Mac), виберіть команду Перейти до особливого, а потім виберіть параметр формули.

    Діалогове вікно "Виділення групи клітинок"
  2. На вкладці Формули в групі Аудит формули натисніть кнопку Вікно контрольного значення.

  3. Натисніть кнопку Додати контрольне значення

    Натисніть кнопку "Додати контрольне значення", щоб додати контрольне значення в електронній таблиці.
  4. Переконайтеся, що виділено всі потрібні вам клітинки, і натисніть кнопку Додати.

    У діалоговому вікні "Додавання контрольного значення" введіть діапазон клітинок, які потрібно переглядати
  5. Щоб змінити ширину стовпця у вікні контрольного значення, перетягніть праву межу заголовка стовпця.

  6. Щоб відобразити клітинку, на яку посилається запис у вікні контрольного значення, двічі клацніть цей запис.

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

Видалення клітинок із вікна контрольного значення

  1. Якщо вікно контрольного значення не відображається, на вкладці Формули в групі Аудит формули натисніть кнопку Вікно контрольного значення.

  2. Виберіть клітинки, які потрібно видалити.

    Щоб вибрати кілька клітинок, клацайте їх, утримуючи натиснутою клавішу Ctrl.

  3. Натисніть кнопку Видалити контрольне значення

    Видалити контрольне значення

Іноді важко зрозуміти, як вкладена формула обчислює остаточний результат через кілька проміжних обчислень і логічних перевірок. Однак за допомогою діалогового вікна Обчислення формули ви можете побачити обчислення різних частин вкладеної формули в порядку обчислення всієї формули. Наприклад, формулу =IF(AVERAGE(D2:D5)>50;SUM(E2:E5);0) легше зрозуміти, якщо побачити ось такі проміжні результати:

Функція "Обчислити формулу" дає змогу переглядати обчислення різних частин вкладеної формули

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

Опис

=IF(AVERAGE(D2:D5)>50;SUM(E2:E5);0)

Спочатку відображається вкладена формула. Функції AVERAGE і SUM вкладено у функцію IF.

Діапазон клітинок D2:D5 містить значення 55, 35, 45 і 25, а тому результат функції AVERAGE(D2:D5) дорівнює 40.

=IF(40>50;SUM(E2:E5);0)

Діапазон клітинок D2:D5 містить значення 55, 35, 45 і 25, а тому результат функції AVERAGE(D2:D5) дорівнює 40.

=IF(FALSE;SUM(E2:E5);0)

Оскільки 40 не більше за 50, вираз у першому аргументі функції IF (аргумент "лог_вираз") має значення False.

Функція IF повертає значення третього аргументу (аргумент "значення_якщо_хибність"). Функцію SUM обчислено не буде, оскільки вона – другий аргумент функції IF (аргумент "значення_якщо_істина"), і її буде повернуто лише тоді, коли вираз матиме значення True.

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

  2. Перейдіть на вкладку Формули, а потім у групі Аудит формули натисніть кнопку Обчислити формулу.

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

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

    Кнопка Крок із заходом буде недоступна для посилання, коли посилання вдруге з’явиться у формулі, або якщо формула містить посилання на клітинку з окремої книги.

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

  5. Щоб знову переглянути обчислення, натисніть кнопку Спочатку.

  6. Щоб завершити обчислення, натисніть кнопку Закрити.

Примітки : 

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

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

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

Відповіді на запитання

Опублікуйте запитання на форумі спільноти Microsoft Excel

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

Маєте пропозиції щодо того, як покращити наступну версію програми Excel? Тоді перегляньте розділи на веб-сайті Excel User Voice.

Див. також

Відображення зв’язків між формулами та клітинками

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

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

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

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

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

Дякуємо за відгук! Схоже, вам може стати в нагоді допомога одного з наших спеціалістів служби підтримки Office, з яким ми вас можемо з’єднати.

×