Группировка по выделенному в Excel в сводных таблицах
Бывает, в сводной присутствует в строках какое-то значение, при этом ни в сырых данных, ни в Power Query, ни в модели данных его нет. Создаётся он с помощью группировки по выделенному.
345 открытий
Приведу пример, есть вот такая сводная таблица с данными и её сырые исходники.
Если провалится в настройки сводной таблицы, то вы заметите строку Quality score2, которой нет в сырых данных. Её также нет в PQ и модели данных, таблица туда попросту не подключена.
Группировка по выделенному в Excel
Непонятно откуда взялся Quality score2. На самом деле он делается проще простого, вернёмся к изначальному виду таблицы без этой строки. Выделим нужный интервал который должен быть в одной группе и выберем пункт меню «Группировка по выделенному».
Группировка строк в сводной таблице
У нас появится первая группа и новая строка в настройках сводной таблицы под названием Quality score2, потому что исходная строка называлась Quality score.
Добавление группы в сводной таблице
Таким же образом добавляем остальные группы. А после переименовываем, как нам необходимо.
Excel: как сгруппировать значения в сводной таблице по диапазону
В следующем пошаговом примере показано, как сгруппировать значения в сводной таблице в Excel по диапазону.
Шаг 1: введите данные
Во-первых, давайте введем следующие данные о 15 разных магазинах:
Шаг 2: Создайте сводную таблицу
Чтобы создать сводную таблицу из этих данных, щелкните вкладку « Вставка » на верхней ленте, а затем щелкните значок «Сводная таблица»:
В появившемся новом окне выберите A1: C16 в качестве диапазона и поместите сводную таблицу в ячейку E1 существующего рабочего листа:
После того, как вы нажмете OK , в правой части экрана появится новая панель полей сводной таблицы .
Перетащите кв. Feet в поле Rows и перетащите поле Sales в поле Values :
Сводная таблица будет автоматически заполнена следующими значениями:
Шаг 3. Сгруппируйте значения сводной таблицы по диапазону
Чтобы сгруппировать значения квадратных метров по диапазону, щелкните правой кнопкой мыши любое значение в первом столбце сводной таблицы, затем нажмите «Группировать» в раскрывающемся меню:
В появившемся окне « Группировка » выберите группировку значений, начиная с 100 и заканчивая 250 , по 25 :
Как только вы нажмете OK , значения площади в квадратных футах в сводной таблице будут автоматически сгруппированы от 100 до 250 в диапазоне длины 25:
Вот как интерпретировать значения в сводной таблице:
- Сумма продаж магазинов площадью от 100 до 124 квадратных метров составляет 51 .
- Сумма продаж магазинов площадью от 125 до 149 квадратных метров составляет 82 .
- Сумма продаж магазинов площадью от 150 до 174 квадратных метров составляет 108 .
В этом примере мы сгруппировали значения, используя диапазон 25, но не стесняйтесь использовать любой диапазон в зависимости от ваших данных.
Дополнительные ресурсы
В следующих руководствах объясняется, как выполнять другие распространенные задачи в Excel:
Exceltip
Блог о программе Microsoft Excel: приемы, хитрости, секреты, трюки
Группировка в сводных таблицах
Опубликовано 15.04.2013 Автор Ренат Лотфуллин
Сводные таблицы Excel обладают инструментом группировки полей с различными типами данных: даты, числа и текст. К примеру, вы можете сгруппировать даты заказов по годам или месяцам, посмотреть количество протестированных учеников, набравших проходной бал. Вручную выбрать текстовые элементы в поле сводной таблицы и группировать их. Это позволит увидеть промежуточные итоги для определенного набора пунктов в сводной таблице.
В сегодняшнем посте мы рассмотрим группировку в сводных таблицах.
Группировка по датам
Для группировки по датам, щелкните правой кнопкой мыши на поле с датами в сводной таблице. В выпадающем меню, выберите Группировать.
В появившемся диалоговом окне Группирование, выберите один или несколько пунктов из поля с шагом. Для ограничения стартовой и конечной даты, вы можете изменить их в полях Начиная с и По. Жмем ОК для закрытия диалогового окна.
Группировка по неделям
Для группировки дат по неделям, щелкните правой кнопкой мыши на поле с датами. В выпадающем меню, выберите Группировать. В появившемся диалоговом окне Группирование, выберите пункт Дни в поле С шагом и укажите количество дней 7. Если необходимо, можно изменить стартовую дату на другую. Жмем ОК.
Группировка числовых значений
Для начала необходимо создать сводную таблицу, и поместить в поле строк данные, которые вы хотите сгруппировать.
Обратите внимание, что данные по численности населения находятся в поле строк.
Выделите поле, которое вы хотите сгруппировать, и перейдите по вкладке Работа со сводными таблицами -> Группировать -> Группировка по полю.
В появившемся диалоговом окне Группирование, настройте диапазон группировки и шаг. В нашем случае, мы выставили диапазон от 0 до 12000 с шагом 1000.
В поле Значения поместите данные, которые вы хотите свести по сгруппированным данным. В нашем примере, я хочу увидеть количество регионов в каждом диапазоне численности.
Получившаяся сводная таблица показывает частотное распределение численности населения регионов России.
Группировка текстовых полей
Пункт Группировать будет недоступен из выпадающего меню, если вы щелкните правой кнопкой мыши по текстовому полю. Их необходимо группировать вручную.
Выберите поле, которое вы хотите сгруппировать. На ленте перейдите по вкладке Работа со сводными таблицами -> Группировать -> Группа по выделенному.
Чтобы переименовать название группы, щелкните по полю левой кнопкой мыши и в строке формул введите свое название.
Вам также могут быть интересны следующие статьи
- Применение нескольких фильтров в сводной таблице
- Работаем со срезами сводных таблиц
- Создание базы данных в Excel
- Повторяющиеся значения в Excel — найти, выделить или удалить дубликаты в Excel
- Создание сводной таблицы из данных PowerPivot
- Функция ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ
- Создание списка из сводной таблицы
- Добавление визуализации в сводную таблицу
- Новая временная шкала в Excel 2013
- Создание сводных таблиц в Excel
Один комментарий
Здравствуйте, помогите пожалуйста разобраться, при группировке цифровых данных группы формируются в таком виде: 0-199, 200-399, 400-599 и т.д., как сделать чтобы конец диапазона совпадал с началом? То есть было бы 0-200, 200-400, 400-600 и т.д.? Надеюсь этому есть какое-то решение..( Спасибо!
Добавить комментарий Отменить ответ
Рубрики
- Google Таблицы (3)
- PowerPivot (8)
- Без рубрики (1)
- Ввод данных (13)
- Визуализация (12)
- Дашборды (15)
- Диаграммы (16)
- Макросы/VBA (4)
- Математика (5)
- Надстройки (7)
- Основы (16)
- Сводные таблицы (10)
- Формат (3)
- Формулы (20)
Подписаться на e-mail рассылку
Популярные статьи
- Несколько условий ЕСЛИ в Excel
- Как рассчитать коэффициент корреляции в Excel
- Повторяющиеся значения в Excel — найти, выделить или удалить дубликаты в Excel
- Как рассчитать регрессию в Excel
- Что такое стандартное отклонение — использование функции СТАНДОТКЛОН для расчета стандартного отклонения в Excel
- 14 способов вставки Excel, о которых вы должны знать
- Как расчитать дисперсию в Excel с помощью функции ДИСП.В
- Как сравнить два столбца в Excel — методы сравнения данных Excel
- Создание связи между таблицами Excel
- Как построить график с нормальным распределением в Excel
Группировка данных в сводной таблице
Основное предназначение сводной таблицы — анализ данных. И для этого она снабжена достаточным довольно обширным функционалом, включая группировку элементов. Для чего она нужна. Предположим, имеется таблица примерно такого вида:
И необходимо проанализировать данные в разрезе месяца или квартала или на основании групп артикулов с определенным шагом.
ГРУППИРОВКА ДАТЫ И ВРЕМЕНИ
Если необходимо просмотреть суммарную стоимость предложений по кварталам, то пригодиться группировка по датам.
- Выделить любую ячейку нужного поля из области строк или столбцов и щелкнуть правой кнопкой мыши;
- Выбрать из контекстного меню пункт Группировать (Group) ;
- В поле Начиная с (Starting at) ввести начальную дату для группы;
- В поле по (Ending at) ввести конечную дату для группы;
- В поле с шагом (By) выбрать диапазон группировки: секунды, минуты, часы, дни, месяцы, кварталы, годы (seconds, minutes, hours, days, months, quarters, years) ;
- Нажать OK
Начиная с 2016 версии Excel группировка полей с датой и временем создается автоматически сразу при добавлении поля в область сводной таблицы. Чтобы отключить автогруппировку перейдите в: Файл (File) —Параметры (Options) —Данные (Data) -снять галочку с пункта Отключить автоматическое группирование столбцов даты и времени в сводных таблицах (Disable automating grouping of Date|Time columns in PivotTables) .
ГРУППИРОВКА ЧИСЛОВЫХ ПОЛЕЙ
Может пригодиться для группировки по занятым местам или по ценам предложений. Например, можно отобрать все предложения от 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) ). Вполне возможно, что это действие придется повторить еще один-два раза.
Но может быть и так, что зеленых треугольников нет, а группировка не работает. Для начала следует убедиться, что в столбце нет ячеек с явным текстом, а так же ячеек с пробелами. По хорошему — пустые ячейки тоже лучше убрать или заменить нолями. Опять обновить пару раз сводную.
Если после этого группировка все равно отказывается работать — значит где-то еще есть числа/даты, записанные как текст. Но они могут быть не подсвечены зеленым треугольником. Такое поведение часто наблюдается в файлах, выгруженных из 1С или иных программ. Часто побеждают это очень упорным трудом: выделяют ячейку, жмут F2(чтобы войти в режим редактирования ячейки) и Enter. Тогда Excel преобразует дату/число в настоящие дату/число. Но если таких ячеек хотя бы 100 — это уже не на пару минут рутины. Благо, все это можно сделать за пару секунд. Чтобы быстро преобразовать ячейки с датами/числами, записанными как текст в реальные даты/число необходимо:
- скопировать любую пустую ячейку на листе
- выделить все ячейки с датами/числами
- правая кнопка мыши -Специальная вставка (Paste Special) -в окне выбрать Значения (Values) , операция — Сложить (Multiply)
- ОК
Excel автоматом преобразует даты и числа в нормальные данные. Возможно, придется заново задать формат датам — но это уже совершенно не сложно: правая кнопка мыши —Формат ячеек (Format Cells) -Дата (Date) .
Про другие возможности Специальной вставки можно прочитать в этой статье: Как быстро умножить/разделить/сложить/вычесть из множества ячеек одно и то же число?
ГРУППИРОВКА ТЕКСТОВЫХ ПОЛЕЙ ИЛИ ОТДЕЛЬНЫХ ЭЛЕМЕНТОВ
- Выделить ячейку из области строк или столбцов с одним из элементов поля для группировки;
- Удерживая CTRL или SHIFT выделить другие элементы (ячейки) этого поля;
- Щелкнуть правой кнопкой по любой выделенной ячейке и выбрать из контекстного меню пункт Группировать (Group) или на вкладке Параметры (Options) в группе Группировать (Group) нажать кнопку Группа по выделенному (Group Selection);
- При необходимости задать свое имя группе
В полях с уровнями можно группировать только элементы, имеющие одинаковые подуровни. Например, если в поле есть два уровня «Страна» и «Город», нельзя сгруппировать города из разных стран.
ПЕРЕИМЕНОВАНИЕ ГРУППЫ ПО УМОЛЧАНИЮ
При группировке элементов Excel задает имена групп по умолчанию, например Группа1 (Group1) для выбранных элементов или Кв-л1 (Qtr1) для квартала 1(если работаем с датами). Задать группе более понятное имя совсем несложно:
- Выделить имя группы;
- Нажать клавишу F2;
- Ввести новое имя группы.
- Выделить группу элементов, которые требуется разгруппировать;
- На вкладке Параметры (Options) в группе Группировать (Group) нажать кнопку Разгруппировать (Ungroup) (или щелкнуть правой кнопкой мыши и выбрать из контекстного меню пункт Разгруппировать (Ungroup) ).
- Для источников данных OLAP (Online Analytical Processing), не поддерживающих инструкцию CREATE SESSION CUBE, группировка элементов невозможна.
- При наличии одного или нескольких сгруппированных элементов использовать команду Преобразование в формулы (Параметры— Сервис— Средства OLAP) невозможно. Перед использованием этой команды необходимо сначала удалить сгруппированные элементы.
- Для быстрой работы c группами данных надо выделить ячейки в области названий строк или столбцов сводной таблицы, щелкнуть правой кнопкой мыши на любой из выделенных ячеек и выбрать Развернуть/Cвернуть (Expand/Collapse)
Для числовых полей или полей даты и времени все группы для поля будут разгруппированы. При выборе групп будут разгруппированы только выбранные элементы, а поле группы не будет удалено из списка полей сводной таблицы, пока все группы в поле не будут удалены.
Статья помогла? Поделись ссылкой с друзьями!