Как сделать график продаж в excel по месяцам, числам и дням недели
Обновлено: 02.07.2024
Сгруппировать даты по дням/месяцам/годам в сводной таблице Excel легко, но знаете ли вы, как сгруппировать даты по дням недели в сводной таблице, как показано на снимках экрана ниже? Эта статья поможет вам закончить его.
Группировка сводной таблицы по дням недели в Excel
На самом деле сводная таблица не поддерживает группировку дат по дням недели. Однако мы можем добавить вспомогательный столбец о дне недели в исходные данные, а затем сгруппировать даты по вспомогательному полю в сводной таблице. Пожалуйста, сделайте следующее:
<р>2. Выберите исходные данные и нажмите «Вставить» > «Сводная таблица».
<р>4. На панели «Поля сводной таблицы» перетащите поле «День недели» в раздел «Строки», а поле «Продажи» — в раздел «Значения». Смотрите скриншот:
 |  |
И теперь вы увидите, что даты сгруппированы по дням недели в новой созданной сводной таблице автоматически. См. снимок экрана выше.
Примечание. Если вы хотите сгруппировать даты по месяцам и дням недели вместе, измените сводную таблицу:
(1) На панели "Поля сводной таблицы" перетащите поле "Дата" в верхнюю часть раздела "Строки".
(2) В сводной таблице щелкните правой кнопкой мыши любую дату в столбце «Метки строк» и выберите «Группировать» в контекстном меню. Затем в диалоговом окне «Группировка» выберите «Только месяцы» в списке «По» и нажмите кнопку «ОК». Смотрите скриншот:
Теперь вы увидите, что даты сгруппированы по месяцам и дням недели в новой созданной сводной таблице. Смотрите скриншот:
Сгруппируйте сводную таблицу по дням недели с помощью замечательного инструмента
Если у вас установлен Kutools for Excel, вы можете применить его инструмент группировки по времени сводной таблицы, чтобы быстро создать сводную таблицу и автоматически сгруппировать данные сводной таблицы по дням недели.
Kutools for Excel — включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная пробная версия на 30 дней, кредитная карта не требуется! Бесплатная пробная версия сейчас!
Kutools for Excel — объединяет более 300 расширенных функций и инструментов для Microsoft Excel
(1) установите флажок "День недели" в списке "Группировать по";
(2) Укажите столбец даты, из которого вы получите данные о дне недели;
(3) Укажите место, где вы разместите новую сводную таблицу.
<р>4. Завершите построение сводной таблицы: перетащите поле Продажи в раздел "Значения".
Затем вы увидите, что данные о продажах добавляются в сводную таблицу и сгруппированы по дням недели. Смотрите скриншот:
Легко группирует ячейки и вычисляет по значениям в другом столбце в Excel
С помощью Kutools for Excel's Advanced Combine Rows вы можете быстро сгруппировать все ячейки одного столбца на основе значений в другом столбце или вычислить (сумма, количество, среднее, максимальное и т. д.) эти ячейки по значениям в другом столбце. вольно! Полнофункциональная бесплатная пробная версия на 30 дней!
Статьи по теме:
Группировка по финансовому году в сводной таблице Excel.
Например, финансовый год в вашей компании/организации начинается в июле, теперь вы хотите создать сводную таблицу и сгруппировать в ней даты по финансовому году, как Вы могли бы справиться с этим? Это решение может облегчить вашу работу.
Группировка по диапазону в сводной таблице Excel.
Если указать поле даты как метку строки в сводной таблице, можно легко сгруппировать даты по неделе, месяцу, кварталу и т. д. Но если указать число подается как метка строки, не могли бы вы сгруппировать по указанному диапазону? Да конечно! Эта статья поможет вам сгруппировать по диапазону в сводной таблице Excel.
Группировка по неделям в сводной таблице
Когда мы группируем дату в сводной таблице, мы можем группировать по месяцам, дням, кварталам и годам. В определенном случае нам нужно сгруппировать данные по неделям, как бы вы справились с этой проблемой? Здесь я расскажу вам о некоторых быстрых хитростях.
Группировка данных по полугодиям в сводной таблице Excel
Как известно, сводная таблица может помочь нам более эффективно анализировать данные, а также может группировать данные по годам/месяцам/дням. Однако пытались ли вы когда-нибудь сгруппировать данные по полугодиям, как показано ниже на снимке экрана в сводной таблице Excel?
Сгруппировать время по часам в сводной таблице Excel
Например, у меня есть таблица, показанная на снимке экрана ниже. Теперь мне нужно создать сводную таблицу и сгруппировать данные о времени по часам в сводной таблице, есть идеи? Эта статья покажет вам решение в деталях!
На приведенном ниже снимке экрана показаны данные о продажах компании, для которой необходимо создать еженедельный отчет.
ШАГ 1. Создайте новый вспомогательный столбец, в котором будут указаны номера недель для дат.
ШАГ 2. Используйте формулу – НОМЕР НЕДЕЛИ. Он просто вернет номер недели указанной даты. По умолчанию Excel считает, что неделя начинается с воскресенья.
Введите формулу: =WEEKNUM(E2)
ШАГ 3. Этот отчет содержит несколько лет. Чтобы получить точные результаты, номер недели следует сочетать с годом.
Чтобы объединить номер года и недели, используйте формулу: =G2&”-“&WEEKNUM(E2,1)
ШАГ 4. Скопируйте и вставьте приведенную ниже формулу.
ШАГ 5. Выберите «Вставка» > «Сводная таблица».
ШАГ 6. В диалоговом окне "Создание сводной таблицы" нажмите "ОК".
ШАГ 7. Перетащите номер недели в поле "Строка" и "Продажи" в поле "Значения".
Это предоставит вам еженедельный отчет о продажах в Excel с использованием формулы Weeknum.
Вы также можете использовать функцию группового выбора в сводных таблицах для группировки Excel по неделям. Давайте углубимся в этот метод и посмотрим, как он работает.
Использование группового выбора
В прошлый раз мы узнали о группировке Excel по неделям с помощью сводных таблиц. Но что, если ваш начальник хочет, чтобы все недели начинались с понедельника?
Вы бы изо всех сил пытались найти способ, чтобы все недели начинались с понедельника!
Но вот в чем дело: настроить запуск по понедельникам очень просто!
В приведенных ниже примерах показано, как сгруппировать Excel по неделям, начиная с любого выбранного вами дня.
ПРИМЕР 1:
Следуйте приведенному ниже пошаговому руководству о том, как сгруппировать даты по неделям в Excel:
ШАГ 1. Вставьте новую сводную таблицу, нажав на свои данные и выбрав Вставка > Сводная таблица > Новый рабочий лист или Существующий рабочий лист
ШАГ 2. В разделе ROWS укажите значение в поле Дата заказа.
Обратите внимание, что в Excel 2016 даты автоматически группируются по годам и кварталам.
ШАГ 3. Щелкните правой кнопкой мыши любую запись в ярлыках строк сводной таблицы и выберите "Группировать"
ШАГ 4. Обратите внимание, что он смог определить минимальную и максимальную даты. Однако нам нужно, чтобы наши недели начинались в понедельник.
Наша дата начала — 3 января 2012 г. Давайте быстро проверим наш календарь:
Мы видим, что ближайший понедельник перед этой датой — 2 января 2012 г.
Отмените выбор месяцев, кварталов и лет и убедитесь, что выбраны только дни (обозначены синим цветом).
Установите количество дней равным 7.
Установите дату начала 2012-01-02.
Это сгруппирует Excel по неделям (каждые 7 дней, начиная с понедельника).
Нажмите "ОК".
ШАГ 5. В области ЗНАЧЕНИЯ введите значение в поле Продажи.
Это позволит получить общее количество продаж за каждый определенный вами 7-дневный диапазон дат:
Это создаст сводную таблицу Excel по неделям!
ШАГ 6. Теперь мы можем преобразовать наши данные о продажах в нечто более презентабельное.
Нажмите "Сумма ПРОДАЖ" и выберите "Настройки поля значения".
ШАГ 7. Выберите числовой формат
ШАГ 8. Выберите валюту. Нажмите "ОК".
Теперь у вас есть общий объем продаж за каждый 7-дневный период, начиная с понедельника!
Давайте посмотрим на другой пример, в котором вы можете группировать Excel по неделям, начиная с воскресенья!
ПРИМЕР 2:
В приведенном ниже примере представлены данные о продажах, включая дату продажи, название продукта и цену продажи.
ШАГ 1. Выберите «Вставка» > «Сводная таблица».
ШАГ 2. В диалоговом окне "Создать сводную таблицу" выберите диапазон таблицы и параметр "Новый рабочий лист" и нажмите "ОК".
ШАГ 3. Структура сводной таблицы будет создана на новом листе, и вы сможете использовать поля сводной таблицы для создания отчета.
ШАГ 4. Перетащите дату в поле "Строка" и цену в поле "Значения".
Вот как будет выглядеть сводная таблица:
ШАГ 5. Выберите любую ячейку в столбце «Дата», а затем выберите «Анализ сводной таблицы» > «Выбор группы».
ШАГ 6. В диалоговом окне "Группировка" выберите "Дни" (отмените выбор любого другого выделенного параметра) и введите 7 в разделе количества дней.
Это сгруппирует сводную таблицу по семидневному периоду.
Ваш еженедельный отчет о продажах, начиная с воскресенья, готов!
Точно так же вы можете группировать даты по месяцам, годам, часам, секундам и т. д.
Разгруппировать данные
Кроме того, вы можете легко удалить эти группы, созданные одним из двух способов, указанных ниже:
- Способ 1. Щелкните правой кнопкой мыши столбец даты и выберите "Разгруппировать".
- Способ 2. Перейдите в раздел "Анализ сводной таблицы" и нажмите "Разгруппировать".
Заключение
Вы можете легко анализировать данные по неделям, месяцам, годам, дням, часам и т. д. и находить тенденции, используя формулу НОМЕР НЕДЕЛИ или функцию группировки Excel по неделям в сводной таблице.
Это довольно простые и очень быстрые способы группировки дат.
Знаете ли вы, что в сводных таблицах Excel существует множество креативных способов группировки? Нажмите здесь, чтобы узнать все об этом здесь!
В сводных таблицах есть несколько полезных «скрытых» функций, которые еще больше упрощают интерпретацию данных. Одна из таких функций позволяет просматривать данные по группам дат (например, месяц, квартал, даже неделя и час дня). Группируя в самой сводной таблице, вы избегаете постоянного изменения исходных данных и создания нескольких сводных таблиц на основе одних и тех же данных.
Чтобы использовать функцию группировки в сводной таблице, создайте или откройте сводную таблицу, содержащую данные, упорядоченные по дате. Шаги, показанные ниже, применимы к Excel 2007 и более поздним версиям. Изображения в этой статье были сделаны с помощью Excel 2013 в ОС Windows 7. Чтобы использовать наш пример данных, загрузите файл Group PivotTables by Month.xlsx
.Шаг 1. Сгруппируйте сводную таблицу по месяцам
Щелкните правой кнопкой мыши любую ячейку в столбце "Даты" и выберите "Группа" в раскрывающемся списке. Затем выберите Месяц в диалоговом окне. Используя поля Начало в: и Окончание в:, вы даже можете указать диапазон дат, которые вы хотите сгруппировать, если вы не хотите группировать весь список.
Шаг второй. Сгруппируйте сводную таблицу по месяцам и годам
Обратите внимание, что в нашем примере есть данные за три года. Использование месяца в качестве фильтра объединяет данные о продажах за каждый месяц в одну строку, представляющую сумму всех трех лет. Мы можем использовать эту информацию, чтобы определить сезонные тенденции продаж в течение года на основе данных за несколько лет.
Однако, если вы хотите увидеть линейные тренды за все три года, просто добавьте Годы к своему выбору в диалоговом окне Группировки. Когда вы добавляете более одной группы, вы можете свернуть и развернуть группы, щелкнув значок + или – рядом с группой:
Шаг 3. Сгруппируйте сводную таблицу по неделям
В диалоговом окне "Группировка" нет выбора "Неделя". Чтобы упорядочить данные по неделям, выберите «Дни», затем введите 7 в поле «Количество дней:»:
Подсказка! Установите дату начала недели!
В нашем примере вы можете заметить, что первая неделя – период с 4 по 10 июля, который в 2013 году приходился на четверг-среду. Это происходит потому, что наша первая зарегистрированная дата — 4 июля. Чтобы показать традиционную рабочую неделю с воскресенья по субботу в ваших группах, вручную установите в поле «Начало в» воскресенье, близкое к вашей первой зарегистрированной дате, например 30 июня:
Шаг четвертый. Отображение итогов по разнице
После того как вы отсортировали информацию по месяцам, вы можете узнать что-то новое о своих данных, например, как ежемесячно меняются ваши общие продажи. Ваша сводная таблица может отображать ежемесячные объемы продаж с точки зрения разницы с предыдущим месяцем.
Чтобы отобразить итоговые значения «Разница с», щелкните любое число в столбце, который вы хотите изменить, в данном случае «Сумма суммы заказа», затем щелкните Параметры поля «Значение» в группе «Активное поле» на вкладке «Анализ».
На вкладке «Показать значения как» в диалоговом окне выберите «Отличие от» в раскрывающемся меню «Показать значения как» и (предыдущее) в поле выбора базового элемента. Теперь ваша сводная таблица покажет, как каждый продавец работал с течением времени, указав разницу в продажах по сравнению с предыдущим месяцем.
Группировка данных в сводной таблице, особенно данных на основе дат, позволяет сочетать аналитические возможности сводной таблицы со знакомыми инструментами, которые делают чтение результатов гораздо более полезным.
В приведенных ниже данных видно, что есть два столбца: один содержит дату транзакции продажи, а второй столбец содержит общую сумму продаж на определенную дату.
Хотите узнать, как группировать даты в сводной таблице по месяцам?
*** Посмотрите наше видео и пошаговое руководство ниже по группировке дат в сводной таблице по месяцам с помощью бесплатной загружаемой книги Excel для практики ***
В приведенном ниже примере показано, как сгруппировать сводную таблицу по месяцам:
ШАГ 1. Вставьте новую сводную таблицу, нажав на свои данные и выбрав Вставка > Сводная таблица > Новый рабочий лист или Существующий рабочий лист
ШАГ 2. В разделе ROWS укажите значение в поле Дата заказа.
Обратите внимание, что в Excel 2016 (версия, которую я использую) он автоматически группирует Дату заказа в годы и кварталы:
ШАГ 3. Щелкните правой кнопкой мыши любую строку в сводной таблице и выберите "Группа", чтобы мы могли выбрать нужный порядок группировки:
ШАГ 4. Нам нужно отменить выбор кварталов и убедиться, что выбраны только месяцы и годы (которые будут выделены синим цветом).
Даты будут сгруппированы по месяцам и годам. Нажмите "ОК".
ШАГ 5: В области ЗНАЧЕНИЙ введите значение в поле Продажи. Это позволит получить общее количество продаж за каждый месяц и год:
Вот как вы можете легко создавать групповые даты сводной таблицы по месяцам!
Группировка дат в сводной таблице по неделям
Чтобы сгруппировать даты по неделям, выполните следующие действия:
ШАГ 1. Щелкните правой кнопкой мыши одну из дат и выберите "Группировать".
ШАГ 2. Выберите параметр дня из списка и отмените выбор других параметров.
ШАГ 3. В разделе "Количество дней" введите 7.
Так будут отображаться групповые даты в сводной таблице по неделям.
ШАГ 4. Вы даже можете изменить дату начала на 01-01-2012 в разделе ниже.
Ваши окончательные сгруппированные данные готовы!
Изменить формат
Теперь у нас есть данные о продажах, сгруппированные по месяцам и годам. Обратите внимание, что мы можем улучшить форматирование, выполнив следующие действия:
ШАГ 1. Нажмите на сумму ПРОДАЖ и выберите Настройки поля значения
ШАГ 2. Выберите числовой формат
ШАГ 3. Выберите валюту. Нажмите "ОК".
Теперь у вас есть общий объем продаж за каждый месячный период! Быстро и легко!
Обобщить значение
В предыдущих примерах вы видели, как получить общий объем продаж по месяцам, годам или неделям. Вы даже можете рассчитать общее количество продаж за определенный месяц, год или неделю.
Давайте рассмотрим пример, чтобы узнать, как это сделать:
ШАГ 1. Щелкните правой кнопкой мыши в любом месте сводной таблицы.
ШАГ 2. Выберите из списка Настройки поля значения.
ШАГ 3. В диалоговом окне "Настройка поля значения" выберите "Число".
ШАГ 4. Нажмите "ОК".
Это суммирует значения как количество продаж, а не сумму продаж (как раньше).
Разгруппировать даты
Чтобы разгруппировать даты в сводной таблице, просто щелкните правой кнопкой мыши столбец дат и выберите "Разгруппировать".
Или вы можете перейти на вкладку Анализ сводной таблицы и выбрать Разгруппировать.
После этого данные снова будут разгруппированы.
Управление автоматической группировкой
При желании вы можете легко отключить эту функцию автоматической группировки дат в Excel 2016. Для этого выполните следующие действия:
ШАГ 1. Перейдите на вкладку «Файл» > «Параметры»
ШАГ 2. В диалоговом окне "Параметры Excel" нажмите "Данные" в категориях слева.
ШАГ 3. Установите флажок Отключить автоматическую группировку столбцов даты и времени в сводных таблицах.
ШАГ 4. Нажмите "ОК".
Это легко отключит функцию автоматической группировки в сводной таблице! Таким образом, дата теперь не будет автоматически группироваться при перетаскивании поля даты в область сводной таблицы.
Заключение
Вы можете легко анализировать данные по неделям, месяцам, годам, дням, часам и т. д. и находить тенденции с помощью этой функции группировки дат в сводной таблице. Это довольно простой и очень быстрый способ сгруппировать даты.
Знаете ли вы, что в сводных таблицах Excel существует множество креативных способов группировки?
Читайте также: