Загрузка...Как сделать сводную таблицу, сгруппировать временной ряд
Как сделать сводную таблицу, сгруппировать временной ряд?
Сводная таблица в Excel — это мощнейший инструмент для анализа данных, который поможет вам быстро:
- Подготовить данные для отчетов;
- Рассчитать различные показатели;
- Сгруппировать данные;
- Отфильтровать и проанализировать интересующие показатели.
А также сэкономить вам кучу времени.
Из данной статьи вы узнаете:
- Как сделать сводную таблицу;
- Как с помощью сводной таблицы сгруппировать временные ряды и оценить данные в динамике по годам, кварталам, месяцам, дням.
- Как рассчитать прогноз с помощью сводной таблицы и Forecast4AC PRO;
Содержание
Первое упоминание о сводных таблицах было в книге «Анализ данных в сводных таблицах» авторов Билла Елена и Майка Александера [2] . Основателем термина «сводная таблица» считается Пито Салас. Он разработал первую программу (Lotus Improv), которая помогала пользователям увидеть закономерности в данных электронных таблиц для быстрого построения моделей данных. В этой программе пользователь мог определять и сохранять наборы категорий, затем изменять представление с помощью перетаскивания категории мышкой. Эта особенность, в дальнейшем, превратилась в базовую для сводных таблиц. Lotus Development выпустила программу в 1991 году на платформе NeXT. Несколько месяцев спустя, технология появилась на компьютерах Mac и называлась DataPivot [3] . Компания Borland купила технологию DataPivot в 1992 году и внедрила её в свой проект электронных таблиц (Quattro Pro). В 1993 году, во время того, как появилась версия Improv. Эта функциональность была значительно улучшена в последующих версиях Microsoft Excel:
Редизайн таблицы по сути означает, что данные исходной таблицы копируются и преобразуются так, чтобы сформировать плоский список. Ваши исходные таблицы не изменяются. Чтобы избежать искажения данных, вместо ссылок на ячейки, функций или формул в исходной таблице, в результирующий плоский список надстройка XLTools вставляет их значения.
Термином «Таблица» в Excel часто обозначают разные понятия:
Надстройка XLTools «Редизайн таблицы» позволяет преобразовать в плоский список «настоящие» таблицы и диапазоны. Чтобы произвести редизайн PivotTable, сначала скопируйте диапазон такой таблицы и вставьте значения — это создаст простой диапазон, который далее можно преобразовать.
Дашборд помогает решать задачи менеджерам по продажам, HR-специалистам, бухгалтерам, маркетологам, руководителям
Дашборд ― динамический отчёт, который состоит из структурированного набора данных и их визуализации на основе диаграмм, графиков и таблиц.
Основные задачи дашборда:
- представить набор данных максимально наглядным и понятным;
- держать под контролем ключевые бизнес―показатели;
- находить взаимосвязи, выявлять негативные и положительные тенденции, находить слабые места в организации рабочих процессов;
- давать оперативную сводку в режиме реального времени.
Построение дашбордов ― такой же hard skill, как владение формулами в Excel. По статистике, пользователь Excel среднего уровня может освоить этот навык за 20 часов обучения и практики.
Для специалистов, которые работают с отчётами, навык построения дашбордов стал необходимостью, а не дополнительным преимуществом.
Чаще всего созданием дашборда занимается аналитик — он обрабатывает огромные массивы данных, оформляет их в красивый и понятный дашборд и передаёт заказчику задачи. Это могут быть руководители, менеджеры по продажам, HR-специалисты, бухгалтеры, маркетологи.
Менеджерам по продажам дашборд помогает управлять продажами. HR-специалистам ― отслеживать основные метрики, связанные с трудовыми ресурсами. Для бухгалтера будет полезен дашборд о движении средств, который отражает финансовое состояние организации. Маркетологи анализируют рекламные кампании и оценивают их эффективность. Руководителю дашборд позволит быстро оценивать состояние ключевых показателей и принимать управленческие решения.
Существует большое количество сервисов для бизнес―аналитики, такие как Tableau, Power BI, Qlik, DataLens, Google Data Studio. Самым доступным можно назвать Excel.
Главное и самое интересное в дашборде ― интерактивность.
Настроить интерактивность можно с помощью следующих приёмов:
- срезы и временные шкалы в сводных таблицах ― эти инструменты упрощают фильтрацию данных и позволяют управлять дашбордом: например, можно более детально посмотреть данные по конкретному менеджеру или заказчику за определённый период времени или в разрезе каналов продаж.
- выпадающие списки, формулы и условное форматирование — использование таких приёмов удобно, когда много разных таблиц и построить сводные таблицы невозможно;
- спарклайны, мини-диаграммы в ячейках, тепловые карты в аналитических таблицах — такой способ чаще всего подходит для тактических целей специалистов или аналитиков, а не для стратегических целей руководителя.
Форматирование таблицы
- Если вы сразу же нажмете на кнопку «ОК», то сводная таблица Excel сразу же будет выведена на отдельный лист.
- Полностью настроить выведение.
В последнем случае разработчики дают нам возможность определить диапазон ячеек, в который будет выводиться искомая информация. После этого пользователь должен определить, где конкретно будет создана новая таблица: на уже существующем или же на вновь созданном листе. Нажав «ОК», вы тут же увидите перед собой готовую таблицу. На этом создание сводных таблиц в Excel практически закончено.
В правой части листа расположены те области, с которыми вам предстоит работать. Поля можно перетаскивать в отдельные области, после чего данные из них будут отображены в таблице. Соответственно, в левой части рабочей области будет расположена сама сводная таблица.
Зажав при нажатой левой кнопке мыши поле «Товар», отправляем его в «Название строк», а пункт «Сумма всех продаж» таким же образом переправляем в «Значения» (в левой части листа). Вот так можно получить сумму продаж за весь анализируемый период.
Как группировать даты в сводных таблицах в Excel
Предположим, у вас есть набор данных, как показано ниже:
В нем есть данные о продажах по дате, магазинам и регионам (восток, запад, север и юг). Данные охватывают более 300 строк и 4 столбца.
Вот простая сводка сводной таблицы, созданная с использованием этих данных:
В этой сводной таблице приведены данные о продажах по датам, но она не совсем полезна, поскольку в ней показаны все более 300 дат. В таком случае было бы лучше сгруппировать даты по годам, кварталам и / или месяцам.
Загрузите данные и следуйте инструкциям.
Группировка по годам в сводной таблице
В приведенном выше наборе данных указаны даты за два года (2014 и 2015 годы).
Вот шаги, чтобы сгруппировать эти даты по годам:
- Выберите любую ячейку в столбце «Дата» сводной таблицы.
- Перейдите в Инструменты сводной таблицы -> Анализировать -> Группа -> Выбор группы.
- В диалоговом окне «Группировка» выберите «Годы».
- При группировании дат вы можете выбрать несколько вариантов. По умолчанию опция «Месяцы» уже выбрана. Вы можете выбрать дополнительную опцию вместе с Месяцем. Чтобы отменить выбор месяца, просто щелкните по нему.
- Он выбирает дату "Начало" и "Дата окончания" на основе исходных данных. Если хотите, можете их изменить.
Это резюмирует сводную таблицу по годам.
Такое суммирование по годам может быть полезно, когда у вас больше лет. В этом случае было бы лучше иметь квартальные или ежемесячные данные.
Группировка по кварталам в сводной таблице
В приведенном выше наборе данных имеет смысл перейти к кварталам или месяцам, чтобы лучше понять продажи.
Вот как вы можете сгруппировать их по кварталам:
- Выберите любую ячейку в столбце «Дата» сводной таблицы.
- Перейдите в Инструменты сводной таблицы -> Анализировать -> Группа -> Выбор группы.
- В диалоговом окне «Группировка» выберите «Кварталы» и отмените выбор любых других выбранных опций.
- Щелкните ОК.
Это подытожит сводную таблицу по кварталам.
Проблема с этой сводной таблицей заключается в том, что она объединяет квартальную стоимость продаж за 2014 и 2015 годы. Следовательно, для каждого квартала стоимость продаж представляет собой сумму значений продаж в 1 квартале 2014 и 2015 годов.
В реальной жизни вы, скорее всего, будете анализировать эти кварталы для каждого года отдельно. Сделать это:
- Выберите любую ячейку в столбце «Дата» сводной таблицы.
- Перейдите в Инструменты сводной таблицы -> Анализировать -> Группа -> Выбор группы.
- В диалоговом окне «Группировка» выберите «Кварталы» и «Годы». Вы можете выбрать более одного варианта, просто щелкнув его.
- Щелкните ОК.
Это суммирует данные по годам, а затем по годам по кварталам. Что-то вроде того, что показано ниже:
Примечание. Я использую макет табличной формы на снимке выше.
Когда вы группируете даты более чем по одной временной группе, происходит кое-что интересное. Если вы посмотрите на список полей, вы заметите, что новое поле было добавлено автоматически. В данном случае это годы.
Обратите внимание, что это новое появившееся поле не является частью источника данных. Это поле было создано в сводном кэше для быстрой группировки и суммирования данных. Когда вы разгруппируете данные, это поле исчезнет.
Преимущество этого нового поля заключается в том, что теперь вы можете анализировать данные, используя кварталы в строках и годы в столбцах, как показано ниже:
Все, что вам нужно сделать, это перетащить поле «Год» из области «Строка» в область «Столбцы».
Группировка по месяцам в сводной таблице
Подобно тому, как мы сгруппировали данные по кварталам, мы также можем сделать это по месяцам.
Опять же, для группировки данных рекомендуется использовать и Год, и Месяц, а не использовать только месяцы (если у вас нет данных только за один год или меньше).
Вот как это сделать:
- Выберите любую ячейку в столбце «Дата» сводной таблицы.
- Перейдите в Инструменты сводной таблицы -> Анализировать -> Группа -> Выбор группы.
- В диалоговом окне «Группировка» выберите «Месяцы» и «Годы». Вы можете выбрать более одного варианта, просто щелкнув его.
- Щелкните ОК.
Это сгруппирует поле даты и суммирует данные, как показано ниже:
Опять же, это приведет к добавлению нового поля Годы в поля сводной таблицы. Вы можете просто перетащить поле лет в область столбцов, чтобы получить годы в столбцах, а месяцы — в строках. Вы получите что-то, как показано ниже:
Группировка по неделям в сводной таблице
При анализе таких данных, как продажи в магазинах или посещаемость веб-сайтов, имеет смысл анализировать их еженедельно.
При работе с датами в сводных таблицах группировка дат по неделям немного отличается от группировки по месяцам, кварталам или годам.
Вот как можно группировать даты по неделям:
- Выберите любую ячейку в столбце «Дата» сводной таблицы.
- Перейдите в Инструменты сводной таблицы -> Анализировать -> Группа -> Выбор группы.
- В диалоговом окне «Группировка» выберите «Дни» и отмените выбор любых других выбранных опций. Как только вы это сделаете, вы заметите, что становится доступной опция «Количество дней» (внизу справа).
- Встроенной возможности группировки по неделям нет. Вам нужно сгруппировать по дням и указать количество дней, которые будут использоваться при группировке.
- Обратите внимание, что для того, чтобы это работало, вам нужно выбрать параметр «Только дни».
- В таком случае вы можете начать дату 30 декабря 2013 г. или 6 января 2014 г. (оба понедельника).
Это сгруппирует даты по неделям, как показано ниже:
Точно так же вы можете группировать даты, указав любое другое количество дней. Например, вместо еженедельных дат можно группировать даты с интервалом в две недели.
- Когда вы группируете даты с помощью этого метода, вы не можете сгруппировать их с помощью каких-либо других параметров (например, месяцев, кварталов или лет).
- Вычисляемое поле / элемент не будет работать при группировке с использованием дней.
Группировка по секундам / часам / минутам в сводной таблице
Если вы работаете с большими объемами данных (например, данными колл-центра), вы можете сгруппировать их по секундам, минутам или часам.
Вы можете использовать тот же процесс для группировки данных по секундам, минутам или часам.
Предположим, у вас есть данные колл-центра, как показано ниже:
В приведенных выше данных дата записывается вместе со временем. В этом случае для менеджера центра обработки вызовов имеет смысл проанализировать, как номера разрешения вызовов меняются за час.
Вот как сгруппировать дни по часам:
- Создайте сводную таблицу с датой в области строк и решенным в области значений.
- Выберите любую ячейку в столбце «Дата» сводной таблицы.
- Перейдите в Инструменты сводной таблицы -> Анализировать -> Группа -> Выбор группы.
- В диалоговом окне Группировка выберите Часы.
- Щелкните ОК.
Это сгруппирует данные по часам, и вы получите что-то, как показано ниже:
Вы можете видеть, что метки строк здесь — 09, 10 и так далее… которые являются часами в сутках. 09 означает 9 утра, а 18 — 6 вечера. Используя эту сводную таблицу, вы можете легко определить, что большинство вызовов разрешается в течение 1-2 часов дня.
Точно так же вы можете сгруппировать даты по секундам и минутам.
Вопросы от чайников
Как видно из приведённых примеров, построить таблицу в Экселе очень просто; в заключение будут даны ответы на самые часто встречающиеся вопросы от начинающих пользователей.
Как добавить столбец или строку?
Чтобы добавить строку или столбец в автоматически созданной таблице Excel, следует:
- Для столбца — выделить заголовок правой клавишей мыши и выбрать в открывшемся меню опцию «Столбцы справа/слева».
- Для строки — выделить всю строку, лежащую ниже планируемой, и, аналогичным образом вызвав выплывающее меню, найти параметр «Строки выше».
- Если таблица создавалась вручную, пользователю достаточно будет выделить новый диапазон и, снова раскрыв меню «Границы», придать ему тот же вид, что у ранее созданных. Перенос данных в этом случае осуществляется посредством копирования и вставки.
Как посчитать сумму в таблице?
Чтобы найти сумму данных в таблице Эксель, нужно:
- Выделить соответствующий столбец, перейти на вкладку «Главная» и нажать на кнопку «Автосумма».
- Соответствующие значения могут быть рассчитаны программой для каждого столбца и каждой строки исходной таблицы.
Порядок вычисления суммы в таблицах Excel, созданных автоматически, ничем не отличается от описанного выше.
Как отсортировать данные в таблице?
Для автоматической сортировки сведений, содержащихся в таблице Эксель, следует:
- Выделить мышью столбец, открыть выпадающее меню «Сортировка».
- Далее выбрать один из простых вариантов обработки данных (в возрастающем или убывающем порядке) или, включив «Настраиваемую сортировку», задать в новом окне параметры выборки: номер столбца.
- Параметр упорядочивания.
- И порядок: прямой или обратный.
Важно: в таблице Excel, созданной автоматически, юзер сможет вызвать сортировку непосредственно в контекстных меню столбцов.
Как вставить изображение в таблицу?
Чтобы добавить картинку в таблицу Эксель, потребуется перейти на вкладку «Вставка» и кликнуть по кнопке «Рисунки», после чего выбрать требуемый файл.
Пользователь, решивший создать таблицу своими силами, сможет отрегулировать размер, положение и другие параметры рисунка, выделив его щелчком мыши и автоматически перейдя на вкладку «Формат».
