Настройка параметров формул в Excel 2010
Поскольку Excel — это программа электронных таблиц, а не просто табличный редактор, вычисления являются очень важной частью ее функциональности. Соответственно, настройка параметров вычислений (формул), выполняемая с помощью элементов управления категории Формулы, — процесс, знание которого может весьма облегчить вам жизнь, а незнание — сделать ее невыносимой.
Все элементы управления этой категории распределены по группам Параметры вычислений
, Работа с формулами
, Контроль ошибок
и Правила контроля ошибок
. Параметры вычислений задаются в одноименном разделе.
Вычисления в книге — эта группа переключателей определяет режим вычислений:
- Автоматически — каждый раз, когда вы меняете значение в ячейке, которая используется в формуле в качестве параметра, формула пересчитывается.
- Автоматически, кроме таблиц данных — пересчет выполняется автоматически для всех формул, кроме тех, результатом выполнения которых являются таблицы данных.
- Вручную — пересчет выполняется только после нажатия клавиши F9 или выбора команды Пересчет в группе Вычисление на вкладке Формула ленты инструментов.
Пересчитывать книгу перед сохранением — перед сохранением книги все формулы в ней будут пересчитаны, чтобы сохранить актуальные значения (это имеет смысл, только если выключен режим автоматического вычисления).
Включить итеративные вычисления — итеративными называются вычисления, в которых для вычисления значений задается шаг изменения какого-то начального значения. Применяются такие вычисления для того, чтобы найти сочетание параметров, удовлетворяющее заданному условию. Путем итеративных вычислений можно решать самые разные задачи, от поиска корня n-й степени до формирования оптимального бюджета. Итерации используются в таких инструментах Excel, как инструменты поиска решения и подбора параметра. Более подробно мы рассмотрим эти инструменты в следующих публикациях.
- Предельное число итераций — в это поле вводится значение, определяющее, сколько раз с подстановкой разных значений будет выполняться пересчет листа. Чем больше итераций вы зададите, тем больше времени уйдет на пересчет. В то же время большое число итераций позволит получить более точный результат. Поэтому это значение надо подбирать, основываясь на реальной потребности. Если для вас важно получить точный результат любой ценой, а формулы в книге достаточно сложные, вы можете установить значение 10 000, щелкнуть на кнопке пересчета и уйти заниматься другими делами. Рано или поздно пересчет будет закончен. Если же вам важно получить результат быстро, то значение надо установить поменьше.
- Относительная погрешность — максимальная допустимая разница между результатами пересчетов. Чем это число меньше, тем точнее будет результат и тем больше потребуется времени на его получение.
В разделе Работа с формулами задаются параметры, управляющие вводом и вычислением формул.
Стиль ссылок R1C1 — переход от стандартного для Excel именования ячеек (A1, D6, E4 и т. д.) к стилю ссылок, при котором нумеруются не только строки, но и столбцы. При этом буква R (row) означает строку, а C (column) — столбец. Соответственно, запись в новом стиле R5C4 будет эквивалентна записи D5 в стандартном стиле.
Автозавершение формул — в этом режиме предлагаются возможные варианты функций во время ввода их в строке формул (рис. 2.11).
Использовать имена таблиц в формулах — вместо того, чтобы вставлять в формулы диапазоны ссылок в виде A1:G8
, вы можете выделить нужную область, задать для нее имя и затем вставить это имя в формулу. На рис. 2.12 приведен такой пример — сначала был выделен диапазон E1:I8
, этому диапазону было присвоено имя MyTable
, затем в ячейке D1
была создана формула суммирования, в которую в качестве аргумента передано имя данного диапазона.
Использовать функции GetPivotData
для ссылок в сводной таблице — в этом режиме данные из сводной таблицы выбираются при помощи вышеуказанной функции. Если вы вставляете в формулу ссылку на ячейку, которая расположена в сводной таблице, то вместо ссылки на ячейку будет автоматически вставлена функция ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ
. Если вам все же нужна именно ссылка на ячейку, этот флажок нужно сбросить.
С помощью элементов управления раздела Контроль ошибок настраивается режим контроля ошибок:
- Включить фоновый поиск ошибок — ошибки будут обнаруживаться и отображаться по мере ввода значений и формул, не дожидаясь пересчета.
- Цвет индикаторов ошибок — выбор цвета, которым будет помечаться ячейка с ошибкой.
В разделе Правила контроля ошибок расположены флажки, управляющие режимом контроля ошибок. Я опишу не все флажки, а только те, названия которых недостаточно полно характеризуют их назначение:
- Ячейки, которые содержат формулы, приводящие к ошибкам — поиск ячеек, в которых использован неверный синтаксис, недопустимое для данной формулы число или тип аргументов.
- Несогласованная формула в вычисляемом столбце таблицы — формулы, расположенные в вычисляемом столбце, обычно получаются в результате заполнения столбца одной и той же формулой по образцу. Это значит, что формулы в вычисляемом столбце отличаются друг от друга только ссылками на соответствующие ячейки, а сами ссылки обычно отличаются друг от друга на один шаг. Если это правило нарушается, то в данном режиме формула помечается как ошибочная.
- ормулы, несогласованные с остальными формулами в области — этот режим аналогичен предыдущему, но только не для столбца, а для области.
- Формулы, не охватывающие смежные ячейки — эта ошибка возникает тогда, когда вы создаете формулу для диапазона ячеек, а затем в этот диапазон добавляете ячейки. Формула не всегда автоматически изменяет ссылки, и, например, если вы суммировали 4 ячейки в столбце, а затем вставили пятую, она в сумму не войдет. Такая ситуация будет считаться ошибкой.
- Незаблокированные ячейки, содержащие формулы — для ячеек, в которые были введены формулы, автоматически включается защита. Если вы затем редактировали формулу или снимали режим защиты с диапазона ячеек, то ячейка с формулой может оказаться незащищенной. Данная ситуация будет оцениваться как ошибка.