Слайд 2План
1. Создание сводной таблицы
2. Структура и редактирование сводной таблицы
Слайд 31. Создание сводной таблицы
Обрабатывать большие объемы информации и составлять сложные
многоуровневые отчеты достаточно непросто без использования средств автоматизации.
Excel 2010 как
раз и является инструментом, позволяющим упростить эти задачи, путем создания сводных (перекрестных) таблиц данных.
Слайд 4Сводная таблица используется для:
выявления взаимосвязей в большом наборе данных;
группировки данных
по различным признакам и отслеживания тенденции изменений в группах;
нахождения повторяющихся
элементов, детализации и т.п.;
создания удобных для чтения отчетов
Слайд 5Перед тем как создавать отчет сводной таблицы, определимся, что будет
использоваться в качестве источника данных.
Для начала следует создать простую
таблицу с перечислением элементов, которые нужно использовать в отчете.
Способ 1. Создание сводных таблиц, используя стандартный инструмент Excel 2010 «Сводная таблица»
Верхняя строка обязательно должна содержать заголовки столбцов.
Слайд 62. На вкладке «Вставка» выбрать из раздела «Таблицы» инструмент «Сводная
таблица».
Если вместе со сводной таблицей нужно создать и сводную диаграмму
– следует нажать на стрелку в нижнем правом углу значка «Сводная таблица» и выбрать пункт «Сводная диаграмма».
3. В открывшемся диалоговом окне «Создание сводной таблицы» выбрать только что созданную таблицу с данными или ее диапазон. Для этого выделить нужную область.
В качестве данных для анализа можно указать внешний источник: установить переключатель в соответствующее поле и выбрать нужное подключение из списка доступных.
Слайд 84. Далее нужно указать, где размещать отчет сводной таблицы. Удобнее
всего это делать на новом листе.
5. После подтверждения действия нажатием
кнопки «ОК», будет создан и открыт макет отчета.
В правой половине окна создается панель основных инструментов управления — «Список полей сводной таблицы». Все поля (заголовки столбцов в таблице исходных данных) будут перечислены в области «Выберите поля для добавления в отчет».
Следует отметить необходимые пункты и отчет сводной таблицы с выбранными полями будет создан.
Слайд 10Расположением полей можно управлять – делать их названиями строк или
столбцов, перетаскивая в соответствующие окна, а так же и сортировать
в удобном порядке.
Можно фильтровать отдельные пункты, перетащив соответствующее поле в окно «Фильтр».
В окно «Значение» помещается то поле, по которому производятся расчеты и подводятся итоги.
Другие опции для редактирования отчетов доступны из меню «Работа со сводными таблицами» на вкладках «Параметры» и «Конструктор». Почти каждый из инструментов этих вкладок имеет массу настроек и дополнительных функций.
Слайд 12Чтобы применить этот способ, следует сделать доступным инструмент, который по
умолчанию на ленте не отображается:
1. выполнить команду
«Файл» — «Параметры» —
«Панель быстрого доступа».
2. в списке «Выбрать команды из» отметить пункт «Команды на ленте».
3. Выбрать «Мастер сводных таблиц и диаграмм».
4. Нажать кнопку «Добавить».
Иконка мастера появится вверху, на панели быстрого доступа.
Способ 2. Создание сводной таблицы с использованием инструмента
«Мастер сводных таблиц и диаграмм»
Слайд 14Кликниуть по иконке мастера в панели быстрого допуска.
В диалоговом окне
поставить переключатель на нужный пункт списка источников данных:
«в списке или
базе данных Microsoft Excel» — источником будет база данных рабочего листа, если таковая имеется;
«во внешнем источнике данных» — если существует подключение к внешней базе, которое нужно будет выбрать из доступных;
«в нескольких диапазонах консолидации» — если требуется объединение данных из разных источников;
«данные в другой сводной таблице или сводной диаграмме» — в качестве источника берется уже существующая сводная таблица или диаграмма.
Слайд 152. После этого выбирается вид создаваемого отчета – «сводная таблица»
или «сводная диаграмма (с таблицей)».
Если в качестве источника выбран текущий
документ, где уже есть простая таблица с элементами будущего отчета, задайте диапазон охвата — выделите курсором нужную область.
Далее выбирается место размещения таблицы — на новом или на текущем листе, и нажмите «Готово». Сводная таблица будет создана.
Если же необходимо консолидировать данные из нескольких источников, надо установить переключатель в соответствующую область и выбрать тип отчета. А после нужно будет указать, каким образом создавать поля страницы будущей сводной таблицы: одно поле или несколько полей.
Слайд 16При выборе «Создать поля страницы» прежде всего придется указать диапазоны
источников данных: выделите первый диапазон, нажмите «Добавить», потом следующий и
т.д.
Для удобства диапазонам можно присваивать имена. Для этого выделить один из них в списке и указать число создаваемых для него полей страницы, потом можно задать каждому полю имя (метку).
После этого выделить следующий диапазон и т.д.
После завершения нажать кнопку «Далее», выбрать месторасположение будущей сводной таблицы – на текущем листе или на другом, нажать «Готово» и отчет, собранный из нескольких источников, будет создан.
Слайд 172. Структура и редактирование сводной таблицы
Сводная таблица состоит из четырех
областей.
В зависимости от того, куда будут помещены данные, внешний вид
сводной таблицы будет меняться.
Слайд 18Область значений
В этой области происходят все расчеты исходных данных.
Как правило,
в это поле перетаскиваются данные, которые необходимо рассчитать – итоговая
площадь территории, средний доход на душу населения и т.д.
Слайд 19Область строк
Исходные данные перенесенные в это поле, размешаются в левой
части сводной таблицы и представляют из себя уникальные значения этого
поля.
Как правило область строк имеет хотя бы одно поле, хотя возможно его наличие без полей вовсе.
Сюда обычно помещают данные, которые необходимо сгруппировать и категорировать, например, название округа или продуктов.
Слайд 20Область столбцов
Область столбцов содержит заголовки, которые находятся в верхней части
сводной таблицы
Область столбцов идеально подходит для создания матрицы данных или
указания временного тренда.
Слайд 21Область фильтров
В верхней части сводной таблицы находится необязательная область фильтров
с одним или более полем.
В зависимости от выбора фильтра
меняется внешний вид сводной таблицы.
Если необходимо изолировать, или наоборот, сконцентрироваться на конкретных данных, то необходимо поместить данные в это поле.
Слайд 22Область значений
Область строк
Область столбцов
Область фильтров
Слайд 23Можно добавлять необходимые поля в сводную таблицу простым перетаскиванием диапазонов
с названиями в одну из четырех областей сводной таблицы.
Если диалоговое
окно Поля сводной таблицы не появилось, следует щелкнуть правой кнопкой мыши на любом месте сводной таблицы и выбрать
Показать список полей.
Слайд 24*Если ставить галочки напротив полей с текстовыми значениями, Еxcel по
умолчанию помещает эти значения в область строк, с числовыми значениями
– в область значений.
Слайд 25Использование фильтров в сводной таблице
Часто возникает необходимость создать отчет для
разных типов данных (к примеру, проанализировать только конкретные округи).
Вместо того,
чтобы тратить время на изменения исходных данных, следует воспользоваться областью Фильтры.
Перетаскивая необходимые поля в область Фильтры можно менять внешний вид сводной таблицы.
Слайд 26Со временем исходные данные изменяются, к ним добавляются новые строки
и колонки.
Для обновления сводной таблицы надо нажать правой кнопкой мыши
на любом месте таблицы и выбрать Обновить.
Обновление сводной таблицы
Слайд 27Бывают ситуации, когда структура исходных данных меняется, к примеру, вам
необходимо добавить новые строки в таблицу с данными.
Этот тип изменений
повлияет на диапазон источника данных, и об этом необходимо сообщить сводной таблице.
Простое обновление не сработает, так как в данном случае необходимо расширить диапазон источника данных.
Слайд 28Щелкаем левой кнопкой мыши в любом месте сводной таблицы.
Во вкладке
Работа со сводными таблицами
-> Анализ –> Источник данных.
Слайд 29В появившемся диалоговом окне
Изменить источник данных сводной таблицы
следует задать
изменившийся диапазон данных.