Як прибрати помилки в осередках Excel

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

Помилки у формулі Excel відображаються в комірках

У цьому уроці буде описано значення помилок формул, які можуть містити комірки. Знаючи значення кожного коду (наприклад: #ЗНАЧ!, #СПРАВА/0!, #ЧИСЛО!, #Н/Д!, #ІМ'Я!, #ПУСТО!, #ПОСИЛКА!) можна легко розібратися, як знайти помилку у формулі та усунути її.

Як прибрати #ДІЛ/0 в Excel

Як видно при розподілі на комірку з порожнім значенням програма сприймає як розподіл на 0. У результаті видає значення: # СПРАВ/0! У цьому вся можна переконатися і з допомогою підказки.

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

Результат помилкового обчислення - #КІЛЬКІСТЬ!

Неправильне число: #ЧИСЛО! - Це помилка неможливості виконати обчислення у формулі.

Декілька практичних прикладів:

Помилка: #КІЛЬКІСТЬ! виникає, коли числове значення занадто велике або занадто маленьке. Так само дана помилка може виникнути при спробі отримати корінь із негативного числа. Наприклад, = КОРІНЬ (-25).

У осередку А1 – дуже велика кількість (10^1000). Excel не може працювати з такими великими числами.

У осередку А2 – та проблема з великими числами. Здавалося б, 1000 невелике число, але при поверненні його факторіалу виходить занадто велике числове значення, з яким Excel не впорається.

У осередку А3 – квадратний корінь може бути з негативного числа, а програма відобразила цей результат цієї ж помилкою.

Як прибрати НД в Excel

Значення недоступне: #Н/Д! означає, що значення є недоступним для формули:

Записана формула B1: =ПОШУКПОЗ("Максим"; A1:A4) шукає текстовий вміст "Максим" в діапазоні осередків A1:A4. Вміст знайдено у другому осередку A2. Отже, функція повертає результат 2. Друга формула шукає текстовий вміст «Андрій», діапазон A1:A4 не містить таких значень. Тому функція повертає помилку #Н/Д (немає даних).

Помилка #ІМ'Я! в Excel

Відноситися до категорії помилки у написанні функцій. Неприпустиме ім'я: #ІМ'Я! - означає, що Excel не розпізнав тексту написаного у формулі (назва функції = СУМ() йому невідомо, воно написано з помилкою). Це результат помилки синтаксису під час написання імені функції. Наприклад:

Помилка #ПУСТО! в Excel

Порожня множина: #ПОРОЖНЯ! - Це помилки оператора перетину множин. В Excel існує таке поняття як перетин множин. Воно застосовується для швидкого отримання даних великих таблиць за запитом точки перетину вертикального і горизонтального діапазону осередків. Якщо діапазони не перетинаються, програма відображає хибне значення – #ПУСТО! Оператором перетину множин є одиночний пробіл. Їм поділяються вертикальні та горизонтальні діапазони, задані в аргументах функції.

У цьому випадку перетином діапазонів є комірка C3 і функція відображає її значення.

Задані аргументи функції: =СУМ(B4:D4 B2:B3) – не утворюють перетин. Отже, функція дає значення з помилкою - #ПУСТО!

#ПОСИЛКА! – помилка посилань на комірки Excel

Неправильне посилання на комірку: #ПОСИЛКА! – означає, що аргументи формули посилаються на хибну адресу. Найчастіше це неіснуючий осередок.

У цьому прикладі помилка виникала при неправильному копіюванні формули. У нас є 3 діапазони осередків: A1: A3, B1: B4, C1: C2.

Під першим діапазоном в комірку A4 вводимо формулу, що підсумовує: =СУММ(A1:A3).А далі копіюємо цю формулу під другий діапазон, в комірку B5. Формула, як і раніше, підсумовує лише 3 осередки B2: B4, минаючи значення першої B1.

Коли та сама формула була скопійована під третій діапазон, в комірку C3 функція повернула помилку #ПОСИЛКА! Так як над осередком C3 може бути тільки 2 осередки, а не 3 (як того вимагала вихідна формула).

Примітка. У цьому випадку найзручніше під кожним діапазоном перед початком введення натиснути комбінацію гарячих клавіш ALT+=. Тоді вставити функцію підсумовування і автоматично визначить кількість підсумовуваних осередків.

Так само помилка #ПОСИЛКА! часто виникає при неправильному вказівці імені аркуша на адресу тривимірних посилань.

Як виправити ЗНАЧ в Excel

#ЗНАЧ! - Помилка в значенні. Якщо ми намагаємося скласти число і слово Excel в результаті ми отримаємо помилку #ЗНАЧ! Цікавий той факт, що якби ми спробували скласти два осередки, в яких значення першої число, а другий - текст за допомогою функції = СУМ (), то помилки не виникне, а текст прийме значення 0 при обчисленні. Наприклад:

Грати в комірці Excel

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

Так решітки (######) замість значення осередків можна побачити за негативної дати. Наприклад, ми намагаємося відібрати від старої дати нову дату. А в результаті обчислення встановлено формат осередків "Дата" (а не "Загальний").

Неправильний формат комірки також може відображати замість значень ряд символів решітки (######).

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

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

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

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

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

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

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

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

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

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

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

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

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

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