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

Формули з кількома аргументами використовують роздільники списків, щоб відділити ці аргументи. Роздільник може відрізнятися залежно від локалізації ОС і параметрів Excel. Найпоширенішими роздільниками списку є кома "," і крапка з комою ";".

Формула не працюватиме, якщо в будь-якій із її функцій використовуються неправильні роздільники.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  

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

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

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

   

Щоб дізнатися більше про обчислення, перегляньте статтю Змінення переобчислення, ітерації або точності формули.

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

Щоб дізнатися більше про циклічні посилання, перегляньте статтю Видалення або дозвіл циклічного посилання.

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

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

Щоб уникнути таких несподіваних результатів, завжди починайте функцію зі знака рівності. Наприклад, введіть =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 на аркуші "Продажі" в книзі "Операції за 2-й квартал", яку відкрито в Excel, введіть формулу =[Операції за 2-й квартал.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! і видаліть. Потім знову введіть діапазон для формули.

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

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

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

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

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

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

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

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

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

   

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

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

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

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

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

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

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

   

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

   

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

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

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

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

   Примітки.: 

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

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

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