Анализ данных в БД с помощью сводных таблиц

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

Рассмотрим примеры создания сводных таблиц (рис.4).

Все построения, рассматриваемые в этом разделе пособия, производим на листе Сводные таблицы.

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

Построение сводной таблицы начинаем c того, что щелкаем мышкой в любой клетке таблицы базы данных “Аспиранты” (рис. 1).

Далее выполняем следующую последовательность команд из меню Вставка.

1-й шаг.

Вставка, Сводная таблица:Создание сводной таблицы:Выбрать таблицу или диапазон:Таблица и диапазон: ТБД Аспиранты!$A:$M, Укажите, куда следует поместить отчет сводной таблицы: На существующий лист: Диапазон: СводныеТаблицы!$A$3, Ok.

2-й шаг.

В появившемся на экране окне Макета сводных таблиц выделяем мышкой в списке полей сводной таблицы поле “ Форма обучения ” и размещаем это поле в области “Название строк” макета сводной таблицы. Перетаскиваем кнопку поля “ Ф.И.О. аспиранта ” в область “ Значения ”.

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

В дальнейшем, перед печатью сводной таблицы на принтере, таблицу можно подкорректировать - изменить заголовок, изменить ширину колонок, выбрать другие шрифты. Например, выделим полученную сводную (табл. 1) и установим следующие настройки:

Формат ячеек, Число, Выравнивание:По горизонтали: по центру, По вертикали: по верхнему краю, Отображение: переносит по словам.

Введём в клетку А3 текст: Таблица 1.

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

Пример 2. Создадим сводную таблицу (рис. 5, табл. 2) для получения итоговых данных по национальностям и формам обучения аспирантов. Таблицу 2 создадим на том же листе, что и таблица 1, созданная в предыдущем примере.

Рис. 5. Сводные данные по национальностям и формам обучения аспирантов

Щелкаем мышкой в клетке на листе, на котором размещена таблица базы данных "Аспиранты" (рис.1).

Далее выполняем следующую последовательность команд из меню Вставка.

1-й шаг.

Вставка, Сводная таблица:Создание сводной таблицы:Выбрать таблицу или диапазон:Таблица и диапазон: ТБД Аспиранты!$A:$M, Укажите, куда следует поместить отчет сводной таблицы: На существующий лист: Диапазон: СводныеТаблицы!$A$16, Ok.

2-й шаг.

В появившемся на экране Макете сводных таблиц перетаскиваем мышкой поле “ Форма обучения ” в область “ Названия с толбцов ”. Перетаскиваем поле “ Ф.И.О. аспиранта ” в область “ Значения ” и затем перетаскиваем поле “ Национальность аспиранта ” в область “ Названия строк ”.

В результате этих действий, на листе СводныеТаблицы будет создана новая сводная таблица (табл. 2) с данными о количестве аспирантов разных национальностей, сгруппированными по формам их обучения.

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

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

Отдельные базы данных следует создавать в отдельных книгах (файлах) MS Excel.

3.5 Создание графиков и диаграмм по данным в сводных таблицах

При построении графиков и диаграмм на основе данных в Сводных таблицах используются те же приёмы, что и при построении их на основе данных в обычных таблицах MS Excel.

В качестве примера постройте круговую диаграмму на основе данных в сводной таблице 1 (рис. 4), отражающую соотношение количества очных и заочных аспирантов.

3.6 Обновление данных в сводных таблицах

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

1. Выделим в ТБД Аспиранты запись с данными о Мамлеевой С.И. и удалим её из БД выполняя команду Главная, Удалить, Строки с листа.

2. Кликнем в любой клетке в области любой Сводной таблицы или в ТБД Аспиранты и выполним команду Данные, Обновить, Обновить все.

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