Як прибрати помилки в осередках 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

Функція ЕНД в Excel використовується для перевірки осередків або виразів, що передаються як аргумент, і повертає логічне значення ІСТИНА. Наприклад, якщо осередок містить код помилки #Н/Д або результатом обчислення виразу, переданого як аргумент, є код помилки #Н/Д. В іншому випадку результатом виконання цієї функції є логічне брехня.

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

Приклади використання функції ЕНД Excel. Ця функція належить до категорії «Перевірка властивостей та значень» – функції Excel (нелогічні функції для перевірки умов). Вона зручна під час проведення складних розрахунків із розгалуженням логіки. Наприклад, за відсутності помилки буде виконано дію_1, інакше – дію_2.

Приклад 1. У таблиці містяться дані про товари та їх кількість. Дані було отримано з СУБД, якщо кількість одиниць товарів дорівнює нулю, у таблиці Excel ця інформація відобразилася як коду помилки #Н/Д. Визначити кількість найменувань товарів, яких немає.

Вид таблиці даних:

Для розрахунку використовуємо наступний запис (формула масиву CTRL+SHIFT+Enter):

Функція ЕНД приймає відразу діапазон осередків B3:B13 як аргумент, оскільки використовується формула масиву. Подвійне заперечення «--» необхідне для явного перетворення логічних значень до числових даних (ІСТИНА – 1, БРЕХНЯ – 0).Функція СУМ підсумовує елементи отриманого масиву з нулів та одиниць. В результаті отримуємо:

Через війну ми отримали число дорівнює кількості помилок #Н/Д у стовпці B.

Як отримати перше значення осередку замість помилки Н/Д в Excel

Приклад 2. У таблиці міститься діапазон комірок із випадковими числами, відсортованими у порядку зростання. Знайти найближче число з даного діапазону заданому за допомогою функції ПЕРЕГЛЯД. Відомо, якщо число, яке шукається менше першого значення в діапазоні, буде виведений код помилки #Н/Д. Обробити цю ситуацію те щоб замість коду помилки виводився перший елемент масиву.

Вид таблиці даних:

Для пошуку числа 1 використовуємо наступний запис:

Функція ЕНД аналізує результат виконання функції ПЕРЕГЛЯД. Якщо в якості першого аргументу ПЕРЕГЛЯД передано числове значення, яке менше значення першого елемента діапазону, що проглядається, буде згенерований код помилки #Н/Д і буде виконано вираз, передане в якості аргументу значення_якщо_істина функції ЯКЩО. В іншому випадку (необхідне число знаходиться в діапазоні масиву або перевищує значення його останнього елемента), виконається вираз, переданий як аргумент значення_якщо_брехня .

Як видно, помилка #Н/Д не виводиться, а замість неї перше значення комірки стовпця, що переглядається.

Опис синтаксису та параметрів функції ЕНД в Excel

Функція ЕНД має наступний синтаксичний запис:

Єдиним і обов'язковим для заповнення аргументом цієї функції є значення. Він приймає посилання на осередки, текстові, числові, логічні дані, і навіть імена.

1. Перетворення типів даних для значень, переданих як аргумент функції ЕНД, не виконується.Наприклад, число «99» вказане в лапках, буде розглядатися як текстові дані. Якщо в якості аргументу було передано рядок «#Н/Д», ЕНД поверне значення брехня. на цю комірку як аргумент, поверне - ІСТИНА.
2. Ця функція зазвичай використовується в комбінації з ЯКЩО та іншими функціями для перевірки виразу для своєчасної перевірки результатів обчислень та перехоплення можливої ​​помилки.
3. Код помилки #Н/Д генерують функції у випадках, коли у формулах використовуються неприпустимі значення.

• при використанні функцій для пошуку даних (ПОІСКОП, ВВР та інших), якщо в якості аргументу «шукане значення» було введено неіснуюче;
• у разі використання формул масивів, якщо довжина масиву результатів перевищує довжину вихідних масивів;
• якщо за використанні функції були зазначені чи кілька аргументів, обов'язкових заповнення.

• Створити таблицю
• Форматування
• Функції Excel
• Формули та діапазони
• Фільтр та сортування
• Діаграми та графіки
• Зведені таблиці
• Друк документів
• Бази даних та XML
• Можливості Excel
• Налаштування параметри
• Уроки Excel
• Макроси VBA
• Завантажити приклади

Виправлення помилки #Н/Д у функціях ІНДЕКС та ПОШУКПОЗ

Примітка: Якщо ви хочете, щоб функція INDEX або MATCH повертала осмислене значення замість #N/A, використовуйте функцію IFERROR , а потім вставте функції INDEX і MATCH у цю функцію. Заміна #N/A власним значенням лише ідентифікує помилку, але не усуває її. IFERRORпереконайтеся, що формула працює правильно, як ви плануєте.

Проблема: Немає відповідностей

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

Якщо ви вважаєте, що дані є в електронній таблиці, але match не може знайти їх, це може бути викликано такими причинами:

• Осередок містить непередбачені символи або приховані пробіли.
• До комірки застосовано неправильний формат даних. Наприклад, комірка містить числове значення, але відформатована як текстова.
  

РІШЕННЯ. Щоб видалити непередбачені символи або приховані пробіли, використовуйте CLEAN або TRIM відповідно. Крім того, перевірте, чи мають комірки правильні типи даних.

Ви використовували формулу масиву, але не натиснули клавіші CTRL+SHIFT+ВВЕДЕННЯ

При використанні масиву в INDEX, MATCH або поєднання цих двох функцій необхідно натиснути клавіші CTRL+SHIFT+ВВЕДЕННЯ на клавіатурі. Excel автоматично укладе формулу у фігурні дужки <>. Якщо ви спробуєте самостійно ввести квадратні дужки, excel відобразить формулу як тексту.

Примітка: Якщо у вас є поточна версія Microsoft 365, можна просто ввести формулу в комірку виводу, а потім натиснути клавішу ВВЕДЕННЯ щоб підтвердити формулу як формулу динамічного масиву. В іншому випадку формула повинна бути введена як застаріла формула масиву. Спочатку виберіть діапазон вихідних даних, ввівши формулу в комірку виводу, а потім натисніть клавіші CTRL+SHIFT+ВВЕДЕННЯ , щоб підтвердити її. Excel автоматично вставляє фігурні дужки на початку та наприкінці формули. Додаткові відомості про формули масиву див. у статті Використання формул масиву: рекомендації та приклади.

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

При використанні MATCH має бути узгодженість між значенням у аргументі match_type та порядком сортування значень у масиві підстановки. Якщо синтаксис відхиляється від наведених нижче правил, виникає помилка #Н/Д.

• Якщо match_type одно 1 або не задано, значення в lookup_array мають бути у порядку зростання. Приклади: -2, -1, 0, 1, 2…; А, Б, В ...; БРЕХНЯ, ІСТИНА тощо. буд.
• Якщо match_type дорівнює -1, значення в lookup_array повинні бути в порядку зменшення.

У наступному прикладі функція MATCH має значення

=ПОШУКПОЗ(40;B2:B10;-1)

Аргумент match_type в синтаксисі має значення -1, що означає, що порядок значень B2:B10 повинен бути в порядку зменшення, щоб формула працювала. Але значення перебувають у порядку зростання, що зумовлює помилку #N/Д.

РІШЕННЯ: Змініть аргумент match_type на 1 або відсортуйте таблицю у спадному форматі. Потім спробуйте ще раз.

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

Ви завжди можете поставити запитання експерту в Excel Tech Community або отримати підтримку у спільнотах.