Для чего нужны сводные pivot tables таблицы
Сводные таблицы Excel
Работу в Excel можно разбить на два этапа: вычисления и форматирование данных. Несмотря на немалое количество встроенных инструментов и формул, которые способствуют выполнению обеих задач, сводные таблицы зачастую предоставляют самый быстрый и эффективный способ расчета и форматирования данных.
Давайте рассмотрим простой сценарий, демонстрирующий вышесказанное. Вы предоставили начальнику некую информацию о доходах компании за месяц, и он, как и ожидалось, затребовал более подробный отчет. Он добавил в рабочий лист примечание, после чего отправил его вам по электронной почте. Как показано на рис. 1.1, он попросил добавить строку, содержащую сведения о предоставленных за месяц кредитах.
Рис. 1.1. Как и ожидалось, начальник изменил требования к отчету после получения первого его варианта
Чтобы выполнить это требование, вы запрашиваете конечные данные у своей системы, в которой сохранена вся необходимая информация. Как обычно, данные введены в таком формате, что понять в них хоть что-то весьма проблематично даже вам. Вместо данных за месяц система предоставила подробную финансовую информацию всего за один день, как показано на рис. 1.2.
Рис. 1.2. Устаревшая система возвращает данные за день, а не за месяц
А теперь вам предстоит подсчитать общую сумму кредитов за месяц и представить результаты в формате исходного отчета. Окончательный фрагмент данных должен выглядеть примерно так, как показано на рис. 1.3. Если бы вы создавали фрагмент данных вручную, вам потребовалось бы 18 раз щелкнуть кнопкой мыши и три раза нажать разные клавиши:
Рис. 1.3. Ваша цель состоит в получении итоговых сведений за месяц и транспонировании их в горизонтальный вид
Получение необходимого фрагмента данных с помощью сводной таблицы требует всего лишь 9 щелчков мышью:
Оба метода приводят к получению одинаковых фрагментов данных, которые можно вставить в итоговый отчет, как показано на рис. 1.4.
Рис. 1.4. Добавив сведения о кредитах в отчет, можно подсчитать чистую прибыль
Использование сводной таблицы для выполнения этой задачи уменьшает количество действий наполовину и снижает возможность появления ошибок вследствие «человеческого фактора». Кроме того, сводные таблицы позволяют легко и быстро формировать данные. В этом примере продемонстрировано, что сводные таблицы используются не только для расчетов и подведения итогов на основе исходного набора данных. Зачастую с помощью сводных таблиц можно выполнять задачи быстрее и эффективнее, чем при использовании стандартных функций и формул. Например, можно применять сводные таблицы для быстрого транспонирования данных из горизонтального представления в вертикальное. Можно также применять их для быстрого поиска и подсчета уникальных значений в наборе данных. Кроме того, сводные таблицы можно применять для подготовки данных для диаграмм.
Таким образом, сводные таблицы позволяют повысить эффективность работы и снизить вероятность появления ошибок при выполнении задач с использованием Excel. Сводные таблицы не являются панацеей, но знание базовых принципов их использования и функциональных возможностей поможет вывести анализ данных на совершенно новый уровень производительности.
Общие сведения о сводных таблицах
БД.xlsx (30,9 KiB, 2 068 скачиваний)
Несмотря на то, что первая возможность создания сводных таблица появилась еще в Excel 5.0(аж в 1993 году), даже сейчас лишь немногие из пользователей Excel используют сводные таблицы для решения задач. Кто-то считает, что сводные таблицы это нечто слишком сложное, кто-то про них даже не слышал. В данном разделе (Сводные таблицы) я постараюсь убедить читателей в том, что это не так. На самом деле ничего сложного в сводных таблицах нет. А вот польза при анализе информации просто неоценима.
Сводная таблица (Pivot Table) – инструмент Excel, используемый для создания уникального представления данных и последующего анализа. Сводная таблица может быть построена на основе правильно сформированной исходной таблицы данных:
СОЗДАНИЕ СВОДНОЙ ТАБЛИЦЫ
СВОДНАЯ ТАБЛИЦА СОСТОИТ ИЗ ЧЕТЫРЕХ ОБЛАСТЕЙ:
Область данных – основная область сводной 
таблицы, в которой производятся расчеты. Содержит основные итоговые данные по числовым полям. В область данных можно поместить одно и тоже поле, но с разными вычислениями (например одно Сумма по полю, другое Количество по полю).
Основные вычислительные функции области данных:
Сумма (Sum)
Количество (Count)
Среднее (Average)
Максимум (Max)
Минимум (Min)
Произведение (Product)
Область строк – состоит из заголовков в левой части сводной таблицы и отображает уникальные значения вставленного поля.
Область столбцов – состоит из заголовков столбцов сводной таблицы.
Область страниц и фильтров – необязательная область. Расположена в верхней части сводной таблицы и выполняет функции фильтрации по вставленным в неё полям.
Статья помогла? Поделись ссылкой с друзьями!
Преимущества сводной по Модели Данных
К сожалению, очень многие даже давно знакомые со сводными таблицами и успешно применяющие их в работе пользователи, порой не очень понимают смысл этих опций и никогда их не используют. И зря. Ведь создание сводной по Модели Данных даёт нам несколько очень важных преимуществ по сравнению с классической сводной таблицей Excel.
Однако, перед тем, как рассматривать эти «плюшки» вблизи, давайте сначала разберёмся с тем, что такое, собственно, эта Модель Данных?
Что такое Модель Данных
Важное замечание по ходу: книга Excel может содержать только одну Модель Данных.
Грузим таблицы в Модель Данных
Затем можно использовать любой из трех способов, на выбор:
Строим сводную по Модели Данных
Чтобы построить сводную Модели Данных можно использовать любой из трёх подходов:
Теперь, когда мы разобрались с тем, как загружать данные в Модель Данных и строить по ним сводную, давайте изучем те выгоды и преимущества, которые нам это даёт.
Преимущество 1. Связи между таблицами без помощи формул
Преимущество 2. Подсчёт количества уникальных значений
Обычная сводная таблица даёт нам возможность выбрать одну из нескольких встроенных функций расчёта: сумму, среднее, количество, минимум, максимум и т.д. В сводной по Модели Данных к этому стандартному списку добавляется весьма полезная функция подсчёта количества уникальных (неповторяющихся значений). С её помощью, например, можно легко посчитать количество уникальных наименований товаров (ассортимент), который мы продаём в каждом городе.
Преимущество 3. Свои формулы на языке DAX
Иногда в сводных таблицах приходится выполнять различные дополнительные вычисления. В обычных сводных это делается с помощью вычисляемых полей и объектов, а сводной по Модели Данных для этого используются меры на специальном языке DAX (DAX = Data Analysis Expressions).
В открывшемся окне задаём:
Язык DAX не всегда прост для понимания, т.к. оперирует не отдельными значениями, а целыми столбцами и таблицами, т.е. требует некоторой перестройки мышления после классических формул Excel. Однако же, оно того стоит, ибо мощь его возможностей при обработке больших объемов данных трудно переоценить.
Преимущество 4. Свои иерархии полей
Созданную иерархию можно переименовать и перетащить в неё мышью требуемые поля, чтобы потом в одно движение забрасывать их в сводную:
Преимущество 5. Свои наборы элементов
Продолжая идею предыдущего пункта, в сводной по Модели Данных можно создавать ещё и свои наборы элементов для каждого поля. Например, из всего списка городов можно легко сделать набор только из тех, которые входят в зону вашей ответственности. Или собрать в специальный набор только своих клиентов, свои товары и т.п.
В открывшемся окне можно выборочно удалить, добавить или поменять положение любых элементов и сохранить получившийся набор под новым именем:
Все созданные наборы будут отображаться в панели полей сводной таблицы в отдельной папке, откуда их можно свободно перетаскивать в области строк и столбцов любой новой сводной таблицы:
Преимущество 6. Выборочное скрытие таблиц и столбцов
Это хоть и небольшое, но весьма приятное в некоторых случаях преимущество. Щёлкнув правой кнопкой мыши по названию поля или по ярлычку таблицы в окне Power Pivot, можно выбрать команду Скрыть из набора клиентских средств (Hide from Client Tools) :
Скрытый столбец или таблица пропадут из панели со списком полей сводной таблицы. Очень удобно, если вам требуется скрыть от пользователя некоторые вспомогательные столбцы (например, расчетные или столбцы с ключевыми значениями для создания связей) или даже целые таблицы.
Преимущество 7. Продвинутый drill-down
Если в обычной сводной таблице сделать двойной щелчок левой кнопкой мыши по любой ячейке в области значений, то Excel выводит на отдельном листе копию фрагмента исходных данных, которые участвовали в расчёте этой ячейки. Это очень удобная штука, официально называющаяся Drill-down (на русском обычно говорят «провалиться»).
В сводной по Модели Данных этот удобный инструмент работает более тонко. Встав на любую интересующую нас ячейку с результатом, можно щёлкнуть по всплывающему рядом значку с лупой (он называется Экспресс-тенденции) и выбрать затем любое интересующее вас поле в любой связанной таблице:
После этого текущее значение (Модель = Explorer) уйдет в область фильтра, а сводная будет построена уже по офисам:
Само-собой, такую процедуру можно повторять многократно, последовательно углубляясь в ваши данные в интересующем вас направлении.
Преимущество 8. Преобразование сводной в функции кубов
Технически, это означает, что теперь мы имеем дело не со сводной, а с несколькими ячейками с формулами, т.е. спокойно можем делать с нашим отчетом любые преобразования недоступные в сводных, например, вставлять в середину отчета новые строки или столбцы, делать внутри сводной любые доп.вычисления, оформлять их любым желаемым образом и т.д.
При этом связь с исходными данными, само-собой, остается и в будущем эти формулы будут обновляться при изменении источников. Красота!
Сводные таблицы в 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. Более того, это можно множеством разных методов — достаточно лишь выбрать оптимальный для вашей СУБД.
Создание отчетов с помощью Сводных таблиц (Pivot Tables)
Представьте, что вам нужно с большой таблицы, которая содержит множество разнообразной информации сделать краткий аналитический отчет для принятия решений. Предположим, вы имеете данные, где отображается информация по кредитному портфелю Банка, где есть записанная подробная информация по каждому заемщику. Поэтому, для анализа кредитного портфеля, из базы данных в Excel выгружается примерно вот-такая таблица:
В ней каждая отдельная строка содержит полную информацию об одной кредитной сделке:
Естественно, если менеджеры по продажам знают свое дело и вкалывают всерьез, то каждый день этой таблице будет дописываться несколько десятков строк и до конца, например, года или хотя бы квартала размеры таблицы станут ужасными.
Однако еще больший ужас вызовет у Вас необходимость создания отчетов по этим данным. Сколько выдали кредитов в каждом месяце? Какова величина кредитного портфеля в разрезе направлений кредитования по количеству и сумме? Десять крупнейших заемщиков? и т.д.
Появится вот такое окно, в котором мы можем выбрать куда поместить нашу сводную таблицу и скорректировать диапазон данных.
Работа с макетом
То, что Вы увидите, нажав кнопку OK называется макет (layout) сводной таблицы:
И получаем следующий вид:
Не так уж это все и сложно, не так ли?