Как в экселе сгруппировать дни в недели
Обновлено: 07.07.2024
Я продолжаю знакомить вас со сводной таблицей в Excel, и сегодня во второй части расскажу как группировать данные по времени, а также как разгруппировать их. Буквально вчера я подробно расписал как это сделать по дате, конкретно по году, кварталу и месяцу. Если вы ее пропустили, почитать можно здесь:
Группировка по секундам / часам / минутам в сводной таблице
Итак.. Если вы работаете с большими объемами данных (например, данными колл-центра), вы можете сгруппировать их по секундам, минутам или часам. Предположим, у вас есть данные колл-центра, как показано ниже:
В приведенных выше данных дата записывается вместе со временем. В этом случае вы можете проанализировать, например, в какое время суток поступило наибольшее количество звонков.
Вот как сгруппировать данные по часам:
- Создайте сводную таблицу с датой и количеством поступивших звонков.
- Выберите любую ячейку в столбце с датой сводной таблицы.
- Перейдите в Параметры сводной таблицы -> Раздел "группировать" -> Группировка по полю
- Выберите шаг по "часам"
Это сгруппирует данные по часам, и вы получите что-то, как показано ниже:
Вы можете заметить, что метки строк здесь - 09, 10 и т. д., Которые являются часами в сутках. 09 означает 9 утра, а 18 - 6 вечера. Используя эту сводную таблицу, вы можете легко определить, что большинство вызовов совершается в течение от 13 до 14 часов дня.
Точно так же вы также можете сгруппировать даты по секундам и минутам.
Как разгруппировать даты в сводной таблице в Excel
Чтобы разгруппировать даты в сводных таблицах:
- Выберите любую ячейку в ячейках даты в сводной таблице.
- Перейдите в Параметры сводной таблицы -> Раздел "группировать" -> Разгруппировать
Это мгновенно разгруппирует любую сделанную вами группировку.
На этом у меня всё. Если вам понравился сегодняшний урок, ставьте лайки 👍 👍 👍 и подписывайтесь на канал чтобы не пропустить еще более интересные материалы. Если хотите посмотреть еще уроки загляните в СОДЕРЖАНИЕ 👈 , обязательно еще что-нибудь присмотрите )) Спасибо!
Когда мы группируем дату в сводной таблице, мы можем группировать по месяцу, дню, кварталу и году. В некоторых случаях нам нужно сгруппировать данные по неделям, как бы вы могли решить эту проблему? Здесь я расскажу вам о некоторых простых трюках.
Вкладка Office позволяет редактировать и просматривать в Office с вкладками и значительно упрощает работу . Kutools for Excel решает большинство ваших проблем и увеличивает вашу производительность на 80%- Повторное использование чего угодно: Добавляйте наиболее часто используемые или сложные формулы, диаграммы и все остальное в избранное и быстро используйте их в будущем.
- Более 20 текстовых функций: Извлечь число из текстовой строки; Извлечь или удалить часть текстов; Преобразование чисел и валют в английские слова.
- Инструменты слияния : Несколько книг и листов в одну; Объединить несколько ячеек / строк / столбцов без потери данных; Объедините повторяющиеся строки и сумму.
- Разделить инструменты : Разделение данных на несколько листов в зависимости от ценности; Из одной книги в несколько файлов Excel, PDF или CSV; От одного столбца к нескольким столбцам.
- Вставить пропуск Скрытые / отфильтрованные строки; Подсчет и сумма по цвету фона ; Отправляйте персонализированные электронные письма нескольким получателям массово.
- Суперфильтр: Создавайте расширенные схемы фильтров и применяйте их к любым листам; Сортировать по неделям, дням, периодичности и др .; Фильтр жирным шрифтом, формулы, комментарий .
- Более 300 мощных функций; Работает с Office 2007-2019 и 365; Поддерживает все языки; Простое развертывание на вашем предприятии или в организации.
Группировка по неделям в сводной таблице с установкой 7 дней в неделю
Удивительный! Использование эффективных вкладок в Excel, таких как Chrome, Firefox и Safari!
Экономьте 50% своего времени и сокращайте тысячи щелчков мышью каждый день!
Чтобы сгруппировать данные по неделям, вы можете установить 7-дневную дату с понедельника по воскресенье как неделю, сделайте следующее:
1. После создания сводной таблицы щелкните правой кнопкой мыши любую дату в сводной таблице и выберите группы из контекстного меню см. снимок экрана:
2. В группирование диалог, пожалуйста, сделайте следующие опции:
A: Под Auto в разделе введите дату начала одной недели. В этом примере моя первая дата в моей таблице - 2014-3-12, но это не первый день недели, поэтому я меняю ее на 2014-3-10, то есть первый день недели, начиная с понедельника. . И дату окончания изменять не нужно;
B: Выберите Дней из By список;
C: Войти 7 в Количество дней пунктом.
3, Затем нажмите OK, и ваши данные были сгруппированы по неделям, как вы хотите, см. снимок экрана:
Группировка по неделям в сводной таблице со вспомогательным столбцом
В Excel вы также можете добавить вспомогательный столбец для вычисления номера недели относительно даты, а затем выбрать диапазон данных, включая это поле, для создания сводной таблицы. Пожалуйста, сделайте следующие шаги:
1. Сначала вычислите номера недель до даты, введите эту формулу: = ГОД (D2) & "-" & ТЕКСТ (WEEKNUM (D2,2), "00") в пустую ячейку рядом с вашими данными, (D2 указывает ячейку даты, которую вы хотите использовать), см. снимок экрана:
2. Затем перетащите маркер заполнения по диапазону, в котором вы хотите применить эту формулу, и номера недель были заполнены в этом вспомогательном столбце, см. Снимок экрана:
3. Затем выберите весь диапазон данных, включая поле, которое вы добавили на шаге выше, которое вы хотите использовать для создания сводной таблицы, и нажмите Вставить > PivotTable > PivotTable, см. снимок экрана:
4. В Создать сводную таблицу диалоговом окне выберите новый лист или диапазон текущего листа, чтобы разместить сводную таблицу под Выберите, где вы хотите разместить отчет сводной таблицы .
5. Нажмите OK, затем перетащите поля в соответствующий список полей и поместите Неделя поле вверху в заголовки строк раздел, и вы можете видеть, что данные были сгруппированы по неделям, см. следующий снимок экрана:
Сгруппировать даты по дням / месяцам / годам в сводной таблице в Excel легко, но знаете ли вы, как сгруппировать даты по дням недели в сводной таблице, как показано на скриншотах ниже? Эта статья поможет вам закончить это.
Сгруппируйте сводную таблицу по дням недели в Excel
На самом деле сводная таблица не поддерживает групповые даты по дням недели. Однако мы можем добавить вспомогательный столбец о дне недели в исходные данные, а затем сгруппировать даты по вспомогательному полю в сводной таблице. Пожалуйста, сделайте следующее:
1. Вставьте пустой столбец в исходные данные, введите День недели в качестве имени столбца введите формулу = ТЕКСТ (A2, «ddd») в первую ячейку вспомогательного столбца и, наконец, перетащите дескриптор автозаполнения вниз, чтобы заполнить весь столбец. Смотрите скриншот:
2. Выберите исходные данные и нажмите Вставить > PivotTable.
3. В диалоговом окне «Создание сводной таблицы» выберите целевой диапазон для размещения сводной таблицы и щелкните значок OK кнопку.
4. На панели полей сводной таблицы перетащите День недели поле к Ряды раздел и перетащите ПРОДАЖИ поле к Ценности раздел. Смотрите скриншот:
 |  |
И теперь вы увидите, что даты автоматически сгруппированы по дням недель в новой созданной сводной таблице. Смотрите скриншот выше.
Внимание: Если вы хотите сгруппировать даты по месяцам и дням недели, пожалуйста, измените сводную таблицу:
(1) На панели полей сводной таблицы перетащите Время поле вверху Ряды .
(2) В сводной таблице щелкните правой кнопкой мыши любую дату в столбце «Ярлыки строк» и выберите группы в контекстном меню. Затем в диалоговом окне Группировка щелкните, чтобы выделить Месяцев только в By список и щелкните OK кнопка. Смотрите скриншот:
Теперь вы увидите, что даты сгруппированы по месяцам и дням недель в новой созданной сводной таблице. Смотрите скриншот:
Сгруппируйте сводную таблицу по дням недели с помощью замечательного инструмента
Если у вас есть Kutools for Excel После установки вы можете применить его инструмент группировки по специальному времени сводной таблицы, чтобы быстро создать сводную таблицу и автоматически сгруппировать данные сводной таблицы по дням недели.
Kutools for Excel - Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная 30-дневная пробная версия, кредитная карта не требуется! Бесплатная пробная версия сейчас!
1. Выберите данные, на основе которых вы создадите сводную таблицу, и нажмите Kutools Plus > Сводная таблица > Группировка по особому времени сводной таблицы.
2. В диалоговом окне «Группировка по особому времени сводной таблицы» выполните следующие действия:
(1) Отметьте День недели опция в списке «Группировать по»;
(2) Укажите столбец даты, из которого вы будете получать данные дня недели;
(3) Укажите место, в котором вы разместите новую сводную таблицу.
3, Нажмите Ok кнопка. Затем создается сводная таблица со столбцом дня недели.
4. Продолжайте, чтобы завершить построение сводной таблицы: перетащите ПРОДАЖИ поле и опустите его в Ценности .
Затем вы увидите, что данные о продажах добавлены в сводную таблицу и сгруппированы по дням недели. Смотрите скриншот:
Легко группирует ячейки и вычисляет значения в другом столбце Excel
С Kutools для Excel Расширенные ряды комбинирования функция, вы можете быстро сгруппировать все ячейки одного столбца на основе значений в другом столбце или легко вычислить (сумма, количество, среднее, максимальное и т.д.) эти ячейки по значениям в другом столбце! Полнофункциональная бесплатная 30-дневная пробная версия!
Статьи по теме:
Группировка по диапазону в сводной таблице Excel
Если вы укажете поле даты как метку строки в сводной таблице, вы можете легко сгруппировать даты по неделям, месяцам, кварталам и т. Д. Но, если вы укажете число, указанное в качестве метки строки, можете ли вы сгруппировать по указанному диапазону? Конечно да! Эта статья поможет вам сгруппировать по диапазону в сводной таблице Excel.
Группировать по неделям в сводной таблице
Когда мы группируем дату в сводной таблице, мы можем группировать по месяцу, дню, кварталу и году. В некоторых случаях нам нужно сгруппировать данные по неделям, как бы вы могли решить эту проблему? Здесь я расскажу вам о некоторых простых трюках.
Группируйте данные по полугодиям в сводной таблице Excel
Как мы знаем, сводная таблица может помочь нам более эффективно анализировать данные, а также может группировать данные по годам / месяцам / дням. Однако пробовали ли вы сгруппировать данные по полугодиям, как показано на скриншоте ниже в сводной таблице Excel?
Группировка времени по часам в сводной таблице Excel
Например, у меня есть таблица, как показано на скриншоте ниже. Теперь мне нужно создать сводную таблицу и сгруппировать данные времени по часам в сводной таблице, есть идеи? Эта статья подробно расскажет вам решение!
Диалоговое окно Группирование (Grouping) предлагает настройки группировки по секундам, минутам, часам, дням, месяцам, кварталам и годам. А что делать, если нужно сгруппировать данные по одной или двум неделям? Это вполне реально.
Прежде всего следует свериться с бумажным календарем данного года. Рабочие даты могут начинаться с 4 января 2010 года, причем важно знать, что в этом году 4 января пришлось на понедельник. Вы решаете, с какого дня должна начинаться неделя: с воскресенья, понедельника или любого другого дня. Сверьтесь с календарем. Ближайший понедельник был 4 января 2010 года.
Выделите любой заголовок дат в сводной таблице. Перейдите на контекстную вкладку ленты Параметры (Options) и в разделе Группировать (Group) щелкните на кнопке Группировка по полю (Group Field). В диалоговом окне Группирование (Grouping) отмените выделение всех параметров в списке с шагом (By), за исключением параметра Дни (Days). В результате станет доступным счетчик количество дней (Number of Days). Чтобы создать недельный отчет, увеличьте количество дней с 1 до 7.
И наконец, следует установить флажок начиная с (Starting At) и определить дату. Если вы приняли настройки по умолчанию и начали с 4 января 2010 года, то все недельные периоды в отчете будут начинаться с понедельника и заканчиваться воскресеньем. Эти настройки нас вполне устраивают (рис. 4.6), поэтому оставим все без изменений.
Рис. 4.6. Для получения доступа к счетчику количество дней в списке с шагом выберите значение Дни
В результате выполнения перечисленных выше действий генерируется отчет, отображающий еженедельные объемы продаж, как показано на рис. 4.7.
Рис. 4.7. Отчет, отображающий еженедельные объемы продаж
Если вы решили выполнить группировку данных по неделям, иные настройки группировки будут недоступны. Вы не сможете сгруппировать текущее или любое другое поле по месяцам или кварталам.
Группирование двух полей дат в один отчет
При группировке поля дат по месяцам и годам программа Excel переназначает исходное поле дат для отображения месяцев и добавляет новое поле для отображения лет. Новое поле получает имя Годы. Все довольно просто, если у вас в отчете представлено только одно поле дат.
Если вам нужно создать отчет с двумя полями дат и вы пытаетесь сгруппировать оба поля по месяцам и годам, программа Excel сама установит первому сгруппированному полю имя Годы, а второму — имя Годы2. Это неминуемо приведет к конфликту данных. В подобном случае важно правильно переименовать поля.
Возможность быстро группировать даты в сводных таблицах 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
Чтобы разгруппировать даты в сводных таблицах:
![]()
- Выберите любую ячейку в ячейках даты в сводной таблице.
- Перейдите в Инструменты сводной таблицы -> Анализировать -> Группировать -> Разгруппировать.
Это мгновенно разгруппировало бы любую сделанную вами группировку.
Загрузите файл примера.
Другие учебные пособия по сводным таблицам, которые могут вам понравиться:
Читайте также:
