Узнаем как сделать сводную таблицу в Excel, которая позволяет быстро и удобно анализировать большие объемы данных, а также научимся ее корректно обновлять.

Приветствую всех, дорогие читатели блога TutorExcel.Ru!

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

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

При анализе данных перед нами может встать ряд вопросов, к примеру:

Но смотря на обычную таблицу мы ответов найти скорее всего не сможем, а вот сводная таблица нам в этом прекрасно поможет.

Давайте пошагово разберем как создать сводную таблицу.

Меня регулярно спрашивают, как бы так выгрузить дашборд из Power BI в PowerPoint? Чтобы диаграммы в презентации были такими же интерактивными. Но, к сожалению, экспорт в PPT работает так же, как и в PDF — статичной картинкой.

Создание сводной таблицы на основе внешнего источника данных (на примере MS Access) — Сводные таблицы — Эффективная работа в Excel — Статьи об Excel — Мир MS Excel

• Умная таблица получает имя (в примере выше Таблица1, его можно легко поменять), которое можно использовать для определения диапазона в качестве источника для сводной таблицы;
• Умная таблица автоматически изменяет размер при добавлении или удалении новых строк или столбцов.

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

Exceltip

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

Выберите Сводная таблица из выпадающего меню. В появившемся диалоговом окне Создание сводной таблицы, выберите расположение на новом листе.

Следует обратить внимание, что в отличие от стандартной сводной таблицы пустая сводная таблица PowerPivot находится в ячейках B3:D20, что позволяет размещать срезы слева и верхней части таблицы.

Справа вы увидите список полей PowerPivot. Это уже третье изменение списка полей сводных таблиц.

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

Обратите внимание, что появились два новых поля Вертикальные срезы и Горизонтальные срезы.

Так как вы находитесь в сводной таблице, вам доступна вкладка Работа со сводными таблицами на ленте Excel.

Поставьте галочку напротив поля Revenue. Так как это поле содержит числовые данные, оно автоматически попадет в поле Значения, находящееся в нижнем правом углу списка полей. В сводной таблице вы увидите сумму по полю Revenue, которая будет содержать итоговое значение 1,8 млн строк.

Разверните таблицу StoreInfo и поместите поле Region в Метки строк, а поле Division в Метки столбцов. Excel построит сводную таблицу, используя данные с двух разных таблиц, которые связаны между собой.

Перетащите поле Store Name в Метки строк, так чтобы оно оказалось под полем Region.

Чтобы похвастаться новыми возможностями сводных таблиц PowerPivot, давайте добавим несколько срезов:

Наконец, необходимо отформатировать получившуюся сводную таблицу

1. Во вкладке Работа со сводными таблицами -> Конструктор в группе Параметры стилей сводной таблицы поставьте галочку напротив Чередующиеся строки.
2. В этой же вкладке в группе Стили сводной таблицы,выберитепонравившийся стиль оформления сводной таблицы.
3. Перейдите во вкладку Конструктор. В группе Активное поле должна быть надпись Сумма Revenue. Если надпись другая, выберите любое числовое поле в сводной таблице.
4. В этой же группе щелкните по Параметры поля.
5. В появившемся диалоговом окне Параметры поля значений в нижнем левом углу щелкните по кнопке Числовой формат.
6. В списке форматов выберите Числовой, установите число десятичных знаков равным нулю и поставьте галочку напротив поля Разделитель групп разрядов. Нажмите ОК, чтобы закрыть окно Параметры поля значений и нажмите ОК, чтобы закрыть окно Формат ячеек.

В результате у нас получился впечатляющий интерактивный отчет.

Если вы выберите в срезе 2000 год, отчет отобразит только данные релевантные для 2000 года. К тому же срез Mall Developer тоже изменит свой вид, активными остануться только 5 полей, которые соотвтвтуют 2000 году.

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

Обучение: Сводные таблицы 2.0. Excel и новые инструменты (Power Pivot и Power Query) для работы с большими объемами данных | Новосибирск |

Если функция VALUES() имеет единственное значение, то в значения сводной таблицы его можно добавить текстом, а не только рассчитать количество таких значений, чем ограничивается стандартный Excel.

2013 год. Power View и визуальная аналитика

Power View появился в 2010 году в комплекте Share Point, а в 2013 стал составляющей Экселя. Это был шаг вперед в области визуализации. Именно она значительно «оживила» данные и представления о них. Если раньше self-service подход с Power Pivot был доступен только продвинутым пользователям, то в Power View можно было легко и просто собрать динамический дашборд, да еще и с картами.

Минусом было то, что для работы необходимо было установить Silverlight последней версии. И обычно на чужом компьютере такой отчет сразу не запускался. А настройки форматирования были очень ограничены. В целом эта первая версия модуля больше напоминала пресейловую демоверсию и для реальных внедрений не подходила.

В период с 2013 по 2016 мы отошли от BI-проектов на Майкрософте, вся эта идеология платформы-конструктора не выдерживала конкуренции с другими вендорами. Мы занимались Qlikview и IBM Cognos. И даже, когда в 2015 вышла первая версия Power BI, он был настолько сырой, что проще было настроить отчеты в связке Power Pivot и сводных таблиц.

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

Файл → Параметры → Надстройки → Надстройки COM → Microsoft Power Pivot.

Сводная таблица с текстовыми полями в области значений

Сводные таблицы – простой, удобный и быстрый инструмент для анализа данных и для построения отчетов. Но они будут хорошо работать только, если исходная база данных подготовлена корректно. Подготовка базы данных – это один из основных и трудоемких моментов работы со сводными таблицами. На семинаре будет подробно рассмотрено: