Защо Excel не преизчислява автоматично формулите. Формулите не работят в Excel. Грешки в Excel - Грешка във формулата поради твърде голяма стойност

Главна функция Excel програми- работа с формули. За това най-често се използва. Понякога се случва формулите в Excel да не работят и в този случай трябва да направите нещо. Това е, което ще бъде обсъдено по-нататък.

Отстраняваме проблема

Има много причини формулата да не работи в Excel. Може би сте задали неправилно настройките на програмата или определен диапазон или формулата е въведена неправилно.

Формулите не работят при плъзгане

Същността на грешката е, че се създават няколко колони, например 2, а резултатът трябва да се покаже в третата. Формулата се посочва там и се плъзга надолу, за да покрие целия диапазон, но накрая стойността от първата клетка просто се пренася в следващата. В този случай има няколко начина да поправите всичко:

Натиснете бутона F9, за да започнете преизчисляването на листа.
Променете формата на клетката на общ. За да направите това, изберете го и щракнете с десния бутон. Ще се отвори меню, където трябва да изберете „Формат на клетка“ и да промените на „Общи“.
Отворете опциите за изчисляване на формулата и проверете дали маркировките са същите като на екранната снимка.

Формулата не се брои

Има няколко причини, поради които формулата на Excel не се брои:

Невалиден формат на клетка. За да го промените, трябва да изберете желания диапазон от клетки и да изберете "Cell Format".

Активиран е режимът "Показване на формули". За да го изключите, отидете в секцията „Формули“ в горния панел, щракнете върху „Зависимости на формули“ и изключете „Показване на формули“.

Формулата или нейният синтаксис са въведени неправилно. В този случай формулата се показва вместо стойността. Трябва да го проверите внимателно.

Това са някои от основните причини, поради които Excel не брои формули.

Програмата не взема предвид формулата, въведена в клетката

Това често се случва, защото форматът на клетката е зададен на текст. Как да го промените на друг вече е описано по-рано.

Сумата не е калкулирана

Има два варианта защо приложението Excel не изчислява сумата:

Форматът на клетката е тестов.
Изчисляването на сумата е деактивирано. Задължително използване десен бутонкликнете с мишката върху специален панел и задайте знака на "Сума".

Ето защо сумата не се взема предвид.

Клетъчните данни не са сумирани правилно

Някои потребители се сблъскват, когато Excel неправилно изчислява сумата на избраните клетки. Често това се дължи на факта, че някои клетки или дори повечето от тях са в текстов формат. Поради това не всички данни са обобщени и резултатът е неправилен. Трябва да промените формата на „Общ“, за да разрешите проблема.

Деактивирано преизчисляване

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

Отворете настройките на програмата.
Там трябва да отидете в раздела "Формули". Най-отгоре ще има блок "Параметри на изчисленията", а там параметърът "Изчисления в книгата", където трябва да бъде избрана позицията "Автоматично". Ако не, задайте го правилно и щракнете върху OK.

Проблеми с формули в Microsoft Excel

Сега, ако някоя стойност бъде променена, резултатът ще бъде преизчислен.

От всичко това се вижда, че има много различни причини, поради които Excel изчислява формулата неправилно или изобщо отказва да ги изчисли. Ако потребителят има показана формула, където трябва да бъде резултатът, тогава форматът на клетката е зададен на текст или е направена грешка в самата формула. Режимът на преглед може да е активиран. Потребителите често забравят да поставят "=" преди началото на формулата. Ако стойностите се променят в свързани клетки, но резултатът остава същият, трябва да проверите дали е активирана автоматичната актуализация. В някои случаи се показва грешка в клетките с общи суми, тогава трябва да проверите всички стойности.

Читателите на Lifehacker вече са запознати Денис Батяновкойто сподели с нас. Днес Денис ще говори за това как да избегнем най-често срещаните проблеми с Excel, които често създаваме сами.

Незабавно направете резервация, че материалът на статията е предназначен за начинаещи потребители на Excel. Опитните потребители вече са танцували запалително на този рейк повече от веднъж, така че моята задача е да предпазя младите и неопитни „танцьори“ от това.

Не давате заглавки на колоните на таблицата

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

Празни колони и редове във вашите таблици

Това обърква Excel. Когато срещне празен ред или колона във вашата таблица, той започва да мисли, че имате 2 таблици, а не само една. Ще трябва постоянно да го коригирате. Освен това не крийте редовете/колоните, които не ви трябват в таблицата, по-добре е да ги изтриете.

Няколко таблици на един лист

Ако това не са малки таблици, съдържащи справочници със стойности, тогава това не си струва да се прави.

Ще ви бъде неудобно да работите пълноценно с повече от една таблица на лист. Например, ако едната таблица е отляво, а другата е отдясно, тогава филтрирането на една таблица ще засегне другата. Ако таблиците са разположени една под друга, тогава е невъзможно да се използва фиксирането на области, а също така една от таблиците ще трябва постоянно да се търси и да се извършват ненужни манипулации, за да се постави курсорът на таблицата върху нея. трябва ли ти

Данните от един и същи тип са изкуствено подредени в различни колони

Много често потребителите, които познават Excel повърхностно, предпочитат този формат на таблицата:

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

Факт е, че този формат съдържа 2 измерения: за да направите това, трябва да изберете линията, като сортирате клона, групата и агента. Когато намерите правилния ред, тогава ще трябва да потърсите желаната колона, тъй като има много от тях. И тази "двуизмерност" значително усложнява работата с такава таблица за стандартните инструменти на Excel - формули и обобщени таблици.

Ако създадете обобщена таблица, ще откриете, че не е възможно лесно да получите данни по година или тримесечие, тъй като индикаторите са разпределени в различни полета. Нямате едно поле за обем на продажбите, което може да се манипулира удобно, а 12 отделни полета. Ще трябва ръчно да създадете отделни изчисляеми полета за тримесечия и години, въпреки че ако всичко беше в една колона, обобщената таблица щеше да го направи вместо вас.

Ако искате да използвате стандартни формули за сумиране като SUMIF, SUMIFS, SUMPRODUCT, също ще откриете, че те няма да работят ефективно с това оформление на таблица.

Разделяне на информацията на различни листове на книгата "за удобство"

Друга често срещана грешка е, като имате някакъв стандартен формат на таблица и се нуждаете от анализи, базирани на тези данни, да ги публикувате на отделни листове книги на ексел. Например обичайно е да се създават отделни листове за всеки месец или година. В резултат на това количеството работа по анализ на данни всъщност се умножава по броя на създадените листове. Не е нужно да правите това. Натрупвайте информация на ЕДИН лист.

Информация в коментарите

Потребителите често добавят важна информация, от които може да се нуждаят, в коментар в клетка. Имайте предвид, че това, което е в коментарите, можете да видите само (ако го намерите). Трудно е да го вкараш в клетка. Препоръчвам, че е по-добре да отделите отделна колона за коментари.

Форматиране бъркотия

Определено няма да добави нищо добро към масата ви. Това изглежда отблъскващо за хората, които използват вашите електронни таблици. В най-добрия случай те няма да придадат значение на това, в най-лошия - ще си помислят, че не сте организирани и небрежни в бизнеса. Стремете се към следното:

Сливане на клетки

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

Комбиниране на текст и числа в една клетка

Болезнено впечатление прави клетка, съдържаща число, допълнено отзад с текстовата константа "RUB." или "USD", въведени ръчно. Особено ако това не е печатен формуляр, а обикновена таблица. Аритметичните операции с такива клетки са естествено невъзможни.

Числата като текст в клетка

Избягвайте да съхранявате числови данни в клетка в текстов формат. С течение на времето някои от клетките в такава колона ще имат текстов формат, а някои ще имат нормален. Поради това ще има проблеми с формулите.

Ако вашата маса ще бъде представена чрез LCD проектор

Изберете най-контрастните комбинации от цвят и фон. Тъмният фон и светлите букви изглеждат добре на проектора. Най-лошо впечатление прави червено върху черно и обратно. Тази комбинация изглежда изключително нисък контраст на проектора - избягвайте я.

Лист за режим на страница в Excel

Това е същият режим, в който Excel показва как ще бъде пагиниран листът при отпечатване. Границите на страниците са маркирани в синьо. Не препоръчвам постоянно да работите в този режим, което мнозина правят, тъй като драйверът на принтера участва в процеса на показване на данни на екрана и това, в зависимост от много причини (например, принтерът е свързан в мрежа и в момента е недостъпен), е изпълнен със замръзване в процеса на визуализация и преизчисляване на формули. Работете нормално.

Дори повече полезна информацияНаучете повече за Excel на

Как да изчислим сумата от стойности в Excel

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

Ако трябва да използвате получената сума някъде или просто да я покажете в клетка, ще използваме функцията SUM. Може да работи като ръчно управление, както и автоматичен. Изберете желаните клетки и щракнете върху бутона "Автосумиране", той се намира в раздела "Начало". Формулата с изчислението на сумата ще се появи в следващата клетка под тези, които сте избрали. За да вмъкнете сумата на произволно място, изберете всяка свободна клетка и въведете “=SUM(” без кавички в нея, след това изберете желания диапазон с мишката и натиснете Enter на клавиатурата.

Защо сумата може да не се разглежда в Excel

Има две най-вероятни причини.

1. Грешен десетичен разделител. Например в Windows има запетая, а в таблицата имате точка. Опитайте да замените разделителния знак за двойка стойности и ги добавете заедно. За да видите кой знак е в системата, отидете на "Контролен панел", изберете "Регионални и езикови опции" -> "Разширени опции":

2. Вашите клетки са в текстов формат и освен числа имат допълнителни символи, например интервали. Изберете ги и натиснете десния бутон на мишката. Изберете „Форматиране на клетки“ от менюто и задайте формата на „Число“, ако не е. Проверете дали в клетките няма нищо освен числа.

Ако имате въпроси или се нуждаете от разяснение - въпрос, нашите експерти ще отговорят своевременно на вашето запитване.

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

За съжаление това означава, че Excel не разбира какво се опитвате да направите, така че може би е по-добре да се откажете и да започнете отначало.

Първо щракнете върху бутона Добреили ключ ESCза да затворите съобщението за грешка.

Ще се върнете към клетката с грешна формула, която ще бъде в режим на редактиране и проблемната област ще бъде маркирана. Ако не знаете какво да правите с него и искате да започнете отначало, излезте от режима на редактиране, като натиснете отново клавиша ESCили бутон Отказв лентата с формули.

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

Виждате ли грешката с решетка (#)?

Excel показва различни хеш грешки (#), като #VALUE!, #REF!, #NUM, #N/A, #DIV/0!, #NAME? и #NULL!, за да посочите какво не работи правилно във формулата. Например #VALUE! грешката е причинена от лошо форматиране или неподдържани типове данни в аргументите. Освен това ще видите #REF! грешка, ако формулата се отнася до клетки, които са били изтрити или заменени с други данни. Съветите за отстраняване на неизправности ще бъдат различни за всяка грешка.

Забележка:# # # # не е грешка, свързана с формула. Това означава, че колоната не е достатъчно широка, за да покаже съдържанието на клетката. Просто плъзнете колона, за да я разгънете, или щракнете върху раздела начало > формат > ширина на колоната за автоматично приспособяване.

Прегледайте следните раздели за грешките, които виждате:

Всеки път, когато отворите електронна таблица с формули, които препращат към стойности в други електронни таблици, ще бъдете подканени да актуализирате връзките или да ги оставите такива, каквито са.

Excel показва горния диалогов прозорец, за да гарантира, че формулите в текущия работен лист винаги сочат към най-актуалните стойности в случай референтна стойностсе е променило. Можете да актуализирате връзките или да пропуснете тази стъпка. Дори ако изберете да не актуализирате връзките, можете да го направите ръчно по всяко време директно в електронната таблица.

Можете също така да изберете да не показвате този диалогов прозорец при отваряне на файл. За да направите това, отидете в раздела Файл > Опции > Разширени > Общии премахнете отметката Поискайте актуализиране на автоматични връзки.

Формула се връща синтактична конструкция, а не стойността

Ако формулата не върне стойност, използвайте инструкциите по-долу.

Формулата не е оценена

Ако формулата не изчислява, проверете дали функцията за автоматично изчисление е активирана в Excel. Формулите не се оценяват, ако ръчното изчисление е активирано. За да проверите дали е активиран автоматично изчисление, следвайте стъпките по-долу.

Допълнителна информацияЗа повече информация относно изчисленията вижте Промяна на преизчисляване на формула, итерация или прецизност.

Формулата съдържа една или повече кръгови препратки

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

За повече информация относно кръгови препратки вижте Премахване или разрешаване на кръгова препратка

Функцията започва ли със знак за равенство (=)?

Ако даден запис не започва със знак за равенство, той не се счита за формула и не се оценява (това е често срещана грешка).

Ако влезете SUM(A1:A10), Excel ще покаже текстовия низ SUM(A1:A10)вместо резултата от формулата. Освен това при влизане 11/2 Excel показва дата като 2-Nov или 02/11/2009 вместо да разделя 11 на 2.

За да избегнете тези неочаквани резултати, винаги започвайте формулата си със знак за равенство. Например въведете = SUM(A1:A10)и =11/2 .

Съвпадат ли отварящите и затварящите скоби?

Ако във формула се използва функция, важно е тя да работи правилно всяка отваряща скоба да има затваряща скоба, така че се уверете, че всяка скоба има двойка. Например формулата =АКО(B5<0);"Недопустимо";B5*1,05) няма да работи, защото има две затварящи и само една отваряща скоба. Правилната версия на тази формула е следната: =АКО(B5<0;"Недопустимо";B5*1,05) .

Синтаксисът включва ли всички необходими аргументи?

Функциите в Excel имат аргументи - стойности, които трябва да бъдат предоставени, за да може функцията да работи. Само малък брой функции работят без аргументи (например PI или TODAY). Проверете синтаксиса на формулата, който се показва, когато започнете да въвеждате функцията, за да се уверите, че всички необходими аргументи са включени.

Ако трябва бързо да прегледате синтаксиса на конкретна функция, вижте списъка с функции на Excel (по категории).

Има ли неформатирани числа във формулите?

Не въвеждайте във формули числа, форматирани със знаци за долар ($) и десетични разделители (,), тъй като знаците за долар означават абсолютни препратки, а запетаите са разделители на аргументи. Вместо $1,000 Във формулата трябва да въведете 1000.

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

Забележка:Резултатът от формула може да бъде форматиран с помощта на десетични разделители и валутни символи следвъвеждане на формула с неформатирани числа (константи). Обикновено не се препоръчва да използвате константи директно във формули: те могат да бъдат трудни за намиране, ако трябва да актуализирате стойности, и често са грешно написани. Много по-удобно е да поставите константи в отделни клетки, където те ще бъдат достъпни и лесни за справка.

Формулата може да не върне очакваните резултати, ако типът данни на клетката не е подходящ за изчислението. Например, ако въведете простата формула =2+3 в клетка, която е в текстов формат, Excel няма да може да изчисли въведените данни. Клетката ще покаже низа =2+3 . За да коригирате тази грешка, променете типа данни на клетката от Текстна Общкакто е описано по-долу.

Изберете клетка.

В раздела У домащракнете върху стрелката до списъка Числен формат(или щракнете CTRL+1) и изберете Общ.

Натиснете клавиш F2за да влезете в режим на редактиране и след това натиснете клавиша ENTERза валидиране на формулата.

Ако въведете дата в клетка, която има числовитип данни, той може да се покаже като числова стойност, а не като дата. За да се покаже това число като дата, в колекцията Числен форматизберете формат датата.

Опитвате ли се да умножите, без да използвате знака *?

Кръстът често се използва като оператор за умножение във формула ( х), но Excel трябва да използва звездичка (*) за тази цел. Ако използвате знака "x" във формула, ще се появи съобщение за грешка и ще бъдете подканени да коригирате формулата, като замените хна "*".

Липсват ли кавички около текста във формулите?

Ако формулата съдържа текст, той трябва да бъде поставен в кавички.

Например формулата ="Днес" & ТЕКСТ(ДНЕС();"дддд, дд.ММ")свързва текстовия низ "Днес" с резултатите от функциите TEXT и TODAY и връща резултат, подобен на следния: Днес е понеделник, 30.05.

Във формулата има интервал в полето „днес“ преди затварящите кавички, за да се осигури бяло пространство между думите „днес“ и „понеделник, 30 май“. Ако около текста няма кавички, във формулата може да се появи #ИМЕ?. грешка

Формулата включва ли повече от 64 функции?

Имената на листовете оградени ли са с апостроф?

Например, за да върнете стойността от клетка D3 на тримесечния лист с данни в работна книга, въведете: = "тримесечни данни"! D3. Без кавичките около името на листа, формулата показва #ИМЕ? грешка

Можете също да щракнете върху стойности или клетки в друг лист, за да ги посочите във формула. Excel автоматично ще огради имената на листовете в кавички.

Ако формулата препраща към външна работна книга, правилно ли е указан пътят на работната книга?

Например, за да се обърнете към клетки A1 - A8 на листа Продажби в извлечението Q2, отворено в Excel, въведете: = Разпродажби! A1: A8. Ако във формулата няма квадратни скоби, #REF! .

Ако работната книга не е отворена в Excel, въведете пълния път до файла.

Например =ROWS("C:\Моите документи\[Q2 Operations.xlsx]Продажби"!A1:A8).

Забележка:Ако пълният път съдържа интервали, трябва да го поставите в апостроф (в началото на пътя и след името на книгата преди удивителния знак).

съвет:За да получите пътя до друга работна книга, най-лесният начин е да я отворите, да въведете знак за равенство (=) в оригиналната работна книга и след това да използвате клавишите ALT+TABотидете на втората книга и изберете произволна клетка от желания лист. След това изходната работна книга може да бъде затворена. Формулата ще се актуализира автоматично, за да покаже пълния път до името на листа с правилния синтаксис. Този път може да бъде копиран и поставен, ако е необходимо.

Опитвате ли се да разделите числови стойности на нула?

Разделянето на клетка с друга клетка, която има стойност нула (0) или никаква стойност, води до #DIV или 0! Грешка.

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

IF(B1;A1/B1;0)

Значението на тази формула е: АКО B1 съществува, върнете резултата от разделянето на A1 на B1, в противен случай върнете 0.

Формулата отнася ли се за отдалечени данни?

Преди да изтриете данни в клетки, диапазони, конкретни имена, работни листове и работни книги, винаги проверявайте дали имате формули, които се отнасят до тях. Можете да замените формули с техните резултати, преди да изтриете референтните данни.

Ако имате проблеми със замяната на формули с техните резултати, прочетете за грешките и възможните решения:

Ако формулата препраща към клетки, които са били изтрити или заменени с други данни и връща #REF! Маркирайте клетката с #REF! Ако посочената от вас позиция е преди първия или след последния елемент в полето, формулата връща грешка #REF!. В лентата с формули щракнете върху #REF! и го изтрийте. След това въведете отново диапазона за формулата.

Ако конкретно име липсва и формулата, която се отнася до това име, връща #NAME? грешка Дефинирайте ново име, което се отнася до желания диапазон, или променете формулата, за да се отнася директно към диапазона от клетки (например a2:D8).

Ако листът липсва и формулата, отнасяща се до него, връща #REF! Грешка: Този проблем не може да бъде коригиран, за съжаление изтритият лист не може да бъде възстановен.

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

Например, ако се използва формулата =[Книга1.xlsx]Лист1"!A1и работната книга вече не съществува, стойностите, които я препращат, ще продължат да бъдат налични. Но ако редактирате и запишете формула, която препраща към тази работна книга, ще се появи диалогов прозорец Актуализиране на стойностиподканвайки ви да въведете име на файл. Щракнете върху бутона Откази пазете данните в безопасност, като замените формулата, която препраща към липсващата работна книга, с нейните резултати.