Лабораторная работа LibreOffice calc
материал

СОЗДАНИЕ                 И                 ЗАПОЛНЕНИЕ                 ТАБЛИЦЫ СРЕДСТВАМИ LIBREOFFICE CALC

Цель работы: сформировать навык выполнения основных операций по созданию и заполнению электронной таблицы постоянными данными и формулами (работа с листами, ввод и форматирование данных различных типов, использование автозаполнения, «Мастера функций» и т.д.).

Теоретические сведения

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

Постоянные значения – это числа, символы, текст. В ячейку константа записывается следующим образом: курсор устанавливается в ячейку и с клавиатуры вводится значение. Редактирование введенного значения проводится после установки курсора в нужную ячейку, а далее следует либо нажать клавишу , либо щелкнуть кнопкой мыши в строке ввода в нужном месте.

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

Формула всегда начинается с символа равно (=).

Будьте внимательны! Ссылки на ячейки, используемые в формулах, следует вводить на английском языке.

Редактируются формулы аналогично постоянным данным.

Ход работы

1. Ввод данных. Автозаполнение.

1. Загрузите электронные таблицы Calc, используя Пуск / Программы / LibreOffice.
2. Откройте справочную систему (пункт меню Справка). Ознакомьтесь со структурой справочной системы. Ее разделы вы можете использовать в случае затруднений при выполнении лабораторных работ.
3. Переименуйте Лист 1. Для этого щелкните на ярлыке листа правой кнопкой, выберите из контекстного меню команду Переименовать и введите новое имя «Задание 1».
4. Начиная с ячейки А1 введите заголовки столбцов будущейтаблицы в соответствии с рис. 30.

Рис. 30. Начальный вид таблицы

5. Отрегулируйте ширину столбцов.

Это можно сделать автоматически командой Формат / Ширина или Формат / Оптимальная ширина. Также ширину можно отрегулировать вручную, установив курсор на границе между столбцами (курсор при этом превратится в двустороннюю стрелочку) и растащив столбец до нужной ширины. 1.6. В ячейку А2 введите первый тип компьютера: Тип

1.

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

собой маленький квадратик, расположенный в правом нижнем углу активной ячейки (рис. 31). При наведении на него курсора он превращается в черный крестик.

Рис. 31. Маркер автозаполнения

Для того чтобы заполнить значениями типов компьютеров несколько ячеек:

• щелкните по ячейке А2;
• подведите указатель мыши к маркеру Автозаполнения так, чтобы указатель превратился в черный крестик;
• нажмите левую кнопку мыши и протащите маркер Автозаполнения до нужной ячейки (до ячейки А11). Обратите внимание, что при опускании вниз на каждую ячейку около нее всплывает будущее значение: Тип 2, Тип 3

и т.д.; и

• отпустите кнопку мыши, ячейки заполнятся значениями автоматиче-ски (рис. 32).

Рис. 32. Вид ячеек, заполненных через функцию «Автозаполнение»

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

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

Рис. 33. Промежуточный вид таблицы

2. Рассчитайте общую стоимость закупки (в $), используя метод ввода формул вручную.

Для того чтобы рассчитать стоимость закупки компьютеров Типа 1 (в $)

методом ввода формул вручную:

• убедитесь, что выбран английский язык;
• установите курсор в ячейке D2;
• введите знак равенства (=), а затем вручную напечатайте формулу:

В2*С2;

• обратите внимание, что все действия повторяются выше в строкеформул (рис. 34);

Рис. 34. Ввод формулы вручную

• для завершения ввода формулы нажмите клавишу  или кнопку  на панели формул. Убедитесь, что в ячейке D2 появилось числовое значение 6500.

2.3. Рассмотрим более рациональный способ ввода формул, которым рекомендуем пользоваться в дальнейшем – метод ввода формул путем указания ячеек.

Рассчитайте общую стоимость закупки (в $). Для этого:

• установите курсор в ячейке D3;
• щелкните в строке формул и введите знак равенства (=);
• щелкните по ячейке В3. Убедитесь, что вокруг ячейки В3 появиласьактивная рамка, а в строке формул отобразился адрес ячейки В3 (рис. 35);

Рис. 35. Ввод формулы путем указания ячеек

• продолжите ввод формулы, напечатав с клавиатуры знак умножения (*);
• щелкните по ячейке С3. Убедитесь, что ее адрес также отразился встроке формул;
• для завершения ввода формулы нажмите клавишу  или кнопку  на панели формул. Убедитесь, что в ячейке D3 появилось числовое значение 8000.

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

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

Рассмотрим на примере их особенности.

3.1. Относительные ссылки

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

• щелкните по ячейке D3;
• установите курсор на маркер автозаполнения;
• нажмите левую кнопку мыши и, не отжимая, протащите формулувниз до конца списка;
• отпустите левую кнопку;
• убедитесь, что в каждой строке программа изменила ссылки на ячейкив соответствии с новым положением формулы (в выбранной на рис. 36 ячейке D11 формула выглядит =В11*С11) и что все ячейки заполнились соответст-вующими числовыми значениями.

Рис. 36. Вид формулы с относительными ссылками на ячейки

Это стало возможным, так как по умолчанию Calc создает относительные ссылки, которые изменяются при копировании в зависимости от нового положения формулы. Изменение адреса происходит по правилу относительной ориентации клетки с исходной формулой и клеток с аргументами (на сколько ячеек влево, вправо, вверх или вниз переместилась формула, на столько изменится имя столбца и номер строки). Относительные ссылки имеют вид А1, В3.

3.2. Абсолютные ссылки

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

• установите курсор в ячейке Е2;
• введите формулу =С2*В27;
• убедитесь, что получилось числовое значение 40040;
• попробуйте распространить формулу вниз на весь список с помощьюмаркера автозаполнения. Убедитесь, что везде получились нули! Это произошло потому, что при копировании формулы относительная ссылка на курс доллара в ячейке В27 автоматически изменилась на В28, В29 и т.д. А по-скольку эти ячейки пустые, то при умножении на них получается 0. Таким образом, исходную формулу перевода цены из долларов в рубли следует изменить так, чтобы ссылка на ячейку В27 при копировании не менялась.

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

• Пересчитайте столбец Е: удалите все содержимое диапазона ячеекЕ2:Е11, введите в ячейку Е2 формулу = С2*$В$27.
• С помощью маркера автозаполнения распространите формулу внизна весь список. Просмотрите формулы и убедитесь, что относительные ссылки изменились, но абсолютная ссылка на ячейку В27 осталась прежней. Убедитесь, что цена рассчитывается правильно.

Существует также понятие смешанной (частично абсолютной) ссылки: если при копировании и перемещении не меняется имя столбца или номер строки. При этом символ $ в первом случае ставится перед номером строки, а во втором – перед наименованием столбца. Например, В$5, D$12 – частичная абсолютная ссылка по строке; $В5, $D12 – частичная абсолютная ссылка по столбцу.

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

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

Все функции имеют одинаковый формат записи и включают в себя две части – имя функции и ее аргументы. Например: =SUM(А1;C1:D5). Аргументы – это данные, которые используются функцией для получения результата. Аргументом функции могут быть числа (константы), ссылки на ячейки (диапазоны), текст, формулы, другие функции, логические значения и др. Аргументы записываются в круглых скобках после имени функции и разделяются точкой с запятой ( ; ). Если аргументов нет, то скобки остаются пустыми. Некоторые функции могут иметь несколько аргументов.

Функции вводятся обычным набором с клавиатуры или более предпочтительным способом – с помощью Мастера функций. Рассмотрим оба эти метода на примерах.

1. Рассчитайте итог по столбцу «Количество», используя функциюSUM, методом ввода функций вручную.

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

Для расчета итога по столбцу «Количество»:

• установите курсор в ячейку В13;
• напечатайте с клавиатуры формулу =SUM(B2:B11);
• нажмите клавишу  и убедитесь, что в ячейке В13 появилось числовое значение 75.

2. Рассчитайте итог по столбцу «Цена, $», используя средство Мастер функций.

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

Для его открытия используются:

• команда Вставка / Функция;
• кнопка Мастер функций на панели формул (рис. 37).

Рис. 37. Кнопка «Мастер функций» на панели формул

Для расчета итога по столбцу «Цена, $»:

• установите курсор в ячейке С13;
• вызовите диалоговое окно Мастер функций одним из указанных выше способов;
• в поле Категория выберите Все;
• в поле Функция найдите SUM. Обратите внимание, что при выборе функции в левой части диалогового окна появляется список ее аргументов вместе с кратким описанием;
• для ввода аргументов суммы нажмите кнопку Далее;
• в поле Число 1 можно ввести сразу весь диапазон суммирования С2:С11 (диапазон можно ввести с клавиатуры, а можно выделить на листе ле-вой кнопкой мыши, и тогда он отобразится в формуле автоматически) (рис. 38);

Рис. 38. Расчет суммы через Мастер функций

• обратите внимание на кнопку сворачивания диалогового окна , расположенную в правой части поля Число 1. Это приведет к временному сворачиванию окна, в результате чего будет виден весь рабочий лист;

• щелкните по кнопке ОК, убедитесь, что в ячейке С13 появилось числовое значение 11704.

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

Адреса ячеек и соответствующие им расчетные функции

5. Форматирование данных.

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

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

Некоторые операции форматирования Calc выполняет автоматически. Например, если ввести в ячейку значение 10 %, то программа будет знать, что вы хотите использовать процентный формат, и применит его автоматически. Аналогично если вы используете пробел для отделения в числах тысяч от сотен (например, 123 456), Calc применит форматирование с этим разделителем автоматически. Если вы ставите после числового значения знак денежной единицы, установленный по умолчанию, например «руб.», то к данной ячейке будет применен денежный формат.

Для установки форматов ячеек предназначено диалоговое окно Формат ячеек.

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

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

Кроме этого диалоговое окно Формат ячеек содержит несколько вкладок, предоставляющих пользователю различные возможности для форматирования: Шрифт, Эффекты шрифта, Выравнивание, Обрамление, Фон, Защита ячейки.

5.1. Измените формат диапазона ячеек С2:С11 на Денежный:

• выделите диапазон ячеек С2:С11;
• щелкните внутри диапазона правой кнопкой мыши;
• выберите команду Формат / Ячейки;
• на вкладке Число выберите категорию Денежный;
• в перечне Формат выберите USD $ Английский (США); – параметр Дробная часть укажите равным 0; – нажмите кнопку ОК (рис. 39).

Рис. 39. Установка «Денежного» формата ячеек

5.2. Аналогичным образом измените формат для столбцов «Общая стоимость закупки, $», «Цена, руб.», «Общая стоимость закупки, руб.». Также измените формат для ячеек итога со средней, минимальной и максимальной ценами. Для рублевых данных используйте формат RUB руб. русский и дробную часть укажите равной 1.

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

Увеличьте ширину столбца. 6.        Оформление таблиц.

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

6.1. Добавьте заголовок к таблице:

• щелкните правой кнопкой мыши по цифре 1 у первой строки;
• выберите команду Вставить строки;
• выделите диапазон ячеек А1:F1 и выполните команду Формат / Объединить ячейки;
• введите в объединенные ячейки название «Отчет по закупке техниче-ского оборудования»;
• выполните команду Формат / Ячейки. В открывшемся окне установите следующие параметры: Шрифт – Courier New, начертание – полужирный курсив, кегль – 14; Эффекты шрифта – цвет синий; Выравнивание – по центру; Обрамление – положение линий со всех сторон, стиль – сплошная линия 2,5 пт., цвет – зеленый; Фон – желтый 2; – нажмите кнопку ОК.

6.2. Отформатируйте содержимое таблицы:

• примените полужирное начертание к данным в диапазонах ячеек

А2:F2, А3:А28;

• установите Фон и Обрамление для диапазонов ячеек: А14:F14;

А16:С16; А18:Е18; А20:С20; А22:Е22; А24:С24; А26:Е26;

• выделите курс доллара полужирным начертанием и красным цветом;
• диапазон ячеек А2:F12 оформите Обрамлением: внешняя рамка и ли-нии внутри.

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

Рис. 40. Конечный вид таблицы

4. Установите горизонтальную ориентацию листа: Формат / Страница / Страница / Ориентация альбомная.
5. Просмотрите документ в предварительном режиме: Файл / Предварительный просмотр страницы.
6. Сохраните электронную таблицу в личной папке под именем «Работа 1».

        7.        Контрольное задание.

В рабочей книге «Работа 1» перейдите на Лист 2. Переименуйте его в «Задание 2».

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

Рис. 41. Начальный вид таблицы для самостоятельного расчета

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

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

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

1. Что собой представляют электронные таблицы Calc?
2. Что понимают под ячейкой электронной таблицы?
3. Из чего складывается адрес ячейки? Что такое ссылка?
4. Что понимают под диапазоном ячеек?
5. Какие типы данных можно ввести в ячейки рабочего листа?
6. Что понимают под формулой в электронных таблицах?
7. Какие элементы находятся на главном окне Calc?
8. Как переименовать, скопировать, переместить рабочий лист?
9. Какие вы знаете способы для изменения ширины столбцов

(строк)?

10. Как работает маркер автозаполнения?
11. Какие правила следует соблюдать при создании формулы в Calc?
12. В чем особенности относительных, абсолютных и частично абсолютных ссылок на ячейки?
13. Что собой представляет функция в электронных таблицах?
14. Какие шаги следует выполнить, чтобы ввести функцию, используя «Мастер функций»?

Как изменить формат ячеек?