10 възможни грешки във функциите на Excel и техните решения

Ето как автоматично да откриете тези грешки в Excel

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

Това са 10 често срещани грешки във функциите на Excel

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

1. #ИМЕ?

Възникват грешки при въвеждане - ако случаят е такъв във формулата, вместо резултата се появява съобщението за грешка #NAME? Няма смисъл да скривате грешката с функция като IFERROR. Трябва да го коригирате. Всичко, което трябва да направите, е да разгледате внимателно името, което сте дали на формулата. Има ли усукана буква? Забравили ли сте писмо или сте го въвели два пъти? Например, поправете = SUME (A3: A16) до = SUM (A3: A16) и ще получите правилния резултат.

Използвайте съветника за формули

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

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

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

Съветникът за функции ще ви помогне при влизане във функцията. Ако маркирате клетката с формулата и изберете „Вмъкване на функция“ в раздела „Формула“, Excel извиква съветника. Отделните аргументи се показват тук - както и дали има грешка.

2. #НУЛЯ!

Това съобщение за грешка може да показва две неща:

• Във формула сте посочили неправилен оператор на диапазон.
• Използвате оператор на пресичане на две не припокриващи се области.

В първия случай визирате непрекъснат диапазон от клетки във формула - например клетки В4 до В12. Използвате двоеточие като оператор на обхват. Например, ако искате да изчислите сума, правилната формула е = SUM (B4: B12). Ако визирате две области без припокриване, точката с запетая е подходящият оператор. Ако искате да изчислите сумата от областите B4 до B12 и C9 до C23, тази формула е правилна: = SUM (B4: B12; C9: C23).

Във втория случай искате да работите с припокриващи се диапазони от клетки, но посочените два диапазона не се припокриват. Например получавате грешката за формулата = CELL ("Адрес" (B4: B12 D4: D6)). Няма припокриване между двете области. Ако промените областите така, че да се припокриват - например като = CELL ("Адрес" (B4: B12 B5: D5)) - точката на пресичане на двете области се показва като резултат. В този случай това е клетка В5.

3. #РЕФЕРЕНЦИЯ!

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

Например имате електронна таблица на Excel с редове 1, 2 и 3 и колони A, B и C. Формулата е = SUM (A2; B2; C2). Сега изтрийте ред 2. Вместо резултата, ще видите стойността на грешката #REFER, защото една от стойностите за изчислението не съществува.

Ако случайно сте изтрили частта, която липсва за изчислението, можете да коригирате грешката си директно с командата „Отмяна“. Ако подобластта е правилно изтрита, преформулирайте формулата. Ако въведете = SUM (A2: C2), ще бъде показан правилният резултат, защото Excel просто премахва изтритата втора колона.

4. #ЦЕННОСТ!

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

Тъй като има много потенциални източници на грешка за #VALUE! има много начини да го поправите. Следните решения могат да помогнат:

• Можете да използвате функции вместо математически оператори. Така че вместо = B4 + B5 + B6 въведете функцията = SUM (B4: B6).
• Проверете въпросните клетки за специални символи. Ако е необходимо, използвайте функцията ISTTEXT в отделна колона за това. Това ви показва в коя клетка се намира грешката.
• Може би стойността на грешката се дължи на интервали в клетка, която се появява във функцията. За да разберете, маркирайте съответните клетки. След това отидете на „Търсене и избор“ под „Старт“ и кликнете върху „Замяна“. Въведете интервал за „Търсене“ и нищо за „Замяна с“. Сега можете да замените нежеланите пространства.
• Скритите знаци също могат да доведат до тази често срещана грешка във функциите на Excel. За да ги премахнете, отидете на „Старт“ и „Сортиране и филтриране“ на „Филтър“. Под стрелката на филтъра деактивирайте „Избиране на всички“ и маркирайте „Празни клетки“ и всички позиции, където няма нищо. Ако натиснете OK, Excel ще ви покаже всички предполагаемо празни клетки, които съдържат скрити символи. Маркирайте го и натиснете „Премахване“. Изтрийте отново филтъра.

5. #####

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

6. # DIV / 0!

Да предположим например, че въвеждате функция, която искате да разделите няколко стойности на други стойности. Тогава може да се случи, че в една от клетките има нула, чиито стойности искате да разделите на - или изобщо нищо. В този случай стойността на грешка # DIV / 0! Изглежда показва, че разделянето с нула не е разрешено.

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

7. #NV!

С тази стойност на грешка Excel показва, че това, което търсите, не може да бъде намерено. Например, ако сте присвоили стойности на определени термини и искате да ги покажете, но един от термините липсва, това съобщение за грешка се появява вместо резултата. Това често се прави заедно с формулите LOOKUP, HLOOKUP, MATCH или VLOOKUP. Например, можете да показвате грешки с VLOOKUP, защото ви помага да сравнявате таблици една с друга или да ги обобщавате.

Пример: Създали сте таблица със съответните цени за винтове, куки, щифтове, болтове, гайки и т.н. и сте забравили едно от условията. Програмата не го намира. Съответно не може да има цена, само #NV! посочи. Решавате този проблем, като добавите термина. Като алтернатива можете да потиснете показването на грешки с формулата IFERROR (вижте по -долу).

8. #НОМЕР

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

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

Как да го направим:

1. Под „Файл“ и „Опции“ изберете „Формули“. Тук под „Опции за изчисление“ поставете отметка в квадратчето до „Активиране на итеративно изчисление“.

2. Под „Максимален брой итерации“ можете да посочите броя на изчисленията, които Excel трябва да извърши. Колкото по -голямо е това число, толкова по -дълго ще отнеме да се изчисли.

3. В полето "Максимална промяна" можете да посочите колко може да бъде сумата на промяната между два резултата от фактурата.

В редки случаи също може да се случи, че резултатът от формула е твърде голям или твърде малък за изчисление в Excel. За това обаче резултатът трябва да бъде под -1x10 (високо) 307 или над 1x10 (високо) 307. В този случай трябва да редактирате формулите, така че резултатът да е в диапазон, който Excel може да изчисли.

9. Кръгова справка

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

Как да разрешите грешката:

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

2. Ако искате да намерите неоткрити кръгови препратки, отидете на „Формули“, „Мониторинг на формули“ и „Кръгови препратки“. Всички кръгови препратки в документа вече се показват и можете да ги разрешите както в първия случай.

10. Е +

E + не е типична грешка във функциите на Excel, но все пак създава объркване за потребителите от време на време. Ако въведете много голям брой в една от клетките, Excel може да го съкрати. Тогава E + влиза в игра. Например 26500000000000000 в клетката на Excel става 2.65E + 14. Това е експоненциален формат. Excel го използва, за да може да показва дори големи числа, така че да са напълно видими в клетките.

По този начин можете да показвате числата изцяло

Ако искате числата да се показват изцяло, са необходими няколко прости стъпки:

1. Маркирайте съответните позиции в списъка си.

2. Натиснете Ctrl и 1, за да можете да форматирате клетките.

3. В диалоговия прозорец, който се отваря, изберете раздела „Номера“ и категорията „Номер“.

4. Въведете големия брой и изберете колко десетични знака искате да се покаже и дали искате да се показва разделител на хиляди.

5. След като щракнете върху OK, Excel ще ви покаже изцяло номера в таблицата.

Автоматично откриване на типични грешки във функциите на Excel

Имате възможност да активирате автоматичната проверка на Excel. Ако все още не е включен за вас, са достатъчни няколко кликвания:

Под „Файл“ отидете на „Опции“.

Изберете „Формули“.

Поставете отметка в квадратчето до „Активиране на проверката на грешки във фонов режим“.

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

Тези функции идентифицират грешки във функциите на Excel

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

ГРЕШКА

Някои формули може да не са пълни, както стоят нещата. Искате ли да предотвратите показването на грешки? Функцията IFERROR е подходяща за това. Той оценява типовете грешки #NV, #VALUE!, #REFERENCE!, #DIV / 0!, #NUMBER!, #NAME? или #NULL! край. Той има проста структура, защото съдържа само стойността и value_if_error. Първата стойност проверява функцията за коректност. Програмата трябва да покаже последната стойност в резултата, ако има грешка. Функцията е много полезна, когато редактирате таблица за дълго време.

Пример:

Въвеждате = IFERROR (B3 * C3; "Enter values") или = IFERROR (B3 * C3; ""). В първия случай ще ви бъде напомнено какво все още трябва да направите (ако има грешка) с текста „Добавяне на стойности“. Във втория случай клетката, която обикновено съдържа резултата, просто остава празна.

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

ГРЕШКА

Във връзка с функцията IFERROR функцията ISERROR може също да показва съществуващи грешки. По този начин те не пречат на по -нататъшни изчисления в списъка. За да направите това, въведете например: = IF (ISERROR (B3); "моля проверете"; B3 / 6). Ако изчислението може да се извърши, ще получите резултата. Ако Excel открие грешка при проверка на B3, думите „моля проверете“ се появяват в съответната клетка.

Заключение: Поправете грешките във функциите на Excel бързо и лесно

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