Лабораторная работа № 2

Функции. Относительная и абсолютная адресация. Мастер функций.

Строка формул

Строка формул расположена под панелью инструментов: 

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

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

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

Создание формул

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

Вводить формулу надо обязательно со знака равенства! Это надо для того, чтобы программа поняла, что в ячейку вводится именно формула, а не данные. Нужно либо с клавиатуры ввести знак равенства, либо нажать на кнопку "Функция" . Затем записывается само математическое выражение А1+B1, после ввода функции нужно либо нажать клавишу Enter, либо кнопку "Принять" . В результате данных действий ячейке С1 появится результат сложения значений ячеек А1 и В1. Формулу можно записывать и не впечатывая имена ячеек слагаемых, а просто щелкая левой кнопкой мыши по соответствующим ячейкам.

Относительная и абсолютная адресация.

Если адрес ячейки записывается как имя столбца и номер строки (например, A1, B1, C1) то такая форма записи называется относительной адресацией (относительной ссылкой)

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

Т.е. формула A1+B1 в ячейке С1 означает, что нужно сложить числа, которые находятся в соседних ячейках слева на той же строке. При копирование данной формулы в ячейку С2 формула изменилась и в ячейке С2 скопировалась формула A2+B2. Если необходимо, чтобы в формуле всегда была ссылка на конкретную ячейку и при копировании ссылка не менялась необходимо пользоваться абсолютной адресацией.

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

При абсолютной адресацией до и после имени столба нужно поставить знаки $ (например $A$1). Для превращения текущей ссылки, в которой находится курсор в строке ввода, из относительной в абсолютную и наоборот, можно использовать комбинацию клавиш Shift+F4 знаки $ будут расставлены/убраны автоматически.

Относительная ссылка	Абсолютная ссылка
A1	$A$1

Математические операторы в Open Office Calc

Для записи математических формул используются следующие операторы:

В таблице операторы расставлены в порядке их приоритета. Если необходимо возвести в квадрат сумму значений содержащихся в ячейках A1 и B1, то запись формулы должна быть следующей: =(A1+B1)^2. Запись =A1+B1^2 - неправильная!!!

Мастер функций

В Open Office Calc существуют огромное число встроенных функций. Для ввода функций в ячейку можно воспользоваться мастером функций. Нужно выбрать ячейку, в которую надо ввести функцию, и нажать кнопку "мастер функций" , которая находится в строке функций, или выберите команду Главное меню: Вставка - Функция... Откроется диалоговое окно Мастер функций, в котором надо выделить функцию и нажать кнопку Далее>> или OK, после чего появится окно ввода аргументов выбранной функции.

Функция Open Office Calc - это заранее определенная формула, которая работает с одним или несколькими аргументами и возвращает результат:

Пример: Сумма ячеек А1, А2, А3, А4 это  =SUM(А1:А4), где SUM – это имя функции суммирование, а А1:А4 – аргументы функции диапазон ячеек которые необходимо сложить.

Диалоговое окно "Мастер функций" содержит следующие поля:

В поле "категория" в раскрывающемся списке содержатся разделы, на которые разбиты все встроенные функции (математические, логические, статистические и т.п.)

В поле "функция" содержится в алфавитном порядке список функций, которые содержатся в выбранной категории. При выборе функции справа отображается справочная информация по данной функции.

После выбора требуемой функции необходимо нажать кнопку "Далее >>", следующим этапом необходимо будет задать аргументы функции.

Статистические функции - вычисление среднего значения

Для вычисления среднего значения в категории статистические существует несколько встроенных функций:

avedev
average	вычисляет среднее значение выборке, при этом ячейки с текстом игнорируются;
averagea	вычисляет среднее значение выборки, при этом текст считается равным 0.

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

Аргументы данных функций можно задать либо указав в полях "Значение 1" имя первой ячейки, в которой содержится первое число; "Значение 2 " - имя второй ячейки и т.п., либо, если числа содержатся в соседних ячейках, указать весь диапазон ячеек (диапазон задается следующим образом: первая ячейка : последняя ячейка).

Можно либо вписывать имена ячеек в соответствующие поля, либо выделять ячейки мышкой. Чтобы диалоговое окно "Мастер функций" не мешало, его можно свернуть с помощью кнопки "уменьшить/увеличить" .

Математические функции - округление (другие математические функции)

В разделе математические функции находится несколько встроенных функций для округления:

int(число)	округляет число до ближайшего меньшего целого числа
round(число; кол-во разрядов)	округляет число в соответствии с правилами округления до указанного количества десятичных разрядов. Если число разрядов равно 0, то округление - до целого числа.
rounddown(число; кол-во разрядов)	округляет число в большую сторону до указанного количества десятичных разрядов. Если число разрядов равно 0, то округление - до большего целого числа.
roundup(число; кол-во разрядов)	округляет число в меньшую сторону до указанного количества десятичных разрядов. Если число разрядов равно 0, то округление - до меньшего целого числа.

Математическая функция - сумма

В разделе математические есть встроенная функция для вычисления суммы sum(). Аргументами данной функции могут быть как набор отдельных ячеек, так и диапазон ячеек. Кроме того, так как вычисление суммы является наиболее популярной функцией, то для нее есть отдельная кнопка в строке функций .

Статистическая функция ранг - RANK()

Функция RANK вычисляет ранг значения в выборке (распределения участников по местам). Функция RANK() имеет три аргумента. Первый – значение, место (ранг) которого определяется. Второй аргумент данные – диапазон, в котором происходит распределение по местам. Диапазон должен быть неизменным, следовательно, его нужно указать с помощью абсолютной адресаций. Наконец, третий аргумент - тип – указатель порядка сортировки. Если третий аргумент 0 или не указан, места распределяются по убыванию значений (т.е. чем больше – тем лучше, 1-е место – максимальное значение). Если же поставить 1, то места будут распределяться по возрастанию (т.е. чем меньше, тем лучше).

Логическая функция условие: IF

Логические функции используются для вычислений с условиями и формирования этих условий. Если условие выполняется, выполняется один набор команд, а если не выполняется – другой набор команд. Для формирования условий в формулах используется функция IF(). Она имеет три аргумента. Первый аргумент тест – условие, второй аргумент тогда значение – действия которое совершается при выполнении условия, третий аргумент иначе значение – действия при не выполнении условия.

Пусть, например, ячейка D5 содержит формулу "=IF(A1<100,С2*10,"н/у")". Если значение в ячейке A1 меньше 100, то D5 примет значение равное значению ячейки C2, умноженному на 10. Если же значение в клетке A1 не меньше 100, то ячейка D5 примет текстовое значение - н/у.

Обратите внимание на то что текстовое значение надо заключать в двойные кавычки!

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

Например, сложная функция

=IF(A1<100,"ку-ку",IF(A1=100,"ура",C1))

выполняет следующие действия: если значение в ячейке A1 меньше 100, то выводится текстовое значение "ку-ку". В противном случае проверяется условие вложенной функции IF(). Если значение в ячейке A1 равно 100 выводится текстовое значение "ура", иначе выводится значение из ячейки C1. Toт же результат может быть получен с помощью выражения

=IF(A1<>100,IF(A1<100,"ку-ку",C1),"ура").

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

Пример структуры формулы показан на рисунке: