Инструменты аудита формул в Excel
Как мы все знаем, MS Excel в основном используется и популярен благодаря своим функциям, формулам и макросам. Но что, если при написании формулы у нас возникли проблемы или мы не можем получить желаемый результат в ячейке, поскольку мы неправильно сформулировали функцию. Вот почему MS Excel предоставляет множество встроенных инструментов для аудита формул и устранения неполадок формул.
Инструменты, которые мы можем использовать для аудита и устранения неполадок формул в Excel:
- Прецеденты трассировки
- Следить за зависимыми
- Удалить стрелки
- Показать формулы
- Проверка ошибок
- Оценить формулу
Примеры инструментов аудита в Excel
Мы узнаем о каждом из вышеперечисленных инструментов аудита один за другим, используя несколько примеров в Excel.
Вы можете скачать этот шаблон Excel для инструментов аудита здесь - Шаблон Excel для инструментов аудита# 1 - Отслеживание прецедентов
Предположим, у нас есть следующая формула в ячейке D2 для расчета процентов по счету FD в банке.
Теперь, если мы хотим проверить прецеденты для формулы, мы можем нажать F2, чтобы перейти в режим редактирования после выбора требуемой ячейки, чтобы ячейки прецедентов были окаймлены разными цветами и в том же цвете, ссылка на ячейку записана.
Мы видим, что A2 написано синим цветом в ячейке формулы и тем же цветом, что и ячейка A2 окаймлена.
Таким же образом
Ячейка B2 имеет красный цвет.
Ячейка C2 имеет фиолетовый цвет.
Этот способ хорош, но у нас есть более удобный способ проверить прецеденты для ячейки формулы.
Для того, чтобы проследить прецеденты, мы можем использовать «Влияющие ячейки» команду в «Формуле аудит» группа под «формулами» вкладки.
Нам просто нужно выбрать ячейку с формулой, а затем щелкнуть команду «Трассировать прецеденты» . Затем вы увидите стрелку, как показано ниже.
Мы видим, что прецедентные ячейки выделены синими точками.
# 2 - Удалить стрелки
Для того, чтобы удалить эти стрелки, мы можем использовать «Удалить Arrows» команду в «Формуле аудит» группа под «формулы» вкладки.
# 3 - Следить за зависимыми
Эта команда используется для отслеживания ячейки, которая зависит от выбранной ячейки.
Воспользуемся этой командой на примере.
Предположим, у нас есть 4 суммы, которые мы можем инвестировать. Мы хотим знать, сколько процентов мы можем заработать, если будем инвестировать.
Мы можем видеть, что на изображении выше мы применили формулу для расчета процентов с суммой 1 и указанным процентом процентов и продолжительностью в году.
Мы скопируем формулу и вставим ее в соседние ячейки для суммы 2, суммы 3 и суммы 4. Можно заметить, что мы использовали абсолютную ссылку на ячейку для ячеек G2 и I2, так как мы не хотим изменять эти ссылки, пока копирование и вставка.
Теперь, если мы хотим проверить, зависят ли какие ячейки от ячейки G2. Тогда мы будем использовать «Зависимую» команду , доступную в «Формуле аудит» группа под «формулами» вкладки.
Выберите ячейку G2 и нажмите команду «Отследить зависимые» .
На изображении выше мы можем видеть линии со стрелками, где стрелки указывают, какие ячейки зависят от ячеек.
Теперь удалим линии со стрелками, используя команду «Удалить стрелки» .
# 4 - Показать формулы
Мы можем использовать эту команду для отображения формул, написанных на листе Excel. Сочетание клавиш для этой команды - Ctrl + ~ .
См. Изображение ниже, где мы можем видеть формулы в ячейке.
Мы видим, что вместо результатов формулы мы можем видеть формулу. Для сумм валютный формат не отображается.
Чтобы отключить этот режим, снова нажмите «Ctrl + ~», или мы можем щелкнуть команду «Показать формулы» .
# 5 - Проверка ошибок
Эта команда используется для проверки ошибки в указанной формуле или функции.
Давайте рассмотрим пример, чтобы понять это.
См. Изображение ниже, где у нас есть ошибка в функции, примененной к результату.
Теперь, чтобы решить эту ошибку, мы воспользуемся командой «Проверка ошибок» .
Шаги будут:
Выберите ячейку, в которой записана формула или функция, затем нажмите «Проверка ошибок».
Когда мы нажимаем на команду, мы получаем следующее диалоговое окно с заголовком «Проверка ошибок» .
В приведенном выше диалоговом окне можно увидеть, что произошла некоторая ошибка недопустимого имени. Формула содержит нераспознанный текст.
Если мы используем функцию или построили формулу впервые, то мы можем нажать кнопку «Справка по этой ошибке» , которая откроет страницу справки для функции в браузере, где мы сможем увидеть всю связанную информацию в Интернете и понять причину и найдите все возможные решения.
Щелкнув сейчас по этой кнопке, мы обнаружим следующую страницу.
На этой странице мы узнаем об ошибке, которая возникает, когда
- Формула относится к имени, которое не было определено. Это означает, что имя функции или именованный диапазон ранее не были определены.
- В названии формулы есть опечатка. Это означает, что произошла опечатка.
Если мы использовали функцию раньше и знаем о ней, то мы можем нажать кнопку «Показать шаги вычисления» , чтобы проверить, как оценка функции приводит к ошибке.
Если мы нажмем на эту кнопку, отобразятся следующие шаги:
- Следующее диалоговое окно отображается, когда мы нажимаем кнопку «Показать шаги расчета» .
- После нажатия на кнопку «Оценить» подчеркнутое выражение, то есть «IIF», оценивается и дает следующую информацию, отображаемую в диалоговом окне.
Как мы видим на изображении выше, выражение «IIF» было оценено как ошибка «#NAME?». Теперь следующее выражение или ссылка, то есть B2, было подчеркнуто. Если мы нажмем кнопку «Step In» , мы сможем проверить внутренние детали шага и выйти, нажав кнопку «Step Out» .
- Теперь мы нажмем кнопку «Оценить» , чтобы проверить результат подчеркнутого выражения. После нажатия получаем следующий результат.
- После нажатия на кнопку «Оценить» мы получаем результат примененной функции.
- В результате мы получили ошибку, и, когда мы анализировали функцию шаг за шагом, мы узнали, что есть некоторая ошибка в «IIF». Для этого мы можем использовать команду «Вставить функцию» в группе « Библиотека функций» в разделе « Вкладка Формулы.
Когда мы набрали «если» , у нас появилась аналогичная функция в списке, нам нужно выбрать соответствующую функцию.
После выбора функции «Если» мы получаем следующее диалоговое окно с текстовыми полями для аргумента, и мы заполняем все детали.
После нажатия на «Ок» мы получаем результат в ячейке. Мы скопируем функцию для всех студентов.
То, что нужно запомнить
- Если мы активируем команду «Показать формулы», даты также будут показаны в числовом формате.
- При оценке формулы мы также можем использовать F9 как ярлык в Excel.