Трюк №45. Что такое сводные таблицы в Excel? Сводные таблицы: сами по себе — трюк.

Сводные таблицы (PivotTables) — это одна из самых мощных и привлекательных возможностей Excel, хотя многие пользователи и не знают, что же они умеют. Сводные таблицы умеют отображать и выделять различную информацию из таблицы данных, находящейся в Microsoft Excel или другой базе данных совместимого типа. Часто при помощи сводных таблиц извлекают статистическую информацию из необработанных данных. Различные поля сводной таблицы можно передвигать, чтобы увидеть данные с разных точек зрения.

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

[stextbox id=»warning»]Необработанные данные для сводной таблицы должны быть организованы в формате классической таблицы. Первая строка должна содержать заголовки, а данные, относящиеся к ним, должны находиться в соответствующем столбце под своими заголовками. В данных не должно быть пустых столбцов или строк. Даже если вы не собираетесь использовать сводные таблицы, хранение необработанных данных в таком формате позволит другим пользователям проанализировать данные serm при помощи сводных таблиц.[/stextbox]
Если вы еще не изучали мир сводных таблиц, вам обязательно стоит этим заняться. Чтобы узнать еще больше о преимуществах сводных таблиц, а также о том, как создавать трюки, делающие их еще более гибкими и мощными, продолжайте посещать наш сайт или подпишитесь на обновления в форме внизу этой страницы, а также вы можете стать участником нашей группы ВКонтакте или зафолловить нас в Твиттере.

Мы часто используем сводные таблицы при разработке электронных таблиц для наших клиентов. Когда клиенты видят сводную таблицу, они практически всегда спрашивают, а смогут ли они сами создавать их. Хотя любой пользователь может создать сводную таблицу, к сожалению, многие люди стараются избегать их, так как эти таблицы кажутся слишком сложными. Действительно, когда вы впервые работаете со сводной таблицей, процесс может показаться немного запутанным. Определенно требуется некоторая настойчивость.

Вы обнаружите, что ваша настойчивость окупится, как только познакомитесь с лучшим свойством сводных таблиц — возможностью манипулировать ими методом проб и ошибок и немедленно видеть результаты этих манипуляций. Если вы получили неожиданный результат, то при помощи возможности Excel Отмена (Undo) всегда сможете отменить его и прийти к другому. Что бы вы ни делали, вы не измените структуру исходной таблицы, а потому не причините своей работе никакого вреда.

Почему они называются сводными таблицами?

Сводные таблицы позволяют сочетать данные различными способами, используя технику перетаскивания (drag-and-drop), и немедленно получать результаты. Это интерактивное средство: закончив создание таблицы, легко можно видеть, как повлияет на информацию перемещение данных. Все станет совершенно ясно, как только вы попробуете работать со сводными таблицами. Даже для опытных разработчиков сводных таблиц в процессе получения желаемых результатов присутствует элемент проб и ошибок. Вы часто будете перетаскивать и разными способами крутить данные в таблице!

Для чего подходят сводные таблицы?

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

• у скольких людей одинаковые имена;
• сколько людей живут по адресу с одинаковым почтовым индексом;
• сколько людей работают на одном и том же месте.

Можно получить и такую информацию, как:

• список людей, работающих на одном и том же месте;
• список адресов с одинаковым почтовым индексом.

Если данные нужно разобрать, разделить и составить отчет, сводные таблицы становятся жизненно важной составляющей набора инструментов.

Зачем использовать сводные таблицы, если электронные таблицы предлагают так много возможностей для анализа?

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

Сводные диаграммы расширяют возможности сводных таблиц

Microsoft впервые представила сводные диаграммы (PivotCharts) в Excel 2000. На основе таблицы, которую вы создаете при помощи мастера сводных таблиц, можно создать сводную диаграмму (или, точнее, отчет сводной диаграммы (PivotChart Report) на основе сводной таблицы). Во время создания сводной таблицы безо всяких усилий с вашей стороны можно одновременно создать сводную диаграмму. Сводные диаграммы позволяют создавать интерактивные диаграммы, что раньше было невозможно без помощи VBA или элементов управления Excel.
Подробнее о мастере сводных таблиц (PivotTable Wizard) — далее в разделе «Трюки со сводными таблицами».
[stextbox id=»alert»]Сводные диаграммы недоступны в Excel для Macintosh.[/stextbox]

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

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

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

Когда вы извлекаете данные при помощи функций поиска или функций баз данных, к настройке таблицы или списка можно отнестись чуть менее серьезно. Вы всегда можете компенсировать это за счет функции и, вероятно, все так же получить нужный результат. Однако проще всего настраивать список или таблицу настолько точно и аккуратно, насколько это возможно. Встроенные возможности Excel базируются на множестве предположений о макете и настройке данных. Хотя они обеспечивают некоторую степень гибкости, чаще всего проще работать, если держаться следующих правил относительно настройки таблицы или списка:

• Заголовки необходимы, коль скоро сводная таблица использует их для имен полей. Заголовки всегда должны находиться в строке непосредственно над данными. Никогда не оставляйте пустую строку между данными и заголовками. Кроме того, заголовки должны быть отформатированы по-другому, например, жирным шрифтом.
• Над заголовками оставьте, по меньшей мере, три пустые строки. В них можно будет поместить формулы, важные данные и т. д. При желании эти строки можно скрыть.
• Если на одном рабочем листе несколько списков или таблиц, оставляйте, по меньшей мере, один пустой столбец между списками или таблицами. Так Excel будет проще понять, что это различные сущности. Однако если списки и таблицы связаны между собой, лучше объединить их в одну большую таблицу.
• Избегайте пустых ячеек в данных. Вместо того чтобы оставлять пустые ячейки для одинаковых данных в столбце, повторяйте эти данные необходимое количество раз.
• Сортируйте списки данных, лучше по самому левому столбцу. Так данные будет проще читать и интерпретировать.

Если как можно строже соблюдать эти советы, работа со сводными таблицами станет относительно простой задачей.

На рис. 4.1 показаны хорошо организованная таблица данных и процесс создания сводной таблицы. Обратите внимание, что в столбце Date повторяется множество одинаковых дат. На переднем плане вы видите этап создания макета (layout) данных, демонстрирующий дополнительные поля Страница (Page), Строка (Row) и Column (Столбец) и обязательное поле Данные (Data).

Мастер сводных таблиц и сводных диаграмм

Как упоминалось ранее, чтобы помочь пользователям в создании сводных таблиц, Excel предлагает средство Мастер сводных таблиц и сводных диаграмм (PivotTable and PivotChart Wizard). Этот мастер проводит вас через несколько шагов процесса создания сводной таблицы, в ходе которого вы сообщаете Excel следующее:

• как организованы данные и нужно ли создавать связанную сводную диаграмму (если сводные диаграммы доступны в вашей версии Excel);
• где хранятся данные: диапазон в этой рабочей книге, база данных, другая рабочая книга и т. д.;
• какой столбец данных соответствует какому полю: дополнительные поля Страница (Page), Строка (Row) и Столбец (Column) и обязательное поле Данные (Data);
• куда следует поместить сводную таблицу (например, на новый рабочий лист или в существующий).

В ходе создания можно также делать множество дополнительных шагов по настройке сводной таблицы, однако большинству пользователей проще делать это уже после того, как они сообщат Excel, куда следует поместить сводную таблицу.
[stextbox id=»info»]У Excel 2000 и более поздних версий есть большое преимущество перед Excel 97: они позволяют выбирать, как следует настроить данные, уже после того, как работа мастера закончится.[/stextbox]
Теперь, когда вы больше узнали о сводных таблицах и их предназначении, настало время изучить некоторые удобные трюки, делающие эту возможность еще более мощной.