Poldet1.ru

Пул Дет №1
1 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

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

Exceltip

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

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

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

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

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

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

группировка по годам и месяцам

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

Чтобы сгруппировать даты по неделям, щелкните правой кнопкой мыши на поле даты. Выберите Group в выпадающем меню. В появившемся диалоговом окне Группировка в поле С шагами выберите Дни и укажите количество дней 7. При необходимости вы можете изменить дату начала на другую дату. Нажмите OK.

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

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

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

В поле строки вы найдете данные о населении.

сводная таблица без группировки

Выберите поле, которое хотите сгруппировать, и перейдите в Crosstabs -> Group -> Group by field.

группировка по полю

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

группирока по выделенному полю

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

список полей сводной таблицы

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

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

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

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

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

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

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

Excel works!

menu

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

ГруппировкаСводные5

Данные, о которых я говорил выше, защищены договором о конфиденциальности, поэтому приведу простой пример из предыдущей статьи «Как сделать сводную таблицу». У нас есть данные по отделам, теперь добавлен столбец Месяц

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

Нужно посчитать данные столбца Показатель по кварталам, т.е. по 3 месяца. Можно сказать: «Так посчитай руками». Но нет, строк может быть много, а мы тут собрались автоматизировать.

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

Давайте создадим сводную таблицу. Без группировки.

ГруппировкаСводные1

Щелкните левой кнопкой мыши на любом значении, которое нужно сгруппировать, чтобы выиграть, и щелкните правой кнопкой мыши, чтобы получить меню:

Читайте так же:
Как применить произвольный числовой формат к диаграмме Excel?

ГруппировкаСводные2

Появится экран настройки параметров группировки.

ГруппировкаСводные4

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

И это, в общем-то, все.

Зачем же тогда использовать электронные таблицы?

Во-первых, он удобен для выполнения основной задачи

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

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

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

Это полезная, но не столь впечатляющая функция.

Выберите несколько текстовых данных и нажмите кнопку группировать. Теперь вы сможете свернуть эту группу текстовых значений.

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

Группировка точно такая же 😉

Разгруппировка данных

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

Если числовые данные не группируются?

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

Подробно, как это сделать, вы можете прочитать в этой статье, а так же может помочь проверка формулы подсчета

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

Немного изменим нашу электронную таблицу и добавим в строки числовой код продукта вместо дат:

Видоизменяем сводную таблицу для чисел

Алгоритм такой же, как и в варианте с датами, щелкните правой кнопкой мыши по любой ячейке с числами и выберите команду Группировать:

Настройка группировки для чисел

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

Группировка для чисел

Цифры оказались не такими уж сложными, поэтому перейдем к последнему варианту.

Группировка по годам в excel. Разгруппировка сгруппированных данных. Группировка по неделям

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

Дополнительные варианты сверления


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

Группы дат и часов четко обозначены в сводной таблице.

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

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

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

Группировка, выберите один или несколько элементов в поле шага. Если вы хотите ограничить даты начала и окончания, вы можете изменить их в полях Start From и To. НажмитеОК , чтобы закрыть диалоговое окно.

Читайте так же:
Как преобразовать несколько адресов электронной почты в гиперссылки в Excel?

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

Например, апрель, май, июнь в течение нескольких месяцев. Более подробную информацию о группировке можно найти в конце этой статьи. Если вы перетащите дату из поля списка в поле строки или столбца, где уже есть поле, а затем вставите ‘date’ поверх существующего поля, существующее поле даты будет удалено из поля строки или столбца, а данные автоматически сбросятся, чтобы вы могли видеть поле при уменьшении данных. Вы также можете выбрать определенные элементы и сгруппировать их следующим образом

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

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

Щелкните правой кнопкой мыши на том, что вы выбрали, и нажмите «Группировать». При группировке выбранных элементов создается новое поле на основе сгруппированных полей. Например, когда вы группируете поле «Поставщик», вы создаете новое поле с именем «Поставщик1». Это поле добавляется на вкладки списка полей, и вы можете использовать его как любое другое поле.

Дезагрегировать агрегированные данные

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

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

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

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

При разгруппировке числового поля или поля даты и времени все группы будут удалены. При разгруппировке группы выбранных элементов фрагментируются только отдельные элементы. Поле группы не будет удалено из списка полей, пока не будут разгруппированы все группы полей. Например, предположим, что в поле Город есть четыре города: Бостон, Нью-Йорк, Лос-Анджелес и Сиэтл. Вы можете сгруппировать их так, чтобы Нью-Йорк и Бостон находились в группе, которую вы назовете «Атлантический океан», а Лос-Анджелес и Сиэтл — в группе, которую вы назовете «Тихий океан».

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

Ниже приведен список четырех городов, сгруппированных по «Атлантическому» и «Тихоокеанскому» регионам. Отмена группировки по времени или автоматически свернутых полей удаляет все вычисляемые поля, оставляя только поле даты. Это соответствует тому, как работает список отмены в предыдущих выпусках.

Для группировки данных в сводную таблицу

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

Читайте так же:
Как преобразовать числа в год / месяц / день или дату в Excel?

Диапазон группировки и шаг можно выбрать в появившемся диалоговом окне. В нашем случае мы установили диапазон от 0 до 12000 с шагом 1000.

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

Группировка данных: т.е. группировка данных по дате в сводной таблице. Форматирование цифр: т.е. исправление дублирующего форматирования цифр в сводных таблицах. Группировка чисел и текста: т.е. группировка целых чисел. Группировка десятичных дробей.

Суммирование поля значений: различные способы суммирования поля значений. Суммировать несколько раз в одном поле. Переименование полей в сводной таблице. Быстрый поиск информации из сводной таблицы по перекрывающимся критериям. Crosstabs: вставка сводной диаграммы. Соедините кросстаб с кросстабом.

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

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

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

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

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

Использование сводных таблиц позволяет объединять элементы в группы, что является важной функцией. Группировка элементов проста: выделите их и выполните команду Выполнить параметры электронной таблицы Группировать по выделенным .

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

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

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

Читайте так же:
Как просуммировать диагональ диапазона в Excel?

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

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

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

  • Убедитесь, что значения даты имеют правильный формат DATE.
  • Создайте таблицу pivot, которая показывает общий объем продаж по датам.
  • Сгруппируйте сводную таблицу, чтобы показать общий объем продаж по месяцам
  • Сгруппируйте сводную таблицу, чтобы показать общий объем продаж по месяцам за каждый год.

Давайте по очереди рассмотрим эти четыре этапа.

Убедитесь, что ваши значения даты находятся в правильном формате DATE

Как вы можете видеть в наборе данных, даты отображаются в столбце OrderDate. В этой колонке указывается период или продолжительность, в течение которого были совершены продажи (или заказаны). Для правильной работы группы перекрестных вкладок важно, чтобы значения в столбце OrderDate были в формате DATE, который по умолчанию равен MM/DD/YYYY (в большинстве случаев).

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

  • Выберите столбец OrderDate (столбец A на нашем листе).
  • Щелкните меню «Формат» на ленте меню.
  • Наведите указатель мыши на параметр «Число» в появившемся раскрывающемся списке.
  • Откроется подменю Number.
  • Теперь вы можете выбрать опцию «Дата», если хотите, чтобы ваши значения были отформатированы в формате даты по умолчанию.

Примечание. Если вам нужен другой формат даты, вы можете навести курсор на «Другие форматы» после шага 4, а затем нажать на «Дополнительные форматы даты и времени». Откроется окно «Пользовательские форматы даты и времени», в котором можно выбрать нужный формат даты.

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

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

  • Щелкните меню «Данные» на ленте меню.
  • В появившемся раскрывающемся меню выберите параметр «Сводная таблица».
  • Теперь вы должны увидеть окно с вопросом, хотите ли вы вставить сводную таблицу на существующий лист или на новый лист. Выберите тот вариант, который вам больше нравится. Для наглядности всегда лучше создавать его на новом листе.
  • Щелкните по кнопке Create.
  • Это должно создать вашу сводную таблицу либо на том же листе, либо на новом листе, в зависимости от того, что вы выбрали на шаге 3.
  • Ваша сводная таблица на этом этапе должна выглядеть как на снимке экрана, показанном ниже:
  • Должна быть сетка, отображающая «Строки», «Столбцы» и «Значения».
  • Теперь вы можете начать заполнять сводную таблицу необходимыми данными. В правой части окна вы должны увидеть редактор сводной таблицы. Это поможет вам указать, что должно быть в вашей сводной таблице.
  • Теперь мы хотим, чтобы в нашей сводной таблице было два столбца (изначально) — Дата заказа и Общий объем продаж на эту дату. Итак, в категории «Строки» нажмите «Добавить».
  • В появившемся раскрывающемся списке выберите OrderDate. Это добавит каждую уникальную дату заказа в отдельные строки вашей сводной таблицы.
  • Затем мы хотим увидеть общий объем продаж для каждой даты заказа. Итак, в категории «Значения» нажмите «Добавить».
  • В появившемся раскрывающемся списке выберите Всего. Это отобразит сумму всех продаж для каждой даты заказа.
Читайте так же:
Как разбить данные на несколько листов на основе столбца в Excel?

Сводная таблица начинает обретать смысл. Но было бы логичнее и легче читать, если бы даты были сгруппированы по месяцам.

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

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

Google Spreadsheets предлагает очень простой способ группировки значений в сводной таблице по дате. Вот шаги:

  • Щелкните правой кнопкой мыши любую дату в столбце OrderDate.
  • В появившемся контекстном меню выберите или наведите указатель мыши на «Создать группу дат сводной таблицы».
  • Вы должны увидеть подменю с множеством опций для группировки по дате.
  • Вы заметите, что есть несколько вариантов даты, по которым вы можете сгруппировать их. Вы можете группировать по дню, неделе, месяцу, кварталу, году и даже по их комбинации. Чтобы сгруппировать по месяцам, выберите вариант «Месяц».
  • У вас также есть возможность выбрать «Год-месяц». При этом ваши данные будут сгруппированы сначала по годам, а затем по месяцам для каждого года.

У вас должна получиться сводная таблица, похожая на эту:

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

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

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

Для этого вам сначала нужно отсортировать строки по «Месяцу», а не «Год-месяц». Вот шаги, которые вам необходимо выполнить:

  • Щелкните правой кнопкой мыши любую дату в столбце OrderDate.
  • В появившемся контекстном меню выберите или наведите указатель мыши на «Создать группу дат сводной таблицы».
  • Вы должны увидеть подменю с множеством опций для группировки по дате.
  • Чтобы сгруппировать по месяцам, выберите вариант «Месяц».
  • Затем нажмите кнопку «Добавить» в категории «Столбцы» редактора сводной таблицы.
  • В появившемся раскрывающемся списке выберите OrderDate.
  • Это должно отображать дату каждого заказа в столбцах.
  • Щелкните правой кнопкой мыши одну из дат.
  • В появившемся контекстном меню выберите или наведите указатель мыши на «Создать группу дат сводной таблицы».
  • Вы должны увидеть подменю с множеством опций для группировки по дате.
  • Чтобы сгруппировать по году, выберите вариант «Год».

Теперь в вашей сводной таблице каждый год должен отображаться в колонках.

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

В этом руководстве мы показали простой пример того, как легко сгруппировать данные по месяцам в сводной таблице в Google Spreadsheets. Мы надеемся, что это было полезно для вас.

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector