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

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

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

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

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

1. В Excel для Windows перейдіть до розділу Параметри> файлів > формул або
   в Excel для Mac відкрийте меню Excel > Параметри > перевірка помилок. 

   У Excel 2007 натисніть кнопку Microsoft 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 поруч із формулою відображається помилка.

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

     

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

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

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

     

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

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

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

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

3. Якщо помилки пропущено раніше, їх можна знову перевірити, виконавши такі дії: перейдіть до розділу Параметри файлу > > Формули. В Excel для Mac відкрийте меню Excel > Параметри > перевірка помилок.

   У розділі Перевірка помилок виберіть Скинути пропущені помилки > 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)
Виправлення помилки #N/A	Програма Excel відображає цю помилку, коли функція або формула не має доступу до значення. Якщо ви використовуєте таку функцію, як VLOOKUP, перевірте, чи шукане значення відповідає чомусь у діапазоні пошуку. Найчастіше це не так. Спробуйте скористатися функцією IFERROR, щоб не отримувати помилку #N/A. Наприклад, ось так: =IFERROR(VLOOKUP(D2;$D$6:$E$8;2;TRUE);0)
Виправлення помилки #NAME? помилки	Ця помилка відображається, коли програма Excel не розпізнає текст у формулі. Наприклад, ім'я діапазону або ім'я функції може бути написано неправильно. Примітка.: Перевірте, чи правильно написано ім’я формули. У цьому прикладі формулу SUM написано неправильно. Видаліть "e", і Excel виправить його.
Виправлення #NULL! помилки	Програма Excel відображає цю помилку, коли вказано перетин двох областей, які не перетинаються (не перехрещуються). Оператор перетину – це символ пробілу, що відокремлює посилання у формулі. Примітка.: Переконайтеся, що діапазони правильно розділено. Області C2:C3 та E4:E6 не перетинаються, тому формула =SUM(C2:C3 E4:E6) поверне помилку #NULL!. Якщо помістити кому між діапазонами C та E, вона виправить формулу =SUM(C2:C3;E4:E6)
Виправлення помилки #NUM! помилки	Програма Excel відображає цю помилку, коли формула або функція містить неприпустимі числові значення. Ви використовуєте функцію, яка ітеративна, наприклад IRR або RATE? Якщо так, то #NUM! можливо, через те, що функції не вдалося знайти результат. Щоб дізнатися, як вирішити цю проблему, див. розділ довідки.
Виправлення помилки #REF! помилки	Програма Excel відображає цю помилку, коли посилання на клітинку недійсне. Наприклад, ви видалили клітинки, на які посилалися інші формули, або вставлено клітинки, переміщені поверх клітинок, на які посилалися інші формули. Можливо, ви випадково видалили рядок або стовпець. У нашому прикладі видалено стовпець B у формулі =SUM(A2;B2;C2) і ось результат. Натисніть кнопку Скасувати (або клавіші Ctrl+Z), щоб скасувати видалення, перебудуйте формулу або використайте посилання на неперервний діапазон, як-от =SUM(A2:C2), що автоматично оновиться, якщо видалити стовпець B.
Виправлення помилки #VALUE! помилки	Програма Excel може відображати таку помилку, якщо формула містить клітинки з різними типами даних. Можливо, ви використали математичні оператори (+, –, *, /, ^) з даними різних типів. Якщо так, натомість використайте функцію. У нашому прикладі формула =SUM(F2:F5) допоможе вирішити проблему.

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

Цю панель інструментів можна переміщати або пристиковувати як будь-яку іншу панель. Наприклад, ви можете закріпити її в нижній частині вікна. Ця панель інструментів дає змогу відслідковувати такі властивості клітинки: 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) легше зрозуміти, коли відображаються такі проміжні результати:

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

2. Перейдіть до розділу Формули > Аудит формули > обчислити формулу.

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

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

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

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

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

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