Даты не сгруппированы в Excel

Обновлено: 20.11.2024

У меня есть таблица Excel, заполняемая пользовательской формой, созданной в VBA. Большая часть данных в таблице была там до создания пользовательской формы. Пользовательская форма была создана, чтобы сделать ввод данных более удобным для пользователя. Одно из текстовых полей в пользовательской форме — это дата, которая заполняется в столбце A. Столбец A отформатирован как краткая дата. Я создал сводную таблицу для отображения данных и смог сгруппировать даты по годам и месяцам. Но когда я добавляю новые данные в таблицу, даты не группируются. Например, в раскрывающемся списке фильтров я могу выбрать 2018, 2019, 2020 годы, а также любые новые данные, которые я добавил туда, даты также отображаются как 9/10/2020, не сгруппированные по году, как я ожидаю. Также я теряю группу по году и месяцу на своих слайсерах. Почему добавленные новые даты не группируются?

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

Сообщить о нарушении

Домогательство – это любое поведение, направленное на то, чтобы побеспокоить или расстроить человека или группу людей. К угрозам относятся любые угрозы самоубийства, насилия или причинения вреда другому человеку. Любой контент на тему для взрослых или неуместный для веб-сайта сообщества. Любое изображение, ссылка или обсуждение наготы. Любое поведение, которое является оскорбительным, грубым, вульгарным, оскверняет или демонстрирует неуважение. Любое поведение, которое может нарушать лицензионные соглашения с конечными пользователями, включая предоставление ключей продукта или ссылок на пиратское программное обеспечение. Незапрашиваемая массовая рассылка или массовая реклама. Любые ссылки на вирусы, шпионское ПО, вредоносное ПО или фишинговые сайты или их пропаганда. Любой другой неприемлемый контент или поведение, как это определено Условиями использования или Кодексом поведения. Любое изображение, ссылка или обсуждение, связанные с детской порнографией, детской наготой или другим жестоким обращением с детьми или их эксплуатацией.

Ответы (5) 

Благодарим за обращение. Меня зовут Али. Я независимый консультант и пользователь Microsoft, как и вы. Я буду рад помочь вам сегодня.

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

Примечание. Это веб-сайт, не принадлежащий Microsoft. Страница предоставляет точную и безопасную информацию. Следите за объявлениями на сайте, которые могут рекламировать продукты, часто классифицируемые как ПНП (потенциально нежелательные продукты). Тщательно изучите любой продукт, рекламируемый на сайте, прежде чем принять решение о его загрузке и установке.

Надеюсь, у вас все получилось. Если нет, пожалуйста, ответьте на этот форум, чтобы сообщить мне. Я буду рад продолжить и помочь вам.

Сообщить о нарушении

Фильтры (или автофильтры) очень эффективны в Excel: они позволяют выполнять не только базовую фильтрацию, но и сортировку, фильтрацию по цветам и многое другое. Один аспект фильтров мне нравится больше всего: получение быстрого обзора данных в столбце ниже. Когда дело доходит до дат, фильтры группируют даты по году, месяцу и дню. Это очень полезно для получения обзора диапазонов дат, а также для быстрого применения фильтров. Но что, если фильтр вдруг перестал группировать даты? Вот возможные причины и способы их устранения!

Первоначальные комментарии к проблемам группировки дат в фильтрах

Несколько комментариев, прежде чем мы начнем:

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

Причина 1: группировка дат в фильтрах отключена

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

В Excel выберите «Файл».
Нажмите "Параметры" (обычно в левом нижнем углу экрана).
Перейдите на вкладку "Дополнительно" на левой панели окна "Параметры".
Прокрутите страницу вниз до настроек рабочей книги и установите флажок "Группировать даты в меню автофильтра".

Вернемся к вашему фильтру, группировка теперь работает?

Причина 2: Ваш фильтр не охватывает все строки для группировки дат

Второй вариант больше похож на небольшое примечание: убедитесь, что ваш фильтр охватывает все элементы в вашем списке или таблице.

Конечно, почему бы вам не удалить его и не вставить снова? Вот как это сделать.

Хотите повысить производительность в Excel?

Получите ленту профессора Excel!

Добавьте в Excel более 120 замечательных функций!

Причина 3: Excel не распознает даты как даты

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

Сначала вы можете проверить следующее: что показывает сводка в строке состояния, когда вы выбираете несколько (более одной, достаточно двух) ячеек даты?

Если указано только «Количество: 2», скорее всего, это текстовая ячейка (см. скриншот справа). В этом случае вам нужно преобразовать ячейки в даты. Вот как это сделать (см. раздел о преобразовании текста в числовой формат или универсальный формат в конце).

Если в строке состояния также отображается сводка дат (с хотя бы одним из следующих значений: минимум, максимум, сумма, числовое значение или среднее), обе выбранные ячейки распознаются как даты. В этом случае у вас не должно возникнуть проблем (по крайней мере, с этими двумя ячейками) и проверьте причины 1 и 2 выше.

Также интересно:

Хенрик Шиффнер — независимый бизнес-консультант и разработчик программного обеспечения. Живет и работает в Гамбурге, Германия. Помимо увлечения Excel, он увлекается фотографией и спортом.

Оставить комментарий Отменить ответ

Лучшие записи и страницы

Объединение файлов Excel: как объединить книги в один файл
Возврат пустых ячеек вместо нулей в формулах Excel: легко!
Тысячи или миллионы в Excel: как изменить единицу измерения
Не удается выполнить прокрутку в Excel? Вот причины и простые решения!
Как отобразить сразу все строки или столбцы в Excel
Кнопки для вставки изображений или диаграмм в Excel выделены серым цветом?
ДВССЫЛ: как использовать текст в качестве ссылки на ячейку в Excel
Оглавление в Excel: 4 простых способа создания каталогов
Именованные диапазоны в Excel: просмотр всех определенных имен (включая скрытые имена)
Вставить название листа в ячейку: легко! 3 способа вернуть имя рабочего листа

Не пропустите главное

«Professor Excel Tools»: добавьте в Excel более 120 замечательных функций!

Обзор конфиденциальности

Нажмите здесь, чтобы прочитать нашу Политику конфиденциальности.

У меня есть большая электронная таблица, которую я объединил в сводную таблицу.

В столбцах у меня есть 1. Дата 2. Идентификатор.

Дата относится к данным электронной таблицы, которые были предоставлены мне в общем формате (ГГГГ-ММ). Сводная таблица понимает отдельные записи, но всякий раз, когда я пытаюсь сгруппировать по дате, она не работает.

Поэтому я переформатировал ячейки и переформатировал дату. Даты теперь выглядят как 1-1-2000 (не имеет значения, потому что я могу снова переформатировать их в своей сводной таблице, которую я использую для получения результатов), они также распознаются как число (выровнено по правому краю). Однако он по-прежнему не группируется. Пустых ячеек нет. Любые данные, не относящиеся к дате, некоторые из которых отфильтровываются из результатов, и используются только достоверные данные.

Почему не работает групповая функция.

Сообщить о нарушении

Ответы (8) 

Скопируйте свои данные (не копируйте весь лист, только строки, содержащие данные, не беря ни одной пустой строки) в новую тестовую рабочую книгу, измените форматирование ячеек даты на ДЛИННАЯ ДАТА, просто чтобы убедиться, что один раз опять же, что все в формате даты. В столбце данных введите число, например от 1 до 100. Теперь снова вставьте сводную таблицу и посмотрите, работает ли функция группировки по дате или нет.

Надеюсь, вы найдете причину, по которой он не работает в исходном месте.

Сообщить о нарушении

1 человек нашел этот ответ полезным

Был ли этот ответ полезен?

Извините, это не помогло.

Отлично! Благодарим за отзыв.

Насколько вы удовлетворены этим ответом?

Спасибо за ваш отзыв, он помогает нам улучшить сайт.

Насколько вы удовлетворены этим ответом?

Спасибо за отзыв.

Дата относится к данным электронной таблицы, которые были предоставлены мне в общем формате (ГГГГ-ММ).

если у вас есть даты в исходной таблице, например. ЯНВАРЬ 2000 г. или февраль 2013 г. или 02 февраля 2013 г.

(какой бы формат ни пытался использовать)
я имею в виду, что если в формате General вы можете увидеть 2013 FEB или 2013-02, то
>это НЕ дата, а текстовая строка,
поэтому вы можете изменить/перепечатать даты в исходной таблице как даты,
чтобы их можно было обрабатывать, группируя (в Pivot Tb).< /p>

Сообщить о нарушении

1 человек нашел этот ответ полезным

Был ли этот ответ полезен?

Извините, это не помогло.

Отлично! Благодарим за отзыв.

Насколько вы удовлетворены этим ответом?

Спасибо за ваш отзыв, он помогает нам улучшить сайт.

Насколько вы удовлетворены этим ответом?

Спасибо за отзыв.

Когда вы выбираете весь набор ваших базовых данных и фильтруете по столбцу даты, вы видите, что все даты сгруппированы по месяцам и годам ИЛИ также появляются какие-либо несгруппированные даты?

Сообщить о нарушении

Домогательство – это любое поведение, направленное на то, чтобы побеспокоить или расстроить человека или группу людей. К угрозам относятся любые угрозы самоубийства, насилия или причинения вреда другому человеку. Любой контент на тему для взрослых или неуместный для веб-сайта сообщества. Любое изображение, ссылка или обсуждение наготы. Любое поведение, которое является оскорбительным, грубым, вульгарным, оскверняет или демонстрирует неуважение. Любое поведение, которое может нарушать лицензионные соглашения с конечными пользователями, включая предоставление ключей продукта или ссылок на пиратское программное обеспечение. Незапрашиваемая массовая рассылка или массовая реклама. Любые ссылки на вирусы, шпионское ПО, вредоносное ПО или фишинговые сайты или их пропаганда.Любой другой неприемлемый контент или поведение, как это определено Условиями использования или Кодексом поведения. Любое изображение, ссылка или обсуждение, связанные с детской порнографией, детской наготой или другим жестоким обращением с детьми или их эксплуатацией.

Был ли этот ответ полезен?

Извините, это не помогло.

Отлично! Благодарим за отзыв.

Насколько вы удовлетворены этим ответом?

Спасибо за ваш отзыв, он помогает нам улучшить сайт.

Насколько вы удовлетворены этим ответом?

Спасибо за отзыв.

Всякий раз, когда я фильтрую, отображается ГГГГ-ММ, в диапазоне от 1994-11 до 2012-07 (исходные данные). Они помещены в мою колонку. Всякий раз, когда я пытаюсь сгруппировать это, он дает «Невозможно сгруппировать этот выбор».

Однако я изменил ГГГГ-ММ на ДД-ММ-ГГГГ (с преобразованием текста в столбец) и сделал дату всего столбца в формате (длинная дата). Это дало мне (на удивление) как ГГГГ-ММ, так и ДД-ММ-ГГГГ в фильтре. Первый, ГГГГ-ММ, не содержит данных. Оттуда я отфильтровал только те, которые содержат данные (учитывая одну или две случайные проверки из обоих форматов, это огромное количество данных). Это случилось в формате ДД-ММ-ГГГГ. Вот ответ на ваш вопрос, да, он содержит «несгруппированные» даты (показаны как переформатированные, например, 01-11-2001).

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

Теперь я сгруппировал все свои данные вручную, что, как ни удивительно, сводная таблица принимает. Но мне бы очень хотелось, чтобы Excel принял мой групповой запрос. Мой набор данных содержит около 60 тысяч записей, и мне нужно сделать еще как минимум 10 таким образом. Я уверен, что делаю что-то не так, но не могу понять, в чем дело.

Заранее спасибо за помощь!

Сообщить о нарушении

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

Уровень квалификации: средний

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

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

Существует три способа группировки дат в Excel:

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

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

Функция группового поля позволяет быстро группировать поле дат по годам, кварталам, месяцам и т. д.

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

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

Как сгруппировать и разгруппировать поля

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

Выберите ячейку в области «Строки» или «Столбцы» сводной таблицы, содержащую поле, которое вы хотите сгруппировать. Обычно это поле даты, но могут быть и числа.
Выберите вкладку Анализ/Параметры на ленте.
Нажмите кнопку "Группировать поле". Ознакомьтесь с этой статьей, если кнопка группового поля отключена.
Выберите элементы, по которым вы хотите сгруппировать поле. Для полей даты это Годы, Кварталы, Месяцы, Дни, Часы, Минуты, Секунды.
Нажмите "ОК".

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

Чтобы разгруппировать поле:

Выберите ячейку в сводной таблице для сгруппированного поля.
Выберите вкладку Анализ/Параметры на ленте.
Нажмите кнопку "Разгруппировать" (расположенную над кнопкой "Группировать").

Автоматическая группировка по дате/времени

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

Вот как отключить автоматическую группировку дат для сводных таблиц:

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

Вот снимок экрана с настройками в окне параметров Excel.

Если вы используете более раннюю версию Excel 2016, этот параметр находится на вкладке «Дополнительно» параметров Excel. Вот скриншот.

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

Где хранятся поля группы?

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

Это может быть хорошо или плохо.

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

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

К сожалению, в приложении Excel невозможно увидеть, какие сводные таблицы используют один и тот же кэш. Для этого мы можем использовать VBA.

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

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

Другой вариант — добавить столбцы к исходным данным, которые вычисляют группы. Я объяснял это более подробно в своей статье о группировке времени в Excel.

Мы можем использовать функцию ТЕКСТ или некоторые функции даты (ГОД, МЕСЯЦ, ДЕНЬ) для вычисления текстового или числового значения, представляющего группировку.

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

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

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

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

Функция групповых полей в сводной таблице НЕ рассчитывает финансовые календари.

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

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

Power Pivot помогает решить эту проблему сортировки и группировки за счет использования таблиц календаря. Таблицы календаря — это таблицы, содержащие список дат за весь период времени наших данных. Они также содержат дополнительные столбцы для групп дат.

Затем мы можем использовать Power Pivot для создания взаимосвязей между нашими наборами данных и таблицей календаря для группировки дат. Это похоже на подход ВПР в Excel и объединение в терминах базы данных.

Подход, основанный на календарной таблице, не добавляет никаких столбцов в исходную таблицу исходных данных и не применяет группировку в сводном кеше. Вместо этого он использует связи (объединение), созданные в Power Pivot, для выполнения группировки на лету.

Я считаю, что это своего рода гибридный подход, который больше подходит для группировки дат в исходных данных. Однако в Power Pivot есть функция сортировки по столбцу, которая позволяет сортировать таблицу календаря по финансовым кварталам, месяцам, неделям и т. д.

Мы также можем создавать таблицы календаря с помощью Power Query. А затем используйте формулы поиска, чтобы вставить группы полей в наши исходные данные. Или используйте функцию слияния Power Query для поиска. Ознакомьтесь с моей статьей «Объяснение таблицы календаря» для получения более подробной информации.

Список сравнения методов группировки дат

Вау! Это масса вариантов! И немного помятая рубашка. Давайте уладим это в организованном списке… 🙂

Функция группового поля сводной таблицы

Профессиональная версия — быстро и легко создавайте группы дат с помощью встроенного меню "Группировка".
За/против: сводные таблицы, использующие один и тот же кеш, также будут иметь одни и те же группы.
Профессиональная версия — переход вверх/вниз (свертывание/развертывание) групп дат в сводных диаграммах с помощью кнопок.
Против: Excel 2016 автоматически группирует поля дат, поэтому их необходимо разгруппировать вручную, если группировка вам не нужна.

Добавить столбцы дат в исходные данные

Pro – столбцы даты добавляются к исходным данным и могут использоваться в другом анализе вне сводной таблицы.
Против: исходные данные содержат больше столбцов и добавляются в файл. Может быть неэффективным для больших наборов данных.
Pro – можно рассчитывать финансовые кварталы, месяцы, недели для финансовых календарей, которые не начинаются 1 января.
Минус: сортировка периодов финансового времени может быть сложной и требует дополнительных чисел в текстовых датах.

Таблицы дат Power Pivot

Профессиональная версия: группировка дат, созданная с помощью отношений, делает отчеты более эффективными и гибкими, поскольку таблицы остаются узкими.
Профессиональная версия: одну таблицу дат можно повторно использовать с несколькими наборами данных и использовать для создания связей между несколькими таблицами данных (фактов).
За/против: требуется создание отдельной таблицы дат. Power Query может упростить эту задачу.
Против: требуется использование Power Pivot. У всех пользователей должен быть установлен Power Pivot, чтобы изменять или взаимодействовать со сводными таблицами.

Что я пропустил?

Я не думаю, что здесь есть правильный или неправильный ответ. Выбранный вами метод будет зависеть от ваших потребностей и процесса.

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

Читайте также: