Обучение сводным таблицам Excel на примерах
Сводные таблицы в Excel – это мощный инструмент и чтобы описать все его возможности нельзя вложиться в одну статью. Возможностям сводных таблиц придаться посвящать целую книгу. В этой же статье будет обзор принципиальных свойств сводной таблицы. В двух словах можно описать ее главную особенность как инструмент для создания разного рода отчетов из суммирующих наборов. Такое определение сложно воспринять словами, поэтому сразу переходим к примерам и практике.
Работа со сводными таблицами в Excel
Создайте таблицу исходных данных о клиентах фирмы, так как показано на рисунке:
Теперь по исходной базе данных клиентов построим сводную таблицу, в которой можно легко отобразить средний возраст всех клиентов фирмы, которые не имеют дисконтную скидку c распределением на:
- возраст;
- образование;
- пол.
Решение для построения сводного отчета в Excel:
- Перейдите на любую ячейку исходной клиентской базы и выберите инструмент: «Вставка»-«Таблицы»-«Сводная таблица».
- В появившемся диалоговом окне переключите на опцию «На существующий лист» и в поле «Диапазон» укажите значение h4:
- В окне настроек (справа) «Список полей сводной таблицы» переместите значение «Пол» из поля «Выберите поля для добавления в отчет» в поле «Фильтр отчета»:
- Таким же образом распределите остальные значения по полям так как указано выше на рисунке.
- Щелкните правой кнопкой мышки по любой ячейке внутри сводной таблицы. Из появившегося контекстного меню выберите опцию: «Итоги по»-«Среднее».
- Округлите все значения до десятых с помощью формата ячеек.
Обратите внимание! В ячейке I1 мы имеем возможность указать пол (мужской, женский или оба) для сегментирования отчета.
Полезный совет! Данная сводная таблица не имеет динамического подключения баз данных к исходной таблице клиентов. Поэтому любое изменение в исходной таблице не обновляются автоматически в сводной таблице. Поэтому после каждого изменения исходных данных следует щелкнуть правой кнопкой мышки по сводной таблице и выбрать опцию «Обновить».
Тогда все данные выполнять пересчет и обновятся.
Пример настройки сводных отчетов
Теперь поменяем структуру отчета в той же сводной таблице по тем же данным.
- Если вы случайно закрыли окно «Список полей сводной таблицы», тогда щелкните левой кнопкой мышки по сводной таблице, чтобы сделать ее активной. Затем включите опцию: «Работа со сводными таблицами»-«Параметры»-«Показать»-«Список полей».
- Переместите значение «Пол» в поле «Название строк» под значение «Образование».
Структура отчета кардинально изменена:
Скачать пример обучения сводными таблицами
Появились группировки, которые управляются кнопками (+ и -). Эти вспомогательные кнопки и заголовки можно включить/отключить через меню: Работа со сводными таблицами»-«Параметры»-«Показать»-«Кнопки» или «Заголовки полей». Так же у нас больше нет фильтра для сегментирования отчета по полу в ячейке I1.
все уроки
Сводные таблицы
Здесь вы найдете больше сотни моих статей по самым лучшим, полезным и эффективным приемам, трюкам и способам работы в Microsoft Excel.
Пройдитесь по полному списку или сначала выберите раздел слева и — вперед!
Сортировка: дата создания дата изменения просмотры комментарии
Динамический «Топ-N + Другие» в сводной таблице
Как при помощи запроса Power Query реализовать в сводной таблице вывод динамического «Топ-N + Другие».
Переключение вычислений в сводной таблице срезами
Как использовать срезы (графические кнопки) для переключения режимов вычислений (сумма, среднее, количество и т.д.) в сводной таблице по модели данных Power Pivot.
Одновременная фильтрация нескольких сводных таблиц
Как фильтровать сразу несколько сводных таблиц в дашборде с помощью общего среза. Разбор 2 способов для сводных, построенных по одному и по разным источникам данных.
Преимущества сводной по Модели Данных
Как и зачем строить сводную в Excel по Модели Данных (Data Model) и какие преимущества это даёт по сравнению с классической сводной таблицей?
Сводная по таблице с многострочной шапкой
Как построить сводную таблицу по исходным данным с многострочной шапкой, преобразовав их в нормальный вид с помощью надстройки Power Query.
План-факт анализ в сводной таблице с Power Pivot и Power Query
Пошаговый разбор решения задачи план-факт анализа с использованием сводных таблиц, модели данных Power Pivot со связями «многие-ко-многим», простых мер на DAX и допиливании входных данных с помощью Power Query.
Расчет средневзвешенного значения в Excel
Что такое средневзвешенное значение и чем оно отличается от обычного среднего арифметического. Как вычислить средневзвешенное в Excel с помощью формул и в сводной таблице (в том числе с помощью DAX и Power Pivot).
Сравнение двух таблиц
Как сравнить между собой две таблицы (прайс-листа) и наглядно отобразить отличия между ними — добавленные и удаленные товары, изменение цены и т.д. Разбор 3 способов: с помощью функции ВПР (VLOOKUP), сводной таблицей и через надстройку Power Query.
Независимая группировка сводных таблиц
Что делать, если вы построили несколько сводных таблиц на основании одного источника и теперь не получается группировать их отдельно — группировка одной сводной влияет на все остальные и наоборот.
Как отвязать сводную от общего кэша?
Сводная таблица с текстом в значениях
Обычно в области значений сводных таблиц находятся числовые данные. А что делать, если нужно создать подобие сводной, но с текстом?
Создание базы данных в Excel
Пошаговый пример создания в Excel небольшой, но полноценной базы данных на примере учета продаж: с формой ввода, связанными таблицами, отчетами и печатными формами.
Зачем нужна функция ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ
Если вы когда-нибудь пытались сослаться на ячейку сводной таблицы, то должны были встречать эту функцию (от которой большинство шарахается). Зачем она нужна на самом деле и в каких случаях она может здорово помочь?
Фильтр исходных данных в сводной таблице
Как при помощи простого макроса отфильтровать те строки в исходной таблице, которые участвовали в расчете заданной ячейки сводной таблицы.
Это позволит быстро подогнать результаты сводной под нужные значения.
Новые возможности сводных таблиц в Excel 2013
Рекомендованные сводные таблицы. Построение сводной по нескольким исходным таблицам. Фильтрация с помощью Временной шкалы. Новые функции вычислений полей значений. «Погружение» в данные с помощью Экспресс-просмотра.
Подробнее…
Фильтрация сводных таблиц Срезами и Шкалой
Красивый и удобный способ быстро и наглядно отбирать данные в сводной таблице по нескольким параметрам при помощи интерактивных графических кнопочных фильтров — срезов.
Быстрая настройка форматов данных в сводной таблице
Как с помощью простого макроса быстро настроить форматы всех полей в сводной таблице, подтянув их из исходных данных.
Подробнее…
Сводная таблица по нескольким диапазонам данных
3 способа построить полноценную сводную таблицу не по одному, а по нескольким диапазонам данных, находящихся на разных листах.
Настройка вычислений в сводных таблицах
Как произвести тонкую настройку вычисляемых данных в сводной таблице (поменять сумму на количество, посчитать долевые проценты, отобразить динамику и т.д.).
Группировка в сводных таблицах
Как в сводной таблице сгруппировать даты с нужным шагом (день-месяц-квартал…), числа в заданные диапазоны (от-до) или наименования (товары по категориям).
Создание отчетов при помощи сводных таблиц
Как за несколько секунд из огромной таблицы в несколько тысяч строк сформировать компактный, наглядный и правильный отчет? Один из лучших инструментов во всем Excel!
Специальный макрос, позволяющий переконструировать Вашу «красивую» таблицу в тот вид, который понимает и с которым может работать Excel.
Сводные таблицы — Видеоруководство по Microsoft Excel
Из курса: Excel для бухгалтерии
Сводные таблицы
“
— [Преподаватель] Сводная таблица — это быстрый и простой способ суммировать информацию в Excel, который выглядит чистой магией для тех, кто с ним не знаком, но это также важный инструмент для любого бухгалтера.
Хотя сводные таблицы — отличный способ обобщить данные всего за несколько секунд, у сводных таблиц есть несколько недостатков. Во-первых, они выглядят не очень. У вас есть эта странная пустая строка здесь и эта странная вещь менее чем 1/1/21 здесь, и стиль не самый красивый, форматирование сводных таблиц сложно и требует много времени. Во-вторых, привязка формул к сводным таблицам не работает должным образом, вы никогда не захотите, чтобы ваша электронная таблица вычисляла что-то на основе результатов сводной таблицы. Сводные таблицы должны быть конечным результатом, а не промежуточным этапом. Если вы суммируете информацию в качестве промежуточного шага, используйте вместо этого СЧЁТЕСЛИ или СУММЕСЛИ. Наконец, сводные таблицы не обновляются автоматически, поэтому, если вы измените это значение обратно на сумму суммы, затем на первом листе измените эту продажу Элли в апреле на миллиард долларов, а затем снова посмотрите на свою сводную таблицу, вы увидите сумма не обновилась. В реальном мире тот факт, что ваша сводная таблица показывает устаревшую информацию, может быть не столь очевиден.
Если вы обновляете свои базовые данные, очень важно не забыть обновить свою сводную таблицу, щелкнув ее правой кнопкой мыши и выбрав «Обновить», и теперь вы видите, что появляется продажа на миллиард долларов. Сводные таблицы могут не выиграть ни одного конкурса красоты, но они значительно облегчают вашу рабочую нагрузку, когда вам нужно легко сортировать и суммировать данные. Я призываю вас поэкспериментировать с созданием собственных сводных таблиц, чтобы лучше понять этот невероятно полезный инструмент.Содержание
Excel 2013: сводные таблицы
Урок 27: сводные таблицы
/en/excel2013/conditional-formatting/content/
Введение
рабочий лист. Сводные таблицы могут помочь сделать ваши рабочие листы более управляемыми, обобщая данные и позволяя вам манипулировать ими различными способами.
Дополнительно: загрузите нашу рабочую тетрадь.
Использование сводных таблиц для ответа на вопросы
Допустим, мы хотели ответить на вопрос: Какова сумма, проданная каждым продавцом? для данных о продажах в приведенном ниже примере. Ответ на этот вопрос может занять много времени и быть сложным — каждый продавец появляется в нескольких строках, и нам нужно будет суммировать все их заказы по отдельности. Мы могли бы использовать промежуточный итог , чтобы помочь найти общую сумму для каждого продавца, но у нас все равно будет много данных для работы.
Рабочий лист, содержащий данные о продажах
К счастью, сводная таблица может мгновенно вычислять и суммировать данные таким образом, чтобы их было легко читать и ими легко манипулировать.
Когда мы закончим, сводная таблица будет выглядеть примерно так:
Завершенная сводная таблица
После того, как вы создали сводную таблицу, вы можете использовать ее для ответов на различные вопросы, меняя порядок — или поворотная —данные. Например, если мы хотим ответить на вопрос: Какова общая сумма продаж в каждом месяце? мы можем изменить нашу сводную таблицу, чтобы она выглядела следующим образом:
Сведение данных для ответов на различные вопросы
вы хотите использовать.
Выбор ячеек для сводной таблицы
При нажатии команды сводной таблицы
В нашем примере мы будем использовать Table1 в качестве исходных данных и поместим сводную таблицу на новый рабочий лист .Создание сводной таблицы
Пустая сводная таблица на отдельном рабочем листе
Проверка нужных полей
В нашем примере поле Продавец было добавлено в область Строки , а поле Сумма заказа было добавлено в область Значения . Кроме того, вы можете щелкнуть, удерживать и перетащить поле в нужную область. Добавление полей в сводную таблицу
Сводная таблица вычисляет выбранные поля
Как и в случае с обычными данными электронной таблицы, вы можете сортировать данные в сводной таблице с помощью команды Сортировка и фильтрация на вкладке Главная. Вы также можете применить любой тип форматирования номера , который вы хотите. Например, вы можете изменить числовой формат на Валюта . Однако имейте в виду, что некоторые типы форматирования могут исчезнуть при изменении сводной таблицы.
Отсортированная и отформатированная сводная таблица
Если вы измените какие-либо данные в исходном листе, сводная таблица не будет автоматически обновляться . Чтобы вручную обновить его, выберите сводную таблицу, а затем перейдите к Анализ > Обновить .
Сводка данных
Одна из лучших особенностей сводных таблиц заключается в том, что они могут быстро сводные таблицы — или реорганизовать — данные, что позволяет просматривать данные рабочего листа по-разному. Сводные данные могут помочь вам ответить на различных вопросов и даже экспериментируйте с данными, чтобы обнаружить новые тенденции и закономерности.
В нашем примере мы использовали сводную таблицу, чтобы ответить на вопрос: Какова общая сумма, проданная каждым продавцом ? Но теперь мы хотели бы ответить на новый вопрос: Какова общая сумма, продаваемая в каждый месяц ? Мы можем сделать это, просто изменив поле в области строк .
Чтобы изменить строку:
- Щелкните, удерживайте и перетащите любой существующий полей из области строк . Поле исчезнет.
Удаление поля
- Перетащите новое поле из списка полей в область строк . В нашем примере мы будем использовать поле Month .
Добавление поля
- Сводная таблица скорректируется — или повернется — для отображения новых данных. В нашем примере теперь отображается общая сумма заказа за каждый месяц.
Обновленная сводная таблица
Добавить столбцов:
До сих пор наша сводная таблица показывала только один столбец данных за раз. Чтобы отобразить несколько столбцов , вам нужно добавить поле в область столбцов .
- Перетащите поле из списка полей в область столбцов . В нашем примере мы будем использовать поле Region .
Добавление поля в область столбцов
- Сводная таблица будет включать несколько столбцов. В нашем примере теперь есть столбец для каждого региона.
Сводная таблица со столбцами
В нашем примере мы будем использовать поле Region .Фильтры
Иногда вам может понадобиться сосредоточиться только на определенном разделе ваших данных. Фильтры можно использовать для сужения данных в сводной таблице, позволяя просматривать только необходимую информацию.
Чтобы добавить фильтр:
В нашем примере мы будем отфильтровывать определенных продавцов, чтобы определить, как они влияют на общий объем продаж.
- Перетащите поле из списка полей в список Фильтры области. В этом примере мы будем использовать поле Salesperson .
Добавление поля в область фильтров
- Фильтр появится над сводной таблицей. Щелкните стрелку раскрывающегося списка , затем установите флажок рядом с Выбрать несколько элементов .
Установка флажка для выбора нескольких элементов
- Снимите флажок для любых элементов, которые вы не хотите включать в сводную таблицу. В нашем примере мы снимем флажки для нескольких разных продавцов, а затем нажмем 9.0029 ОК .
Выбор данных для фильтрации и нажатие кнопки «ОК»
- Сводная таблица будет изменена, чтобы отразить изменения.
Обновленная сводная таблица
Щелкните стрелку раскрывающегося списка , затем установите флажок рядом с Выбрать несколько элементов .Срезы
Срезы еще больше упрощают фильтрацию данных в сводных таблицах. Слайсеры — это всего лишь фильтры , но их проще и быстрее использовать, что позволяет мгновенно менять данные. Если вы часто фильтруете свои сводные таблицы, вы можете рассмотреть возможность использования срезов вместо фильтров.
Чтобы добавить срез:
- Выберите любую ячейку в сводной таблице.
- На вкладке Analyze нажмите команду Insert Slicer .
Щелчок по команде «Вставить слайсер»
- Появится диалоговое окно. Выберите нужное поле . В нашем примере мы выберем Продавец , затем нажмем OK .
Выбор поля и нажатие OK
- Слайсер появится рядом со сводной таблицей. Каждый выбранный элемент будет выделен в синий . В приведенном ниже примере слайсер содержит список всех продавцов, из которых выбрано шесть .
Вставленный слайсер
- Так же, как фильтры , в сводной таблице используются только выбранных элементов. Когда вы выбираете или отменяете выбор элементов, сводная таблица мгновенно отражает изменения. Попробуйте выбрать разные элементы, чтобы увидеть, как они влияют на сводную таблицу. Нажмите и удерживайте клавишу Ctrl на клавиатуре, чтобы выбрать несколько элементов из слайсера.
Выбор элементов из слайсера
Попробуйте выбрать разные элементы, чтобы увидеть, как они влияют на сводную таблицу. Нажмите и удерживайте клавишу Ctrl на клавиатуре, чтобы выбрать несколько элементов из слайсера.Вы также можете щелкнуть значок фильтра в правом верхнем углу, чтобы выбрать сразу все элементы из слайсера.
Сводные диаграммы
Сводные диаграммы похожи на обычные диаграммы, за исключением того, что они отображают данные из сводной таблицы . Как и в случае с обычными диаграммами, вы сможете выбрать тип диаграммы , макет и стиль , которые лучше всего представляют данные.
Для создания сводной диаграммы:
В этом примере наша сводная таблица показывает общий объем продаж каждого человека за месяц. Мы будем использовать сводную диаграмму, чтобы мы могли видеть информацию более четко.
- Выберите любую ячейку в сводной таблице.
Щелкните ячейку в сводной таблице
- На вкладке Вставка выберите команду Сводная диаграмма .
При нажатии команды PivotChart
- Появится диалоговое окно Вставить диаграмму . Выберите нужный тип диаграммы и макет , затем нажмите OK .
Выберите тип диаграммы и нажмите OK
- Появится сводная диаграмма.
Вставленная сводная диаграмма
Попробуйте использовать срезы или фильтры , чтобы изменить отображаемые данные. Сводная диаграмма автоматически настроится для отображения новых данных.
Управление сводной диаграммой
Вызов!
- Откройте существующую книгу Excel.
