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

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

  1. Перейдіть на вкладку Файл, виберіть пункт Параметри та клацніть розділ Формули.
  2. У розділі Правила перевірки помилок установіть або зніміть прапорці для зазначених нижче правил, а потім натисніть кнопку OK.
    Клітинки з формулами, які повертають помилку Оскільки у формулі не виявлено очікуваного синтаксису, аргументів або типів даних, відображається помилка. Значення помилки можуть виглядати як #####, #DIV/0!,#N/A, #NAME?, #NULL!, #NUM!, #REF! і #VALUE!. Усі значення помилки можуть мати свої причини та різні способи виправлення.

    ПРИМІТКА : Якщо безпосередньо в клітинку ввести таке значення помилки, як #N/A, воно не позначатиметься як помилка.

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

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

      ПРИМІТКА : Якщо скопійовані дані містять формулу, дані в обчислюваному стовпці буде замінено на цю формулу.

    • З області іншого аркуша переміщено або видалено клітинку, на яку посилається один із рядків обчислюваного стовпця.
    Клітинки, у яких рік зображено лише 2 цифрами Клітинка містить текстове подання дати з двох цифр, які можуть бути неправильно інтерпретовані як століття під час використання у формулі. Наприклад, у формулі =YEAR(«01.01.31″) рік може бути інтерпретовано як 1931 або 2031. Використовуйте це правило для перевірки сумнівних дат у текстовому поданні.
    Числа в текстовому форматі або перед якими стоїть апостроф Клітинка містить числа, збережені як текст. Зазвичай це трапляється внаслідок імпорту даних з інших джерел. Числа, збережені як текст, можуть призвести до неправильного порядку сортування та не можуть використовуватися для обчислень, тому краще перетворити їх на числовий формат.
    Формули, неузгоджені з іншими формулами в області Формула не відповідає схемі інших суміжних із нею формул. У багатьох випадках формули, суміжні з іншими формулами, відрізняються лише посиланнями на використовувані клітинки. У наведеному нижче прикладі чотирьох суміжних формул поруч із формулою =SUM(A10:F10) у програмі Excel відображатиметься помилка, оскільки суміжні формули змінюються на один рядок, а формула =SUM(A10:F10) змінюється на 8 рядків (у цьому випадку в програмі Excel очікується формула =SUM(A3:F3)).

    А
    формули
    =SUM(A1:F1)
    =SUM(A2:F2)
    =SUM(A10:F10)
    =SUM(A4:F4)

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

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

    Наприклад, після застосування цього правила поруч із формулою =SUM(A2:A4) відобразиться помилка, оскільки клітинки A5, A6 і A7 суміжні із клітинками, на які посилається формула (A2:A4), а також із клітинкою, яка містить формулу (A8), і ці три клітинки (A5, A6 і A7) містять дані, на які має посилатися формула.

    A
    Рахунок
    15 000
    9 000
    8 000
    20 000
    5 000
    22 500
    =SUM(A2:A4)
    Не заблоковано клітинку, яка містить формулу Формулу не заблоковано для захисту. За замовчуванням усі клітинки на аркуші блокуються для захисту, тому це означає, що користувач вимкнув захист для цієї клітинки. Захищену формулу не можна змінити, не вимкнувши захист. Переконайтеся, що цю клітинку насправді не потрібно блокувати. Захист клітинок із формулами запобігає їх зміненню та допомагає уникнути помилок у майбутньому.
    Формула посилається на пусті клітинки Формула містить посилання на пусту клітинку. Це може призвести до неочікуваних результатів, як у наведеному нижче прикладі.

    Припустімо, що потрібно обчислити середнє значення чисел у наведеному нижче стовпці. Якщо третя клітинка пуста, оскільки вона не вважається значенням, то її не включено до обчислення й результат становить 22,75. Якщо третя клітинка містить 0 (0 вважається значенням), її включено до обчислення й результат становить 18,2.

    A
    Дані
    24
    12
    45
    10
    =AVERAGE(A2:A6)
    Неприпустимі дані в таблиці Таблиця містить помилку перевірки. Перевірте настройки перевірки для клітинки. Для цього на вкладці Дані в групі Знаряддя даних натисніть кнопку Перевірка даних.

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

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

  1. Виберіть аркуш, який потрібно перевірити на помилки.
  2. Якщо аркуш обчислюється вручну, натисніть клавішу F9, щоб повторно обчислити аркуш і відобразити актуальні результати формул.
  3. Виберіть вкладку Формули й натисніть кнопку Контроль помилок.

    Формула аудиту групи на вкладці Формули

    Коли програма Excel виявляє помилки, відображається вікно Перевірка помилок.

  4. Щоб знову перевірити пропущені раніше помилки, виконайте наведені нижче дії. Вікно Контроль помилок при цьому можна не закривати.
    1. Перейдіть на вкладку Файл, виберіть пункт Параметри та клацніть розділ Формули.
    2. У розділі Перевірка помилок натисніть кнопку Скинути пропущені помилки, а потім натисніть кнопку OK.
    3. У діалоговому вікні Контроль помилок натисніть кнопку Продовжити.

      ПРИМІТКА : Скидання пропущених помилок впливає на всі помилки на всіх аркушах активної книги.

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

    Рядок формул

  6. У вікні Перевірка помилок натисніть кнопку з дією, яку потрібно виконати.

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

    Діалогове вікно Перевірка помилок

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

  7. Натискайте кнопку Далі, доки не завершите перевірку всіх помилок.

Виправлення типових помилок у формулах під час роботи

  1. Перейдіть на вкладку Файл, виберіть пункт Параметри та клацніть розділ Формули.
  2. У розділі Перевірка помилок установіть прапорець Увімкнути фонову перевірку помилок.
  3. Щоб змінити колір трикутника, який позначає місце виникнення помилки, виберіть бажаний колір у полі Позначати помилки цим кольором.
  4. Виділіть клітинку із трикутником у верхньому лівому куті.
  5. Натисніть кнопку Контроль помилок, зображення кнопки яка з’явиться поруч із клітинкою, і виберіть потрібну команду.

    Доступні команди залежать від типу помилки. Перший рядок у контекстному меню описує помилку.

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

  6. Повторно виконайте кроки 4 та 5.

Прокоментуйте першими

Залишити коментар

Ваш e-mail не буде публікуватись.

*