Сводная таблица

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

Хотя сводная таблица является общим термином, Microsoft владела товарным знаком этого термина в США с 1994 по 2020 год. ^[1]

История

В своей книге «Обработка данных сводной таблицы » ^[2] Билл Джелен и Майк Александер называют Пито Саласа «отцом сводных таблиц». Работая над концепцией новой программы, которая в конечном итоге станет Lotus Improv , Салас заметил, что электронные таблицы содержат шаблоны данных. Инструмент, который мог бы помочь пользователю распознать эти закономерности, поможет быстро построить расширенные модели данных. С помощью Improv пользователи могли определять и сохранять наборы категорий, а затем изменять представления, перетаскивая названия категорий мышью. Эта основная функциональность обеспечит модель сводных таблиц.

Lotus Development выпустила Improv в 1991 году на платформе NeXT . Через несколько месяцев после выпуска Improv компания Brio Technology опубликовала отдельную реализацию для Macintosh под названием DataPivot (технология была запатентована в 1999 году). ^[3] Borland приобрела технологию DataPivot в 1992 году и реализовала ее в своем собственном приложении для работы с электронными таблицами Quattro Pro .

В 1993 году появилась версия Improv для Microsoft Windows. В начале 1994 года Microsoft Excel 5. ^[4] вывел на рынок новую функцию под названием «Сводная таблица». Microsoft дополнительно улучшила эту функцию в более поздних версиях Excel:

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

В 2007 году корпорация Oracle произвела PIVOT и UNPIVOT операторы, доступные в базе данных Oracle 11g. ^[5]

Механика

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

	А	Б	С	Д	И	Ф	Г
1	Область	Пол	Стиль	Дата отправки	Единицы	Цена	Расходы
2	Восток	Мальчик	тройник	2005-01-31	12	11.04	10.42
3	Восток	Мальчик	Гольф	2005-01-31	12	13.00	12.60
4	Восток	Мальчик	Изысканный	2005-01-31	12	11.96	11.74
5	Восток	Девочка	тройник	2005-01-31	10	11.27	10.56
6	Восток	Девочка	Гольф	2005-01-31	10	12.12	11.95
7	Восток	Девочка	Изысканный	2005-01-31	10	13.74	13.33
8	Запад	Мальчик	тройник	2005-01-31	11	11.44	10.94
9	Запад	Мальчик	Гольф	2005-01-31	11	12.63	11.73
10	Запад	Мальчик	Изысканный	2005-01-31	11	12.06	11.51
11	Запад	Девочка	тройник	2005-01-31	15	13.42	13.29
12	Запад	Девочка	Гольф	2005-01-31	15	11.48	10.67
⋮	…	…	…	…	…	…	…

Хотя подобные таблицы могут содержать множество элементов данных, получить из них обобщенную информацию может быть сложно. Сводная таблица может помочь быстро обобщить данные и выделить нужную информацию. Использование сводной таблицы чрезвычайно широко и зависит от ситуации. Первый вопрос, который нужно задать: «Чего я ищу?» В приведенном здесь примере давайте спросим: «Сколько единиц мы продали в каждом регионе на каждую дату поставки? »:

Сумма единиц	Дата отправки ▼
Регион ▼	2005-01-31	2005-02-28	2005-03-31	2005-04-30	2005-05-31	2005-06-30
Восток	66	80	102	116	127	125
Север	96	117	138	151	154	156
Юг	123	141	157	178	191	202
Запад	78	97	117	136	150	157
(пустой)
общий итог	363	435	514	581	622	640

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

Выполнение

Используя приведенный выше пример, программа найдет все различные значения для региона . В данном случае это: Север , Юг , Восток , Запад . Кроме того, он найдет все различные значения для даты отправки . В зависимости от типа агрегации sum он суммирует факты, количества Unit и отображает их на многомерной диаграмме. В приведенном выше примере первый элемент данных равен 66. Это число было получено путем поиска всех записей, в которых регион был «Восток» , а дата отправки — 31 января 2005 г. , и добавления единиц измерения из этого набора записей ( т. е. ячеек от E2 до E7). ) вместе, чтобы получить окончательный результат.

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

Дата продажи	Продавец	Товар продан	Цвет товара	Продано единиц	Цена за единицу	Общая стоимость
2013-10-01	Джонс	Блокнот	Черный	8	25 000	200 000
2013-10-02	Принц	Ноутбук	Красный	4	35 000	140 000
2013-10-03	Джордж	Мышь	Красный	6	850	5100
2013-10-04	Ларри	Блокнот	Белый	10	27 000	270 000
2013-10-05	Джонс	Мышь	Черный	4	700	2800

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

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

Фильтры
Столбцы
Строки
Ценности

Некоторые варианты использования сводных таблиц связаны с анализом анкет с дополнительными ответами, но некоторые реализации сводных таблиц не допускают таких вариантов использования. Например реализация в LibreOffice Calc с 2012 года не умеет обрабатывать пустые ячейки. ^[6]^[7]

Фильтры

Фильтр отчета используется для применения фильтра ко всей таблице. Например, если в эту область перетащить поле «Цвет элемента», то над построенной таблицей будет вставлен фильтр отчета. Этот фильтр отчета будет иметь раскрывающиеся параметры (черный, красный и белый в приведенном выше примере). выбран вариант Если в этом раскрывающемся списке («Черный» в этом примере), то видимая таблица будет содержать только данные из тех строк, которые имеют «Цвет элемента = черный».

Столбцы

Метки столбцов используются для применения фильтра к одному или нескольким столбцам, которые должны отображаться в сводной таблице. Например, если в эту область перетащить поле «Продавец», то в построенной таблице будут значения из столбца «Продавец», т.е. количество столбцов будет равно числу «Продавец». Также будет добавлен один столбец «Итого». В приведенном выше примере эта инструкция создаст в таблице пять столбцов — по одному для каждого продавца и «Общий итог». Над данными будет фильтр — метки столбцов, из которого можно будет выбрать или отменить выбор конкретного продавца для сводной таблицы.

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

Строки

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

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

Ценности

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

Метки строк	Сумма проданных единиц
Джонс	12
Принц	4
Джордж	6
Ларри	10
общий итог	32

Поддержка приложений

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

Таблицы

Microsoft Excel поддерживает сводные таблицы, которые можно визуализировать с помощью сводных диаграмм. ^[8]
Апач POI ^[9]
LibreOffice Calc и Openoffice Calc поддерживают сводные таблицы. До версии 3.4 эта функция называлась «DataPilot». ^{[ нужна ссылка ]}
Calligra Sheets поддерживает сводные таблицы. ^[10]
Google Таблицы изначально поддерживают сводные таблицы. ^[11]
В программе Numbers от Apple Inc. появилась поддержка сводных таблиц в версии 11.2. ^[12]

Поддержка базы данных

PostgreSQL , объектно-реляционная система управления базами данных , позволяет создавать сводные таблицы с помощью модуля tablefunc . ^[13]
MariaDB , ответвление MySQL, позволяет использовать сводные таблицы с использованием механизма хранения CONNECT. ^[14]
Microsoft Access поддерживает сводные запросы под названием «перекрестный запрос». ^{[ нужна ссылка ]}
Microsoft SQL Server поддерживает поворот, начиная с SQL Server 2016, с ключевыми словами FROM...PIVOT. ^[15]
База данных Oracle поддерживает операцию PIVOT. ^{[ нужна ссылка ]}
Некоторые популярные базы данных, которые напрямую не поддерживают функции сводных данных, такие как SQLite , обычно могут имитировать функции сводных данных с помощью встроенных функций, динамического SQL или подзапросов. Проблема с поворотом в таких случаях обычно заключается в том, что количество выходных столбцов должно быть известно в момент начала выполнения запроса; для поворота это невозможно, поскольку количество столбцов зависит от самих данных. Следовательно, имена должны быть жестко запрограммированы , или же выполняемый запрос должен создаваться динамически (то есть перед каждым использованием) на основе данных. ^{[ нужна ссылка ]}

Веб-приложения

ZK , платформа Ajax, также позволяет встраивать сводные таблицы в веб-приложения. ^{[ нужна ссылка ]}

Языки программирования и библиотеки

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

Python В наборе инструментов анализа данных pandas есть функция pivot_table^[16] и xs Метод полезен для получения разделов сводных таблиц. ^{[ нужна ссылка ]}
В R есть метапакет Tidyverse , который содержит набор инструментов, обеспечивающих функциональность сводных таблиц. ^[17]^[18] а также пакет Pivottabler. ^[19]

Аналитическая обработка онлайн

Сводные таблицы Excel включают функцию прямого запроса к серверу онлайн-аналитической обработки (OLAP) для получения данных вместо получения данных из электронной таблицы Excel. В этой конфигурации сводная таблица является простым клиентом OLAP-сервера. Сводная таблица Excel позволяет подключаться не только к службе анализа Microsoft, но и к любому серверу XML для анализа (XMLA), совместимому со стандартом OLAP.

См. также

Агрегатная функция
Деловая отчетность
Сравнение офисных пакетов
Сравнение OLAP-серверов
Таблица непредвиденных обстоятельств — перекрестная таблица, в которой подсчитываются значения, а не итоговые суммы.
Детализация данных
Интеллектуальный анализ данных
Визуализация данных
Хранилище данных
Извлечь, преобразовать, загрузить
Сгиб (функция высшего порядка)
OLAP-куб
Реляционная алгебра
Широкие и узкие данные

Ссылки

^ «Серийный номер товарного знака США 74472929» . 27 декабря 1994 г. Проверено 23 марта 2022 г.
^ Джелен, Билл ; Александр, Михаил (2006). Обработка данных сводной таблицы . Индианаполис: Que. стр. 274 . ISBN 0-7897-3435-4 .
^ Гартунг, Дэниел Л.; Эдхольм, Йорген Х.; Эдхольм, Кей-Мартин; МакНалл, Кристен Н.; Лью, Карл М., патент № 5915257 , получено 16 февраля 2010 г.
^ Дарлингтон, Кейт (6 августа 2012 г.). VBA для Excel — это просто . Рутледж (опубликовано в 2012 г.). п. 19. ISBN 9781136349775 . Проверено 10 сентября 2014 г. [...] Excel 5, выпущенный в начале 1994 года, включал первую версию VBA.
^ Шах, Шаранам; Шах, Вайшали (2008). Oracle для профессионалов — охватывает Oracle 9i, 10g и 11g . Издательская серия Шроффа. Нави Мумбаи: Shroff Publishers (опубликовано в июле 2008 г.). п. 549. ИСБН 9788184045260 . Проверено 10 сентября 2014 г. Одной из наиболее полезных новых функций Oracle Database 11g с точки зрения SQL является введение операторов Pivot и Unpivot.
^ «LibreOffice Calc и сводная таблица с пустыми ячейками» . StackOverflow . 17.06.2021 . Проверено 17 июня 2021 г.
^ «Запрос функциональности для СВОБОДНОЙ ТАБЛИЦЫ» . Ошибки LibreOffice . 19 марта 2012 г. Проверено 17 июня 2021 г.
^ Далглиш, Дебра (2007). Начало работы со сводными таблицами в Excel 2007: от новичка до профессионала . Апресс. стр. 233–257. ISBN 9781430204336 . Проверено 18 сентября 2018 г.
^ «Руководство для занятых разработчиков по функциям HSSF и XSSF» . poi.apache.org . Проверено 9 декабря 2022 г.
^ «Сводные таблицы» .
^ «Создание и использование сводных таблиц» . Помощь редакторам документов . Гугл Инк . Проверено 6 августа 2020 г.
^ «Обновление iWork вносит серьезные изменения в приложения для Mac, iPhone и iPad» . Макмир . Проверено 28 сентября 2021 г.
^ «PostgreSQL: Документация: 9.2: tablefunc» . postgresql.org . 9 ноября 2017 г.
^ «Типы таблиц CONNECT — Тип сводной таблицы» . mariadb.com .
^ «Предложение FROM плюс JOIN, APPLY, PIVOT (T-SQL) — SQL Server» .
^ "pandas.pivot_table" . Проверено 21 ноября 2023 г.
^ dplyr и сводные таблицы .
^ Поворот .
^ «поворотный стол» .

Дальнейшее чтение

Полное руководство по сводным таблицам: визуальный подход ( ISBN 1-59059-432-0 ) ( подробный обзор на slashdot.org )
Сводные таблицы и сводные диаграммы Excel 2007: визуальный план ( ISBN 978-0-470-13231-9 )
Обработка данных сводной таблицы (бизнес-решения) ( ISBN 0-7897-3435-4 )
Начало работы со сводными таблицами в Excel 2007 ( ISBN 1-59059-890-3 )

[1] «Серийный номер товарного знака США 74472929» . 27 декабря 1994 г. Проверено 23 марта 2022 г.

[2] Джелен, Билл ; Александр, Михаил (2006). Обработка данных сводной таблицы . Индианаполис: Que. стр. 274 . ISBN 0-7897-3435-4 .

[3] Гартунг, Дэниел Л.; Эдхольм, Йорген Х.; Эдхольм, Кей-Мартин; МакНалл, Кристен Н.; Лью, Карл М., патент № 5915257 , получено 16 февраля 2010 г.

[4] Дарлингтон, Кейт (6 августа 2012 г.). VBA для Excel — это просто . Рутледж (опубликовано в 2012 г.). п. 19. ISBN 9781136349775 . Проверено 10 сентября 2014 г. [...] Excel 5, выпущенный в начале 1994 года, включал первую версию VBA.

[5] Шах, Шаранам; Шах, Вайшали (2008). Oracle для профессионалов — охватывает Oracle 9i, 10g и 11g . Издательская серия Шроффа. Нави Мумбаи: Shroff Publishers (опубликовано в июле 2008 г.). п. 549. ИСБН 9788184045260 . Проверено 10 сентября 2014 г. Одной из наиболее полезных новых функций Oracle Database 11g с точки зрения SQL является введение операторов Pivot и Unpivot.

[6] «LibreOffice Calc и сводная таблица с пустыми ячейками» . StackOverflow . 17.06.2021 . Проверено 17 июня 2021 г.

[7] «Запрос функциональности для СВОБОДНОЙ ТАБЛИЦЫ» . Ошибки LibreOffice . 19 марта 2012 г. Проверено 17 июня 2021 г.

[Dalgleish2007-8] Далглиш, Дебра (2007). Начало работы со сводными таблицами в Excel 2007: от новичка до профессионала . Апресс. стр. 233–257. ISBN 9781430204336 . Проверено 18 сентября 2018 г.

[9] «Руководство для занятых разработчиков по функциям HSSF и XSSF» . poi.apache.org . Проверено 9 декабря 2022 г.

[10] «Сводные таблицы» .

[11] «Создание и использование сводных таблиц» . Помощь редакторам документов . Гугл Инк . Проверено 6 августа 2020 г.

[12] «Обновление iWork вносит серьезные изменения в приложения для Mac, iPhone и iPad» . Макмир . Проверено 28 сентября 2021 г.

[13] «PostgreSQL: Документация: 9.2: tablefunc» . postgresql.org . 9 ноября 2017 г.

[14] «Типы таблиц CONNECT — Тип сводной таблицы» . mariadb.com .

[15] «Предложение FROM плюс JOIN, APPLY, PIVOT (T-SQL) — SQL Server» .

[16] "pandas.pivot_table" . Проверено 21 ноября 2023 г.

[17] dplyr и сводные таблицы .

[18] Поворот .

[19] «поворотный стол» .

[1]

[2]

[3]

[4]

[5]

[6]

[7]

[8]

[9]

[10]

[11]

[12]

[13]

[14]

[15]

[16]

[17]

[18]

[19]