Группировка строк и столбцов в Excel

Работать с огромным количеством инфы в Excel бывает трудно. Бессчетные строчки соединяются во время просмотра. Отыскать что-то в таком массиве фактически нереально. Не говоря уж о том, чтоб редактировать таблицу либо применять данные из неё. Но можно соединить позиции, разбить их на отдельные списки либо совсем «упрятать». Разберитесь, как создать группировку в Excel, чтоб структурировать либо скрывать ряды. Они останутся в документе, но отображаться будут лишь тогда, когда вы на их нажмёте. Так вы можете тихо поработать с необходимыми ячейками, не отвлекаясь на не применяемые свойства.

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

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

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

Отметьте нужные ряды.
Чтоб выделить огромное количество позиций, находящихся рядом, кликните по первой и крайней группы в спектре с зажатой кнопкой Shift. Либо «обведите» их курсором.
Для охвата всей строчки либо колонки в Excel, нажмите на её номер либо буковку. Если вы собираетесь группировать ряд 23, изберите число «23» слева, если столбец F —знак «F» над таблицей.
Бессвязный спектр (ячейки, которые размещаются далековато друг от друга) скомбинировать недозволено.
Опосля того как вы избрали группы, откройте раздел «Данные» наверху окна.
Найдите панель «Структура».
Нажмите на небольшую чёрную стрелочку рядом с клавишей «Группировать». Там будет подходящая функция.
Если вы выделили лишь строчки либо лишь столбцы, итог будет сходу.
При работе с прямоугольной областью, программка спросит, к чему использовать атрибут — к рядам либо к колонкам. Можно сгруппировать и те и остальные. Но данные позиции придётся соединять воединыжды раздельно. Но в группу попадёт не сам прямоугольник, а все ряды и столбики, входящие в него.

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

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

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

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

Ах так группировать в Excel и скрывать объекты:

Соедините какие-нибудь строчки.
Слева от их покажется луч с «минусом» в начале.
Нажмите на иконку с минусом. Она перевоплотится в плюс. И данные ряды свернутся.
Чтоб развернуть их, снова кликните на начало луча.

Есть ещё один метод.

Если вы сделали в Excel множественную группировку с огромным количеством структур, в области слева вверху покажутся числа.
Допустим, у вас семь уровней групп. Кликните на цифру «3». Четвёртый, 5-ый, 6-ой и седьмой подуровни свернутся.
Чтоб раскрыть перечень, кликните на его номер.
Если вы нажмёте на цифру «1», спрячутся все объединённые позиции.

Также это можно создать на панели управления, на которой находится клавиша «Группировать»:

Поставьте курсор-ячейку в какую-нибудь группу.
Найдите в разделе «Структура» клавиши «Показать детали» и «Скрыть детали». Если вы кликните на первую, категория развернётся, если на вторую — свернётся.

Как убрать группировку?

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

Выделите скомбинированные группы.
На панели «Структура» нажмите «Разгруппировать».
Если вы отметите лишь один ряд, он будет исключён из группы. А категория, в которой он находился, разделится надвое.
Чтоб убрать сходу все композиции, кликните «Удалить структуру». Они пропадут из документа.

Промежный результат

Колонки можно группировать с помощью специальной функции Excel «Промежный результат». Она создаёт структуры и подсчитывает значения снутри их. Разберёмся на примере.

Допустим, в таблице, в которой находятся данные по продажам костюмов, есть столбцы «Наименование продукта», «Размер», «Количество на складе» и «Стоимость».
Отсортируйте пункты в сетке по наименованию продукта. К примеру, в строчки 2–13 забейте информацию по группы «Сероватые пиджаки», в 14–27 — «Белоснежные рубахи» и тому схожее.
Выделите все столбики.
В меню «Данные» на панели «Структура» нажмите «Промежный результат». Раскроется окно с несколькими полями.
Откройте выпадающий перечень «При любом изменении в». Там будут все ваши группы. Изберите «Наименование продукта». Так система соединит воединыжды все строчки с схожими наименованиями. Если вы всё отсортировали, будут группы «Пиджаки», «Рубахи» и так дальше.
В разделе «Добавить итоги по» поставьте галочку в «На складе».
В перечне «Операция» изберите «Сумма».
Нажмите «OK».
Информация будет разбита на группу по категориям «Наименование». Под каждой структурой покажется подсчёт: полное количество того либо другого продукта на складе.
Если вы отметите в области «Добавить итоги по», например, стоимость, Excel укажет раздельно стоимость пиджаков и рубашек.
Так можно подсчитать количество каких-либо позиций, среднее арифметическое, производное, смещённое отклонение и даже дисперсию. Все функции доступны в выпадающем перечне «Операция».
Чтоб избавиться от этих структур, в окне «Промежные итоги» нажмите «Убрать всё».

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

Как автоматом группировать строчки в Excel

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

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

Интересно почитать: Excel слишком много различных форматов ячеек

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

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

Автоматическая группировка

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

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

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

Изберите всякую из ячеек, которые содержат данные в вашем наборе данных.
Изберите вкладку «Данные» в меню Excel.
В разделе «Схема» щелкните небольшую стрелку под значком «Группа».
Изберите «Авто контур».

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

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

Перейдите на вкладку «Данные».
В разделе «Контур» щелкните значок стрелки в правом нижнем углу.
Раскроется меню «Опции контура».
Снимите флаг «Сводные строчки под деталями».
Нажмите «ОК».

Направьте внимание на изменение уровней группировки слева от столбца А.

Сейчас, когда вы удачно сгруппировали свои данные, вы сможете применять клавиши «+» и «-», чтоб показать и скрыть детали для каждой из групп. Чтоб свернуть либо развернуть всю группу, просто нажмите на числа в высшей части столбца группировки.

Ручная группировка

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

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

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

Чтоб вручную сгруппировать ваши данные:

Изберите строчки, которые вы желаете поместить в группу.
Перейдите на вкладку «Данные».
В разделе «Схема» щелкните значок «Группа». Вы также сможете надавить на черную стрелку и избрать «Группировать…».
«
Покажется меню «Группа».
Изберите «Rows» и нажмите «OK».

Сейчас, когда ваши данные сгруппированы, уровни группы покажутся слева от столбца А.

Выбор группы

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

Для Ванкувера поначалу изберите строчки от 2 до 7. Потом сгруппируйте их вручную, как описано в прошлом разделе.

Для Нью-Йорка сгруппируйте строчки с 9 по 14. Сейчас, когда вы свернете эти группы, вы увидите итоговые суммы по любому из городов, не обращая внимания на числа разбивки.

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

Время от времени для вас может пригодиться выяснить, сколько всего продуктов в сумме. И вас не интересует, как они распределены по городку и типу хранилища. Для этого просто сгруппируйте строчки со 2 по 15 и нажмите номер 1 в столбце группировки. Чтоб узреть полный разбив, нажмите на номер 4.

Мудро группируйте свои данные

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

Вы в первый раз узнаете, как работает группировка в Excel? Считаете ли вы это полезным? Если у вас есть какие-либо советы по этому вопросцу либо хоть какой иной вариант в Excel, пожалуйста, поделитесь в комментах ниже.

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

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

Приветствую всех, дорогие читатели блога TutorExcel.Ru!

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

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

Мы разберем 3 главных варианта группировки полей сводной таблицы в зависимости от типа данных:

Дата/время;
Числа;
Случайная группировка.

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

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

Разглядим ординарную таблицу, где в качестве данных как раз находятся достойные внимания нам срезы данных — даты, числа и наименования:

Интересно почитать: Как сделать одинаковый формат ячеек в excel

А сейчас приступим к определенным примерам.

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

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

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

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

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

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

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

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

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

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

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

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

С числами тоже все оказалось не так трудно, перейдем к крайнему варианту.

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

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

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

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

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

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

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

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

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

Спасибо за внимание!
Если у вас есть вопросцы по теме — входите и пишите комменты.

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

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

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

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

Согласитесь, что обычное перечисление продаж не дает нам довольно полезной инфы, чтоб создать какие-то выводы и предложения.

Группировка по числовому значению.

Давайте попробуем подсчитать, с какой скидкой у нас происходит больше всего покупок.

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

В итоге получаем длиннющий перечень покупок с процентом скидки по каждой из их.

И на какой спектр скидок нам идеальнее всего ориентироваться? Вот тут нам и понадобится группировка.

Устанавливаем курсор на то поле, по которому желаем сгруппировать числа. Во вкладке «Анализ» находим «Группа» (1) и дальше действуем так, как показано на рисунке чуток ниже. Программка автоматом описывает наибольшее и малое значение (3) и по дефлоту дает шаг группировки равный единице, другими словами 1%. Думаю, есть смысл прирастить его до 5%. (4)

К огорчению, процентный формат чисел тут не учитывается, потому в уме множьте всё на 100.

Получаем сжатый и довольно понятный итог. Для наглядности можно добавить сюда к тому же диаграмму. Для этого на вкладке «Анализ» нажмите «Сводная диаграмма» и изберите более пригодный для вас вид. И дальше ее можно корректировать буквально так же, как и обыденную диаграмму Excel.

Больше всего покупок – со скидкой от 6,6% до 11,6% (на диаграмме это интервал 0,066 – 0,116).

Интересно почитать: Excel фильтр по значению в ячейке

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

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

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

Как и ранее, добавим для наглядности сводную диаграмму.

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

Случайная группировка.

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

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

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

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

Используем такую увлекательную новинку, показавшуюся в Office 2013, как срезы.

На вкладке «Анализ» нажмите клавишу «Вставить срез» и в показавшемся окне изберите «Код покупателя».

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

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

Согласитесь, это довольно мощнейший инструмент анализа.

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

Выделим ячейки с необходимым продуктом. Потом, как это показано на снимке экрана, нажмём на ленте «Группировка по выделенному». Итог вы видите ниже.

Избранные нами наименования продукта на данный момент объединены в группу. И по ней мы можем созодать все нужные расчеты.

Работа с группировками.

Вы сможете свернуть и развернуть группу сводной таблицы, нажав на знак плюс / минус (+/-) слева от ее наименования.

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

Для этого изберите интересующую вас группу и два раза щелкните ячейку «Итого». Данные, образовавшие этот результат, будут извлечены на новейший лист в вашей рабочей книжке.
Представим, мы желаем получить детализированную информацию о том, как образовалась сумма продаж равная 32116 по покупателю с кодом «Голубой» за 1 квартал. Вы ее видите на прошлом снимке экрана чуток выше.

Щелкаем по данной цифре, и получаем на новеньком листе вот что:

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

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

Устранение ошибок

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

Это может произойти по ряду обстоятельств.

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

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

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

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

Может быть, для вас также полезно:

9 методов сопоставить две таблицы в Excel и отыскать разницу — В этом руководстве вы познакомитесь с разными способами сопоставления таблиц Excel и определения различий меж ними. Узнайте, как просматривать две таблицы рядом, как применять формулы для сотворения отчета о различиях, выделить…
Как сопоставить два столбца на совпадения и различия — На чтение данной статьи у вас уйдет около 10 минут, а в последующие 5 минут (либо даже резвее) вы просто сравните два столбца Excel на наличие дубликатов и выделите отысканные…
Как выделить цветом повторяющиеся значения в Excel? — В этом руководстве вы узнаете, как показывать дубликаты в Excel. Мы разглядим разные способы затенения дублирующих ячеек, целых строк либо поочередных повторений с внедрением условного форматирования. Ранее мы изучили разные…
Как создать сводную таблицу в Excel – пошаговая {инструкция} для чайников — В этом руководстве вы узнаете, что такое сводная таблица, и отыщите подробную аннотацию, как по шагам создавать и применять её в Excel. Если вы работаете с большенными наборами данных в…
Как поменять цвет ячейки в зависимости от значения в Excel? — В данной статье вы отыщите 13 примеров как поменять цвет фона ячеек в зависимости от значения в Excel 2016, 2013 и 2010. Не считая того, вы узнаете, как применять формулы Excel…
Как создать условное форматирование в Excel? Аннотации с примерами. — В данной статье вы отыщите огромное количество стремительных методов как создать условное форматирование строк, столбцов и отдельных ячеек в MS Excel 2016, 2013 и 2010. Мы разглядим, как можно применить различное…