Чому формули в Excel не функціонують?

Багато людей знають, що інколи формула може не давати бажаного результату. Причини для цього можуть бути різними.

На жаль, існує чимало причин. Тому важко одразу з’ясувати, чому не функціонує конкретна формула.

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

У цьому матеріалі ми розглянемо ці типові помилки.

Отже, розпочнемо!

Неправильний виклик формули

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

Наприклад, розглянемо функцію ВПР. Вона має три обов’язкові аргументи, які необхідно вказати, і один додатковий, який можна залишити необов’язковим.

Припустимо, ми маємо ось таку таблицю:

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

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

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

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

Припустимо, у нас існує ось така таблиця:

Ми користуємося ВВР у наступний спосіб:

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

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

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

Недоліки в значеннях

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

Уявімо собі наступний випадок:

Ви, напевно, помітили, що в кінці клітинки D2 після імені є пробіл. Саме тому функція ВВР не може виконати свою роботу належним чином.

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

Якщо ми усунемо неполадку — усе буде функціонувати належним чином.

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

Для нашого прикладу використовуємо функціональну комбінацію:

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

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

Ручний метод обчислення у формулах

Можливо, ви не знайомі з поняттям режимів розрахунку. Дозвольте мені роз’яснити:

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

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

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

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

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

Як діяти? Можна натиснути F9, щоб примусити Excel порахувати знову, або активувати автоматичний режим, який є стандартним.

Яким чином це реалізувати?

Алгоритм дій поетапно:

• Клікніть на вкладку “Формули”;

• У розділі “Налаштування обчислень” виберіть опцію “Автоматичний”.

Помилка #REF

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

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

У загальному випадку, #REF з’являється, коли програма не здатна отримати значення з вказаного посилання.

Ось зразок:

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

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

Невірна послідовність виконання розрахунків

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

Наприклад, розглянемо наступний випадок:

=5 + 10 помножити на 50

По-перше, здійснюється множення, а вже потім відбувається додавання.

Якщо необхідно спочатку здійснити додавання, можемо помістити його в дужки.

=(5 плюс 10) помножити на 50

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

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

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

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

Розгляньмо відразу приклад.

Потрібно визначити загальну кількість балів за перший екзамен.

Пропонуємо застосувати наступну формулу:

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

Ось і помилка. Але в чому ж вона полягає?

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

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

Некоректне посилання на документ або повідомлення

У цій ситуації виникла подібна проблема: ми створили посилання на документ або лист, але допустили помилку. Внаслідок цього сталася помилка.

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

=Лист1!A1

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

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

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

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

='C:UsersExcelDesktop[приклад.xlsx]лист1'!$A$1 Якщо ви вирішите поміняти назву файла чи листа, виникне помилка.

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

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

Прикладом може слугувати наступне: у клітинці A4 ми вводимо цю формулу – =СУМ(A1:A4).

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

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

Натисніть на вкладку “Формули” і оберіть “Перевірка помилок”. Ви знайдете параметр “Циклічні посилання”, наводьте на нього курсор, щоб переглянути клітинки, в яких Excel виявив циклічні зв’язки.

Форматування текстового осередку

Якщо ви спостерігаєте щось таке:

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

Яким чином поновити виконання формули:

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

Якщо це не допомогло — переконайтеся, що перед формулою стоять ”’. Це можна перевірити лише в режимі редагування.

Конвертація чисел у дати

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

Іноді це може бути корисним, але часом викликає невдоволення.

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

Яким чином це реалізувати?

Послідовний алгоритм дій:

• Оберіть елементи, що потребують перетворення;
• Натисніть на вкладку “Головна”;

• Зазначте формат “Текст”.

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

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

Сховані компоненти

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

У нас є колонка З з даними про продажі:

Пропоную обчислити загальну суму продажів:

Усе вірно, але зверніть увагу на те, що буде далі.

Можна активувати показ інформації про продажі принтерів:

Спостерігаємо дивовижне – підсумок залишився таким же.

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

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

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

Сподіваюся, що ця інформація була для вас корисною!