Предмет: Информационные технологии

Просмотров: 3663

Сводные таблицы

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

Источники данных для формирования сводной таблицы:

-           список ( база данных ) EXCEL;

-           внешний источник данных в  любом конвертируемом формате (текстовый файл, содержащий табличные данные, реляционная база данных);

-           несколько  диапазонов  ячеек  одного  или  нескольких  рабочих листов одной или нескольких рабочих книг;

-           другая сводная таблица.

Построение сводной таблицы осуществляется с помощью Мастера сводных  таблиц,  вызываемого по команде меню Данные,  Сводная  таблица. На первом шаге выбирается источник данных, обычно в списке или базе данных Microsoft Excel (рис. 19).

Рис. 19 Шаг 1 Мастера сводных таблиц – выбор вида источника

После выбора вида источника выполняется второй шаг, на котором определяется диапазон исходных данных (рис. 20).

Рис. 20 Шаг 2 Мастера сводных таблиц – выбор диапазона ячеек

На третьем шаге (рис. 21) строится макет сводной таблицы. Макет сводной таблицы определяет структуру агрегированных данных и включает области:

-           Страница – используется для размещения полей списка, по которым осуществляется фильтрация (отбор) записей;

-           Строка для размещения полей группирования;

-           Столбец для размещения полей группирования;

-           Данные  для размещения полей, по которым подводятся итоги различного вида (сумма, среднее значение, количество значений, максимум, минимум и др.).

Рис. 21  Макет сводной таблицы

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

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

При работе в макете сводной таблицы настройка поля осуществляется двойным щелчком мыши на имени поля. При  работе в сводной таблице для настройки полей сводной таблицы выполняется команда короткого (контексто-зависимого) меню Поле  или одноименной кнопки на панели инструментов Сводные таблицы.

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

На последнем шаге работы Мастера  сводных  таблиц  можно задать имя сводной таблицы, указать на подсчет общих итогов по строкам и столбцам, задать внешнее представление данных сводной таблицы кнопка Параметры. Место размещения сводной таблицы определяется пользователем.

Рис. 22 Сводная таблица

В сводной таблице (рис. 22) обеспечена фильтрация записей списка по типам видеокарт (Video). Для различных сочетаний емкости жесткого диска (HDD,Gb) и ОЗУ (RAM,Gb) и определенного типа видеокарты (Video) вычислены итоговые показатели:

♦ средняя цена компьютера (Средняя цена),

♦ количество компьютеров (Кол комп).

Информационная технология сводных таблиц позволяет значения полей группирования объединять в диапазоны и вычислить для них итоги. Так, емкости магнитных дисков объединены с шагом – 2,4 Гб (рис. 23).

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

Для числового поля группировка осуществляется с заданным шагом,

для данных типа дата/время            шаг составляет от нескольких дней до нескольких лет.

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

Группа  и структура, Разгруппировать.

Рис. 23 Сводная таблица с дополнительной группировкой

Вычисляемые поля сводной таблицы (рис. 24) создаются по заданным формулам.

Рис. 24 Сводная таблица с вычисляемым полем

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

Для вычисляемого поля Итого  стоимость формула расчета:

Итого  стоимость=Цена+'НДС, 20\%'

Средствами сводных таблиц EXCEL можно консолидировать (соединять) данные различных диапазонов, расположенных в одной или нескольких рабочих книгах, вычислять консолидированные итоги.

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