Группировка по полю неактивна в Excel

Обновлено: 20.11.2024

Настройка — сводные таблицы Excel 2013, получение данных из куба SSAS2014.

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

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

Тем не менее. Я не могу включить эту команду «Группировать поле» (на ленте «Инструменты сводной таблицы», раздел «Анализ»), когда я нажимаю поле даты. (Если быть точным, я нажимаю на конкретный день в сводной таблице; я нажимаю на атрибут в разделе «Строки» поля сводной таблицы; я нажимаю на заголовок строки - без разницы).

Вот что я пробовал:

  1. Уровень параметра "Дни" отображается в сводной таблице в формате США (мм/дд/гггг). Я подумал, что эти данные в сочетании с настройками локали моего компьютера (европейский формат даты дд/мм/гггг) могут сбить с толку Excel, поэтому я изменил локаль на США через панель управления и снова открыл файл .xlsx. Без разницы.
  2. Проверен базовый атрибут измерения. Он имеет следующие свойства: Type=Days; KeyColumn=[целочисленный столбец, не связанный с датами, просто суррогатный ключ IDENTITY]; NameColumn=[столбец WChar, содержащий дату в формате мм/дд/гггг]; ValueColumn=[нет]. Ладно, подумал я, ничего похожего на Date там нет, может быть, поэтому Excel не может понять, как сгруппировать эти данные.
  3. Открыл сводную таблицу в AdventureWorks, чтобы посмотреть, что она делает. Использование атрибута «Дата» измерения «Дата» — Excel по-прежнему не включает «Поле группы». Глядя на дизайн базового измерения, атрибут Date имеет несколько иные свойства: Type=Date; KeyColumn=[целочисленный столбец формы 20150807 на сегодня, например]; NameColumn=[столбец Wchar]; ValueColumn=[столбец типа Дата].

Поэтому я в замешательстве. Формат ячеек — это хороший быстрый способ узнать, воспринимает ли Excel содержимое ячеек как даты: но формат ячеек не работает в метках строк сводной таблицы (ни в AdventureWorks, ни в моем кубе).

Есть ли способ заставить «Групповое поле» работать с измерениями даты в сводных таблицах/SSAS? Я надеялся узнать через AdventureWorks, но и это не работает.

Самая близкая аналогия, которую я могу найти в Интернете, находится здесь, где люди предполагают, что проблема заключается в том, что Excel не понимает данные как даты. Но все ответы в этой ветке предназначены для людей, которые используют сводные таблицы для данных, импортированных в Excel, а не против SSAS:

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

Уровень навыков: начальный

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

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

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

Простое правило включения функции группового поля для дат:

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

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

Итак, теперь мы ищем ТЕКСТ и ОШИБКИ! 🙂

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

3 способа найти текст или ошибки в столбце

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

Даты группируются в раскрывающемся меню фильтра

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

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

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

Чтобы отфильтровать значения Text и ERROR:

  1. Снимите отметку с элементов группы дат ИЛИ снимите флажок (выбрать все), затем выберите текст и элементы с ошибкой.
  2. Нажмите "ОК".

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

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

Суперсовет: если столбец содержит только текст и не содержит ошибок, вы можете использовать меню «Пользовательский автофильтр», чтобы быстро отфильтровать все, что не является датой. В раскрывающемся меню фильтра выберите «Фильтры даты» > «Равно» > введите звездочку * в поле критериев > нажмите «ОК».

Это применит фильтр ко всем текстовым значениям в столбце. Обратите внимание, что этот метод работает только для текста, а не для логических (ИСТИНА/ЛОЖЬ) или значений ошибок.

Выбор текста и ячеек с ошибками в специальном меню «Переход»

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

В этом случае мы можем использовать GoTo Special для выбора ячеек с текстом или ошибками. Вот инструкции:

  1. Выделите весь столбец поля даты [сочетание клавиш: Ctrl+Пробел].
  2. Откройте специальное меню «Перейти» (вкладка «Главная» > меню «Найти и выбрать» > «Перейти к специальному…») [сочетание клавиш: F5, Alt+S]
  3. Выберите переключатель «Константы».
  4. Снимите флажок с номера.
  5. Нажмите "ОК".

Будут выбраны все ячейки, содержащие текст, логические значения (ИСТИНА/ЛОЖЬ) или ошибки. Затем мы можем применить другой цвет заливки, чтобы пометить эти ячейки для исправления.

Поиск ячеек даты с помощью макроса (VBA)

Мы также можем использовать VBA для определения типа данных ячейки. Есть несколько способов сделать это. Одним из быстрых способов является использование функции TypeName.

Функция TypeName возвращает тип данных объекта или значение, которое вы ей передаете. Мы можем поместить следующую строку кода в окно Immediate и нажать Enter, чтобы получить тип данных ячейки.

Строковое значение «Дата» будет возвращено, если ячейка содержит значение даты.

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

Ознакомьтесь с моей статьей о 5 способах использования Immediate Window VBA, чтобы узнать больше об этом методе.

Вот макрос, который перебирает все ячейки в выбранном диапазоне и выводит список адресов ячеек, не содержащих даты, в окне Immediate. Вы можете скопировать/вставить код в свой файл.

Метод Debug.Print печатает значение в Immediate Window на новой строке. Нажмите здесь, чтобы ознакомиться с моей бесплатной серией обучающих материалов по началу работы с макросами и VBA, чтобы узнать больше о выполнении этого макроса.

Исправление дат, сохраненных в виде текста

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

Существует МНОЖЕСТВО дополнительных методов исправления неправильных значений дат. Пожалуй, достаточно, чтобы написать книгу. Пожалуйста, оставьте комментарий ниже с любыми датами, сохраненными в виде текста, которые у вас возникли проблемы с преобразованием в значение даты. Я сделаю следующий пост с некоторыми распространенными методами.

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

Я думаю, что он соответствует всем критериям (пустых полей нет, источник данных в формате даты), но, к сожалению, у меня этого не происходит.

Факты об Excel

Это связано с тем, что сумма является 9-й по алфавиту в полях Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

Голень22

Новый участник

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

Флойд Палмер

Новый участник

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

Пример дат, которые я ввожу — это не только это.

старл

Администратор

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

Обратите внимание, что формат, который вы нам показываете, не является стандартным форматом Excel. Хотя вы могли бы создать собственный формат.

Голень22

Новый участник
  1. Выберите всю строку, содержащую даты.
  2. Нажмите Ctrl+F
  3. Выберите вкладку "Заменить".
  4. Введите "." (без кавычек) в "Найти что:"
  5. Введите "/" (без кавычек) в поле "Заменить на:"
  6. Не снимая выделения со всеми ячейками, щелкните правой кнопкой мыши одну из них и выберите "Форматировать ячейки".
  7. На вкладке "Число" выберите "Дата" и выберите формат даты, который вы предпочитаете.
  8. Попробуйте снова запустить сводную таблицу.

Флойд Палмер

Новый участник
  1. Выберите всю строку, содержащую даты.
  2. Нажмите Ctrl+F
  3. Выберите вкладку "Заменить".
  4. Введите "." (без кавычек) в "Найти что:"
  5. Введите "/" (без кавычек) в поле "Заменить на:"
  6. Не снимая выделения со всеми ячейками, щелкните правой кнопкой мыши одну из них и выберите "Форматировать ячейки".
  7. На вкладке "Число" выберите "Дата" и выберите формат даты, который вы предпочитаете.
  8. Попробуйте снова запустить сводную таблицу.

Спасибо, сработало!

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

Голень22

Новый участник

Спасибо, сработало!

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

Я не могу повторить ошибку, когда я обновляю, она обновляется с новым форматом даты. Я бы попробовал две вещи в этом порядке:

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

Проблема

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

Есть несколько возможных причин. Начнем

Решение 1. Выберите только один лист для группировки строк или столбцов

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

Итак, просто выберите только один рабочий лист. Если причина не в этом, перейдите к решению номер 2 ниже.

Решение 2. Вы редактируете ячейку — просто оставьте ячейку, чтобы вставить группировку

Вы находитесь внутри ячейки Excel, например, для ввода формулы? Если да, вам нужно сначала покинуть ячейку, чтобы добавить группы.Также возможно, что вы редактируете ячейку в другом файле Excel (хотя в более новых версиях Excel это не должно быть проблемой).

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

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

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

Решение 3. Снимите защиту с листа или книги, чтобы добавить группировку

Рабочий лист защищен? В этом случае вы обычно получаете сообщение об ошибке. Итак, вам нужно снять защиту с рабочего листа. Для этого перейдите на ленту обзора и нажмите «Снять защиту с листа».

Аналогично для загруженных файлов Excel: Excel открывает эти книги в режиме «Защищенный просмотр». Это видно по желтой полосе в верхней части листа:

Если вы нажмете «Включить редактирование», вы сможете добавить группу.

Небольшое примечание: знаете ли вы, что в Excel можно изменить направление группировок? Вот как это сделать!

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

Может быть и другая причина. В параметрах Excel вы можете выбрать, хотите ли вы отображать группы. Так может группировка работает, а просто не показывается?

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

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

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

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

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

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

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

Перед уходом: улучшите свои навыки работы с Excel! Присоединяйтесь к тысячам пользователей Excel и подпишитесь на информационный бюллетень!

  • Лучшие советы, рекомендации и учебные пособия по Excel.
  • 1 раз в месяц.
  • Никакого спама. Обещано.

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

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

Если вы попытаетесь сгруппировать элементы сводной таблицы в Excel, вы можете получить сообщение об ошибке "Невозможно сгруппировать этот выбор". В более старых версиях Excel, если у вас возникали проблемы с группировкой элементов сводной таблицы, это обычно было вызвано пустыми ячейками или текстом в полях числа/даты. В Excel 2013 и более поздних версиях есть еще одна вещь, которая может помешать вам группировать, — модель данных Excel.

Невозможно сгруппировать это выделение

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

Модель данных

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

  • При создании сводной таблицы есть флажок "Добавить эти данные в модель данных".
  • Если вы установите этот флажок, вы не сможете группировать элементы в сводной таблице.

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

Он основан на OLAP?

Быстрый способ узнать, основана ли ваша сводная таблица на основе OLAP, — это проверить ленту:

Выберите любую ячейку в сводной таблице

  • На ленте Excel перейдите на вкладку "Анализ" (в разделе "Инструменты сводных таблиц").
  • В разделе "Расчеты" найдите команду "Инструменты OLAP".
    • Если он неактивен, ваша сводная таблица имеет традиционный тип.
    • Если команда активна, ваша сводная таблица основана на OLAP

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

    Исправить проблему с группировкой

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

    ПРИМЕЧАНИЕ. Вы также можете сохранить сводную таблицу на основе OLAP и иметь две сводные таблицы на основе одних и тех же данных, используя разные сводные кэши.

    Пустые ячейки или текст

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

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

    • пустая ячейка в поле даты/числа или
    • текстовый ввод в поле даты/числа.

    Чтобы решить проблему

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

    Затем обновите сводную таблицу и попробуйте снова сгруппировать элементы.

    Предыдущая группа

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

    1. Проверьте список полей, чтобы увидеть, есть ли вторая копия поля даты, например Дата2.
    2. Если есть, добавьте его в область строк и разгруппируйте.
    3. После этого вы сможете снова сгруппировать поле даты.

    Видео: группировка сводной таблицы

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

    В этом коротком видео показаны основы группировки сводной таблицы

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

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

    17 мыслей на тему “Проблемы с группировкой элементов сводной таблицы”

    Спасибо, это оказалось действительно полезным, но после перехода на Excel 2016 это меня расстраивало.

    СПАСИБО СПАСИБО СПАСИБО СПАСИБО!

    Я искал решение этой проблемы повсюду. Вы спасаете жизни (или, по крайней мере, спасаете рассудок).

    Я перепробовал все, но все равно не могу заставить это работать. Я буду рад вашей помощи.

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

    Филипп, вы только что спасли мои волосы от поседения! Спасибо 🙂

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

    Любая помощь будет принята с благодарностью. Я схожу с ума.

    Да, это полезно.

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

    Спасибо! Форматирование данных как даты исправило это для меня.

    Большое спасибо!
    Я боролся с этим, пока не нашел запись в вашем блоге!
    Я предлагаю вам оставить ссылку на эту запись в блоге на вашем основном сайте!

    alt+sift+d, чтобы создать новую сводку, и выберите ….

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

    Спасибо, это решило проблему, не было сохранено как дата 🙂

    Как раз то, что мне было нужно. Спасибо.

    Почему автоматическая группировка дат работает с использованием обычных сводных таблиц, НО при создании сводной таблицы с помощью мощного инструмента сводной таблицы с использованием точно такого же набора данных я не могу их автоматически сгруппировать? Это должно работать в Excel 2016, однако я использую Microsoft Office 2019 Professional Plus — они отключили его для этой версии?

    Заранее большое спасибо.

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

    У меня была аналогичная проблема, вызванная целой кучей дат, которые, по-видимому, были тайно текстовыми значениями (несмотря на то, что поле было отформатировано как дата).
    Я подтвердил это, введя идентичную дату в другое поле, а затем спросив Excel, если А1 = В1. Несмотря на то, что это одна и та же дата, они не оказались равными. Поэтому я создал новый столбец, добавил формулу для умножения значения в столбце даты на 1, а затем вставил результаты поверх оригинала в качестве значений. Он заменил текстовые даты числовыми датами. Это решило мою проблему.

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

    Оставить ответ Отменить ответ

    Этот сайт использует Akismet для уменьшения количества спама. Узнайте, как обрабатываются данные ваших комментариев.

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