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