Перейти к содержимому

Для чего нужны сводные таблицы в эксель

  • автор:

Для чего нужны сводные таблицы в excel

​Смотрите также​​ имеющихся полей строк​ в двух магазинах.​ флажок напротив названия​ продаж по месяцам.​ доступа.​ которая подходит для​ форматирование. Например, если​ ниже.​ 2 3 4​Второе поле​Group​ будущем измените структуру​ В оставшейся части​ заголовки из верхней​ примерно так:​

​ было.​Сводные таблицы​ и установить у​Порядок создания сводной таблицы​ товара.​ Поэкспериментируем и установим​На первом шаге выбираем​ Вашей версии Excel.​ для значений в​Убедитесь, что выбранный диапазон​ 5 6 7​Amount​(Группировать):​ сводной таблицы (а​ статьи мы разберём​ области в пустые​Возможно, Вас удивит, что​Сводные таблицы вместе с​

  • ​— это один​
  • ​ других полей. Сделаем​
  • ​ из нескольких листов​
  • ​Жмем ОК – сводная​
  • ​ шаг – «Кварталы».​
  • ​ источник данных для​
  • ​Рекомендуется начать с 1-й​

Немного истории

​ столбцах​ охватывает именно те​ 8 9 10​назовите​Появится диалоговое окно группировки.​ это случится с​ некоторые способы создания​ области внизу. При​ из этого списка​ недооценкой успеха Windows​ из мощнейших инструментов​ отчет по наименованиям​ такой же.​ таблица меняется.​ Результат – сводная​ формирования сводной таблицы.​ части самоучителя и​B​ ячейки, которые должны​ 11​Averag​ Мы выбираем​ вероятностью 99%), то​ более сложных сводных​ этом автоматически формируется​ торговых операций с​

​ в целом, сыграли​ Excel. Они позволяют​

  • ​ товаров, а не​Создадим отчет с помощью​Сводная таблица применяется для​ таблица вида:​ Нажимаем «Далее». Чтобы​ изучать пособие по​–​ быть использованы для​Date​e, операцию для него​Months​ форматирование будет потеряно.​ таблиц, а также​ сводная таблица, в​ помощью сводных таблиц​ похоронный марш для​ анализировать и подводить​ по отделам.​
  • ​ мастера сводных таблиц:​ быстрого анализа большого​Если фамилия продавцов для​ собрать информацию в​ сводным таблицам Excel​G​ создания сводной таблицы.Здесь​Invoice Ref​ установите​(Месяцы) и жмём​ Нам же нужен​ узнаем, как их​ соответствии с нашими​ и всего за​ Lotus 1-2-3 и​ различные итоги для​

​А вот что получится,​Вызываем меню «Мастер сводных​ объема данных. Она​ анализа деятельности сети​ сводный отчет из​ последовательно.​настроить денежный формат,​

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

​ же можно выбрать,​Amount​Average​

​ОК​ способ сделать его​ настраивать.​ инструкциями. Если мы​ несколько секунд, мы​ положили начало успеху​ больших объёмов данных​ если мы уберем​ таблиц и диаграмм».​ позволяет объединять информацию​ магазинов не важна,​ нескольких листов, выбираем:​Часть 1: Что такое​ то прочесть сводную​ где должна быть​Sales Rep.​(Среднее) и формат​:​ (почти) постоянным.​Во-первых, мы можем создать​ допустили ошибку, то​ можем создать в​ Microsoft Excel.​ с помощью всего​ «дату» и добавим​ Для этого щелкаем​ из разных таблиц​ можем сформировать отчет​ «в списке или​

​ сводная таблица в​ таблицу станет гораздо​ размещена создаваемая сводная​Region​ чисел в этом​Вуаля! От такой таблицы​Во-первых, найдём запись​ двумерную сводную таблицу.​ можно удалить заголовки​ Excel помесячный отчёт​Итак, как же лучше​ лишь нескольких щелчков​

​ «отдел»:​ кнопку настройки панели​

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

​ и листов, подсчитать​ с поквартальной прибылью.​ базе данных Microsoft​ Excel?​ легче.​ таблица. Можно поместить​01/01/2016​ поле тоже измените​ намного больше пользы:​Sum of Amount​ Сделаем это, используя​ из нижней области​ о продажах, что​ охарактеризовать, что собой​ мышью. В этой​А вот такой отчет​ быстрого доступа и​ общий результат. Этот​Чтобы убрать результаты группировки,​ Excel».​Часть 2. Создаём простейшую​Фильтры в сводной таблице​ сводную таблицу​2016-0001​ на​Кстати, эта таблица практически​в области​ заголовок столбца​ либо перетащить другие​

​ мы разбирали выше.​ представляют сводные таблицы?​ статье мы познакомимся​ можно сделать, если​ нажимаем «Другие команды».​ универсальный аналитический инструмент​ необходимо щелкнуть по​На втором шаге определяем​ сводную таблицу в​ позволяют отобразить информацию​На существующий лист​$819​Accounting​ идентична той, которая​Values​Payment Method​

​ им на замену.​ Да, мы сможем​Говоря простым языком, сводные​ со сводными таблицами,​

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

​ перетащить поля между​ Здесь на вкладке​ существенно расширяет возможности​ ячейке с данными​ диапазон данных, на​ Excel?​ для одного значения​(Existing Worksheet) или​Barnes​(Финансовый) с числом​ была показана в​(Значения) и кликнем​(Способ оплаты). Просто​

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

​Область​ сделать это и​ таблицы — это​ разберёмся, что они​ разными областями:​ «Настройка» находим «Мастер​ программы Excel.​ правой кнопкой мыши​ основании которых будет​Часть 3: Группировка в​ или избирательно для​На новый лист​North​ десятичных знаков равным​

​ начале статьи, где​ по ней. В​

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

​ перетащите заголовок​Values​ еще многое другое!​ итоги каких-то данных,​ собой представляют, научимся​

​Чтобы название строки сделать​ сводных таблиц». Добавляем​

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

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

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

​(New Worksheet). Нажмите​​01/01/2016​​ нулю.​ итоги продаж были​ появившемся меню выберем​

  • ​Payment Method​(Значения), вероятно, самая​Для начала убедитесь, что​
  • ​ созданные для облегчения​

​ создавать их и​ названием столбца, выбираем​ инструмент в панель​ по исходным параметрам,​ Либо выбрать данный​ как у нас​Часть 4: Продвинутые сводные​ имеющихся полей данных.​ОК​2016-0002​Для третьего поля​ составлены вручную.​ пункт​в область​ важная из четырёх.​ у Вас есть​ анализа этих данных.​ настраивать.​ это название, щелкаем​ быстрого доступа. После​ поменяв строки и​ параметр в меню​ стоит курсор в​​ таблицы в Excel.​​ Например, в показанной​

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

​.​$456​Amount​Есть еще один очень​

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

​Value Field Settings​Column Labels​ То, какой заголовок​ какие-то исходные данные​ В отличие от​​При написании данной статьи​​ по всплывающему меню.​

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

​ добавления:​ столбцы местами. Можно​ «Структура».​ таблице, диапазон обозначится​Часть 5: Сортировка в​ выше сводной таблице​Появится пустая сводная таблица​Brown​установите название​ важный момент, который​(Параметры полей значений):​(Колонны):​ помещён в эту​ на листе Excel.​ созданных вручную итогов,​ был использован Excel​ Нажимаем «переместить в​Ставим курсор на первую​ произвести фильтрацию данных,​У нас есть сводный​ автоматически.​ сводной таблице.​ мы сможем просматривать​ и панель​South​Count​ необходимо знать! Вы​

​Появится диалоговое окно​​Получим результат:​​ область, определяет, по​ Перечень финансовых операций​ сводные таблицы Excel​ 2010. Концепция сводных​ название столбцов». Таким​ табличку и нажимаем​ показав разные элементы.​ отчет такого вида:​На третьем шаге Excel​Дальнейшее углублённое обучение работе​ данные только для​Поля сводной таблицы​01/01/2016​и операцию для​​ можете создать не​​Value Field Settings​Выглядит очень круто!​ каким данным будут​​ – самое типичное,​​ интерактивны. После создания,​

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

​ таблиц почти не​​ способом мы переместили​​ инструмент «Мастера». В​ А также наглядно​​Видны итоги по месяцам​​ предлагает выбрать, куда​​ со сводными таблицами​​ региона продаж​(Pivot Table Field​2016-0003​​ него –​​ один, а несколько​

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

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

​ открывшемся окне отмечаем,​ детализировать область.​ (сделано «Группировкой») и​ поместить сводную таблицу.​ можно найти на​North​ List), в которой​$538​

​Count​ уровней заголовков строк​Нажмите кнопку​ Как может выглядеть​ среднее, максимум, минимум​ самом деле, это​ изменять их, если​ но способ их​Поле «Отдел» мы проставили​ что создать таблицу​Для примера используем таблицу​ по наименованиям товаров.​ Жмем «Готово» и​ сайте Microsoft Office.​или только для​ уже содержатся несколько​Jones​(Количество)​ (или столбцов):​Number Format​​ такая таблица? Давайте​​ и т.д.) Это,​ может быть перечень​​ они не дали​​ создания немного различен​

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

​ перед наименованиями товаров.​ хотим в «нескольких​ реализации товара в​

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

​ Сделаем отчет более​ открывается макет.​Урок подготовлен для Вас​ региона​ полей данных. Обратите​

​South​В области​… а выглядеть это​(Числовой формат), откроется​ посмотрим…​ почти всегда, численные​ чего угодно: контактные​ той картины, которую​ в каждой новой​ Воспользовавшись разделом меню​ диапазонах консолидации». То​ разных торговых филиалах.​ удобным для изучения.​Нужно обозначить поля для​ командой сайта office-guru.ru​

Настройка сводной таблицы

​South​ внимание, что эти​01/01/2016​Column Labels​​ будет так…​​ диалоговое окно​Перетащите заголовок​​ значения. Отличный кандидат​​ данные сотрудников, коллекция​​ Вы надеялись получить.​​ версии Excel. Если​

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

​ «переместить в начало».​

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

​Из таблички видно, в​Как в сводной таблице​ отображения в отчете.​Источник: http://www.excelfunctions.net/Excel-Pivot-Table-Tutorial.html​

​.​​ поля – заголовки​​2016-0004​​(Колонны) автоматически создано​​То же самое можно​

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

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

​Package​ на место в​ компакт-дисков или данные​ Всего лишь парой​ у Вас версия​Покажем детали по конкретному​ объединить несколько мест​ каком отделе, что,​ сделать итоги сверху:​ Допустим, мы хотим​Перевел: Антон Андронов​Чтобы отобразить данные только​ из таблицы исходных​$1,009​ поле​​ проделать с заголовками​​(Формат ячеек):​

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

​(Комплекс) в область​ этой области –​ о расходе топлива​ щелчков мышью итоги​ Excel не 2010​

​ продукту. На примере​ с информацией. Вид​ когда и на​«Работа со сводными таблицами»​ узнать суммы продаж​Автор: Антон Андронов​ для региона продаж​ данных.Мы хотим, чтобы​​Barnes​​Σ Values​ столбцов (или даже​​Из списка​​Report Filter​ данные под заголовком​ Вашей компании.​ могут быть перевёрнуты​​ года, то будьте​​ второй сводной таблицы,​

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

​ отчета – «сводная​ какую сумму было​ — «Конструктор».​ по каждому продавцу.​​Работать со сводными таблицами​​North​

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

Форматирование сводных таблиц в Excel

​ сводная таблица показывала​North​(Σ Значения) –​ с фильтрами).​Category​(Фильтры):​Amount​Итак, запускаем Excel… и​ таким образом, что​ готовы, что снимки​ где отображены остатки​ таблица». «Далее».​ продано. Чтобы найти​

​На вкладке «Макет» нажимаем​ Ставим галочки –​ Excel приходится в​, в панели​ итоги продаж помесячно​01/02/2016​ перетащите его в​Вернёмся к исходному виду​(Числовые форматы) выберите​Заметьте, где он оказался…​(Стоимость) нашей исходной​ загружаем такой список…​ заголовки столбцов становятся​ экранов в данной​ на складах. Выделяем​Следующий этап – «создать​ величину продаж по​ «Промежуточные итоги». Выбираем​ получаем:​ разных сферах. Можно​Поля сводной таблицы​

​ с разбиением по​​2016-0005​​ область​​ таблицы и посмотрим,​​Accounting​Это даёт нам возможность​ таблицы. Перетащим этот​После того, как мы​​ заголовками строк и​​ статье будут отличаться​

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

​ ячейку. Щелкаем правой​​ поля». «Далее».​​ каждому отделу, придется​

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

​ «Показывать все промежуточные​​Готовый отчет можно форматировать,​​ быстро обрабатывать большие​(Pivot Table Field​​ регионам и по​​$486​

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

​Row Labels​​ как вывести средние​​(Финансовый) и число​​ отфильтровать отчёт по​​ заголовок в область​ открыли этот список​ наоборот. Со сводными​ от того, что​​ кнопкой мыши –​​Прописываем диапазон данных, по​ посчитать вручную на​ итоги в заголовке​

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

​ изменять.​ объемы информации, сравнивать,​ List) перетащите поле​

​ продавцам. Для этого​Jones​(Строки)​ значения вместо сумм.​ десятичных знаков установите​ признаку «Какой комплекс​Values​

​ в Excel, можем​​ таблицами можно проделать​​ Вы увидите на​ «развернуть».​

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

​ которым будем формировать​ калькуляторе. Либо сделать​ группы».​​​ группировать данные. Это​​Region​ в панели​South​

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

​Вот что мы получим​Для начала кликните на​ равным нулю. Теперь​ отдыха был оплачен».​

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

Прочие настройки сводных таблиц в Excel

​(Значения):​ приступить к созданию​ множество различных действий.​ своём экране.​В открывшемся меню выбираем​ сводный отчет. Выделяем​ еще одну таблицу​Получается следующий вид отчета:​Это можно сделать вручную​ значительно облегчает труд​в область​

​Поля сводной таблицы​​01/02/2016​​ в итоге:​​Sum of Amount​​ несколько раз нажмите​ Например, мы можем​Обратите внимание, что заголовок​ сводной таблицы.​ Вместо того, чтобы​​Немного истории​​ поле с данными,​

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

​ первый диапазон вместе​ Excel, где посредством​Уже нет той перегруженности,​ и автоматически.​ менеджеров, продавцов, руководителей,​Фильтры​(Pivot Table Field​2016-0006​Общая сумма, среднее значение​и из появившегося​

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

​ОК​ видеть разбивку по​Amount​Выделите любую ячейку из​ пытаться описать на​​Что такое сводные таблицы?​​ которые необходимо показать.​

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

​ с шапкой –​ формул показать итоги.​​ которая затрудняла восприятие​​Вручную:​​ маркетологов, социологов и​​(Report Filters).​

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

​ List) сделайте вот​$948​

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

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

​ словах все возможности​Как создать сводную таблицу?​Когда нажимаем на сводную​ «добавить». Второй диапазон​ Такими методами анализировать​ информации.​Ставим курсор в любом​

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

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

​ что:​Smith​ – всё в​Value Field Settings​

​ к нашей сводной​ способам оплаты для​ а в области​Затем на вкладке​

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

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

​ месте сводной таблице.​​Сводные таблицы позволяют быстро​​Region​Перетащите поле​​North​​ одной сводной таблице!​(Параметры полей значений):​​ таблице.​​ всех комплексов или​

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

​Values​Insert​​ продемонстрировать это на​​Форматирование сводных таблиц​​ закладка с параметрами​​ столбцов – снова​ и ошибиться.​ Просто на вкладке​ В результате становится​ сформировать различные отчеты​​появится вверху сводной​​Date​

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

​01/02/2016​​Сводные таблицы Microsoft Excel​​В списке​Как видите, числа оказались​ за пару щелчков​(Значения) появилась запись​(Вставка) выберите команду​ практике…​Прочие настройки сводных таблиц​ отчета. С ее​​ «добавить».​​Самое рациональное решение –​

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

​ макет выбираем «Не​ видна вкладка «Работа​ по одним и​ таблицы. Откройте выпадающий​в область​

​2016-0007​ содержат очень-очень много​Summarize value field by​ отформатированы как суммы​

  1. ​ мышью изменить вид​​Sum of Amount​​PivotTable​Данные, которые Вы анализируете​​Заключение​​ помощью можно менять​
  2. ​Теперь в списке выбираем​​ это создание сводной​​ показывать промежуточные суммы»:​​ со сводными таблицами».​​ тем же данным.​
  3. ​ список в этом​​Строки​​$740​​ функций и настроек.​​(Операция) в диалоговом​ в долларах.​ сводной таблицы и​​(Сумма по полю​​(Сводная таблица):​ с помощью сводных​
  4. ​На заре развития программ​​ заголовки, источники данных,​​ первый диапазон. Ставим​​ таблицы в Excel:​​Получим отчет без дополнительных​В меню «Данные» жмем​​ Кроме того, эти​​ поле и выберите​(Row Labels);​Barnes​ В такой небольшой​​ окне​​Раз уж мы занялись​ показать такую же​ Amount), указывающая на​
  5. ​Появится диалоговое окно​​ таблиц, не могут​​ для создания электронных​​ группировать информацию.​​ птичку у единицы.​Выделяем ячейку А1, чтобы​​ сумм:​​ на кнопку «Обновить»​
  6. ​ отчеты можно гибко​​ в нём регион​​Перетащите поле​North​​ статье их все​​Value Field Settings​ форматированием, давайте настроим​ разбивку только для​​ то, что столбец​​Create PivotTable​

​ быть какими попало.​ таблиц балом правил​

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

​С помощью сводных таблиц​ Это первое поле​ Excel знал, с​Огромные сводные таблицы, которые​

Заключение

​ (или комбинацию клавиш​ настраивать, изменять, обновлять​North​Amount​01/03/2016​ не охватить даже​(Параметры поля значений)​ формат для всей​ заказавших комплекс​Amount​(Создание сводной таблицы)​ Это должны быть​ Lotus 1-2-3. Его​ Excel легко проверить,​ сводного отчета. Даем​ какой информацией придется​ составляются на основании​ ALT+F5).​ и детализировать.​. Сводная таблица (как​в область​2016-0008​ близко. Чтобы полностью​​ выберите​​ сводной таблицы. Есть​​Sunseekers​​просуммирован.​ с двумя вопросами​ необработанные исходные данные,​ превосходство было настолько​ насколько правильно обслуживающие​ ему имя –​ работать.​ «чужих» таблиц, периодически​Если нужно обновить все​У нас есть тренировочная​

​ показано на картинке​Σ Значения​
​$543​
​ описать все возможности​

Продвинутые сводные таблицы в Excel

​ несколько способов сделать​.​Если мы посмотрим на​ для Вас:​ вроде какого-то списка.​ полным, что усилия​ организации начисляют квартплату.​ «Магазин 1». Выделяем​В меню «Вставка» выбираем​ нуждаются в детализации.​ отчеты в книге​ таблица с данными:​ ниже) покажет значения​

​(Σ Values);​Smith​ сводных таблиц, потребовалась​(Среднее):​ это. Используем тот,​Итак, если Вы правильно​ саму сводную таблицу,​

​Какие данные использовать для​ Например, это может​ Microsoft, направленные на​ Другой положительный момент​ второй диапазон данных​ «Сводная таблица».​

​ Мы не знаем,​ ​ Excel, выбираем кнопку​ ​Каждая строка дает нам​ ​ только для региона​ ​Перетащите поле​
​North​ ​ бы небольшая книга​ ​Заодно, пока мы здесь,​ ​ что попроще…​ ​ это понимаете, то​
​ то увидим сумму​ ​ создания новой сводной​ ​ быть список совершённых​ ​ разработку собственного программного​ ​ – экономия. Если​
​ – ставим птичку​ ​Откроется меню «Создание сводной​ ​ откуда взялась сумма​ ​ «Обновить все» (или​ ​ исчерпывающую информацию об​
​North​ ​Region​ ​01/03/2016​ ​ или большой веб-сайт.​ ​ давайте изменим​
​Откройте вкладку​ ​ нашу сводную таблицу​ ​ всех значений из​ ​ таблицы?​ ​ продаж в компании​
​ обеспечения (Excel), как​ ​ мы будем ежемесячно​ ​ у цифры «2».​ ​ таблицы», где выбираем​ ​ в конкретной ячейке​
​ комбинацию клавиш CTRL+ALT+F5).​ ​ одной сделке:​ ​.​ ​в область​ ​2016-0009​
​ Смелые и любознательные​ ​Custom Name​ ​PivotTable Tools: Design​ ​ можно назвать трёхмерной.​ ​ столбца​
​Куда поместить сводную таблицу?​ ​ за последние шесть​ ​ альтернативы Lotus, казались​ ​ контролировать, сколько расходуется​ ​ Название поля –​
​ диапазон и указываем​ ​ Excel. Но можно​ ​Настройка автоматического обновления при​

​в каком магазине были​Вы можете быстро переключиться​Колонны​$820​ читатели могут продолжить​(Пользовательское имя) с​(Работа со сводными​ Продолжим настраивать…​Amount​

  1. ​Так как на предыдущем​ месяцев.​ пустой тратой времени.​ света, газа, то​ «Магазин 2». Нажимаем​​ место. Так как​​ это выяснить, если​ изменении данных:​ продажи;​ на просмотр данных​(Column Labels);​Brown​ исследование сводных таблиц.​Average of Amount​ таблицами: Конструктор):​Если вдруг выясняется, что​исходной таблицы.​
    • ​ шаге мы уже​Посмотрите на данные, показанные​ А теперь перенесёмся​
    • ​ сможем найти резерв​ «Далее».​ мы установили курсор​
  2. ​ разбить сводную таблицу​​На вкладке «Работа со​​какого товара и на​ только для региона​​Перетащите поле​​South​​ Для этого достаточно​​(Количество по полю​Далее разверните меню нажатием​Продвинутые сводные таблицы в Excel
  3. ​ в сводной таблице​​Итак, наша первая сводная​​ выбрали одну из​ на рисунке ниже:​ в 2010 год!​Продвинутые сводные таблицы в Excel​ для экономии средств​Выбираем, где расположить сводную​ в ячейку с​ на несколько листов.​ сводными таблицами» (необходимо​ какую сумму;​South​Sale​…​ щелкать правой кнопкой​​ Amount) на что-нибудь​​ на стрелочку в​​ должны выводится только​​ таблица создана! Удобно,​​ ячеек списка, то​​Обратите внимание, что это​
  4. ​ Excel доминирует среди​ на оплату квартиры.​​ таблицу. На существующем​​ данными, поле диапазона​В марте продано двуспальных​ щелкнуть по отчету)​кто из продавцов постарался;​– для этого​s Rep.​…​ мыши практически на​ покороче. Введите в​ нижнем правом углу​ оплата чеком и​ но не особо​ для создания сводной​ не сырые исходные​​ электронных таблиц более,​​Для начала предлагаем составить​ листе или новом.​ заполнится автоматически. Если​
    • ​ кроватей на сумму​​ выбираем меню «Параметры».​​когда (число, месяц).​​ нужно в выпадающем​​в область​
    • ​…​​ любом элементе сводной​​ этом поле что-нибудь​​ раздела​​ кредитной картой (то​
    • ​ впечатляет. Вероятно, мы​​ таблицы будет выделен​​ данные, поскольку для​​ чем Lotus кода-либо​​ сводную таблицу тарифов​
    • ​ Лучше выбрать новый​​ курсор стоит в​​ 23 780 у.е.​​Открываем «Дополнительные параметры сводной​​Если это огромная сеть​​ списке в поле​

Продвинутые сводные таблицы в Excel

  • ​ весь список автоматически.​ них уже подведены​ за всю свою​ по всем коммунальным​
  • ​ лист, чтобы не​ пустой ячейке, необходимо​​ Откуда взялась эта​​ таблицы». Открывается мастер.​
  • ​ магазинов и продажи​​Region​​(Column Labels).​ создадим сводную таблицу,​ какие открываются функции​Avg​​(Стили сводной таблицы),​​ то мы можем​​ информации о наших​​ Заметьте, что мы​ итоги. В ячейке​ историю, а число​ платежам. Для разных​ было наложений и​ прописать диапазон вручную.​ цифра. Выделяем ячейку​Продвинутые сводные таблицы в Excel
  • ​В разделе «Данные» устанавливаем​​ идут, то в​​выбрать​

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

Продвинутые сводные таблицы в Excel

​ можем выбрать другой​ B3 мы видим​ людей, которые до​ городов данные будут​ смещений. У нас​​ Сводную таблицу можно​​ с данной суммой​​ галочку напротив пункта​​ течение одного квартала​South​ будет заполнена ежедневными​ продаж помесячно за​

Фильтры в сводной таблице

​ Ленте Вы найдёте​Нажмите​ коллекцию встроенных стилей:​Cash​ сейчас.​ диапазон, другую таблицу​ $30000, что, вероятно,​ сих пор используют​ свои.​ получилось так:​ сделать на этом​​ и щелкаем правой​​ «Обновить при открытии​ размер таблицы станет​​.​​ значениями продаж для​

​ год с разбивкой​ две вкладки:​​ОК​​Выберите любой подходящий стиль​​(Наличными). Для этого​​Обратимся к исходным данным​ и даже какой-нибудь​​ является суммарным результатом,​​ Lotus, стремится к​​Для примера мы сделали​​Как видите всего в​

Продвинутые сводные таблицы в Excel

​ же листе или​​ кнопкой мыши и​​ файла».​ ужасающим. Проанализировать данные​Урок подготовлен для Вас​ каждого региона и​ по регионам и​​PivotTable Tools: Options​​и посмотрите, что​ и посмотрите на​ рядом с​ и попробуем определить​​ внешний источник данных,​​ который сделал James​

Продвинутые сводные таблицы в Excel

​ нулю. Как это​ сводную табличку тарифов​ несколько кликов можно​​ на другом. Если​​ выбираем опцию:​Теперь каждый раз при​ в сотне строк​​ командой сайта office-guru.ru​​ для каждого продавца,​​ по продавцам. Процесс​​(Анализ) и​

​ получилось. Обратите внимание,​ результат в своей​
​Column Labels​
​ один или несколько​

​ например, таблицу базы​

Сводные таблицы в Excel – самоучитель в примерах

​ Cook в январе.​ могло произойти? Что​ для Москвы:​

​ создавать сложные отчеты​ мы хотим, чтобы​На новом листе откроется​ открытии файла с​​ будет очень сложно.​Источник: http://www.excelfunctions.net/advanced-pivot-table.html​ как показано ниже.​​ создания этой сводной​Design​ все значения изменились​ сводной таблице:​

​нажмите стрелку вниз​ столбцов, которые можно​ данных Access или​ Где же тогда​ послужило причиной для​Для учебных целей возьмем​ из нескольких листов​ сводные данные были​ таблица с данными​ измененными данными будет​ А на составление​Перевел: Антон Андронов​

​Чтобы сгруппировать данные помесячно:​ таблицы описан ниже.​(Конструктор). Не бойтесь​ с итоговых сумм​Иногда приходится фильтровать данные​ и в выпадающем​ использовать, чтобы раздробить​ MS-SQL. К тому​ исходные данные? Откуда​ такого драматического разворота​ семью из 4​

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

  • ​Автор: Антон Андронов​Кликните правой кнопкой мыши​Выделите любую ячейку в​
  • ​ допустить ошибку, всегда​ на средние значения,​ по датам. Например,​
  • ​ меню снимите галочку​ эту сумму. Например,​
  • ​ же нам необходимо​ взялась цифра $30000?​
  • ​ событий?​ человек, которые проживают​

​ объема информации.​ не забывайте указывать​Мы можем переместить всю​ сводной таблицы.​

​ один день. В​Перед Вами пошаговое руководство​
​ по любой дате​
​ диапазоне или весь​

​ можно удалить сводную​

Примеры работы со сводными таблицами в Excel

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

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

Создание отчета с помощью мастера сводных таблиц

​ торговых операций присутствует​Cash​

Тренировочная таблица.

​ нашу сводную таблицу​ новую сводную таблицу:​ продаж, из которого​

  • ​ фактора:​ кв. м. Чтобы​
  • ​ и удаления столбцов.​ На странице появляется​
  • ​ новый лист, выбрав​
  • ​ отчет большой объем​

​ таблица просто необходима.​ таблиц в Excel.​ столбце сводной таблицы;​ нужно использовать для​ все заново. У​ ячейке) поменялся на​ много-много дат. Excel​:​ таким образом, чтобы​ на новом листе​ этот итог за​Во-первых, компания Lotus решила,​ контролировать коммунальные платежи,​

​ Для примера рассмотрим​ следующая форма:​ на вкладке «Действия»​ данных, для выводов​Создам отчет с помощью​Мы начнём с того,​

  1. ​В появившемся контекстном меню​
  2. ​ построения сводной таблицы.​ Вас есть возможность,​Avg​ предоставляет инструмент для​
  3. ​Давайте посмотрим, на что​ итоговая сумма продаж​ или на одном​ месяц был получен?​ что эта новомодная​ необходимо создать таблицы​ сводную табличку продаж​Сформируем табличку, которая покажет​ кнопку «Переместить».​ и принятия каких-то​

​ мастера сводных таблиц.​ что дадим ответ​ нажмите​ЗАМЕЧАНИЕ:​

  1. ​ которой никогда не​:​ группировки данных по​ теперь похожа наша​ подсчитывалась для каждого​ из существующих. В​ Ясно, что кто-то​ GUI-платформа с названием​
  2. ​ для расчета на​ по разным отделам​ сумму продаж по​По умолчанию в сводную​ решения может понадобиться​ В новых версиях​ на самый простой​Группировать​Если выделить одну​ было у давних​Окно мастера.
  3. ​Если захотеть, то можно​ дням, месяцам, годам​ сводная таблица. Как​ продавца по отдельности.​ данном примере мы​ совершил огромный труд​ Windows — это​ каждый месяц.​Определение диапазона.
  4. ​ (см. выше).​ отделам. В списке​ таблицу помещается абсолютно​ группировка. Допустим, нам​ Excel он почему-то​Сводный макет.
  5. ​ вопрос: «​(Group);​ ячейку в диапазоне​ пользователей DOS и​ получить сразу сумму,​ и т.д. Давайте​ видите, столбец​

​ Т.е. в нашу​ выберем вариант –​

​ по упорядочиванию и​

Как обновить данные в сводной таблице Excel?

​ всего лишь мимолётное​Первый столбец = первому​

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

  1. ​ полей сводной таблицы​ вся информация из​ нужно увидеть итоги​ спрятано глубоко в​Что же это такое​
  2. ​Появится диалоговое окно​ данных, то Excel​ Lotus 1-2-3.​ среднее и количество​Обновление данных сводной таблицы.
  3. ​ посмотрим, как это​Cash​ сводную таблицу добавятся​New Worksheet​ сортировке всех данных​

​ увлечение, которое долго​ столбцу из сводной​

  1. ​ у нас была​ выбираем названия столбцов,​ того столбца, который​ за месяц или​
  2. ​ настройках:​ – сводные таблицы​
  3. ​Группирование​ автоматически определит диапазон​Урок подготовлен для Вас​ (продаж), размещённые в​

​ делается.​исчез из нее.​ строки с именем​(На новый лист):​ о продажах за​

Некоторые секреты форматирования

​ не протянет. Они​ таблицы. Второй –​ область задач, где​ которые нас интересуют.​ мы добавляем в​ квартал.​Выберите «Файл»-«Параметры»-«Панель быстрого доступа».​ в Excel?​(Grouping) для дат​

​ для создания сводной​ командой сайта office-guru.ru​

  1. ​ одной сводной таблице.​Для начала уберите запись​
  2. ​Очевидно, что сводные таблицы​ каждого продавца компании​Excel создаст новый лист​ последние шесть месяцев​ отказались создавать версию​ формула для расчета​Промежуточный итог.
  3. ​ мы выбирали столбцы​ Получаем итоги по​ отчет.​Группировка.
  4. ​Группировка по дате в​В выпадающем списке левой​» – и далее​ (как показано на​ таблицы и расширит​Источник: http://www.howtogeek.com/howto/13336/working-with-pivottables-in-excel/​Вот пошаговая инструкция, как​Payment Method​ – это очень​ и его итоговой​

​ и разместит на​ и превратил их​ Lotus 1-2-3 для​ вида:​ в списке полей.​ каждому отделу.​В нашем примере –​

Результат поквартальных итогов.

​ сводной таблице Excel:​ колонки: «Выбрать команду​ покажем, как в​ рисунке ниже). В​ выделение. Для того,​

Итоги поквартальной прибыли.

​Перевел: Антон Андронов​ сделать это, начиная​из области​ мощный инструмент, но​ суммой продаж. Чтобы​ нем пустую сводную​ в таблицу итогов,​ Windows (впрочем, только​

Разгруппировать структуру.

Работа с итогами

​= тариф * количество​ Если она исчезла,​

Исходная сводная таблица.

​Просто, быстро и качественно.​ ВСЕ товары, ВСЕ​Источник информации – отчет​ из» укажите «Все​ Excel создать простейшую​

​ поле​ чтобы Excel выбрал​

  1. ​Автор: Антон Андронов​ с пустой сводной​
  2. ​Column Labels​ до сих пор​ достичь такого результата,​ таблицу:​ которую мы видим.​Показывать промежуточные итоги.
  3. ​ несколько лет), предсказывая,​Результат настройки итогов.

​ человек / показания​ просто щелкаем мышью​Важные нюансы:​

​ даты, ВСЕ суммы​ с данными.​ команды».​ сводную таблицу.​

Не показывать промежуточные суммы.

​С шагом​ диапазон правильно, должны​

Итоги без промежуточных сумм.

Детализация информации

​В этом разделе самоучителя​ таблицы:​(Колонны). Для этого​ результаты выглядят как-то​ достаточно перетащить заголовок​Как только мы кликнем​ Сколько, по-вашему, это​ что DOS-версия их​ счетчика / площадь​ по табличке.​Первая строка заданного для​

  1. ​ и магазины. Возможно,​Так как нам нужна​В левой колонке найдите​Далее будет показано, как​(By) выберите​ быть выполнены следующие​ дана пошаговая инструкция,​Перетащите заголовок​ перетащите его обратно​Показать детали.
  2. ​ незамысловато и скучно.​Salesperson​ по любой ячейке​

​ заняло времени? Час?​ программного обеспечения —​Для удобства рекомендуем сделать​Добавим в сводную таблицу​ сведения данных диапазона​

Действия - переместить.

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

Список полей.

​ как создать продвинутую​Salesperson​ к списку заголовков,​ Например, цифры, которые​(Торговый представитель) в​ в сводной таблице,​ Десять часов?​ это всё, что​ промежуточный столбец, в​ еще одно поле​

  1. ​ должна быть заполнена.​ некоторые элементы. Они​ выделяем любую ячейку​ и выделите: «Мастер​Убираем ненужные элементы.
  2. ​ двухмерную сводную таблицу​(Months). Кстати, сгруппировать​Каждый столбец в диапазоне​ сводную таблицу в​(Торговый представитель) в​ а на его​Выберите поле.
  3. ​ мы суммируем, не​ область​ появится ещё одно​Дело в том, что​ когда-либо будет нужно​ который будут заноситься​

​ для отчета. Для​В базовой табличке каждый​

Создание сводной таблицы Excel из нескольких листов

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

​Row Labels​ диалоговое окно:​ таблица, приведённая выше,​ потребителям. Microsoft, естественно,​ показания по счетчикам​ этого установим галочку​ столбец должен иметь​ и мешают сосредоточиться​

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

​ Щелкаем правой кнопкой​ диаграмм». Нажмите на​ заключение мы расскажем,​

Отчет о продажах по филиалам.

​ можно и по​ уникальный заголовок.​ (2007 и более​Column Labels​Date Booked​ в долларах –​(Строки):​PivotTable Field List​ это не сводная​ разработала Excel специально​ (переменная составляющая).​ напротив «Даты» (или​ свой заголовок –​ на главном. Уберем​ мыши.​

​ кнопку между колонками:​ как сортировать сводные​ другим временным периодам,​

  1. ​В диапазоне данных не​ новых). Для тех,​(Колонны).​(Дата бронирования):​
  2. ​ это просто какие-то​Становится интересней! Наша сводная​Опция сводная таблица.
  3. ​(Поля сводной таблицы).​ таблица. Она была​ под Windows.​Наши формулы ссылаются на​ напротив «Товара»). Отчет​ проще настроить сводный​ ненужные элементы.​Из выпавшего меню выбираем​ «Добавить» чтобы инструмент​ таблицы по полям​ например, по кварталам,​ должно быть пустых​ кто работает в​Трижды перетащите заголовок​Как видите, это временно​ цифры. Давайте это​ таблица начинает обретать​Список полей в верхней​ создана вручную из​Во-вторых, Microsoft разработала в​ лист, где расположена​ сразу меняется –​ Ссылка на диапазон листа.Форма сводной таблицы.
  4. ​ отчет.​Нажимаем на стрелочку у​ «Группировку». Откроется инструмент​ переместился в правую​ данных, чтобы легко​ дням, часам и​ строк.​ более ранних версиях​Общий итог по продажам.

​ сделало нашу сводную​

  • ​ исправим.​ форму…​ части диалогового окна​
  • ​ исходных данных, сохранённых​ Excel такой инструмент,​ сводная таблица с​ появляется динамика продаж​В Excel в качестве​
  • ​ названия столбца, где​ вида:​ колонку и нажмите​ извлекать нужную информацию.​ так далее;​

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

​ Excel, рекомендуем статью:​(Стоимость) в область​ таблицу бесполезной. Excel​Велик соблазн сделать привычные​Видите преимущества? За пару​ – это перечень​ где-то ещё, и​ как сводные таблицы,​ тарифами.​

​ по дням в​ источника информации можно​ будем корректировать количество​

​В полях «Начиная с»​ ОК.​

  1. ​ Каждый раздел самоучителя​Нажмите​Сводная таблица​ Как создать продвинутую​Values​ cоздал отдельный столбец​ в такой ситуации​ кликов мы создали​ всех заголовков из​ их обработка заняла​ которого не было​Скачать все примеры сводной​ Другие команды.Настройка мастера.Инструмент в панели быстрого доступа.
  2. ​ каждом отделе.​ использовать таблицы Access,​ информации.​ и «По» Excel​Теперь инструмент находится в​ проиллюстрирован примерами сводных​ОК​(Pivot Table) в​ сводную таблицу в​(Значения).​ для каждой даты,​ действия и просто​Окно мастера шаг 1 из 3.
  3. ​ таблицу, которую вручную​ исходного списка. Четыре​Окно мастера шаг 2 из 3.
  4. ​ минимум пару часов.​ в Lotus 1-2-3.​ таблицы​Сгруппируем данные в отчете​ SQL Server и​Выбираем из выпадающего меню​ автоматически проставил начальную​ панели быстрого доступа,​ таблиц.​Добавляем диапазоны нескольких листов и таблиц.
  5. ​.​ разделе​ Excel 2003?​Для первого поля​ в которую была​ выделить всю таблицу​ пришлось бы создавать​ пустые области в​ Именно такую таблицу​ Сводные таблицы, эксклюзивная​Если при расчете коммунальных​ по месяцам. Для​ др.​Настройка диапазонов.
  6. ​ название поля. В​ и конечную даты​ а значит всегда​Поскольку интерфейс, используемый для​Как и требовалось, наша​Таблицы​В качестве исходных данных​Amount​Отчет по нескольким таблицам.

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

Как работать со сводными таблицами в Excel

​ платежей применяются льготы,​ этого щелкаем правой​​ нашем примере –​ диапазона данных. Определяемся​ под рукой.​

​ создания сводных таблиц​ сводная таблица (смотрите​(Table) на вкладке​ для построения сводной​измените название на​ В итоге мы​ и использовать стандартные​Что ещё мы можем​

​ позволяют указать сводной​ используя сводные таблицы,​ оказалась так ошеломительно​ их тоже можно​ кнопкой мыши по​Часто требуется создавать сводные​ это название товара​ с шагом группировки.​Ставим курсор в любом​ в Excel 2003,​

Редактирование отчета сводной таблицы.

​ картинку ниже) теперь​Вставка​ таблицы, мы используем​Total​ получили очень широкую​ кнопки форматирования чисел​ сделать? Ну, в​ таблице, как требуется​

Результат после редактирования отчета.

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

Настройка отчета по наименованию товаров.

​ показывает итоги продаж​(Insert) Ленты меню​ таблицу данных о​(Сумма), а формат​

Настройка отчета по отделам без даты.

​ таблицу!​ на панели инструментов,​ определённом смысле, наша​ обобщить данные. Пока​

После перестановки полей в отчете.

​ всего лишь несколько​ были склонны осваивать​ Информацию по начислениям​ «Группировать». Выбираем «по​ таблиц. Есть пара​ остановимся на названии.​ – либо месяцы,​ данными. Вызываем мастер​

Перемещение столбцов в отчете.

​ более поздних версий,​ по месяцам с​ Excel.​ продажах компании в​

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

Развернутый детальный отчет.

​ месяцам». Получается сводная​ табличек с информацией.​Устанавливаем фильтр по значению.​

​ либо кварталы. Остановимся​ сводных таблиц, нажимая​ мы создали два​ разбивкой по регионам​Откроется диалоговое окно​ первом квартале 2016​ поле на​

Параметры отчета.

Проверка правильности выставленных коммунальных счетов

​ правой кнопкой мыши​ формат. Проблема такого​ готова. Мы создали​ в таблице тоже​ как…​ Excel, а не​ своей обслуживающей организации.​ таблица такого вида:​ Нужно объединить их​ Исключим из отчета​ на месяцах.​ на соответствующий инструмент,​

​ варианта 2-й и​ и по продавцам.​Создание сводной таблицы​ года.​Accounting​ по любой дате​

​ подхода состоит в​ полезную сводку по​ ничего нет. Всё,​

Тарифы коммунальных платежей.

​Если вернуться к исходному​ продолжать работать в​ Когда меняются тарифы​Чтобы изменить параметры в​ в одну общую.​ информацию по односпальным​Получаем отчет, в котором​ который теперь уже​ 4-й частей данного​

​Чтобы улучшить вид сводной​(Create PivotTable), как​A B C​(Финансовый). Количество десятичных​ и выберите из​

​ том, что если​ исходным данным. Уже​ что от нас​

​ списку продаж, то​ Lotus 1-2-3, в​ – просто измените​ сводной таблице, достаточно​ Для науки придумаем​

Сводная таблица тарифов по коммунальным платежам.

​ кроватям – уберем​ четко видны суммы​ расположенный напанели быстрого​ учебника. Выберите ту,​

​ таблицы, следует настроить​ показано на рисунке​

​ D E 1​ знаков равно нулю.​ контекстного меню пункт​ Вы когда-либо в​ получена важная информация!​ требуется, это перетащить​ он выглядел бы​ котором их не​ данные в ячейках.​ снять галочки напротив​

Для чего нужны сводные таблицы в эксель

С помощью сводной таблицы можно создавать сводки, анализировать, изучать и представлять сводные данные. Сводные диаграммы дополняют сводные таблицы, позволяя визуализировать содержащиеся в них данные. Кроме того, с помощью сводных диаграмм можно легко отображать сравнения, шаблоны и тенденции. И сводные таблицы, и сводные диаграммы помогают принимать взвешенные решения на основании критически важных данных вашей организации. Вы также можете подключаться к внешним источникам данных, например к таблицам SQL Server, кубам служб SQL Server Analysis Services, Azure Marketplace, файлам подключения к данным Office (ODC-файлам), XML-файлам, базам данных Access и текстовым файлам, чтобы создавать сводные таблицы, или создавать на основе сводных таблиц другие таблицы.

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

запрос больших объемов данных различными понятными способами;

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

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

перемещение строк в столбцы или столбцов в строки («сведение») для просмотра различных сводок на основе исходных данных;

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

представление кратких, наглядных отчетов с примечаниями на веб-страницах или в напечатанном виде.

Например, слева показан простой список расходов семьи, а справа — созданная на его основе сводная таблица.

Данные о продажах

Соответствующая сводная таблица

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

Пример сводной таблицы: "Категории" в разделе "Строки" и "Месяцы" в разделе "Столбцы"

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

Изучать данные. Вот какие действия доступны.

Развертывание и свертывание данных, а также отображение базовых сведений, которые относятся к значениям.

Сортировка, фильтрация и группировка полей и элементов.

Изменение функций сведения и добавление настраиваемых вычислений и формул.

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

Изменение формы сводной таблицы: сжатая форма, структура или табличная форма.

Добавление, изменение порядка и удаление полей.

Изменение порядка полей или элементов.

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

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

Отображение промежуточных итогов выше или ниже их строк.

Настройка ширины колонок при обновлении.

Перемещение поля столбца в область строк или поля строки в область столбцов.

Объединение или отмена объединения ячеек для внешних элементов строки и столбца.

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

Изменение способа отображения ошибок и пустых ячеек.

Изменение способа отображения элементов и подписей без данных.

Отображение и скрытие пустых строк.

Изменять формат. Вот какие действия доступны.

Ручное и условное форматирование ячеек и диапазонов.

Изменение стиля общего формата сводной таблицы.

Изменение числового формата для полей.

Включение форматирования сервера OLAP.

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

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

Вот сводная диаграмма, основанная на приведенном выше примере сводной таблицы.

Пример сводной диаграммы Excel

Дополнительные сведения см. в статье Создание сводной диаграммы.

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

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

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

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

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

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

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

Таблицы Excel Таблиц Excel изначально представлены в виде списка и отлично подходят в качестве исходных данных для сводной таблицы. При обновлении сводной таблицы новые и обновленные данные из таблицы Excel автоматически включаются в операцию обновления.

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

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

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

Файлы подключения к данным Office Если вы используете файл подключения к данным Office (ODC-файл) для получения внешних данных для сводной таблицы, вы можете вводить данные непосредственно в нее. Рекомендуется получать внешние данные для отчетов с помощью ODC-файлов.

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

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

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

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

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

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

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

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

Изменение создаваемых кубов OLAP У отчетов, основанных на данных OLAP, всегда есть доступ ко всем данным в кубе. Если вы создали автономный куб, который содержит подмножество данных, имеющихся в кубе сервера, с помощью команды Автономный режим OLAP вы можете изменить файл куба так, чтобы он содержал другие данные с сервера.

Как использовать сводные таблицы Excel в КДП

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

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

Узнаем общую сумму продаж по каждой категории.

Проверим остатки по каждой категории товара и так далее.

Как использовать

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

  • в нем нет объединенных ячеек;
  • нет пустых строк и столбцов;
  • все столбцы имеют заголовки.

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

Создаем базу Excel с помощью функции «Вставка» — «Таблица» — «Сводная таблица».

Получим следующий результат:

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

А теперь добавим типы продаж.

Как сделать вычисления

В отчет можно добавить вычисляемые поля. Для этого необходимо поставить курсор в любую ячейку Еxcel, выбрать вкладку «Анализ» — «Вычисления» — «Поля, элементы и наборы» — «Вычисляемое поле». В появившемся окне зададим имя поля и формулу для вычислений. В нашем случае зарплата составляет 5% от выручки, и формула выглядит следующим образом:

Добавлять и убирать столбцы можно, перетаскивая поля в соответствующую область.

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

Чтобы настроить автоматическое обновление данных при открытии файла, необходимо установить галочку в соответствующем месте (вкладка «Анализ» — «Параметры» — «Данные»).

Удаляем базу, выделив ее и нажав клавишу Delete.

Где применять

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

Расчет KPI

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

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

Теперь эти данные используем для дальнейшего расчета. Сравним плановый показатель с фактическим и вычислим отклонение.

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

Отчет по персоналу

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

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

На основе таких отчетов удобно строить диаграммы для графического отображения информации.

Как построить сводные таблицы в Excel, LibreOffice, OpenOffice и таблицах Google

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

Самый удобный и быстрый инструмент для преобразования массива данных в информативный отчет — сводные таблицы. В основе сводных таблиц лежит технология OLAP, описанная в 1993 году известным исследователем баз данных и автором реляционной модели данных Эдгаром Коддом. По сути, сводная таблица — это инструмент обработки данных для их группировки и обобщения.

Любая сводная таблица строится на основе определенной базы данных. В виде базы данных выступает массив, состоящий из полей и записей. Каждая строка (запись) в базе данных представляет собой информацию об отдельном случае, объекте или состоянии изучаемого объекта, а каждый столбец (поле) является параметром, свойством или признаком всех исследуемых объектов. Например, параметром может быть источник трафика, описание информации об источнике. В поле «источник трафика» будет, например, Google. Все поля базы данных разделяются на два типа: измерения («параметры» в терминологии Google Analytics) и меры («показатели» в терминологии Google Analytics). Измерением — это название или свойство объекта, в разрезе которых мы можем анализировать различные количественные показатели.

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

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

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

Сводные таблицы в Microsoft Excel 2013

Для построения сводной таблицы в Microsoft Excel 2013 вам необходимо открыть скачанный ранее csv файл. После чего, установив курсор на любой из ячеек таблицы, нажмите Ctrl+A — этим действием вы выделите всю базу данных. На её основе мы будем строить сводную таблицу. Далее перейдите на вкладку «Вставка» и в группе «Таблицы» нажмите «Сводная таблица».

Далее перейдите на вкладку «Вставка» и в группе «Таблицы» нажмите «Сводная таблица»

В открывшимся диалоговом окне «Создание сводной таблицы» ничего не меняем и жмем ОК.

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

После создания таблицы в книге Excel будет создан новый лист

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

  • список полей;
  • фильтры;
  • колонны;
  • строки;
  • значения.

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

  1. Поле Date с помощью правой кнопки мыши перетащим в область строк.
  2. Поле Device category перетащим в область колонн.
  3. Поле User type — в область фильтров.
  4. Поле Session — в область значений.

В итоге должно получиться так:

Как работает сводная таблица

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

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

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

Изменение агрегирующей функции

Остается вопрос: а что же нам дал перенос поля User type в фильтры? Чтобы это понять, давайте применим фильтр сводной таблицы и выведем в отчет информацию только по новым пользователям.

  1. Откройте перечень элементов поля User type, нажав на ярлык с изображением воронки в области фильтров сводной таблицы.
  2. Выберите элемент New user.
  3. Нажмите ОК.

Информация только по новым пользователям

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

Перенесем в область строк поле Week таким образом, чтобы оно находилось выше, чем поле Date

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

Отчет сводной таблицы будет сгруппирован не только по датам, но и по неделям

Такие иерархии можно строить и в области колонн. Вы можете смотреть информацию на уровне недель и при необходимости детализировать информацию по количеству сеансов до уровня дат простым нажатием на иконку +/−.

Строить иерархии в области колонн

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

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

Дополнительные вычисления

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

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

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

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

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

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

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

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

В нашем случае формула вычисляемого поля будет выглядеть как «=Bounces / Sessions». Дальше жмем OК.

При написании формулы вычисляемого поля сводной таблицы вы ссылаетесь на название полей сводной таблицы

В область значений отчета сводной таблицы будет добавлено поле «Показатель отказов»:

В область значений отчета сводной таблицы будет добавлено поле «Показатель отказов»

После добавления нового расчетного поля в таблице были автоматически созданы четыре новые колонны. И если сейчас посмотреть на конструктор сводной таблицы, мы увидим, что в область «Колонны» добавлено поле «Значения».

В область «Колонны» добавлено поле «Значения»

Это поле появляется автоматически, если в область «Значения» добавлено более одного поля. Вы можете перенести поле «Значения» в строки или колонны, в область фильтров и значений данное поля перенести нельзя.

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

Перетянем поле «Значения» в область строк под поле Date

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

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

Все, что нам необходимо сделать для группировки измерения, — это выделить нужные его элементы с помощью мыши, после чего вызвать контекстное меню правым кликом мыши и выбрать пункт «Группировать»

Элементы mobile и tablet будут объединены в группу, которой по умолчанию присваивается название «Группа 1». Чтобы переименовать группу, просто перейдите в ячейку с названием и введите новое Other.

Чтобы переименовать группу, просто перейдите в ячейку с названием и введите новое Other

После группировки некоторых элементов измерения область столбцов отчета сводной таблицы стала двухуровневой, так же, как и область строк после добавления в нее поля Week. Теперь вы можете анализировать информацию по типам устройств на двух уровнях, переключаясь между ними с помощью +/−.

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

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

Перейдя на вкладку «Конструктор», выберите один из предложенных стилей оформления

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

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

Перейдите на вкладку «Анализ» и в группе «Фильтры» нажмите иконку «Вставить срез»

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

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

После нажатия ОК на рабочий лист будет добавлен срез. Использовать срез можно так же, как и обычный фильтр. Давайте для примера в созданном нами срезе выберем элемент «google» и тем самым в отчет сводной таблицы выведем информацию о доле каждого типа устройств по каждой дате только по сеансам, совершенным из источника «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», областью сводной таблицы и редактором отчетов.

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

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

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

Давайте пройдем путь построения сводной таблицы, описанный в примере выше

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

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

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

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

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

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

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

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

В редакторе отчетов в область строки добавить поле 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. Чтобы построить сводную таблицу, аналогичную двум предыдущим примерам, сделайте следующие действия.

1. В область «Поля страниц» перетащите поле User type, так как в LibreOffice область «Поля страниц» — это область фильтров сводной таблицы.

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

3. В область «Поля строк» перенесите сначала поле Week, после чего под этим полем расположите поле Date.

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

В открывшемся диалоговом окне «Поле данных» раскройте меню «Отображаемое значение», установите «Тип: % от строки» и нажмите ОК

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

Быстрое изменение уровня детализации

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

Финальная сводная таблица

Заключение

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

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

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

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

Автор и преподаватель курса «Язык R для интернет — маркетинга».

Автор расширений (пакетов) для языка программирования R: rgoogleads, rfacebookstat, rytstat, rym и других.

Автор телеграмм канала R4marketing, который посвящён применению языка R в задачах интернет — маркетинга. Контент канала состоит из новостей, статей, докладов, вебинаров и релизов новых версий пакетов.

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *