Инструменти за одит на формули в Excel
Както всички знаем, че MS Excel се използва главно и е популярен със своите функции, формули и макроси. Но какво ще стане, ако получим някакъв проблем, докато пишем формулата или не можем да получим желания резултат в клетка, тъй като не сме формулирали функцията правилно. Ето защо MS Excel предоставя много вградени инструменти за одит на формули и формули за отстраняване на неизправности.
Инструментите, които можем да използваме за одит и отстраняване на неизправности по формули в Excel са:
- Прецеденти на проследяване
- Проследяване на зависимите
- Премахнете стрелките
- Показване на формули
- Проверка на грешка
- Оценете формула
Примери за инструменти за одит в Excel
Ще научим за всеки от горните инструменти за одит един по един, като използваме някои примери в Excel.
Можете да изтеглите този шаблон за инструменти за одит на Excel оттук - Шаблон за инструменти за одит на Excel# 1 - Прецеденти на проследяване
Да предположим, че имаме следната формула в клетка D2 за изчисляване на лихва за FD сметка в банка.
Сега, ако искаме да проверим прецедентите за формулата, тогава можем да натиснем F2, за да влезем в режим на редактиране, след като изберем необходимата клетка, така че клетките с прецеденти да са оградени с различни цветове и в един и същи цвят се записва препратка към клетка.
Можем да видим, че А2 е написана със син цвят в клетката с формула и със същия цвят, клетка А2 е оградена.
По същия начин,
B2 клетката има червен цвят.
Клетката С2 има лилав цвят.
Този начин е добър, но имаме по-удобен начин за проверка на прецеденти за клетката с формула.
За да проследим прецеденти, можем да използваме командата „Trace Precedents“ в групата „Audit Formula“ под раздела „Formulas“ .
Трябва само да изберете клетката с формула и след това да кликнете върху командата „Trace Precedents“ . След това можете да видите стрелка, както е показано по-долу.
Виждаме, че прецедентните клетки са подчертани със сини точки.
# 2 - Премахване на стрелките
За да премахнем тези стрелки, можем да използваме командата „Премахване на стрелките“ в групата „Одит на формула“ под раздела „Формули“ .
# 3 - Зависими от проследяване
Тази команда се използва за проследяване на клетката, която зависи от избраната клетка.
Нека използваме тази команда като пример.
Да предположим, че имаме 4 суми, които можем да инвестираме. Искаме да знаем, че колко лихви можем да спечелим, ако инвестираме.
Виждаме, че в горното изображение сме приложили формула за изчисляване на лихва с Сума 1 и посочени процент и продължителност на лихвата през годината.
Ще копираме формулата и ще я поставим в съседните клетки за количество 2, количество 3 и количество 4. Може да се забележи, че сме използвали абсолютна референция за клетки за G2 и I2 клетки, тъй като не искаме да променяме тези референции, докато копиране и поставяне.
Сега, ако искаме да проверим кои клетки са зависими от G2 клетката. След това ще използваме командата „Проследяване на зависимите“, налична в групата „Одит на формула“ под раздела „Формули“ .
Изберете клетката G2 и кликнете върху командата „Trace Dependents“ .
На горното изображение можем да видим линиите на стрелките, където стрелките показват кои клетки са зависими от клетките.
Сега ще премахнем линиите със стрелки, като използваме командата „Премахване на стрелките“ .
# 4 - Показване на формули
Можем да използваме тази команда за показване на формули, написани в Excel листа. Клавишът за пряк път за тази команда е 'Ctrl + ~' .
Вижте изображението по-долу, където можем да видим формулите в клетката.
Виждаме, че вместо резултатите от формулата, можем да видим формулата. За суми форматът на валута не се вижда.
За да деактивирате този режим, натиснете отново „Ctrl + ~“ или можем да щракнем върху командата „Показване на формули“ .
# 5 - Проверка на грешки
Тази команда се използва за проверка на грешката в посочената формула или функция.
Да вземем пример, за да разберем това.
Вижте изображението по-долу, където имаме грешка във функцията, приложена за резултата.
Сега, за да разрешим тази грешка, ще използваме командата „Проверка на грешки“ .
Стъпките ще бъдат:
Изберете клетката, в която е написана формулата или функцията, след което кликнете върху „Проверка на грешки“.
Когато щракнем върху командата, получаваме следния диалогов прозорец с надпис „Проверка на грешки“ .
В горния диалогов прозорец може да се види, че има някаква грешка в невалидно име. Формулата съдържа неразпознат текст.
Ако използваме функцията или конструираме формулата за първи път, тогава можем да кликнете върху бутона „Помощ за тази грешка“, който ще отвори страницата за помощ за функцията в браузъра, където можем да видим цялата свързана информация онлайн и да разберем причината и намерете всички възможни решения.
Когато щракнем върху този бутон сега, ще намерим следната страница.
На тази страница се запознаваме с грешката, която тази грешка възниква, когато
- Формулата се отнася до име, което не е дефинирано. Това означава, че името на функцията или наименованият диапазон не са били дефинирани по-рано.
- Формулата има печатна грешка в определеното име. Това означава, че има някаква грешка при писане.
Ако сме използвали функцията по-рано и знаем за функцията, тогава можем да щракнем върху бутона „Показване на стъпките за изчисляване“, за да проверим как оценката на функцията води до грешка.
Ако щракнем върху този бутон, се показват следните стъпки:
- Следващият диалогов прозорец се показва, когато щракнем върху бутона „Показване на стъпките за изчисляване“ .
- След като щракнете върху бутона „Оценяване“ , подчертаният израз, т.е. „IIF“ се оценява и дава следната информация, както се показва в диалоговия прозорец.
Както виждаме на горното изображение, изразът „IIF“ е оценен като грешка, която е „#NAME?“. Сега следващият израз или препратка, т.е. B2 се подчерта. Ако щракнем върху бутона „Стъпка“, тогава можем да проверим и вътрешните подробности за една стъпка и да излезем, като натиснем бутона „Стъпка навън“ .
- Сега ще кликнете върху бутона „Оценяване“, за да проверите резултата от подчертания израз. След щракване получаваме следния резултат.
- След като кликнете върху бутона „Оценяване“ , получаваме резултата от приложената функция.
- Получихме грешка в резултат и докато анализирахме функцията стъпка по стъпка, разбрахме, че има някаква грешка в „IIF“. За това можем да използваме командата „Вмъкване на функция“ в групата „ Библиотека на функциите“ под „ Раздел "Формули".
Докато написвахме „ако“ , получихме подобна функция в списъка, трябва да изберем подходящата функция.
След като изберем функцията „Ако“ , получаваме следния диалогов прозорец с текстови полета за аргумент и ще попълним всички подробности.
След като щракнем върху „Ok“ , получаваме резултата в клетката. Ще копираме функцията за всички ученици.
Неща за запомняне
- Ако активираме командата „Показване на формули“, датите също се показват в числовия формат.
- Докато оценяваме формулата, можем също да използваме F9 като пряк път в Excel.