Инструменты аудита в Excel

Инструменты аудита формул в Excel

Как мы все знаем, в основном используется MS Excel, известный своими функциями, формулами и макросами. Но что, если при написании формулы у нас возникают проблемы или мы не можем получить желаемый результат в ячейке, так как мы неправильно сформулировали функцию. Вот почему MS Excel предоставляет множество встроенных инструментов для аудита формул и устранения неполадок формул.

Инструменты, которые мы можем использовать для аудита и устранения неполадок формул в Excel:

  1. Прецеденты трассировки
  2. Следить за зависимыми
  3. Удалить стрелки
  4. Показать формулы
  5. Проверка ошибок
  6. Оценить формулу

Инструменты аудита в Excel

Примеры инструментов аудита в Excel

Мы узнаем о каждом из вышеперечисленных инструментов аудита один за другим, используя несколько примеров в Excel.

Вы можете скачать этот шаблон Excel для инструментов аудита здесь — Шаблон Excel для инструментов аудита

# 1 — Отслеживание прецедентов

Предположим, у нас есть следующая формула в ячейке D2 для расчета процентов по счету FD в банке.

Инструменты аудита, пример 1

Если мы хотим проверить прецеденты формулы, мы можем нажать F2 для перехода в режим редактирования после выбора нужной ячейки так, чтобы ячейки прецедентов были окаймлены разными цветами и одного цвета, записывается ссылка на ячейку.

Инструменты аудита, пример 1-1

Мы видим, что A2 написано синим цветом в ячейке формулы, и этим же цветом выделена ячейка A2.

Таким же образом

Ячейка B2 имеет красный цвет.

Ячейка C2 имеет фиолетовый цвет.

Этот способ хорош, но у нас есть более удобный способ проверить прецеденты для ячейки формулы.

Чтобы отследить прецеденты, мы можем использовать ‘Следы прецедентов’ команда в ‘Формула аудита’ группа под ‘Формулы’ таб.

Инструменты аудита, пример 1-2

Нам нужно выбрать ячейку с формулой, а затем нажать на ‘Следы прецедентов’ команда. Затем вы увидите стрелку, как показано ниже.

Инструменты аудита, пример 1-3

Мы видим, что прецедентные ячейки выделены синими точками.

# 2 — Удалить стрелки

Чтобы удалить эти стрелки, мы можем использовать ‘Удалить стрелки’ команда в ‘Формульный аудит’ группа под ‘Формулы’ таб.

Инструменты аудита, пример 1-4

# 3 — Следить за зависимыми

Эта команда используется для отслеживания ячейки, которая зависит от выбранной ячейки.

Воспользуемся этой командой на примере.

Предположим, у нас есть четыре суммы, в которые мы можем инвестировать. Мы хотим знать, сколько процентов мы можем заработать, если будем инвестировать.

Инструменты аудита, пример 2

Мы можем видеть, что на изображении выше мы применили формулу для расчета процентов с суммой 1 и указанным процентом процентов и продолжительностью в году.

Мы скопируем формулу и вставим ее в соседние ячейки для суммы 2, суммы 3 и суммы 4. Можно заметить, что мы использовали абсолютную ссылку на ячейку для ячеек G2 и I2, поскольку мы не хотим изменять эти ссылки. при копировании и вставке.

Пример 2-1 инструментов аудита

Теперь, если мы хотим проверить, зависят ли какие ячейки от ячейки G2, мы будем использовать «Следить за зависимыми» команда доступна в ‘Формула аудита’ группа под ‘Формулы’ таб.

Инструменты аудита (зависимые от трассировки)

Выберите ячейку G2 и нажмите на «Следить за зависимыми» команда.

Инструменты аудита, пример 2-2

На изображении выше мы можем видеть линии со стрелками, где стрелки указывают, какие ячейки зависят от ячеек.

Теперь удалим линии со стрелками, используя ‘Удалить стрелки’ команда.

# 4 — Показать формулы

Мы можем использовать эту команду для отображения формул, написанных на листе Excel. Сочетание клавиш для этой команды: Ctrl + ~.

Инструменты аудита, пример 3

См. Изображение ниже, где мы можем видеть формулы в ячейке.

Инструменты аудита, пример 3-1

Мы видим, что вместо результатов формулы мы можем видеть формулу. Для сумм формат валюты не отображается.

Чтобы отключить этот режим, нажмите ‘Ctrl + ~’ еще раз или нажмите на ‘Показать формулы’ команда.

# 5 — Проверка ошибок

Эта команда используется для проверки ошибки в указанной формуле или функции.

Давайте рассмотрим пример, чтобы понять это.

См. Изображение ниже, где у нас есть ошибка в функции, примененной к результату.

Инструменты аудита, пример 4

Теперь, чтобы решить эту ошибку, мы будем использовать «Проверка ошибок» команда.

Шаги будут:

Выберите ячейку, в которой записана формула или функция, затем нажмите «Проверка ошибок».

Инструменты аудита, пример 4-1

Когда мы нажимаем на команду, мы получаем следующее диалоговое окно с заголовком «Проверка ошибок».

Инструменты аудита, пример 4-2

В приведенном выше диалоговом окне можно увидеть, что произошла некоторая ошибка недопустимого имени. Формула содержит нераспознанный текст.

Если мы используем функцию или построили формулу впервые, то мы можем нажать на ‘Помощь по этой ошибке’ Кнопка, которая откроет страницу справки для функции в браузере, где мы сможем просмотреть всю связанную информацию в Интернете, понять причину и найти все возможные решения.

Щелкнув сейчас по этой кнопке, мы обнаружим следующую страницу.

Инструменты аудита, пример 4-3

На этой странице мы узнаем об ошибке, которая возникает, когда

  1. Формула относится к имени, которое не было определено. Это означает, что имя функции или именованный диапазон ранее не были определены.
  2. В названии формулы есть опечатка. Это означает, что произошла опечатка.

Если мы использовали эту функцию ранее и знаем о ней, то мы можем нажать на ‘Показать шаги расчета’ кнопку, чтобы проверить, как оценка функции приводит к ошибке.

Если мы нажмем на эту кнопку, отобразятся следующие шаги:

  • Следующее диалоговое окно отображается, когда мы нажимаем на ‘Показать шаги расчета’ кнопка.

показать шаги расчетов Пример 4-4

  • После нажатия на ‘Оценить’ кнопка, подчеркнутое выражение, т. е. «IIF,» оценивается и дает следующую информацию, отображаемую в диалоговом окне.

подчеркнутое выражение Пример 4-5

Как видно на изображении выше, ‘IIF’ Выражение оценивается как ошибка, то есть ‘#NAME?’. Теперь следующее выражение или ссылка, то есть B2, было подчеркнуто. Если мы нажмем на «Шаг внутрь» кнопку, то мы также можем проверить внутренние детали шага и выйти, нажав кнопку ‘Выйти’ кнопка.

Функциональная ошибка Пример 4-6

Оценить ошибку Пример 4-7

  • Теперь мы нажмем на ‘Оценить’ кнопку, чтобы проверить результат подчеркнутого выражения. После нажатия получаем следующий результат.

Оцените пример 4-8

  • После нажатия на ‘Оценить’ кнопку, получаем результат примененной функции.

Перезапустить Пример 4-9

  • В результате мы получили ошибку, и, анализируя функцию шаг за шагом, мы узнали, что в IIF. Для этого мы можем использовать ‘Вставить функцию’ команда в ‘Библиотека функций’ группу на вкладке «Формулы».

Формулы Пример 4-10

Когда мы набирали ‘если,’ у нас в списке появилась аналогичная функция, нам нужно выбрать соответствующую функцию.

Пример функции вставки 4-11

После выбора ‘Если’ функция, мы получаем следующее диалоговое окно с текстовыми полями для аргумента, и мы заполним все детали.

Аргументы функции Пример 4-12

После нажатия на ‘Хорошо,’ получаем результат в ячейке. Мы скопируем функцию для всех студентов.

Инструменты аудита, пример 4-13.png

То, что нужно запомнить

  1. Если мы активируем команду «Показать формулы», даты также будут показаны в числовом формате.
  2. При оценке формулы мы также можем использовать F9 как ярлык в Excel.

УЗНАТЬ БОЛЬШЕ >>

Похожие записи

Добавить комментарий

Ваш адрес email не будет опубликован.