Что такое pivot table

Как сделать сводную таблицу в Excel – пошаговая инструкция для чайников

В этом руководстве вы узнаете, что такое сводная таблица, и найдете подробную инструкцию, как по шагам создавать и использовать её в Excel.

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

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

Что такое сводная таблица?

Это инструмент для изучения и обобщения больших объемов данных, анализа связанных итогов и представления отчетов. Они помогут вам:

Например, у вас множество записей в электронной таблице с цифрами продаж шоколада:

И каждый день сюда добавляются все новые сведения. Одним из возможных способов суммирования этого длинного списка чисел по одному или нескольким условиям является использование формул, как было продемонстрировано в руководствах по функциям СУММЕСЛИ и СУММЕСЛИМН.

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

Вот посмотрите сами.

Этот скриншот демонстрирует лишь несколько из множества возможных вариантов анализа продаж. И далее мы рассмотрим примеры построения сводных таблиц в Excel 2016, 2013, 2010 и 2007.

Как создать сводную таблицу.

Многие думают, что создание отчетов при помощи сводных таблиц для «чайников» является сложным и трудоемким процессом. Но это не так! Microsoft много лет совершенствовала эту технологию, и в современных версиях Эксель они очень удобны и невероятно быстры.

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

1. Организуйте свои исходные данные

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

Полезные советы:

2. Создаем и размещаем макет

Нажатие ОК создает пустой макет без цифр в целевом местоположении, который будет выглядеть примерно так:

Полезные советы:

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

Изменения, которые вы вносите в этих разделах, немедленно применяются в вашей таблице.

3. Как добавить поле

Чтобы иметь возможность добавить поле в нужную область, установите флажок рядом с его именем.

По умолчанию Microsoft Excel добавляет поля в раздел «Макет» следующим образом:

4. Как удалить поле из сводной таблицы?

Чтобы удалить любое поле, вы можете выполнить следующее:

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

5. Как упорядочить поля?

Вы можете изменить расположение показателей тремя способами:

Все внесенные вами изменения применяются немедленно.

Ну а ежели спохватились, что сделали что-то не так, не забывайте, что есть «волшебная» комбинация клавиш CTRL+Z, которая отменяет сделанные вами изменения (если вы не сохранили их, нажав соответствующую клавишу).

6. Выберите функцию для значений (необязательно)

По умолчанию Microsoft Excel использует функцию «Сумма» для числовых показателей, которые вы помещаете в область «Значения». Когда вы помещаете нечисловые (текст, дата или логическое значение) или пустые значения в эту область, к ним применяется функция «Количество».

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

Здесь же вы можете изменить имя его на более приятное и понятное для вас. Ведь оно отображается в таблице, и поэтому должно выглядеть соответственно.

7. Используем различные вычисления в полях значения (необязательно)

Подсказка. Функция «Дополнительные вычисления» может оказаться особенно полезной, когда вы добавляете одно и то же поле более одного раза и показываете, как в нашем примере, общий объем продаж и объем продаж в процентах от общего количества одновременно. Согласитесь, обычными формулами делать такую таблицу придется долго. А тут – пара минут работы!

Итак, процесс создания завершен. Теперь пришло время немного поэкспериментировать, чтобы выбрать макет, наиболее подходящий для вашего набора данных.

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

Чтобы изменить способ отображения вашей рабочей области, нажмите кнопку «Инструменты» и выберите предпочитаемый макет.

Вы также можете изменить размер панели по горизонтали, перетаскивая разделитель, который отделяет панель от листа.

Закрытие и открытие панели редактирования.

Закрыть список полей в сводной таблице так же просто, как нажать кнопку «Закрыть» (X) в верхнем правом углу панели. А вот как заставить его появиться снова – уже не так очевидно 🙂

Также можно нажать кнопку «Список полей» на ленте, которая находится на вкладке меню «Анализ».

Воспользуйтесь рекомендациями программы.

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

Давайте улучшим результат.

Теперь, когда вы знакомы с основами, вы можете перейти к вкладкам «Анализ» и «Конструктор» инструментов в Excel 2016 и 2013 ( вкладки « Параметры» и « Конструктор» в 2010 и 2007). Они появляются, как только вы щелкаете в любом месте таблицы.

Вы также можете получить доступ к параметрам и функциям, доступным для определенного элемента, щелкнув его правой кнопкой мыши (об этом мы уже говорили при создании).

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

Чтобы улучшить дизайн, перейдите на вкладку «Конструктор», где вы найдете множество предопределенных стилей. Чтобы получить свой собственный стиль, нажмите кнопку «Создать стиль….» внизу галереи «Стили сводной таблицы».

На снимке экрана ниже показан новый дизайн и макет.

Я изменил цветовой макет, а также постарался, чтобы таблица была более компактной. Для этого поменяем параметры представления товара. Какие параметры я использовал – вы видите на скриншоте.

Думаю, стало даже лучше. 😊

Как избавиться от заголовков «Метки строк» ​​и «Метки столбцов».

При создании сводной таблицы, Excel применяет Сжатую форму по умолчанию. Этот макет отображает «Метки строк» и «Метки столбцов» в качестве заголовков. Согласитесь, это не очень информативно, особенно для новичков.

И вот что мы получим в результате.

Показаны реальные имена, как вы видите на рисунке справа, что имеет гораздо больше смысла.

Как обновить сводную таблицу.

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

Как обновить вручную.

Кроме того, вы можете по щелчку правой кнопки мыши выбрать пункт Обновить из появившегося контекстного меню.

Примечание. Если внешний вид вашей сводной таблицы сильно изменяется после обновления, проверьте параметры «Автоматически изменять ширину столбцов при обновлении» и « Сохранить форматирование ячейки при обновлении». Чтобы сделать это, откройте «Параметры сводной таблицы», как это показано на рисунке, и вы найдете там эти флажки.

Автоматическое обновление сводной таблицы при открытии файла.

Как переместить на новое место?

Как удалить сводную таблицу?

Если вам больше не нужен определенный сводный отчет, вы можете удалить его несколькими способами.

Примечание. Если у вас есть какая-либо диаграмма, построенная на основе свода, то описанная выше процедура удаления превратит ее в стандартную диаграмму, которую больше нельзя будет изменять или обновлять.

Надеемся, что этот самоучитель станет для вас хорошей отправной точкой. Далее нас ждут еще несколько рекомендаций, как работать со сводными таблицами. И спасибо за чтение!

Источник

Общие сведения о сводных таблицах

БД.xlsx (30,9 KiB, 2 067 скачиваний)

Несмотря на то, что первая возможность создания сводных таблица появилась еще в Excel 5.0(аж в 1993 году), даже сейчас лишь немногие из пользователей Excel используют сводные таблицы для решения задач. Кто-то считает, что сводные таблицы это нечто слишком сложное, кто-то про них даже не слышал. В данном разделе (Сводные таблицы) я постараюсь убедить читателей в том, что это не так. На самом деле ничего сложного в сводных таблицах нет. А вот польза при анализе информации просто неоценима.

Сводная таблица (Pivot Table) – инструмент Excel, используемый для создания уникального представления данных и последующего анализа. Сводная таблица может быть построена на основе правильно сформированной исходной таблицы данных:

СОЗДАНИЕ СВОДНОЙ ТАБЛИЦЫ

СВОДНАЯ ТАБЛИЦА СОСТОИТ ИЗ ЧЕТЫРЕХ ОБЛАСТЕЙ:
Область данных – основная область сводной таблицы, в которой производятся расчеты. Содержит основные итоговые данные по числовым полям. В область данных можно поместить одно и тоже поле, но с разными вычислениями (например одно Сумма по полю, другое Количество по полю).
Основные вычислительные функции области данных:

 Сумма (Sum)
 Количество (Count)
 Среднее (Average)
 Максимум (Max)
 Минимум (Min)
 Произведение (Product)

Область строк – состоит из заголовков в левой части сводной таблицы и отображает уникальные значения вставленного поля.
Область столбцов – состоит из заголовков столбцов сводной таблицы.
Область страниц и фильтров – необязательная область. Расположена в верхней части сводной таблицы и выполняет функции фильтрации по вставленным в неё полям.

Статья помогла? Поделись ссылкой с друзьями!

Источник

Сводные таблицы в 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. Более того, это можно множеством разных методов — достаточно лишь выбрать оптимальный для вашей СУБД.

Источник

Создание первой сводной таблицы

Исходные данные

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

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

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

Рекомендуемые сводные таблицы

Пользователи Excel 2013 могут выбрать сводную таблицу из макета на основе рекомендаций, которые предлагает Excel. Вот как это делается:

Выберите любую ячейку исходной таблицы.

Перейдите на вкладку ленты Вставка [Insert] и найдите группу Таблицы [Table].

Выберите команду Рекомендуемые сводные таблицы [Recommended PivotTables].

Выберите подходящую таблицу в списке слева. После выборы в окне справа вы увидите макет будущей таблицы.

Самостоятельное создание сводной таблицы с помощью конструктора

Для тех, у кого нет Excel 2013, или же если нужной сводной таблицы нет в предложенных, можно создать ее с нуля с помощью конструктора. Для этого:

Выберите любую ячейку исходной таблицы.

Перейдите на вкладку ленты Вставка [Insert] и найдите группу Таблицы [Table].

Выберите команду Сводная таблица [PivotTable].

В диалоговом окне Создание сводной таблицы [Create PivotTable] убедитесь в правильности диапазона данных, на основе которого будет строиться отчет. Если диапазон некорректный, его нужно поменять в поле Таблица и диапазон [Table/Range].

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

Так же укажите, где необходимо разместить отчет сводной таблицы: на новом листе или на уже существующий.

Советую размещать каждый отчет сводной таблицы на отдельном листе. Это поможет избежать ошибок в случае если будут добавлять данные.

После формирования сводной таблицы в выбранном месте появиться область с ее названием. По умолчанию отчет называется СводнаяТаблица1 [PivotTable 1]. Для начала работы необходимо щелкнуть левой клавшей мыши по данной области. В результате в правой части листа откроется макет сводной таблицы.

Макет сводной таблицы состоит из списка полей сводной таблицы, в котором перечислены все заголовки исходной таблицы и четырех областей: ФИЛЬТРЫ, КОЛОННЫ, СТРОКИ и ЗНАЧЕНИЯ.

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

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

Данную операцию можно сделать еще 2 способами:

После добавления поля вы увидите список всех групп, которые есть в исходной таблице:

Теперь осталось добавить сумму по полю Рыночная стоимость. Для этого перенесем поле Рыночная стоимость в область значений. Желаемая таблица получена.

Источник