Лабораторная работа № 13. Структурирование, консолидация данных, построение сводных таблиц и диаграмм

 

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

Краткое описание теоретической части.

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

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

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

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

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

Порядок выполнения работы.

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

Таблица 1

Номер	Название	Автор	Цена	Тираж	Сумма	Квартал
	Математика	Воробьев				Кв1
	Физика	Воробьев				Кв2
	История	Щеглов				Кв1
	Литература	Щеглов				Кв3
	Химия	Семенов				Кв3
	Биология	Семенов				Кв1
	Философия	Грачев				Кв1
	Экономика	Грачев				Кв3
	Информатика	Воробьев				Кв3
	Психология	Грачев				Кв4

2. Рассчитайте цену одного экземпляра по каждому наименованию книжной продукции путем ввода и последующего копирования формулы.

3. Переименуйте Лист1 в Заказ. Для этого установите указатель на ярлык Лист1, нажмите правую кнопку мыши, в контекстном меню выберите команду Переименовать и вместо прежнего имени листа Лист1 введите новое имя Заказ.

4. Получите итоговую сумму по столбцу Сумма. Для этого установите курсор в ячейку E12 и нажмите кнопку автосуммирования. Введите сформированную формулу нажатием клавиши Enter.

5. Добавьте к существующим листам рабочей книги еще один. Для этого установите указатель на один из ярлыков, нажмите правую кнопку мыши и выберите команду Вставить. В диалоговом окне Вставка выберите значок с названием Лист и нажмите ОК.

6. Переименуйте Лист2, Лист3, Лист4 в Январь, Февраль, Март, так как они будут содержать информацию о реализации книжной продукции за первые три месяца 2001 года (табл. 2,3,4).

7. Сгруппируйте листы Январь, Февраль, Март для ввода общей для них информации (названия столбцов и наименования книг). Группа листов создается щелчком мышью на ярлыке листа при нажатой клавише CTRL.

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

 

 

Январь Таблица 2

Наименование	Количество	Стоимость
Математика
Физика
История
Литература
Химия
Биология
Философия
Экономика
Информатика
Психология

Февраль Таблица 3

Наименование	Количество	Стоимость
Математика
Физика
История
Литература
Химия
Биология
Философия
Экономика
Информатика
Психология

Март Таблица 4

Наименование	Количество	Стоимость
Математика
Физика
История
Литература
Химия
Биология
Философия
Экономика
Информатика
Психология

 

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

10. Используйте консолидацию рабочих листов для получения итоговой информации о продаже литературы в целом за квартал (Январь, Февраль, Март). Для этого:

· добавьте новый лист, переименуйте его в Квартал;

· выделите ячейку на новом листе Квартал, начиная с которой будут размещены итоговые данные (например, A1);

· выполните команду Данные/ Консолидация;

· в диалоговом окне Консолидация выберите в списке функций функцию Сумма;

· в строку Ссылка введите абсолютную ссылку на консолидируемые данные (например, Январь!$A$2:$C$11) и нажмите кнопку Добавить;

· повторите ввод и добавление данных для ввода всей консолидируемой информации (Февраль!$A$2:$C$11 и Март!$A$2:$C$11);

· включите флажок значения левого столбца;

· нажмите кнопку OK.

11. Измените некоторые данные в одном из консолидируемых листов. Изменятся ли данные в итоговой таблице?

12. Установите связанную консолидацию данных. Для этого вставьте новый рабочий лист, переименуйте его в Квартал1, активизируйте ячейку начала формирования итоговой таблицы (например, A1), выполните все положения пункта 13, добавив флажок Создавать связи с исходными данными.

13. В полученной структурированной таблице просмотрите скрытые данные, нажав кнопки «2» или «+».

14. Измените некоторые данные в одном из консолидируемых листов. Изменятся ли данные в итоговой таблице на этот раз?

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

· активизируйте рабочий лист Заказ;

· вызовите мастер сводных таблиц и диаграмм, выполнив команду Вставка/Сводная таблица;

· открывается диалоговое окно создания сводной таблицы. В поле Таблица или диапазон вводится адрес диапазона исходных данных для построения сводной таблицы, например, Заказ!$A$1:$G$11, и щелкните по кнопке Далее;

· в окне Укажите, куда следует поместить отчет сводной таблицы установите переключатель На новый лист;

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

16. Поменяйте местами строки и столбцы сводной таблицы. Для этого щелкните на поле Квартал и перетащите его на поле Название, а поле Название – на место поля Квартал.

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

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

Контрольные вопросы.

1. Как провести консолидацию данных нескольких рабочих листов?

2. Как скрыть ненужные столбцы?

3. Как сортировать данные по одному ключу?

4. Как сортировать данные по нескольким ключам?

Дата защиты ____________ подпись преподавателя______________