Начини за избягване на повредени формули

Начини за избягване на повредени формули

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

Изображение на диалоговия прозорец "Проблем с тази формула" на Excel

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

Започнете, като изберете OK или натиснете ESC , за да затворите съобщението за грешка.

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

Изображение на бутона "Отказ" на лентата за формули

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

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

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

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

Изображение на "Начало > Формат > Автопобиране на ширината на колона"

Вижте някоя от следните теми, които отговарят на грешката в паунд, която виждате:

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

Диалогов прозорец за повредени препратки в Excel

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

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

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

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

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

  • Уверете се, че Excel е настроен да показва формули във вашата електронна таблица. За да направите това, изберете раздела формули и в групата проверка на формули изберете Показвай формулите.

    Съвет: Можете също да използвате клавишната комбинация Ctrl + ' (клавишът над клавиша TAB). Когато направите това, колоните ще се разширяват автоматично, за да показват формулите ви, но не се тревожете, когато превключвате обратно към нормален изглед, колоните ви ще се преоразмеряват.

  • Ако стъпката по-горе все още не реши проблема, е възможно клетката да е форматирана като текст. Можете да щракнете с десния бутон върху клетката и да изберете Форматиране на клетки > Общи (или Ctrl + 1), след което да натиснете F2 > Enter, за да промените формата.

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

    Изображение на диалоговия прозорец "Данни > Текст в колони"

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

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

  2. В раздела Опции за изчисления под Изчисляване на работната книга се уверете, че е избрана опцията Автоматично.

    Изображение на опциите за автоматично и ръчно изчисляване

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

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

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

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

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

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

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

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

Например функцията UPPER приема само един текстов низ или една препратка към клетка като свой аргумент: =UPPER("здравей") или =UPPER(C2)

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

Екранна снимка на лентата с инструменти за справки за функциите

Също така някои функции, като например SUM, изискват само числови аргументи, докато други функции, като например REPLACE, изискват текстова стойност за поне един от своите аргументи. Ако използвате грешен тип данни, функциите могат да върнат неочаквани резултати или да покажат грешка #VALUE! .

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

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

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

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

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

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

  2. Изберете Начало и изберете стрелката, за да разгънете групата число или формат на числата (или натиснете Ctrl + 1). След това изберете Общи.

  3. Натиснете F2, за да поставите клетката в режим на редактиране, и натиснете Enter, за да приемете формулата.

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

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

Прозорец за съобщения с молба да се замести x с * за умножение

Ако обаче използвате препратки към клетки, Excel ще върне #NAME? грешка.

#NAME? грешка, когато се използва x с препратки към клетки вместо * за умножение

Ако създадете формула, която включва текст, поставете текста в кавички.

Например формулата ="Днес е " & TEXT(TODAY();"дддд, дд.мммм") комбинира текста "Днес е " с резултатите от функциите TEXT и TODAY и връща нещо от рода на Днес е понеделник, 30 май в клетката.

Във формулата "днес е" има интервал преди крайната кавичка, за да се осигури празното място, което искате, между думите "днес е" и "понеделник, 30 май". Без кавички около текста формулата може да покаже грешката #NAME?.

Можете да комбинирате (или вложите) до 64 нива на функции в дадена формула.

Например формулата = if (SQRT (PI ()) <2, "по-малко от две!"; "повече от две!") има три нива на функции; функцията PI е вложена във функцията SQRT, която от своя страна е вложена във функцията IF.

Когато въвеждате препратка към стойности или клетки в друг работен лист и името на този лист съдържа знак, който не е буква (например интервал), оградете името с единични кавички (').

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

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

Когато въведете препратка към стойности или клетки в друга работна книга, включете името на работната книга, заградено с квадратни скоби ([]), последвано от името на работния лист, съдържащ стойностите или клетките.

Например за да препратите към клетките от a1 до A8 в листа за продажби в работната книга на Q2, която е отворена в Excel, въведете: = [Q2 Operations.xlsx] Sales! A1: A8. Без квадратните скоби формулата показва грешката #REF!.

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

Например =ROWS('C:\Моите документи\Операции през тримесечие 2.xlsx]Продажби!'A1:A8).

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

Съвет: Най-лесният начин да получите път към другата работна книга е да отворите другата работна книга, а след това от вашата първоначална работна книга въведете = и използвайте Alt + Tab , за да преминете към другата работна книга. Изберете произволна клетка в листа, която искате, и след това затворете работната книга източник. Вашата формула ще се актуализира автоматично, за да се покаже пълният път към файла и името на листа, заедно с необходимия синтаксис. Можете дори да копирате и поставите пътя и да го използвате, когато ви потрябва.

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

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

=IF(B1;A1/B1;0)

Което означава, че АКО(B1 съществува, тогава A1 трябва да се раздели на B1, а в противен случай да върне 0).

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

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

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

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

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

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

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

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

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

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

  1. В работен лист изберете клетките, съдържащи стойностите на резултата от формулата, която искате да копирате.

  2. В раздела Начало , в групата клипборд изберете Копирай Изображение на бутон .

    Изображение на лентата на Excel

    Клавишна комбинация: Натиснете CTRL+C.

  3. Изберете горната лява клетка на област за поставяне.

    Съвет: За да прехвърлите или копирате селекция в различен работен лист или работна книга, изберете друг раздел на работен лист или превключете на друга работна книга, след което изберете горната лява клетка на областта за поставяне.

  4. В раздела Начало , в групата клипборд изберете поставяне на Изображение на бутон , след което изберете поставяне на стойностиили натиснете Alt > E > S > V , за да въведете Windows, или изберете командата > за управление, за да > въведете на Mac.

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

  1. Изберете формулата, която искате да изчислите.

  2. Изберете формули > изчисляване на формула.

    Група "Проверка на формули" в раздела "Формула"

  3. Изберете оценка , за да изследвате стойността на подчертаната препратка. Резултатът от изчислението се показва с курсив.

    Диалогов прозорец "Изчисляване на формула"

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

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

  5. Продължете, докато всяка част от формулата бъде изчислена.

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

    Забележки: 

    • Някои части от функциите IF и CHOOSE няма да се изчислят и може да се покаже грешка #N/A в полето Изчисляване.

    • Празните препратки се показват като нулеви стойности (0) в полето Изчисляване.

    • Някои функции се преизчисляват всеки път, когато работният лист се промени. Тези функции, включително функциите RAND, AREAS, INDEX, OFFSET, CELL, INDIRECT, ROWS, COLUMNS, NOW, TODAY и RANDBETWEEN, могат да доведат до показване на резултати в диалоговия прозорец Изчисляване на формула, които са различни от действителните резултати в клетката в работния лист.

Имате нужда от още помощ?

Винаги можете да попитате експерт в техническата общност на excel или да получите поддръжка в Общността за отговори от.

Вж. също

Общ преглед на формулите в Excel

Откриване на грешки във формули

Функции на Excel (по азбучен ред)

Функции на Excel (по категории)

Нуждаете се от още помощ?

Разширете уменията си в Office
Преглед на обучението
Получавайте първи новите функции
Присъединете се към участниците в Office Insider

Беше ли полезна тази информация?

Благодарим ви за вашата обратна връзка. Изглежда, че ще бъде полезно да ви свържем с един от нашите агенти по поддръжката на Office.

×