Использование таблиц Pivot в Excel

Те, кому приходится оценивать большие объемы данных, быстро достигают предела возможностей обычных таблиц Excel — они часто слишком негибкие. Расширенные разворотные таблицы — идеальное решение. Когда дело доходит до оценки и подготовки данных, поворотные таблицы в Excel демонстрируют весь свой потенциал: с их помощью можно сжать данные и настроить отображение так, как это требуется в каждом конкретном случае. Работа, которая обычно сложна с обычными таблицами, становится простой с поворотными таблицами. Но как создать такую таблицу, и когда ее формат подходит?

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

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

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

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

Таблицы pivot предоставляют бесчисленные функции, которые чрезвычайно полезны при оценке данных. Универсальность pivot в Excel лучше всего описать на примере. Мы уже создали саму таблицу pivot; она содержит набор данных со списком продаж за один год. В нем указаны задействованные сотрудники, оборот и тип проданного продукта. Обратите внимание, что набор данных представляет собой простую таблицу, в которой помечены только столбцы, но не строки.

Новая электронная таблица, в которой данные теперь должны появиться в виде поворотной таблицы, в начале совершенно пуста. Однако теперь в окне должна появиться новая боковая панель: Поля PivotTable. Там должны появиться категории, которые вы определили для столбцов набора данных. На боковой панели также есть четыре области, куда можно перетащить поля PivotTable Fields:

• Фильтры
• Столбцы
• Строки
• Значения

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

В нашем примере мы вводим месяцы в строки, а сотрудников — в столбцы. Сводная таблица автоматически анализирует информацию, полученную из набора данных, и определяет, что было задействовано двенадцать различных месяцев и четыре различных сотрудника. К двум типам продуктов применяются фильтры, что позволяет нам оценивать таблицу по Пакету А или Пакету Б.

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

В нашем случае Excel автоматически определяет количество продаж в месяц и на одного сотрудника. Вместо исходных денежных значений, размещение их в разделе «Значения» приводит непосредственно к «Подсчету продаж». Это дает пользователю возможность поместить пустые цифры в контекст и оценить их таким образом: Лаура совершила две продажи в апреле. Таблица pivot также автоматически отображает общие результаты: в июне было 3 продажи, а за весь год — 24 продажи.

Если вас больше интересует денежная стоимость продаж и меньше — количество операций по продаже, это можно легко изменить. Для этого нажмите на «Продажи» в поле «Значения» и перейдите к «Настройкам поля значений». Здесь, в разделе «Summarize values by», измените «Count» на «Sum». Как видите, доступны и многие другие типы отображения (типы вычислений соответствуют функциям Excel).

Для денежных значений имеет смысл отображать их в соответствующих валютах. Нажав на кнопку «Формат чисел», вы попадете в окно, позволяющее форматировать ячейки (это доступно и при обычной работе с Excel). Здесь вы можете выбрать различные форматы, а также валюты.

Есть и другие варианты отображения значений: в настройках поля значений переключитесь на «Показывать значения как». Здесь вы можете отобразить данные в процентах от множества различных значений, например, в сравнении с общим результатом. Это говорит нам о том, что Лаура и Тим ответственны примерно за 60 процентов от общего объема продаж.

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

Стоит также взглянуть на опции PivotTable: здесь вы можете еще больше настроить отображение данных. Чтобы найти эту область, щелкните правой кнопкой мыши на таблице. Опции позволяют, например, скрыть итоговые результаты или настроить макет. Кроме того, вы можете дать разворотной таблице название или описание. Это позволит вам предоставить дополнительную информацию будущим пользователям, особенно тем, кто может иметь проблемы со зрением.

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