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

Microsoft Excel

трюки • приёмы • решения

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

Одна из наиболее полезных функций сводных таблиц — возможность комбинировать элементы в группах. Группировка элементов проста: выделите их и выполните команду Работа со сводными таблицами ► Параметры ► Группировать ► Группа по выделенному.

Но вы можете сделать еще один шаг вперед. Если поле содержит даты, Excel способен создавать группы автоматически. Многие пользователи упускают эту полезную функцию. На рис. 171.1 показана часть таблицы, в которой есть два столбца с данными: Дата и Продажи. Полностью таблица содержит 93156 строк и охватывает период с 8 июля 2006 года по 8 августа 2009 года. Цель состоит в том, чтобы объединить информацию о продажах по месяцам.

Рис. 171.1. Вы можете использовать сводную таблицу для обобщения данных о продажах за месяц

На рис. 171.2 вы можете видеть часть сводной таблицы (в столбцах D:E ), созданную на основе данных. Неудивительно, что она выглядит так же, как и входные данные, поскольку даты не были сгруппированы. Для группировки элементов по месяцам щелкните правой кнопкой мыши на любой ячейке в столбце Дата сводной таблицы и выберите в контекстном меню пункт Группировать. Вы увидите окно Группирование, показанное на рис. 171.3. В списке выберите Месяцы и Годы и убедитесь, что начальная и конечная даты являются правильными. Нажмите кнопку ОК. Элементы Дата в сводной таблицы группируются по годам и по месяцам (как показано на рис. 171.4).

Рис. 171.2. Сводная таблица перед группировкой по месяцам и годам

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

Если вы выберете только Месяцы в окне Группирование, будут объединены месяцы ИЗ разных лет. Например, элемент июнь отобразит продажи за 2008 и 2009 годы. Обратите внимание на то, что окно Группирование содержит и другие элементы, основанные на времени. Например, можно сгруппировать данные по кварталам (рис. 171.5).

Рис. 171.4. Сводная таблица после группировки по месяцам и годам

Рис. 171.5. Сводная таблица после группировки по кварталам и годам

Источник: excelexpert.ru

Exceltip

Блог о программе Microsoft Excel: приемы, хитрости, секреты, трюки

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

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

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

Группировка по датам

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

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

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

Для группировки дат по неделям, щелкните правой кнопкой мыши на поле с датами. В выпадающем меню, выберите Группировать. В появившемся диалоговом окне Группирование, выберите пункт Дни в поле С шагом и укажите количество дней 7. Если необходимо, можно изменить стартовую дату на другую. Жмем ОК.

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

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

Обратите внимание, что данные по численности населения находятся в поле строк.

Выделите поле, которое вы хотите сгруппировать, и перейдите по вкладке Работа со сводными таблицами -> Группировать -> Группировка по полю.

В появившемся диалоговом окне Группирование, настройте диапазон группировки и шаг. В нашем случае, мы выставили диапазон от 0 до 12000 с шагом 1000.

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

Получившаяся сводная таблица показывает частотное распределение численности населения регионов России.

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

Пункт Группировать будет недоступен из выпадающего меню, если вы щелкните правой кнопкой мыши по текстовому полю. Их необходимо группировать вручную.

Выберите поле, которое вы хотите сгруппировать. На ленте перейдите по вкладке Работа со сводными таблицами -> Группировать -> Группа по выделенному.

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

Вам также могут быть интересны следующие статьи

Один комментарий

Здравствуйте, помогите пожалуйста разобраться, при группировке цифровых данных группы формируются в таком виде: 0-199, 200-399, 400-599 и т.д., как сделать чтобы конец диапазона совпадал с началом? То есть было бы 0-200, 200-400, 400-600 и т.д.? Надеюсь этому есть какое-то решение..( Спасибо!

Источник: exceltip.ru

Как сделать сводную таблицу, сгруппировать временной ряд?

Сводная таблица в Excel — это мощнейший инструмент для анализа данных, который поможет вам быстро:

• Подготовить данные для отчетов;
• Рассчитать различные показатели;
• Сгруппировать данные;
• Отфильтровать и проанализировать интересующие показатели.

А также сэкономить вам кучу времени.

Из данной статьи вы узнаете:

• Как сделать сводную таблицу;
• Как с помощью сводной таблицы сгруппировать временные ряды и оценить данные в динамике по годам, кварталам, месяцам, дням.
• Как рассчитать прогноз с помощью сводной таблицы и Forecast4AC PRO;

Для начала научимся делать сводные таблицы.

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

И в каждой строке 3-м параметра связаны между собой, т.е. например, 01.02.2010 года Товар 1 продали на 422 656 руб.

После того, как вы подготовили данные для сводной таблицы, устанавливаем курсор в первый столбец в первую ячейку простой таблицы, далее заходим в меню “Вставка” и нажимаем кнопку “Сводная таблица”

Появится диалоговое окно, в котором:

• вы можете сразу нажать кнопку “ОК”, и сводная таблица выведется в отдельный лист.
• а можете настроить параметры вывода данных сводной таблицы:

1. Диапазон с данными, которые будут выведены в сводную таблицу;
2. Куда вывести сводную (в новый лист или на существующий (если выберите на существующий, то необходимо будет указать ячейку, в которую вы хотите поместить сводную таблицу)).

Нажимаем “ОК”, сводная таблица готова и выведена в новый лист. Назовем лист “Сводная”.

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

Теперь, зажимаем левой кнопкой мыши поле “Товар” – перетаскиваем его в “Название строк”, поле “Продажи в руб.” – в “Значения” в сводной таблице. Таким образом мы получили сумму продаж по товарам за весь период:

Группировка и фильтрация временных рядов в сводной таблице

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

Для этого переходим в лист “Данные”, и после даты вставляем 3 пустых столбца. Выделяем столбец “Товар” и нажимаем “Вставить”.

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

Вставленные столбцы называем “Год”, “Месяц”, “Год-Месяц”.

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

• В столбец “Год” добавляем формулу =ГОД(со ссылкой на дату);
• В столбец “Месяц” добавляем формулу =МЕСЯЦ(со ссылкой на дату);
• В столбец “Год – Месяц” добавляем формулу =СЦЕПИТЬ(ссылка на год;” “;ссылка на месяц).

Получаем 3 столбца с годом, месяцем и годом и месяцем:

Теперь переходим в лист “Сводная”, устанавливаем курсор на сводную таблицу, вызываем правой кнопкой мыши меню и нажимаем кнопку “Обновить”. После обновления в списке полей у нас появляются новые поля сводной таблицы “Год”, “Месяц”, “Год – месяц”, которые мы добавили в простую таблицу с данными:

Теперь давайте проанализируем продажи по годам.

Для этого поле “Год” мы перетаскиваем в “название столбцов” сводной таблицы. Получаем таблицу с продажами по товарам по годам:

Теперь мы хотим еще более глубже “опуститься” на уровень месяцев и проанализировать продажи по годам и по месяцам. Для этого в “название столбцов” перетаскиваем поле “месяц” под год:

Для анализа динамики месяцев по годам, можем месяцы переместить в область сводной “Название строк” и получить следующий вид сводной таблицы:

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

• продажи по каждому товару в сумме за целый год (строка с названием товара);
• более подробно продажи по каждому товару в каждом месяце в динамике за 4 года.

Следующая задача, мы хотим убрать из анализа продажи за какой-то месяц (например, октябрь 2012 года), т.к. данные о продажах у нас еще не за полный месяц.
Для этого в область сводной “Фильтр отчета” перетащим “Год – месяц”

Нажимаем на появившейся над сводной фильтр и ставим галочку “Выделить несколько элементов”. Затем в списке с годами и номерами месяцев снимаем галочку с 2012 10 и нажимаем ОК.

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

Расчет проноза с помощью сводной таблицы и Forecast4AC PRO

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

Для того чтобы отключить итоги в сводной таблице устанавливаем курсор на столбец “Общий итог” и нажимаем на кнопку “Удалить общий итог”. Итог из сводной пропадает.

Для расчета прогноза с помощью Forecast4AC PRO устанавливаем курсор в 1 января 2009 года

и нажимаем кнопку “График Модель прогноза” в меню Forecast4AC PRO

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

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

Точных вам прогнозов!

Присоединяйтесь к нам!

Скачивайте бесплатные приложения для прогнозирования и бизнес-анализа:

• Novo Forecast Lite – автоматический расчет прогноза в Excel .
• 4analytics – ABC-XYZ-анализ и анализ выбросов в Excel.
• Qlik Sense Desktop и QlikView Personal Edition – BI-системы для анализа и визуализации данных.

Тестируйте возможности платных решений:

• Novo Forecast PRO – прогнозирование в Excel для больших массивов данных.

Получите 10 рекомендаций по повышению точности прогнозов до 90% и выше.

Источник: 4analytics.ru

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

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

В Excel предусмотрено удобное средство, которое позволяет группировать определенные элементы поля. Например, если одно из полей базы данных состоит из дат, то для каждой даты в сводной таблице будет отведена отдельная строка или столбец. Иногда полезно объединить даты в месяцы или кварталы, а затем убрать с экрана слишком детальное их представление. На рисунке 1 показана сводная таблица, созданная на основе базы данных Банк .

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

Рис. 1. Пример сводной таблицы

Чтобы создать группу, выделите ячейки, которые будут сгруппированы, в данном случае – А6:А7 . Затем выберите команду Данные | Группа и структура | Группировать . В результате Excel создаст новое поле и назовет его Отделение2 . В этом поле находиться два элемента: Западное и Группа1 (рис. 2).

Рис. 2. Сводная таблица после группировки данных

Теперь можно удалить исходное поле Отделение и переименовать названия полей и элементов. На рисунке 3 показана сводная таблица после этих изменений. Новое название поля не может совпадать с названием существующего поля. При несовпадении имен Excel просто добавляет новое поле к сводной таблице. Поэтому в рассмотренном примере нельзя переименовать Отделение2 в Отделение без удаления исходного поля.

Рис. 3. Сводная таблица после выполненных преобразований

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

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

Рис. 4. Пример сводной таблицы

Чтобы создать группу автоматически, отметьте любой элемент поля Счет . Затем выберите команду Данные | Группа и структура | Группировать . Появится диалоговое окно Группирование , показанное на рисунке 5.

Рис. 5. Диалоговое окно Группирование

По умолчанию в нем будут показаны наименьшее и наибольшее значения, которые можно изменить по своему усмотрению. Например, чтобы создать группу с шагом в 5 000, введите 0 в поле Начиная с , 100 000 – в поле По и 5 000 – в поле С шагом . Щелкните на кнопке OK , и Excel создаст указанные Вами группы. На рисунке 6 показана результирующая сводная таблица.

Рис. 6. Результирующая сводная таблица

На следующем шаге мы рассмотрим некоторые варианты отображения сводных таблиц.

Источник: it.kgsu.ru

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

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

• Ручной. После создания сводной таблицы выделите элементы, подлежащие группировке, а затем выберите команду Работа со сводными таблицами→Параметры→Группировать→Группировка по выделенному. Также можете щелкнуть правой кнопкой и выбрать в контекстном меню пункт Группировать.

• Автоматический. Если элементы являются числами или датами, можете определить способ группировки в специальном диалоговом окне Группировка. Для этого выделите любой элемент в заголовках строк или столбцов и выберите команду Работа со сводными таблицами→Параметры→Группировать→Группировка по выделенному. Также можете щелкнуть правой кнопкой и выбрать в контекстном меню пункт Группировать. В любом случае будет открыто диалоговое окно Группировка.

Пример ручной группировки

На рисунке показана сводная таблица, созданная из списка сотрудников, находящегося в столбцах А:С. В этих столбцах содержатся поля заголовков Работник, Регион и Пол. Сводная таблица, содержащаяся в столбцах Е:Н, отображает список работников в каждом из регионов.

Наша задача – создать две группы регионов: западный (города Москва, Калуга и Тверь) и восточный (города Казань, Тула и Пермь). Для создания первой группы, удерживая клавишу , выделите города Москва, Калуга и Тверь.

После этого щелкните правой кнопкой и выберите в контекстном меню пункт Группировать. Ту же операцию повторите и для второй группы. На рисунке показаны результаты такой группировки.

Можно создать любое количество групп, и даже объединить их в более общие группы.

Просмотр сгруппированных данных

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

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

Примеры автоматической группировки

Когда некоторое поле содержит числа, даты или время, Excel может автоматически создавать группы. Два примера, приведенные в настоящем разделе, демонстрируют автоматическую группировку.

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

На рисунке показана часть обычной таблицы, содержащей два поля: Дата и Продажи. Эта таблица содержит 730 строк и охватывает диапазон дат от 1 января 2009 года до 31 декабря 2010 года. Требуется обобщить данные о продажах по месяцам.

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

Для группировки элементов по месяцам выделите любую дату и выберите команду Работа со сводными таблицами→Параметры→Группировать→Группировка по полю или щелкните правой кнопкой на поле и выберите в контекстном меню команду Группировать. Откроется диалоговое окно, показанное на рисунке.

В поле с шагом выделите элементы Месяцы и Годы; при этом проверьте правильность начальной и конечной дат. Щелкните на кнопке ОК. Элементы Дата в сводной таблице будут сгруппированы по годам и месяцам, после чего таблица примет вид, показанный на рисунке.

Примечание

Если в диалоговом окне Группирование выбрать только элемент Месяцы, одинаковые месяцы разных лет будут объединены. К примеру, в строке Январь будут сведены данные о продажах за два января 2005 и 2006 года.

На рисунке ниже показано еще одно представление тех же данных, однако в этом случае группировка выполнена по годам и кварталам.

Группировка по времени

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

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

• В области значений содержится три экземпляра поля Чтение. Использовано диалоговое окно Параметры поля значений для обобщения первого экземпляра поля по среднему, второго – по минимальному и третьего – по максимальному значению.
• Поле Время помещено в область Названия строк; при этом в диалоговом окне Группирование выполнена группировка по часам.

Источник: wordex999.ru

Сводные таблицы Excel

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

Прежде всего следует свериться с бумажным календарем данного года. Рабочие даты могут начинаться с 4 января 2010 года, причем важно знать, что в этом году 4 января пришлось на понедельник. Вы решаете, с какого дня должна начинаться неделя: с воскресенья, понедельника или любого другого дня. Сверьтесь с календарем. Ближайший понедельник был 4 января 2010 года.

Выделите любой заголовок дат в сводной таблице. Перейдите на контекстную вкладку ленты Параметры (Options) и в разделе Группировать (Group) щелкните на кнопке Группировка по полю (Group Field). В диалоговом окне Группирование (Grouping) отмените выделение всех параметров в списке с шагом (By), за исключением параметра Дни (Days). В результате станет доступным счетчик количество дней (Number of Days). Чтобы создать недельный отчет, увеличьте количество дней с 1 до 7.

И наконец, следует установить флажок начиная с (Starting At) и определить дату. Если вы приняли настройки по умолчанию и начали с 4 января 2010 года, то все недельные периоды в отчете будут начинаться с понедельника и заканчиваться воскресеньем. Эти настройки нас вполне устраивают (рис. 4.6), поэтому оставим все без изменений.

Рис. 4.6. Для получения доступа к счетчику количество дней в списке с шагом выберите значение Дни

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

Рис. 4.7. Отчет, отображающий еженедельные объемы продаж

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

Группирование двух полей дат в один отчет

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

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

Источник: pivot-table.ru