Как сделать группировку строк в excel с плюсом сверху

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

• Выделите ячейки, которые следует свернуть. После сворачивания информации, видимой будет оставаться только шапка и результат.
• Выполните «Данные/Группировать/Группировать»

• В ответ на появившийся запрос определите способ выравнивания: ряды или колонки.

Советы и рекомендации при работе с группировкой в Эксель

Если авто-структура не работает должным образом, выберите обычный способ: он может оказаться проще и больше соответствовать информации, содержащейся в таблице Excel. Команда не может использоваться на листах с совместным доступом, при сохранении в htm/html или при защите документа, в последнем случае пользователь-получатель не сможет сворачивать/разворачивать ряды.

Показать видеоинструкцию

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Создать новую строку над или под теми, которые мы хотим группировать. Здесь все зависит от того, какой способ отображения итогов был выбран на предыдущем этапе. Следующий шаг – создание названия таблицы в левой верхней ячейке добавленной строки. Это будет название группы, в которую будут объединяться ячейки, общие по определенному признаку.
Выделить все ячейки в области под новосозданной строкой или над ней (в зависимости от того, что мы сделали на первом этапе). После этого ищем кнопку «Структура» на вкладке с данными и там находим опцию «Группировать»

Важно при этом не нажимать на стрелочку или название команды во всплывающей панели, а именно по значку

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

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

О том, что строки были сгруппированы, мы можем понять по знаку «минус» на координатной панели. Это нам говорит о том, что данные были раскрыты. Теперь мы можем их скрыть, нажав на этот значок или кликнув по клавише 1 чуть выше (он обозначает степень группировки).

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

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

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

1. Переходим во вкладку «Данные».

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

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

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

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

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

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

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

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

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

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

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

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

20.
03.2017

Блог Дмитрия Вассиярова.

Как настроить группировку строк в excel?

Здравствуйте, друзья.

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

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

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

Как скопировать структуру таблицы Excel?

Выделите формулу и нажмите клавиши CTRL+C, чтобы скопировать её. Откройте конечную таблицу, щёлкните нужную ячейку и нажмите клавиши CTRL+V.

Как скопировать отфильтрованные данные в Excel?

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

1. фильтруешь данные
2. выбираешь КАЖДУЮ строку отфильтрованных данных (просчелкиваешь справа на сером фоне, где стоят номера строк) с нажатой клавишей CTRL.
3. правой кнопкой по выделенному тексту — «копировать»

Как скопировать диапазон в Excel?

Выделите ячейку или диапазон ячеек. нажмите CTRL+X. Выделите ячейку, в которую вы хотите переместить данные.…Копируйте ячейки на листе с помощью команд Копировать и Вставить.

1. Выделите ячейку или диапазон ячеек.
2. Нажмите кнопку Копировать или клавиши CTRL+C.
3. Нажмите кнопку Вставить или клавиши CTRL+V.

Как скопировать ячейки с группировкой?

Щелкните Главная > Найти и выделить, а затем выберите пункт Выделение группы ячеек. Выберите параметр только видимые ячейки и нажмите кнопку ОК. Щелкните Копировать (или нажмите клавиши CTRL+C).

Как отменить группировку в Excel?

Подсказка: Чтобы отменить группировку выберите диапазон, содержащий сгруппированные столбцы, и нажмите Shift+Alt+Стрелка влево.

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

Выделяем исходную таблицу, которую нам необходимо скопировать, нажимаем на Ctrl+C. Выделяем новую (уже скопированную) таблицу, куда нам необходимо формат ширины столбцов и нажимаем на ячейку правой кнопкой мыши, после чего в выпадающем меню находим раздел «Специальная вставка».

Как создать структуру в Excel?

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

Как скопировать строки в столбцы?

Преобразуем строки в столбцы в Excel при помощи Специальной вставки

1. кликните по ним правой кнопкой мыши и выберите Copy (Копировать) в контекстном меню.
2. нажмите Ctrl+C.
3. нажмите кнопку Copy (Копировать) на вкладке Home (Главная) в разделе Clipboard (Буфер обмена).

Как протянуть формулу только по видимым ячейкам?

Щелкните Главная > Найти и выделить , а затем выберите пункт Выделение группы ячеек . Выберите параметр только видимые ячейки и нажмите кнопку ОК .

Как скопировать таблицу в Excel с сохранением формул?

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

Как скопировать определенные строки в Excel?

Все ячейки, содержащие определенный текст или значение в выбранных столбцах, обнаруживаются, и все строки с выбранными ячейками выбираются сразу. 5, Нажмите Ctrl + C ключи тем временем, чтобы скопировать эти строки.

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

Копирование листа в одной книге

1. Щелкните ярлычок листа правой кнопкой мыши и выберите команду Переместить или скопировать.
2. Установите флажок Создать копию.
3. В разделе перед листом укажите, куда вы хотите поместить копию.
4. Нажмите кнопку ОК.

Как в Excel копировать только значения?

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Консолидация данных из нескольких листов на одном.

Самый быстрый способ консолидировать данные в Excel (в одной или нескольких книгах) — использовать встроенную функцию Excel Консолидация.

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

Как вы видите на скриншоте ниже, четыре объединяемых листа имеют схожую структуру данных, но разное количество строк и столбцов:

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

1. Правильно расположите исходные данные. Чтобы функция консолидации Excel работала правильно, убедитесь, что:
   • Каждый диапазон (набор данных), который вы хотите объединить, находится на отдельном листе. Не помещайте данные на лист, куда вы планируете выводить консолидированные данные.
   • Каждый лист имеет одинаковый макет, и каждый столбец имеет заголовок и содержит похожие данные.
   • Ни в одном списке нет пустых строк или столбцов.
2. Запустите инструмент «Консолидация». На новом листе, где вы планируете поместить результаты, щелкните верхнюю левую ячейку, начиная с которой должны отображаться консолидированные данные, затем на ленте перейдите на вкладку «Данные» и нажмите кнопку «Консолидация».

Совет. Желательно объединить данные в пустой лист. Если на вашем основном листе уже есть данные, убедитесь, что имеется достаточно места (пустые строки и столбцы) для записи результатов.

3. Настройте параметры консолидации. Появляется диалоговое окно «Консолидация», и вы делаете следующее:
   • В поле «Функция» выберите одну из функций, которую вы хотите использовать для консолидации данных (количество, среднее, максимальное, минимальное и т. д.). В этом примере мы выбираем Сумма.
   • В справочном окне, нажав в поле Ссылка на значок  , выберите диапазон на первом листе. Затем нажмите кнопку «Добавить», чтобы присоединить его к списку диапазонов. Повторите этот шаг для всех листов, которые вы хотите объединить.

Если один или несколько листов находятся в другой книге, используйте кнопку «Обзор», чтобы найти эту книгу и использовать ее.

4. Настройте параметры обновления. В том же диалоговом окне Консолидация выберите любой из следующих параметров:
   • Установите флажки «Подписи верхней строки» и / или «Значения левого столбца» в разделе «Использовать в качестве имён», если вы хотите, чтобы заголовки строк и / или столбцов исходных диапазонов были также скопированы.
   • Установите флажок «Создать связи с исходными данными», если нужно, чтобы консолидированные данные обновлялись автоматически при изменении исходных таблиц. В этом случае Excel создаст ссылки на ваши исходные листы, а также схему, как на следующем скриншоте:

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

Как видите, функция консолидации Excel очень полезна для сбора данных. Однако у нее есть несколько ограничений. В частности, он работает только для числовых значений и всегда обрабатывает эти числа тем или иным образом (сумма, количество, среднее и т. д.). Исходные цифры вы здесь не увидите.

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

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

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

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

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

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

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

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

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

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

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

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

1. Переходим во вкладку «Данные».
2. В нижнем левом углу блока инструментов «Структура» на ленте расположена маленькая наклонная стрелочка. Кликаем по ней.
3. Открывается окно настройки группировки. Как видим по умолчанию установлено, что итоги и наименования по столбцам располагаются справа от них, а по строкам – внизу. Многих пользователей это не устраивает, так как удобнее, когда наименование размещается сверху. Для этого нужно снять галочку с соответствующего пункта. В общем, каждый пользователь может настроить данные параметры под себя. Кроме того, тут же можно включить автоматические стили, установив галочку около данного наименования. После того, как настройки выставлены, кликаем по кнопке «OK».

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

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

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

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

Группировка строк и столбцов в Excel — как сделать?

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

Группировка в Excel — возможности

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

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

1. Откроется окно настройки, где вам нужно будет убрать соответствующие флажки.
2. Как сделать группировку данных в Excel?
3. Рассмотрим подробно на примере, как можно сгруппировать данные в таблице разными способами:
4. 1) Группировка по строкам.
5. 2) Группировка по столбцам.
6. 3) Автоматическая группировка.
7. Группировка по строкам

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

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

Для того, чтобы сделать группировку по строкам в Экселе (Excel), необходимо выделить требуемую область (группу) и в меню выбрать пункт «Данные» -> «Группировать». В появившемся окне выбираем «Группировать строки».

• Поочерёдно выбираем строки, соответствующие каждому периоду и группируем их с помощью данного инструмента.
• Таблица приобретёт следующий вид:

Если нужно скрыть группу, то нужно сбоку нажать на минус, а если развернуть — на плюс. Кроме того, Excel отображает число уровней группировки — в нашем примере их 2.

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

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

• Добавим в ранее созданную таблицу формулы, рассчитывающие суммарные цены и количество товаров по каждой группе (функция СУММ).
• Для создания автоматической группировки строк в Экселе заходим в «Данные» -> «Группировать» -> «Создание структуры».

Если всё сделали правильно, то строки автоматически будут сгруппированы. Если нет — Excel выдаст ошибку «Невозможно создать структуру документа». Это означает, что на листе отсутствуют формулы, позволяющие сгруппировать данные автоматически по определённым признакам.

Наша таблица будет иметь следующий вид:

1. Создание нескольких уровней группировки
2. Как было сказано выше, в Экселе можно создавать довольно сложные структуры данных.

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

После этого воспользуемся автоматической группировкой. Вот какая таблица получится:

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

• Группировка по столбцам
• Такой вид группировки в Excel осуществляется схожим образом — указываем требуемые столбцы и создаём группу (в диалоговом окне выбираем «Группировать столбцы»).

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

Для того, чтобы разгруппировать данные, нужно выбрать «Данные» -> «Разгруппировать». В зависимости от способа группировки выбираем либо «Разгруппировать», либо «Удалить структуру».

Как сортировать данные таблицы?

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

Цветовое деление

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

• Во вкладке «Данные» переходим к полю «Сортировка и фильтр».
• В зависимости от версии excel нужная нам команда может называться просто «Сортировка» или «Настраиваемая». После нажатия на нее должно появиться новое окно.

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

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

Объединение значений

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

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

Упрощаем большую таблицу

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

На этом всё, друзья.

Если же вы хотите изучить Excel поглубже, то рекомендую приобрести данный видео курс: «Неизвестный Excel»
.

Добрый день, мои дорогие читатели!
Сегодня на примере Excel 2010 я покажу вам, как создать многоуровневую структуру из строк или столбцов. Мы поговорим о структурировании и группировке данных. С примерами и скриншотами, конечно-же. Итак, продолжаем .

Во-первых — для чего нам может быть нужна группировка данных в Excel?
Предположим, что вы работаете с очень большой таблицей. Причем, для работы не все столбцы или строки вам нужны одновременно. Для большего удобства вы можете скрыть некоторые строки / столбцы и заниматься работой с таблицей в более компактном ее виде. А в нужный момент раскрыть все содержимое.
Сделать это можно несколькими способами. Рассмотрим два из них.

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

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

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

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

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

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

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

Сортировка по месяцам в Excel и списки порядка сортировки

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

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

Порядок сортировки следующий:

1. Перейдите на любую ячейку исходного диапазона A1:C10 и выберите инструмент: «ДАННЫЕ»-«Сортировка».

В появившемся диалоговом окне в первой секции «Столбец» из первого выпадающего списка «Сортировать по:» выбираем значение «Месяц», во втором выпадающем списке без изменений «Значение». А в третьей секции «Порядок» выбираем последнюю опцию «Настраиваемый список».

В появившемся диалоговом окне «Списки» выбираем список названий месяцев в году и нажимаем ОК.

Нажмите на кнопку «Добавить уровень» и на втором уровень в первом выпадающем списке укажите на значение «День». Остальные секции – без изменений. И нажмите ОК.

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

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



Исходная таблица для следующего примера:

Необходимо распределить данные по столбцу F – «Категория сотрудника» в следующей последовательности:

1. Стажер.
2. Физический.
3. Удаленный.
4. Руководитель.

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

Ведь текстовые значения по умолчанию в Excel можно сортировать только в алфавитном порядке или в обратно-алфавитном.

Но в программе Excel предусмотрена возможность создания пользовательских порядков сортировок. Для решения данной задачи создадим свой ключ к порядку сортирования.

Сортировка по списку в Excel:

1. Перейдите на любую ячейку таблицы и снова воспользуйтесь инструментом: «ДАННЫЕ»-«Сортировка».

В первом выпадающем списке укажите «Категории сотрудника». Во втором все тоже «Значение». А в третьем выпадающем списке в секции «Порядок» выберите последнюю опцию «Настраиваемый список».

В появившемся окне «Списки» в левой группе укажите на первый «НОВЫЙ СПИСОК», а в правом текстовом поле введите все 4 категории разбив их на 4 строки. Для этого после ввода каждой нового названия категории сотрудника нажимайте клавишу Enter на клавиатуре:

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

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

Полезный совет! Чтобы каждый раз не создавать новые списки для порядка сортировки воспользуйтесь редактором списков в настройках Excel. Для этого:

1. Откройте редактор списков в настройках Excel: «ФАЙЛ»-«Параметры»-«Дополнительно»-«Общие»-«Изменить списки».
2. Введите свои списке и нажмите на кнопку «Добавить» как описано выше в примере. Если же у вас большой список и нет желания его заполнять вручную, тогда воспользуйтесь встроенным импортом. Для этого в данном диалоговом окне присутствует дополнительное поле «Импорт списка из ячеек:» в котором можно ввести ссылку на диапазон ячеек и нажать на кнопку «Импорт». И нажмите ОК.

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