Как читать кросс-таблицу: секреты анализа данных

Как визуализировать кросс таблицу?

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

Вот шаги, которые помогут вам визуализировать кросс таблицу:

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

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

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

Примечание: Для создания и визуализации кросс таблиц рекомендуется использовать специализированные программы или инструменты, такие как Microsoft Excel, Google Sheets, Tableau или Power BI. Эти инструменты предоставляют более широкий набор функций и мощные возможности для анализа и визуализации данных.

Что такое сводная таблица и для чего она нужна в Microsoft Excel?

Что ж, динамические таблицы, также известные как Сводные таблицы на английском языке это очень практичный инструмент Excel. Его цель — позволить пользователю сравнивать, упорядочивать, группировать и фильтровать информацию в соответствии со своими потребностями. Точно так же вы также можете определить поля как строк, столбцов, так и табличных значений. Кроме того, у вас есть возможность вносить изменения в любое время. Хотя они обычно кажутся сложными, на самом деле они весьма практичны в использовании.

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

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

Таким образом, сводные таблицы предназначены для следующих задач:

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

Реализация сводной таблицы вручную

Чтобы изучить эти данные, возможно, потребуется сгруппировать пассажиров по таким параметрам, как пол, выжил или нет, или на основании какой-либо комбинации параметров. Если вы прочитали предыдущий раздел, у вас может появиться искушение применить к этим данным операцию GroupBy. Например, давайте вычислим процент выживших для каждого пола:

titanic.groupby('sex')].mean()

	survived
sex
female	0.742038
male	0.188908

Сразу же можно сделать вывод о том, что из каждых четырех женщин, находившихся на борту, выжили три, в то время как из каждых пяти мужчин выжил только один!

Это интересная информация, но мы можем пойти дальше и выяснить взаимосвязь между показателем выживаемости и двумя другими параметрами, такими как пол и, например, класс. Используя терминологию GroupBy, мы могли бы сформулировать последовательность наших действий следующим образом: группируем по (group by) классу и полу, отбираем (select) выживших, применяем (apply) агрегацию по среднему, объединяем (combine) результирующие группы и преобразуем (unstack) иерархический индекс, чтобы раскрыть скрытую многомерность. Выразим это в коде:

titanic.groupby().aggregate('mean').unstack()

class	First	Second	Third
sex
female	0.968085	0.921053	0.500000
male	0.368852	0.157407	0.135447

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

Сводные таблицы в Google Spreadsheets (Google таблицы)

Основное преимущество Google Spreadsheets — доступ к ним есть всегда и везде при условии наличия подключения к интернету. Именно это делает данный продукт наиболее удобным при совместной работе нескольких пользователей.

Чтобы загрузить базу данных в таблицу, в меню «Файл» выберите пункт «Импорт».

В диалоговом окне «Импорт файла» устанавливаем переключатель «Действие после импорта» в положение «Заменить текущий лист», а переключатель «Разделитель» устанавливаем в положение «Другое». В качестве разделителя вводим точку с запятой.

После нажатия кнопки «Импортировать» данные будут загружены в вашу таблицу на «Лист1».

Теперь можно приступить непосредственно к созданию сводной таблицы. Для этого надо выделить весь загруженный массив данных. Наиболее быстрым способом в этом случае будет перейти в ячейку A1 и последовательно использовать сочетание клавиш Ctrl + Shift + Стрелка вправо, затем Ctrl + Shift + Стрелка вниз. Далее переходим в меню «Данные» и жмем на пункт «Сводная таблица».

Далее в Google таблице, так же как и в Microsoft Excel, будет создан новый лист с названием «Сводная таблица 1», областью сводной таблицы и редактором отчетов.

Редактор отчет так же состоит из четырех областей: «Строки», «Столбцы», «Значения», «Фильтры».

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

В область «Строки» добавляем поле Date.
В область «Столбцы» добавляем поле Device Category.
В область «Значения» добавляем поле Sessions.
В область «Фильтры» добавляем поле User type.

Сводная таблица приобрела знакомый из описания Microsoft Excel вид:

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

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

Для этого в области фильтра редактора отчетов в выпадающем списке «Показать» отмечаем галочкой элемент New Visitor.

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

Теперь в отчете сводной таблицы представлены два уровня детализации, по неделям и датам.

Для подсчета показателя отказов с помощью расчетного поля в области значений редактора отчетов нажмите «Добавить поле» и выберите пункт «Рассчитываемое поле».

Далее необходимо ввести название расчетного поля и формулу. В нашем случае названием поля будет «Показатель отказов», а формулой для расчета «=bounces / sessions».

Теперь сводная таблица имеет следующий вид:

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

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

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

Сводные таблицы в LibreOffice и OpenOffice

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

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

Для создания сводной таблицы в меню «Файл» LibreOffice выберите пункт «Открыть», после чего укажите скачанный ранее файл pivotTableDB.csv.

В диалоговом окне «Импорт текста» установите переключатель «Параметры разделителя» в положение «Разделитель», и в качестве разделителя установите «Точка с запятой».

После нажатия ОК необходимая таблица данных будет загружена в документ. Теперь, когда у вас есть база данных, необходимо выделить ее нажатием Ctrl+A, и в в группе «Сводная таблица» (меню «Данные») нажать кнопку «Создать».

В качестве источника в диалоговом окне «Выбрать источник» устанавливаем переключатель в положение «Выбранное выделение». Нажимаем ОК.

Далее появится диалоговое окно «Разметка сводной таблицы». Оно и является своеобразным конструктором сводной табицы из приведенных выше примеров с Microsoft Excel и Google Spreadsheets.

Чтобы построить сводную таблицу, аналогичную двум предыдущим примерам, сделайте следующие действия.

В область «Поля страниц» перетащите поле User type, так как в LibreOffice область «Поля страниц» является областью фильтров сводной таблицы.
В область столбцов перенесите поле Device category. По умолчанию область столбцов уже будет содержать поле с именем «Данные». С помощью этого поля вы можете изменять положение рассчитываемых метрик. Примерно так же, как мы делали это в Excel и Google Таблицах. Тогда мы располагали информацию так, чтобы каждая дата содержала две строки: одну с данными о сессиях, а вторую — о показателе отказов. Так же и в LibreOffice вы можете менять расположение вычисляемых данных, отображая их в строках или столбцах.
В область «Поля строк» перенесите сначала поле Week, после чего под этим полем расположите поле Date.
В область «Поля данных» перетащите поле Sessions. Для того, чтобы изменить агрегирующую функцию либо настроить дополнительное вычисление для рассчитываемого поля, достаточно дважды кликнуть на него левой кнопкой мыши и выбрать из списка нужную функцию либо дополнительное вычисление.

Ранее мы в примере с Microsoft Excel устанавливали в качестве дополнительного вычисления «Процент от суммы по строке». Чтобы настроить подобное вычисление в LibreOffice после двойного клика по полю Sessions, расположенного в области данных, в открывшемся диалоговом окне «Поле данных» раскройте меню «Отображаемое значение», установите «Тип: % от строки» и нажмите ОК.

5. Для быстрого изменения уровня детализации откройте подменю «Параметры», находящееся в нижней части диалогового окна «Разметка сводной таблицы», и установите там флажки «Добавить фильтр» и «Разрешить переход к деталям». Нажмите OK.

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

У вас получится такая сводная таблица:

Генерация отчетов

Затем давайте скомпилируем и выполним указанные выше файлы, используя наш обычный процесс сборки ANT. Содержимое файла build.xml (сохраненного в каталоге C: \ tools \ jasperreports-5.0.1 \ test) показано ниже.

Файл импорта — baseBuild.xml взят из главы « Настройка среды» и должен быть помещен в тот же каталог, что и build.xml.

Затем давайте откроем окно командной строки и перейдем в каталог, в котором находится build.xml. Наконец, выполните командуant -Dmain-class=com.tutorialspoint.JasperReportFill (viewFullReport является целью по умолчанию) следующим образом:

В результате вышеуказанной компиляции открывается окно JasperViewer, как показано на экране, приведенном ниже —

Здесь мы видим, что каждая страна и название указаны в таблице.

Использование функции PivotTable

Чтобы использовать функцию PivotTable, сначала необходимо убедиться, что данные в таблице организованы по столбцам или диапазонам. Затем выберите любую ячейку в таблице и откройте вкладку «Вставка» в меню Excel. На вкладке «Вставка» выберите «Сводная табличка» и определите диапазон данных, который вы хотите анализировать.

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

Поля, добавленные в категорию «Строки», будут отображаться в вертикальных строках кросс-таблицы. Поля, добавленные в категорию «Столбцы», будут отображаться в горизонтальных столбцах кросс-таблицы. Поля, добавленные в категорию «Значения», будут использоваться для агрегирования данных.

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

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

Пример кросс-таблицы PivotTable
Страна
Год
Продажи

Россия
2020
10000

Россия
2021
15000

США
2020
20000

США
2021
25000

В данном примере кросс-таблицы PivotTable можно видеть сводную информацию о продажах в разных странах и за разные годы. Поля «Страна» и «Год» добавлены в категории «Строки», а поле «Продажи» добавлено в категорию «Значения».

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

Примеры использования кросс таблицы в различных отраслях

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

1. Маркетинг и реклама

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

2. Финансы и бухгалтерия

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

3. Исследования и социология

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

4. Производство и логистика

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

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

Построение взаимосвязей между таблицами в модели данных

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

Функция представления диаграммы в Power Pivot позволяет перетаскивать эти поля для построения взаимосвязи. Выполните следующие действия, чтобы создать ссылки на таблицы в модели данных Excel:

1. На ленте книги Excel щелкните меню Power Pivot .

2. Теперь нажмите « Управление» в разделе « Модель данных ». Вы увидите редактор Power Pivot, как показано ниже:

3. Нажмите кнопку « Просмотр диаграммы» , расположенную в разделе « Просмотр » на вкладке « Главная страница Power Pivot». Вы увидите заголовки столбцов таблицы, сгруппированные по имени таблицы.

4. Теперь вы сможете перетаскивать уникальный идентификатор поля из одной таблицы в другую. Ниже представлена схема отношений между четырьмя таблицами модели данных Excel:

Ниже описывается связь между таблицами:

Стол студенты | Студенческий билет к таблице Оценки | Студенческий билет
Таблица семестров | Идентификатор семестра в таблицу Оценки | Семестр
Табличные классы | Номер класса в таблицу Оценки | ID класса

5. Вы можете создавать отношения, выбирая пару столбцов с уникальными значениями. Если есть дубликаты, вы увидите следующую ошибку:

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

7. В редакторе Power Pivot щелкните вкладку « Дизайн » и выберите « Управление взаимосвязями», чтобы узнать, в каких полях выполняются подключения.

Синтаксис сводных таблиц

Ниже представлен эквивалент рассмотренной выше операции, реализованный с помощью метода pivot_table объекта DataFrame:

titanic.pivot_table('survived', index='sex', columns='class')

Это выражение намного легче читается, по сравнению с эквивалентным выражением для GroupBy, и дает тот же результат. Как можно было ожидать, в случае трансатлантического рейса начала 20-го века, больше шансов выжить было у женщин и пассажиров более высоких классов. Женщины из первого класса спаслись почти все (привет, Кейт!), в то время как из каждых десяти мужчин с билетами третьего класса выжил только один (прости, Лео!).

Как правильно анализировать

Процесс анализа состоит из нескольких этапов. Аналитику следует:

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

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

Пример

Предположим, есть две переменные: пол (мужской или женский) и праворукость (правша или левша). Далее предположим, что 100 человек были случайным образом выбраны из очень большой популяции в рамках исследования половых различий в руке. Таблица непредвиденных обстоятельств может быть создана для отображения количества людей, которые являются мужчинами-правшами и левшами, женщинами-правшами и левшами. Такая таблица непредвиденных обстоятельств представлена ниже.

Handed- Несс Секс	Правша	Левша	Общий
Мужской	43 год	9	52
женский	44	4	48
Общий	87	13	100

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

Таблица позволяет пользователям сразу увидеть, что доля правшей мужчин примерно такая же, как и доля правшей женщин, хотя пропорции не идентичны. Сила связи может быть измерена отношением шансов , а отношение шансов популяции оценивается отношением шансов выборки . Значение разности между двумя пропорциями могут быть оценены с различными статистическими тестов , включая хи-квадрат тест Пирсона , тем G -test , точный критерий Фишера , тест Boschloo в и тест Барнарда , при условии , что записи в таблице представляют собой лиц , случайно отобранные из совокупности, о которых следует сделать выводы. Если пропорции людей в разных столбцах значительно различаются между строками (или наоборот), говорят, что между двумя переменными существует непредвиденное обстоятельство . Другими словами, две переменные не независимы. Если нет непредвиденных обстоятельств, говорят, что две переменные независимы .

Приведенный выше пример представляет собой простейший вид таблицы непредвиденных обстоятельств, таблицы, в которой каждая переменная имеет только два уровня; это называется таблицей непредвиденных обстоятельств 2 × 2. В принципе, можно использовать любое количество строк и столбцов. Также может быть более двух переменных, но таблицы непредвиденных обстоятельств более высокого порядка трудно представить визуально. Связь между порядковыми переменными или между порядковыми и категориальными переменными также может быть представлена в таблицах непредвиденных обстоятельств, хотя такая практика встречается редко. Для получения дополнительной информации об использовании таблицы непредвиденных обстоятельств для связи между двумя порядковыми переменными см. Гамму Гудмана и Крускала .

Заключение

Предлагаю сравнить функционал Microsoft Excel, Google Spreadsheets и OpenOffice по работе со сводными таблицами.

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

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

Оригинал: http://blog.netpeak.ru/kak-postroit-svodnie-tablitsy-v-excel-libreoffice-openoffice-i-tablitsah-google/