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

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

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

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

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

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

1. Функції: входять до складу Excel і здійснюють певні обчислення за допомогою формул. Наприклад, функція PI() повертає значення числа пі: 3,142.
2. Посилання: посилаються на окремі клітинки або діапазони клітинок. Посилання A2 повертає значення клітинки A2.
3. Константи: числа або текстові значення, введені безпосередньо у формулу, наприклад 2.
4. Оператори: оператор ^ (символ кришки) підносить число до степеня, а оператор * (зірочка) – множить числа. Використовуйте символи “+” і “–”, щоб додавати й віднімати числа, і “/”, щоб ділити їх.

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

Наприклад, функції SUM потрібен лише один аргумент, але взагалі вона може вмістити до 255 аргументів.

=SUM(A1:A10) – приклад функції з одним аргументом.

=SUM(A1:A10;C1:C10) – приклад функції з кількома аргументами.

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

Кожна функція має починатися зі знака рівності (=)

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

Усі відкривні й закривні дужки мають відповідати одна одній

Переконайтеся, що кожна дужка (відкривна та закривна) має відповідну пару. Якщо у формулі використовується функція, важливо, щоб кожна дужка була в правильному положенні, щоб функція працювала належним чином. Наприклад, формула =IF(B5 <0);"Не припустимо";B5*1,05)не працюватиме, оскільки містить дві закривні дужки й лише одну відкривну, хоча має бути лише одна дужка кожного типу. Правильний вигляд формули такий: =IF(B5.

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

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

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

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

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

Для деяких функцій, як-от SUM, необхідні числові аргументи. В інших функціях, як-от REPLACE, принаймні для одного з аргументів потрібне текстове значення. Якщо як аргумент використовується неправильний тип даних, Excel можете повернути неочікувані результати або відобразити помилку.

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

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

Імена інших аркушів узято в одинарні лапки

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

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

Наприклад, щоб повернути значення із клітинки D3 в аркуші ”Квартальні дані” в тій самій книзі, скористайтеся такою формулою: =’Квартальні дані’!D3.

Вказано шлях до зовнішніх книг

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

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

Якщо книга, на яку потрібно послатися, не відкрита в Excel, посилання не неї все одно можна додати до формули. Укажіть повний шлях до файлу, наприклад ось так =ROWS(‘C:\Мої документи\[Операції за II квартал.xlsx]Продажі’!A1:A8). Ця формула повертає номер рядків у діапазоні, який містить клітинки A1–A8 в іншій книзі (8).

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

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

Не форматуйте числа у формулах. Наприклад, якщо потрібно ввести значення “1 000 ₴”, у формулі введіть 1000. Якщо ввести кому чи пробіл як частину числа, програма Excel інтерпретує це як символ роздільника. Якщо потрібно, щоб числа відображалися з роздільниками тисяч або мільйонів чи символами грошових одиниць, форматуйте клітинки після введення чисел.

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

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

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

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

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

Клітинки з формулами, які призводять до помилки: у формулі не використовується очікуваний синтаксис, аргументи або типи даних. Значення помилки можуть бути такими: #DIV/0!, #N/A, #NAME?, #NULL!, #NUM!, #REF!, і #VALUE!. Кожне з цих значень помилок має різні причини та вирішується по-різному.

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

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

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

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

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

Якщо формула не може правильно обчислити результат, excel відображає значення помилки, наприклад #####, #DIV/0!, #N/A, #NAME?, #NULL!, #NUM!, #REF!, і #VALUE!. Кожен тип помилки має різні причини та різні рішення.

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

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

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

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

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

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

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

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

Спробуйте скористатися функцією IFERROR, щоб не отримувати помилку #N/A. Наприклад, ось так:

=IFERROR(VLOOKUP(D2;$D$6:$E$8;2;TRUE);0)

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

Примітка.: Перевірте, чи правильно написано ім’я формули. У цьому прикладі формулу SUM написано неправильно. Видаліть “e”, і Excel виправить його.

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

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

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

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

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

Можливо, ви випадково видалили рядок або стовпець. У нашому прикладі видалено стовпець B у формулі =SUM(A2;B2;C2) і ось результат.

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

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

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

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

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

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

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

Виберіть клітинки, які потрібно відстежувати. Щоб виділити всі клітинки на аркуші з формулами, перейдіть до розділу Основне >Редагування > виберіть Знайти & Виділити (або натисніть клавіші Ctrl+G або Control+G на комп’ютері Mac)> Перейти до спеціальних >формул.

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

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

1. Якщо панель інструментів Вікно контрольного значення не відображається, перейдіть до розділу Формули >Аудит формули > виберіть Вікно контрольного значення.
2. Виберіть клітинки, які потрібно видалити. Щоб виділити кілька клітинок, натисніть клавішу Ctrl, а потім виділіть їх.
3. Виберіть Видалити контрольне значення.

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

Вміст діалогового вікна “Обчислення формули”

Спочатку відображається вкладена формула. Функції AVERAGE і SUM вкладено у функцію IF.

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

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

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

Функція IF повертає значення третього аргументу (аргумент “значення_якщо_хибність”). Функцію SUM обчислено не буде, оскільки вона – другий аргумент функції IF (аргумент “значення_якщо_істина”), і її буде повернуто лише тоді, коли вираз матиме значення True.

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

• Деякі частини формул, які використовують функції IF і CHOOSE , не обчислюються . У таких випадках #N/A відображається в полі Обчислення .
• Якщо посилання пусте, у полі Обчислення відображається нульове значення (0).
• Після кожної зміни аркуша виконується повторне обчислення наведених нижче функцій, у результаті чого результати в діалоговому вікні Обчислення формули можуть відрізнятися від тих, що відображаються в клітинці: RAND, AREAS, INDEX, OFFSET, CELL, INDIRECT, ROWS, COLUMNS, NOW, TODAY, RANDBETWEEN.

Потрібна додаткова довідка?

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

Як виправити помилку у документі

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

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

Розбіжність навіть у одній літері в написанні ім’я, по-батькові чи прізвищі може дуже ускладнити оформлення документів.

Необхідність у виправленні помилки найчастіше виникає при оформленні спадщини.

Так, нотаріуси відмовляють у видачі свідоцтва про право на спадщину при виявленні помилок у правовстановлюючих документах.

Також необхідність у виправленні помилки виникає при реєстрації права власності на нерухомість в Державному реєстрі речових прав на нерухоме майно.

На сьогоднішній день існує два способи виправити помилки в документі:

2) Судовий (шляхом встановлення належності правовстановлюючого документа конкретній особі).

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

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

Законодавство передбачає спрощений порядок розгляду таких заяв.

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

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

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

Потрібна допомога адвоката? Не затягуйте з вирішенням проблеми – записуйтесь на консультацію за тел. 096-203-18-51. Інформація про вартість консультації та інших послуг

Читайте більше про поради адвоката тут