Печатать эту главуПечатать эту главу

Лабораторная работа №5. Табличный процессор Microsoft Excel 2007

1. ТЕОРЕТИЧЕСКИЙ МАТЕРИАЛ

1.6. Формулы и функции. Абсолютные, относительные и смешанные адреса ячеек. Автокпирование и Автозаполнение формул

Структура формулы. Помимо числовых и текстовых данных, ячейки таблицы Excel могут содержать формулы. Формулой считается любое математическое выражение. Формула в Excel начинается со знака «равно» ( = ).

В формулах допустимо использование следующих операторов: вычитание (-), деление(/), умножение (*), возведение в степень (^), <, > (меньше, больше), < >(не равно), => (больше или равно), <= (меньше или равно) и, так называемых операторов связи - диапазон (:), объединение (;) и объединение текстов (& ).

В формулах также можно использовать встроенные функции. Функция – это заранее определенная формула, которая по одному или нескольким аргументам, заключенным в скобки, вычисляет результат. Excel содержит огромное количество встроенных функций. К ним относятся как достаточно распространенные функции, например, СУММ, СРЗНАЧ и КОРЕНЬ, так и функции, специально предназначенные для определенных целей, например, статистические или инженерные. После имени функции следуют круглые скобки. Данные внутри скобок называются аргументами. В качестве аргумента может выступать адрес ячейки или диапазон, числовое значение, текстовая строка, выражение или другая функция. Если в функции несколько аргументов, то каждый из них отделяется точкой с запятой. Функции различаются по тому, как они используют аргументы:

  • функции без аргумента, например, ПИ(), возвращающая число p, в этом случае ставятся просто пустые скобки;
  • функции с одним аргументом, например, SIN(A1), возвращающая значение синуса числа, расположенного в ячейке А1;
  • функции с фиксированным числом аргументов, например, ОКРУГЛ(B4; В2), реализующая округление числа из ячейки B4 до указанного в ячейке B2 числа десятичных разрядов;
  • функция с неопределенным числом аргументов, например, СУММ(A1:B1; J2:K3; J3:L4), реализующая суммирование данных из разных диапазонов ячеек; каждый диапазон – аргумент функции, их может быть от 1 до 255.

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

1) выделить ячейку и нажать клавишу «=» (равно);

2) набрать формулу и нажать <Enter>.
Например: = 2*6-9 или = А1*5-ВЗ/2+С4^2.

Первая формула содержит только константы и знаки арифметических операций. Вторая формула использует ссылки на ячейки А1, ВЗ и С4. Содержимое этих ячеек подставляется в формулу и после нажатия клавиши <Enter> вычисляется результат.

Таким образом, в ячейке показывается результат вычисления формулы, а саму формулу можно увидеть и отредактировать а) либо в строке формул, предварительно выделив нужную ячейку, б) либо активизировав ячейку двойным кликом левой кнопки мыши или нажатием клавиши < F2 > – редактирование «по месту». Следует заметить, что ссылки на ячейки во второй формуле желательно набрать неcс клавиатуры, а с помощью мыши. Для этого после нажатия клавиши «= » щелкните мышью в ячейке А1 (в строке формул автоматически появится А1), затем наберите *5, щелкните мышью в ячейке ВЗ и т.д.

В формулах электронной таблицы применяются три типа адресов ячеек.

Абсолютный адрес – неизменяемый при копировании формулы адрес, например, $А$1, знак $ фиксирует и/или букву столбца и/или цифру строки. Ввод абсолютного адреса начинается с ввода обычного адреса, а затем нажимаем <F4> для вставки знаков $.

Относительный адрес – адрес, который при копировании формул корректируется программой автоматически.

Смешанный адрес – адрес, в котором номер строки (или столбца) является абсолютным, а столбца (строки) – относительным. Например, $А1 означает, что при копировании будут меняться строки столбца А, а для адреса D$23 – будут изменяться столбцы строки №23.

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

При заполнении таблицы принято задавать расчётные формулы только для “первой” (начальной) строки или “первого” (начального) столбца, а остальную часть таблицы заполнять формулами с помощью режимов копирования или заполнения. Прекрасный результат даёт применение автокопирования формул с помощью автозаполнителя.

Например, вам нужно складывать построчно значения столбцов А и В (рис. 4а) и поместить результат в столбец С. Если вы копируете формулу =А2+В2 из ячейки С1 в ячейку С2 (и далее вниз по С), то Excel сам преобразует адреса формулы соответственно как =А2+В2 (и т. д.). Но если вам нужно поместить формулу, скажем, из С2 в ячейку D4, то формула уже будет выглядеть как =В4+С4 (вместо нужной =А4+В4), и соответственно результат вычислений будет неправильным! Иными словами, обратите особое внимание на процесс копирования и при необходимости вручную корректируйте формулы. Кстати, само копирование из С1 в С2 делается следующим образом:

1) выбираем ячейку С1, из которой нужно скопировать формулу;

2) нажимаем кнопку Копировать на вкладке Главная, или клавиши <Ctrl+C>, или выбираем в контекстном меню соответствующую команду;

3) выбираем ячейку С2, в которую будем копировать формулу;

4) нажимаем кнопку Вставить на вкладке Главная, или клавиши <Ctrl+V>, или через меню контекстном меню выбираем команду Вставить с нажатием <Enter>.

Рассмотрим режим автозаполнителя (не путать со средством Автозаполнение формул). Если необходимо перенести (скопировать) формулу в несколько ячеек (например, в С2:С10) вниз по столбцу, то это удобнее и проще сделать так:

1) выделить ячейку С1,

2) подвести курсор мыши к нижнему правому углу ячейки – курсор примет форму жирного черного креста

3) нажать левую кнопку мыши и, не отпуская её, протащить ниже до требуемой последней ячейки диапазона (рис. 4б). В нашем случае это ячейка С10. Excel сам преобразует адреса формул в выделенном нами диапазоне по соответствующим адресам строк (рис. 4в).

       

a)                                     б)                                        в)

Рис. 4 Прием набора и автокопирования формул