Выделенные объекты нельзя объединить в группу excel

Выделенные объекты нельзя объединить в группу excel

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

Пример 1: Группировка в сводной таблице по дате

Предположим, мы создали сводную таблицу (как на картинке ниже), которая показывает данные продаж для каждого дня первого квартала 2016 года.

Если нужно выполнить группировку данных о продажах по месяцам, то это можно сделать вот так:

  1. Кликните правой кнопкой мыши по левому столбцу сводной таблицы (столбец с датами) и выберите команду Группировать (Group). Появится диалоговое окно Группирование (Grouping) для дат.
  2. Выберите Месяцы (Month) и нажмите ОК. Данные таблицы будут сгруппированы по месяцам, как показано в сводной таблице ниже.

Пример 2: Группировка в сводной таблице по диапазону

Предположим, мы создали сводную таблицу (как на картинке ниже), которая группирует список из 150 детей по возрасту. Группы делятся по возрастам от 5 до 16 лет.

Если нужно пойти еще дальше и объединить возрастные группы в категории 5-8 лет, 9-12 лет и 13-16 лет, то можно поступить вот так:

  1. Кликните правой кнопкой мыши по левому столбцу сводной таблицы (столбец с возрастами) и выберите команду Группировать (Group). Появится диалоговое окно Группирование (Grouping) для чисел.Excel автоматически заполнит поля Начиная с (Starting At) и По (Ending At) минимальным и максимальным значениями из наших исходных данных (в нашем примере это 5 и 16).
  2. Мы хотим объединить возрастные группы в категории по 4 года, следовательно, в поле С шагом (By) вводим значение 4. Жмём ОК.Таким образом возрастные группы будут объединены в категории начиная с 5-8 лет и далее с шагом по 4 года. В итоге получится вот такая таблица:

Как разгруппировать сводную таблицу

Чтобы разгруппировать значения в сводной таблице, нужно:

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

Распространённые ошибки при группировке в сводной таблице

Ошибка при группировке в сводной таблице: Выделенные объекты нельзя объединить в группу (Cannot group that selection).

Иногда при попытке выполнить группировку в сводной таблице оказывается, что команда Группировать (Group) в меню не активна, или появляется окно с сообщением об ошибке Выделенные объекты нельзя объединить в группу (Cannot group that selection). Это происходит чаще всего потому, что в столбце данных в исходной таблице содержатся нечисловые значения или ошибки. Чтобы исправить это, нужно вместо нечисловых значений вставить числа или даты.

После этого кликните правой кнопкой мыши по сводной таблице и нажмите Обновить (Refresh). Данные в сводной таблице будут обновлены, и теперь группировка строк или столбцов должна быть доступна.

Несмотря на то что большая часть операций подведения итогов и вычисления показателей выполняется с использованием стандартных команд сводных таблиц, иногда возникают ситуации, в которых требуется дополнительная группировка данных. [1] Например, исходные финансовые данные, как правило, фиксируются с указанием даты сделки (рис. 1), в отчете желательно представить их не по каждой дате отдельно (рис. 2а), а сгруппированными по месяцам (рис. 2б). Команда Группировать вкладки Анализ позволяет легко и быстро консолидировать даты сделок в группы, основанные на месяцах или кварталах. Впоследствии вы сможете суммировать данные в этих группах таким же образом, как это выполняется в полях сводной таблицы.

Рис. 1. Исходные данные содержат список сделок с указанием конкретной даты

Скачать заметку в формате Word или pdf, примеры в формате Excel

Рис. 2. Отчет, сведенный по датам: (а) данные двух лет продаж занимают более 500 столбцов; (б) после группировки по месяцам отчет существенно удобнее

Группировка полей дат

Для группировки полей дат выделите заголовок поля дат или любую ячейку с датой. Например, на рис. 2а выделите одну из ячеек: В3, В4, С4, D4… На контекстной вкладке ленты Анализ в области Группировать щелкните на кнопке Группировка по полю. Если поле содержит информацию о датах, откроется диалоговое окно Группирование (рис. 3). Обратите внимание, что исходные данные (см. рис. 1) в колонке Дата заказа должны содержать только даты; даже одна текстовая или незаполненная ячейка (пустая) в исходных данных в столбце Дата заказа не позволит сделать группировку по датам. Если перед группированием вы выделили только одну ячейку, то кнопки Группировка по полю и Группировка по выделенному работают одинаково. Различие проявится только если перед группированием вы выделите несколько ячеек.

Рис. 3. Окно Группирование

Можно группировать данные по секундам, минутам, часам, дням, месяцам, кварталам и годам. По умолчанию выделен вариант Месяцы. Выберите также Дни и Годы и нажмите Ok. Обратите внимание на некоторые особенности группировки данных в итоговой сводной таблице. Во-первых, поля Месяцы и Годы добавлены в список полей (рис. 4). Не позволяйте себя одурачить — ваш источник данных не изменился и никаких новых полей не содержит. Эти поля теперь являются частью кеша сводной таблицы в памяти (подробнее о кеше см. Excel 2013. Создание нескольких сводных таблиц на основе одного источника данных: один кеш или несколько?). Во-вторых, по умолчанию поля Месяцы и Годы автоматически добавляются в макет сводной таблицы. Вы можете работать с ними, как с обычными полями: перетаскивать в другие области или делать неактивными. В-третьих, включайте данные по Дням, чтобы иметь возможность добавить это поле в сводную таблицу. Если вы в окне Группирование оставите только Месяцы, то поле Даты вы будете видеть в списке, вот только оно эквивалентно месяцам, а поле Месяцы не появится. И наконец, в окне Группирование добавляйте поле Годы. Если это сделать, то вы корректно сможете разделить данные по годам (рис. 5а), если же этого не сделать, то данные двух лет наблюдений объединяться в одном столбце (рис. 5б).

Читайте также:  Усилитель для термопары на lm358

Рис. 4. Добавление полей Месяцы и Годы в список полей и макет сводной таблицы

Рис. 5. Группирование: (а) по дням, месяцам и годам; (б) по дням и месяцам

Группировка полей дат по неделям

Диалоговое окно Группирование предлагает настройки группировки по секундам, минутам, часам, дням, месяцам, кварталам и годам. А что делать, если нужно сгруппировать данные по одной или двум неделям или иным промежуткам времени? Это вполне реально.

Прежде всего следует свериться с календарем, чтобы решить, с какого дня должна начинаться неделя: с воскресенья, понедельника или любого на ваш выбор. Например, первый понедельник в 2014-м году – 6 января. Если вы хотите, чтобы данные за первую неделю также отражались, вам следует выбрать в качестве начала отсчета последний понедельник 2013-го года – 30 декабря. Чтобы сгруппировать даты по неделям выделите заголовок или ячейку с датой. Например, на рис. 2 ячейки В3 или В4. Перейдите на контекстную вкладку ленты Анализ и в разделе Группировать щелкните на кнопке Группировка по полю. В диалоговом окне Группирование (рис. 6) выделите только параметр Дни. В результате станет доступным счетчик количество дней. Чтобы создать недельный отчет, установите значение 7. Установить в поле начиная с требуемую дату (в нашем примере – 30.12.13). Нажмите Ok. В результате сгенерируется отчет, отображающий еженедельные объемы продаж, как показано на левой части рис. 6.

Рис. 6. Группировка дат по неделям

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

Группирование двух полей дат в одной сводной таблице

При группировке поля дат по месяцам и годам программа Excel переназначает исходное поле дат для отображения месяцев и добавляет новое поле для отображения лет. Новое поле получает имя Годы. Все довольно просто, если у вас в отчете представлено только одно поле дат. Если вам нужно создать отчет с двумя полями дат (например, дата заказа и дата оплаты) и вы пытаетесь сгруппировать оба поля по месяцам и годам, программа Excel сама установит первому сгруппированному полю имя Годы, а второму — имя Годы2. Чтобы не возникло путаницы, переименуйте поле Годы, прежде чем запустить группирование по второму полу дат.

Группировка числовых полей

Диалоговое окно Группирование, применяемое для числовых полей, позволяет группировать элементы в одинаковые диапазоны. Это может быть полезно при проведении частотного анализа. Сводная таблица на рис. 7 сформирована необычным образом. Здесь в область СТРОКИ помещено поле Доход, а в область ЗНАЧЕНИЕ – поле Заказчик. Поскольку поле Заказчик является текстовым, сводная таблица автоматически подсчитывает их количество, а не сумму.

Рис. 7. Сводная таблица, «заготовка» для проведения частотного анализа

Выделите в столбце А любое число, а затем на вкладке Анализ щелкните на кнопке Группировка по полю. В диалоговом окне Группирование выберите параметры группирования (рис. 8). В рассматриваемом случае группирование начинается с 0 и завершается величиной 25 350 при шаге группирования 5000.

Рис. 8. Частотное распределение на основе группировки заказов в группы по $5000 по полю Доход

Разгруппировка. Создав группу, вы можете разгруппировать её с помощью кнопки Разгруппировать, находящейся на вкладке Анализ. Достаточно выделить ячейку со сгруппированными данными и щелкнуть на этой кнопке. Эта команда также доступна в контекстном меню: выделите ячейку со сгруппированными данными и щелкните правой кнопкой мыши.

Группировка текстовых полей

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

Для начала создайте отчет, отображающий доход по рынкам сбыта. Держа нажатой клавишу Ctrl, выделите рынки сбыта, на основе которых будет создана новая зона (рис. 9). Перейдите на вкладку Анализ, и щелкните на кнопке Группировка по выделенному.

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

Читайте также:  Обратный элемент по модулю python

Excel добавит новое поле с именем Рынок сбыта2 (рис. 10). Выделенные на первом шаге рынки сбыта объединились в Группа1. Выделите оставшиеся рынки сбыта и повторно щелкните на кнопке Группировка по выделенному.

Рис. 10. Первая зона создана

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

Рис. 11. Итоговый отчет по новым зонам продаж

[1] Заметка написана на основе книги Билл Джелен, Майкл Александер. Сводные таблицы в Microsoft Excel 2013. Глава 4.

13 комментариев для “Группировка данных сводной таблицы в Excel 2013”

Очень все доходчиво и понятно описано.
Есть вопрос.
То, что показано на рис. 6. Группировка дат по неделям, есть возможность задать шаг группировки по дням с указанием к-ва дней.
В вашем файле (лист " Рис. 6 " ) все работает. Но когда я самостоятельно пытаюсь воссоздать подобную сводную, у меня шаг к-во дней не активно. Почему?
Спасибо, Артем.

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

Добрый день!
Как быть, если ни на вкладке «Анализ», ни в контекстном меню команда Группировать недоступна? Не помогает ни создание файла с 0 и копирование содержимого с применением спецвставки в режиме «Только значение». В каких случаях в сводных таблицах группировка недоступна? Речь не идет о связанных таблицах — таблица только одна с исходными данными.
С уважением
Новак

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

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

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

этим можно как то управлять?

Я не сталкивался с таким. Правда использую Excel2013. Если я первоначально группирую по месяцам в русском Excel, то при открытии в английском Excel показывает название месяцев на английском; при этом группировка сохраняется.

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

Мне не известна такая возможность. На мой взгляд, названия месяцев зашиты в региональные настройки MS Windows и не могут быть изменены внутри Excel. Если найдете, как это сделать, поделитесь…

Группировка станет доступна (активна) если при создании таблицы не добавлять данные в модель (снять флажок «Добавлять эти данные в модель данных»)

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

Наталья, пришлите файл в личку с подробным описанием, что добавляете, и что при этом не получается.

Группировка в сводных таблицах (831,4 KiB, 1 070 скачиваний)

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

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

ГРУППИРОВКА ДАТЫ И ВРЕМЕНИ
Если необходимо просмотреть суммарную стоимость предложений по кварталам, то пригодиться группировка по датам.

  1. Выделить любую ячейку нужного поля из области строк или столбцов и щелкнуть правой кнопкой мыши;
  2. Выбрать из контекстного меню пункт Группировать (Group) ;
  3. В поле Начиная с (Starting at) ввести начальную дату для группы;
  4. В поле по (Ending at) ввести конечную дату для группы;
  5. В поле с шагом (By) выбрать диапазон группировки: секунды, минуты, часы, дни, месяцы, кварталы, годы (seconds, minutes, hours, days, months, quarters, years) ;
  6. Нажать OK

ГРУППИРОВКА ЧИСЛОВЫХ ПОЛЕЙ
Может пригодиться для группировки по занятым местам или по ценам предложений. Например, можно отобрать все предложения от 110 000р до 130 000р с шагом 10 000р. В данном случае получим таблицу, в которой будут интересующие предложения из указанного диапазона, разбитые с нужным шагом. Если какие значения превышают указанную сумму(130 000р), то будет отдельная группа: >130000, если меньше: (Group) ;

  • В поле Начиная с (Starting at) ввести первый элемент группы (110000);
  • В поле по (Ending at) ввести последний элемент группы (130000);
  • В поле с шагом (By) указать шаг для детализации в каждой группе (10000).
  • Что делать, если данные не группируются
    При группировке дат и числовых полей есть небольшой нюанс: иногда, при наличии дат или чисел в ячейках и вызове группировки можно получить сообщение "Выделенные объекты нельзя объединить в группу.":

    И вроде бы в ячейках даты/числа и все равно. В данном случае следует проверить — а действительно ли числа это числа, а даты — даты? Потому как бывает и так, что выглядят в ячейках данные как числа или даты, а на деле это просто текст. В большинстве случаев Excel подсвечивает такие ячейки зелеными треугольничками в левом верхнем углу:

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

    После этого обязательно необходимо перейти в сводную таблицу и обновить её(выделить любую ячейку сводной таблицы→Правая кнопка мыши→Обновить (Refresh) или вкладка Данные (Data) →Обновить все (Refresh all) →Обновить (Refresh) ). Вполне возможно, что это действие придется повторить еще один-два раза.

    Читайте также:  Invalid hwid ошибка чита freeqn net

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

    Если после этого группировка все равно отказывается работать — значит где-то еще есть числа/даты, записанные как текст. Но они могут быть не подсвечены зеленым треугольником. Такое поведение часто наблюдается в файлах, выгруженных из 1С или иных программ. Часто побеждают это очень упорным трудом: выделяют ячейку, жмут F2(чтобы войти в режим редактирования ячейки) и Enter. Тогда Excel преобразует дату/число в настоящие дату/число. Но если таких ячеек хотя бы 100 — это уже не на пару минут рутины. Благо, все это можно сделать за пару секунд. Чтобы быстро преобразовать ячейки с датами/числами, записанными как текст в реальные даты/число необходимо:

    • скопировать любую пустую ячейку на листе
    • выделить все ячейки с датами/числами
    • правая кнопка мыши -Специальная вставка (Paste Special) -в окне выбрать Значения (Values) , операция — Сложить (Multiply)
    • ОК

    Excel автоматом преобразует даты и числа в нормальные данные. Возможно, придется заново задать формат датам — но это уже совершенно не сложно: правая кнопка мыши —Формат ячеек (Format Cells) -Дата (Date) .
    Про другие возможности Специальной вставки можно прочитать в этой статье: Как быстро умножить/разделить/сложить/вычесть из множества ячеек одно и то же число?

    ГРУППИРОВКА ТЕКСТОВЫХ ПОЛЕЙ ИЛИ ОТДЕЛЬНЫХ ЭЛЕМЕНТОВ

    1. Выделить ячейку из области строк или столбцов с одним из элементов поля для группировки;
    2. Удерживая CTRL или SHIFT выделить другие элементы (ячейки) этого поля;
    3. Щелкнуть правой кнопкой по любой выделенной ячейке и выбрать из контекстного меню пункт Группировать (Group) или на вкладке Параметры (Options) в группе Группировать (Group) нажать кнопку Группа по выделенному (Group Selection);

    4. При необходимости задать свое имя группе

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

    ПЕРЕИМЕНОВАНИЕ ГРУППЫ ПО УМОЛЧАНИЮ
    При группировке элементов Excel задает имена групп по умолчанию, например Группа1 (Group1) для выбранных элементов или Кв-л1 (Qtr1) для квартала 1(если работаем с датами). Задать группе более понятное имя совсем несложно:

      1. Выделить имя группы;
      2. Нажать клавишу F2;
      3. Ввести новое имя группы.

      1. Выделить группу элементов, которые требуется разгруппировать;
      2. На вкладке Параметры (Options) в группе Группировать (Group) нажать кнопку Разгруппировать (Ungroup) (или щелкнуть правой кнопкой мыши и выбрать из контекстного меню пункт Разгруппировать (Ungroup) ).

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

      1. Для источников данных OLAP (Online Analytical Processing), не поддерживающих инструкцию CREATE SESSION CUBE, группировка элементов невозможна.
      2. При наличии одного или нескольких сгруппированных элементов использовать команду Преобразование в формулы (ПараметрыСервисСредства OLAP) невозможно. Перед использованием этой команды необходимо сначала удалить сгруппированные элементы.
      3. Для быстрой работы c группами данных надо выделить ячейки в области названий строк или столбцов сводной таблицы, щелкнуть правой кнопкой мыши на любой из выделенных ячеек и выбрать Развернуть/Cвернуть (Expand/Collapse)

      Так же см.:
      [[Общие сведения о сводных таблицах]]
      [[Сводная таблица из нескольких листов]]

      Статья помогла? Поделись ссылкой с друзьями!

      Поиск по меткам

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

      Хотелось бы увидеть в столбцах: Общие итоги (+), Итоги по Магазину (+), Итоги по Марке (- не дает, только внутри каждого магазина), Итоги по Цвету (+), Итоги по Магазину-Марке(+), Итоги по Марке-Цвету(- не дает), Итоги по Магазину -Цвету (не дает).

      Итого 7 итогов: 4 могу сделать, а 3 не получается ( в одной таблице). Приходится делать надстройку поверх Сводной.

      Ссылка на основную публикацию
      Вопросы по эксель с ответами
      1. Для чего предназначена программа Microsoft Excel? А) Для редактирования текстов Б) Для редактирования картинок В) Для работы с таблицами...
      Видеокарта 1 vs 2 performance
      Вашему вниманию предлагается самая большая таблица сравнения видеокарт NVIDIA GeForce. Все видеокарты в виде таблицы, содержат информацию о порядковом месте...
      Видеорегистратор mdr 210 инструкция
      Настройка автомобильного видеорегистратора, если есть инструкция по эксплуатации - процесс несложный. Однако при ее отсутствии могут возникнуть определенные проблемы. Между...
      Выберите факс модем или сервер
      При установке роли факс-сервера, если факсимильное устройство не подключено к компьютеру, в папке «Принтеры» на панели управления будет автоматически создано...
      Adblock detector