Лабораторная работа №10 Использование формул. Мастер функций. Построение диаграмм.

Действия над ячейками

При работе с ячейками производятся различные операции, такие, как копирование, вырезание, перемещение.

Для копирования ячеек выделяем их и используем одним из указанных способов:

● Правка → Копировать;

● вызываем контекстное меню на выделенных ячейках и выбираем Копировать;

● воспользуемся горячими клавишами CTRL+C.

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

● Правка → Вставить;

● вызываем контекстное меню на выделенных ячейках и выбираем Вставить;

● воспользуемся горячими клавишами CTRL+V.

Также существует специальная вставка – это вставка содержимого в формате, который можно указать. Для данной вставки выбираем пункт меню Правка → Вставить как…

Рис. 1. Параметры специальной вставки

Параметры специальной вставки.

Выбор: выбор формата для содержимого, которое требуется вставить:

● Вставить все - вставка содержимого всех ячеек, примечаний, форматов и объектов в текущий документ;

● Строки - вставка ячеек, содержащих текстовые строки;

● Числа - вставка ячеек, содержащих числа;

● Дата и время - вставка ячеек, содержащих значения даты и времени;

● Формулы вставка ячеек, содержащих формулы;

● Примечания - вставка примечаний, присоединенных к ячейкам;

● Форматы - вставка атрибутов форматов ячеек;

● Объекты - вставка объектов, содержащихся в выбранном диапазоне ячеек (это могут быть объекты диаграмм или рисованные объекты).

Операции: выбор операции, которая будет применена при вставке ячеек на лист

● Нет - при вставке диапазона ячеек операция не применяется;

● Сложить - сложение значений скопированных ячеек со значениями ячеек назначения;

● Вычесть - вычитание значений скопированных ячеек из значений ячеек назначения;

● Умножить - умножение значений скопированных ячеек на значения ячеек назначения;

● Разделить - деление значений ячеек назначения на значения скопированных ячеек.

Параметры - задание параметров вставки:

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

● Транспонировать - ячейки из левого столбца становятся верхней строкой, а верхняя строка — левым столбцом. Масштаб объектов, содержащихся в скопированных ячейках, изменяется в соответствии с диапазоном вывода;

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

Переместить ячейки - задаются параметры смещения конечных ячеек при вставке скопированных:

● Не перемещать - вставленные ячейки заменяют конечные ячейки;

● Вниз - при вставке скопированных ячеек конечные ячейки сдвигаются вниз;

● Вправо - при вставке скопированных ячеек конечные ячейки сдвигаются вправо.

Аналогично используем Правка → Вырезать.

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

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

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

Проведение расчетов

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

При записи формулы следует соблюдать обычные правила алгоритмических языков: арифметические операции выполняются слева направо в порядке старшинства (возведение в степень ^ , умножение *, деление /, сложение +, вычитание ). Для изменения порядка выполнения операций используются круглые скобки, аргумент функции также берется в круглые скобки, количество открывающих скобок равно количеству закрывающих скобок. Адреса ячеек вводят с помощью мыши. Кликнув на ячейку при написании формулы, её имя отображается в формуле и выделено красным цветом.

Как уже отмечалось, ввод формулы начинается со знака равенства, далее пишется сама формула. К примеру: =4+16. Записав такую формулу и нажав Enter, мы увидим в ячейке число 20. Конечно, формулы без переменных обычно не имеют особого смысла, поэтому теперь посмотрим, как использовать переменные, в качестве которых в OpenOffice.org Calc служат адреса ячеек. К примеру, если в A1 мы записали число 20, то если мы запишем в B1 формулу =A1^2 и нажмём Enter в ячейке B1 появится число 400.

Основные арифметические операции, доступные OpenOffice.org Calc:

+ сложение

- вычитание

* умножение

/ деление

^ возведение в степень

: задание диапазона

Помимо этих операций, в OpenOffice.org Calc доступен обширный набор функций следующих категорий:

• работа с базами данных;

• обработка времени и дат;

• финансовые;

• информационные;

• логические;

• математические;

• работа с массивами;

• статистические;

• текстовые;

• дополнительные.

Для удобства написания формул в OpenOffice.org Calc разработан «Мастер функций». Чтобы вызвать его, нажмите кнопку «Мастер функций» слева от строки ввода или Вставка → Функция.

Рис. 2. Диалоговое окно Мастера функций

В окне Мастера можно набирать функции и проверять правильность их набора; список доступных функций зависит от того, какую категорию вы выбрали. Кроме перечисленных выше категорий, для удобства добавлены «Все» и «Недавно использованные».

Для ввода нужной функции нужно дважды щелкнуть по ее названию или нажать кнопку Далее.

Адресация ячеек

Адрес ячейки состоит из имени столбца и номера строки рабочего листа (например А1, ВА55).

При создании формулы Calc использует адреса ячеек, которые входят в формулы. Адреса указываются с помощью ссылок – относительных или абсолютных. Благодаря ссылкам данные, находящиеся в разных частях листа, могут использоваться в нескольких формулах одновременно.

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

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

Рис. 3. Пример относительной ссылки

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

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

Примеры записи ссылок:

абсолютные –$A$1, $F$10, относительные – A1, F10, смешанные – $A1, F$10.

Рис. 4. Пример абсолютной ссылки по столбцу и по строке

Рис. 5. Пример абсолютной ссылки

Автозаполнение

В Calc существует функция автозаполнения, полезная при заполнении рядов данных.

Если ввести в две соседние ячейки последовательно два числа, составляющие начало арифметической прогрессии, например, 1 и 3, затем их выделить и, как при копировании, с помощью маркера заполнения протащить их на несколько ячеек, то ряд продолжится: 1, 3, 5, 7 и т.д. Если же надо заполнить ячейки без шага, то достаточно ввести первое число, например, 1 и с помощью маркера заполнения протащить до нужного номера, получим ряд 1, 2, 3 и т.д.

Calc также позволяет вводить и нечисловые последовательности. Например, если ввести в ячейку Январь осуществить описанную выше операцию, то в следующих ячейках появится Февраль, Март и т.д.

Эти последовательности, или списки, можно сформировать самому и дать Calc запомнить их. Для этого необходимо выполнить команду Сервис → Параметры → Списки сортировки → Создать и в окне Элементы записать (разделяя Enter) элементы, составляющие список и нажать Добавить (рис. 6).

Также можно отметить, что маркер заполнения позволяет копировать формат ячейки подобно кнопке Взять форматирование как образец на панели Форматирование.

Рис. 6. Добавление списка автозаполнения

Использование автозамены при вводе

Команда Сервис - Параметры автозамены - Заменить предназначена для автоматической замены одних заданных сочетаний символов на другие в процессе ввода.

Автозавершение вводимой информации

При вводе часто повторяющихся слов программа Calc предлагает завершение набираемого слова. Если продолжение слова вас устраивает, нажмите клавишу Enter и оно будет автоматически завершено.

Чтобы настроить автоввод данных используйте команду Сервис - Содержимое ячейки - Автоввод.

При вводе в ячейку нужного текста нажмите Enter, для просмотра других вариантов используйте клавишу Tab.

Выбор данных из списка

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

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

Построение диаграмм и графиков функций

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

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

Построение графиков и диаграмм осуществляется с помощью Мастера диаграмм. Его вызов производится либо с помощью команды Вставка → Диаграмма, либо щелчком по кнопке Вставить диаграмму в панели инструментов Стандартная.

Создание диаграммы

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

2. Щелкните по кнопке Диаграмма на панели Стандартная. Откроется окно предварительного просмотра диаграммы и окно Мастер диаграмм.

Рис. 7. Первый шаг создания диаграммы

3. В окне Мастер диаграмм в группе Выберите тип диаграммы щелкните по названию нужного типа диаграммы.

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

5. Щелкните по кнопке Далее.

6. В правой части окна в группе Выберите диапазон данных при необходимости активируйте:

Рис. 8. Указание диапазона данных

• Ряды данных в строках – для получения значений из последовательно расположенных строк в выбранном диапазоне;

• Ряды данных в столбцах – для получения значений из последовательно расположенных столбцов в выбранном диапазоне;

• Первая строка как надпись – для использования значений первой строки диапазона в качестве имен рядам данных в столбцах;

• Первый столбец как надпись – для использования значений первого столбца диапазона в качестве имен рядам данных в строках .

7. Щелкните по кнопке Далее.

Рис. 9. Окно задания параметров диапазонов данных

8. На этом этапе создания диаграммы в правой части окна можно отдельно изменить исходный диапазон всего ряда данных, задать для него имя. Можно также изменить диапазон категорий.

9. Щелкните по кнопке Далее.

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

11. Активируйте пункт Показать легенду и выберите место ее отображения: слева, справа, снизу или сверху.

12. Активируйте нужные пункты в группе Отображать сетку: Ось Х, Ось Y и Ось Z. Видимые линии сетки делают более наглядным расположение значений в диаграмме. Расстояние между линиями сетки соответствует настройкам интервала на вкладке Масштаб свойств оси. Для круговых диаграмм линии сетки недоступны.

13. Щелкните по кнопке Готово.

Рис. 10. Результат создания диаграммы

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

Практическая часть

Задание 1.

1. Создайте новую книгу и сохраните в своей папке с названием Расчет заработной платы.

2. Первый лист назовите Зар. Плата и создайте таблицу Расчет заработной платы (рис. 11).

Рис. 11. Структура таблицы

3. Столбец Оклад заполните произвольными данными и установите денежный формат ячеек, используя команду Формат - Ячейки - вкладка Числа - Денежный формат.

4. Составьте формулу для вычисления премии, которая составляет 20 % от оклада. Любая формула начинается со знака =, поэтому перейдите в ячейку I6 и введите формулу =H6*20%.

5. С помощью маркера автозаполнения (черный крестик возле правого нижнего угла выделенной ячейки) скопируйте формулу в область I7: I13.

6. Между столбцами Премия и Подоходный налог вставьте столбец Итого начислено (Вставка - Столбцы) и посчитайте сумму Оклад + Премия.

7. Заполните остальные столбцы таблицы, учитывая, что подоходный налог составляет 13 % от начисленной суммы.

8. Посчитайте сумму к выдаче в долларах, для этого внизу таблицы задайте текущий курс доллара, например 30,05, и в ячейку М6 введите формулу: =L6/$D$17.

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

9. Используя функцию SUM, посчитайте общую сумму к выдаче в рублях, общую сумму к выдаче в долларах и общую сумму подоходного налога (Вставка - Функция).

Для подсчета общей суммы к выдаче в рублях нужно установить курсор в ячейку К14 и вызвать Мастер функций. В окне Мастера функций в разделе Категории выбрать Математические и в списке Функция найти SUM. При двойном щелчке по названию функции откроются параметры данной функции:

Устанавливаем курсор в поле Число 1 и выделяем диапазон ячеек К6:К13. При нажатии кнопки ОК в ячейке К14 отобразится результат суммирования.

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

11. Постройте диаграмму Заработная плата сотрудников предприятия (рис. 12). Выделите одновременно столбцы Ф.И.О. и Сумма к выдаче (удерживая клавишу Ctrl) и в меню Вставка выберите команду Диаграмма.

Рис. 12. Диаграмма Заработная плата сотрудников предприятия

12. Постройте круговую диаграмму, показывающую соотношение между общей суммой к выдаче и суммарным подоходным налогом и поместите ее на Листе 2(рис. 13).

Рис. 13. Пример оформления круговой диаграммы

Задание 2.

1. В созданной рабочей книге откройте новый лист.

2. Дайте рабочему листу имя «Ведомость 1».

3. Постройте таблицу: