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

Сводная таблица – это один из мощных инструментов обработки данных, т.к. в этом случае сразу подводятся итоги, выполняется сортировка и фильтрация списков. Построение сводной таблицы выполняется с помощью Мастера сводных таблиц, который вызывается командой Данные /Сводная таблица.

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

Макет сводной таблицы определяет её структуру:

· Страница - для размещения полей, по которым выполняется отбор записей;

· Строка – для полей, по которым выполняется группировка;

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

· Данные – для размещения полей, по которым получают итоги (сумма, среднее значение, количество значений, максимум и т.д.). На рассмотренных далее примерах рассмотрим создание сводных таблиц. В первом примере используем данные нашего практического задания.

Пример 1. Создадим сводную таблицу на основе таблицы листа Рабочая ведомость следующего вида:

Выполним команду Данные/ Сводная таблица… Затем Далее.

В окне указать имя Ведомость_список. (нажать F3 и выбрать Ведомость_список).

После нажатия кнопки Далее в следующем окне нажать Макет. Появится окно Мастер сводных таблиц:

В этом окне начинается построение макета сводной таблицы.

Все названия полей расположены справа в окне макета. Каждое поле можно переместить с помощью мыши в любую область макета.

При таком макете происходит размещение данных по каждому адресу на “отдельной странице”. Для отображения на экране данных о каком-либо городе нужно выбрать адрес (город) из списка.

Поля Сумма к выплате и Сумма Долг+Пеня следует разместить в область Данные. Поле Наименование заказчика в область полей строк, а Период в область полей столбцов. Т.к. в сводной таблице надо показать ещё и %задолженности, то Сумма Долг+Пеня разместим в область Данные дважды, чтобы затем преобразовать одно из них в %.

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

Откроется окно:

в окне Имя ввести Сумма к выплате. Нажать ОК. Щёлкнуть 2 раза по полю Сумма по полю Долг+Пеня и заменить его на Задолженность.

Щёлкнуть 2 раза по другому полю Сумма по полю Долг+Пеня и заменить это название на

% задолженности. В этом же окне для получения значения % нажать кнопку Дополнительно>>.

В окне Дополнительные вычисления открыть список и выбрать Доля от суммы по столбцу.

Если надо внести изменения в макет таблицы, то можно воспользоваться панелью Сводные таблицы:

Раскрыть список Сводная таблица и выбрать Мастер, а затем Макет. Или щёлкнуть правой кнопкой мыши по сводной таблице. Выбрать Мастер, а затем Макет.

Пример 2. С помощью сводной таблицы получить общие сведения о суммах выплаты и долге по каждому периоду внутри каждого города. Рассчитать % долга по каждому периоду внутри каждого города.

Создадим макет таблицы. Изменим названия данных на Сумма долга и Сумма выплат.

Нажмём ОК.

Замечание: если итоги по строкам или по столбцам выдавать не требуется, то надо войти в окно Параметры и снять флажки

Общая сумма по столбцам и Общая сумма по строкам:

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

· На панели Сводные таблицы раскрыть список, затем Формулы и Вычисляемое поле

В окне Вставка вычисляемого поля ввести имя и формулу. Нажать ОК.

В поле данных макета появится ещё одно поле.

· Изменим название поля на % долга за квартал в городе. Затем нажмём кнопку Формат.

· В следующем окне установим формат Процентный и два десятичных знака.

· Получим сводную таблицу:

На основании данных сводной таблицы можно построить диаграмму.

При построении диаграмм рекомендуется:

· Удалите из таблицы промежуточные и общие итоги;

· Убедитесь, что таблица имеет не более двух полей в области столбцов и строк;

· Скройте все элементы за исключением тех, которые вас интересуют.