Ребята, всем привет! 👋 В прошлом уроке мы завершили серию уроков посвященных работе с большими табличными массивами. Но все же наиболее удобным встроенным инструментом для эффективного анализа больших табличных данных является сводная таблица.
О том, как создается сводная таблица сегодня и поговорим.
✨ А прежде, чем мы начнем 📣 напомню, теперь все видео 📽 предыдущих уроков доступны в 👉 одном месте.
📚 По уже сложившейся традиции... немного теории
Сводная таблица — это эффективный инструмент для вычисления, сведения и анализа данных, который упрощает поиск сравнений, закономерностей и тенденций.
Отчет сводной таблицы:
- позволяет обобщать, анализировать, изучать и представлять данные из больших табличных данных;
- может быть особенно полезным в ситуации, когда необходимо просуммировать большой набор чисел.
🔔 Основное преимущество сводной таблицы состоит в том, что объединенные данные и промежуточные итоги позволяют взглянуть на данные под разными углами и сравнить однотипные показатели из разных групп данных.
При создании отчета сводной таблицы каждый из столбцов исходных данных становится полем, которое можно использовать в отчете.
⚠️ Обратите внимание! Перед тем как перейти к созданию сводной таблицы следует убедиться, что в первой строке таблицы, используемой в качестве источника данных, для каждого из столбцов указано имя и таблица не содержит объединенных ячеек.
⏩ Как создать сводную таблицу?
📝 Изучение рассмотрим на примере.
Пусть мы имеем некоторую таблицу содержащую следующие данные:
Для того, чтобы создать сводную таблицу нужно:
📍 ШАГ 1. Выделить любую ячейку таблицу и на вкладке Вставка[Insert], в группе Таблицы [Table], выбрать Сводная таблица [PivotTable]
📍 ШАГ 2. В диалоговом окне Создание сводной таблицы [Create PivotTable] проверить правильность выделения диапазона данных или выделить новый источник данных в поле Таблица или диапазон [Table/Range]:
⚠️ Обратите внимание! Диапазон исходных данных, на основе которого строится отчет сводной таблицы, должен быть выделен с заголовками столбцов и не должен включать итоговые данные.
📍 ШАГ 3. Определить место размещения сводной таблицы: На новый лист [New Worksheet] или На существующий лист [Existing Worksheet]
📍 ШАГ 4. Жмем 🆗
И получаем результат. На листе появилась графическая область с указанием имени сводной таблицы - Сводная таблица 1 [PivotTable 1].
По умолчанию имени сводной таблицы присваивается нумерация. Если выделена произвольная ячейка на листе, то в графической области появляется надпись: Чтобы начать работу с отчетом сводной таблицы, щелкните в этой области
При активной ячейке в области отчета надпись заменяется на: Чтобы построить отчет, выберите поля из списка полей сводной таблицы
📍 ШАГ 5. Сформировать отчет сводной таблицы
- Чтобы начать работу с отчетом сводной таблицы, щелкните в этой области [Click in this area to work with the PivotTable report]
и списке полей в области Поля сводной таблицы (отображается справа) выберите нужные поля для добавления в отчет, например:
- Каждая область макета, куда помещаются поля исходной таблицы, имеет свое назначение, определяющее внешний вид сводной таблицы и ее функции. Макет отчета состоит из 4 частей:
Фильтры [Filters] – фильтр отчета сводной таблицы. Если установлен фильтр, то построение и расчет данных сводной таблицы ведется для заданного значения.
Строки [Rows] – формируют заголовки строк сводной таблицы, если размещено несколько полей, то они размещаются в макете сверху вниз, обеспечивая группирование данных сводной таблицы по иерархии полей (для каждого элемента внешнего поля, элементы внутреннего поля повторяются).
Столбцы [Columns] – формируют заголовки столбцов сводной таблицы, если таких полей несколько, то они в макете размещаются слева направо, обеспечивая группирование данных сводной таблицы по иерархии полей.
Значения [ Values] – обязательная область макета для размещения полей, по которым подводятся итоги, согласно выбранной функции. Размещаемые здесь поля могут быть произвольных типов. Если в расчетной области расположено несколько полей, то в области макета Столбцы [Columns] автоматически появляется поле Значения [ Values], которое можно при необходимости переместить в область Строки [Rows].
🔔 Чтобы поместить поле в область макета по умолчанию, нужно установить флажок напротив имени данного поля в разделе полей.
🔔 Чтобы поместить поле в определенную область раздела макета, можно щелкнуть правой кнопкой мыши по имени соответствующего поля в разделе полей перетащить поле в нужную область макета или выбрать команду:
- Добавить в фильтр отчета [Add to Report Filter],
- Добавить в названия строк [Add to Row Labels],
- Добавить в названия столбцов [Add to Column Labels],
- Добавить в значения [Add to Values]
В условиях рассматриваемого примера поместим поля в следующие области раздела макета:
🔔 Если источник содержит много полей, то удобно воспользоваться мгновенным поиском через поле Поиск [Search].
☑️ В условиях рассматриваемого примера наша сводная таблица примет вид:
Т.к. наименование товара мы перенесли в фильтр, то выбрав нужный товар, например игровая приставка:
☑️ Получаем результат:
Как вы заметили, заголовкам столбцов присвоены имена отличные от наименований в исходной таблицы. Логично их изменить и присвоить соответствующие названия. Для этого:
- в режиме Конструктора в группе Макет выбираем Показать в табличной форме или Показать в форме структуры:
- затем, в области раздела макета Значение изменим Пользовательское имя (чтобы избежать сообщения об ошибке добавим например пробел до или после изменяемого имени):
☑️ Получаем результат:
⏩ Преобразование сводных таблиц
В любой момент макет сводной таблицы можно изменить, но для этого необходимо наличие области Поля сводной таблицы [PivotTable Fields].
🔔 Если при активной ячейке отчета сводной таблицы, список полей сводной таблицы не отображается, то его необходимо вернуть
Отсутствие области Поля сводной таблицы может возникнуть если вы нажали ✖️
Чтобы вернуть области Поля сводной таблицы нужно:
- щелкнуть правой кнопкой мыши по ячейке сводной таблицы и выбрать Показать список полей [Show Field List]
- или на вкладке Анализ [Analyze], в группе Показать [Show], выбрать Список полей [Field List].
- Добавить поле в нужный раздел макета – перетащить поле из списка полей в нужную область раздела макета сводной таблицы.
- Переместить поле из одного раздела макета в другую – перетащить поле в нужную область раздела макета.
Например,
И применив в режиме Конструктора в группе Макет -->> Показать в табличной форме:
Получаем результат:
🔔 Если требуется удалить поле из отчета – убрать флажок в списке полей, щелкнуть по полю в разделе макета и выбрать Удалить поле [Delete Field] или перетащить поле из раздела макета в список полей.
🔔 Если исходная таблица, на основании которой построен отчет состоит из большого количества данных, то процесс изменения отчета будет сопровождаться потерями по времени, необходимыми для расчета данных. Чтобы этого избежать:
- В области Поля сводной таблицы [PivotTable Fields] включить флажок Отложить обновление макета [Defer Layout Update].
- Изменить макет, например:
- Нажать кнопку Обновить [Refresh] для построения и расчета измененного отчета.
- Убрать флажок Отложить обновление макета [Defer Layout Update].
☑️ Получаем результат:
А на этом сегодня все. Теперь создание любой сводной таблицы будет вам под силу 👏 Продолжение следует...
Подписывайтесь на канал, чтобы не пропустить новые уроки и полезные фишки Excel. Следите за нашими новостями и вы узнаете больше о VBA и Excel в частности.
В следующих уроках более подробно рассмотрим:
☑ фильтры, срезы, временные шкалы в сводных таблицах;
☑ настройку полей сводной таблицы;
☑ повторное использование исходного поля в отчете сводной таблицы;
☑ добавление вычисляемых полей в сводную таблицу и др.
За лайк 👍 и репост 🔁 данного поста благодарочка 💖 и респект 🤝 каждому!
📝 Нам очень важна Ваша обратная связь. Отзыв по уроку и любые вопросы пишите в комментариях.
#сводные таблицы в excel #отчет сводной таблице #преобразование сводных таблиц #таблицы excel #обучение excel #фишки excel #примеры excel #анализ данных в excel #как создать сводную таблицу #пример сводной таблицы в excel
