Структурирование, консолидация данных

Построение сводных таблиц и диаграмм

Цель Занятия

Занятие помогает получить практические навыки по изучению следующих тем:

· Управление данными, расположенными на разных листах рабочей книги;

· Работа со списками и операции над ними (фильтрация, сортировка);

· использование диалоговых окон для изменения информации в списках;

· Работа со структурой таблицы (создание и удаление);

· формирование таблиц с общими и частными итогами;

· консолидация данных, расположенных на разных листах рабочей книги.

· построение сводных таблиц и сводных диаграмм.

Основные сведения о списках, структуре рабочего

листа, консолидации и сводных таблицах

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

Столбцы списка называются полями, строки – записями.

Ведение списка можно осуществлять в диалоговом окне (форме).

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

В процессе сортировки списка строки переупорядочиваются в соответствии с видом сортировки (по возрастанию или убыванию)

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

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

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

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

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

Содержание Занятия

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

Выполнение Занятия

1. Загрузите программу MS Excel 2013.

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

Таблица 1

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

= F2/E2

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

5. Получите итоговую сумму по столбцу Сумма. Выполните команду Главная/Редактирование/Автосуммирование(∑)/Сумма.

Примечание. В дальнейшем подобная информация будет выводиться в виде: выполните команду

Главная/Редактирование/Автосуммирование(∑)/Сумма

6. Создайте структуру построенной таблицы для скрытия детальных числовых данных. Для этого выделите столбцы с числовыми значениями и выполните следующие действия: Данные/Структура/Группировать/по столбцам.На экране структуры таблицы щелкните кнопку «–», чтобы скрыть столбцы с числами, а затем кнопку «+» для показа скрытой информации:

Пример структуры для скрытия детальных числовых данных

7. Удалите структуру, выделив, ячейки с числовыми значениями и выполнив команду Данные/Разгруппировать/столбцы.

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

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

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

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

Таблица 2

Таблица 3

Таблица 4

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

=ВПР(Март!A3;Заказ!$B$2:$F$11;3;ложь)*B3

(для таблицы 4)

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

· Добавьте новый лист переименуйте его в Консолидация.

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

· Выполните: команду КонсолидациявкладкиДанныегруппы/ Работа с данными.

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

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

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

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

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

Таблица 5

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

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

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

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

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

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

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

· в окне Создание сводной таблицы выберите источник, введите диапазон исходных данных для построения сводной таблицы, например, Заказ!$A$1:$G$11, установите переключатель Новый листи нажмите кнопку ОК;

· постройте сводную таблицу, перетащив в окне Список полей сводной таблицы поле Название в область полей строк, поле Квартал – в область полей столбцов, а поле Сумма – в область значений.

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

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

Примечание. При работе со сводными таблицами на ленте появляется дополнительная вкладка Работа со сводными таблицами – Параметры, Конструктор.

20. Постройте сводную диаграмму (разрезанная круговая) на основе сводной таблицы, выполнив команду Работа со сводными таблицами/ Параметры/Сервис/Сводная диаграмма.

Пример разрезанной круговой диаграммы.

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

22. Отсортируйте данные таблицы Заказ по возрастанию цены. Для этого:

· Выделите ячейку поля Цена;

· Выполните команду Сортировка от А до ЯкнопкиСортировка и фильтрвкладкиГлавнаягруппыРедактирование.

23. Выполните многоуровневую сортировку по двум ключам: сначала по цене, потом по названиям в порядке возрастания значений этих ключей. Для этого:

· установите курсор в область данных таблицы Заказ;

· выполните команду Настраиваемая сортировкавкладкиДанныегруппыСортировка и фильтр;

· в диалоговом окне в область Сортировать по введите первый ключ сортировки «Цена»;

· Нажмите на «Добавить уровень» и введите в значение Затем по« Наименование товара»

· Щелкните кнопку OK

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

· удалите итоговую сумму в столбце Сумма;

· сделайте текущей ячейку поля Квартал;

· Выполните команду Сортировка от новых к старымвкладкиДанныегруппыСортировка и фильтр.

· выполните команду Промежуточный итогвкладки Данные группы Структура;

· в диалоговом окне команды Промежуточные итоги в области «При каждом изменении в» выберите Квартал, в области «Операция» выберите Сумма, в области «Добавить итоги по» выберите Сумма;

· установите флажки Заменить текущие итоги и Итоги под данными;

· щелкните кнопку OK.

25. Аннулируйте промежуточные итоги таблицы Заказ. Для этого:

· установите указатель мыши на таблицу Заказ;

· выполните команду Промежуточный итогвкладки Данные группы Структура;

· в диалоговом окне команды Промежуточные итоги щелкните по кнопке Убрать все.

26. Выполните подсчет многоуровневых итогов по стоимости выпуска учебников в разрезе авторов и кварталов. Для этого:

· выполните многоуровневую сортировку таблицы Заказ сначала поавторам, а затем по кварталам;

· с помощью команды Промежуточный итогвкладки Данные группы Структура подсчитайте суммарную стоимость выпуска учебников каждого автора;

Пример промежуточных итогов (Задание 26)

· повторно выполните команду Промежуточный итог вкладки Данные группы Структура для подсчета суммарной стоимости продукции в каждом квартале, сняв в диалоговом окне команды флажок Заменить текущие итоги.

27. Используйте автофильтр для вывода в таблице Заказ информации только о книгах, цена которых более 85 руб. Для этого:

· выделите область столбца Цена с данными и заголовком;

· выполните команду Данные/Фильтр;

· нажмите стрелку списка в заголовке столбца Цена, выберите Числовые фильтры/больше;

· введите 85, нажмите OК.

28. Отмените фильтр, выполнив команду Данные/Сортировка и фильтр/Очистить. Чтобы убрать значок автофильтра из заголовка столбца, повторно нажмите Фильтр на вкладке Данные в группе Сортировка и фильтр.

29. Воспользуйтесь расширенным фильтром для поиска в таблице Заказ информации о продукции, количество которой превышает 10000 И фамилии авторов начинаются на букву «С». Для этого:

· скопируйте лист Заказ и переименуйте его в По_критерию;

· в ячейку D15 введите текст «Критерий»;

· создайте таблицу критериев, скопировав имя столбца (поля) Тираж в ячейку D16, а имя столбца Автор в ячейку Е16;

· в ячейки D17 и Е17 введите условия: >10000 и С*;

· создайте заголовки столбцов для результирующей таблицы, скопировав имена всех столбцов основной таблицы в диапазон ячеек А20:G20.

· поместите курсор в пределы основной таблицы и выполните команду Данные/Сортировка и фильтр/Дополнительно;

· в диалоговом окне Расширенный фильтр установите флажок Скопировать результат в другое место, задайте исходный диапазон А1:G11, диапазон условий D16:E17 и диапазон таблицы результатов A20:G20;

· нажмитеОК(ниже представлен вид выполненного задания):

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

30. Если условия отбора должны объединяться логическим оператором ИЛИ, они должны находиться в разных строчках таблицы критерия, например:

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

Для этого:

· создайте новый диапазон условий: в ячейки I4 и K4 поместите заголовок столбца Цена. В ячейки I5 и K5 поместите условия отбора: >=80 и <=100;

· с помощью команды Данные/Сортировка и фильтр /Дополнительновыполните фильтрацию данных, сославшись на диапазон условий I4:K5 и поместив результат фильтрации в свободное место рабочего листа.

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

32. Сохраните рабочую книгу в файле с именем lab3.xls.

ЗАНЯТИЕ № 4