Анализ данных Excel - аудит формул

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

Какие ячейки участвуют в вычислении формулы в активной ячейке.
Какие формулы относятся к активной ячейке.

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

Настройка параметров отображения

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

Нажмите FILE > Options.
В диалоговом окне «Параметры Excel» нажмите «Дополнительно».
В параметрах отображения для книги -
- Выберите книгу.
- Убедитесь, что в разделе «Показать объекты» выбрано «Все».
Повторите этот шаг для всех проверяемых книг.

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

Ячейки-предшественники - это ячейки, на которые ссылается формула в активной ячейке.

В следующем примере активной ячейкой является C2. В C2 у вас есть формула=B2*C4.

B2 и C4 являются предшествующими ячейками для C2.

Чтобы отследить прецеденты ячейки C2,

Щелкните ячейку C2.
Щелкните вкладку Формулы.
Щелкните Прецеденты трассировки в группе Аудит формул.

Отобразятся две стрелки, одна от B2 до C2, а другая от C4 до C2, отслеживая прецеденты.

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

Щелкните ячейку, которая не содержит формулы, или щелкните пустую ячейку.
Щелкните Прецеденты трассировки в группе Аудит формул.

Вы получите сообщение.

Удаление стрел

Щелкните Удалить стрелки в группе Аудит формул.

Все стрелки на листе исчезнут.

Поиск иждивенцев

Зависимые ячейки содержат формулы, относящиеся к другим ячейкам. Это означает, что если активная ячейка участвует в формуле в другой ячейке, другая ячейка является зависимой ячейкой в активной ячейке.

В приведенном ниже примере C2 имеет формулу =B2*C4. Следовательно, C2 является зависимой ячейкой от ячеек B2 и C4.

Чтобы отследить иждивенцев ячейки B2,

Щелкните ячейку B2.
Щелкните вкладку Формулы.
Щелкните Отслеживать зависимые в группе Аудит формул.

Стрелка появляется от B2 к C2, показывая, что C2 зависит от B2.

Чтобы отследить иждивенцев ячейки C4 -

Щелкните ячейку C4.
Щелкните вкладку «Формула»> «Отслеживать зависимые» в группе «Аудит формул».

Другая стрелка появляется от C4 к C2, показывая, что C2 также зависит от C4.

Нажмите Remove Arrowsв группе Formula Auditing. Все стрелки на листе исчезнут.

Note- Для отслеживания иждивенцев ячейки на ячейку должна ссылаться формула в другой ячейке. В противном случае вы получите сообщение об ошибке.

Щелкните ячейку B6, на которую не ссылается никакая формула, или щелкните любую пустую ячейку.
Щелкните Отслеживать зависимые в группе Аудит формул. Вы получите сообщение.

Работа с формулами

Вы поняли концепцию прецедентов и иждивенцев. Теперь рассмотрим рабочий лист с несколькими формулами.

Щелкните ячейку под категорией сдачи в таблице результатов экзамена.
Щелкните Прецеденты трассировки. Ячейка слева (Метки) и диапазон E4: F8 будут сопоставлены как прецеденты.
Повторите эти действия для всех ячеек в разделе «Категория сдачи» в таблице результатов экзамена.

Щелкните ячейку под категорией сдачи в таблице оценок учащихся.
Щелкните Отслеживать иждивенцев. Все ячейки под категорией прохождения в таблице результатов экзамена будут сопоставлены как иждивенцы.

Отображение формул

Рабочий лист ниже содержит сводку продаж продавцов в регионах Восток, Север, Юг и Запад.

Щелкните вкладку ФОРМУЛЫ на ленте.
Щелкните Показать формулы в группе Аудит формул. Появятся формулы на листе, чтобы вы знали, какие ячейки содержат формулы и что это за формулы.

Щелкните ячейку под TotalSales.
Щелкните Прецеденты трассировки. В конце стрелки появится значок рабочего листа. Значок рабочего листа указывает, что прецеденты находятся на другом рабочем листе.

Дважды щелкните стрелку. АGo TO появляется диалоговое окно, показывающее прецеденты.

Как вы заметили, существует четыре прецедента на четырех разных листах.

Щелкните ссылку на один из прецедентов.
Ссылка появится в поле Ссылка.
Щелкните ОК. Появится рабочий лист, содержащий этот прецедент.

Оценка формулы

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

Рассмотрим формулу NPV (средний год) в ячейке C14. Формула

=SQRT (1 + C2)*C10

Щелкните ячейку C14.
Щелкните вкладку ФОРМУЛЫ на ленте.
Щелкните Вычислить формулу в группе Аудит формул. Откроется диалоговое окно "Вычислить формулу".

в Evaluate FormulaВ диалоговом окне формула отображается в поле под заголовком Оценка. Щелкнув поEvaluateнесколько раз, формула будет оцениваться пошагово. Следующим всегда будет выполняться выражение с подчеркиванием.

Здесь C2 подчеркнут в формуле. Итак, он оценивается на следующем этапе. НажмитеEvaluate.

Ячейка C2 имеет значение 0,2. Следовательно, C2 будет оценен как 0,2.1+0.2подчеркнут, показывая это как следующий шаг. НажмитеEvaluate.

1 + 0,2 будет оцениваться как 1,2. SQRT(1.2)подчеркнут, показывая это как следующий шаг. НажмитеEvaluate.

SQRT (1.2) будет оцениваться как 1.09544511501033. C10подчеркнут, показывая это как следующий шаг. НажмитеEvaluate.

C10 будет оцениваться как 4976.8518518515.

1.09544511501033 * 4976.8518518515 подчеркнут, показывая это как следующий шаг. НажмитеEvaluate.

1.09544511501033 * 4976.8518518515 будет оценено как 5,451,87.

Больше нет выражений для оценки, и это ответ. ВEvaluate кнопка будет изменена на Restart кнопка, указывающая на завершение оценки.

Проверка ошибок

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

Рассмотрим следующие простые вычисления.

Расчет в ячейке привел к ошибке # DIV / 0 !.

Щелкните ячейку C5.
Щелкните вкладку ФОРМУЛЫ на ленте.
Щелкните стрелку рядом с пунктом Проверка ошибок в группе Аудит формул. В раскрывающемся списке вы найдетеCircular References деактивирован, что означает, что на вашем листе нет циклических ссылок.
Выбрать Trace Error из раскрывающегося списка.

Ячейки, необходимые для вычисления активной ячейки, обозначены синими стрелками.

Щелкните Удалить стрелки.
Щелкните стрелку рядом с надписью «Проверка ошибок».
В раскрывающемся списке выберите «Проверка ошибок».

В Error Checking появится диалоговое окно.

Обратите внимание на следующее -

Если вы нажмете Help on this error, Появится справка Excel по ошибке.
Если вы нажмете Show Calculation Steps, Появится диалоговое окно «Вычислить формулу».
Если вы нажмете Ignore Error, диалоговое окно "Проверка ошибок" закроется, и если вы нажмете Error Checking снова, она игнорирует эту ошибку.
Если вы нажмете Edit in Formula Bar, вы перейдете к формуле в строке формул, чтобы вы могли редактировать формулу в ячейке.