Как сгруппировать таблицу в excel по фильтру

Помогла ли Вам статья?

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

Настраиваем параметры функции

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

1. Переключившись во вкладку “Данные” щелкаем по кнопке “Структура” и в открывшемся перечне команд – по небольшому значку в виде стрелки, направленной по диагонали вниз.
2. На экране отобразится небольшое окошко с параметрами функции. Здесь мы можем настроить отображение итогов. Ставим галочки напротив нужных опций (в т.ч. автоматические стили) и жмем кнопку OK.Примечание: расположение итоговых данных в строках под данными многим кажется неудобным, поэтому данный параметр можно выключить.
3. Все готово, теперь можем перейти, непосредственно, к самой группировке данных.

Группируем данные по строкам

Для начала давайте рассмотрим, как можно сгруппировать строки:

1. Вставляем новую строку над или под строками, которые хотим сгруппировать (зависит от того, какой вид расположения итогов по строкам мы выбрали). Как это сделать, читайте в нашей статье – “Как добавить новую строку в Excel“.
2. В самой левой ячейке добавленной строки пишем название, которое хотим присвоить группе.
3. Любым удобным способом, например, с помощью зажатой левой кнопки мыши производим выделение ячеек строк (кроме итоговой), которые требуется сгруппировать. Во вкладке “Данные” щелкаем по кнопке “Структура” и в открывшемся списке выбираем функцию “Группировать”. Щелкнуть нужно именно по значку команды, а не по ее названию. Если же нажать на последнее (со стрелкой вниз), откроется еще одно подменю, в котором следует нажать на одноименную кнопку.
4. В появившемся окошке отмечаем пункт “строки” (должен быть выбран по умолчанию) и подтверждаем действие нажатием OK.Примечание: Если вместо ячеек выделить все строки целиком на вертикальной панели координат, а затем применить группировку, то промежуточного окна с выбором строки или столбца не будет, так как программа сразу понимает, что именно ей необходимо сделать.
5. Группа создана, о чем свидетельствуют появившаяся на панели координат полоска со знаком “минус”. Это означает, что сгруппированные данные раскрыты. Чтобы их скрыть, нажимам по минусу или кнопке с цифрой “1” (самый верхний уровень группировки).
6. Теперь строки скрыты. Чтобы их обратно раскрыть, нажимаем по значку “плюса”, который появился вместо “минуса” (или по кнопке “2”).

Группируем столбцы

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

1. Вставляем столбец справа или слева от группируемых – зависит от выбранного параметра в настройках функции. Подробнее о том, как это сделать, читайте в нашей статье – “Как вставить столбец в таблицу Эксель“.
2. Пишем название в самой верхней ячейке нового столбца.
3. Выделяем ячейки группируемых столбцов (за исключением добавленного) и применяем функцию группировки.
4. Ставим отметку напротив варианта “столбцы” и кликам OK.Примечание: как и в случае с группировкой строк, при выделении столбцов целиком на горизонтальной панели координат, группировка данных будет выполнена сразу, минуя промежуточное окно с выбором элементов.
5. Задача успешно выполнена. 

Создаем многоуровневую группировку

Возможности программы позволяют выполнять как одноуровневые, так и многоуровневые группировки. Вот как это делается:

1. В раскрытом состоянии главной группы, внутри которой планируется создать еще одну, выполняем действия, рассмотренные в разделах выше в зависимости от того, с чем мы работаем – со строками или столбцами.
2. Таким образом, мы получили многоуровневую группировку.

Разгруппировываем данные

Когда ранее выполненная группировка столбцов или строк больше не нужна или требуется выполнить ее иначе, можно воспользоваться обратной функцией – “Разгруппировать”:

1. Производим выделение сгруппированных элементов, после чего все в той же вкладке “Данные” в группе инструментов “Структура” выбираем команду “Разгруппировать”. Жмем именно по значку, а не по названию.
2. В открывшемся окне ставим отметку напротив требуемого пункта (в нашем случае – “строки”) и нажимаем OK.Примечание: в случае многоуровневой группировки или наличия нескольких групп данных, каждую из них необходимо расформировать отдельно.
3. Вот и все, что требовалось сделать.

Заключение

Группировка данных выполняется в несколько кликов и не требует особых навыков в работе с программой, однако, данный прием позволяет существенно сэкономить время, когда приходится иметь дело с большим объемом информации. Это делает функцию одной из самых полезных и незаменимых в Excel.

Фильтр по многоуровневым группам

Еще один пример, расширяющий грани возможного в Excel.

Требуется создать фильтр, отбирающий записи в таблице сгруппированных по определенному полю. Главное условие — группы могут быть вложены одна в другую. Задача по описанию проста, встречается повсеместно, но реализовать нормальную работу в электронных таблицах на практике достаточно проблематично. В программных комплексах для решения этой задачи используются служебные таблицы соответствий «родитель-потомок» (все ко всем), либо аналогичные по функциональности структуры в оперативной памяти. В Excel можно использовать автофильтр с множественной выборкой подчиненных групп вручную. Этот же алгоритм можно попытаться автоматизировать, используя программную проверку элементов фильтра. Решение вполне реализуемо, но по факту смотрится не очень красиво, так как в Excel не реализован вызов события на изменение автофильтра. Придется добавлять специальную кнопку или искать еще какое-то не самое красивое решение с точки зрения пользовательского интерфейса. Можно также попытаться совсем не использовать встроенные возможности фильтрации Excel, вместо этого полностью реализовать проверку, скрытие и показ нужных строк с данными средствами VBA. Но это не наш метод — мы не используем программирование, там где без него можно обойтись.

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

Файл

В приложении к статье файл multilevelgroups.xls с решением задачи. Работает во всех версиях Excel, начиная с 2000 (10.0) (по идее Excel 2011 for Mac тоже должно работать, пока нет возможности проверить). Для автоматического обновления таблицы данных по выбору из списка необходимо подключить макросы Excel.

В файле 2 таблицы, первая — справочник многоуровневых групп, вторая — пример таблицы с данными (Группа-Продукт). Для фильтрации групп использовано поле со списком.

Можно добавлять данные в середину справочника групп через копирование строк целиком. Также можно менять названия и подчиненность. Помните, что при переименовании родительской группы, значения в подчиненных группах (поле Parent) автоматически не меняется. Для переименования можно использовать замену данных, либо привязать подчиненную группу через формулу со ссылкой на название родителя. Справочник групп не содержит никаких дополнительных полей для ввода, кроме названия и ссылки на родительсий элемент. В скрытых столбцах содержатся формулы, позволяющие решить задачу фильтрации данных без программирования. Для показа скрытых столбцов проще всего нажать на кнопку [+] структуры документа.

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

В таблице с условными данными в заголовке поля Group располагается элемент управления типа раскрывающийся список. Взят за основу контрол из коллекции Forms, а не ActiveX Combobox — для возможности использования в Excel for Mac. Переделать пример на ActiveX Combobox не представляет особого труда. В свойствах контрола указан источник значений списка ($D$2:$D$19) и служебная ячейка записи номера выбранного элемента ($H$1)

Как это работает

Основной идеей, реализации алгоритма решения задачи является использование стандартного интерфейсного средства Расширенный фильтр. Он позовляет применить в фильтре условия, записанные в отельном диапазоне рабочего листа. Самое важное отличие от обычного автофильтра — эти условия можно вычислить по формулам! Так в примере отбираются строки по полю Group таблицы данных на основе условий записанных в столбце F справочника групп. Критерий отбора расширенного фильтра представляет собой множество значений, вычисленных по сложным формулам. Служебное поле FilterID формируется на основе рассчитанного уровня вложенности и номера строки в справочнике. Таким образом, вместо дополнительного идентификатора группы использован номер строки по порядку, всегда хранящийся в электронных таблицах неявным образом. Префикс «=» в итоговых значениях поля Group определяет точное соответствие в условии, без него отбираются наименования, начинающиеся на значение в ячейке.

После изменения значения в поле со списком запускается макрос, который обновляет диапазон расширенного фильтра таблицы. Для удобства использования диапазонам фильтрации и критериев были присвоены имена — listData и listCriteria (в примере выделены жирными рамками). Использование именованных диапазонов позволяет сохранить работоспособность примера при изменении размеров справочника или таблицы данных. Макрос, запускаемый после выбора нового элемента списка, настолько простой, что первоначально был просто записан автоматическим средством Excel.

Sub Macro1()
    Range("listData").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range("listCriteria"), Unique:=False
End Sub

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

Ограничения

Из-за особенностей реализации имеются ограничения в справочнике групп:

• Общее количество элементов в каждой подгруппе — не более 99
  Формулы поддерживают два знака на каждую группу.
• Количество уровней вложенности — не более 4
  Задано в ячейке G1, можно увеличить
• Группы должны иметь уникальные наименования
• Заголовки полей фильтрации групп в таблице и в служебном столбце справочника должны быть одинаковые.
  В примере — Group.

Основная причина ограничений в размере поля FilterID, там должно храниться целое число. В каждом конкретном случае практической реализации можно настроить пример на расширение ограничений, например, увеличить максимальное количество групп, уменьшив лимит уровней вложенности.

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

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

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

Пример 1. Группировка чисел

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

Теперь щелкаем правой кнопкой мыши по цене (область строк) и выбираем из контекстного меню команду Группировать (Group). В Excel 2000-2003 эта команда находится в разделе Группа и структура (Group and Show Detail) контекстного меню:

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

В окне можно задать начальное и конечное значение интересующего нас интервала и шаг изменения. Особое внимание хочу обратить на флажки слева от слов «начиная с» и «по» — эти флажки относятся к слову «Авто» и означают, что Excel будет сам выбирать интервал (дизайнеру окна напинать бы за такой user-friendly интерфейс сами знаете что). Для определенности введем начальное значение равное нулю, конечное 270 (самый дорогой наш товар стоит 263,5$) и зададим шаг 30, т.е. окно должно выглядеть так:

После нажатия на ОК Excel сгруппирует товары с ценами в заданном интервале и посчитает суммарную выручку по каждому интервалу:

Теперь ясно видно, что (в данном примере) наибольшую выручку нам дают товары с ценой до 60 долларов. «Хочешь быть богатым — работай для бедных!» как говорил Рональд Макдональд.

Пример 2. Группировка дат

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

Строим простую сводную, поместив Дату заказа в область строк, а Стоимость заказа в область данных:

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

После нажатия на ОК Excel сгруппирует даты с шагом месяцы и кварталы и мы получим вот такой красивый отчет:

Пример 3. Произвольная группировка

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

… и сгруппировать товары (предварительно перетащив их мышью в сводной таблице в соседние строки) в одну кучку:

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

Ссылки по теме

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

Is this article up to date?