Сводная таблица Microsoft Excel
Сводная таблица обеспечивает формирование сводной (агрегированной) информации в структурированном виде. Источниками данных для формирования сводной таблицы являются:
Список (база данных) Excel на листе текущей рабочей книги.
Внешний источник данных (текстовый файл, содержащий табличные данные, реляционная база данных).
Диапазоны ячеек для консолидации текущей рабочей книги.
Другая сводная таблица.
Построение сводной таблицы осуществляется с помощью Мастера сводных таблиц – команда меню ДанныеСводная таблица:
На первом шаге выбирается тип источника данных и вид результирующей сводной таблицы.
На втором шаге задается диапазон ячеек для построения сводной таблицы.
Если источник сводной таблицы – список, сводная таблица размещается в той же книге. Если источник сводной таблицы – диапазон ячеек для консолидации, ячейки могут находиться на разных листах/рабочих книгах, а сводная таблица может размещаться в любой книге, в том числе и новой.
На третьем шаге осуществляется формирование структуры сводной таблицы. Для этого нажимается кнопка Макет. Дополнительная настройка параметров сводной таблицы выполняется при нажатии кнопки Параметры.
Макет сводной таблицы включает области:
Страница – для размещения полей фильтрации (отбора) записей, отображаемых в сводной таблице;
Строка – для размещения полей группирования, учитывается последовательность полей для создания вложенных групп, подгрупп и т.д.;
Столбец - для размещения полей группирования, учитывается последовательность полей для создания вложенных групп, подгрупп и т.д.;
Данные - для размещения полей итогов.
При этом любое поле списка размещается однократно в области группирования (страница, строка или столбец). В области Данные размещаются только те поля, которые не вошли в области группирования, но любое из них может многократно размещаться в области Данные для вычисления различных итогов. Помимо «базовых» полей из основной таблицы (списка), сводная таблица может содержать вычисляемые поля в области Данные, а также вычисляемые элементы для полей группирования.
Элементы полей группирования могут объединяться в новые группы. Для группы полей осуществляется выбор типа итога, скрытие/отображение деталей подчиненных полей.
Правило размещения сводной таблицы:
Сводная таблица, построенная на основе списка (базы данных), размещается в той же рабочей книге, что и список, на любом листе. Не допускается перекрытие сводной таблицей другой информацией на рабочем листе.
Сводная таблица, построенная на основе консолидации диапазона ячеек, может размещаться в любой рабочей книге, в том числе новой. Сводная таблица, построенная на базе другой сводной таблицы, размещается в той же рабочей книге, что и исходная и может находиться на другом листе.
В табл. 20 приведены ограничения, которые действуют для сводных таблиц Microsoft Excel 2000.
Ограничение
Число сводных таблиц на одном листе
Ограничивается объемом доступной оперативной памяти
Число элементов в сводной таблице
Число полей строк или столбцов в сводной таблице
Ограничивается объемом доступной оперативной памяти
Число полей страницы в сводной таблице
256 (может ограничиваться объемом доступной оперативной памяти)
Число полей данных в сводной таблице
Число формул вычисляемых элементов в сводной таблице
Ограничивается объемом доступной оперативной памяти
Для работы со сводной таблицы используется панель инструментов Сводные таблицы – рис. 19.
Сводная таблица – вызов меню команд для выбора:
Формат отчета – выбор формата отчета для сводной таблицы.
Сводная диаграмма – построение диаграммы по сводной таблице.
Мастер сводных таблиц – переход к 3 шагу построения сводной таблицы (изменение макета, настройка параметров, выбор места размещения сводной таблицы).
Скрыть детали, Отобразить детали полей группирования.
Обновить данные сводной таблицы при изменении источника.
Настройка клиент-сервера при использовании OLAP-куба для построения сводной таблицы.
Выделить отдельные части сводной таблицы: заголовки, данные или таблицу целиком.
Формулы – создание вычисляемых полей в области данных или вычисляемых объектов/элементов в области заголовков.
Параметры поля – настройка параметров поля сводной таблицы.
Параметры таблицы – настройка параметров таблицы.
Отобразить страницы – на отдельных листах книги.
Другие кнопки соответствуют командам: Формат отчета, Мастер диаграмм, Мастер сводных таблиц, Скрыть/Отобразить детали (для выделенного заголовка), Обновить данные, Параметры поля, Отобразить поля.
Пример построения сводной таблицы для расчета месячной зарплаты рабочих при повременной форме оплаты труда, начисления премии и учета удержаний. Премия дифференцированы по разрядам: 2 разряд 25%, 3 разряд 32%, 4 разряд 50% к тарифу, 5 разряд – 50%. Удержания берутся со всех видов начислений (зарплата, премия) и составляют 13% от суммы начислений.
Вставить новый лист – команда меню ВставкаЛист, если необходимо.
Переименовать лист в Картотека – команда меню ФорматЛистПереименовать.