Сводная таблица представляет собой таблицу сгруппированных значений, объединяющие отдельные элементы более обширной таблицы (например, из базы данных , таблица или программ бизнес - анализ ) в пределах одного или несколько дискретных категорий. Эта сводка может включать суммы, средние или другие статистические данные, которые сводная таблица группирует вместе с использованием выбранной функции агрегирования, применяемой к сгруппированным значениям.
Сводные таблицы - это метод обработки данных . Они упорядочивают и перестраивают (или «сводят») статистику, чтобы привлечь внимание к полезной информации. Это приводит к быстрому нахождению цифр и фактов, что делает их неотъемлемой частью анализа данных . В конечном итоге это помогает компаниям или частным лицам принимать обоснованные решения. [1]
Хотя сводная таблица является общим термином, Microsoft зарегистрировала торговую марку PivotTable в США в 1994 году (отменена в 2020 году). [2]
История
В своей книге сводной таблицы данных хруст , [3] Билл Елен и Майк Александр относятся к Пито Салас как «отец сводных таблиц». Работая над концепцией новой программы, которая в конечном итоге станет Lotus Improv , Салас заметил, что электронные таблицы содержат шаблоны данных. Инструмент, который может помочь пользователю распознать эти закономерности, поможет быстро построить расширенные модели данных. С Improv пользователи могли определять и сохранять наборы категорий, а затем изменять представления, перетаскивая имена категорий с помощью мыши. Эта основная функциональность предоставит модель для сводных таблиц.
Lotus Development выпустила Improv в 1991 году на платформе NeXT . Через несколько месяцев после выпуска Improv компания Brio Technology опубликовала автономную реализацию Macintosh под названием DataPivot (технология, в конечном итоге, запатентованная в 1999 году). [4] Borland приобрела технологию DataPivot в 1992 году и реализовала ее в своем собственном приложении для работы с электронными таблицами Quattro Pro .
В 1993 году появилась версия Improv для Microsoft Windows. В начале 1994 года в Microsoft Excel 5 [5] на рынок была выведена новая функция, названная «Сводная таблица». Microsoft дополнительно улучшила эту функцию в более поздних версиях Excel:
- Excel 97 включал новый улучшенный мастер сводных таблиц, возможность создавать вычисляемые поля и новые объекты сводного кэша, которые позволяют разработчикам писать макросы Visual Basic для приложений для создания и изменения сводных таблиц.
- В Excel 2000 появились «сводные диаграммы» для графического представления данных сводных таблиц.
В 2007 году корпорация Oracle сделала PIVOT
и UNPIVOT
операторы , доступные в Oracle Database 11g. [6]
Механика
Для типичного ввода и хранения данных данные обычно отображаются в плоских таблицах, что означает, что они состоят только из столбцов и строк, как в следующей части образца электронной таблицы, показывающей данные о типах рубашек:
Хотя такие таблицы могут содержать много элементов данных, получить из них обобщенную информацию может быть сложно. Сводная таблица может помочь быстро обобщить данные и выделить нужную информацию. Сводная таблица используется очень широко и зависит от ситуации. Первый вопрос, который нужно задать: «Чего я ищу?» В приведенном здесь примере давайте спросим: «Сколько единиц мы продали в каждом регионе на каждую дату отгрузки? »:
Сводная таблица обычно состоит из полей строк , столбцов и данных (или фактов ). В этом случае столбец - это дата отправки , строка - это регион, а данные, которые мы хотели бы видеть, - это (сумма) единиц . Эти поля допускают несколько видов агрегирования , включая: сумму, среднее значение, стандартное отклонение , количество и т. Д. В этом случае здесь отображается общее количество отгруженных единиц с использованием агрегирования суммы .
Выполнение
Используя приведенный выше пример, программа найдет все различные значения для региона . В данном случае это: Север , Юг , Восток , Запад . Кроме того, он найдет все различные значения для даты отгрузки . Основываясь на типе агрегирования, сумме , он суммирует факт, количество единиц и отображает их на многомерной диаграмме. В приведенном выше примере первым элементом данных является 66. Это число было получено путем поиска всех записей, в которых оба региона были восточными, а дата отгрузки - 31.01.2005 , и добавления единиц этого набора записей ( т. Е. Ячеек с E2 по E7. ) вместе, чтобы получить окончательный результат.
Сводные таблицы не создаются автоматически. Например, в Microsoft Excel нужно сначала выбрать все данные в исходной таблице, а затем перейти на вкладку «Вставка» и выбрать «Сводная таблица» (или «Сводная диаграмма»). Затем пользователь может либо вставить сводную таблицу в существующий лист, либо создать новый лист для размещения сводной таблицы. Пользователю предоставляется список полей сводной таблицы, в котором перечислены все заголовки столбцов, присутствующие в данных. Например, если таблица представляет данные о продажах компании, она может включать дату продажи, продавца, проданный товар, цвет товара, проданные единицы, цену за единицу и общую цену. Это делает данные более доступными.
Дата продажи | Продавец | Товар продан | Цвет предмета | Проданных единиц | Цена за единицу | Итоговая цена |
---|---|---|---|---|---|---|
01.10.13 | Джонс | Ноутбук | Чернить | 8 | 25000 | 200000 |
02.10.13 | Принц | Ноутбук | красный | 4 | 35000 | 140000 |
03.10.13 | Георгий | Мышь | красный | 6 | 850 | 5100 |
04.10.13 | Ларри | Ноутбук | белый | 10 | 27000 | 270000 |
05.10.13 | Джонс | Мышь | Чернить | 4 | 700 | 2800 |
Поля, которые будут созданы, будут видны в правой части рабочего листа. По умолчанию макет сводной таблицы отображается под этим списком.
Поля сводной таблицы являются строительными блоками сводных таблиц. [7] Каждое поле из списка можно перетащить на этот макет, который имеет четыре варианта:
- Фильтры
- Столбцы
- Рядов
- Значения
Некоторые виды использования сводных таблиц связаны с анализом вопросников с дополнительными ответами, но некоторые реализации сводных таблиц не допускают таких вариантов использования. Например, реализация в LibreOffice Calc с 2012 года не может обрабатывать пустые ячейки. [8] [9]
Фильтры
Фильтр отчета используется для применения фильтра ко всей таблице. Например, если в эту область перетащить поле «Цвет элемента», то в созданную таблицу будет вставлен фильтр отчета над таблицей. Этот фильтр отчета будет иметь раскрывающиеся варианты (черный, красный и белый в приведенном выше примере). При выборе варианта из этого раскрывающегося списка (в данном примере «Черный») видимая таблица будет содержать только данные из тех строк, которые имеют «Цвет элемента = Черный».
Столбцы
Метки столбцов используются для применения фильтра к одному или нескольким столбцам, которые должны отображаться в сводной таблице. Например, если в эту область перетащить поле «Продавец», то в созданной таблице будут значения из столбца «Продавец», т.е. количество столбцов будет равно количеству столбцов «Продавец». Также будет добавлен один столбец Итого. В приведенном выше примере эта инструкция создаст в таблице пять столбцов - по одному для каждого продавца и общий итог. Над данными - метками столбцов - будет фильтр, в котором можно выбрать или отменить выбор конкретного продавца для сводной таблицы.
В этой таблице не будет числовых значений, поскольку числовое поле не выбрано, но когда оно выбрано, значения будут автоматически обновляться в столбце «Общая сумма».
Рядов
Метки строк используются для применения фильтра к одной или нескольким строкам, которые должны отображаться в сводной таблице. Например, если перетащить поле «Продавец» в эту область, тогда другая построенная выходная таблица будет иметь значения из столбца «Продавец», то есть в одной будет количество строк, равное количеству «Продавца». Также будет добавлена одна строка «Общий итог». В приведенном выше примере эта инструкция создаст пять строк в таблице - по одной для каждого продавца и общий итог. Над данными - метками строк - будет фильтр, в котором можно выбрать или отменить выбор конкретного продавца для сводной таблицы.
В этой таблице не будет никаких числовых значений, поскольку числовое поле не выбрано, но когда оно выбрано, значения автоматически обновляются в строке «Общая сумма».
Значения
Обычно это поле с числовыми значениями, которые можно использовать для различных типов вычислений. Однако использование текстовых значений также не было бы неправильным; вместо Sum будет отображаться счетчик. Итак, в приведенном выше примере, если поле «Продано единиц» перетащить в эту область вместе с меткой строки «Продавец», тогда инструкция добавит новый столбец «Сумма проданных единиц», в котором будут значения, соответствующие каждый продавец.
Заголовки строк | Сумма проданных единиц |
---|---|
Джонс | 12 |
Принц | 4 |
Георгий | 6 |
Ларри | 10 |
общий итог | 32 |
Поддержка приложения
Сводные таблицы или функции сводных таблиц являются неотъемлемой частью многих приложений для работы с электронными таблицами и некоторого программного обеспечения для баз данных , а также присутствуют в других инструментах визуализации данных и пакетах бизнес-аналитики .
Таблицы
- Microsoft Excel поддерживает сводные таблицы, которые можно визуализировать с помощью сводных диаграмм. [10] [11]
- LibreOffice Calc и Openoffice Calc поддерживают сводные таблицы. До версии 3.4 эта функция называлась «DataPilot».
- Google Таблицы изначально поддерживают сводные таблицы. [12]
Поддержка базы данных
- PostgreSQL , система управления объектно-реляционной базой данных , позволяет создавать сводные таблицы с помощью модуля tablefunc . [13]
- MariaDB , ветвь MySQL, позволяет создавать сводные таблицы с помощью механизма хранения CONNECT. [14]
- Microsoft Access поддерживает сводные запросы под названием «запрос кросс-таблицы».
- Microsoft SQL Server поддерживает сводку с SQL Server 2016 с ключевыми словами FROM ... PIVOT [15]
- Oracle Database поддерживает операцию PIVOT.
- Некоторые популярные базы данных, которые напрямую не поддерживают функциональность сводной таблицы, например SQLite , обычно могут моделировать функциональность сводной таблицы с помощью встроенных функций, динамического SQL или подзапросов. Проблема с поворотом в таких случаях обычно заключается в том, что количество выходных столбцов должно быть известно в момент начала выполнения запроса; для поворота это невозможно, поскольку количество столбцов зависит от самих данных. Следовательно, имена должны быть жестко закодированы, или запрос, который должен быть выполнен, должен сам создаваться динамически (то есть перед каждым использованием) на основе данных.
Веб-приложения
- ZK , структура Ajax, также позволяет встраивать сводные таблицы в веб-приложения.
Языки программирования и библиотеки
- Языки программирования и библиотеки, подходящие для работы с табличными данными, содержат функции, которые позволяют создавать сводные таблицы и управлять ими. Набор инструментов для анализа данных Python pandas имеет функцию pivot_table и метод xs, полезный для получения разделов сводных таблиц. [16] В то время как R имеет метапакет Tidyverse, который содержит набор инструментов, обеспечивающих функциональность сводной таблицы, [17] [18], а также пакет pivottabler. [19]
Онлайн-аналитическая обработка
Сводные таблицы Excel включают функцию прямого запроса к серверу оперативной аналитической обработки (OLAP) для извлечения данных вместо получения данных из электронной таблицы Excel. В этой конфигурации сводная таблица является простым клиентом сервера OLAP. Сводная таблица Excel не только позволяет подключаться к Microsoft Analysis Service, но и к любому серверу, совместимому со стандартом XML for Analysis (XMLA) OLAP.
Смотрите также
- Агрегатная функция
- Деловая отчетность
- Сравнение офисных комплексов
- Сравнение серверов OLAP
- Таблица непредвиденных обстоятельств , перекрестная таблица , которая подсчитывает подсчеты, а не итоги
- Бурение данных
- Сбор данных
- Визуализация данных
- Хранилище данных
- Извлечь, преобразовать, загрузить
- Сложить (функция высшего порядка)
- Куб OLAP
- Реляционная алгебра
- Широкие и узкие данные
Рекомендации
- ^ «Все о сводных таблицах Microsoft Excel» . Excel Pivot . Проверено 2021 января .
- ^ "Серийный номер товарного знака США 74472929" . 1994-12-27 . Проверено 17 февраля 2013 .
- ^ Джелен, Билл ; Александр, Майкл (2006). Обработка данных сводной таблицы . Индианаполис: Que. С. 274 . ISBN 0-7897-3435-4.
- ^ Gartung, Daniel L .; Edholm, Yorgen H .; Эдхольм, Кей-Мартин; McNall, Kristen N .; Лью, Карл М., Патент № 5915257 , извлекаются 2010-02-16
- ^ Дарлингтон, Кит (2012-08-06). VBA для Excel стало проще . Рутледж (опубликовано в 2012 г.). п. 19. ISBN 9781136349775. Проверено 10 сентября 2014 .
[...] Excel 5, выпущенный в начале 1994 года, включал первую версию VBA.
- ^ Шах, Шаранам; Шах, Вайшали (2008). Oracle для профессионалов - охватывает Oracle 9i, 10g и 11g . Издательская серия Шроффа. Navi Mumbai: Shroff Publishers (опубликовано в июле 2008 г.). п. 549. ISBN. 9788184045260. Проверено 10 сентября 2014 .
Одной из наиболее полезных новых функций Oracle Database 11g с точки зрения SQL является введение операторов Pivot и Unpivot.
- ^ «Что такое поля сводной таблицы и как их изменить» . Excel Pivot . Проверено 2 декабря 2020 .
- ^ «LibreOffice Calc и сводная таблица с пустыми ячейками» . StackOverflow . 2021-06-17 . Проверено 17 июня 2021 .
- ^ «Запрос функциональности Сводной таблицы» . Ошибки LibreOffice . 2012-03-19 . Проверено 17 июня 2021 .
- ^ Далглиш, Дебра (2007). Начало сводных таблиц в Excel 2007: от новичка к профессионалу . Апресс. С. 233–257. ISBN 9781430204336. Проверено 18 сентября 2018 года .
- ^ «Как сделать сводную диаграмму» . Excel Pivot . Проверено 23 апреля 2021 .
- ^ «Создавайте и используйте сводные таблицы» . Справка редакторов Документов . Google Inc . Проверено 6 августа 2020 .
- ^ «PostgreSQL: Документация: 9.2: tablefunc» . postgresql.org .
- ^ «Типы таблиц CONNECT - тип сводной таблицы» . mariadb.com .
- ^ https://docs.microsoft.com/en-us/sql/t-sql/queries/from-transact-sql?view=sql-server-2017
- ^ "pandas.pivot_table" .
- ^ «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 )