Создание отчетов с помощью сводной таблицы

ЗАНЯТИЕ 4.2

Консолидация данных

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

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

Выполните консолидацию данных

Пример: имеются сводки раскрываемости преступлений за различные отрезки времени.

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

Для этого:

1. Создайте на диске С: в своей папке Работа в Excel документ с именем Занятие 4.2, откройте его.

2. Перенесите на Лист 1 таблицу «Раскрываемость преступлений в крае», представленную на рис. 1.7.1.

 

 

Рис. 1.7.1. Таблица Раскрываемость преступлений в крае

3. Дайте Листу 1 название «7-9_сент», Листу 2 «21-24_сент», на них будут располагаться данные за два временных периода (в нашем случае одинаковые).

4. Лист 3 назовите Консолидация, на нем мы будем формировать, различными способами, сводную картину преступности за два имеющихся временных периода.

5. Выделите ячейку А2 листа консолидация, выберите вкладку Данные → .

6. В появившемся диалоговом окне из раскрывающегося списка Функция выберите Сумма.

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

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

9. Установите флажки напротив соответствующих опций в окне Консолидация (рис. 1.7.2).

 

Рис. 1.7.2. Диалоговое окно Консолидация

 

10. Нажмите кнопку OK, итоговый результат см. на рис. 1.7.3.

 

Рис. 1.7.3. Итоговый результат

 

Создание отчетов с помощью сводной таблицы

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

Сводные отчеты облегчают анализ числовых данных и получение нужных ответов.

Буквально за несколько секунд можно узнать нужную вам информацию.

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

 

Создайте сводный отчет на основании таблицы «Состояние преступности»

Используйте для этого заголовки: Виды преступлений, Зарегистрировано по краю; Раскрыто по горячим следам. Для этого выполните следующие действия:

1. Откройте на диске С: из папки Работа в Excel файл Занятие 3, лист «Состояние преступности».

Примечание! Перед началом работы с отчетом сводной таблицы взгляните на лист Excel и убедитесь, что он подготовлен для создания отчета.

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

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

2. Введите в ячейку А2 – Виды преступлений.

3. Для создания сводной таблицы перейдите на вкладку Вставка, где в группе Таблицы выберите команду Сводная таблица (рис. 1.7.4).

 

 

Рис. 1.7.4. Вкладка Вставка, панель Таблицы, Сводные таблицы

4. Откроется диалоговое окно Создание сводной таблицы (рис. 1.7.5).

 

Рис. 1.7.5. Диалоговое окно Создание сводной таблицы

5. В этом окне отметим Выбрать таблицу или диапазон.

6. В поле Таблица или диапазон укажите свою таблицу ячейки с А2 по D11.

7. Далее выберите размещение таблицы На новый лист.

8. Нажмите ОК, после этого откроется рабочий лист, в котором в Списке полей сводной таблицы будут отображены заголовки таблицы (рис. 1.7.6).

 

Рис. 1.7.6. Рабочий лист для создания сводных отчетов

 

9. В списке полей сводной таблицы выберите поля Виды преступлений, Зарегистрировано по краю; Раскрыто по горячим следам для создания отчета (рис. 1.7.7).

Рис. 1.7.7. Список полей сводной таблицы

10. Распределите выбранные поля, как показано на рис. 1.7.8.

 

 

Рис. 1.7.8. Формирование отчета

 

После этого на рабочем листе отобразится сформированный нами отчет (рис. 1.7.9).

 

 

Рис. 1.7.9. Сформированный отчет

 

11. Мы можем изменять уже готовый отчет по желанию. Для этого в столбце Названия строк откройте раскрывающееся меню, нажав на кнопку . Появится окно для редактирования отчета, в нем отмените пункт В т.ч. Особо тяжких и На улицах и в общественных местах (рис. 1.7.10). Нажмите ОК.

 

 

Рис. 1.7.10. Окно для редактирования отчета

 

Посмотрите, как изменился ваш отчет.

12. Самостоятельно добавьте обратно убранные пункты.

13. Удалите из отчета поля Кражи чужого имущества, Прочие преступления, Умышленные убийства.

14. Нажмите правой кнопкой мыши на сводной таблице → включится режим работы со сводными таблицами → перейдите на вкладку Конструктор.

15. На панели Стили сводной таблицы выберите стиль Средний 1.

16. Параметры стилей сводной таблицы отредактируйте, как на рис. 1.7.11.

 

 

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

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

17. Перейдите на вкладку Конструктор → панель Макет → Общие итоги → Отключить для строк и столбцов (рис. 1.7.12).

Рис. 1.7.12. Отключение общих итогов

Сравните результат с рис. 1.7.13.

 

 

Рис. 1.7.13. Результат работы

! Сохраните на диске С: документ с именем Занятие 4.2 с результатами выполнения заданий в своей папке Работа в Excel.

 

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

1. Что такое консолидация данных, для чего она применяется?

2. Расскажите технологию консолидации данных.

3. Что такое сводный отчет, для чего он нужен?

4. Расскажите технологию создания сводных отчетов.

5. Что обозначает определение Поле в теме Сводные отчеты?

6. Можно ли изменять уже готовый сводный отчет?