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

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

¡ Самый верхний уровень группировки – Год. Приведенную на рис. 6.28,а таблицу можно представить в виде двух отдельных таблиц, – для 2002 и 2003 года. Совокупность таких таблиц определяет третье измерение. Итоги можно вычислять для каждого года и для всех лет.

¡ Следующий уровень группировки – Полугодие. Для каждого полугодия можно вычислить итоги по количеству груза, перевезенного каждым судном и всеми судами вместе.

¡ Самый низкий уровень группировки – Судно.

На рис. 6.28,б показана сводная таблица, обобщающая информацию, представленную на рис. 6.28,а. При подведении итогов грузоперевозок в ней использована функция суммирования.

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

Таким образом, можно поменять приоритет перечисленных выше уровней группировки. Например, верхним уровнем группировки можно сделать Судно. Тогда всю приведенную на рис. 6.28,а информацию можно будет представить в виде двух отдельных таблиц, – для Витязя и Керчи. Следующим уровнем группировки может быть Год, а затем – Полугодие.

Создание сводной таблицы с помощью мастера. Для создания сводных таблиц можно воспользоваться мастером сводных таблиц и диаграмм. Как и все остальные мастера Excel, этот мастер выполняет все операции в пошаговом режиме, предоставляя пользователю необходимые подсказки.

Рис. 6.28. Исходная таблица (а) и сводная таблица (б)

Чтобы создать таблицу с помощью мастера сводных таблиц и диаграмм, необходимо вы­полнить следующие операции:

В меню Данные выбрать команду Сводная таблица. В результате откроется первое окно мастера сводных таблиц (рис. 6.29). Здесь с помощью переключателей можно вы­­брать источник данных и вид создаваемого отчета. Имеются четыре варианта выбора источника данных:

— В списке или базе данных Microsoft Excel – используются данные, находящиеся в текущей рабочей книге. Этот вариант выбран по умолчанию.

— Во внешнем источнике данных – используются данные, не содержащиеся в текущей рабочей книге.

— В нескольких диапазонах консолидации – используются данные, находящиеся на нескольких рабочих листах.

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

Рис. 6.29. Первое окно мастера сводных таблиц

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

Рис. 6.30. Второе окно мастера сводных таблиц

Во втором окне мастера указать диапазон исходных данных и щелкнуть на кнопке Далее >. В результате откроется третье окно мастера сводных таблиц (рис. 6.31).

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

Рис. 6.31. Третье окно мастера сводных таблиц

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

Формирование макета сводной таблицы. Для создания макета свод­ной таблицы нужно щелкнуть в третьем окне мастера (см. рис. 6.31) на кнопке Макет. В результате от­кроется окно, показанное на рис. 6.32,а.

Рис. 6.32. Окно для создания макета сводной таблицы (а) и список полей (б)

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

¡ Страница – используется для размещения раскрывающегося меню, которое располагается над сводной таблицей. В этом меню можно выбирать элементы того столб­ца исходной таблицы (см. рис. 6.28,а), имя которого отбуксировано в область Страница.

На рис. 6.33,а по­казано, что в эту область отбуксировано имя поля Год. Соответственно, в раскрывающемся меню сводной таблицы (рис. 6.33,б) можно выбрать: Все, 2002 или 2003.

¡ Строка – служит для размещения заголовков строк сводной таблицы. В качестве заго­ловков строк используются значения того столб­ца исходной таблицы, имя которого от­букси­ровано в область Строка.

На рис. 6.33,а по­казано, что в эту область отбуксировано имя поля Полугодие. Соответственно, в сводной таблице на рис. 6.33,б заголовками строк являются: 1 (т.е. 1-е полугодие), 2 (2-е полугодие) и Итог.

¡ Столбец – служит для размещения заголовков столбцов сводной таблицы. В качестве заголовков столбцов используются значения того столб­ца исходной таблицы, имя которого отбуксировано в область Столбец.

На рис. 6.33,а по­казано, что в эту область отбуксировано имя поля Судно. Соответственно, в сводной таблице на рис. 6.33,б заголовками столбцов являются: Витязь, Керчь и Итог.

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

На рис. 6.33,а по­казано, что в эту область отбуксировано имя поля Груз и выбрана операция суммирования. Соответственно, в сводной таблице вычислены суммарные количества груза, перевезенного каждым судном и всеми судами вместе.

Если бы в исходной таблице были еще столбцы Прибыль и Количество пройденных миль, то вместе с именем поля Груз в область Данные можно было бы отбуксировать и эти имена полей. Тогда в сводной таблице были бы просуммированы и эти показатели.

Рис. 6.33. Макет (а) и соответствующая сводная таблица (б)

Когда макет сводной таблицы определен, нужно щелкнуть на кнопке ОК. При этом окно макета закроется и активным станет третье окно мастера сводных таблиц (см. рис. 6.31). Теперь при необходимости можно задать параметры сводной таблицы (этот вопрос рассмотрен в следующем разделе).

Если макет сводной таблицы не был создан своевременно, и после щелчка на кнопке Готово сводная таблица оказалась пустой, нужно щелчком в любом ее месте открыть список полей (рис. 6.32,б) и отбуксировать поля в те области пустой таблицы, где они должны находиться. Когда таблица будет готова, нужно щелкнуть вне ее, чтобы список полей исчез.

Задание параметров сводной таблицы. Для задания параметров свод­ной таблицы нужно щелкнуть в третьем окне мастера (см. рис. 6.31) на кнопке Параметры. В результате от­кроется окно, показанное на рис. 6.34. Это окно откроется также, если щелкнуть правой кнопкой в любом месте сводной таблицы и выбрать в открывшемся контекстном меню команду Параметры таблицы.

В верхней части окна Параметры сводной таблицы находится поле Имя. Оно предназначено для задания имени сводной таблицы. Изначально там находится стандартное имя (в нашем примере это СводнаяТаблица7). Пользователь может назвать таблицу по-своему.

Остальные элементы управления разбиты на две группы. В группу Формат входят:

R Общая сумма по столбцам. Если этот флажок установлен, в сводной таблице будет выполняться суммирование данных по столбцам.

R Общая сумма по строкам. Если этот флажок установлен, в сводной таблице будет выполняться суммирование данных по строкам.

Рис. 6.34. Окно для задания параметров сводной таблицы

R Автоформат. Сводную таблицу можно форматировать точно так же, как обычную таблицу (см. Главу 3). И, как для обычной таблицы, можно применять средство Автоформат. Для этого используется команда Формат 4 Автоформат. Чтобы отказаться от результатов автоформатирования, этот флажок нужно снять.

R Включать скрытые значения. Если этот флажок установлен, при вычислении итоговых результатов будут учитываться данные из скрытых строк и столбцов.

R Объединять ячейки заголовков. Если этот флажок установлен, сводная таблица приобретает более красивый вид, так как ячейки заголовков строк и столбцов (где это возможно) объединяются, а сами заголовки смещаются в центр объединенных ячеек. Например, если бы такой параметр был задан для сводной таблицы, изображенной на рис. 6.28,б, то в ней объединились бы следующие ячейки: A3 и A4; A5 и B5; A6 и A7; A8 и B8; A9 и B9.

R Сохранять форматирование. Если сводная таблица отформатирована без применения Автоформата, и нужно сохранить параметры форматирования при обновлении данных или изменении структуры сводной таблицы, следует установить этот флажок.

R Повторять подписи на каждой странице печати. Этот флажок обретает смысл в тех случаях, когда необходимо распечатать сводную таблицу, занимающую несколько страниц. Если флажок установлен, то на каждой странице будут печататься заголовки строк.

R Для ошибок отображать. Если среди обрабатываемых данных есть ячейки, содержащие ошибки (например, #Н/Д), то с помощью этого элемента управления можно задать вид получаемого результата. Когда флажок снят, в сводной таблице будет отображаться стан­дартное сообщение об ошибке (см. Главу 4, п. 4.3). Чтобы отобразить в этом случае какой-то текст, нужно установить флажок и ввести его в расположенное рядом поле.

R Для пустых ячеек отображать. Если этот флажок снят, в пустых ячейках будут отображаться нули. Чтобы вместо нулей вывести какое-то сообщение, нужно установить флажок и ввести в расположенное рядом поле соответствующий текст.

R Печать заголовков. Если этот флажок установлен, то при распечатке большой сводной таблицы на каждой странице будут печататься заголовки строк и столбцов.

Вторая группа элементов управления называется Данные. В ее состав входят:

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

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

R Обновить при открытии. Этот флажок устанавливается в случаях, когда нужно, чтобы при открытии рабочей книги автоматически выполнялось обновление сводной таблицы.

R Обновлять каждые … мин. Этот флажок доступен только для сводных таблиц, построенных по данным внешних источников. Если флажок установлен, то обновление сводной таблицы будет выполняться автоматически с заданным интервалом.

R Сохранить пароль. Если источником данных сводной таблицы является за­щищенная паролем внешняя база данных, установив этот флажок, можно сохранить пароль вместе с таблицей. Тогда при обновлении таблицы вводить заново пароль не потребуется.

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

R Оптимизировать память. Этот флажок устанавливают для экономии оперативной памяти при обновлении сводной таблицы, построенной по данным внешних источников.

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

В контекстном меню сводной таблицы имеется также команда Обновить данные. Ее мо­ж­но использовать для пересчета сводной таблицы после каждого изменения исходных данных.

Изменение структуры сводной таблицы. После того как сводная таблица создана, можно многократно изменять ее структуру, чтобы придать ей необходимый вид. Проще всего это делать посредством буксировки имен полей. Например, для преобразования сводной таблицы, показанной на рис. 6.28,б, в таблицу с рис. 6.33,б достаточно отбуксировать имя поля Год вверх за пределы таблицы. А чтобы вернуть таблице исходный вид, нужно отбуксировать это же имя поля Год вниз на левую границу таблицы (ниже заголовка Сумма Груза). О готовности к изменению структуры в данном случае свидетельствует объемная вертикальная линия, совпадающая с левой границей сводной таблицы.

В заключение продемонстрируем еще одно изменение структуры сводной таблицы. Чтобы преобразовать таблицу, показанную на рис. 6.35,а, в таблицу с рис. 6.35,б, нужно отбуксировать имя поля Год в направлении, указанном стрелкой. Когда появится показанная на рис. 6.35,а объемная горизонтальная линия, левую кнопку мыши нужно отпустить. В результате структура сводной таблицы будет преобразована, как это показано на рис. 6.35.

Рис. 6.35. Исходная (а) и преобразованная (б) сводная таблица