OpenOffice Calc

 

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

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

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

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

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

Активная ячейка

Редактируемая ячейка

Посредством кнопки Функция "Функция" можно активизировать строку ввода для ввода формулы.

Кнопка Сумма - автосуммирование ячеек стоящих выще выбранной ячейки.

Кнопка Мастре функций - мастер функций позволяет выбрать необходимую функцию из списка и ввести её множество исходных данных.

Кнопка Отменить "Отменить"  позволяет отменить последнее действие по вводу или редактированию содержимого ячейки.

Кнопка Принять "Принять"  служит для подтверждения ввода данных. 

В конце ввода формулы  для заверщения ввода обезательно нажмите <ENTER>

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

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

Задание №1:

Введите в ячейки А1, А2, B1, B2 произвольные числа. Введите в ячейку С1 формулу для сложения значений ячеек A1, B1. Убедитесь в правильном результате.

Поменяйте значения ячеек А1 и B1 (но не C1!). Убедитесь, что после смены значений в ячейках А1 и B1 автоматически пересчитывается значение ячейки С1 (согласно формуле).

Скопируйте формулу из ячейки С1 в  ячейку С2 (подведите курсор к маркеру автозаполнения и протяните его до ячейки С2). Убедитесь, что в ячейке С2 отобразился результат сложения значений ячеек A2, B2.

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

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

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

Если адрес ячейки записывается как имя столбца и номер строки (например, A1, B1, C1) то такая форма записи называется относительной адресацией (относительная ссылка). Отнасительная ссылка при копировании ячейки изменяется.
Если скопировать С1 в С2 то фортула из =A1+B1 привратится в =A2+B2.

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

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

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

Относительная ссылка

Абсолютная ссылка

A1 $A$1

Задание №2:

Измените формулу в ячейки С1 на =$A$1 + B1. Скопируйте формулу из ячейки С1 в  ячейку С2. Убедитесь, что в ячейке С2 отобразился результат сложения значений ячеек A1, B2. При копировании ячеяк с Абсолютным адресом адрес ячейки не изменяется.

Математические операторы в 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 " - имя второй ячейки и т.п., либо, если числа содержатся в соседних ячейках, указать весь диапазон ячеек (диапазон задается следующим образом: первая ячейка : последняя ячейка).

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

Задание №3:

На "Лист2" заполните таблицу успеваемость по шаблону расположенному ниже. С 5 по 10 строки заполнить самостоятельно. В ячейку M2 введите функцию вычисления среднего значения =averagea(C2:L2), в качестве аргумента функции в поле "значение 1" введите диапазон ячеек от C2 до L2. Скопируйте формулу на весь столбец M с помощью маркера заполнения. 


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

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

int(число)

округляет число до ближайшего меньшего целого числа

round(число; кол-во разрядов)

округляет число в соответствии с правилами округления до указанного количества десятичных разрядов. Если число разрядов равно 0, то округление - до целого числа.
rounddown(число; кол-во разрядов)

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

округляет число в меньшую сторону до указанного количества десятичных разрядов. Если число разрядов равно 0, то округление - до меньшего целого числа.
Задание №4:

В ячейку N2 введите функцию округления round, в качестве аргумента функции задайте M2. Скопируйте формулу на весь столбец N.

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

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

Задание №5:

Скопируйте лист "Лист2" (вызовите контекстное меню для Ярлыка Лист2 и выберите Переместить/копировать... --> выберите Лист2,  поставте галочку Копировать --> OK),
переименуйте созданный лист, дайте ему имя "Рейтинг студентов".   Удалите столбеци M и N.

Заполните все ячейки для оценок баллами по следующей схеме:
0 - пропустил занятие;
1 - был на занятии;
2 - работа выполнена не полностью;
3 - работа полностью выполнена.

Подпишите столбци
M - "Сумма баллов";
N - "Рейтинг студентов";
O - "Итог" 

В ячейку M2 введите формулу суммирования, в качестве аргумента функции задайте диапазон ячеек от С2 до L2.

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

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

функция RANK

Задание №6:

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




Практическое задание №15

Во время каникул ребята отправились путешествовать на разных видах транспорта. 

Коля проплыл 50 км на пароходе, проехал 40 км на поезде и пролетел 100 км на самолете. Вася проплыл на пароходе 100 км, проехал на поезде 20 км и пролетел на самолете 60 км. Толя пролетел на само­лете 200 км, проехал поездом 10 км и проплыл на пароходе 25 км. Маша проехала на поез­де 30 км, пролетела на самолете 100 км и проплыла на пароходе 60 км.

1. Построить на основе вышеперечисленных данных электронную таблицу.

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

3. Вычислить общее количество километров, которое ребята проехали на поезде, пролетели на самолете и проплыли на пароходе (на каждом виде транспорта по отдельности). 

4. Вычислить суммарное количество километров, которое дети проехали в сумме.

5. Определить максимальное и минимальное количество километров, которое дети проехали на поезде.

6. Определить среднее количество километров, которое дети проплыли на пароходе.

7. Внести в таблицу следующие изменения: Коля проехал на поезде 150 км, а Вася пролетел на самолете 200 км и выделить другим цветом ячейки, в которых произошли изменения.

8. Отсортировать таблицу по убыванию количества километров.