Repeater-zone.ru

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

Группировка данных в Microsoft Excel

Группировка данных в Microsoft Excel

Группирование в Microsoft Excel

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

Exceltip

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

12 комментариев

Добрый день!
Как в книге создать 2 группировки друг за другом, чтобы когда они свернуты не отображались страницы.
Например, в таблице 15 строк. 3 всегда отображаются. Затем 4 строки в группировке и следующие 4 строки в группировке. Надо чтобы,когда обе группировки собраны из них ничего не отображалось.

Читать еще:  PortableApps для запуска portable программ — подробное описание

Здравствуйте, Кир.
Для каждой группировки нужно определять итоговый столбец, который располагается либо над группируемым участком, либо под ним. Поскольку у Вас две группы строк примыкают друг к другу, нужно для верхнего участка установить итоги над данными, для нижнего — под данными. Как это делается — описано вначале пункта «Ручное создание структуры».

Добрый день!
Как сделать чтобы группировки были разного цвета( например 1 группа где знак (+) красного. 2 группа где знак (+) зеленого итд)

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

Здравствуйте! Почему при создании структуры с первого столбца в документе не отображается плюсик? Только точки над структурируемыми столбцами и цифры уровня структуры… Что делать, если структура нужна именно с 1го столбца? Excel 2016. Спасибо.

Здравствуйте, Елена. Не отображается плюсик, потому что он проставляется над столбцом промежуточных итогов. Например, в столбцах у Вас данные по месяцам, и они сворачиваются в итоговые цифры года. Плюсик будет над столбцом соответствующего года.
Итоговый столбец может быть слева или справа от группируемого массива. У Вас в настройках выставлено «слева», однако столбца слева не существует, Вы группируете начиная с первого. Потому, плюсика и нет. Вы можете пойти двумя путями:
1. Скрывать и раскрывать группировку кнопками уровней в верхнем левом углу окна
2. Выбрать расположение столбца итогов справа от группируемого массива. Тогда плюсик будет отображаться в следующем столбце после группируемых. Как это сделать — я рассказал в пункте «Ручное создание структуры»

Объединение таблиц запроса в MS Excel

Для получения запроса с большим количеством данных Вы можете объединить несколько запросов. Для этого необходимо:

  1. В верхнем меню перейдите на закладку "Главная" и нажмите кнопку "Комбинировать"
  2. В открывшемся меню выберите пункт "Слияния запросов". Для добавления столбцов в текущий запрос выберите подпункт "Слияние запросов". Для формирование нового запроса выберите пункт "Объединение запросов в новый".
  3. В открывшемся окне "Слияние" выберите поле по которому будет установлена связь с первого запроса и поле для связи со второго запроса. Рекомендуем связывать по полям идентификаторам, это уникальные поля содержащие номера: проектов, ресурсов, заданий, назначений. Если хотите связать по нескольким полям выделите их с нажатым клавишей Shift, при этом последовательность связей указывается номерами возле столбцов.
  4. В поле "Тип соединения" выберите каким образом будут соединены запросы. Шесть типов связей позволяет связать таблицы разными способами.
  5. После установки всех значений нажмите кнопку "Ок"

Объединение таблиц запроса в MS Excel

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

7. В открывшемся перечне полей выберите поля необходимые поля. Если снять галочку "Использовать исходное им столбца как префикс" в названии вставляемых столбцов не будет выводится название сказуемого запроса.

8. Для добавления столбцов нажмите кнопку "Ок".

Читать еще:  Как сделать инверсию в Фотошопе

Excel works!

menu

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

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

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

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

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

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

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

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

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

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

Появится окно выбора параметров Группирование

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

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

Вот в принципе и все.

Почему же стоит пользоваться сводными таблицами?

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

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

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

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

Это полезная, но не такая эффектная функция.

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

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

Группировка происходит абсолютно по такому же принципу 😉

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

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

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

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

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

Построим по исходной таблице сводную (подробно про это рассказывал в отдельной статье), в строки таблицы добавим даты, в значения поместим сумму:

Создание сводной таблицы по датам

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

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

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

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

Читать еще:  Драйвера для XP-PEN

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

Группировка по месяцам и кварталам

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

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

Как сгруппировать объекты?

Вот как сгруппировать данные в Excel:

  1. Отметьте нужные ряды.
  2. Чтобы выделить множество позиций, находящихся рядом, кликните по первой и последней категории в диапазоне с зажатой клавишей Shift. Или «обведите» их курсором.
  3. Для охвата всей строки или колонки в Excel, нажмите на её номер или букву. Если вы собираетесь группировать ряд 23, выберите число «23» слева, если столбец F —символ «F» над таблицей.
  4. Несвязный диапазон (ячейки, которые располагаются далеко друг от друга) скомбинировать нельзя.
  5. После того как вы выбрали категории, откройте раздел «Данные» наверху окна.
  6. Найдите панель «Структура».
  7. Нажмите на маленькую чёрную стрелочку рядом с кнопкой «Группировать». Там будет нужная опция.
  8. Если вы выделили только строки или только столбцы, результат будет сразу.
  9. При работе с прямоугольной областью, программа спросит, к чему применять атрибут — к рядам или к колонкам. Можно сгруппировать и те и другие. Но данные позиции придётся объединять отдельно. Однако в группу попадёт не сам прямоугольник, а все ряды и столбики, входящие в него.

По завершении этих манипуляций наверху или слева от рабочей области Excel появится линия с пиктограммой в виде минуса. Это графическое обозначение соединённых категорий. Луч «охватывает» данные объекты и показывает, где они находятся.

Как работать в Excel

Можно создавать сложные комбинации, вложенные друг в друга.

  1. Добавьте новую группу в уже существующую. Просто выделите любые строки или столбцы, которые уже находятся в ней, и нажмите «Группировать».
  2. Рядом с «главным» лучом появится другой — поменьше. Вверху слева от рабочей области отобразятся цифры. Это уровни. Первый обозначает родительскую структуру, второй — вложенную и так далее.
  3. Можно создать максимум восемь уровней.

Если объект закреплен, прежде чем переместить, удалить или изменить этот объект, его необходимо открепить.

Нажмите объект, чтобы его выбрать, либо выберите несколько объектов.

Выполните любое из описанных ниже действий.

Закрепить. Выберите «Расстановка» > «Закрепить» (меню «Расстановка» расположено у верхнего края экрана).

Открепить. Выберите «Расстановка» > «Открепить».

Либо выберите один или несколько объектов. В боковой панели «Формат» нажмите вкладку «Расстановка», затем нажмите «Закрепить» или «Открепить» у нижнего края боковой панели.

Совет. Можно добавить в панель инструментов кнопки для закрепления и открепления объектов.

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