Лабораторная работа № 1. Визуализация

Экономической информации в табличном

И графическом виде

Цель работы: изучение возможностей MS Ехсеl по отображению экономической информации: создание сводных таблиц и диаграмм.

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

Задача 1. Изучение технологии создания сводных таблиц

По одной таблице данных

Исходные данные – перечень банковских вкладов физических лиц – приведены на рис. 2.1.1.

 

	А	B	C	D	E
	Фамилия	Тип вкл	Размер	Отделение	Примечание
	Тронина	Текущий		Северное	Выехал
	Югова	Депозит		Северное
	Золотухина	Текущий		Центральное
	Иванов	Текущий		Западное
	Клюкин	Текущий		Северное
	Казаков	Депозит		Центральное
	Рябов	Депозит		Центральное
	Попов	Депозит		Западное
	Огородов	Текущий		Северное
	Сухих	Депозит		Северное	Перевел в др. отд
	Панфилова	Текущий		Северное
	Корякова	Текущий		Северное
	Гончарова	Текущий		Центральное
	Корсакова	Депозит		Центральное	Сменил адрес
	Русских	Депозит		Центральное
	Жевлаков	Текущий		Западное
	Пинегин	Текущий		Центральное
	Красовский	Депозит		Центральное
	Потанин	Текущий		Центральное

 

Рис. 2.1.1. Исходные данные для задачи 1

 

Порядок выполнения задания

 

1. Введите исходные данные на рабочем листе во вновь созданном файле MS Excel. Присвойте данному листу имя «Вклады» (на ярлыке листа Excel, находящемуся внизу окна рабочей книги, выполните двойной щелчок мышью, после чего старое имя листа замените на требуемое).

2. Определение количества вкладов, хранящихся в различных отделениях банка (первый способ построения сводных таблиц).

2.1. Запустите Мастер сводных таблиц командой Данные®Сводная таблица. В появляющихся последовательно трёх диалоговых окнах выберите следующие настройки (рис. 2.1.2). В последнем диалоговом окне Шаг 3 из 3 нажмите кнопку «Макет».

Рис. 2.1.2. Последовательность настроек

для создания сводной таблицы

 

2.2. В окне Макета справа в виде кнопок представлены заголовки полей исходной базы данных (заголовки столбцов исходной таблицы), по центру – «заготовка» сводной таблицы, которая путём перетаскивания кнопок приобретает необходимую структуру, в данном случае кнопку «Отделение» перетащите в поле строк, а кнопку «Размер вклада» – в поле Данные макета сводной таблицы.

2.3. В поле данных после перетаскивания появляется кнопка Сумма по полю раз, нажатием на которую можно выбрать способ обработки данных для сводного отчёта, в данном случае Кол-во значений по полю Размер.

2.4. После выхода из окна Макет и завершения работы Мастера сводных таблиц (кнопка «Готово») в окне MS Excel появляется сводная таблица, изображённая на рис. 2.1.3.

 

Кол-во значений по полю Размер
Отделение	Всего
Западное
Северное
Центральное
Общий итог

 

Рис. 2.1.3. Результаты отображения

количества вкладов по отделениям банка

 

3. Определение суммарного размера по каждому виду вкладов по отделениям банка (второй способ построения сводных таблиц).

3.1. Запустите заново Мастер создания сводных таблиц. На предложение Excel создать новый отчёт на основе уже существующего ответьте «Нет», т.е. создайте независимый отчёт. Окно Макет не открывайте, а сразу завершите диалог Мастера сводных таблиц командой Готово. При этом на рабочем столе появится новый пустой макет сводной таблицы, а в панели инструментов «Сводные таблицы» будет выведен список полей таблицы исходных данных (рис. 2.1.4).

Рис. 2.1.4. Режим интерактивного заполнения сводной таблицы

 

3.2. Заполнение сводной таблицы выполняется аналогично первому способу перетаскиванием кнопок из панели «Сводные таблицы» в соответствующие поля заготовки таблицы на листе Excel. Преимуществом данного способа является интерактивное заполнение сводной таблицы, которое можно интерактивно откорректировать (перетаскиванием полей сводной таблицы на другие поля или, для удаления поля, на любое свободное от заготовки таблицы место на рабочем листе). В одно поле сводной таблицы могут быть при необходимости перемещены несколько полей из таблицы исходных данных (см. рис. 2.1.5 – поля «Отделение» и «Тип вклада» вместе размещены в поле строк сводной таблицы). Кроме того, Excel не требует заполнять все поля заготовки сводной таблицы, необходимо лишь наличие двух полей, одно из которых – поле Данных.

3.3. Результатом работы по п. 3 должны быть таблица, изображённая на рис. 2.1.5.

Рис. 2.1.5. Результат отображения объёма различных типов вкладов

по отделениям банка

 

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

 

Рис. 2.1.6. Результат отображения объёма различных типов вкладов

по отделениям банка

 

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

4.2. При изменении таблицы исходных данных Excel не гарантирует автоматическое обновление сводных таблиц и диаграмм. Для актуализации данных сводных таблиц выберите в контекстном меню сводной таблицы или на панели инструментов «Сводные таблицы» функцию «Обновить данные». Проверьте работоспособность данной функции.

 

Задача 2. Изучение технологии создания сводных таблиц

по нескольким однотипным таблицам исходных данных

Исходные данные – сведения об объёмах продаж распространителей-работников торговой фирмы за 3 месяца. Структура таблицы с месячным объёмом продаж изображена на рис. 2.1.7.

 

	Чингисханов	Батыев	Узбеков	Тимуров	Тохтамышев
Парфюмерия
Быт. Товары
Продовольствие
Топливо
Расх. Мат-лы
Электроника

 

Рис. 2.1.7. Структура таблицы исходных данных

 

Порядок выполнения задания

 

1. Создайте на новом листе структуру таблицы продаж по образцу (рис. 2.1.7). Оформите таблицу по своему усмотрению.

2. Присвойте рабочему листу с таблицей имя «Продажи январь».

3. Скопируйте лист «Продажи январь» два раза, назовите полученные листы «Продажи февраль» и «Продажи март». Щёлкните правой кнопкой мыши по ярлычку «Продажи январь». В контекстном меню выберите команду Переместить/Скопировать. В открывшемся диалоге установите флажок Создать копию и нажмите кнопку ОК. В рабочей книге появится копия листа «Продажи январь» с названием «Продажи январь (2)».

4. Заполните таблицы рабочих листов «Продажи январь», «Продажи февраль», «Продажи март» числовыми значениями по своему усмотрению. Желательно выбирать численные значения одного порядка, значения объёмов продаж на соседних листах должны различаться.

5. Создайте общую сводную таблицу по рабочим листам «Продажи январь», «Продажи февраль», «Продажи март». Выполните команду Данные ® Сводная таблица. В первом диалоговом окне укажите, что таблица создаётся на основе данных, расположенных в нескольких диапазонах консолидации, а вид создаваемого отчёта – сводная таблица. Во втором диалоговом окне укажите, что необходимо создать одно поле сводной таблицы. В следующем диалоговом окне укажите диапазоны ячеек, где находятся исходные данные для таблицы: выделите мышью диапазон ячеек А1:F7 на листе «Продажи январь» и щёлкните кнопку Добавить. Такие же диапазоны с листов «Продажи февраль» и «Продажи март» добавьте в список диапазонов исходных данных аналогично. В последнем диалоговом окне Мастера создания сводных таблиц поставьте переключатель в положение, соответствующее созданию сводной таблицы на новом листе. Здесь же дайте этому листу имя «Сводная таблица». Структура получившейся сводной таблицы должна быть такой же, как на рис. 2.1.8.

 

 

Страница1	(Все)
Сумма по полю Значение	Столбец
Строка	Батыев	Тимуров	Тохтамышев	Узбеков	Чингисханов	Общий итог
Быт. Товары
Парфюмерия
Продовольствие
Расх. Мат-лы
Топливо
Электроника
(пусто)
Общий итог

 

 

Рис. 2.1.8. Структура сводной таблицы для задачи № 2

 

6. Поменяйте местами методом перетаскивания заголовки Строка и Страница 1 в полученной сводной таблице (ячейки А1 и А4), при этом в ячейках А6 – А8 появятся надписи Объект1, Объект2 и Объект3. Вручную в строке формул переименуйте их в названия месяцев, по которым составлялась сводная таблица, в соответствии с численными значениями объёмов продаж, расположенными в соответствующих строках.

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

8. Постройте сводную диаграмму по Вашей сводной таблице. Для этого однократно щёлкните левой кнопкой мыши по любой из ячеек данных сводной таблицы и выполните команду Вставка ® Диаграмма.

9. Покажите полученную рабочую книгу MS Excel преподавателю.