Які існують помилки в Excel та як їх виправляти

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

1. #СПРАВА/О! - Ця помилка практично завжди означає, що формула в осередку намагається розділити якесь значення на нуль. Найчастіше це відбувається через те, що в іншому осередку, що посилається на дану, знаходиться нульове значення або значення відсутнє. Вам необхідно перевірити всі пов'язані осередки щодо наявності таких значень. Також ця помилка може виникати, коли ви вводите неправильні значення в деякі функції, наприклад, в ОСТАТ() , коли другий аргумент дорівнює 0. Також помилка поділу на нуль може виникати, якщо ви залишаєте порожні осередки для введення даних, а якась формула вимагає деякі дані. При цьому буде виведено помилку #СПРАВ/0!що може збентежити кінцевого користувача. Для цих випадків ви можете використовувати функцію ЯКЩО() для перевірки, наприклад =ЯКЩО(А1=0;0;В1/А1) . У цьому прикладі функція поверне 0 замість помилки, якщо в комірці А1 є нульове або порожнє значення.
2. #Н/Д - Ця помилка розшифровується як недоступно, і це означає, що значення недоступне функції або формулі. Ви можете побачити таку помилку, якщо введете невідповідне значення у функцію. Для виправлення перевірте насамперед вхідні осередки щодо помилок, особливо якщо в них теж з'являється дана помилка.
3. #ІМ'Я? - Ця помилка виникає, коли ви неправильно вказуєте ім'я у формулі або помилково задаєте ім'я самої формули.Для виправлення перевірте ще раз усі імена та назви у формулі.
4. #ПУСТО! - Ця помилка пов'язана з діапазонами у формулі. Найчастіше вона виникає, коли у формулі вказується два діапазони, що не перетинаються, наприклад =СУММ(С4:С6;А1:С1) .
5. #КІЛЬКІСТЬ! — помилка виникає, коли у формулі є некоректні числові значення, що виходять за межі допустимого діапазону.
6. #ПОСИЛКА! - Помилка виникає, коли були видалені осередки, на які посилається дана формула.
7. #ЗНАЧ! — у разі йдеться про використання неправильного типу аргументу для функції.

Якщо при введенні формули ви неправильно розставили дужки, Excel виведе на екран попереджувальне повідомлення - див. рис. 1. У цьому повідомленні ви побачите припущення Excel про те, як їх потрібно розставити. Якщо ви підтверджуєте таку розстановку, натисніть Так. Але часто потрібне власне втручання. Для цього натисніть Ні та виправте дужки самостійно.

Обробка помилок за допомогою функції ПОМИЛКА()

Перехопити будь-які помилки та обробити їх можна за допомогою функції ПОМИЛКА() . Ця функція повертає істину чи брехню залежно від цього, чи з'являється помилка при обчисленні її аргументу. Загальна формула для перехоплення виглядає так: = ЯКЩО (ПОМИЛКА (вираз); помилка; вираз).

Мал. 1. Попереджувальне повідомлення про неправильно розставлені дужки

Функція якщо поверне помилку (наприклад, повідомлення), якщо під час розрахунку з'являється помилка. Наприклад, розглянемо наступну формулу: =ЯКЩО(ЕПОМИЛКА(А1/А2);""; А1/А2) . У разі помилки (розподіл на 0) формула повертає порожній рядок. Якщо ж помилки немає, повертається саме вираз А1/А2 .

Існує інша, більш зручна функція ЕСЛИПОМИЛКА() , яка поєднує дві попередні функції ЕСЛИ() і ПОМИЛКА() : ЕСЛИПОМИЛКА(значення; значення при помилці) , де: значення - Вираз для розрахунку, значення при помилці — результат, що повертається у разі помилки. Для нашого прикладу це буде виглядати так: =ЯКЛИПОМИЛКА(А1/А2;"") .

Приховування значень та індикаторів помилок

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

Формули можуть повертати помилки з багатьох причин. Наприклад, Excel не може поділити на 0, а якщо ввести формулу =1/0Excel поверне #DIV/0. Значення помилок: #DIV/0!, #N/A, #NAME?, #NULL!, #NUM!, #REF!і #VALUE!. Осередки з індикаторами помилок, які відображаються у вигляді трикутників у лівому верхньому кутку комірки, містять помилки формул.

Приховування індикаторів помилок у комірках

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

Осередок з індикатором помилки

1. У меню Excel виберіть пункт Параметри.
2. У розділі Формули та Списки клацніть Перевірка помилок

, а потім зніміть прапорець Увімкнути перевірку перевірки помилок у фоновому режимі режимі.

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

Додаткові параметри

1. Виділіть комірку зі значенням помилки.
2. Додайте формулу в комірці (стара_формула) до наступної формули: =ЯКЩО(ЕПОМИЛКА( стара_формула),"", стара_формула)
3. Виконайте одну з наведених нижче дій.

1. Натисніть на зведену таблицю.
2. На вкладці Аналіз зведеної таблиці натисніть кнопку Параметри.

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

1. Натисніть на зведену таблицю.
2. На вкладці Аналіз зведеної таблиці натисніть кнопку Параметри.