Лабораторная работа 2. Построение диаграмм и графиков. Электронная таблица как база данных. Сортировка и фильтрация данных. Промежуточные итоги

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

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

Как построить диаграмму?

Рассмотрим таблицу 2.1, показывающую рост штатного состава подразделений фирмы. Порядок действий следующий.

Таблица 2.1 – Штат фирмы «Шмидт и сыновья»

Подразделение	Период
	Январь	Октябрь
Офис 1	2	5
Офис 2	7	9

1. Выделяем таблицу вместе с подписями строк и столбцов.

2. Нажимаем кнопку Диаграмма и шаг за шагом проходим все этапы построения диаграммы.

Шаг 1. Выбираем тип диаграммы Гистограмма и вид диаграммы Обычная, ставим флажок «Трехмерный вид» (рисунок 2.1).

Шаг 2. Здесь указывается диапазон ячеек, содержащих данные (рисунок 2.2). Так как таблица была заранее выделена, диапазон уже установлен. Проверьте в окне предварительного просмотра, как выглядит диаграмма. Если она не соответствует желаемому, укажите другой диапазон. Отметьте, как расположены данные – в столбцах или строках. Если в строках, то подписями оси Х будут Январь, Октябрь, а категории Офис 1 и Офис 2 уйдут в легенду диаграммы, если в столбцах, то наоборот.

Шаг 3. На этом шаге можно указать другие данные для диаграммы, а также удалить или добавить ряды.

Рисунок 2.1 – Шаг 1 Мастера диаграмм

Рисунок 2.2 – Шаг 2 Мастера диаграмм

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

Рисунок 2.3 – Шаг 4 Мастера диаграмм

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

Существует также удобный способ добавления новых данных к диаграмме: выделить новые данные в таблице и перетащить их на диаграмму. В соответствующем виде они появятся на поле диаграммы.

Область электронной таблицы можно рассматривать как базу данных. При этом столбцы называются полями, а строки – записями. Столбцам присваиваются имена, которые будут использоваться как имена полей записей.

Существует ряд ограничений, накладываемых на структуру базы данных:

• первый ряд базы данных должен содержать неповторяющиеся имена полей и располагаться в одной строке;

• для имен полей следует использовать шрифт, тип данных, формат, рамку, отличные от тех, которые используются для данных в записях;

• таблицу следует отделить от других данных рабочего листа пустым столбцом и пустой строкой;

• информация по полям должна быть однородной, т.е. только цифры или только текст.

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

Задание 1. Привести все данные в исходной таблице 2.2 к параметрам Земли (не забудьте сменить размерность величин в заголовках новой таблицы!).

В полученной таблице, используя Данные | Сортировка или «горячие клавиши» Сортировка по возрастанию и Сортировка по убыванию:

1) отсортировать данные в порядке убывания количества спутников;

2) отсортировать данные в алфавитном порядке названий планет;

3) отсортировать данные в порядке возрастания массы.

Разместить результаты сортировки (а затем и результаты фильтрации) на различных листах рабочей книги.

Таблица 2.2 – Планеты Солнечной системы

Планета	Период обращения, земной год	Расстояние от Солнца, млн. км	Диаметр, тыс. км	Масса, Тт	Количество спутников
Меркурий	0,241	58	4,9	0,3210^9	0
Венера	0,615	108	12,1	4,8610^9	0
Земля	1	150	12,8	6,010^9	1
Марс	1,881	288	6,8	6,110^8	2
Юпитер	11,86	778	142,6	19,0710^11	16
Сатурн	29,46	1426	120,2	57,0910^10	17
Уран	84,01	2869	49,0	87,2410^9	14
Нептун	164,8	4496	50,2	10,3410^10	2
Плутон	247,7	5900	2,8	0,110^9	1

Задание 2. С помощью Автофильтра осуществить:

1. поиск планет, имеющих диаметр менее 4-х диаметров Земли и массу менее массы Земли;

2. поиск планет, находящихся от Солнца на расстоянии не менее 0,5 расстояния от Земли, имеющих массу от одной до 100 масс Земли;

3. поиск трех планет, имеющих самый большой диаметр.

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

Все – задает все строки.

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

Стандартный фильтр – выбирает строки, удовлетворяющие одному или двум условиям. Эта строка меню открывает диалоговое окно Стандартный фильтр (рисунок 2.4), где в среднем поле под надписью Критерии фильтра, выбирается необходимый оператор сравнения (равно, меньше, больше и т.п.), а в правом поле задается значение сравнения. Оператор используется для второго и третьего условий отбора, соединяющихся с первым знаками логических операций И или ИЛИ.

Рисунок 2.4 – Диалоговое окно Стандартный фильтр

Задание 3. С помощью Расширенного фильтра осуществить поиск планет с периодом обращения более 10 земных лет и количеством спутников не менее 2-х.

Для этого следует сначала определить (создать) три области (диапазона):

1. исходный диапазон  область базы данных;

2. диапазон условий  область, где задаются критерии фильтрации;

3. диапазон результатов  область, в которой будут появляться результаты фильтрации.

Первый диапазон уже определен – это преобразованная исходная таблица (пусть, например, она располагается в ячейках А1:F10). Второй диапазон (пусть это будет А12:F13) необходимо сформировать из строки заголовков таблицы (просто скопировав ее; отметим, что эта строка не должна являться результатом объединения нескольких ячеек!) и строки, где в соответствующих ячейках записываются условия фильтрации (например, >10, >=2 и т.п.). Третий диапазон нужен, если результат фильтрации собираются расположить не на месте исходного диапазона, а в другом месте. Он также состоит из строки заголовков и нескольких пустых строк (например, А15:F17). В эти строки запишется результат фильтрации. Если отведенных строк не хватит, появится предупредительное сообщение, и фильтрация может быть продолжена.

Чтобы выполнить фильтрацию, необходимо воспользоваться командами меню Данные | Фильтр | Расширенный фильтр (рисунок 2.5) и в открывшемся диалоговом окне указать ячейки диапазонов.

Рисунок 2.5 – Диалоговое окно Расширенный фильтр

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

Чтобы восстановить таблицу после работы Автофильтра или Расширенного фильтра, следует выполнить следующие действия: Данные | Фильтр | Удалить фильтр.

Задание 4. Создайте таблицу ежемесячных продаж товаров (таблица 2.3). Товары можно выбирать свои, главное, чтобы их было не менее трех видов и они повторялись от месяца к месяцу. При заполнении последнего столбца воспользуйтесь функцией автозаполнения.

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

2. Постройте промежуточные итоги по суммам и количеству проданного товара по месяцам: Данные | Промежуточные итоги… (рисунок 2.6)

3. Постройте график помесячной реализации товаров определенного наименования (например, картофеля). Подсказка – можно воспользоваться автофильтром.

4. Постройте гистограмму итоговых продаж товаров. Подсказка – сначала постройте соответствующие промежуточные итоги.

Таблица 2.3 – Помесячные продажи товаров

Месяц	Товар	Цена за 1 кг, руб.	Количество, кг	Сумма, руб.
Январь	Картофель	50	130040	6502000
Февраль	Картофель	50	153400	7670000
Март	Картофель	60	164500	9870000
Апрель	Картофель	65	142300	9249500
Январь	Лук	100	34200	3420000
Февраль	Лук	100	54200	5420000
Март	Лук	90	43000	3870000
Апрель	Лук	90	12500	1125000
Январь	Морковь	120	45020	5402400
Февраль	Морковь	120	34802	4176240
Март	Морковь	120	35097	4211640
Апрель	Морковь	110	24734	2720740

Рисунок 2.6 – Диалоговое окно Промежуточные итоги