Формули Excel не працюють: можливі причини та як це виправити!

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

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

Але оскільки ми живемо у світі, який дотримується принципу Парето, якщо ви перевірите деякі загальні проблеми, це, ймовірно, вирішить 80% (а може, навіть 90% чи 95% проблем, де формули не працюють у Excel).

І в цій статті я висвітлю ті загальні проблеми, які, ймовірно, є причиною ваших Формули Excel не працюють.

Тож почнемо!

Неправильний синтаксис функції

Дозвольте мені почати з того, що зазначу очевидне.

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

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

Наприклад, функція VLOOKUP бере три обов’язкових аргументу та один необов’язковий аргумент.

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

Наприклад, припустимо, що у вас є набір даних, як показано нижче, де вам потрібно знати оцінку оцінки на іспиті 2 (у клітині F2).

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

= VLOOKUP (A2, A2: C6,2, FALSE)

У цьому випадку формула обчислює (оскільки повертає значення), але результат є неправильним (замість оцінки на іспиті 2 вона дає оцінку на іспиті 1).

Інший приклад, коли потрібно бути обережним щодо аргументів, - це використання VLOOKUP з наближеним збігом.

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

Нижче наведено приклад, коли я маю дані оцінок для деяких студентів, і я хочу витягти оцінки на іспиті 1 для студентів у таблиці праворуч.

Коли я використовую формулу VLOOKUP нижче, це дає мені помилку для деяких імен.

= VLOOKUP (E2, $ A $ 2: $ C $ 6,2)

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

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

Хоча я взяв приклад функції VLOOKUP, у цьому випадку це те, що може бути застосоване до багатьох формул Excel, які також мають необов’язкові аргументи.

Читайте також: Виявлення помилок за допомогою налагодження формул Excel

Додаткові місця викликають несподівані результати

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

Наприклад, у наведеному нижче прикладі, якщо я намагаюся використовувати VLOOKUP для отримання оцінки для Mark, це видає мені помилку #N/A (недоступна помилка).

Хоча я бачу, що формула правильна, а ім’я «Позначити» - це явно список, я не бачу, що в клітинці є кінцевий пробіл із назвою (у клітинці D2).

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

Щоб вирішити цю проблему, потрібно видалити зайві символи пробілу.

Ви можете зробити це, використовуючи будь -який із наведених нижче способів.

1. Перед використанням у формулах очистіть клітинку та видаліть усі провідні/кінцеві пробіли
2. Використовуйте функцію TRIM у формулі, щоб переконатися, що будь -які провідні/кінцеві/подвійні пробіли ігноруються.

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

= VLOOKUP (TRIM (D2), $ A $ 2: $ B $ 6,2,0)

Хоча я взяв приклад VLOOKUP, це також поширена проблема при роботі з функціями TEXT.

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

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

Використання ручного розрахунку замість автоматичного

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

Excel має два режими розрахунку - Автоматичний та Посібник.

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

Це обстановка, до якої ми всі звикли.

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

Але в деяких випадках люди вмикають налаштування обчислення вручну.

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

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

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

Все, що вам потрібно зробити в цьому випадку, - це або повернути розрахунок до автоматичного, або примусити перерахунок, натиснувши клавішу F9.

Нижче наведено кроки для зміни розрахунку з ручного на автоматичний:

1. Перейдіть на вкладку Формула
2. Натисніть на Параметри розрахунку
3. Виберіть Автоматично

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

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

Видалення рядків/стовпців/клітинок, що ведуть до #REF! Помилка

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

Коли це трапляється, Excel іноді коригує посилання та переконується, що формули працюють нормально.

А іноді… це не може.

На щастя, одним ясним ознакою, яку ви отримуєте, коли формули розриваються при видаленні клітинок/рядків/стовпців, є #REF! помилка в клітинках. Це помилка посилання, яка вказує на те, що існує певна проблема з посиланнями у формулі.

Дозвольте мені показати вам, що я маю на увазі, на прикладі.

Нижче я використав формулу SUM, щоб додати клітинки A2: A6.

Тепер, якщо я видалю будь -яку з цих комірок/рядків, формула SUM поверне #REF! помилка. Це відбувається тому, що коли я видалив рядок, формула не знає, на що посилатися зараз.

Ви можете побачити, що третій аргумент у формулі став #REF! (яка раніше посилалася на клітинку, яку ми видалили).

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

Неправильне розміщення дужок (BODMAS)

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

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

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

Наприклад, припустимо, що у вас є така формула:

=5+10*50

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

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

=(5+10)*50

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

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

Оператор	Опис	Порядок пріоритету
: (двокрапка)	Діапазон	1
(єдиний пробіл)	Перехрестя	2
, (кома)	профспілки	3
-	Заперечення (як у -1)	4
%	Процент	5
^	Поповнення степенем	6
* та /	Множення і ділення	7
+ і -	Додавання і віднімання	8
&	Сполучення	9
= =	Порівняння	10

Вивезти / Як виправити: Завжди використовуйте дужки, щоб уникнути плутанини, навіть якщо ви знаєте порядок пріоритету і використовуєте його правильно. Наявність дужок полегшує перевірку формул.

Неправильне використання абсолютних/відносних посилань на клітинки

Коли ви копіюєте та вставляєте формули в Excel, вона автоматично коригує посилання. Іноді це саме те, що вам потрібно (переважно, коли ви вставляєте копії формул у стовпці), а іноді ви не хочете, щоб це сталося.

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

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

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

Дозвольте мені показати вам приклад.

Нижче у мене є набір даних, де я хочу отримати оцінку на іспиті 1 для імен у стовпці E (простий варіант використання VLOOKUP)

Нижче наведена формула, яку я використовую у комірці F2, а потім копіюю у всі комірки під нею:

= VLOOKUP (E2, A2: B6,2,0)

Як бачите, ця формула в деяких випадках дає помилку.

Це відбувається тому, що я не заблокував аргумент масиву таблиці - це так А2: В6 у комірці F2, хоча це мало бути $ A $ 2: $ B $ 6

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

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

Неправильне посилання на назви аркушів / робочих книг

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

Наприклад, якщо я хочу посилатися на клітинку A1 у Sheet2, посилання буде таким = Аркуш2! A1 (де після назви аркуша є знак оклику)

І якщо у назві аркуша є кілька слів (скажімо, це приклади даних), посилання буде таким = 'Приклади даних'! A1 (де ім’я укладено в одинарні лапки, за якими слідує знак оклику).

Якщо ви маєте на увазі зовнішню книгу (скажімо, ви маєте на увазі клітинку A1 у "Прикладі аркуша" у книзі під назвою "Приклад робочої книги"), посилання буде таким, як показано нижче:

= '[Приклад робочої книги.xlsx] Приклад аркуша'! $ A $ 1

І якщо ви закриєте книгу, посилання зміниться, включивши весь шлях до книги (як показано нижче):

= 'C: \ Users \ sumit \ Desktop \ [Приклад робочої книги.xlsx] Приклад аркуша'! $ A $ 1

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

Читайте також: Як знайти зовнішні посилання та посилання в Excel

Циркулярні посилання

Кругове посилання - це коли ви посилаєтесь (прямо чи опосередковано) на ту саму клітинку, де обчислюється формула.

Нижче наведено простий приклад, коли я використовую формулу SUM у комірці А4, використовуючи її у самому обчисленні.

= SUM (A1: A4)

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

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

Для цього перейдіть на вкладку Формула і в групі «Аудит формул» натисніть на спадне меню Перевірка помилок (маленька стрілка вниз).

Наведіть курсор на параметр «Круговий довідник», і він покаже клітинку з проблемою кругового посилання.

Осередки у форматі тексту

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

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

Це не змушує його обчислювати і давати результат.

І це має легке виправлення.

1. Змініть формат на «Загальні» з «Текст» (це на вкладці «Домашня сторінка» у групі «Номери»)
2. Перейдіть до комірки з формулою, перейдіть у режим редагування (використовуйте клавішу F2 або двічі клацніть по клітинці) і натисніть Enter

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

Якщо є апостроф, його можна просто видалити.

Автоматичне перетворення тексту в дати

У Excel є така погана звичка перетворювати дані, які виглядають як дата у фактичну дату. Наприклад, якщо ввести 1/1, Excel перетворить її на 01 січня поточного року.

У деяких випадках це може бути саме те, що ви хочете, а в деяких випадках це може працювати проти вас.

А оскільки Excel зберігає значення дати та часу як цифри, як тільки ви вводите 1/1, він перетворює їх на число, що представляє 1 січня поточного року. У моєму випадку, коли я це роблю, він перетворює його на число 43831 (для 01-01-2020).

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

Як це виправити?

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

Нижче наведено кроки, щоб змінити формат на текст, щоб він автоматично не перетворював текст на дати:

1. Виберіть клітинки/діапазон, де потрібно змінити формат
2. Натисніть на вкладку Домашня сторінка
3. У групі Число натисніть спадне меню Формат
4. Натисніть Текст

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

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

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

Приховані рядки/стовпці можуть дати несподівані результати

Це не випадок, коли формула дає вам неправильний результат, а використовує неправильну формулу.

Наприклад, припустимо, що у вас є набір даних, як показано нижче, і я хочу отримати суму всіх видимих ​​клітинок у стовпці C.

У комірці C12 я використав функцію SUM, щоб отримати загальну вартість продажу для всіх цих даних.

Все йде нормально!

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

І ось проблема - формула в комірці С12 все ще показує той самий результат - тобто суму всіх записів.

Як я вже сказав, формула не дає неправильного результату. Насправді, функція SUM працює нормально.

Проблема в тому, що ми використали тут неправильну формулу.

Функція SUM не може враховувати відфільтровані дані та видавати результат для всіх клітинок (прихованих або видимих). Якщо ви хочете отримати лише суму/кількість/середнє значення видимих ​​клітинок, використовуйте функції SUBTOTAL або AGGREGATE.

Ключовий винос - Зрозумійте правильне використання та обмеження функції в Excel.

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

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

Сподіваюся, вам цей підручник був корисним!