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

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

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

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

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

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

• Нажмите ФАЙЛ> Параметры .
• В диалоговом окне «Параметры 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.

Нажмите Удалить стрелки в группе Аудит формул. Все стрелки на листе исчезнут.

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

• Клик в ячейке B6 не ссылается ни на одну формулу или клик в любой пустой ячейке.
• Нажмите Трассировать зависимых в группе Аудит формул. Вы получите сообщение.

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

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

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

• Нажмите в ячейке под категорией Pass в таблице оценок учеников.

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

Нажмите в ячейке под категорией Pass в таблице оценок учеников.

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

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

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

• Нажмите вкладку ФОРМУЛ на ленте.

• Нажмите Показать формулы в группе Аудит формул. Появятся формулы в рабочей таблице, чтобы вы знали, какие ячейки содержат формулы и что это за формулы.

Нажмите вкладку ФОРМУЛ на ленте.

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

• Нажмите в ячейке под TotalSales .

• Нажмите «Прецеденты трассировки». Значок рабочего листа появляется в конце стрелки. Значок рабочего листа указывает, что прецеденты находятся на другом рабочем листе.

Нажмите в ячейке под TotalSales .

Нажмите «Прецеденты трассировки». Значок рабочего листа появляется в конце стрелки. Значок рабочего листа указывает, что прецеденты находятся на другом рабочем листе.

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

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

• Нажмите на ссылку одного из прецедентов.
• Ссылка появится в поле «Ссылка».
• Нажмите ОК. Появляется рабочий лист с этим прецедентом.

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

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

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

= SQRT (1 + C2) * C10

• Нажмите на ячейку C14.
• Нажмите вкладку ФОРМУЛ на ленте.
• Нажмите Оценка формулы в группе Аудит формул. Откроется диалоговое окно «Формула оценки».

В диалоговом окне « Оценка формулы » формула отображается в поле «Оценка». При нажатии кнопки « Оценить» несколько раз формула оценивается пошагово. Выражение с подчеркиванием всегда будет выполняться следующим.

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

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

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

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

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

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

1.09544511501033 * 4976.8518518515 будет оцениваться как 5 451,87.

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

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

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

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

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

• Нажмите в ячейке C5.

• Нажмите вкладку ФОРМУЛ на ленте.

• Нажмите стрелку рядом с пунктом «Проверка ошибок» в группе «Аудит формул». В раскрывающемся списке вы увидите, что Циркулярные ссылки деактивированы, что указывает на то, что на вашем рабочем листе нет циклических ссылок.

• Выберите Trace Error из выпадающего списка.

Нажмите в ячейке C5.

Нажмите вкладку ФОРМУЛ на ленте.

Нажмите стрелку рядом с пунктом «Проверка ошибок» в группе «Аудит формул». В раскрывающемся списке вы увидите, что Циркулярные ссылки деактивированы, что указывает на то, что на вашем рабочем листе нет циклических ссылок.

Выберите Trace Error из выпадающего списка.

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

• Нажмите Удалить стрелки.
• Нажмите стрелку рядом с «Проверка ошибок».
• Выберите Error Checking из выпадающего списка.

Откроется диалоговое окно « Проверка ошибок ».

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

Если вы нажмете Справка по этой ошибке , в справке Excel будет отображена ошибка.

Если вы нажмете Показать шаги расчета , откроется диалоговое окно Оценка формулы.

Если вы нажмете Игнорировать ошибку , диалоговое окно «Проверка ошибок» закроется, и если вы снова нажмете команду « Проверка ошибок» , эта ошибка будет проигнорирована.

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