Формулы

Excel предназначен в основном для автоматизации вычислений. Для этого используются формулы. Формула имеет вид:

=ВЫРАЖЕНИЕ

Выражение состоит из операндов, соединенных друг с другом с помощью знаков операций.

Примеры выражений: 1; 2+3; A1*B1; $F$10*B2; (A1+B1)*C1; F7^G7; A1+sin(B1*C1+5); СУММ(C5:C10)/СУММ(B5:B10)*SIN(А5); и т.д.

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

Так, например, если в ячейке В5 должно помещаться среднее арифметическое чисел из ячеек С6 и Е7, то после установки курсора на В5 следует набрать =(C6+Е7)/2 (что отобразится в строке формул) и нажать Enter. Знак "=" отличает ввод текста от ввода формулы.

В формулах можно использовать числовые константы (-4,5), ссылки на адреса ячеек (D4), ссылки на блоки (A3:D8), знаки арифметических операций, встроенные функции (СУММ, МАКС, SIN и др.).

Excel позволяет использовать в формулах два стиля ссылок: стиль А1 и стиль R1C1.  Пусть в ячейке D3 нужно получить произведение чисел, находящихся в ячейках А2 (второй ряд, первая колонка) и B1 (первый ряд, вторая колонка). Это может быть записано одним из следующих способов:

 

Вид ссылок	Стиль А1	Стиль R1C1
Относительный	=A2*B1	=R[-1]C[-3]*R[-2]C[-2]
Абсолютный	=$A$2*$B$1	=R2C1*R1C2
Смешанный	=$A2*B$1 =A$2*$B1	=R[-1]C1*R1C[-2] =R2C[-3]*R[-2]C2

Абсолютная и относительная адресация ячеек

• Одно из преимуществ электронных таблиц в том, что в формулах можно использовать не только конкретные числовые значения (константы), но и переменные — ссылки на другие ячейки таблицы (адреса ячеек). В тот момент, когда вы нажимаете клавишу Enter, в формулу вместо адреса ячейки подставляется число, находящееся в указанной ячейке.

• Другое достоинство в том, что при копировании формул входящие в них ссылки изменяются (относительная адресация).

• Однако иногда при решении задач требуется, чтобы при копировании формулы ссылка на какую-либо ячейку не изменялась. Для этого используется абсолютная адресация, или абсолютные ссылки.

Ссылки относительные и абсолютные

Относительные ссылки.Если при изменении положения формулы (при копировании и распространении) автоматически меняются ссылки на ячейки относительно исходной, то такие ссылки называютсяотносительными. Относительные ссылки имеют следующий вид: А1, В1 и т.п..

Поясним это на следующем примере:

В ячейку В3 (столбец В, строка 3) занесена формула =А1+А2, то есть нужно сложить числа, находящиеся в предыдущем (левом) столбце А и двух предыдущих строках, 1 и 2.

При копировании ячейки В3, например, в ячейку Е5 соотношение в формуле сохраняется: автоматически складываются числа, находящиеся в предыдущем левом столбце D и двух предыдущих строках, 3 и 4. Аналогично, при копировании ячейки В3 в ячейку В7 формула изменяется на =А5+А6.

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

Абсолютные ссылки имеют вид: $А$5, $F$5, $G$3 и т.п.

Примечание. Для того чтобы относительную ссылку преобразовать в абсолютную, достаточно после ввода ссылки нажать клавишуF4– и знаки доллара появятся автоматически.

Смешанные ссылки. Часто применяют не чисто относительные или абсолютные ссылки, а смешанные ссылки, например, С$1 или $C1. Часть ссылки, не содержащая знак $, будет обновляться при копировании, а другая часть, со знаком $, останется без изменения. В первом случае будет фиксироваться положение строки, а во втором случае – положение столбца. Для смешанных нет необходимости вводить знак доллара с клавиатуры, достаточно воспользоваться клавишей F4. Многократное нажатие клавиши F4 работает, как переключатель и позволяет выбрать нужную ссылку.

Иногда при копировании или переносе формул требуется запретить автоматическое изменение адресов ячеек в формулах.

Фиксирование производится подстановкой знака “$”. Например:

Относительный адрес	Абсолютный адрес фиксирование
	строки	столбца	всей ячейки
D12	D$12	$D12	$D$12

Поясним это на предыдущем примере:

 

В ячейку В3 занесена формула =А$1+$A$2

(A$1 - зафиксирована первая строка, столбец будет изменяться; $A$2 - зафиксированы и строка и столбец, то есть при любом копировании изменяться нее будут)

При копировании ячейки В3 в ячейку В7 формула не изменилась, так как не изменился столбец.

При копировании ячейки В3 в ячейку Е5 формула преобразовалась к виду =D$1+$A$1