Как группировать и разгруппировать данные в excel

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Функция «Группировать» в таблицы получился такой. – перечень данных
в Excel по группы выделить в планируем выводить наименование в которые входитПри защите листа,: Чеботарь, Вам уже
Seasons таблице. быстрого доступа, выберите быть объединены в последовательность чисел или чем ранее. с полями столбца, строки, т.д.Excel.Плюсиками у строк и подраздела. На закладке разделам. названиям, т.д.. ней определенные ячейки, и итоги. В большое количество строк раскрытие и скрытие, gling подсказал, жмите(Сезоны).Мастер сводных таблиц и пункт группу. Есть целый дат. Иногда группаЕсли данные, с которымиStarting At Читайте об этомМы рассмотрели, как столбцов раскрываем нужный

«Данные» в разделеГруппировать в которые вы собираетесь

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

новой ячейке вводим или столбцов, актуальным

группированных строк и маленький квадратик соСводная таблица будет сформирована диаграммMore Commands ряд причин, почему становится группой просто Вы работаете, являются(Начиная с), статью “Суммирование ячеек
можно установить структуру раздел. А цифрами «Структура» нажимаем наExcel сгруппировать отдельно. Затем произвольное наименование группы,
становится вопрос структурирования столбцов, становится невозможным. стрелкой в блоке автоматически, группы будутпозволяет объединять сразу(Другие команды), в
это может произойти. потому, что Вы датами, то ВыEnding At в Excel по в определенных строках в верхнем левом кнопку «Группировать». Получилосьможно несколькими способами следует провести одну подходящее к ней данных. В Экселе

Есть ли способ “Структура” созданы как поля несколько диапазонов в открывшемся диалоговом окне

Одна из причин: так решили. В сможете использовать похожий

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

(По) и условию”.

или столбцах, чтобы углу таблицы разворачиваем так. – сгруппировать отдельные из тех процедур,
по контексту. этого можно достичь решить проблему?ЧеботарьReport Filter одной сводной таблице. установите группу
если Вы пытаетесь этом случае можно приём. Чтобы сгруппироватьByHelen Bradley рассказывает, как понять принцип работы или сворачиваем все

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

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

(Фильтры) в спискеВAll Commands создать собственную группировку, создать свою собственную информацию по датам,(С шагом). В группировать данные в группировки. уровни или разделы. появилась кнопка со или строки и выше, в зависимости сгруппировать, кроме итоговой соответствующих элементов. Этот одна тема!Vinkelman

полей сводной таблицы,Мастере сводных таблиц и(Все команды) и то должны выделить группировку. Откройте лист кликните правой кнопкой данной ситуации Excel сводной таблице ExcelЕсли нужно создатьНапример, чтобы развернут знаком «Минус» -

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

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

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

ShAMВ Excel крестики пожелаете, можете перетащитьпервым делом нужно

найдите элемента для создания кликните по первому в столбце или элементы по 10 обобщения и анализа. таблице, сгруппировать сразу нажимаем на цифру Если нажмем на формулами и таблицу или со строками.«Данные» данные, но и: Вы название темы группировок строк снизу эти поля в

выбрать, какие диапазоны

lumpics.ru⁪>

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

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

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

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

Вы можете создавать сложные соединения, вложенные друг в друга.

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

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

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

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

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

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

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

Создание сводной таблицы вручную

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

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

На экране появится диалоговое окно создания сводной таблицы, и будут выделены все данные в списке, в котором находится курсор ячейки (вокруг списка появится мерцающая пунктирная рамка). Можете откорректировать этот диапазон в текстовом поле Таблица или диапазон, если рамка не включает какие-либо данные, которые следует консолидировать. По умолчанию Excel создает сводную таблицу на новом рабочем листе, добавляемом в книгу. Если хотите, чтобы сводная таблица была создана на том же листе, где находятся исходные данные, щелкните на кнопке На существующий лист, а затем в поле Диапазон укажите ячейки, в которых будет находиться сводная таблица. (Проверьте, не будет ли перекрывать создаваемая сводная таблица данные, уже существующие на рабочем листе.)

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

После того как будет определен источник данных и указано место расположения сводной таблицы, щелкните на кнопке ОК, и программа добавит пустую сетку для новой таблицы, а также откроет в правой части области рабочего листа панель Список полей сводной таблицы. Эта панель разделена на две части. Вверху находится список полей источника данных, которые можно добавить в сводную таблицу, а внизу — область, разделенная на четыре зоны: ФИЛЬТРЫ, СТРОКИ, СТОЛБЦЫ и ЗНАЧЕНИЯ.

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

ФИЛЬТРЫ. Здесь содержатся поля, позволяющие фильтровать данные таблицы. Так, например, если перетащить сюда поле года, то можно будет отображать в таблице сводные данные для каждого конкретного года, представленного в списке данных.
СТОЛБЦЫ. Здесь содержатся поля, определяющие данные, которые отображаются в столбцах сводной таблицы.
СТРОКИ. Здесь находятся поля, определяющие данные, которые отображаются в строках сводной таблицы.
ЗНАЧЕНИЯ. Здесь содержатся поля, определяющие, какие данные будут отображаться в ячейках таблицы, т.е. значения, консолидируемые в последнем столбце (по умолчанию суммируемые).

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

В этой сводной таблице в качестве фильтра из списка данных было выбрано поле Дата. Поле Категория я назначил меткам строк. В качестве значений были выбраны поля Расход и Доход.

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

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

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

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

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

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

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

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

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

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

Как сбросить настройки по умолчанию в Excel?

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

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

Включите JavaScript

Как сбросить/восстановить код Visual Studio до настроек по умолчанию

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

Обратите внимание: перед применением изменений закройте открытые файлы Excel

Метод 1: сброс Excel с помощью редактора реестра

Редактор реестра — это графическая программа, в которой хранятся все конфигурации, связанные с операционной системой, программным обеспечением и настройками.

Изменения в файле реестра могут быть повреждены в любое время

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

Шаги следующие:

1 Нажмите Ctrl + R , чтобы открыть Run.

2 Введите regedit и нажмите Enter

3 Во всплывающем окне нажмите кнопку « Да ». Это откроет реестр, как показано ниже.рис. Введите regedit в поле «Выполнить» и нажмите кнопку «ОК».

4 На правой панели разверните HKEY_CURRENT_USER. рис. Развернуть HKEY ТЕКУЩИЙ ПОЛЬЗОВАТЕЛЬ

5 Развернуть Программное обеспечение рис. Расширенное программное обеспечение

6 Развернуть Microsoft рис. Развернуть Microsoft

7 Развернуть Office рис. Развернуть папку Office

В верхней строке поиска убедитесь, что вы находитесь внутри следующего пути:

HKEY_CURRENT_USER\Software\Microsoft\Office

8 Здесь вы можете получить другую версию офиса, например 14.0, 15.0 ,16.0 и скоро.

Если вы используете Excel 2007 , затем разверните 12,0
Если вы используете Excel 2010 , затем разверните 14,0
, если вы используете Excel 2013 , то Expand 15,0
.

Здесь я собираюсь сбросить Excel 2019, поэтому я расширим 16.0, как показано ниже.

9 Затем щелкните правой кнопкой мыши Excel Папка и выберите Удалить ит.рис. Щелкните правой кнопкой мыши Desire Office Version, а затем нажмите Удалить 9. 0003

10 Закройте редактор реестра

11 Откройте Excel, и вы увидите, что Excel сброшен до настроек по умолчанию.

Метод 2: сброс Excel с помощью Powershell

Powershell — мощный инструмент, и мы будем использовать командлеты для удаления этой папки из реестра. Мы удалим папку Excel с помощью Remove-Item. Шаги следующие:

1 Нажмите Win + R, чтобы открыть команду «Выполнить». 2 Введите Powershell и нажмите Enter. Это откроет программу синего экрана, которая является программой PowerShell. 3 Введите команду ниже и замените на установленную версию Office

Remove-Item -Path HKCU:\Software\Microsoft\Office\\Excel -Recurse

Если вы используете Excel 2007, введите:

Remove-Item -Path HKCU:\Software\Microsoft\Office\ 12.0 \Excel -Recurse

Если вы используете Excel 2010, введите:

Remove-Item -Path HKCU:\Software\Microsoft\Office\ 14.0 \Excel -Recurse

Если вы используете Excel 2013, введите:

Remove-Item -Path HKCU:\Software\Microsoft\Office\ 15. 0 \Excel -Recurse

Если вы используете Excel 2016 или более позднюю версию, введите:

Remove-Item -Path HKCU:\Software\Microsoft\ Office\ 16.0 \Excel -Recurse

Здесь я использую Excel 2019, поэтому я буду использовать последнюю команду, как показано ниже:

Что делает эта команда? Эта команда удаляет все элементы, рекурсивно присутствующие в пути «HKCU:\Software\Microsoft\Office\\Excel» файла реестра.

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

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

Post Views: 6

Автоматическая структуризация

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

Благодаря этому стол не занимает много места.

Это правда, что программа не всегда структурирует то, что нужно. Тогда придется прибегнуть к ручной настройке. Однако, если вы имеете дело с формулами, автогруппировка вам, скорее всего, подойдет. Для его применения команда «Группировать», выбираем подпункт «Создать структуру» — готово.

Как отменить созданный вручную пул, вы узнали ранее. Как это сделать после использования автоматического метода? В той же вкладке «Разгруппировать» нажмите «Очистить структуру».

Вложенные группы

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

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

Примечание! В квартал входит 3 месяца, а в полугодие 6. При составлении таблицы для примера — это правило было нарушено. Здесь в квартал входит 4 месяца, а в полугодие 8, что является фактическим нарушением принятых норм.

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

Excel группировка данных по значению

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

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