Способи уникнення недійсних формул

Excel кидає різні фунти (#) помилки, як-от #VALUE!, #REF!, #NUM, #N/A, #DIV/0!, #NAME?, і #NULL!, щоб позначити щось у формулі, не працює належним чином. Наприклад, #VALUE! Помилка спричинена неправильним форматуванням або непідтримуваними типами даних у аргументах. Ви побачите #REF! помилка, якщо формула посилається на клітинки, видалені або замінені іншими даними. Інструкції з виправлення неполадок відрізнятимуться для кожної помилки.

Зверніться до будь-якої з наведених нижче розділів, які відповідають повідомленням про помилку "фунт":

• Виправлення помилки #NUM! помилка

• Виправлення помилки #VALUE! помилка

• Виправлення помилки #N/A

• Виправлення помилки #DIV/0! помилка

• Виправлення помилки #REF! помилка

• Виправлення помилки #NAME? помилка

• Виправте #NULL! помилка

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

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

Крім того, можна зробити так, щоб це діалогове вікно не відображалося під час запуску програми. Для цього послідовно виберіть елементи Файл > Параметри > Додатково, перейдіть до розділу "Загальні" та зніміть прапорець Запитувати про оновлення автоматичних зв’язків. 

Якщо у формулі не відображається значення, зробіть ось що:

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

  Порада.: Ви також можете використовувати сполучення клавіш Ctrl + ' (ключ вище за клавішу табуляції). Після цього стовпці буде автоматично розширитися, щоб відобразити формули, але не хвилюйтеся, коли ви повертаєшся назад до звичайного подання, розмір стовпців буде змінити.

• Якщо навіть після цього проблему вирішити не вдається, цілком можливо, що для клітинки вибрано текстовий формат. Щоб змінити формат, можна клацнути клітинку правою кнопкою миші та послідовно вибрати елементи Формат клітинок > Загальний (або натиснути сполучення клавіш Ctrl+1), а потім послідовно натиснути клавіші F2 > Enter.

• Якщо в стовпці великий діапазон клітинок мають текстовий формат, можна виділити діапазон, застосувати потрібний числовий формат і послідовно вибрати елементи Дані > Текст за стовпцями > Готово. Таким чином формат застосується до всіх виділених клітинок.

  

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

1. На вкладці Файл виберіть пункт Параметри, а потім виберіть категорію Формули.

2. У розділі Параметри обчислення в області Обчислення робочої книги виберіть параметр автоматично.

   

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

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

Докладні відомості про циклічні посилання наведено в статті видалення або надання циклічного посилання

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

Якщо ввести щось на кшталт SUM(A1:A10), в Excel замість результату формули відображатиметься текстовий рядок SUM(A1:A10). Крім того, якщо ввести 11/2, у програмі Excel відображається дата, наприклад 2-Nov або 11/02/2009, замість ділення 11 на 2.

Щоб уникнути таких несподіваних результатів, завжди починайте функцію зі знака рівності. Наприклад, вводьте =SUM(A1:A10) і =11/2.

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

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

Наприклад, функція UPPER приймає тільки один рядок тексту або посилання на клітинку як аргумент: =UPPER("привіт") або =UPPER(C2).

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

Щоб швидко переглянути синтаксис певної функції, див. список функцій Excel (за категоріями).

Не вводити числа, відформатовані за допомогою знаків долара ($) або десятковими роздільниками (,) у формулах, оскільки знаки долара вказують на абсолютні посилання та коми – роздільники аргументів. Замість $1000 введіть у формулі лише число 1000.

Якщо ви використовуєте відформатовані числа в аргументах, ви отримаєте несподівані результати обчислення, але ви також можете побачити помилку #NUM! . Наприклад, якщо ви ввели формулу = ABS (-2 134) , щоб отримати абсолютне значення-2134, у програмі Excel відображається #NUM! помилка, оскільки функція ABS приймає лише один аргумент, і він бачить-2 і 134 як окремі аргументи.

Формула може не повернути потрібні результати, якщо тип даних клітинки не можна використовувати в обчисленнях. Наприклад, якщо в клітинку, відформатовану як текст, введено просту формулу =2+3, у програмі Excel обчислення введених даних може не відбутися. У клітинці відобразиться лише =2+3. Щоб виправити таку помилку, змініть для клітинки тип даних Текстовий на Загальний

1. Виділіть клітинку.

2. Перейдіть на вкладку Основне, клацніть стрілку поряд із полем Числовий (або натисніть клавіші Ctrl+1) і виберіть пункт Загальний.

3. Натисніть клавішу F2, щоб перейти в режим редагування клітинки, а потім натисніть клавішу Enter, щоб прийняти формулу.

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

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

Однак, якщо ви використовуєте посилання на стільникові, програма Excel поверне #NAME? помилку #REF!.

Текст у формулах потрібно брати в лапки.

Наприклад, у формулі ="Сьогодні " & TEXT(TODAY();"dddd, dd mmmm") текстовий рядок "Сьогодні " поєднується з результатами функцій TEXT і TODAY, і в клітинці повертається введене слово та сьогоднішня дата, наприклад Сьогодні понеділок, 30 травня.

У формулі "сьогодні" є пробіл перед закінченням лапок, щоб надати пусте місце між словами "сьогодні" і "понеділок, 30 травня". Без лапок навколо тексту, у формулі може відображатися #NAME? помилка.

В одну формулу можна вкласти (помістити) щонайбільше 64 рівні функцій.

Наприклад, формула =IF(SQRT(PI())<2;"Менше двох";"Більше двох") містить 3 рівні функцій: функцію PI вкладено у функцію SQRT, яку у свою чергу вкладено у функцію IF.

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

Наприклад, щоб повернути значення з клітинок D3 на аркуші під назвою "Квартальні дані" в книзі, введіть: = "Квартальні дані"! D3. Без лапок навколо імені аркуша у формулі відображається #NAME? помилка.

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

Якщо посилання вказує на значення або клітинку з іншої книги, ім’я цієї книги потрібно взяти в квадратні дужки ([]), після яких указується ім’я аркуша, що містить значення або клітинку.

Наприклад, щоб послатися на клітинки a1 – A8 на аркуші збуту в книзі Q2 Operations, яку відкрито в програмі Excel, введіть: = [Q2 операціях. XLSX] продажі! A1: A8. Без квадратних дужок у формулі відображається #REF! помилка.

Якщо книгу не відкрито в програмі Excel, потрібно вказати повний шлях до файлу.

Приклад: =ROWS('C:\Мої документи\[Операції за 2-й квартал.xlsx]Збут'!A1:A8).

Розділення однієї з них на іншу, яка має нульове значення (0) або не призводить до помилки #DIV/0!.

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

=IF(B1;A1/B1;0)

Ця формула означає таке: ЯКЩО(B1 існує, тоді A1 ділиться на B1, інакше функція повертає 0).

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

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

• Якщо формула посилається на клітинки, які було видалено або замінено на інші дані, і повертає помилку #REF!Виберіть камеру з #REF! помилку #REF!. У рядку формул виберіть #REF! та видаліть його. Потім введіть діапазон для формули.

• Якщо відсутнє визначене ім'я, а формула, що посилається на це ім'я, повертає #NAME? помилку, визначте нове ім'я, яке посилається на потрібний діапазон, або змініть формулу, щоб посилатися безпосередньо на діапазон клітинок (наприклад, a2: D8).

• Якщо відсутній аркуш, а формула, яка посилається на нього, повертає помилку #REF!, виправити це, на жаль, неможливо. Аркуш, який було видалено, не можна відновити.

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

  Наприклад, якщо формула має вигляд =[Книга1.xlsx]Аркуш1'!A1, а файл Книга1.xlsx більше не існує, значення з цієї книги все ще будуть доступні. Однак якщо відредагувати та зберегти формулу, яка посилається на цю книгу, у програмі Excel відкриється діалогове вікно Оновити значення з пропозицією ввести ім’я файлу. Натисніть кнопку Скасувати, а потім переконайтеся, що ці дані не втрачено, замінивши формули, що посилаються на відсутню книгу, на результати цих формул.

Бувають випадки, коли потрібно скопіювати лише результат формули в клітинці, проігнорувавши синтаксис, записаний у рядку формул.

Наприклад, можливо, потрібно скопіювати отримане значення формули до клітинок на іншому аркуші. Або ви можете видалити значення, які використовувалися у формулі після того, як ви скопіювали отримане значення до іншої клітинок на аркуші. Обидві ці дії призводять до помилки неприпустимого посилання на клітинку (#REF!) щоб з'явитися в кінцевій клітинці, оскільки клітинки, які містять значення, які використовуються у формулі, більше не можуть посилатися на посилання.

Щоб уникнути цієї помилки, вставте в цільову клітинку лише результат формули (без синтаксису).

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

2. На вкладці Основне в групі Буфер обміну натисніть кнопку Копіювати .

   

   Сполучення клавіш: Ctrl+C.

3. Клацніть верхню ліву клітинку в область вставлення.

   Порада.: Щоб перемістити або скопіювати виділену область до іншого аркуша або книги, відкрийте вкладку іншого аркуша або перейдіть до іншої книги, а потім виділіть верхню ліву клітинку області вставлення.

4. На вкладці основне у групі буфер обміну натисніть кнопку Вставити , а потім виберіть пункт вставити значення, або натисніть клавіші ALT > E > S > V > введіть для Windows або Option > Command > v > v > введіть на комп'ютері Mac.

Процес обчислення складної або вкладеної формули можна простежити.

1. Виберіть формулу, яку потрібно проаналізувати.

2. Виберіть елементи Формули > Обчислити формулу.

   

3. Натисніть кнопку Обчислити, щоб проаналізувати значення підкресленого посилання. Результат обчислення відображається курсивом.

   

4. Якщо підкреслена частина формули – це посилання на іншу формулу, натисніть кнопку Крок із заходом, щоб відобразити формулу в полі Обчислення. Натисніть кнопку Крок із виходом, щоб повернутися до попередньої клітинки та формули.

   Кнопка Крок із заходом буде недоступна, коли посилання вдруге з’явиться у формулі, або якщо формула містить посилання на клітинку з іншої книги.

5. Продовжуйте, доки всі компоненти формули не обчисляться.

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

   Примітки.: 

   • Деякі частини функцій IF і CHOOSE не обчислюватимуться, а в полі Обчислення може відображатися помилка #N/A.

   • Пусті посилання відображаються в полі Обчислення як нульові значення (0).

   • Функції, які повторного обчислюються під час кожної зміни аркуша. Для цих функцій, зокрема RAND, AREAS, INDEX, OFFSET, CELL, INDIRECT, ROWS, COLUMNS, NOW, TODAY і RANDBETWEEN, у діалоговому вікні Обчислення формули можуть відображатися результати, відмінні від фактичних результатів у клітинці на аркуші.