Сводная таблица
Сводные таблицы - одно из наиболее мощных средств Excel по работе с базами данных. Они полезны как для анализа, так и для обобщения информации, хранящейся в БД.
Создадим из нашей БД сводную таблицу для расчета прибыли по каждому товару. Выполним команду Данные Сводная таблица. В первом диалоговом окне Мастер сводных таблиц (рис. 9.13) необходимо установить переключатель в положение, показывающее, откуда берутся данные для сводной таблицы:
в списке или базе данных MS Excel - если данные берутся с одного рабочего листа;
во внешнем источнике данных - если данные берутся из внешней базы данных;
в нескольких диапазонах консолидации - если данные берутся с нескольких рабочих листов;
в другой сводной таблице - если сводная таблица создается на основании данных другой сводной таблицы.
В этом же диалоговом окне указывается вид создаваемого отчета - сводная таблица или сводная диаграмма.
|
Рис. 9.13. Первое диалоговое окно Мастер сводных таблиц |
В рассматриваемом примере переключатель устанавливается в положение В списке или базе данных MS Excel.
В поле Диапазон второго диалогового окна Мастер сводных таблиц (рис. 9.14) необходимо указать диапазон, на основании которого строится сводная таблица. В нашем случае: 'Поставщики товара'!$A$:$I$18.
В третьем диалоговом окне Мастер сводных таблиц (рис. 9.15) необходимо установить переключатель в положение, указывающее, где будет размещена сводная таблица - на новом листе или на уже существующем.
|
Рис. 9.14. Второе диалоговое окно Мастер сводных таблиц |
|
Рис. 9.15. Третье диалоговое окно Мастер сводных таблиц |
Структуру сводной таблицы можно создать, воспользовавшись кнопкой Макет…, третьего диалогового окна Мастер сводных таблиц.
Поля БД, на основании которой строится сводная таблица, представлены в окне создания макета в виде кнопок с названием этих полей (рис. 9.16). Перетаскивая их в соответствующие области, пользователь задает необходимую структуру сводной таблицы.
В окне имеются четыре области:
Строка - для использования данных поля, расположенного в этой области, в качестве заголовка строки;
Столбец - для использования данных поля, расположенного в этой области, в качестве заголовков столбцов;
Данные - для суммирования значений поля, расположенного в этой области, в ячейках сводной таблицы;
Страница - для обеспечения возможности вывода данных сводной таблицы, относящихся только к полю, расположенному в этой области.
|
Рис. 9.16. Диалоговое окно для создания макета сводной таблицы |
В рассматриваемом примере создается сводная таблица, состоящая из строк с наименованием товара и столбцов с названием города из которого этот товар доставлен. В область Данные перемещена кнопка Прибыль. Двойной щелчок по кнопке в области данных, открывает диалоговое окно Вычисление поля сводной таблицы (рис. 9.17), позволяющее выбрать правило по которому подводятся итоги в сводной таблице.
Допустимыми операциями подведения итогов являются: сумма, количество значений, среднее арифметическое, максимальное и минимальное значение, произведение, количество чисел, несмешанное и смешанное отклонение, несмешанная и смешанная дисперсия. В нашем примере в качестве допустимой операции выбрана сумма.
|
Рис. 9.17. Диалоговое окно Вычисление поля сводной таблицы |
Кнопка Параметры… третьего диалогового окна Мастер сводных таблиц позволяет задать некоторые параметры, определяющие вид сводной таблицы (рис. 9.18). Например, в поле Имя можно задать название таблицы. По умолчанию сводные таблицы называются Сводная таблица 1, Сводная таблица 2 и т.д. Устанавливая флажок Общие итоги по столбцам или флажок Общие итоги по строкам можно подвести итоги по столбцам или строкам в сводной таблице. Установка флажка Автоформат позволяет пользоваться средствами автоформата MS Excel. Установив флажок Сохранить данные вместе с таблицей можно создать дополнительную копию данных, позволяющую быстрее пересчитывать сводную таблицу при ее изменении.
|
Рис. 9.18. Диалоговое окно Параметры сводной таблицы |
После щелчка по кнопке Готово в третьем диалоговом окне Мастер сводных таблиц на рабочем листе будет создана сводная таблица, представленная на рис. 9.19.
|
Рис. 9. 19. Сводная таблица подсчета прибыли по каждому товару |