Формулите на 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 за имената в колона Е (прост случай на използване на VLOOKUP)

По -долу е формулата, която използвам в клетка F2 и след това копирам във всички клетки под нея:

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

Както можете да видите, че тази формула дава грешка в някои случаи.

Това се случва, защото не съм заключил аргумента за масива на таблицата - това е A2: B6 в клетка 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 в клетка A4, докато я използвам в самото изчисление.

= SUM (A1: A4)

Въпреки че Excel ви показва подкана, която ви уведомява за кръговата справка, това няма да го направи за всеки екземпляр. И това може да ви даде грешен резултат (без предупреждение).

В случай, че подозирате, че играе кръгова справка, можете да проверите кои клетки я имат.

За да направите това, щракнете върху раздела Формула и в групата „Одит на формули“ щракнете върху падащата икона Проверка на грешки (малката стрелка надолу).

Задръжте курсора на мишката върху опцията Circular reference и тя ще ви покаже клетката, която има проблем с кръговата справка.

Клетки, форматирани като текст

Ако попаднете в ситуация, когато веднага след като въведете формулата като натиснат 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, за да получа общата стойност на продажбите за всички тези дадени записи.

Дотук добре!

Сега прилагам филтър към колоната с артикули, за да показвам само записите за продажбите на принтери.

И тук е проблемът - формулата в клетка C12 все още показва същия резултат - т.е. сумата от всички записи.

Както казах, формулата не дава грешен резултат. Всъщност функцията SUM работи отлично.

Проблемът е, че тук сме използвали грешна формула.

Функцията SUM не може да отчита филтрираните данни и да ви даде резултат за всички клетки (скрити или видими). Ако искате само да получите сумата/броя/средната стойност на видимите клетки, използвайте функции SUBTOTAL или AGGREGATE.

Ключово изнасяне - Разберете правилното използване и ограничения на функция в Excel.

Това са някои от често срещаните причини, които съм виждал къде си Формулите на Excel може да не работят или да дадат неочаквани или грешни резултати. Сигурен съм, че има много повече причини формулата на Excel да не работи или да се актуализира.

Ако знаете по някаква друга причина (може би нещо, което често ви дразни), уведомете ме в секцията за коментари.

Надявам се, че сте намерили този урок за полезен!