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

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

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

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

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

Группировать строки в Excel

Как сгруппировать строчки в Excel при помощи разворачивания / сворачивания?

К примеру, поглядите на данные ниже.

Как сгруппировать строки в Excel, пример 1.0

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

Сделайте последующие шаги, чтоб сгруппировать строчки в Excel.

Шаг 1: Поначалу сделайте промежный результат, как показано ниже.

Пример 1.1

Шаг 2: Сейчас изберите 1-ые строчки штата (Калифорния), исключая промежные итоги.

Как сгруппировать строки в Excel, пример 1.2

Шаг 3: Перейдите на вкладку ДАННЫЕ и изберите опцию «Группа».

Пример 1.3

Шаг 4: Щелкните раскрывающийся перечень в Excel «Группа» и опять изберите «Группа».

Как сгруппировать строки в Excel, пример 1.4

Шаг 5: Сейчас он спросит вас, группировать ли строчки либо столбцы. Так как мы группируем «Ряды,»Изберите строчки и нажмите ОК.

Шаг 6: В тот момент, когда вы нажмете «ОК», вы увидите линию стыка с левой стороны.

Как сгруппировать строки в Excel, пример 1.6

Нажми на «МИНУС»Значок и узреть чудо.

Пример 1.7

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

Сейчас опять изберите город «Колорадо»И нажмите«Группа»Вариант.

Как сгруппировать строки в Excel, пример 1.8

Сейчас он будет сгруппирован для «Колорадо» штат.

Пример 1.9.0

Группировать при помощи сочетания кнопок

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

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

Как сгруппировать строки в Excel, пример 1.10

Сейчас нажмите жаркую кнопку SHIFT + ALT + кнопка со стрелкой на право чтоб сгруппировать эти строчки.

 Пример 1.11

Выше мы лицезрели, как сгруппировать данные и как сгруппировать строчки с опцией развертывания и свертывания при помощи значков ПЛЮС и МИНУС.

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

Умопомрачительно, не правда ли? Используя «Авто контур,”Мы можем автоматом группировать данные.

Пример # 1 — Внедрение Auto Outline

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

Как сгруппировать строки в Excel, пример 2

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

Пример 2.1

В тот момент, когда вы нажмете на эту опцию «Auto Outline», она сгруппирует все строчки, которые находятся над строчкой промежных итогов.

Как сгруппировать строки в Excel, пример 2.2

Как это круто . Весьма круто, не правда ли ??

Пример # 2 — Внедрение промежных итогов

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

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

К примеру, до сотворения промежного итога у нас были данные, подобные приведенным ниже.

Пример 2.3

На вкладке «Данные» у нас есть функция «Промежный результат«Рядом с»Группа»Вариант.

Как сгруппировать строки в Excel, пример 2.4

Щелкните этот параметр, выбрав всякую из ячеек спектра данных; поначалу будет показан вариант ниже.

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

Потом нам необходимо избрать тип функции, так как мы добавляем все значения, чтоб избрать «Сумма»В Excel.

Сейчас изберите столбцы, которые необходимо суммировать. Нам необходимо резюме «Реализации и стоимостьСтолбцы, потому выбирайте то же самое. Нажмите «ОК».

Как разгруппировать строчки в excel

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  • выделите все строчки первого раздела (не включайте в выделение заглавие раздела и итоговую строчку);
  • на вкладке Данные откройте перечень Группировать и изберите одноименную команду. В итоге слева от таблицы возникает зона сделанной структуры.

Аналогично сгруппируйте строчки второго и следующих разделов.

Если щелкнуть по кнопочке 1, то раздел свернется до наименования и итоговой строчки. Чтоб узреть содержимое раздела, щелкните по кнопочке 2.

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

Сейчас можно щелкнуть по кнопочке 2 и свернуть лишь группы (клавишей 3 все можно будет развернуть).

Если группа была сформирована безуспешно, то ее можно разгруппировать. Для этого щелкните по кнопочке, соответственной концу группы в структуре (это быть может +, если группа свернута, либо , если видна вся структура) и по кнопочке Разгруппировать. На рисунке приведен пример для группы "Кладка кирпича".

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

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

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

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

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

    Перебегаем во вкладку «Данные».

На этом настройка характеристик группировки в Эксель завершена.

Группировка по строчкам

Выполним группировку данных по строчкам.

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

Выделяем строчки, которые необходимо сгруппировать, не считая итоговой строчки. Перебегаем во вкладку «Данные».

На ленте в блоке инструментов «Структура» кликаем по кнопочке «Группировать».

На этом создание группы завершено. Для того, чтоб свернуть её довольно надавить на символ «минус».

Чтоб поновой развернуть группу, необходимо надавить на символ «плюс».

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

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

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

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

Группа готова. Аналогично, как и при группировании столбцов, её можно сворачивать и разворачивать, нажимая на знаки «минус» и «плюс» соответственно.

Создание вложенных групп

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

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

Интересно почитать:  Excel объединение строк

Разгруппирование

Если вы желаете переформатировать либо просто удалить группу, то её необходимо будет разгруппировать.

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

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

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

Многоуровневая группировка строк в excel

Структурируемые данные должны быть представлены в формате перечня, в котором 1-ая строчка всякого столбца содержит подпись, другие строчки — однотипные данные; пустые строчки либо столбцы в перечне отсутствуют.

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

Располагайте итоговые строчки выше либо ниже каждой группы детализированных строк. Для этого опосля каждой группы вставьте по пустой строке и в их используйте итоговые функции, к примеру СУММ либо СРЗНАЧ. Для получения лучших результатов итоговые строчки должны содержать формулы, ссылающиеся на ячейки во всех строчках. В показанном на рисунке 8.8 примере имеется строчка под сведениями каждой группы продукта. К примеру, данные по продажам фармацевтических средств в строчках с 5 по 9 суммируются в строке 10. Также можно структурировать данные, в которых итоговые строчки содержат описательный текст и другие данные.

Рис. 8.8. Фрагмент окна со структурированными данными

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

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

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

Автоматическое создание структуры

Если данные на листе обобщены формулами, которые употребляют функции, к примеру СУММА, Microsoft Excel дозволяет автоматом структурировать данные. Итоговые данные должны размещаться рядом с подробными данными.

Для сотворения автоматической структуры необходимо выделить спектр ячеек, для которых нужно сделать структуру (для структурирования листа полностью указать всякую ячейку) и отдать команду меню Данные => Группа и структура => Создание структуры. При вставке автоматических промежных итогов в перечень, представленный в виде строк, Microsoft Excel автоматом структурирует лист, давая возможность показать ровно столько подробной инфы, сколько нужно.

Создание структуры вручную

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

Для сотворения структуры листа вручную, нужно выделить строчки либо столбцы, которые необходимо соединить в одну группу, и отдать команду меню Данные => Группы и структура => Группировать (рис.8.9). Опосля этого рядом с детализированными данными Одежка показаться знак структуры. Продолжать выделение и группировку строк либо столбцов сведений и выполнение команды Группировать необходимо до того времени, пока не будут сделаны все нужные уровни структуры.

Рис. 8.9. Фрагмент окна для сотворения группы Одежка

Настройка структурирования со стилями

Автоматические стили можно применить конкретно при структурировании либо опосля него. Для структурированных строк Microsoft Excel употребляет такие стили, как «Уровеньстрока_1» и «Уровеньстрока_2». Для структурированных столбцов Microsoft Excel употребляет такие стили как «Уровеньстолб_1» и «Уровеньстолб_2». Стили употребляют полужирное, курсивное и другое форматирование текста, чтоб облегчить различение итоговых строк в данных.

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

Если требуется задать стили структуры для автоматического внедрения к новейшей структуре, необходимо отдать команду меню Данные => Группа и структура => Настройка и установить флаг Автоматические стили. Если при разработке структуры не требуется использовать автоматические стили, их можно применить опосля сотворения структуры. Для этого нужно выделить данные, к которыми используются стили, отдать команду меню Данные => Группа и структура => Настройка в открывшемся диалоговом окне Настройка (рис. 8.10) установить флаг Автоматические стили и надавить клавишу Применить стили. Клавиша Сделать этого диалогового окна делает структуру листа. В итоге к итоговым строчкам будет использовано форматирование (рис.8.11)

Рис.8.10. Применение стилей к структуре

Рис. 8.11. Итог внедрения автоматических стилей к структуре

Удаление структуры

При удалении структуры никакие данные не удаляются. Для выполнения удаления структуры на подходящем листе нужно отдать команду меню Данные => Группа и структура => Удалить структуру. Если строчки либо столбцы все равно останутся сокрытыми, то можно просто показать сокрытые строчки либо столбцы средствами Excel (выделить видимые заглавия и отдать команду меню Формат => Строчка либо Столбец => Показать).

Выделенные элементы структуры также можно разгруппировать без удаления всей структуры. Для этого, удерживая нажатой SHIFT щелкнуть клавишу либо группы, а потом отдать команду меню Данные => Группа и структура => Разгруппировать. На рис. 8.12 и 8.13 приведен пример удаления структуры для группы Иванова.

Рис. 8.12 . Выделение элемента структуры Иванова и выбор команды для разгруппирования

Рис. 8.13. . Вид окна при разгруппированной группе Иванова

Интересно почитать:  Как в excel установить высоту строки

Чтоб скрыть структуру, не удаляя ее, нужно показать все данные, нажав клавишу с большим числом в области структуры , и снять флаг Знаки структуры вкладки Вид диалогового окна Характеристики (меню Сервис).

Базы Pandas №2 // Агрегация и группировка

Во 2-м уроке управления по работе с pandas пойдет речь о агрегации (min, max, sum, count и дргуих) и группировке. Это пользующиеся популярностью способы в аналитике и проектах data science, потому удостоверьтесь, что осознаете все в деталях!

Примечание: это управление, потому рекомендуется без помощи других писать код, повторяя аннотации!

Агрегация данных — теория

Агрегация — это процесс перевоплощения значений набора данных в одно значение. К примеру, у вас есть последующий набор данных…

animal water_need
zebra 100
lion 350
elephant 670
kangaroo 200

…простой способ агрегации для него — суммирование water_needs , другими словами 100 + 350 + 670 + 200 = 1320. Как вариант, можно посчитать количество звериных — 4. Теория не так сложна. Но пора перебегать к практике.

Агрегация данных — практика

Где мы тормознули в крайний раз? Открыли Jupyter Notebook, импортировали pandas и numpy и загрузили два набора данных: zoo.csv и article_reads . Продолжим с этого же места. Если вы не прошли первую часть, вернитесь и начните с нее.

Начнем с набора zoo . Он был загружен последующим образом:

 набор данных zoo

Далее сохраним набор данных в переменную zoo .

Сейчас необходимо сделать 5 шагов:

  1. Посчитать количество строк (количество звериных) в zoo .
  2. Посчитать общее значение water_need звериных.
  3. Отыскать меньшее значение water_need .
  4. И самое огромное значение water_need .
  5. В конце концов, среднее water_need .

Агрегация данных pandas №1: .count()

Посчитать количество звериных — то же самое, что применить функцию count к набору данных zoo :

функция count pandas

А что же это все-таки за строчки? По сути, функция count() считает количество значений в каждой колонке. В случае с zoo было 3 колонки, в каждой из которых по 22 значения.

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

В этом случае итог будет даже лучше, если написать последующим образом:

Также будет выбрана одна колонка, но набор данных pandas перевоплотится в объект series (а это означает, что формат вывода будет различаться).

функция count для колонки

Агрегация данных pandas №2: .sum()

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

функция sum для колонки

Просто из любопытства можно испытать отыскать сумму во всех колонках:

функция sum pandas

Примечание: любопытно, как .sum() превращает слова из колонки animal в строчку заглавий звериных. (К слову, это соответствует всей логике языка Python).

Агрегация данных pandas №3 и №4: .min() и .max()

Какое меньшее значение в колонке water_need ? Найти это нетрудно:

Наименьшее значение в колонке

То же и с наибольшим значением:

Максимальное значение в колонке

Агрегация данных pandas №5 и №6: .mean() и .median()

В конце концов, стоит посчитать среднестатистические характеристики, к примеру среднее и медиану:

Cреднее значение в колонке

Медиана в колонке

Это было просто. Намного проще, чем агрегация в SQL.

Но можно усложнить все незначительно при помощи группировки.

Группировка в pandas

Работая аналитиком либо спецом Data Science, вы наверное повсевременно будете заниматься сегментациями. К примеру, отлично знать количество нужной воды ( water_need ) для всех звериных (это 347,72 ). Но удобнее разбить это число по типу звериных.

Вот облегченная репрезентация того, как pandas производит «сегментацию» (группировку и агрегацию) на базе значений колонок!

Функция .groupby в действии

Проделаем эту же группировку с DataFrame zoo .

Меж переменной zoo и функцией . mean() необходимо вставить ключевое слово groupby :

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

По-прежнему, pandas автоматом проведет расчеты . mean() для оставшихся колонок (колонка animal пропала, поэтому что по ней проводилась группировка). Можно либо игнорировать колонку uniq_id либо удалить ее одним из последующих методов:

zoo.groupby(‘animal’).mean()[[‘water_need’]] — возвращает объект DataFrame.

zoo.groupby(‘animal’).mean().water_need — возвращает объект Series.

Можно поменять способ агрегации с . mean() на хоть какой изученный ранее.

Проверить себя №1

Вернемся к набору данных article_read .

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

Если все готово, вот 1-ое задание:

Какой источник употребляется в article_read почаще других?

Верный ответ:
Reddit!

Получить его можно было при помощи кода:

Взять набор данных article_read , сделать сегменты по значениям колонки source ( groupby(‘source’) ) и в конце концов посчитать значения по источникам ( .count() ).

Какой источник используется в article_read чаще остальных

Также можно удалить ненадобные колонки и сохранить лишь user_id :

Проверить себя №2

Вот еще одна, наиболее непростая задачка:

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

Верный ответ: Reddit (источник) и Азия (тема) с 139 чтениями.

Вот Python-код для получения результата:

Какие самые частые источник и страна для пользователей country_2

Вот короткое разъяснение:

Сначала отфильтровали юзеров из country_2 ( article_read[article_read.country == ‘country_2’] ). Потом для этого подмножества был применен способ groupby . (Да, группировку можно производить для нескольких колонок. Для этого их наименования необходимо собрать в перечень. Потому квадратные скобки употребляются меж круглыми. Это что касается части groupby([‘source’, ‘topic’]) ).

А функция count() — заключительный элемент пазла.

Итого

Это была 2-ая часть управления по работе с pandas. Сейчас вы понимаете, что агрегация и группировка в pandas— это обыкновенные операции, а применять их придется нередко.

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

В последующем материале вы узнаете о 4 всераспространенных способах форматирования данных: merge , sort , reset_index и fillna .

Ссылка на основную публикацию
Adblock
detector