Чому не працюють формули в Excel

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

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

Але варто лише перевірити формулу на стандартні помилки і в більшості випадків цього вистачить, щоб, так би мовити, полагодити її.

У цій статті ми якраз і розглянемо ці стандартні помилки.

Тож почнемо!

Формула викликана неправильно

Отже, кожна функція має свої аргументи, відповідно, і свій синтаксис теж. Якщо ви припустилися помилки в ньому або, наприклад, не вказали/вказали неправильно будь-який аргумент – це неодмінно призведе до помилки.

Наприклад, візьмемо функцію ВПР. У неї 3 аргументи, які пропустити не можна, а також 1 додатковий, який можна і не прописувати.

Допустимо, у нас є така табличка:

Чому не працюють формули в Excel

Ось банальний приклад, через який може виникнути помилка (або ми отримаємо невірний результат).

= ВПР (A2; A2: C6; 2; FALSE)

Чому не працюють формули в Excel

Замість отримання оцінки за перший іспит (як і замислювалося), ми отримуємо оцінку за другий. А це ж неймовірно критично у розрахунках! Ви можете навіть не помітити це!

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

Допустимо, у нас є така табличка:

Чому не працюють формули в Excel

Використовуємо ВВР таким чином:

= ВПР (E2; $ A $ 2: $ C $ 6; 2)

Чому не працюють формули в Excel

Аргумент відповідності ми не вказали і подивіться, що сталося з розрахунками! Ми отримали 2 невірні значення, а у всіх інших випадках взагалі помилки.

Для прикладу ми взяли функцію ВПР, але, повірте, у решті випадків наслідки через помилку не менше.

Прогалини у значеннях

Їх досить складно помітити, але через них може бути купа помилок.

Припустимо, такий приклад:

Чому не працюють формули в Excel

Ви могли вже зрозуміти, що в кінці осередку D2 після імені йде пробіл. Через це функція ВВР не може нормально відпрацювати.

Чому не працюють формули в Excel

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

Якщо ми вилучимо прогалину — все запрацює як слід.

Щоб не перевіряти вручну кожен осередок, ми можемо замінити всі подвійні прогалини на один. Або використовувати функцію СЖПРОБЕЛИ.

Комбінація функцій для нашого прикладу:

=ВПР(СЖПРОБІЛИ(D2);$A$2:$B$6;2;0)

Найкраще, звичайно, просто обробити свою табличку, щоб подвійних чи потрійних прогалин не було.

Ручний режим розрахунку у формулах

Ви можете не знати, що таке режими розрахунку. Я зараз поясню:

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

Так це так. Кожного разу, коли ви змінюєте значення в будь-якій комірці, якщо вона вказана як дані для будь-якої формули — змінитися і результат цієї формули.

Але можна ввімкнути ручний режим. В цьому випадку, ви візьмете на себе роль «командира» і вручатимете вручну, коли проводити перерахунок.

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

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

Що робити? Або натиснути F9 і змусити Excel виконати перерахунок, або включити автоматичний режим, як і має бути.

Як це зробити?

Покрокова інструкція:

  • Натисніть “Формули”;

Чому не працюють формули в Excel

  • В опції “Параметри обчислень” вкажіть “Автоматично”.

Чому не працюють формули в Excel

Помилка #REF

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

Зазвичай Excel автоматично коригує вашу функцію, але так буває не завжди.

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

Ось приклад:

Чому не працюють формули в Excel

Якщо ми видаляємо будь-яку комірку, яка вказана у формулі, то ми отримуємо:

Чому не працюють формули в Excel

Виправити цю помилку досить просто – оновіть посилання у формулі.

Неправильний порядок обчислень

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

Наприклад, у нас є такий приклад:

=5+10*50

Спочатку виконується множення, а потім лише додавання.

Але якщо потрібно спочатку виконати додавання? Тоді ми можемо укласти додавання в дужки:

=(5+10)*50

Так от, якщо ви неправильно використовували, наприклад, ці дужки у формулі – результат буде невірним.

Неправильна вказівка посилань

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

Читачі нашого сайту знаю, що є 3 типи посилань в Excel. Але для нас, зараз, важливі лише два – відносний і абсолютний тип. Відносний тип — який буде адаптуватися під кожну нову комірку, а абсолютний — який не змінюватиметься.

Давайте одразу розглянемо приклад.

Нам необхідно вивести кількість балів за Exam 1.

Чому не працюють формули в Excel

Використовуємо таку формулу:

= ВПР (E2; A2: B6; 2; 0)

Чому не працюють формули в Excel

Ось помилка. Але в чому вона?

Ми не уклали область пошуку в $, тобто не зробили посилання абсолютним і Excel адаптував його для кожного наступного осередку. Ось і криється помилка.

Є спосіб швидко зробити посилання абсолютним – виділіть комірку (або діапазон) у формулі і натисніть F4.

Неправильне посилання на файл або лист

Тут схожа ситуація, ми зробили посилання на файл або аркуш, але зробили його неправильно. Через це виникає помилка.

Наприклад, нам потрібно послатися на перший осередок першого листа, це виглядатиме так:

=Аркуш1!A1

А ось якщо в імені листа є пробіл, його ім'я необхідно укласти в одинарні лапки.

А якщо ми робимо посилання на файл та аркуш одночасно, то все це має бути укладено в одинарні лапки:

='[приклад.xlsx]лист1'!$A$1

Припустимо, ми закрили Excel, у разі він автоматично вкаже шлях до файлу, який ми посилаємося:

='C:UsersExcelDesktop[приклад.xlsx]лист1'!$A$1

Але якщо ви зміните назву файлу або аркуша, отримайте помилку.

Циклічне посилання

Що це? Це коли ви вказуєте посилання на ту комірку, яка є джерелом формули.

Наприклад, ми прописуємо цю формулу в A4

=СУМ(A1:A4)

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

На щастя, легко перевірити, де у вас є циклічні посилання.

Клацніть на “Формули” та “Перевірка помилок”, там побачите параметр “Циклічні посилання”, наведіть на нього і побачите осередки, де Excel помітив циклічні посилання.

Чому не працюють формули в Excel

Текстовий формат осередку

Якщо ви бачите щось подібне:

Чому не працюють формули в Excel

То знайте, ви зіткнулися з текстовим форматом осередку. Формула не виконується, а просто відображається як текст.

Як зробити так, щоб формула знову виконувалася:

  1. Поміняйте формат осередку на «Загальний»;
  2. А потім перезбережіть значення комірки.

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

Перетворення чисел на дату

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

Часто це допомагає, але іноді просто дратує.

У такому разі найкращий варіант – перетворити комірку на текстовий формат.

Як це зробити?

Покрокова інструкція:

  • Виділіть елементи, які потрібно перетворити;
  • Клацніть на “Головна”;

Чому не працюють формули в Excel

  • І вкажіть формат “Текст”.

Чому не працюють формули в Excel

Отже, після цих перетворень — усе, що ви не ввели б у осередок вважатиметься текстом. Навіть якщо це формула. Excel не чіпатиме такі осередки.

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

Приховані елементи

Відразу розглянемо приклад.

У нас є стовпчик З значеннями продажів:

Чому не працюють формули в Excel

Давайте знайдемо суму всіх продажів:

Чому не працюють формули в Excel

Все правильно, але дивіться, що буде далі.

Допустимо, увімкнемо відображення продажів принтерів:

Чому не працюють формули в Excel

Бачимо несподіване – результат не змінився.

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

Щоб правильно рахувати суму в таких ситуаціях – використовуйте функцію СУМ в комбінації з ПРОМІЖНІ.ПІДСУМКИ.

От і все! Ми розглянули велику кількість помилок та причин, через які вони можуть виникати. Звісно, ми не охопили всі помилки, тому що зробити це неможливо. Але, все-таки, це були найпоширеніші помилки.

Сподіваюся, ця стаття виявилася корисною для вас!

nBook
Додати коментар