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

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

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

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

Зображення діалогового вікна "Ця формула містить помилку" в програмі Excel

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

Спершу натисніть кнопку OK або клавішу Esc , щоб закрити повідомлення про помилку.

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

Зображення кнопки "Скасувати" в рядку формул

Не знаєте, як діяти далі або в чому проблема? Спробуйте знайти схожі запитання на форумі спільноти Microsoft Excel чи опублікуйте своє запитання.

Посилання на форум спільноти Excel

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

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

Примітка.: #### – це помилка, яка не пов’язана з синтаксисом формули. Вона вказує на те, що стовпець не досить широкий, щоб відобразити вміст клітинки. Просто перетягніть межу стовпця, щоб розширити його, або послідовно виберіть елементи Основне > Формат > Автодобір ширини стовпця.

Зображення, що ілюструє вибір елементів "Основне > Формат > Автодобір ширини стовпця"

Додаткові відомості про помилки зі знаком решітки:

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

Діалогове вікно з повідомленням про недійсне посилання в програмі Excel

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

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

Зображення параметра "Запитувати про оновлення автоматичних зв’язків"

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

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

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

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

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

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

    Зображення діалогового вікна, яке можна відкрити, вибравши елементи "Дані > Текст за стовпцями"

Коли формули не обчислення, потрібно перевірити, чи ввімкнуто автоматичне обчислення у програмі Excel. Формули не обчислюватимуться, чи ввімкнуто обчислення вручну. Виконайте ці кроки, щоб перевірити наявність автоматичного обчислення.

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

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

    Зображення параметрів обчислення "автоматично" та "вручну"

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

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

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

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

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

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

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

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

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

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

Знімок екрана, на якому показано довідкову панель інструментів
Довідкова панель інструментів

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

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

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

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

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

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

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

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

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

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

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

Вікно повідомлення з пропозицією замінити у формулі з множенням символ x на зірочку (*)
Повідомлення про помилку через використання символу "x" для множення констант

Проте якщо у формулі використовувати посилання на клітинки, програма Excel поверне помилку #NAME?.

Помилка #NAME?, що відображається, якщо замість зірочки (*) для множення використовується символ "x"
Помилка #NAME? через використання символу "x" для множення

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

Наприклад, у формулі ="Сьогодні " & 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).

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

Порада.: Найпростіший спосіб отримати шлях до іншої книги – відкрити книгу, потім з вихідної книги, введіть =, а потім використовувати Сполучення клавіш Alt + клавіша табуляції , щоб перейти до іншої книги і виберіть будь-яку клітинку на аркуші, який потрібно. Закрийте вихідну книгу. Формулу буде автоматично оновлено для відображення повний шлях та аркуша ім'я файлу разом із необхідні синтаксис. Можна навіть копіювати та вставте шлях та використовувати в будь-якому вона потрібна.

Якщо розділити клітинку на іншу клітинку, яка містить нуль (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. На вкладці Основне в групі Буфер обміну натисніть кнопку Копіювати Зображення кнопки .

    Зображення стрічки Excel

    Сполучення клавіш: 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, у діалоговому вікні Обчислення формули можуть відображатися результати, відмінні від фактичних результатів у клітинці на аркуші.

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

Ви завжди можете поставити запитання експерту в спільноті Tech (у розділі Excel), отримати підтримку в спільноті, що допомагає знайти відповіді на запитання, або запропонувати нову функцію чи вдосконалення на форумі Excel User Voice.

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

Огляд формул в Excel

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

Функції Excel (за алфавітом)

Функції Excel (за категоріями)

Удосконалення навичок роботи з Office
Ознайомтеся з навчальними матеріалами
Отримуйте нові функції раніше за інших
Приєднайтеся до оцінювачів Office

Ця інформація корисна?

Дякуємо за ваш відгук!

Дякуємо за відгук! Схоже, вам може стати в нагоді допомога одного з наших спеціалістів служби підтримки Office, з яким ми вас можемо з’єднати.

×