вычисляемое поле в сводной таблице гугл
Создание сводной таблицы в Google Sheets
Для того, чтобы свести в один понятный формат большую таблицу со множеством данных можно воспользоваться выводом формулами данных в отдельный диапазон, либо создать простую сводную таблицу в Google Sheets.
Как в Google таблицах создать новую сводную таблицу?
У меня есть реальный пример массива данных расхода на продукты за 2021 год и мне интересно свести все в единую среду через создание простой сводной таблицы.
Нужно выделить всю область, которую мы хотим поместить в таблицу, вместе с заголовками столбцов.
Чтобы сводная таблица была полноценной и полезной, данные в массиве этой таблицы должны быть одного типа и записаны в подготовленных столбцах: дата в столбце для даты; количество товаров (к примеру); цена и т.д. По возможности не используйте объединение ячеек в таблице с данными.
В меню с инструментами Google таблицы кликайте на «Данные» и в модальном меню кликаете на «Создать сводную таблицу».
Появится всплывающее окно с информацией по диапазону с данными, которые мы помещаем в сводную таблицу и возможность разместить эту таблицу на текущем (активном листе) или в новом (создается автоматически).
У вас появится новая сводная таблица с настройками в правой части. В появившимся меню есть возможность выбора отображения данных по рекомендациям системы или по своим собственным предпочтениями настроек вида подачи данных.
В конце можно применить условное форматирование к строкам и столбцам таблицы, выставив границы рамок и т.д.
Попробуйте создать свою простую сводную таблицу в Google таблицах и поделитесь своим результатом в комментариях.
Cоветы и руководства по Google Таблицам
Мастер Google Таблиц с полезными советами и руководствами
Как вставить сводную таблицу в Google Таблицы
Таблицы — отличный способ работы с данными. Они помогают упорядочить данные, чтобы вы могли получить из них полезную информацию. Google Таблицы предоставляет дополнительные возможности сводных таблиц, которые делают процесс анализа данных еще более мощным.
Если вы новичок в сводных таблицах, это руководство даст вам толчок, необходимый для того, чтобы глубже вникнуть в свои данные и совершить серьезную магию обработки данных.
Что такое сводные таблицы?
Сводная таблица принимает большой набор данных и помогает подвести итоги и сделать из них выводы.
Когда у вас есть небольшая электронная таблица с небольшим количеством данных, ее легко читать, понимать и делать выводы. Но по мере того, как данные становятся больше, все, что вы видите, — это множество цифр. Тогда становится трудно делать выводы из ваших данных.
Давайте посмотрим на пример использования. Рассмотрим пример данных на изображении ниже. Он состоит из подробностей о продажах, совершенных сотрудниками воображаемой сети магазинов канцелярских товаров. На первый взгляд все, что мы видим, — это множество чисел, дат и текста. Из этих данных довольно сложно понять какой-то смысл.
Но с помощью сводных таблиц вы можете сузить такой большой набор данных и довольно легко визуализировать отношения между точками данных. Например, вы можете использовать сводную таблицу, чтобы ответить на такие вопросы, как:
Теперь, конечно, можно анализировать данные с помощью формул и функций, но сводные таблицы могут сделать вашу работу намного быстрее и проще и с минимальным количеством человеческих ошибок.
Преимущества сводных таблиц в Google Таблицах
Вот некоторые из причин, по которым все больше и больше людей любят сводные таблицы:
В приведенном выше случае использования мы можем легко использовать сводные таблицы, чтобы сузить данные, чтобы показать нам, сколько всего продал каждый торговый представитель.
Как вставить сводные таблицы в Google Таблицы
Вот как вы можете вставлять и использовать сводные таблицы в Google Таблицах для анализа данных в нашем случае использования. Мы получили эти образцы данных со следующей страницы:
Данные можно использовать и загружать бесплатно, поэтому вы также можете загрузить их и преобразовать в Google Таблицы, чтобы следовать нашему руководству.
Вы увидите, что ваша сводная таблица создана в новой электронной таблице с именем Pivot Table 1. Вы можете продолжить и переименовать ее во что-нибудь другое, если вам нужно.
Сводные таблицы, предлагаемые Google Таблицами
Если вы нажмете на любое из предложенных предложений, Google Таблицы автоматически создадут вашу начальную сводную таблицу. Давайте сначала рассмотрим некоторые из этих предложений.
Одно из предложений — это сводная таблица, в которой показано среднее количество единиц, проданных каждым торговым представителем. Если вы нажмете на эту опцию, вы получите сводную таблицу, которая поможет вам легко узнать, какой торговый представитель в среднем продал больше всего единиц, как каждый из них складывается друг против друга и кто, возможно, заслуживает повышения в этом году!
Другое предложение — это сумма удельной стоимости для каждого региона. Это даст вам таблицу с каждым регионом и соответствующей общей стоимостью единицы. Благодаря этому вы можете получить некоторое представление о том, как обстоят дела в каждом регионе (с точки зрения продаж).
Обратите внимание, что вы можете использовать предлагаемую сводную таблицу и добавлять собственные строки и столбцы для ее настройки.
Например, имеет смысл видеть количество проданных единиц по регионам, а не общую стоимость единицы. Таким образом, вы можете удалить столбец «Стоимость единицы», выбрав крестик, соответствующий параметру «Стоимость единицы» в разделе «Значения».
Это приведет к удалению столбца SUM of Unit Costs из сводной таблицы.
Затем вы можете нажать кнопку «Добавить» рядом с полем «Значения» и выбрать «Единицы» из раскрывающегося списка.
Создание собственной настраиваемой сводной таблицы
Если предложения Google Таблиц не соответствуют вашим требованиям, вы также можете вручную создать свою собственную сводную таблицу Google.
Редактор сводной таблицы (на правой боковой панели) также дает вам возможность вставлять данные в сводную таблицу в соответствии с вашими требованиями.
Здесь вы увидите 4 варианта вставки:
Попробуем создать собственную сводную таблицу для выполнения такого анализа:
Сколько продаж сделал каждый торговый представитель по разным типам товаров, проданных в 2018 году?
Чтобы ответить на поставленный выше вопрос, вот что нам нужно в каждом из измерений:
Теперь приступим к созданию нашей сводной таблицы.
Вставка строк:
Чтобы отобразить имена торговых представителей в каждой строке, нажмите кнопку «Добавить» рядом с «Строки» и выберите «Представитель» в раскрывающемся списке. Сводная таблица извлечет имена всех торговых представителей из исходной таблицы.
Обратите внимание, что сводная таблица принимает имена торговых представителей, удаляет дубликаты и отображает их в алфавитном порядке в столбце A.
Вставка столбцов:
Затем мы хотим отобразить разные товары, проданные в каждом столбце. Для этого нажмите кнопку «Добавить» рядом с «Столбцы» и выберите «Элемент» из раскрывающегося списка. В сводной таблице в каждом столбце будут отображаться названия различных элементов без каких-либо повторов.
Итак, теперь ваш стол начинает обретать форму. У вас есть торговые представители по строкам, а типы предметов — по столбцам.
Вставка значений
Чтобы заполнить отдельные ячейки сводных таблиц, нам нужны значения общих продаж, выполненных каждым торговым представителем для каждого элемента.
Нажмите кнопку «Добавить» рядом с полем «Значения» и выберите «Всего» из раскрывающегося списка.
Теперь вы увидите общий объем продаж каждого типа товара для данного торгового представителя.
Если вместо общих продаж вы хотите увидеть средние продажи, щелкните раскрывающееся меню под «Суммировать по» в разделе «Всего» и выберите «Среднее» из раскрывающегося списка.
Итоги в каждой ячейке теперь должны быть заменены средними продажами, сделанными для каждого типа товаров для данного торгового представителя.
Вы также найдете «Общий итог», рассчитываемый автоматически для каждого элемента, в нижней части сводной таблицы. Общая сумма для каждого торгового представителя отображается в правом столбце таблицы.
Добавление фильтров
Наша сводная таблица уже начала давать много полезной информации. Осталось только убедиться, что мы получим средние продажи только за 2018 год. Для этого мы используем фильтры.
Это гарантирует, что средние значения будут учитывать только все даты до 1 января 2019 года (а это только все даты 2018 года!)
Вот и все!
Теперь у нас есть сводная таблица, которая отвечает на наш вопрос: сколько продаж сделал каждый торговый представитель для разных типов товаров, проданных в 2018 году?
Выводы
Сводные таблицы значительно упрощают работу, когда мы хотим проанализировать большие объемы данных или увидеть общую картину. Имея всю необходимую информацию прямо перед собой, теперь мы можем ответить практически на любые вопросы, которые у нас есть о наших данных.
Итак, вперед, попробуйте, откройте для себя всю мощь сводных таблиц Google и сделайте неограниченное количество открытий о своих данных. Теперь вы должны иметь возможность создавать широкий спектр сводных таблиц, от простых до сложных сводных таблиц в Google Таблицах.
Сводные таблицы в Google Spreadsheets
Сводные таблицы в Google Таблицах имеют более скромный функционал по сравнению с Excel, но все же остаются полезным инструментом.
Добавить новую сводную таблицу можно следующим образом:
Рассмотрим пример: из таблицы с книгами получим сводную картину по тематикам. Исходная таблица выглядит так:
Для этого в редакторе отчетов в значения перенесем 2015 и 2016 год:
В строки добавим Тематику и отсортируем по убыванию по сумме 2016-го года:
И получим следующий результат:
Рассчитываемое поле
Рассчитываемое поле в сводной таблице позволяет производить дополнительные вычисления. Это аналог вычисляемого поля в Excel.
Добавить его можно, щелкнув на соответствующую кнопку меню после нажатия на “Добавить поле” в редакторе отчетов сводной таблицы:
После этого задайте новому полю имя и введите формулу для расчета.
В примере мы рассчитаем прирост в 2016 году.
Обратите внимание, что названия полей в формуле указываются в апострофах. Они соответствуют названиям столбцов исходной таблицы, на основе которой вы построили сводную.
Как видите, в сводной, как и в обычных диапазонах, можно менять формат ячеек. У ячеек с вычисляемым полем мы установили процентный формат.
В Google Таблицах, в отличие от Excel, можно напрямую ссылаться на ячейки сводной в формулах, без помощи функции ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ (GETPIVOTDATA).
То есть мы могли получить такой же результат и без рассчитываемого поля:
Но это менее предпочтительный вариант. Если вы поменяете структуру сводной, рассчитываемое поле будет работать для всех строк и любой структуры. А формулы придется протягивать вручную.
P.S. Функция ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ в Google Таблицах есть. Какой бы у вас ни был язык формул, ее название нужно вводить на английском: GETPIVOTDATA.
Открытые диапазоны в сводных таблицах
Поэтому в случаях, если нужно работать с открытым диапазоном и данные будут постоянно пополняться, лучше использовать функцию QUERY, а не сводную.
Но! Диапазон вида A:E для сводных работать будет. Так что если исходные данные начинаются с первой же строки листа и будут пополняться, используйте диапазон без номеров строк.
Вычисляемое поле в сводной таблице гугл
В предыдущих публикациях я поделился информацией о том, как импортировать данные из Google Analytics в различные процессоры электронных таблиц (мы рассматривали импорт в Google таблицы и Excel). Но импорт — это только первый шаг на пути к полноценному анализу данных.
После загрузки всей необходимой вам информации в редактор электронных таблиц, ее необходимо визуализировать и только потом анализировать.
Самый удобный и быстрый инструмент для преобразования массива данных в информативный отчет — сводные таблицы. В основе сводных таблиц лежит технология OLAP, описанная в 1993 году известным исследователем баз данных и автором реляционной модели данных Эдгаром Коддом. По сути, сводная таблица — это инструмент обработки данных для их группировки и обобщения.
Любая сводная таблица строится на основе определенной базы данных. В виде базы данных выступает массив, состоящий из полей и записей. Каждая строка (запись) в базе данных представляет собой информацию об отдельном случае, объекте или состоянии изучаемого объекта, а каждый столбец (поле) является параметром, свойством или признаком всех исследуемых объектов. Например, параметром может быть источник трафика, описание информации об источнике. В поле «источник трафика» будет, например, Google.
Все поля базы данных разделяются на два типа: измерения («параметры» в терминологии Google Analytics) и меры («показатели» в терминологии Google Analytics). Измерением является название или свойство объекта, в разрезе которых мы можем анализировать различные количественные показатели.
Каждое измерение содержит ряд элементов. Например, элементами измерения «Источник трафика» будут: Google, Yandex и любые другие возможные источники трафика.
Мерой являются количественные показатели, которые мы сравниваем между элементами измерений. Например: количество кликов, количество транзакций, сумма дохода.
С общим понятием, что такое сводная таблица, мы ознакомились. Далее рассмотрим, как строятся сводные таблицы в различных процессорах электронных таблиц.
Безусловным лидером в реализации всего возможного функционала сводных таблиц является Microsoft Excel. По функциональным возможностям с данным инструментом могут соревноваться только такие гиганты BI индустрии, как QlikView и Tableau, но в связи с тем, что данные платформы являются достаточно дорогостоящими и в русскоязычном сегменте пока не успели получить особую популярность, в данной статье рассматриваться не будут.
Для построения сводных таблиц в описанных ниже мануалах используется одна и та же импровизированная база данных, состоящая из выгруженной из Google Analytics информации. Вы можете скачать эту таблицу по ссылке.
Сводные таблицы в Microsoft Excel 2013
Для построения сводной таблицы в Microsoft Excel 2013 вам необходимо открыть скачанный ранее csv файл. После чего, установив курсор на любой из ячеек таблицы, нажмите Ctrl+A — этим действием вы выделите всю базу данных. На её основе мы будем строить сводную таблицу.
Далее перейдите на вкладку «Вставка» и в группе «Таблицы» нажмите «Сводная таблица».
В открывшимся диалоговом окне «Создание сводной таблицы» ничего не меняем и жмем ОК.
На этом процесс создания закончен и мы приступаем к работе со сводной таблицей.
После создания таблицы в книге Excel будет создан новый лист. Он будет выглядеть так:
Как вы видите на скриншоте, данный лист состоит из самой сводной таблицы и конструктора сводной таблицы. Конструктор состоит, в свою очередь, из пяти частей:
Для того, чтобы понять принцип работы сводной таблицы, давайте произведем следующие действия в конструкторе сводной таблицы:
В итоге должно получиться так:
В результате четырех произведенных действий мы построили перекрестную таблицу, которую в Google Analytics построить нельзя. В строках мы видим даты, а по столбцам у нас располагается информация о количестве сеансов за определенную дату по каждому типу устройств. Это получилось, потому что мы перенесли поле Date в область строк.
В отчете сводной таблицы в каждой строке появилась определенная дата. После того, как мы перетащили поле Device category в область колонн, каждый столбец сводной таблицы получил название одного из существующих в базе типов устройств. Перенеся поле Sessions в область значений, мы заполнили область значений сводной таблицы информацией о количестве сеансов за каждый день по каждому типу устройств.
В полях, которые были добавлены в область значений конструктора сводной таблицы, можно изменять агрегирующую функцию. По умолчанию для числовых полей применяется суммирование, но вы можете выбрать любую другую из предложенных агрегирующих функций. Для этого достаточно в области значений сводной таблицы кликнуть правой кнопкой мыши и в выпадающем меню «Итоги по» выбрать нужную функцию.
Остается вопрос: а что же нам дал перенос поля User type в фильтры? Чтобы это понять, давайте применим фильтр сводной таблицы и выведем в отчет информацию только по новым пользователям.
Теперь в область значений сводной таблицы выводится информация о количестве сеансов, совершенных только новыми пользователями в разрезе дат и типов устройств.
Выше показан пример простейшей сводной таблицы, но на самом деле это далеко не весь функционал, который вы можете использовать для построения отчетов сводных таблиц. Любые параметры могут быть выведены в отчет в виде определенной иерархии. Для примера перенесем в область строк поле Week таким образом, чтобы оно находилось выше, чем поле Date.
Отчет сводной таблицы будет сгруппирован не только по датам, но и по неделям. К тому же к каждой неделе будут прикреплены промежуточные итоги для более удобного анализа отображаемой информации. Теперь сводная таблица приобрела следующий вид:
Такие иерархии можно строить и в области колонн. Вы можете смотреть информацию на уровне недель и при необходимости детализировать информацию по количеству сеансов до уровня дат простым нажатием на иконку +/—.
Очень полезной функцией сводных таблиц в Microsoft Excel является возможность дополнительных вычислений над любыми выведенными в таблицу показателями.
Например, мы хотим посмотреть не количество сеансов за каждую дату в разбивке по типам устройств, а какую долю сеансов в каждой дате занимает каждый тип устройств. Для этого достаточно кликнуть правой кнопкой мыши в области значений сводной таблицы, и в контекстном меню выбрать пункт «Дополнительные вычисления» => «% от суммы по строке».
Теперь сводная таблица показывает долю каждого типа устройства в общем объеме сеансов за день.
Зачастую при анализе данных нам необходимо получить какой либо относительный расчетный показатель. При правильном построении базы данных такие показатели в ней не содержатся, но информация о полях, необходимых для вычисления какого-либо расчетного показателя, как правило, в базе данных есть.
Приведем пример. Нам надо вывести в таблицу показатель отказов для каждого типа устройств. Показатель отказов является относительным расчетным показателем. В самой базе данных, на основе которой мы строили сводную таблицу, он не содержится, но у нас есть все необходимые поля для его вычисления.
Чтобы создать вычисляемое поле в сводной таблице, необходимо передвинуть курсор в любую ячейку и перейти на вкладку «Анализ» в основном меню. Далее в группе «Вычисления» открыть выпадающее меню «Поля, элементы и наборы» и выбрать пункт «Вычисляемое поле».
После этого появится диалоговое окно «Вставка вычисляемого поля», в котором необходимо дать название вашему вычисляемому полю. В данном случае наиболее подходящим будет «Показатель отказов».
Заполнив название нужных полей сводной таблицы, напишите формулу, по которой будет рассчитываться наш показатель. При написании формул вы можете использовать все существующие формулы Microsoft Excel.
Единственное отличие от использования функций при создании вычисляемого поля и рабочего листа заключается в том, что при написании формулы в ячейке рабочего листа вы ссылаетесь на адреса нужных вам ячеек (например, A1), а при написании формулы вычисляемого поля сводной таблицы вы ссылаетесь на название полей сводной таблицы.
В нашем случае формула вычисляемого поля будет выглядеть как «=Bounces / Sessions».
В область значений отчета сводной таблицы будет добавлено поле «Показатель отказов»:
После добавления нового расчетного поля в таблице были автоматически созданы четыре новые колонны. И если сейчас посмотреть на конструктор сводной таблицы, мы увидим, что в область «Колонны» добавлено поле «Значения».
Это поле появляется автоматически, если в область «Значения» добавлено более одного поля. Вы можете перенести поле «Значения» в строки или колонны, в область фильтров и значений данное поля перенести нельзя. По сути, это поле содержит название всех показателей (полей), выведенных в область значений.
Для закрепления материала и преобразования сводной таблицы в более читабельный вид перетянем поле «Значения» в область строк под поле Date.
Теперь в таблице каждая дата разбита на две строки. В первой выводится доля сеансов по каждому типу устройства, во второй — показатель отказов для каждого типа устройств.
Иногда в ходе анализа нам требуется каким-либо образом объединить некоторые элементы измерения в группы. Например, в данном случае нам может понадобиться объединить типы устройств mobile и tablet в одну группу и назвать ее Other. Таким образом мы можем проанализировать различие между настольными устройствами и всеми остальными.
Все, что нам необходимо сделать для группировки измерения, — это выделить нужные его элементы с помощью мыши, после чего вызвать контекстное меню правым кликом мыши и выбрать пункт «Группировать».
Элементы mobile и tablet будут объединены в группу, которой по умолчанию присваивается название «Группа 1». Чтобы переименовать группу, просто перейдите в ячейку с названием и введите новое Other.
После группировки некоторых элементов измерения область столбцов отчета сводной таблицы стала двухуровневой, так же, как и область строк после добавления в нее поля Week. Теперь вы можете анализировать информацию по типам устройств на двух уровнях, переключаясь между ними с помощью +/—.
Далее вы можете настроить внешний вид вашей сводной таблицы, используя готовые шаблонные стили, либо выбрав свой собственный.
Для создания кастомного стиля с помощью мыши выберите любую ячейку, которая входит в область отчета сводной таблицы, и, перейдя на вкладку «Конструктор», выберите один из предложенных стилей оформления.
Также существует возможность добавления срезов и временных шкал.
Хочу заметить, что этот функционал не доступен в старых версиях Microsoft Excel, возможность добавления срезов появилась в 2010 версии, а временные шкалы добавили только в 2013 году.
Срез выполняет ту же функцию что и фильтр, который мы построили, добавив поле User type при построении сводной таблицы в область фильтров. Единственное отличие заключается в том, что срез имеет более удобную визуализацию. Давайте добавим срез по Source. Для этого выделите любую ячейку, относящуюся к области отчета сводной таблицы, перейдите на вкладку «Анализ» и в группе «Фильтры» нажмите иконку «Вставить срез».
Откроется диалоговое окно «Вставка срезов», в котором вам необходимо выбрать, по каким полям вы хотите создать срезы. В нашем случае это поле Source.
После нажатия ОК на рабочий лист будет добавлен срез. Использовать срез можно так же, как и обычный фильтр.
Давайте для примера в созданном нами срезе выберем элемент «google» и тем самым в отчет сводной таблицы выведем информацию о доле каждого типа устройств по каждой дате только по сеансам, совершенным из источника «google».
Зажав левый Ctrl, вы можете выбрать любое количество элементов среза, информацию по которым планируете вывести в отчет.
Временная шкала работает по такому же принципу, как и срез, но строить ее можно только на основе полей, содержащих данные в формате даты.
Создается она на вкладке «Анализ» с помощью кнопки «Вставить временную шкалу», находящейся в группе «Фильтры».
С помощью временной шкалы очень удобно выбирать период, за который мы хотим вывести данные в отчет сводной таблицы. Например, мы с помощью нескольких кликов можем вывести в отчет информацию только за август.
Функциональные возможности работы со сводными таблицами в Microsoft Excel 2013 выходят далеко за пределы описанного выше функционала, и в рамках одной статьи осветить все не получится.
Для тех, кто решил всерьез углубиться в изучение этого инструмента, советую ознакомиться с материалами, изложенными одним из ведущих специалистов по программному обеспечению электронных таблиц Биллом Джеленом в своей книге «Сводные таблицы в Microsoft Excel 2013» (если интересно, у меня есть электронная версия «Сводных таблиц в Microsoft Excel 2010»).
Сводные таблицы в Google Spreadsheets (Google таблицы)
Основное преимущество Google Spreadsheets — доступ к ним есть всегда и везде при условии наличия подключения к интернету. Именно это делает данный продукт наиболее удобным при совместной работе нескольких пользователей.
Для построения сводной таблицы в Google Spreadsheets необходимо создать новую таблицу в своем Google Диске, перейдя поэтой ссылке.
После этого импортируйте в созданную таблицу скачанную ранее базу данных (скачать csv файл с базой данных).
Чтобы загрузить базу данных в таблицу, в меню «Файл» выберите пункт «Импорт».
В открывшемся диалоговом окне переходим на вкладку «Загрузка» и жмем кнопку «Выберите файл на компьютере», после чего выбираем скачанный ранее файл pivotTableDB.csv.
В диалоговом окне «Импорт файла» устанавливаем переключатель «Действие после импорта» в положение «Заменить текущий лист», а переключатель «Разделитель» устанавливаем в положение «Другое». В качестве разделителя вводим точку с запятой.
После нажатия кнопки «Импортировать» данные будут загружены в вашу таблицу на «Лист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».
В отличие от Microsoft Excel, в данном случае названия полей в формуле расчета можно ввести только с клавиатуры. Это важно.
Теперь сводная таблица имеет следующий вид:
Для каждого типа устройства добавлен дополнительный столбец с информацией о показателе отказов. Если хотите привести таблицу в более читабельный вид и показатель отказов выводить второй строкой для каждой даты, а не вторым столбцом для каждого типа устройств, в редакторе отчетов в области значений переставьте переключатель в положение «как: Строки».
Отчет примет вид, в котором на каждую дату приходится две строки данных. В первой будет количество сеансов, во второй — показатель отказов.
На этом функциональные возможности 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 не поддерживает функционал рассчитываемых полей.
У вас получится такая сводная таблица:
Заключение
Предлагаю сравнить функционал Microsoft Excel, Google Spreadsheets и OpenOffice по работе со сводными таблицами.
В данной таблице перечислен только тот функционал, который был описан в данной статье. Относительно Microsoft Excel, это даже не половина всех существующих возможностей по построению и использованию сводных таблиц.
Сводные таблицы — самый удобный инструмент для анализа больших массивов данных. Время, инвестируемое в изучение сводных таблиц, с лихвой окупится в будущем, когда вы за считанные минуты сможете из массива данных, насчитывающего десятки, а в некоторых случаях и сотни тысяч строк извлекать необходимую для анализа информацию.
Ищете исполнителя для реализации проекта?
Проведите конкурс среди участников CMS Magazine
Узнайте цены и сроки уже завтра. Это бесплатно и займет ≈5 минут.