Наскоро ме попитаха как бързо и лесно могат да се намерят всички клетки с грешки в Excel файл. Това е един сравнително често сещан проблем за потребителите на Ексел. И обикновено решението, което се практикува е … ръчно обхождане и търсене на грешките една по една. Ясно е, че така е може да бъде много неефективно, особено ако клетките с формули са много и в много шийтове. В тази статия ще ви предложа три метода, с които можете бързо и лесно да откривате всички клетки с грешки в Excel.
Метод 1: CTRL+F
- Започваме с добре позната клавишна комбинация CTRL+F (Find)
- Да предположим, че търсим клетки с грешка #REF!. Тогава в менюто Find срещу Find what напишете #REF!
3. В Options изберете дали да търси в целия файл (Workbook) или само в текущия шийт (Sheet)
4. Препоръчвам в Look in да изберете Values. Така Ексел ще търси в резултата от формулата в клетката и ще обхване всички грешки
5. Давате Find All и излиза списък с всички клетки, в които има грешка от типа #REF!
6. В случая – има две такива в Sheet1 и една в Sheet2. Както знаете от статията за трикове с Find & Replace, с кликане върху някой от редовете отиваме директно в съответната клетка
След като намерим всички грешки от типа #REF!, можем да повторим процедурата за #DIV/0!, #N/A и т.н. грешки.
А, ако искаме да намерим всички грешки във файла, а не да ги търсим вид по вид? Тогава можем да използваме търсене с wild cards. В таблицата по-долу са посочени най-често срещаните грешки в Excel и на какво се дължат.
Лесно се забелязва, че те следват един стандартен модел на изписване – всички започват със знака #, след който следват поне три символа.
Тогава можем с FIND да търсим следния стринг: #???*
Както виждате, Ексел намери всички клетки с грешки.
Но трябва да се внимава, защото някъде в данните може да имаме съвсем валиден запис, който прилича на грешка, но не е грешка. Например някакъв код да е записан така: #PLU123
Този метод е ефективен и лесен, но има един съществен недостатък – можем да обхождаме клетките една по една. Ако са 5-10, както в примерния файл, това не е проблем, но ако са десетки и стотици, няма да е удобно. Друг недостатък е, че пропуска клетки, които са в скрити редове или колони.
Метод2: CTRL+G
- При този метод използваме менюто Go To, което се появява след натискане на CTRL+G или F5
2. Натискаме Special и се появява ново меню, където избираме както е показано – Formulas и отметка на Errors
3. След като натиснем ОК – Ексел маркира всички клетки с грешки
4. Сега без да местим курсора, можем да оцветим клетките с грешки в някакъв цвят, например опушен лешник, за да се отличават. Можем и просто да натиснем Delete, за да ги изтрием или да направим някакво друго действие с тях
Вторият метод е удобен, когато искаме да маркираме всички клетки с грешки в Excel едновременно. Той също така не пропуска скритите клетки, а маркира и тях.
За сметка на това работи само в един шийт и маркира всички грешки без значение от вида им, т.е. не можем да намерим само грешки от типа #REF!. Или всичко, или нищо.
Метод3: Error Checking Tool
Ексел си има вграден инструмент за намиране и коригиране на грешки. Намира се в лентата с менюта и по-точно Formulas, секция Formula Auditing
Когато натиснем този бутон се появява меню, което предлага пълен или както е модерно да се нарича сега 360o поглед върху грешките в даден шийт.
Това меню е разделено условно на три секции. В ляво има две полета с информация:
Горното ни казва в коя клетка е грешката (в случая С6) и изписва формулата (в случая =A6/#REF!).
В долното поле Ексел ни пояснява на какво се дължи грешката. Обясненията не са много по-различни, от тези, които са посочени в таблицата с грешки в Excel по-горе, но е хубаво, че не се налага да ги помним.
Втората част от менюто Error Checking съдържа няколко бутона за допълнителни действия.
Първият бутон е Help on this Error. При натискането му Ексел автоматично се свързва с интернет (ако имате връзка, разбира се) и отваря допълнителен прозорец с указания как да поправите грешката.
Вторият бутон е Show Calculation Steps… Когато го натиснем, се отваря менюто Evaluate Formula.
В него с натискане на бутона Evaluate се преминава през всички стъпки, които изпълнява формулата. В случая то не ни е много от полза, защото формулата е доволно проста – дели едно число на друго. Но ако имаме някоя сложна формула с вложени функции, изпълнявайки формулата стъпка по стъпка можем да видим къде точно се появява грешката (или както му казваме “Формулата се чупи“).
С Ignore Error просто прескачаме грешката в тази клетка и отиваме на следващата. А с Edit in Formula Bar отиваме в полето за писане на формули, за да коригираме грешката ръчно.
Третата част от менюто съдържа три бутона за навигация.
Options извиква менюто с опции за формули (File -> Options -> Formulas), където можем да въведем някои настройки относно формулите.
А с Previous и Next преминаваме към предишната или следваща клетка с грешка в шийта.
Когато обходим всички клетки с грешки, Ексел извежда съобщение, че проверката на грешките в съответния шийт е приключила:
Остава ни само да натиснем ОК и да преминем на следващия шийт.
Основното предимство на този метод е, че няма как да пропуснем грешка в Excel. Той систематично обхожда всички клетки с грешки една по една, дори и да са в скрити редове или колони. Освен това имаме доста възможности да установим каква е грешката и как да я поправим.
Недостатък е, че трябва да минаваме грешките една по една и не можем да изберем типа грешка (примерно само #REF! или само #N/A)
Заключение
Разполагаме с няколко начина за откриване на грешки в Excel. Всеки си има своите предимства и недостатъци, но познаването им дава възможност за използване на подходящия метод за целта. И трите показани по-горе метода имат два големи недостатъка:
1. Те се прилагат когато потребителят се е сетил или установил, че някъде има грешка. Но ако просто разчита, че всичко е ОК, то грешките си остават неустановени, а това може да има доста неприятни последствия.
2. Намират само технически грешки във формулите. Например една формула за сумиране може да е напълно коректна, но да не събира всички клетки, които трябва да се съберат.
Ако искаме да подсигурим, че нашите файлове нямат грешки тогава е необходимо да заложим проверки и контроли в самите тях, както се изисква от правилата за добра работа в Ексел.
Повече за грешките и работа с формули може да прочетете тук