На этом шаге мы рассмотрим этапы создания сводной таблицы.
Для создания сводной таблицы воспользуемся средством Мастер сводных таблиц и диаграмм. Чтобы получить доступ к этому средству, необходимо выполнить команду Данные | Сводная таблица.
Если выбрать эту команду, то появится первое из трех диалоговых окон (рис. 1). На этом этапе определяется источник данных.
Рис. 1. Диалоговое окно Мастер сводных таблиц и диаграмм - шаг 1 из 3
При работе с Мастером сводных таблиц и диаграмм в зависимости от местоположения исходных данных вид диалоговых окон этого мастера может быть различен. В этом разделе предполагается, что исходные данные находятся или на листе Excel, или во внешней базе данных.
Данные в списке Excel
Чаще всего анализируемые данные хранятся в базе данных рабочего листа, который называется списком. База данных, хранящаяся в рабочем листе, не может иметь больше 65 535 записей и 256 полей. В первой строке базы данных должны содержаться названия полей. Данные могут состоять из чисел, текста или формул.
Данные во внешнем источнике данных
Если для создания сводной таблицы используется внешняя база данных, то данные вводятся в рабочий лист с помощью отдельного приложения Microsoft Query, входящего в поставку Excel или Office. Можно использовать файлы dBASE, данные SQL-сервера или другие источники. Выбор источника данных осуществляется на втором шаге работы Мастера сводных таблиц и диаграмм.
Данные в диапазонах консолидации
Сводные таблицы можно также создавать на основе информации, находящейся в нескольких таблицах. Тогда создание сводной таблицы эквивалентно объединению информации из этих таблиц. В этом случае преимущество сводных таблиц перед методами консолидации заключается в том, что можно работать с объединенными данными, используя все средства и возможности сводных таблиц.
Данные в другой сводной таблице
Excel позволяет создавать сводную таблицу из созданной ранее сводной таблицы. На самом деле это не совсем верно. Сводная таблица, которая создается, помогает анализировать исходные данные, использовавшиеся для построения первой таблицы (а не данные самой сводной таблицы). Если активная рабочая книга не имеет никаких сводных таблиц, эта возможность недоступна.
Если необходимо создать несколько сводных таблиц, используя один и тот же набор данных, более эффективно (с точки зрения использования памяти) создать первую сводную таблицу, а затем использовать эту таблицу как исходную для последующих сводных таблиц.
Чтобы перейти к следующему этапу работы Мастера сводных таблиц и диаграмм, щелкните на кнопке Далее. На втором шаге этот мастер выдает запрос об источнике исходных данных. Вид диалогового окна будет зависеть от сделанного выбора в первом окне. На рисунке 2 показано диалоговое окно, появляющееся в случае, если на первом этапе в качестве источника данных был выбран список на рабочем листе Excel.
Рис. 2. Диалоговое окно Мастер сводных таблиц и диаграмм - шаг 2 из 3
Если при выполнении команды Данные | Сводная таблица табличный курсор находился на одном из элементов списка, то Excel автоматически определит диапазон ячеек базы данных на втором этапе работы Мастера сводных таблиц и диаграмм. Чтобы открыть другой рабочий лист и выбрать диапазон ячеек, надо щелкнуть на кнопке Обзор. Чтобы перейти к третьему этапу, надо щелкнуть на кнопке Далее.
Последнее диалоговое окно Мастера сводных таблиц и диаграмм показано на рисунке 3. Здесь необходимо указать местоположение создаваемой сводной таблицы.
Рис. 3. Диалоговое окно Мастер сводных таблиц и диаграмм - шаг 3 из 3
Если вы выбрали переключатель Новый лист, то Excel вставит новый рабочий лист для сводной таблицы в текущую рабочую книгу. Если выбран переключатель Существующий лист, то сводная таблица появится на текущем рабочем листе (можно указать ее положение на листе).
Если в этом окне мастера щелкнуть на кнопке Параметры, то откроется диалоговое окно Параметры сводной таблицы, в котором можно определить параметры отображения данных в сводной таблице. Щелкните на кнопке ОК для возврата в диалоговое окно Мастера сводных таблиц и диаграмм.
Рис. 4. Диалоговое окно Параметры сводной таблицы
Это диалоговое окно содержит следующие опции:
- Имя. Это поле позволяет определить название сводной таблицы. Excel по умолчанию назьшает их СводнаяТаблица1, СводнаяТаблица2 и т.д.
- Общая сумма по столбцам. Установите флажок этой опции, если необходимо подвести общие итоги по столбцам.
- Общая сумма по строкам. Установите флажок этой опции, если необходимо подвести общие итоги по строкам.
- Автоформат. Если необходимо применить средство автоматического форматирования Excel, то установите этот флажок. Excel будет использовать автоматическое форматирование, даже если вы измените макет таблицы.
- Включать скрытые значения. Установите флажок этой опции, если необходимо включать скрытые элементы поля страницы в промежуточные итоги.
- Объединить ячейки заголовков. Установите флажок этой опции, если необходимо объединить ячейки для внешних заголовков строк и столбцов. В результате таблицу будет легче читать.
- Сохранять форматирование. Установите флажок этой опции, если необходимо сохранять любое ваше форматирование при изменении сводной таблицы.
- Повторять подписи на каждой странице печати. Установите флажок этой опции, если необходимо, чтобы заголовки строк печатались на каждой странице отчета сводной таблицы.
- Макет страницы. С помощью этой опции можно указать порядок следования полей страниц.
- Число полей в столбце. С помощью этой опции можно указать количество выводимых полей страницы в строке или столбце перед выводом следующей строки или столбца.
- Для ошибок отображать. Можно указать значение, которое будет выводиться в ячейках сводной таблицы при возникновении ошибки.
- Для пустых ячеек отображать. Можно указать значение, которое будет выводиться в пустых ячейках.
- Печать заголовков. Установите флажок этой опции, если необходимо на каждой странице отчета сводной таблицы распечатывать заголовки столбцов.
- Сохранить данные вместе с таблицей. Если эта опция активизирована, то Excel сохраняет дополнительную копию данных, которая называется "кэш сводной таблицы", вместе с рабочей книгой. Это позволяет гораздо быстрее пересчитывать сводную таблицу при ее изменении. Если на вашем компьютере не хватает оперативной памяти, снимите флажок этой опции, тогда обновление будет происходить немного медленнее.
- Развертывание разрешено. Если данная опция активизирована, то после двойного щелчка на ячейке сводной таблицы выводятся дополнительные сведения для этой ячейки.
- Обновить при открытии. Если эта опция активизирована, то после открытия книги сводная таблица будет обновлена автоматически.
- Сохранить пароль. При использовании внешней базы данных, которая требует пароля, эта опция позволяет сохранить пароль как часть запроса, после чего не требуется его ввод.
- Фоновый запрос. Если эта опция активизирована, то запрос к внешней базе данных будет выполняться в фоновом режиме, и вы сможете продолжить работу в Excel.
- Оптимизировать память. С помощью этой опции можно уменьшить общее количество памяти, которая используется при обновлении запроса к внешней базе данных.
Для создания макета сводной таблицы Excel предоставляет два способа:
- с помощью мастера сводных таблиц и диаграмм;
- самостоятельно на рабочем листе с использованием инструментов панели Сводные таблицы (рис. 5).
Рис. 5. Панель инструментов Сводные таблицы
Создание макета сводной таблицы с помощью мастера
При щелчке в последнем диалоговом окне Мастера сводных таблиц и диаграмм на кнопке Макет будет открыто диалоговое окно Мастер сводных таблиц и диаграмм - макет, показанное на рисунке 6. В этом окне поля базы данных представлены в виде кнопок, которые расположены в столбце в правой части диалогового окна. Необходимо перетащить кнопки в соответствующие области макета сводной таблицы.
Рис. 6. Диалоговое окно Мастер сводных таблиц и диаграмм - макет
Макет сводной таблицы имеет четыре области:
- Строка. Значения поля, помещенного в эту область, используются в качестве заголовков строк в сводной таблице.
- Столбец. Значения поля, помещенного в эту область, используются в качестве заголовков столбцов в сводной таблице.
- Данные. Поле, для которого подводятся итоги в сводной таблице.
- Страница. Значения поля, помещенного в эту область, используются в качестве заголовков страниц в сводной таблице.
В каждую из этих областей можно перетащить любое количество полей, все поля использовать не обязательно. Неиспользованные поля не будут присутствовать в сводной таблице.
При перетаскивании кнопки поля в область данных Мастер сводных таблиц по умолчанию применит функцию СУММ, если поле содержит числовые значения, и функцию СЧЁТ — если поле содержит нечисловые значения.
Для настройки отображения какого-либо поля нужно дважды щелкнуть на нем. Можно указать для конкретного поля функцию подведения итогов - подсчет количества значений или какую-либо другую. Можно также определить, какие элементы поля не показывать или пропустить. Однако эти настройки можно сделать и после создания сводной таблицы.
Если случайно перетащили поле не в ту область, то необходимо выведите его за пределы макета, чтобы удалить из области. На рисунке 7 показано диалоговое окно после перетаскивания некоторых кнопок полей в сводную таблицу. Эта сводная таблица будет отображать сумму по полю Счет в зависимости от типа счета (поле строки Тип) и клиента (поле столбца Клиент). А поле Отделение будет использоваться в качестве поля страницы. Чтобы вернуться к диалоговому окну Мастера сводных таблиц и диаграмм, нужно щелкнуть на кнопке ОК.
Рис. 7. Готовый макет сводной таблицы
Создание макета сводной таблицы с помощью панели инструментов
В Excel можно создать макет сводной таблицы непосредственно на рабочем листе, используя для этого инструменты панели Сводные таблицы. Этот способ очень похож на вышеописанный, так как и здесь для создания макета надо просто поместить нужные поля базы данных в нужные области сводной таблицы.
Выполните два первых шага Мастера сводных таблиц и диаграмм. Укажите местоположение сводной таблицы во втором диалоговом окне Мастера сводных таблиц и диаграмм и щелкните на кнопке Готово в этом окне. Excel отобразит в рабочем листе шаблон сводной таблицы, как показано на рисунке 8.
Рис. 8. Готовый макет сводной таблицы
Перетащите кнопки полей с панели Список полей сводной таблицы в нужную область макета таблицы. Как только какое-либо поле будет помещено в область данных, Excel перейдет от представления шаблона к представлению сводной таблицы, хотя вы можете и дальше перемещать отдельные поля с панели инструментов в нужные области сводной таблицы. Поэтому имеет смысл помещать поля в область данных в самую последнюю очередь, когда заполнены все остальные области сводной таблицы.
Если вы сделали ошибку и поместили какое-либо поле не в ту область сводной таблицы, которую запланировали, просто перетащите это поле в нужную область или за пределы шаблона. Все поля остаются на панели Список полей сводной таблицы, даже если вы перетащили их в шаблон таблицы.
Для завершения создания сводной таблицы щелкните на кнопке Готово в третьем диалоговом окне Мастер сводных таблиц и диаграмм, если таблица создавалась с помощью мастера, или поместите нужные поля в область данных, если сводную таблицу вы создавали самостоятельно без помощи мастера.
На следующем шаге мы рассмотрим работу со сводными таблицами.
