2. Модификация сводной таблицы

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

Рис. 8. Диалоговое окно Вставка вычисляемого поля

Добавление поля осуществляется следующим образом. С помощью команды Сводная таблица, Формулы, Вычисляемое поле панели инструментов «Сводные таблицы» вызывается диалоговое окно Вставка вычисляемого поля (рис. 8). В прокручиваемом списке Поля следует выбрать элемент сумма в счете и нажать кнопку Добавить поле, затем ввести знак " - " (минус) в поле Формула, а затем аналогичным образом добавить в формулу поле сумма оплаты. После этого следует в поле Имя ввести название добавляемого поля «Долг». Теперь следует нажать кнопку ОК для добавления нового поля и кнопку Закрыть для выхода.

Формула вычисляемого поля может строиться с использованием имен полей из списка Поля, знаков операций и чисел. Не допускается использовать в формуле ссылки на ячейки или имена ячеек. Кроме того, нельзя использовать функции, параметрами которых являются ссылки на ячейки или имена ячеек, а также функции массива.

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

Группировка данных по конкретным датам не информативна: даты выставления и оплаты счетов распределены неравномерно, и группировка получается слишком мелкая. Лучше сгруппировать данные по достаточно продолжительным и равномерным интервалам, например, по месяцам. Для группировки по датам в рассматриваемом примере:

1. Выделите поле дата счета, щелкните правой кнопкой мыши и в контекстном меню выберите команду Группа и структура, Группировать.

2. В появившемся диалоговом окне Группирование (рис. 9) снимите флажки «начиная с» и «по», определяющие ручной выбор диапазона группировки. В разделе с шагом выберите значение Месяцы.

3. Нажмите кнопку ОК.

Рис. 9. Диалоговое окно Группирование

4. В диалоговом окне Вычисление поля сводной таблицы, вызванном для поля дата оплаты, установите флажок «Отображать пустые элементы». В результате в сводной таблице данные будут сгруппированы по месяцам по дате оплаты, причем в таблице будут присутствовать столбцы и для месяцев, во время которых операции не производились, но только до того месяца, во время которого была произведена последняя операция (включительно).

Для даты выставления счета нужно произвести аналогичную группировку, но флажок «Отображать пустые элементы» следует снять. В результате данные будут сгруппированы и по дате выставления счета, но "лишних" строк, соответствующих месяцам, в течение которых не выставлялись счета, в таблице не будет.

Существуют несколько способов форматирования сводной таблицы:

Если установлен режим разрешить выделение (т.е. если на панели инструментов «Сводные таблицы» в подменю команды Сводная таблица, Выделить нажата кнопка Разрешить выделение), то отдельные части сводной таблицы можно форматировать как обычные ячейки, меняя их размеры, формат ячеек, заливку, границы и т.д.

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

На панели инструментов «Сводные таблицы» есть кнопка Формат отчета, которая открывает диалоговое окно Автоформат, позволяющее быстро выбрать желаемое оформление сводной таблицы из большого числа заранее заготовленных вариантов.