Пошук помилок у формулах

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

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

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

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

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

Функції: це спеціальні формули Excel, які виконують певні обчислення. Наприклад, функція ПІ() повертає значення числа Пі: 3,142.
Посилання: це посилання на окремі осередки чи діапазони. Наприклад, A2 повертає значення комірки A2.
Константи. Числа або текстові значення, введені у формулу, наприклад 2.
Оператори: оператор * (зірочка) служить для множення чисел, а оператор (кришка) - для зведення числа в ступінь. За допомогою + і – можна складати та віднімати значення, а за допомогою / - ділити їх.

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

Наприклад, функція СУММ вимагає лише один аргумент, але в неї може бути до 255 аргументів (включно).

Приклад одного аргументу: =СУМ(A1:A10).

Приклад кількох аргументів: =СУМ(A1:A10;C1:C10).

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

Починайте кожну формулу зі знака рівності (=)

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

Слідкуйте за відповідністю дужок, що відкривають і закривають.

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

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

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

Деякі функції мають обов'язкові аргументи. Намагайтеся також не вводити надто багато аргументів.

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

У деяких функціях, наприклад СУМ, необхідно використовувати числові аргументи. В інших функціях, наприклад ЗАМЕНІТИ, потрібно, щоб хоч один аргумент мав текстове значення.Якщо в якості аргументу використовується неправильний тип даних, Excel може повертати непередбачені результати або помилку.

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

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

Імена інших листів мають бути поміщені в одинарні лапки

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

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

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

Вказуйте шлях до зовнішніх книг

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

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

У формулу можна також включити посилання на книгу, не відкриту в Excel. Для цього необхідно вказати повний шлях до відповідного файлу, наприклад: =ЧСТРОК('C:\My Documents\[Показники за 2-й квартал.xlsx]Продажи'!A1:A8). Ця формула повертає кількість рядків у діапазоні осередків з A1 до A8 в іншій книзі (8).

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

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

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

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

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

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

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

Для Excel у Windows перейдіть до розділу Параметри >файлів >формули або
для Excel на Mac виберіть меню Excel > Установки > перевірка помилок.
У розділі Пошук помилок встановіть прапорець Увімкнути фоновий пошук помилок. Виявлена помилка позначається трикутником у верхньому лівому кутку комірки.

Осередки, що містять формули, які призводять до помилки

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

Введення даних, що не є формулою, в комірку обчислюваного стовпця.
Введіть формулу в комірку обчислюваного стовпця, а потім натисніть клавіші CTRL+Z або виберіть Скасувати

Виберіть аркуш, на якому потрібно перевірити наявність помилок.
Якщо розрахунок аркуша виконано вручну, натисніть клавішу F9, щоб розрахувати повторно. Якщо діалогове вікно Перевірка помилок не відображається, виберіть Формули >аудит формул >перевірка помилок.
Якщо ви раніше ігнорували будь-які помилки, ви можете знову перевірити їх, виконавши такі дії: перейдіть до розділу Параметри >файлів >Формули. Для Excel на Mac виберіть меню Excel > Установки > перевірки помилок. У розділі Перевірка помилок виберіть Скидання пропущених помилок >ОК.

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

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

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

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

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

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

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

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

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

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

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

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

Якщо ви використовуєте таку функцію, як ВПР, чи є для пошуку значення відповідність в діапазоні пошуку? Скоріш за все, ні.

Використовуйте функцію Якщо помилка для придушення помилки #Н/Д. У цьому випадку можна ввести таке:

=ЯКЛИПОМИЛКА(ВПР(D2;$D$6:$E$8;2;ІСТИНА);0)

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

Примітка: Якщо ви використовуєте функцію, переконайтеся, що її ім'я написано неправильно. У разі слово СУМ введено з помилкою. Видаліть "e", і Excel виправить його.

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

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

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

Чи використовуєте ви функцію, яка виконує ітерацію, наприклад, IRR або RATE? Якщо так, то # NUM! помилка, ймовірно, через те, що функція не може знайти результат. Інструкції з усунення несправностей див. у розділі довідки.

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

Ви випадково видалили рядок чи стовпець? Дивіться, що сталося після видалення стовпця B у формулі = СУМ(A2; B2; C2).

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

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

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

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

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

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

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

сторінку Головна

Редагування

Знайти & Вибрати

клавіші CTRL+G

CONTROL+G

спеціальним

формулам

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

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

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

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

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

Спочатку виводиться вкладена формула. Функції СРЗНАЧ і СУМ вкладено у функцію ЯКЩО.

Діапазон осередків D2:D5 містить значення 55, 35, 45 та 25, тому функція СРЗНАЧ(D2:D5) повертає результат 40

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

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

Функція ЯКЩО повертає значення третього аргументу (аргумент значення_якщо_брехня). Функція СУММ не обчислюється, оскільки вона є другим аргументом функції ЯКЩО (аргумент значення_якщо_істина) і повертається тільки тоді, коли вираз має значення ІСТИНА.

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

Деякі частини формул, які використовують функції IF і CHOOSE , Не обчислюються. У цих випадках #N/A відображається у полі Оцінка .
Якщо посилання порожнє, у полі Обчислення відображається нульове значення (0).
Деякі функції обчислюються заново при кожній зміні аркуша, тому результати в діалоговому вікні Обчислення формули можуть відрізнятися від тих, що відображаються в комірці. Це функції СЛЧИС, ОБЛАСТИ, ІНДЕКС, ЗМІЩ, осередок, ДВССИЛ, ЧЕРТКА, ЧИСЛСТОЛБ, ТДАТА, СЬОГОДНІ, ВИПАДМІЖ.

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

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

Пошук та виправлення помилок у формулах у Excel. Безкоштовні приклади та статті.

Ігноруємо помилку #Н/Д під час підсумовування в MS EXCEL

Якщо в діапазоні підсумовування є значення помилки #Н/Д (значення недоступне), то функція СУММ() також поверне помилку. Використовуємо функцію СУМІСЛИ() для обробки таких ситуацій.

Покроковий контроль обчислення у MS EXCEL складних формул

При написанні складних формул, таких як =ЯКІ(СРЗНАЧ(A2:A10)>200;СУМ(B2:B10);0) часто необхідно отримати проміжний результат обчислення формули. І тому є спеціальний інструмент Обчислити формулу.

Функція ЕНД() у MS EXCEL

Функція ЕНД() , англійський варіант ISNA(), перевіряє на рівність значенню #Н/Д (значення недоступне) і повертає залежно від цього ІСТИНА чи БРЕХНЯ.

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

Буває, що формули повертають помилки (#СПРАВ/0!, #Н/Д, #ЗНАЧ! і т.д.) Підрахуємо кількість комірок, що містять помилки.

Функція НД() у MS EXCEL

Функція НД( ), англійська варіант NA(), повертає значення помилки #Н/Д. Значення помилки #Н/Д означає, що значення недоступне. Розглянемо випадки, коли ця функція може стати в нагоді.

Формула в MS EXCEL відображається як Текстовий рядок

Буває, що ввівши формулу і натиснувши клавішу ENTER, користувач бачить в комірці не результат обчислення формули, а саму формулу. Причина - Текстовий формат комірки. Покажемо, як у цьому випадку змусити …

Функція ЕОШ() у MS EXCEL

Функція ЕОШ() , англійський варіант ISERR(), перевіряє на рівність значенням: #ЗНАЧ!, #ПОСИЛКА!, #СПРАВА/0!, #КІЛЬКЛО!, #ІМ'Я? або #ПУСТО! і повертає залежно від цього ІСТИНА чи БРЕХНЯ.

Функція ПОМИЛКА() в MS EXCEL

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

Функція ЄЛИПОМИЛКА() в MS EXCEL

Функція ЕСЛИПОМИЛКА() , англійський варіант IFERROR(), перевіряє вираз на рівність значенням #Н/Д, #ЗНАЧ!, #ПОСИЛКА!, #СПРАВА/0!, #ЧИСЛО!, #ІМ'Я? або #ПУСТО! Якщо вираз, що перевіряється, або значення в комірці містить помилку, то …

Приховування в MS EXCEL помилки в осередку

Іноді потрібно приховати в осередку значення помилки: #ЗНАЧ!, #ПОСИЛКА!, #СПРАВА/0!, #КІЛЬКІСТЬ!, #ІМ'Я? Зробимо це кількома способами.

Помилки у формулах Excel

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

Помилки, пов'язані з неправильним введенням формули.

. Джерела помилкових результатів можуть бути різними. Помилки Excel можуть бути пов'язані як із введеними даними, так і з іншими умовами. Зокрема, помилки в розрахунку можуть призводити дії самого користувача. Перш за все, при введенні функцій необхідно дотримуватися типу параметрів, що вводяться.Не можна використовувати у вкладених формулах число рівнів більше 64. Якщо у формулі використовується посилання на інший аркуш, то після назви аркуша обов'язково має бути знак оклику, а якщо в назві є пробіл або інший не літерно-числовий символ, то ім'я має бути укладене в одинарні лапки (апострофи). Не можна форматувати дані для обчислень під час їх введення.

Всі ці варіанти призводять, якщо не до прямого значення помилки, то в будь-якому випадку до невірних результатів.

Приклад 1. Переплутано порядок параметрів для функції СУМІСЛІ. В результаті загальний розмір депонованої зарплати за обраним відділом став нульовим, що звичайно не відповідає даним.

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

Ще одним варіантом джерела помилкового результату буде введення числового значення більш ніж з 15 знаків, з урахуванням роздільників, що призводить до того, що початкові розряди будуть примусово обнулені. Це видно на наступному прикладі

Часті помилки пов'язані з результатом.

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

Найбільш поширені такі варіанти таких помилок.

#### - така помилка говорить про те, що введено число, що не вміщується в межах комірки.Виправити її, напевно, найпростіше - досить поміняти ширину стовпця. Але не завжди все так райдужно. Наприклад, така ж помилка вийде при отриманні під час розрахунку негативної дати чи негативного часу.

#СПРАВ/0! -Ця помилка виникає в результаті поділу на порожнє значення. Так як нуль теж є порожнім значенням - і не позитивним, і не негативним, то ця помилка виникає і при спробі поділу на нуль. Щоб уникнути такої помилки, треба перевірити комірки, на які посилається формула, на наявність даних. Врахуйте, що це може бути не настільки очевидним, як здається!

#Н/Д! – Помилка, пов'язана із недоступними даними. У прикладі нижче ця помилка з'явилася, оскільки зазначений код, який немає в таблиці з даними.

#ІМ'Я! - А ось цю помилку я називаю помилка роззяви. Її поява говорить про те, що введено неправильну назву. Зазвичай вона виникає при спробі введення у формулі адреси осередків російськими літерами – як варіант, А10 – літера тут російська. До цієї ж помилки призведе неправильне введення імені діапазону, таблиці, назви формули тощо. Простіше кажучи, вона найчастіше виникає при елементарному друкарському друку під час введення.

#ПУСТО! – така помилка виникне при спробі звернутися до неіснуючого перетину областей. Зазвичай такого результату призводить спроба відсебятини під час введення даних. Наприклад, коли намагаються ввести число, розділяючи розряди пробілами. Зверніть увагу саме ввести, а не отримати з роздільниками розрядів з комірки. Запам'ятайте прості правила вказівки осередків у діапазонах

А) двокрапка. Використовується для визначення меж діапазону. Наприклад, запис А1:А12 говорить про використання осередків в діапазоні від А1 до А12 включно.

Б) крапка з комою.Вказує на перерахування діапазонів або окремих осередків. Як приклад можна навести запис А1: А12; С1: С12

В) пропуск визначає перетин діапазонів. Результатом буде новий діапазон, що складається із загальних осередків вихідних діапазонів. Наприклад, запис = СУМ (B1: C5 A2: D3) ідентична запису = СУМ (B2: C3) так як при перетині діапазонів B1: C5 A2: D3 утворюється діапазон B2: C3. Це видно на зображенні нижче. Зверніть увагу, що результат обчислення функції СУМ в обох випадках однаковий.

#КІЛЬКІСТЬ! Найпоширеніший варіант помилки, пов'язаної з типом даних. Помилка говорить про неприпустиме введення числового значення. Зазвичай означає про введення числа в неприпустимому форматі, або про значення, що отримується в результаті, що виходить за межі числових даних, що використовуються Excel. На прикладі нижче відбувається спроба звести число 1000000000 у ступінь із показником 1000.

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

#ПОСИЛКА! Також досить поширений варіант. Виникає при спробі звернутися до неіснуючого діапазону чи осередку. У наступному прикладі виводиться помилка такого типу при вставці функції ВПР. Справа в тому, що в діапазоні $D$2:$H$715 шостого стовпця немає і близько. Така ж помилка може з'явитися безпосередньо у формулі, якщо діапазон, на який посилалася формула або функція, був видалений або недоступний.

#ЗНАЧ! – як і у випадку з помилкою #ЧИСЛО, ця помилка говорить про невірні дані, але не тільки про невірні числові дані, а про будь-яку помилку при введенні, коли дані не збігаються з параметрами, зазначеними в синтаксисі функції. Наприклад, як вихідні дані у формулі осередку використаний текст, а сама формула застосовує стандартні математичні дії для чисел. Ну не можна перемножувати букви. Часто саме такий варіант виникає при спробі використовувати для розрахунку дані, отримані з різних баз даних. Таке часто відбувається з тією ж 1С при некоректній роботі її розробників. У разі дані вивантажуються в excel за стандартом, бо оскільки роздільником дробової частини у яких зазвичай виступає точка, то Excel закономірно у російському варіанті вважає, що це текст.

Як визначити комірки з помилками та як їх виправити?

Ось тут змушений своїх читачів розчарувати. Якщо йдеться про неправильне введення користувачем у формулі, то автоматично ніяк. Тільки якщо перевіряти кожну формулу по черзі. Зокрема, на вкладці ФОРМУЛИ є кнопка «ВИЧИСЛИТИ ФОРМУЛУ». З її допомогою можна по кроках перевірити хід виконання формули та при виникненні помилки визначити місце її виникнення.

Однак цей варіант працює лише для складових формул. Якщо ж ви застосували простий варіант, наприклад, функцію СУМІСЛИ або ВПР, не застосовуючи вкладених формул, вона не помічник. Іншими словами, якщо ви помилилися в простій формулі на кшталт перелічених, то ви отримаєте повідомлення про помилку, і на цьому все. Далі розбирайтеся самі.

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

Насамперед за допомогою команди «Знайти та виділити» → «Виділити групу осередків», розташовану на вкладці «Головна». Цей варіант можна викликати, натиснувши поєднання F5 або Ctrl+G, а потім кнопку «виділити».

У вікні встановити перемикач у позицію «константи» або «формули», а позику відзначити галочкою варіант «Помилки» і натиснути «ОК». попередньо всі комірки мають бути виділені. Після застосування команди залишаться виділені лише осередки з помилками. Можна відразу їх відзначити певним кольором, і далі детально розбиратися з кожною з них.

Також можна використовувати умовне форматування чи перевірку даних, застосувавши у яких формули визначення осередків з помилками. На вкладці «Формули» можна використовувати кнопки «Впливають комірки» та «Залежні комірки».

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

У таких випадках можна спробувати поєднання клавіш Ctrl+[ або Ctrl+Shift+< для переходу до комірок, що впливають, на іншому аркуші.

Однак, всі ці інструменти зможуть лише визначити дані, які вже неправильні.

Щоб уникнути помилок при введенні даних, необхідно дотримуватись ряду простих правил.

При введенні інформації не застосовувати жодних форматувань. Формат зовнішнього вигляду застосовується до осередків, але не до самих даних.
При вставці функцій, як окремо, так і у складовій формулі, дотримуйтесь її синтаксису та призначення аргументів.Наприклад, якщо у функції ІНДЕКС першим параметром ви поставите номер рядка або стовпця, то програма просто вас не зрозуміє.
Уважно стежити за введеними даними. Саме через неуважність з'являються такі помилки як #ЗНАЧ, #ЧИСЛО, #ПОСИЛКА та інші.
Використовувати вкладеність формул лише тоді, коли це справді необхідно. Наприклад, якщо необхідно перевірити кількість знаків в осередку В1 і при необхідності довести їх до 12, додавши нулі, що бракують, на початку, можна написати так

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

Не змінювати розташування файлів з вихідними даними, аркушів та діапазонів з ними без потреби.
Якщо все ж таки формула видала помилку, не треба її видаляти і писати заново. Від того, що ви десятки разів напишіть формулу помилково, помилка не зникне. Уважно перегляньте всі діапазони та адреси, які у формулі застосовуються, перевірте відповідність даних параметрам функцій тощо. На практиці часто трапляюся, що люди першим параметром функції СУМІСЛІ вказували діапазон для підсумовування, при роботі з ВПР для приблизного пошуку четвертим аргументом вказували нуль, а для точного пошуку четвертий параметр взагалі виявлявся не заданий, траплялося, що при розрахунку кількості позицій намагалися ввести як вихідного значення відразу кілька варіантів та й так далі.

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

Як виправити формулу в Excel