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

Одним из наиболее мощных средств Excel по работе с базами данных являются сводные таблицы. Они полезны как для анализа, так и для обобщения информации, хранящейся в базе данных, на рабочих листах, во внешних файлах. Сводные таблицы являются динамическими объектами, позволяющими выводить информацию с различной степенью детализации. Для создания сводной таблицы необходимо выполнить команду Данные, Сводная таблица:

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

o В списке или базе данных Microsoft Excel – если данные берутся с одного рабочего листа;

o Во внешнем источнике данных – если данные берутся из внешней базы данных;

o В нескольких диапазонах консолидации – если данные берутся с нескольких рабочих листов;

o В другой сводной таблице – если сводная таблица создается на основе данных другой сводной таблицы.

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

· В третьем окне необходимо указать структуру сводной таблиц

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

o Строка – для использования данных поля, расположенного в этой области, в качестве заголовков строк;

o Столбец – для использования данных поля, расположенного в этой области, в качестве заголовков столбцов;

o Данные – для суммирования значений поля, расположенного в этой области, в ячейках сводной таблицы;

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

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

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

o В поле ввода Поместить таблицу указывается верхняя левая ячейка таблицы. Если это поле оставить пустым, то в текущую рабочую книгу будет вставлен новый лист со сводной таблицей, левый верхний угол которой расположен в ячейке А1;

o В поле ввода Название таблицы указывается название сводной таблицы;

o Устанавливая флажок Общие итого по столбцам или флажок Общие итоги по строкам, можно подвести итоги по столбцам или строкам в сводной таблице;

o Установка флажка Сохранить данные с макетом таблицы создает дополнительную копию данных, позволяющую быстрее пересчитывать сводную таблицу при ее изменении;

o Установка флажка Автоматически форматировать таблицу позволяет использовать средства автоформата Excel.

После щелчка на кнопке Готов на рабочем листе будет создана сводная таблица.

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