Работата с формули в Excel може да бъде доста трудна., особено когато се сблъскваме с мистериозни съобщения за грешки или резултати, които просто не си съвпадат. Ако някога сте се сблъсквали със странни символи, цифри или загадъчни предупреждения като #DIV/0!, #VALUE! или #REF!, не се притеснявайте: това е много често срещано и има решение.
Тази статия е предназначена да ви предложи пълно и практично ръководство как да откривате, разбирате и коригирате най-често срещаните грешки, които се появяват при работа с формули в Excel.
Основни грешки във формулите в Excel и как да ги идентифицираме
Когато пишете формули в Excel, Важно е да се обърне внимание на синтаксиса и използваните типове данни, тъй като повечето грешки възникват от малки пропуски или непознаване на основните правила на функциите.
Някои от най-често срещаните повреди Те възникват, когато знакът за равенство не се използва за начало на формулата, скобите са пропуснати, двоеточията са използвани неправилно за диапазони от клетки или са избрани неправилни аргументи за функция, наред с други.
- Винаги започвайте формулите с '='Ако не го направите, Excel ще интерпретира въведеното от вас като текст или дори дата. Например, „SUM(A1:A10)“ ще се покаже както е, а „11/2“ може да се покаже като 2 ноември.
- Проверете скобитеТрябва да отваряте и затваряте правилно всяка скоба във функциите. Ако пропуснете някоя или отидете твърде далеч, формулата ще бъде невалидна. Неправилен пример: =IF(B5<0);"Невалиден";B5*1,05)
- Използвайте двоеточия („:“) за диапазониТова е жизненоважно, за да може Excel да разбере диапазона от клетки. Пишете =SUM(A1:A5), никога =SUM(A1:A5), което би довело до грешка #NULL!.
- Уверете се, че сте записали всички необходими аргументиВсяка функция изисква точен брой аргументи, нито повече, нито по-малко, и от правилния тип (число, текст и т.н.).
Освен това има малки детайли, които често остават незабелязани., като например ограничаване на влагането до 64 функции, правилно използване на кавички при именуване на листове с интервали или специални символи и използване на удивителен знак (!), когато формула извиква друг лист. Ако работите с външни работни книги, пътят и името трябва да бъдат правилно посочени и когато пишете числа, избягвайте формати (като запетаи или символи за евро), ограничавайки се до чистата числова стойност.
8-те най-често срещани грешки и как да ги решим
Excel показва различни кодове за грешки В зависимост от проблема, възникнал във формулата. Всяко съобщение показва различна причина и има свое собствено решение. По-долу разглеждаме най-често срещаните и възможните им корекции.
# N / A
значение: Не е наличен. Възниква, когато функция за търсене не намери заявената стойностТипично е при VLOOKUP, HLOOKUP, MATCH или SEARCH, когато стойността не съществува в търсения диапазон.
Решение: Проверете дали посочените диапазони са правилни, дали информацията е там, където трябва да бъде, и дали не е била изтрита. Ако тази грешка трябва да се появи, защото данните всъщност не съществуват, можете да използвате функция за обработка на грешки, като IFERROR, за да покажете по-полезен текст, например: =SI.ERROR(BUSCARV(A10;A3:B5;2;0);»Valor no encontrado»).
#ЗАСЛУЖАВА СИ!
значение: Невалидна стойност. Тази грешка показва, че формулата взаимодейства с клетки или типове данни, които не се поддържат.Често се появява, когато се използва текст, където са необходими числа или обратното, или когато има празни клетки или неочаквани символи.
Решение: Проверете дали всички аргументи са от правилния тип (ако се очаква число или текст) и дали в диапазона няма интервали или необичайни символи. Използвайте инструмента за проследяване на предшественици, за да откриете откъде идва грешката.
#REF!
значение: Невалидна препратка. Това се случва, когато формула сочи към изтрита клетка или такава, която е пропусната извън листа поради случайно изрязване или поставяне.
Решение: В идеалния случай трябва да отмените действието, което е изтрило клетката, или да промените формулата, така че да сочи към валидни препратки. Можете също така да коригирате диапазона във функциите за търсене, за да съответства на броя на наличните колони.
Например, формула =VLOOKUP(A8;A2:D5,5;XNUMX;FALSE) ще даде грешка, тъй като диапазонът A2:D5 има само 4 колони; трябва да бъде =VLOOKUP(A8;A2:E5,5;XNUMX;FALSE) или коригирайте индекса на колоната.
#ИМЕ?
значение: Excel не разпознава текста, използван във формулата, като валидно име на функция или като известна препратка към клетка.
Чести причини: Печатна грешка в името на функцията или липсващо определение на именуван диапазон. Пример: "=average(A1:A5)" вместо "=average(A1:A5)".
Решение: Проверете дали функцията е написана правилно и използвайте опцията за вмъкване на функция от раздела с формули, за да избегнете грешки при писане.
#DIV/0!
значение: Опитвате се да разделите дадено число на нула, което математически не е възможно.
Решение: Преди деление използвайте функция SI за да проверите дали делителът е нула. Например: =АКО(B3=0;»»;A3/B3)Това ще предотврати появата на грешката и ще ви позволи да покажете алтернативен резултат или празна клетка.
#####
значение: Клетката не е достатъчно широка, за да покаже съдържанието си. Това не е грешка във формулата, а грешка във форматирането.
Решение: Регулирайте ширината на колоната, като щракнете двукратно върху десния край на заглавката или изберете бутона „Избери всички“ и преоразмерите целия лист, ако е необходимо.
#НУЛА!
значение: Препратено е към несъществуващ или лошо дефиниран диапазон, обикновено защото е използван неправилен оператор.
Решение: Винаги използвайте двоеточие ':', за да обозначите диапазони (например A2:C2) и точка и запетая ';', за да съедините няколко диапазона във функция, например: =SUM(A2:A6;D2:D6).
#¡NUM!
значение: Невалидно число, обикновено поради невъзможни изчисления или резултати извън ограниченията, които Excel може да обработи.
причинява: Изчислете квадратен корен от отрицателно число или поискайте резултат, толкова голям или малък, че да е извън разрешения диапазон (между -1×10307 и 1×10307).
Решение: Прегледайте стойностите, използвани във формулата, уверете се, че не работите с нелогични данни и ако използвате итеративни функции като IRR или RATE, коригирайте опциите за итерация в усъвършенствани инструменти за анализ.
Как да избегнем грешки при писане на сложни формули
Много проблеми могат да бъдат предотвратени, като се следват редица препоръки при създаване на формули в Excel:
- Винаги проверявайте броя на скобите и че те са правилно съчетани. Практичен метод е да се преброят началните и крайните числа, преди да се финализира формулата.
- Поддържане на логиката на влаганеВлагането на повече от 64 функции не трябва да се допуска, тъй като Excel няма да го позволи и ще доведе до грешки, които са трудни за проследяване.
- Правилно препращане към клетки между листове и работни книгиАко името на даден лист съдържа интервали или небуквени символи, винаги го оградете в единични кавички („“). Също така, когато правите препратки към външни работни книги, използвайте формата и добавете пътя, ако не е отворена.
- Не включвайте форматиране в числатаАко искате да покажете символа за евро, приложете го към клетката след това, а не във формулата.
Инструменти и трикове на Excel за бързо отстраняване на грешки
Excel има няколко функции и помощни програми, които помагат за откриване и коригиране на грешки във формули или данни в електронни таблици. Познаването им може да ви спести много време и подобрете надеждността на вашите електронни таблици.
- Проверка на грешкаОт раздела „Формули“ можете да получите достъп до тази функция, която анализира формулата и обяснява възможни грешки или решения за тяхното отстраняване. Това е особено полезно за съобщения като #NAME?
- Проследяване на предшественици и зависими лица: Този инструмент ви позволява да покажете със стрелки кои клетки са част от изчислението, което помага да се локализира източникът на грешката #¡VALUE! или други подобни грешки.
- Автоматично попълване на формулиАко има несъответствия между клетките, изберете проблемната формула и, като държите натиснат клавиша SHIFT, разширете селекцията и след това я коригирайте нагоре, надолу, надясно или наляво, ако е необходимо, като използвате клавишни комбинации или опции в менюто „Запълване“.
Практически случаи и често срещани ситуации
За да разберем по-добре грешките във формулите, нека разгледаме някои примери и полезни трикове, които можете да приложите.
- Поправка на грешка #####: Когато въведете дълга дата или голямо число в тясна клетка, съдържанието ще се появи с няколко диез-таза. Разгънете колоната и данните ще бъдат видими.
- #REF! поради недостатъчен обхват: Ако използвате функции като VLOOKUP и заявите колона извън диапазона, коригирайте диапазона или индекса, за да съответстват.
- Обработка на #DIV/0!: Преди деление, използвайте условие, за да проверите дали делителът е нула. По този начин избягвате грешката и контролирате показания резултат.
Основен е че грешките в Excel се коригират своевременно, тъй като те могат да повлияят на зависимите изчисления и да разпространят неправилни резултати в цялата електронна таблица.