Что такое пивот таблица
Создание отчетов с помощью Сводных таблиц (Pivot Tables)
Представьте, что вам нужно с большой таблицы, которая содержит множество разнообразной информации сделать краткий аналитический отчет для принятия решений. Предположим, вы имеете данные, где отображается информация по кредитному портфелю Банка, где есть записанная подробная информация по каждому заемщику. Поэтому, для анализа кредитного портфеля, из базы данных в Excel выгружается примерно вот-такая таблица:
В ней каждая отдельная строка содержит полную информацию об одной кредитной сделке:
Естественно, если менеджеры по продажам знают свое дело и вкалывают всерьез, то каждый день этой таблице будет дописываться несколько десятков строк и до конца, например, года или хотя бы квартала размеры таблицы станут ужасными.
Однако еще больший ужас вызовет у Вас необходимость создания отчетов по этим данным. Сколько выдали кредитов в каждом месяце? Какова величина кредитного портфеля в разрезе направлений кредитования по количеству и сумме? Десять крупнейших заемщиков? и т.д.
Появится вот такое окно, в котором мы можем выбрать куда поместить нашу сводную таблицу и скорректировать диапазон данных.
Работа с макетом
То, что Вы увидите, нажав кнопку OK называется макет (layout) сводной таблицы:
И получаем следующий вид:
Не так уж это все и сложно, не так ли?
Создание первой сводной таблицы
Исходные данные
Сейчас на простом примере мы научимся создавать сводные таблицы и познакомимся с некоторыми их возможностями.
Для примера в качестве исходных данных возьмем ведомость основных средств компании и определим стоимость позиций по каждому филиалу с помощью сводной таблицы.
Перед созданием сводной таблицы убедитесь, что в исходной таблице отсутствуют пустые заголовки. Это требуется потому что каждый столбец таблицы становиться полем сводной таблицы, по которому можно собрать данные.
Рекомендуемые сводные таблицы
Пользователи Excel 2013 могут выбрать сводную таблицу из макета на основе рекомендаций, которые предлагает Excel. Вот как это делается:
Выберите любую ячейку исходной таблицы.
Перейдите на вкладку ленты Вставка [Insert] и найдите группу Таблицы [Table].
Выберите команду Рекомендуемые сводные таблицы [Recommended PivotTables].
Выберите подходящую таблицу в списке слева. После выборы в окне справа вы увидите макет будущей таблицы.
Самостоятельное создание сводной таблицы с помощью конструктора
Для тех, у кого нет Excel 2013, или же если нужной сводной таблицы нет в предложенных, можно создать ее с нуля с помощью конструктора. Для этого:
Выберите любую ячейку исходной таблицы.
Перейдите на вкладку ленты Вставка [Insert] и найдите группу Таблицы [Table].
Выберите команду Сводная таблица [PivotTable].
В диалоговом окне Создание сводной таблицы [Create PivotTable] убедитесь в правильности диапазона данных, на основе которого будет строиться отчет. Если диапазон некорректный, его нужно поменять в поле Таблица и диапазон [Table/Range].
Важно! При выделении диапазона убедитесь, что выбрана таблица с заголовками, а так же что выбранный диапазон не включает строку итогов.
Так же укажите, где необходимо разместить отчет сводной таблицы: на новом листе или на уже существующий.
Советую размещать каждый отчет сводной таблицы на отдельном листе. Это поможет избежать ошибок в случае если будут добавлять данные.
После формирования сводной таблицы в выбранном месте появиться область с ее названием. По умолчанию отчет называется СводнаяТаблица1 [PivotTable 1]. Для начала работы необходимо щелкнуть левой клавшей мыши по данной области. В результате в правой части листа откроется макет сводной таблицы.
Макет сводной таблицы состоит из списка полей сводной таблицы, в котором перечислены все заголовки исходной таблицы и четырех областей: ФИЛЬТРЫ, КОЛОННЫ, СТРОКИ и ЗНАЧЕНИЯ.
В зависимости от того в какую область мы перенесем то или иное поле будет зависеть вид будущей сводной таблицы.
В качестве примера давайте создадим сводный отчет, в котором посчитаем рыночную стоимость для каждой группы из исходной таблицы. Т.е. мы хотим, чтобы напротив группы стали строками сводного отчета. Значит нужно перетащить поле Группа в область строк.
Данную операцию можно сделать еще 2 способами:
После добавления поля вы увидите список всех групп, которые есть в исходной таблице:
Теперь осталось добавить сумму по полю Рыночная стоимость. Для этого перенесем поле Рыночная стоимость в область значений. Желаемая таблица получена.
Создание сводной таблицы для анализа данных листа
Сводная таблица — это эффективный инструмент для вычисления, сведения и анализа данных, который упрощает поиск сравнений, закономерностей и тенденций.
Работа с помощью стеблей немного отличается в зависимости от того, какую платформу вы используете для Excel.
Создание в Excel для Windows
Выделите ячейки, на основе которых вы хотите создать сводную таблицу.
Примечание: Ваши данные не должны содержать пустых строк или столбцов. Они должны иметь только однострочный заголовок.
На вкладке Вставка нажмите кнопку Сводная таблица.
В разделе Выберите данные для анализа установите переключатель Выбрать таблицу или диапазон.
В поле Таблица или диапазон проверьте диапазон ячеек.
В разделе Укажите, куда следует поместить отчет сводной таблицы установите переключатель На новый лист, чтобы поместить сводную таблицу на новый лист. Можно также выбрать вариант На существующий лист, а затем указать место для отображения сводной таблицы.
Настройка сводной таблицы
Чтобы добавить поле в сводную таблицу, установите флажок рядом с именем поля в области Поля сводной таблицы.
Примечание: Выбранные поля будут добавлены в области по умолчанию: нечисловые поля — в область строк, иерархии значений дат и времени — в область столбцов, а числовые поля — в область значений.
Чтобы переместить поле из одной области в другую, перетащите его в целевую область.
Данные должны быть представлены в виде таблицы, в которой нет пустых строк или столбцов. Рекомендуется использовать таблицу Excel, как в примере выше.
Таблицы — это отличный источник данных для сводных таблиц, так как строки, добавляемые в таблицу, автоматически включаются в сводную таблицу при обновлении данных, а все новые столбцы добавляются в список Поля сводной таблицы. В противном случае вам потребуется либо изменить исходные данные для pivotTable,либо использовать формулу динамического именоваемого диапазона.
Все данные в столбце должны иметь один и тот же тип. Например, не следует вводить даты и текст в одном столбце.
Сводные таблицы применяются к моментальному снимку данных, который называется кэшем, а фактические данные не изменяются.
Если у вас недостаточно опыта работы со сводными таблицами или вы не знаете, с чего начать, лучше воспользоваться рекомендуемой сводной таблицей. При этом Excel определяет подходящий макет, сопоставляя данные с наиболее подходящими областями в сводной таблице. Это позволяет получить отправную точку для дальнейших экспериментов. После создания рекомендуемой сводной таблицы вы можете изучить различные ориентации и изменить порядок полей для получения нужных результатов.
Вы также можете скачать интерактивный учебник Создание первой сводной таблицы.
1. Щелкните ячейку в диапазоне исходных данных или таблицы.
2. Перейдите на вкладку > рекомендуемая ст.
«Рекомендуемые сводные таблицы» для автоматического создания сводной таблицы» loading=»lazy»>
3. Excel данные анализируются и представлены несколько вариантов, например в этом примере с использованием данных о расходах семьи.
4. Выберите наиболее подбираемую для вас сетовую и нажмите кнопку ОК. Excel создаст сводную таблицу на новом листе и выведет список Поля сводной таблицы.
1. Щелкните ячейку в диапазоне исходных данных или таблицы.
2. Перейдите в > Вставить.
Если вы используете Excel для Mac 2011 или более ранней версии, кнопка «Сводная таблица» находится на вкладке Данные в группе Анализ.
3. Excel отобразит диалоговое окно Создание таблицы с выбранным диапазоном или именем таблицы. В этом случае мы используем таблицу «таблица_СемейныеРасходы».
4. В разделе Выберите, куда следует поместить отчет таблицы выберите новый или существующий. При выборе варианта На существующий лист вам потребуется указать ячейку для вставки сводной таблицы.
5. Нажмитекнопку ОК, Excel создаст пустую стебли и отобразит список полей.
Список полей сводной таблицы
В области Имя поля в верхней части выберите любое поле, которое вы хотите добавить в свою сетную. По умолчанию не числовые поля добавляются в область строк, поля даты и времени — в область Столбец, а числовые — в область значений. Вы также можете вручную перетащить любой доступный элемент в любое поле или, если элемент в ней больше не нужен, просто перетащите его из списка поля или снимите. Возможность переустановить элементы полей — одна из функций, которые упрощают быстрое изменение внешнего вида.
Список полей сводной таблицы
По умолчанию поля в области значений отображаются как СУММ. Если Excel данные интерпретируются как текст, они отображаются как счёт. Именно поэтому так важно не смешивать типы данных для полей значений. Вы можете изменить вычисление по умолчанию, щелкнув стрелку справа от имени поля и выбрав Параметры полей.
Совет: Так как при изменении способа вычисления в разделе Суммировать по обновляется имя поля сводной таблицы, не рекомендуется переименовывать поля сводной таблицы до завершения ее настройки. Вместо того чтобы вручную изменять имена, можно выбрать пункт Найти ( в меню «Изменить»), в поле Найти ввести Сумма по полю, а поле Заменить оставить пустым.
Значения также можно выводить в процентах от значения поля. В приведенном ниже примере мы изменили сумму расходов на % от общей суммы.
Вы можете настроить такие параметры в диалоговом окне Параметры поля на вкладке Дополнительные вычисления.
Отображение значения как результата вычисления и как процента
Просто перетащите элемент в раздел Значения дважды, щелкните значение правой кнопкой мыши и выберите команду Параметры поля, а затем настройте параметры Суммировать по и Дополнительные вычисления для каждой из копий.
При добавлении новых данных в источник необходимо обновить все основанные на нем сводные таблицы. Чтобы обновить одну сводную таблицу, можно щелкнуть правой кнопкой мыши в любом месте ее диапазона и выбрать команду Обновить. При наличии нескольких сводных таблиц сначала выберите любую ячейку в любой сводной таблице, а затем на ленте откройте вкладку Анализ сводной таблицы, щелкните стрелку под кнопкой Обновить и выберите команду Обновить все.
Если вы создали и решили, что вам больше не нужна, вы можете просто выбрать весь диапазон, а затем нажать кнопку УДАЛИТЬ. Она не влияет на другие данные, с ее стебли или диаграммы. Если сводная таблица находится на отдельном листе, где больше нет нужных данных, вы можете просто удалить этот лист. Так проще всего избавиться от сводной таблицы.
Примечание: Мы постоянно работаем над улучшением работы с Excel для Интернета. Новые изменения внося постепенно, поэтому если действия, которые вы в этой статье, возможно, не полностью соответствуют вашему опыту. Все обновления в конечном итоге будут обновлены.
Выберите таблицу или диапазон данных на листе, а затем выберите Вставить >, чтобы открыть область Вставка таблицы.
Вы можете вручную создать собственную или рекомендованную советку. Выполните одно из следующих действий:
Примечание: Рекомендуемые стеблицы доступны только Microsoft 365 подписчикам.
На карточке Создание собственной таблицы выберите Новый лист или Существующий лист, чтобы выбрать место назначения для этой таблицы.
В рекомендуемой pivottable выберите Новый лист или Существующий лист, чтобы выбрать место назначения для этой таблицы.
Сводные таблицы в SQL
Сводная таблица – один из самых базовых видов аналитики. Многие считают, что создать её средствами SQL невозможно. Конечно же, это не так.
Предположим, у нас есть таблица с данными закупок нескольких видов товаров (Product 1, 2, 3, 4) у разных поставщиков (A, B, C):
Типичная задача – определить размер закупок по поставщикам и товарам, т.е. построить сводную таблицу. Пользователи MS Excel привыкли получать такую аналитику буквально парой кликов:
В SQL это не так быстро, но большинство решений тривиальны.
1. Оператор CASE и аналоги
Если продублировать код для всех поставщиков (которых у нас три — A, B, C), мы получим необходимую нам сводную таблицу:
В неё можно добавить итог по строкам (как обычную сумму, т.е. sum(t.volume) ):
Если ваша СУБД настолько стара, что не поддерживает rollup, – придётся использовать костыли. Например, так:
Особенность FILTER в том, что он является частью стандарта (SQL:2003), но фактически поддерживается только в PostgreSQL и SQLite.
В других СУБД есть ряд эквивалентов CASE, не предусмотренных стандартом: IF в MySQL, DECODE в Oracle, IIF в SQL Server 2012+, и т.д. В большинстве случаев их использование не несёт никаких преимуществ, лишь усложняя поддержку кода в будущем.
2. Использование PIVOT (SQL Server и Oracle)
Описанный выше подход трудно назвать красивым. Как минимум, хочется не дублировать код для каждого поставщика, а просто их перечислить. Сделать это позволяет разворот (PIVOT) таблицы, доступный в в SQL Server и Oracle. Хотя этот оператор не предусмотрен стандартом SQL, обе СУБД предлагают идентичный синтаксис.
Для начала нам необходима таблица с агрегированной статистикой, которую мы «развернём». Казалось бы, для этого достаточно взять суммы по товару и провайдеру:
И этого будет достаточно – если нам нужны итоги только по товарам и по провайдерам. Если же мы хотим получить все возможные итоги, необходимо выбрать все возможные сочетания товара и провайдера, в том числе такие где товар или провайдер NULL :
Этот запрос можно существенно упростить, используя оператор CUBE :
Если мы хотим получить подпись итогов как ‘total_sum’ вместо NULL запрос необходимо немного откорректировать:
К такому результату уже можно применять PIVOT:
3. Common table expression
В принципе, для «поворота» таблицы нам не нужен оператор PIVOT как таковой. Этот запрос можно легко переписать, используя стандартный синтаксис — комбинацию CTE (common table expression) и соединений. Для этого будем использовать тот же запрос, что и для PIVOTа:
Из результатов, полученных в cte нам необходимы только уникальные значения товаров:
… к которым можно поочередно присоединять объем закупок для каждого отдельно взятого поставщика:
Здесь мы используем левое соединение т.к. у поставщика может не быть поставок по некоторым продуктам.
Окончательный запрос будет выглядеть таким образом:
Конечно, такой запрос — это proof-of-concept, поэтому выглядит он довольно экзотично.
4. Функция CROSSTAB (PostgreSQL)
Ещё один нюанс состоит в том, что CROSSTAB заполняет строки слева направо, игнорируя NULL-овые значения. Например, такой запрос:
… вернёт совсем не то, что мы хотим:
Как можно заметить, там, где были NULL-овые значения, всё «съехало» влево. Например, в первой строке для Product1 итог по строке оказался в столбце для поставщика С, а поставки С — в столбце поставщика В (для которого поставок не было). Корректно проставлены данные только для Product3 т.к. для этого товара у всех поставщиков были значения. Иными словами, если бы у нас не было NULL-овых значений, запрос был бы корректным и вернул нужный результат.
Чтобы не сталкиваться с таким поведением CROSSTAB нужно использовать вариант функции с двумя параметрами. Второй параметр должен содержать запрос, выводящий список всех столбцов в результате. В нашем случае это все названия поставщиков из таблицы + «total_sum» для итогов:
… а полный запрос будет выглядеть так:
5. Динамический SQL (на примере SQL Server)
Запрос с PIVOT или CROSSTAB уже функциональнее, чем изначальный с CASE (или CTE), но названия поставщиков все ещё необходимо вносить вручную. Но что делать, если поставщиков много? Или если их список регулярно обновляется? Хотелось бы выбирать их автоматически как как select distinct supplier from test_supply (или же из словаря, если он есть).
Например, в SQL Server мы можем использовать STUFF для получения такой строки
… а затем включить её в окончательный запрос:
Здесь используется цикл для итерации по доступным поставщикам в таблице test_supply (можно заменить на словарь, если он есть), после чего формируется соответствующий кусок запроса:
Во многих СУБД доступно аналогичное решение. Тем не менее, мы уже слишком отдалились от чистого SQL. Любое использование динамического SQL подразумевает углубление в специфику конкретной СУБД (и соответствующего ей процедурного расширения SQL).
Итого: как мы выяснили, сводную таблицу можно легко создать средствами SQL. Более того, это можно множеством разных методов — достаточно лишь выбрать оптимальный для вашей СУБД.
Power Pivot: мощные средства анализа и моделирования данных в Excel
Power Pivot — это Excel надстройка, которая используется для выполнения мощного анализа данных и создания сложных моделей данных. С помощью Power Pivot можно работать с большими объемами данных из различных источников одновременно, быстро анализировать их и распространять результаты.
Примечание. Прежде чем приступить к подробной информации, вы можете вернуться к видеоролику или воспользоваться учебным руководством по & преобразованию и Power Pivot.
Импорт миллионов строк данных из нескольких источников С помощью Power Pivot для Excel можно импортировать миллионы строк данных из нескольких источников в одну книгу Excel, создавать связи между разнородными данными, создавать вычисляемые столбцы и меры с помощью формул, создавать систетные и свиты, а затем дополнительно анализировать данные, чтобы принимать вовремя деловые решения, не требуя помощи ИТ-поддержки.
Быстрое вычисление и анализ Обработка миллионов строк примерно одновременно с тысячами и максимальное количество многоядерных процессоров и гигабайт памяти для быстрой обработки вычислений. Преодолеет существующие ограничения для масштабного анализа данных на компьютере с помощью эффективных алгоритмов сжатия для загрузки даже самых крупных наборов данных в память.
Практически неограниченная поддержка источников данных Служит основой для импорта и объединения исходных данных из любого места для масштабного анализа на компьютере, включая реляционные базы данных, многомерные источники, облачные службы, веб-каналы данных, Excel файлы, текстовые файлы и данные из Интернета.
Панель управленияPower Pivot безопасности и управления позволяет ИТ-администраторам отслеживать общие приложения и управлять ими для обеспечения безопасности, высокой доступности и производительности.
Выражения анализа данных (DAX) DAX — это язык формул, расширяющий возможности работы с данными в Excel для более сложной группировки, вычислений и анализа. Синтаксис формул DAX очень похож на синтаксис Excel формул.
Задачи в Power Pivot или в Excel
Основное различие между Power Pivot и Excel заключается в том, что в окне Power Pivot можно создавать более сложные модели данных. Вот некоторые задачи для сравнения.
Импорт данных из различных источников, таких как большие корпоративные базы данных, публичные ленты новостей, электронные таблицы и текстовые файлы на локальном компьютере.
Импорт всех данных из источника данных.
Фильтрация данных и переименование столбцов и таблиц при импорте.
Прочитайте о том, как получать данные с помощью надстройки Power Pivot
Таблицы могут находиться на любом листе книги. Листы могут содержать несколько таблиц.
Таблицы организованы в виде отдельных страниц с вкладками в окне Power Pivot.
Редактирование данных в таблице
Можно изменять значения в отдельных ячейках таблицы.
Нельзя изменять отдельные ячейки.
Создание связей между таблицами
В диалоговом окне «Связи».
В представлении диаграммы или диалоговом окне «Создание связей».
Расширенные формулы на языке выражений анализа данных (DAX).
Иерархии можно создавать и использовать везде в книге, в том числе в Power View.
Создание ключевых показателей эффективности
Создавайте KPIS для использования в свиттах и отчетах Power View.
Создаваемые перспективы позволяют ограничить число столбцов и таблиц, которые видны пользователям книги.
Создание сводных таблиц и сводных диаграмм
Нажмите кнопку PivotTable в окне Power Pivot.
Расширение модели для Power View
Создается базовая модель данных.
Доступны расширения, например определение полей по умолчанию, изображений и уникальных значений.
Использование Visual Basic для Applications (VBA)
VBA не поддерживается в окне Power Pivot окна.
DaX используется в вычисляемом столбце и вычисляемом поле.
Способ хранения данных
Данные, которые вы работаете в Excel и в окне Power Pivot, хранятся в аналитической базе данных внутри книги Excel, а мощный локальный механизм загружает, запрашивает и обновляет данные в этой базе данных. Так как данные Excel, они сразу же доступны для сводных и сводных книг, Power View и других функций в Excel, которые используются для агрегированных данных и взаимодействия с ними. Все презентации данных и взаимодействие предоставляются Excel; данные и Excel презентации содержатся в одном файле книги. Power Pivot поддерживает файлы размером до 2 ГБ и позволяет работать с данными размером до 4 ГБ в памяти.
Сохранение в SharePoint
Книги, изменяемые с помощью Power Pivot, можно сделать общими, как и любые другие файлы. Однако публикация книги в среде SharePoint с включенными службами Excel имеет некоторые преимущества. На сервере SharePoint службы Excel обрабатывают данные и готовят их к просмотру в окне браузера, где другие пользователи смогут продолжить анализ данных.
В SharePoint вы можете добавить Power Pivot для SharePoint, чтобы получить дополнительную поддержку для совместной работы и управления документами, включая галерею Power Pivot, панель мониторинга управления Power Pivot в Центре администрирования, запланированное обновление данных и возможность использования опубликованной книги в качестве внешнего источника данных в SharePoint.
Получение справочной информации
Все о Power Pivot можно узнать в справке По Power Pivot.
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.