Засоби аудиту формул в Excel
Як ми всі знаємо, що MS Excel в основному використовується і популярний завдяки своїм функціям, формулам та макросам. Але що, якщо під час написання формули ми отримуємо певну проблему або ми не можемо отримати бажаний результат у комірці, оскільки ми не правильно сформулювали функцію. Ось чому MS Excel надає безліч вбудованих інструментів для аудиту формул та формул для усунення несправностей.
Інструментами, які ми можемо використовувати для аудиту та усунення несправностей формул у Excel, є:
- Трасування прецедентів
- Трасозалежні
- Видаліть стрілки
- Показати формули
- Перевірка помилок
- Оцініть формулу
Приклади засобів аудиту в Excel
Ми дізнаємось про кожен із вищезазначених інструментів аудиту по одному, використовуючи кілька прикладів у Excel.
Завантажити цей шаблон інструментів аудиту Excel можна тут - Інструменти аудиту шаблон Excel# 1 - Прецеденти трасування
Припустимо, ми маємо таку формулу в комірці D2 для обчислення відсотків за рахунком ФО в банку.
Тепер, якщо ми хочемо перевірити прецеденти для формули, тоді ми можемо натиснути клавішу F2, щоб увійти в режим редагування після вибору необхідної комірки, щоб комірки прецедентів межували з різними кольорами і в тому ж кольорі записували посилання на комірку.
Ми бачимо, що А2 записується синім кольором у клітинку формули, а тим же кольором клітина А2 облямована.
Так само,
Клітина В2 має червоний колір.
Клітина С2 має фіолетовий колір.
Цей спосіб хороший, але у нас є більш зручний спосіб перевірити прецеденти для комірки формули.
Для того, щоб простежити прецеденти, ми можемо використовувати «Що впливають осередки» команду в «Формулі аудит» група під «формулами» вкладки.
Нам просто потрібно вибрати клітинку формули, а потім натиснути на команду «Відстежити прецеденти» . Тоді ви можете побачити стрілку, як показано нижче.
Ми бачимо, що попередні клітинки виділені синіми крапками.
# 2 - Видалити стрілки
Для того, щоб видалити ці стрілки, ми можемо використовувати «Видалити Arrows» команду в «Формулі аудит» група під «формули» вкладки.
No3 - Залежні від трасування
Ця команда використовується для відстеження комірки, яка залежить від вибраної комірки.
Давайте використаємо цю команду на прикладі.
Припустимо, у нас є 4 суми, які ми можемо інвестувати. Ми хочемо знати, скільки відсотків ми можемо заробити, якщо інвестуємо.
Ми бачимо, що на наведеному вище зображенні ми застосували формулу для розрахунку відсотків із сумою 1 та вказали процентний відсоток та тривалість у році.
Ми скопіюємо формулу та вставимо її в сусідні комірки для суми 2, суми 3 і суми 4. Можна помітити, що ми використовували абсолютне посилання на клітинки для клітинок G2 та I2, оскільки ми не хочемо змінювати ці посилання, поки копіювання та вставка.
Тепер, якщо ми хочемо перевірити, чи які клітини залежать від клітини G2. Потім ми будемо використовувати команду "Trace Dependents", доступну в групі "Audit Formula" на вкладці "Formulas" .
Виділіть клітинку G2 і натисніть на команду "Трасування залежних" .
На зображенні вище ми бачимо лінії стрілок, де стрілки вказують, які клітини залежать від клітин.
Тепер ми видалимо рядки стрілок за допомогою команди «Видалити стрілки» .
# 4 - Показати формули
Ми можемо використовувати цю команду для відображення формул, написаних на аркуші Excel. Клавіша швидкого доступу для цієї команди - 'Ctrl + ~' .
Дивіться зображення нижче, де ми можемо побачити формули в комірці.
Ми бачимо, що замість результатів формули ми можемо бачити формулу. Для суми формат валюти не видно.
Щоб деактивувати цей режим, натисніть "Ctrl + ~" ще раз, або ми можемо натиснути на команду "Показати формули" .
No5 - Перевірка помилок
Ця команда використовується для перевірки помилки у зазначеній формулі або функції.
Візьмемо приклад, щоб зрозуміти це.
Дивіться зображення нижче, де ми маємо помилку у застосованій для результату функції.
Тепер для вирішення цієї помилки ми будемо використовувати команду «Перевірка помилок» .
Кроки:
Виділіть клітинку, де записана формула або функція, потім натисніть «Перевірка помилок».
Коли ми натискаємо на команду, ми отримуємо наступне діалогове вікно із написом "Перевірка помилок" .
У наведеному вище діалоговому вікні видно, що є якась помилкова помилкова назва. Формула містить невпізнаний текст.
Якщо ми використовуємо функцію або створили формулу вперше, тоді ми можемо натиснути кнопку «Довідка про цю помилку», яка відкриє сторінку довідки про функцію в браузері, де ми зможемо побачити всю відповідну інформацію в Інтернеті та зрозуміти причину та знайти всі можливі шляхи вирішення.
Коли ми натискаємо цю кнопку зараз, ми знайдемо наступну сторінку.
На цій сторінці ми дізнаємось про помилку, яка виникає, коли ця помилка виникає
- Формула стосується назви, яка не була визначена. Це означає, що назва функції або іменований діапазон не були визначені раніше.
- Формула має друкарську помилку у визначеному назві. Це означає, що є якась помилка друку.
Якщо ми використовували функцію раніше і знаємо про функцію, тоді ми можемо натиснути кнопку «Показати кроки розрахунку», щоб перевірити, як оцінка функції призводить до помилки.
Якщо ми натискаємо на цю кнопку, відображаються наступні кроки:
- Наступне діалогове вікно відображається, коли ми натискаємо кнопку "Показати кроки розрахунку" .
- Після натискання кнопки "Оцінити" підкреслений вираз, тобто "IIF", отримує оцінку та надає наступну інформацію, як показано у діалоговому вікні.
Як ми бачимо на наведеному вище зображенні, вираз "IIF" оцінено як помилку, яка є "#NAME?". Тепер наступний вираз або посилання, тобто B2 підкреслили. Якщо ми натиснемо кнопку "Ввійти", тоді ми також зможемо перевірити внутрішні деталі кроку і вийти, натиснувши кнопку "Вийти" .
- Тепер ми натиснемо кнопку «Оцінити», щоб перевірити результат підкресленого виразу. Після натискання ми отримуємо такий результат.
- Після натискання кнопки «Оцінити» ми отримуємо результат застосованої функції.
- В результаті ми отримали помилку, і, проаналізувавши функцію поетапно, ми дізналися, що є помилка у "IIF". Для цього ми можемо використати команду "Вставити функцію" в групі "Бібліотека функцій" під Вкладка "Формули".
Коли ми набирали "якщо" , ми отримали подібну функцію у списку, нам потрібно вибрати відповідну функцію.
Після вибору функції "Якщо" , ми отримаємо наступне діалогове вікно з текстовими полями для аргументу, і ми заповнимо всі деталі.
Після натискання кнопки "Ok" ми отримуємо результат у клітинці. Ми скопіюємо функцію для всіх учнів.
Що слід пам’ятати
- Якщо ми активуємо команду "Показати формули", дати також відображаються у цифровому форматі.
- Оцінюючи формулу, ми також можемо використовувати F9 як ярлик у Excel.