Как сгруппировать столбцы в excel в сводной таблице

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:

Excel: как сгруппировать по месяцам и годам в сводной таблице

Часто вам может понадобиться сгруппировать данные по месяцам и годам в сводной таблице Excel.

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

В следующем примере показано, как использовать эту функцию на практике.

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

Предположим, у нас есть следующий набор данных в Excel, который показывает общий объем продаж какой-либо компании в разные дни:

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

Выделите ячейки в диапазоне A1:B12 , затем щелкните вкладку « Вставка » на верхней ленте, затем щелкните « Сводная таблица ».

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

В появившемся окне в правой части экрана перетащите Date в Rows и Sales в Values :

Следующая сводная таблица будет заполнена автоматически:

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

Затем нажмите «Группа» в раскрывающемся меню.

В появившемся новом окне выберите параметры « Месяцы и годы» , а затем нажмите « ОК »:

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

Теперь мы можем видеть сумму продаж, сгруппированную по месяцам и годам.

• Всего в январе 2021 года было совершено 11 продаж.
• Всего в феврале 2021 года было совершено 12 продаж.

Дополнительные ресурсы

В следующих руководствах объясняется, как выполнять другие распространенные операции в 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
• 14 способов вставки Excel, о которых вы должны знать
• Как рассчитать коэффициент корреляции в Excel
• Четыре способа использования ВПР с несколькими условиями
• VBA циклы — For Next и For Each в Excel
• Как сравнить два столбца в Excel — методы сравнения данных Excel
• Что такое стандартное отклонение — использование функции СТАНДОТКЛОН для расчета стандартного отклонения в Excel
• Как рассчитать регрессию в Excel
• Печать таблицы в Excel — настройка области и границ печати Excel

Сортировка данных в сводной таблице или сводной диаграмме

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

Перед сортировкой

При сортировке данных учитывайте приведенные ниже сведения.

• Порядок сортировки зависит от выбранного языка. Убедитесь в том, что на панели управления в разделе «Региональные параметры» или «Язык и региональные стандарты» задан правильный языковой стандарт. Сведения о том, как изменить языковой стандарт, см. в справке Windows.
• Данные могут содержать начальные пробелы, которые влияют на результаты сортировки. Для получения оптимальных результатов следует удалить такие пробелы перед сортировкой.
• Нельзя сортировать текстовые значения с учетом регистра символов.
• Нельзя сортировать данные по определенному признаку форматирования, такому как цвет ячеек или шрифта. Также нельзя сортировать данные по индикаторам условного форматирования, например наборы значков.

Сортировка меток строк или столбцов

1. В сводной таблице щелкните маленькую стрелку рядом со списком Названия строк или Названия столбцов.
2. Щелкните поле в строке или столбце, которые требуется отсортировать.
3. Щелкните стрелку в списке Названия строк или Названия столбцов, а затем выберите нужный параметр.

Чтобы отсортировать данные в порядке возрастания или убывания, нажмите кнопки Сортировка от А до Я или Сортировка от Я до А.

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

Сортировка по столбцу, который не имеет кнопки со стрелкой

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

В приведенном ниже примере данные на уровне категории (шлем, дорожная сумка) сортируются в алфавитном порядке от А до Я.

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

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

Установка собственных параметров сортировки

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

1. Щелкните ячейку в строке или столбце, которые требуется отсортировать.
2. Щелкните стрелку на вкладке Метки строк или Метки столбцов, а затем выберите Дополнительные параметры сортировки.

В диалоговом окне Сортировка выберите необходимый тип сортировки:

• Чтобы изменить порядок элементов перетаскиванием, щелкните Вручную. Наведите указатель мыши на границу элемента, чтобы увидеть четырехконечную стрелку, затем перетащите ее.
• Элементы, которые отображаются в области значений списка полей сводной таблицы, нельзя перетаскивать.
• Щелкните По возрастанию (от А до Я) по полю или По убыванию (от Я до А) по полю и выберите поле для сортировки.
• Чтобы увидеть другие параметры сортировки, нажмите щелкните на пункте Дополнительные параметры, затем в диалоговом окне Дополнительные параметры сортировки выберите подходящий вариант:

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

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

Примечание: Сортировка по настраиваемым спискам не сохраняется после обновления данных в сводной таблице.

Совет: Чтобы восстановить исходный порядок элементов, выберите вариант Как в источнике данных. Он доступен только для источника данных OLAP.

Перед сортировкой

При сортировке данных учитывайте приведенные ниже сведения.

• Порядок сортировки зависит от выбранного языка. Убедитесь в том, что на панели управления в разделе «Региональные параметры» или «Язык и региональные стандарты» задан правильный языковой стандарт. Сведения о том, как изменить языковой стандарт, см. в справке Windows.
• Данные могут содержать начальные пробелы, которые влияют на результаты сортировки. Для получения оптимальных результатов следует удалить такие пробелы перед сортировкой.
• Нельзя сортировать текстовые значения с учетом регистра символов.
• Нельзя сортировать данные по определенному признаку форматирования, такому как цвет ячеек или шрифта. Также нельзя сортировать данные по индикаторам условного форматирования, например наборы значков.

Сортировка меток строк или столбцов по возрастанию или убыванию

1. Щелкните ячейку в строке или столбце, которые требуется отсортировать.
2. Щелкните стрелку на списке Названия строк или Названия столбцов, а затем выберите нужный параметр сортировки.

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

Сортировка меток строк или столбцов вручную

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

• Наведите указатель мыши на границу элемента, пока не увидите курсор перемещения, а затем перетащите его.

• Вы также можете щелкнуть правой кнопкой мыши метки строк или столбцов и выбрать любой из параметров Переместить:

Примечание: Элементы, которые отображаются в области значений списка полей сводной таблицы, нельзя перетаскивать.

Сортировка по определенному значению

1. Щелкните стрелку на списке Названия строк и выберите пункт Сортировка по значению.

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

Перед сортировкой

При сортировке данных учитывайте приведенные ниже сведения.

• Порядок сортировки зависит от выбранного языка. Убедитесь в том, что на панели управления в разделе «Региональные параметры» или «Язык и региональные стандарты» задан правильный языковой стандарт. Сведения о том, как изменить языковой стандарт, см. в справке Windows.
• Данные могут содержать начальные пробелы, которые влияют на результаты сортировки. Для получения оптимальных результатов следует удалить такие пробелы перед сортировкой.
• Нельзя сортировать текстовые значения с учетом регистра символов.
• Нельзя сортировать данные по определенному признаку форматирования, такому как цвет ячеек или шрифта. Также нельзя сортировать данные по индикаторам условного форматирования, например наборы значков.

Сортировка меток строк или столбцов

1. В сводной таблице щелкните маленькую стрелку рядом со списком Названия строк или Названия столбцов.
2. Щелкните поле в строке или столбце, которые требуется отсортировать.
3. Щелкните стрелку в списке Названия строк или Названия столбцов, а затем выберите нужный параметр.

Чтобы отсортировать данные в порядке возрастания или убывания, нажмите кнопки Сортировка от А до Я или Сортировка от Я до А.

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

Сортировка по столбцу, который не имеет кнопки со стрелкой

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

В приведенном ниже примере данные на уровне категории (шлем, дорожная сумка) сортируются в алфавитном порядке от А до Я.

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

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

Подготовка

При сортировке данных учитывайте приведенные ниже сведения.

• Порядок сортировки зависит от выбранного языка. Убедитесь в том, что на панели управления в разделе «Региональные параметры» или «Язык и региональные стандарты» задан правильный языковой стандарт. Сведения о том, как изменить языковой стандарт, см. в справке Windows.
• Данные могут содержать начальные пробелы, которые влияют на результаты сортировки. Для получения оптимальных результатов следует удалить такие пробелы перед сортировкой.
• Нельзя сортировать текстовые значения с учетом регистра символов.
• Нельзя сортировать данные по определенному признаку форматирования, такому как цвет ячеек или шрифта. Также нельзя сортировать данные по индикаторам условного форматирования, например наборы значков.

Сортировка меток строк или столбцов

1. В сводной таблице коснитесь небольшой стрелки рядом с ячейками Метки строк и Метки столбцов.

2. Выберите поле, которое нужно отсортировать, а затем коснитесь нужного параметра сортировки.

Совет: Прокрутите вверх, чтобы просмотреть полный список доступных параметров и полей сортировки.

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

Сортировка по столбцу, который не имеет кнопки со стрелкой

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

В приведенном ниже примере данные на уровне категории (Аксессуары, Велосипеды) имеют алфавитное убывание Сортировка.

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

См. также

• Создание сводной таблицы для анализа данных на листе
• Создание сводной таблицы для анализа внешних данных
• Создание сводной таблицы для анализа данных в нескольких таблицах
• Фильтрация данных в сводной таблице
• Группировка и отмена группировки данных в отчете сводной таблицы
• Применение условного форматирования в сводной таблице