Если Microsoft Excel применяется для выполнения сложных вычислений, может возникнуть ситуация, когда формула написана с ошибкой, и необходимо эту ошибку найти. Если простой анализ (по цветным рамкам ячеек, выделению скобок цветом или через диалоговое окно аргументов Мастера функций, рис. 12.3) не позволяет найти ошибку, можно воспользоваться более мощными инструментами для ее поиска, представленными в группе Зависимости формул вкладки Формулы ленты инструментов.
Основным инструментом для поиска ошибок в формуле является отображение стрелок, которые показывают последовательность использования данных: если в ячейку вставлена ссылка, стрелка соединит исходную ячейку с данной.
Для отображения стрелок следует выделить ячейку и нажать кнопку «
Влияющие ячейки» (отображает все стрелки ссылок, ведущие в данную ячейку) или «
Зависимые ячейки» (отображает все стрелки ссылок, ведущие из данной ячейки в другие). Последовательно выделяя ячейки и нажимая кнопки, можно отобразить на экране сколько угодно стрелок. Команды из выпадающего меню кнопки «
Убрать стрелки» скрывают стрелки с экрана: Убрать стрелки к влияющим ячейкам и Убрать стрелки к зависимым ячейкам удаляют стрелки, ведущие в и из выделенной ячейки; команда Убрать стрелки удаляет все стрелки, вне зависимости от выделенных ячеек.
Если в документе присутствуют длинные цепочки ссылок (одна ячейка ссылается на другую, другая на третью и т.д.), каждое нажатие на кнопки « Влияющие ячейки» и « Зависимые ячейки» добавляет в цепочку стрелок очередное звено, а каждый щелчок по командам Убрать стрелки к влияющим ячейкам и Убрать стрелки к зависимым ячейкам – удаляет крайние стрелки в цепочке.
Двойной щелчок мышью по стрелке делает выделенной ячейку, в которую ведет эта стрелка; следующий двойной щелчок переносит курсор к ячейке, из которой стрелка берет начало.
В диалоговом окне, вызываемом кнопкой «Выделить…» в окне перехода (см. рис. 11.10, окно, напомним, открывается командой Перейти из выпадающего меню кнопки «
Найти и выделить» на Главной вкладке ленты инструментов) можно задать выделение ячеек, влияющих на текущую или зависимых от нее (пункты «Влияющие ячейки» и «Зависимые ячейки»). При этом переключатель ниже определяет, рассматривать ли только одну ступень влияния («Только непосредственно») или все ступени цепочки («На всех уровнях»).
Если формулу невозможно посчитать, Microsoft Excel отображает ошибку. В Microsoft Excel встречаются следующие ошибки:
Ø ##### (значение не помещается в узкий столбец либо вычислена отрицательная дата);
Ø #ЗНАЧ! (операнд или аргумент функции неправильного типа. Например, ошибка возникнет, если там, где требуется число, окажется текст);
Ø #ДЕЛ/0! (деление на 0);
Ø #ИМЯ? (в формуле записано имя, но оно ничему не присвоено). Часто ошибки этого типа возникают, когда студент вводит ссылки в формулу вручную, но при этом забывает переключиться на английский язык. В частности, запись «А2» (где А – русская) не распознается Microsoft Excel как ссылка, ячейки с именем «А2» на листе нет, и будет отображена данная ошибка;
Ø #ССЫЛКА? (ссылка на ячейку указана неверно, в частности, ячейка, на которую ссылаются, была удалена);
Ø #ЧИСЛО! (вычисленное число очень большое по модулю);
Ø #ПУСТО! (при помощи оператора «пробел» задано пересечение диапазонов, однако эти диапазоны не пересекаются).
Ячейка с ошибкой в Microsoft Excel обозначается зеленым треугольником в левом верхнем углу ячейки. При выделении ячейки, содержащей ошибку, рядом с ней появляется символ 
.
Если воспользоваться первой одноименной командой из выпадающего мню кнопки «
Проверка наличия ошибок» в группе Зависимости формул, откроется диалоговое окно контроля ошибок (рис. 12.4), где приведены адрес ячейки и формула. В окне также присутствуют следующие кнопки: «Назад» и «Далее» для перехода к следующей или предыдущей ошибке; «Пропустить ошибку» убирает символ с листа, и данная ошибка больше не будет отображаться в окне; «Изменить в строке формул» открывает формулу для ручного редактирования. Кнопка «Показать этапы вычисления…» открывает диалоговое окно трассировки вычислений (см. рис. 12.6 ниже); «Справка по этой ошибке» запускает справочную систему Microsoft Excel. Эти же команды продублированы в меню, которое появится, если щелкнуть по стрелке вниз, которая появится при наведении мыши на символ .
Рис. 12.4 Диалоговое окно контроля ошибок
Если одна из формул в цепочке выдает ошибку, все последующие формулы в этой цепочке также покажут ошибки. Стрелки упрощают поиск источника ошибки, так как до ошибочной формулы стрелки имеют синий цвет, а после нее – красный. Быстро показать все стрелки, ведущие к ячейке, как синие, так и красные, позволяет команда Источник ошибки из выпадающего меню кнопки « Проверка наличия ошибок». Однако она работает только для ячеек с символом .
Другим инструментом отладки документов Microsoft Excel является окно контрольного значения (рис. 12.5). Данное окно позволяет отображать результат вычисления нескольких формул в одном месте. Для вызова окна служит кнопка «
Окно контрольного значения» в группе Зависимости формул. При помощи кнопки «Добавить контрольное значение» в это диалоговое окно можно добавлять ячейки, за данными которых нужно следить. Меняя исходные данные, можно сразу же увидеть, как изменяются все контрольные значения. Лишние ячейки из окна можно удалить кнопкой «Удалить контрольное значение».
Рис. 12.5 Диалоговое окно контрольных значений
Когда необходимость в контрольных значениях отпадет, можно закрыть окно – кнопкой 
в окне или отжатием кнопки « Окно контрольного значения».
Если формула в ячейке настолько сложная, что тяжело понять логику ее работы, может помочь еще один полезный инструмент – трассировка вычисления. Кнопка «
Вычислить формулу» в группе Зависимости формул открывает диалоговое окно вычислений (рис. 12.6).
В левой части окна пишется адрес ячейки, в которой хранится формула, а в правой – сама формула. Одна из частей формулы при этом подчеркнута. Кнопка «Вычислить» рассчитывает значение подчеркнутой части и подставляет его в формулу, при этом подчеркивается следующая часть. Кнопка «Шаг с заходом» доступна, когда подчеркнутой оказывается ссылка на ячейку. Нажатие кнопки выделяет эту ссылку цветом. При этом слева отображается полная ссылка на ячейку (выделена этим же цветом), а правее создается новое поле с формулой из выделенной цветом ячейки. Одна из частей этой формулы, в свою очередь, будет подчеркнута. Теперь кнопка «Вычислить» рассчитывает подчеркнутое значение в нижней формуле. Кнопка «Шаг с выходом» позволяет подняться на уровень вверх, т.е. вернуться к предыдущей формуле.
Последовательно нажимая на кнопку «Вычислить», а, при необходимости, и кнопки «Шаг с заходом» и «Шаг с выходом», можно просчитать формулу от начала до конца, видя все промежуточные значения. Когда формула посчитана полностью, кнопка «Вычислить» заменяется кнопкой «Заново».
Рис. 12.6 Диалоговое окно трассировки вычисления формулы
Наконец, кнопка «
Показать формулы» в группе Зависимости формул или комбинация клавиш Ctrl+` (клавиша с русской буквой «ё») переводит Microsoft Excel в режим, когда во всех ячейках отображаются формулы вместо вычисляемых ими значений. Если сделать ячейку активной, все ссылки в формуле автоматически раскрасятся в разные цвета, а соответствующие ячейки обводятся цветными рамками.
Говоря об ошибках в формулах Microsoft Excel, следует также упомянуть про такое явление, как циклические ссылки. Например, пользователь может ввести в ячейку B1 формулу, которая включает ссылку на ячейку A1. В то же время в ячейке A1 может находиться формула с функцией, одним из аргументов которой является значение ячейки B1. Очевидно, что при пересчете листа значения ячеек A1 и B1 будут меняться бесконечно.
В этом случае Microsoft Excel не будет рассчитывать формулу. В ячейке отобразится «0» или последнее значение, которое формулы была в состоянии рассчитать до того, как пользователь внес исправления на лист, создав циклическую ссылку. В строке состояния при этом отобразится сообщение «Циклические ссылки: XY», где XY – адрес первой ячейки с циклической ссылкой, обнаруженной Microsoft Excel. Следует перейти к формуле в данной ячейке и исправить ошибку.
Для быстрого перехода к ячейкам с циклическими ссылками можно воспользоваться командой Циклические ссылки из выпадающего меню кнопки « Проверка наличия ошибок». Команда, в свою очередь, раскрывается в меню, которое содержит все ячейки на листе с циклическими ссылками. Щелчок по пункту меню выделяет соответствующую ячейку, позволяя отредактировать ее формулу.
Циклические ссылки не всегда являются ошибками. В некоторых случаях они позволяют организовать итеративные вычисления. Суть итеративных вычислений можно примерно описать так: берется некоторое приближенное значение переменной х; на его основании вычисляется значение переменной y; это значение y, в свою очередь, по специальной формуле позволяет вычислить новое, уточненное значение x; затем снова вычисляется y, и снова x.
Каждое вычисление пары y и x называется итерацией. Процесс повторяется до тех пор, пока в результате очередной итерации обновленное значение x не окажется достаточно близким к предыдущему.
В Microsoft Excel имеется специальный режим, позволяющий организовать итеративные вычисления. Следует воспользоваться меню Файл – Параметры, выбрать в окне настроек Microsoft Excel категорию «Формулы» и установить флажок «Включить итеративные вычисления». В этом режиме Microsoft Excel будет пытаться произвести расчет формул с циклическими ссылками, многократно повторяя вычисления. При этом количество повторений определяется либо предельным числом итераций, либо относительной погрешностью. В последнем случае вычисления будут прерваны, если при очередной итерации значение любой из ячеек с циклическими ссылками изменится не величину, меньшую, чем относительная погрешность. Оба параметра настраиваются в окне свойств Microsoft Excel, рядом с флажком «Включить итеративные вычисления».
Защита ячеек и листов
В некоторых случаях перед пользователем может встать вопрос, как ограничить других пользователей, работающих с книгой, в возможности модифицировать те или иные данные. При этом предполагается, что часть данных модифицировать можно, а часть – нет. Добиться такого результата позволяют функции защиты элементов книги.
В Microsoft Excel имеется возможность настраивать защиту как всей книги в целом, так и каждого листа книги в отдельности.
Наиболее радикальным способом защиты является установка пароля на открытие документа. Данная функция присутствует во всех программах Microsoft Office. Открыв при помощи меню Файл – Сохранить как… стандартное диалоговое окно сохранения документа, следует воспользовавшись командой Общие параметры… выпадающего меню кнопки «Сервис» в нижней части окна. В открывшемся новом окне можно задать два пароля: один позволяет лишь открыть книгу и просмотреть ее содержимое (сохранение книги при этом запрещено), а второй позволяет получить полный доступ, включая редактирование.
Менее радикальный способ заключается в защите книги. Кнопка «
Защитить книгу» в группе Изменения вкладки Рецензирование ленты инструментов открывает диалоговое окно (рис. 12.7). Флажки в этом окне позволяют защитить окно книги (кнопки «Свернуть», «Развернуть» и «Закрыть» для окна документа в программе Microsoft Excel, а также возможность изменять размеры окна, будут недоступны) или ее структуру (невозможно добавлять и удалять листы из книги, перемещать их, отображать скрытые листы). Следует закрепить защиту паролем, иначе любой желающий сможет вновь открыть это окно и снять защиту.
Более гибкий метод заключается в защите отдельных листов. Кнопка «
Защитить лист» в группе Изменения вкладки Рецензирование открывает диалоговое окно (рис. 12.8). Флажок в верхней части включает защиту листа книги, флажки в нижней части настраивают исключения, т.е. позволяют любому пользователю делать указанные действия, несмотря на защиту. Чтобы защиту нельзя было снять, следует также задать пароль.
Чаще всего необходимо запретить пользователям, не знающим пароль, менять лишь определенные ячейки листа. Это можно сделать двумя способами.
Во-первых, в диалоговом окне «Формат ячеек» имеется шестая вкладка «Защита» (рис. 12.9). Флажки на этой вкладке позволяют отнести ячейку к категории защищенных и скрыть формулы, оставив только вычисляемые значения.
Рис. 12.9 Диалоговое окно «Формат ячейки», вкладка защиты ячейки
Во-вторых, можно задать на листе определенные диапазоны, которые можно будет изменять, не смотря на защиту листа. Делается это кнопкой «
Разрешить изменение диапазонов» в группе Изменения вкладки Рецензирование. В открывшемся диалоговом окне (рис. 12.10) перечислены диапазоны ячеек листа, которые имеют особый статус. Кнопка «Создать» позволяет добавить диапазон в список, кнопка «Изменить» позволяет редактировать диапазон, кнопка «Удалить» удаляет его из списка. Создание и редактирование диапазона осуществляется через диалоговое окно (рис. 12.11), где можно задать название для диапазона, указать его, введя адреса ячеек, и задать пароль отдельно для каждого диапазона. Кнопка «Разрешения…» позволяет настроить разрешения на изменение диапазона посредством стандартных средств безопасности Windows (в пособии не рассматривается).
Рис. 12.10 Диалоговое окно управления диапазонами с особыми разрешениями
Рис. 12.11 Диалоговое окно создания диапазона с особыми разрешениями
Флажок внизу диалогового окна рис. 12.10 позволяет создать в книге отдельный пустой лист, к ячейкам которого будут применены все те же разрешения и защиты, что и к ячейкам текущего листа.
Задавать условия защиты как ячеек, так и диапазонов следует до включения защиты листа: если она включена, большинство команд ленты инструментов, в том числе и открытие окна «Формат ячеек» будут недоступны.
Задавая пароли защиты, следует быть осторожным: утерянный пароль нельзя восстановить никаким образом, кроме перебора. Если пароль будет утерян, книга или ее элементы станут недоступными навсегда.
