лаба. Сводные таблицы
 Скачать 456.61 Kb.
|
|
1 ЛАБОРАТОРНАЯ РАБОТА №14 Тема: Сводные таблицы Сводная таблица – это динамическая таблица итоговых данных, извлеченных или рассчитанных на основе информации, содержащейся в базе данных или списках. Источником данных для сводной таблицы служит обыкновенная таблица, информацию в которой необходимо перераспределить или организовать в более удобном для анализа виде. Сводная таблица может содержать все или только часть данных исходной таблицы, при этом данные не меняются, а только перераспределяются. Сводные таблицы могут использоваться для быстрого подведения общих и промежуточных итогов, отбора и обобщения только необходимых данных, выполнения дополнительных вычислений и т.д. На основе сводных таблиц можно построить сводную диаграмму. Создание сводной таблицы Для создания сводной таблицы необходимо выполнить следующие действия: на вкладке Вставка в группе Таблицы выбрать Сводная таблица в появившемся диалоговом окне Создание сводной таблицы (рис.14.1) в поле Таблица или диапазон указать источник; если исходная таблица находится на активном листе и какая-либо ее ячейка выделена, то Excel заполняет это поле автоматически; в области Укажите, куда следует поместить отчет сводной таблицы указать местоположение сводной таблицы (на новый или существующий лист) и нажать кнопку ОК. Также можно воспользоваться Мастером сводных таблиц. Чтобы добавить его на панель быстрого доступа нужно выбрать Настроить панель быстрого доступа – Другие команды – в поле Выбрать команды из выделить Все команды – Мастер сводных таблиц и диаграмм – Добавить (на панель быстрого доступа). Рисунок 14. 1. Создание сводной таблицы 2 В результате этих действий на рабочем листе (новом или существующем) появляется пустая сводная таблица с областью задач Список полей сводной таблицы (рис.14.2) и добавляются две вкладки для работы со сводными таблицами Анализ и Конструктор. В верхней части области задач находится список полей, совпадающих с заголовками столбцов исходной таблицы, в нижней части — макет сводной таблицы, предназначенный для изменения порядка следования полей и их положения. Макет включает области: Фильтр отчета, Названия столбцов, Названия строк, Значения. Рисунок 14. 2. Поля сводной таблицы В зависимости от конкретной задачи следует выбрать поля и разместить их в областях макета. Для этого надо щелкнуть правой кнопкой мыши на названии поля, затем в контекстном меню выбрать одну из команд: Добавить в фильтр отчета, Добавить в названия строк, Добавить в названия столбцов или Добавить в значения. Заполнить области макета можно и другим способом: щелкнуть на имени поля и, удерживая нажатой левую кнопку мыши, перетащить его в одну из областей макета. Область Фильтр отчета используется для фильтрации содержимого всей сводной таблицы на основе выбранных элементов фильтра. Области Названия столбцов и Названия строк применяются для вывода полей в виде соответственно столбцов и строк отчета. В области Фильтр отчета, Названия столбцов и Названия строк, каждое поле может помещаться только один раз. Область Фильтр отчета может оставаться незаполненной. Для удаления какого-либо поля из сводной таблицы, его кнопку нужно перетащить за пределы макета. В область Значения помещают поля, по которым при создании сводной таблицы будут производиться вычисления с помощью одной из функций: сумма, среднее, количество, максимум и др. Для подведения итогов по одному и тому же полю с помощью разных функций в область Значения это поле должно помещаться несколько раз. Для выбора функции и настройки параметров полей, помещенных в область Значения, следует щелкнуть левой кнопкой мыши на стрелке, расположенной справа от названия поля и в 3 раскрывающемся списке выбрать пункт Параметры полей значений, а затем в появившемся диалоговом окне Параметры поля значений на вкладке Операция выбрать нужную операцию. В этом же окне можно изменить формат представления результатов (кнопка Числовой формат) и выбрать функцию для дополнительных вычислений (вкладка Дополнительные вычисления). В сводных таблицах автоматически подводятся общие итоги по строкам и столбцам. Если они не нужны, и нет желания загромождать таблицу дополнительной информацией, можно итоговые строки и столбцы убрать. Для этого надо выбрать команду: Анализ – Сводная таблица – Параметры и в появившемся диалоговом окне Параметры сводной таблицы на вкладке Итоги и фильтры снять флажки в полях Показывать общие итоги для строк и Показывать общие итоги для столбцов (или в одном из них). Рисунок 14. 3. Параметры сводной таблицы На основе готовой сводной таблицы можно построить сводную диаграмму (рис.14.4) с помощью команды: Анализ – группа Сервис – Сводная диаграмма. В появившемся окне Вставка диаграммы надо выбрать нужный тип диаграммы. Поля сводной таблицы в области Названия строк станут полями осей для сводной диаграммы, поля в области Названия столбцов — полями легенды сводной диаграммы. Поля в области Значения станут значениями, которые будут определять высоту или положение графических маркеров в зависимости от выбора типа диаграммы. Поля в области Фильтр отчета останутся, как и прежде, общим фильтром для сводной диаграммы. 4 Рисунок 14. 4. Сводная диаграмма Готовые сводные таблицы и диаграммы можно изменять (модифицировать их структуру) — добавлять и удалять поля, изменять расположение полей и т.д. Сводные таблицы и диаграммы можно форматировать теми же способами, которыми выполняется форматирование обычных таблиц и диаграмм Excel. Сводные таблицы служат только для отображения информации, поэтому ручная правка данных в них невозможна (названия полей можно изменять). При изменении данных в исходной таблице сводная таблица автоматически не обновляется. Для обновления сводной таблицы следует выделить в ней любую ячейку и вызвать команду: Анализ – группа Данные – Обновить. Для удаления всей сводной таблицы надо вызвать команду: Анализ – группа Действия – Очистить – Очистить все. Преобразование сводной таблицы в обычную Если необходимо из сводной таблицы получить обычную, нужно кликнуть два раза левой кнопкой мыши по итоговому значению (рис. 14.5). Рисунок 14. 5. Преобразование сводной таблицы в итоговую В результате сводная таблица преобразуется в обычную (рис. 14.6). 5 Рисунок 14. 6. Преобразованная таблица Консолидация данных Консолидация — это объединение данных из одной или нескольких областей данных и вывод их в виде таблицы в итоговом листе. В Excel предусмотрено несколько способов консолидации данных: консолидация данных с помощью формул со ссылками; консолидация данных по расположению; консолидация данных по категориям. Первый способ позволяет объединить данные консолидируемых областей формулами. Для этого надо на итоговом листе создать (или скопировать) надписи для данных консолидации и в соответствующие ячейки ввести формулы, содержащие ссылки на консолидируемые исходные области листов, которые будут участвовать в консолидации. Например, =СУММ(Лист2!B3;Лист3!B3). Консолидация данных по расположению используется, если консолидируемые данные находятся в одном и том же месте разных листов и размещены в одном и том же порядке. Технология консолидации такова: указать левую верхнюю ячейку области размещения консолидируемых данных, вызвать команду: Данные – группа Работа с данными – Консолидация, в диалоговом окне Консолидация выбрать в списке Функция итоговую функцию для обработки данных, в поле Ссылка ввести исходную область для консолидации данных (диапазон ячеек), нажать кнопку Добавить и повторить эти действия для всех диапазонов, данные из которых участвуют в консолидации. Консолидация данных по категориям используется, если данные исходных областей не упорядочены, но имеют одни и те же заголовки (рис.14.7). Рисунок 14. 7. Таблицы для консолидации 6 Технология этой консолидации совпадает с технологией консолидации данных по расположению, но в диалоговом окне Консолидация в группе Использовать в качестве имен следует установить параметры подписи верхней строки и/или значения левого столбца для указания расположения заголовков в исходных областях (рис. 14.8). Рисунок 14. 8. Консолидация данных Установка параметра Создавать связи с исходными данными в диалогом окне Консолидация означает, что между исходными данными и результатами консолидации устанавливается динамическая связь, обеспечивающая автоматическое обновление данных. Автоматическое обновление данных происходит, если исходные данные находятся в пределах одной книги. Если исходные данные расположены в других рабочих книгах, то их обновление будет выполняться командой: Данные – группа Запросы и подключения – Изменить связи. После установки связей нельзя корректировать ссылки на области- источники (добавлять или удалять области-источники). Связи нельзя использовать, если исходная и итоговая области находятся на одном листе. |