Использование формул

2.1 Ввод формул

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

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

2.2 Ссылки в формулах

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

Ссылка в формуле является идентификатором ячейки, например А5, D10, или идентификатором диапазона, например А5: F20. Ссылку не рекомендуется набирать на клавиатуре. Ввод ссылки целесообразно делать, выделяя нужную ячейку с помощью мыши. Ссылка в формулу вставляется на выделенную ячейку, вокруг нее появляется подвижная рамка. В случае ошибки можно выделить другую ячейку, и ссылка на нее появится в формуле.

Ссылки в Excel бывают относительные (например, = А1), абсолютные (например, = $A$1) и смешанные (например, = $A1 или = A$1). При вводе формулы относительную ссылку, находящуюся рядом с точкой вставки, можно изменять на абсолютную или смешанную, последовательно нажимая клавишу < F4>. Относительная ссылка указывает на ячейку, основываясь на ее положении относительно ячейки, в которой находится формула.

Относительная ссылка при копировании формулы в другие ячейки автоматически настраивается. Абсолютная ссылка указывает на фиксированную ячейку. При копировании формулы абсолютная ссылка не изменяется. Смешанная ссылка содержит относительную и абсолютную ссылки. Координата ячейки, помеченная знаком $, является абсолютной и при копировании формулы сохраняется.

2.3 Присваивание имен ячейкам и диапазонам

Ячейкам и диапазонам ячеек удобно также назначать имена и затем использовать их в формулах. Для работы с именованными ячейками и диапазонами используются команды из группы Определенные имена на вкладке Формулы. Использование имен удобно, поскольку в этом случае пользователю не надо запоминать места расположения исходных данных в рабочих листах при работе с формулами. Имена, определенные в текущем листе, могут использоваться в любых других листах книги. Имя ячейки или диапазона можно использовать в формуле вместо обычной ссылки.

2.4 Использование массивов

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

2.5 Вычисления на листе и ошибки в формулах

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

Для этого на вкладке Формулы в группе Вычисления необходимо установить переключатель в положение, отличное от автоматически. Тогда для обновления значений формул во всех открытых книгах надо нажать клавишу F9, а для проведения вычисления только в активном листе ‑ клавиши SHIFT+F9.

Для вычислений в Excel используются внутренние (хранимые) значения ячеек, а не отображаемые на экране. Если на вкладке Вычисления установлен флажок Точность как на экране, то в вычислениях будут использоваться отображаемые на экране значения. Использование в вычислениях значений, отображаемых на экране, приводит к округлению внутренних значений ячеек листа, а также к изменению точности вычислений. После такого округления возврат к исходной точности невозможен.

2.6 Циклические ссылки и их обработка

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

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

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

2.7 Упражнения по теме 2

2.7.1 Ввод формул, ссылок, формулы массива

При выполнении задания 2.7.1 мы научились основам работ с формулами ссылками и формулами массивов в MS Excel.

2.7.2 Создание таблицы расчета расходов

При выполнении задания 2.7.2 мы научились созданию таблицы в MS Excel.