13.11. средства контроля

13.11. средства контроля

Контроль рабочих листов

Если рабочие книги Excel используются для создания документации (особенно в бухгалтерской деятельности), необходима гарантия, что содержащиеся в них данные не содержат ошибок. Механический ввод данных всегда чреват опечатками, особенно когда речь идет о числах. При вводе текста в качестве естественного регулятора выступает обычная грамотность. Когда глаз натыкается на «неправильное» слово, у большинства людей автоматически срабатывает внутренний сигнал. При вводе числовых данных определить «на глаз», правильно ли введенное число, намного труднее. Кроме того, рабочие листы Excel часто содержат формулы, которые также являются серьезным источником ошибок.

Контроль логических ошибок

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

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

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

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

Если формула при вычислении использует значения, располагающиеся в других ячейках, говорят, что она зависит от них. Соответствующая ячейка называется зависимой. Наоборот, используемая ячейка влияет на значение формулы и поэтому называется влияющей.

Таблица 13.2.

Стандартные сообщения программы об ошибках

Циклическая ссылка — это случай, когда значение в ячейке зависит от нее самой. Простейший вариант — ячейка содержит ссылку на саму себя. Такая ситуация иногда случайно возникает при задании диапазона для итоговых вычислений. В более сложных случаях найти циклическую ссылку может оказаться не так легко, поскольку цепочка от ячейки к ней же самой может идти через большое число промежуточных зависимых ячеек.

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

Для поиска цикла, который всегда связан с ошибкой в логике работы формул рабочего листа, удобно использовать панель инструментов Циклические ссылки. Все ячейки с циклическими ссылками приведены в раскрывающемся списке на этой панели. Если цикл взаимных ссылок включает несколько ячеек (так называемая косвенная циклическая ссылка), то проследить его можно с помощью кнопок Влияющие ячейки и Зависимые ячейки. Если щелкнуть на кнопке Влияющие ячейки, то программа автоматически выделит ячейки, от которых непосредственно зависит значение в текущей ячейке. В случае косвенной циклической ссылки среди них также должна быть ячейка с циклической ссылкой. Выбрав ее и снова щелкнув на кнопке Влияющие ячейки, можно продвинуться на один шаг и далее постепенно выявить весь цикл (рис. 13.17).

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

Проверка вводимых данных

Часто вводом данных в отчетные документы занимаются не те же лица, которые эти данные получают и отвечают за их правильность. Более того, ввод данных часто осуществляют в спешке, а возможность выполнить перепроверку существует пе всегда.

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

Рис. 13.17. Поиск циклической ссылки

Правила, используемые для подтверждения правильности ввода, должны быть. выработаны заранее, до того как начат реальный ввод данных. Выполнить дополнительную операцию по настройке условий правильности ввода необходимо nn-i подготовке рабочей книги к вводу данных. Впрочем, есть и возможность выполнить проверку соответствия ранее введенных данных условиям, которые были заданы позже.

Выделив область рабочего листа, предназначенную для ввода данных определен ного типа, дайте команду Данные > Проверка. Условия, накладываемые на вводимые значения, задаются на вкладке Параметры диалогового окна Проверка вводимых значений (рис. 13.18).

Например, если в выбранные ячейки разрешен ввод только значений из о ленного набора, следует создать где-либо на рабочем листе список доп}ji вариантов, а затем выбрать в раскрывающемся списке Тип данных пункт С Р с i указать диапазон ячеек, содержащих список, в поле Источник. В дадьнейш г i попытке ввести какие-либо данные в данную ячейку, рядом с ней иоявил"> крывающая кнопка, позволяющая открыть список допустимых значений. Ьо ность ввести некорректные данные в этом случае отсутствует физически.

Рис. 13.18. Задание условий на вводимые параметры

Другие пункты списка Тип данных позволяют выбрать другие типы данных и задать для них соответствующие ограничения. Способы задания ограничений зависят от того, какие именно данные должны быть помещены в ячейку.

Специально для тех случаев, когда данные в электронную таблицу вводит не тот, кто ее создал, предусмотрены дополнительные меры управления вводом. Так, чтобы пояснить, что именно должно содержаться в данной ячейке, можно задать параметры диалогового окна (заголовок и текст), автоматически открываемого, когда данная ячейка становится текущей. Для этого служит вкладка Сообщение для ввода. Эта настройка самостоятельна по отношению к средствам подтверждения правильности ввода и может применяться, даже если никаких ограничений не задано.

Способ уведомления о нарушении правил ввода задается на вкладке Сообщение об ошибке. Здесь описывается вид появляющегося диалогового окна, если введенные данные не удовлетворяют заданным условиям.

Дополнительные средства контроля

Все инструменты контроля правильности электронных таблиц сосредоточены на панели инструментов Зависимости, которую можно открыть командой Сервис > Зависимости > Панель зависимостей. Здесь, в частности, имеются кнопки, аналогичные кнопкам панели инструментов Циклические ссылки, позволяющие прослеживать влияние и зависимость ячеек.

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

Кроме того, панель инструментов Зависимости позволяет выделить ячейки, содержимое которых не отвечает заданным условиям правильности данных. Это особенно удобно, если часть данных была введена до того, как были заданы эти условия. Ячейки с неверным содержанием помечаются кружком.