Сообщения об ошибках

Значение,  свидетельствующее  об  ошибке  (error  value),  —  это  результат  обработки формулы,  которую  Excel  не  в   силах  вычислить  корректно.  Все  семь  стандартных сообщений об ошибках в формулах представлены в табл. 12.2.

Таблица 12.2. Ошибки при обработке формул

Ошибка               Описание

#ДЕЛ/0! (#DIV/0!)     Попытка деления на нуль. Ошибка обычно связана с тем, что вы создали формулу, в которой делитель ссылается на пустую ячейку

#ИМЯ? (#NAME?)     В формуле используется имя, отсутствующее вхписке имен диалогового окна Присвоение имени. Возможно, вы ошиблись при вводе или указали имя, которое было удалено. Также это сообщение выводится, если строка символов не заключена в кавычки

продолжение ?

Таблица 12.2 (продолжение)

Ошибка                                             Описание

#ЗНАЧ! (#VALUE!)       Введена математическая формула, ссылающаяся на текстовое значение

#ССЫЛКА! (#REF!)   Отсутствует диапазон ячеек, на который ссылается формула

(возможно, вы его удалили)

#Н/Д (#N/A)          Нет данных для вычислений. При построении модели таблицы можно

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

#ЧИСЛО! (#NUM!)     Задан неправильный-аргумент функции. Также сообщение #ЧИСЛО! может свидетельствовать о том, что значение формулы слишком велико или чересчур мало и не может быть выведено на листе

#ПУСТО! (#NULL!)    В формуле указано пересечение диапазонов, но эти диапазоны не имеют общих ячеек

Вычисления на листе

Разработчики Excel ходят в  изношенных футболках, на которых  красуется боевой клич

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

По умолчанию вычисления производятся в  то время, когда пользователь  не работает с таблицей. Если пересчету подвергается большое количество  ячеек листа, в  левой части строки состояния появляются слова Расчет ячеек (Calculating Cells) и некоторое число. Оно  указывает процентное  соотношение  выполненной работы  к  ее  общему  объему. Процесс пересчета можно прервать, выполнив любую команду или введя любое значение в  ячейку. Excel приостановит вычисления и продолжит их по  завершении выполнения команды или ввода.

Управление вычислениями

Порой, чтобы сэкономить время, особенно когда речь идет о пересчете книги с большим количеством формул,  имеет  смысл  переключиться  с  автоматического выполнения на ручное обновление. Тогда Excel будет  производить пересчет только по вашей команде. Для этого откройте  командой Сервис ? Параметры (Tools ? Options) диалоговое окно   Параметры  (Options)   и   перейдите   на   вкладку  Вычисления   (Calculating), изображенную на рис. 12.20.

Элементы управления вкладки Вычисления используются следующим образом:

? Чтобы отключить автоматическое обновление значений в книге, установите переключатель Вычисления (Calculation) в положение Вручную (Manual).

Рис. 12.20. Вкладка Вычисления диалогового окна Параметры

? Установив  флажок  Пересчет перед сохранением (Recalculate  Before Save),  вы получите уверенность, что в сохраненной книге содержатся актуальные данные.

? Если выбран вариант Автоматически кроме таблиц (Automatic Except Tables), программа без вашего участия пересчитывает все  данные, на которые влияет изменение значений  в   ячейках,  кроме   таблиц  подстановок. Подробнее  об  этом  см.  раздел

«Таблицы подстановки» главы 18.

? Чтобы произвести обработку всех открытых книг, нажмите клавишу F9 или щелкните на кнопке Вычислить (Calc Now).

? Если вы хотите пересчитать только активный лист, щелкните на  кнопке Пересчет листа (Calc Sheet) или используйте комбинацию клавиш Shift+F9.

Копирование значений формул

Обычно при копировании ячейки, которая содержит формулу, вместе с  возвращаемым значением в буфер обмена помещается и сама формула. При необходимости скопировать только  результат  выберите в   меню  Правка  команду  Специальная  вставка  (Paste Special). На экране появится одноименное диалоговое окно, как показано на рис. 12.21. Среди   множества элементов управления этого  окна  вам нужен  лишь  тот,   который отвечает  за   результаты   вычисления  формул.   Чтобы   вставлять  в    ячейки   только возвращаемые значения,  установите   переключатель  Вставить  (Paste)  в   положение Значения (Values). Подробнее см. раздел «Команда Специальная вставка» главы 7.

Рис. 12.21. Для извлечения результирующей информации из копируемых ячеек установите переключатель в диалоговом окне Специальная вставка в положение Значения

Промежуточные значения в формулах

При  желании  вы можете  видеть результат  вычислений только   определенной  части сложной формулы, когда, например, вы проводите трассировку зависимостей или ищете ошибки  в   вычислениях. Чтобы  изменить  часть  формулы  на  возвращаемое  значение, выделите эту   часть   формулы  и  нажмите  клавишу  F9.  Точно  таким  же  способом заменяются отдельные ссылки на ячейки их значениями. Сказанное  иллюстрирует рис. 12.22.

Закончив проверку, нажмите  Esc,  чтобы  вернуть формулам  их  первоначальный вид. Нажав клавишу Enter, вы зафиксируете изменения в формуле, то есть замените ее часть возвращаемым значением.

СОВЕТ 

В  вашем  распоряжении  также  команда  Сервис  ?  Зависимости  формул  ?  Вычислить формулу (Tools ? Formula Auditing ? Evaluate Formula), помогающая разрешить имеющиеся вопросы. Подробнее об этом см. раздел «Вычисление и проверка формул» главы 9.

Рис. 12.22. Выделите фрагмент формулы и нажмите клавишу F9 для преобразования его в значение

Циклические ссылки

Циклическая  ссылка  (circular  reference)  —  это  формула,  которая   зависит от  своего собственного значения. Наиболее очевидный пример — формула, содержащая ссылку на свою собственную  ячейку. Так, если ввести в ячейку А1 формулу =С1-А1, Excel выдаст сообщение об ошибке, показанное на рис. 12.23.

Рис. 12.23. Подобное сообщение об ошибке появляется при попытке ввода формулы,

содержащей циклическую ссылку

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

Для этого выберите в меню Вид команду Панели инструментов, а затем — Циклические ссылки (Circular Reference). Нажмите кнопку Влияющие ячейки (Trace Precedents) или Зависимые ячейки (Trace Dependents) появившейся панели инструментов, чтобы отследить причину неполадок. Программа в виде  стрелок наглядно покажет путь ко всем ячейкам,   вовлеченным в    процесс   вычисления  формулы   с   круговой   ссылкой,   как продемонстрировано на рис. 12.24.

Рис. 12.24. Панель инструментов Циклические ссылки помогает исправить ошибки в формулах, связанные с циклическими ссылками

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

СМОТРИ ТАКЖЕ

Подробнее о трассировке ячеек см. раздел «Проверка и документирование листов» главы 9.

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

12.25. Они здесь циклические, поскольку формула в  ячейке А1 зависит от содержимого ячейки A3, которое, в свою  очередь, определяется значением ячейки А1.

Рис. 12.29В качестве аргумента для этой формулы используется массив констант

Чтобы ввести  в формулу массив констант, выполните следующие действия:

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

2.   Поставьте фигурные скобки вокруг аргумента (самостоятельно), чтобы указать, что эти значения представляют собой массив констант.

3.   Нажмите клавиши Ctrl+Shift+ Enter. Таким образом, формула  окажется  заключена в две  пары фигурных скобок: одна пара для констант, а другая — вокруг всей формулы.

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

Расширение массивов-аргументов

При использовании массивов   в качестве аргументов формул все массивы  должны быть одинаковой размерности. Если размерности массивов не  совпадают, Excel в  некоторых случаях расширяет аргументы. Так, чтобы перемножить все значения в диапазоне А1:В5 на      10,      подходит       каждая      из      следующих      формул:      {-А1:В5*10}      или

{={1;2:3;4:5;6:7;8:9;10}*10}.

Обратите  внимание, что обе формулы не сбалансированы: десять  значений находится слева от оператора умножения и только одно —  справа. Excel в  этом случае расширяет второй аргумент так, чтобы он  количественно соответствовал  первому. Теперь первая формула перепишется в таком виде:

{-А1:В5*{10;10:10;10:10;10:10;10:10;10}}

а вторая будет выглядеть так:

{={1;2:3;4:5;б:7;8:9;10} *{10;10:10;10:10;10:10;10:10;10}}

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

Источник: Эффективная работа: Microsoft Office Excel 2003 / М. Додж, К. Стинсон — СПб.: Питер, 2005. — 1088 с: ил.

Похожие посты:

Вы можете оставить комментарий, или ссылку на Ваш сайт.

Оставить комментарий