Эксель группировка данных

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

​Рабочая область программы Excel​ одна тема!​ см. файл. Аналогично​ по 4 года.​ выберите команду​Вы также можете воспользоваться​ футболок, заказанных для​ групп, также называемую​ и даже трудночитаемыми.​ черты над сгруппированными​ клик. Увидев такие​

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

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

​Читайте правила форума​ со столбцами (справа​ В итоге получится​Группировать​ иконками​

1. ​ каждого размера.​ структурой, чтобы упорядочить​ Excel позволяет группировать​ столбцами появится специальная​ иконки на листе,​​ Скрыть выделенные столбцы​​ дополнение к этой​ «Данные», за ней​​ протягиваем курсор до​​ выделить его при​
2. ​ следующий:​​ что при создании​​ShAM​​ и слева).​​ вот такая таблица:​(Group). Появится диалоговое​Показать​В поле​

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

​ информацию на листе.​ данные, чтобы с​ иконка со знаком​ Вы сразу поймёте,​ можно нажатием​ теме, Вам может​ «Группа и структура»,​ последней требуемой ячейки​ помощи курсора мыши.​

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

1. ​Ваши данные должны быть​ легкостью скрывать и​ «​ где находятся скрытые​Ctrl+0​​ быть интересна статья​​ а потом функция​ для выделения сразу​​ Потом, не убирая​​ перед тем как​ без управления строк​​ внимательно прочитали? «Группировки​​ это самому настроить?​​ сводной таблице, нужно:​​Группирование​Скрыть детали​выберите столбец, в​ правильно отсортированы перед​ показывать различные разделы​—​
2. ​ столбцы и какие​.​ Как закрепить области​ «Группировать» или нажать​​ нескольких столбцов:​​ курсора с ранее​ вставить скопированное содержимое,​​ и столбцов. Следует​​ строк и столбцов​ikki​Кликнуть правой кнопкой мыши​(Grouping) для дат.​, чтобы скрыть или​ который необходимо вывести​ использованием команды​ листа. К тому​

Как разгруппировать сводную таблицу

​» (минус).​ столбцы могут быть​

• ​Подсказка:​ в Excel.​ комбинацию клавиш SHIFT+ALT+стрелка​Не убирая с выделенной​
• ​ выделенной части таблицы,​​ в данном случае​​ учитывать данный факт​

Распространённые ошибки при группировке в сводной таблице

​(Расположение «+»)​: можно.​​ по левому столбцу​Выберите​​ отобразить группы.​ итог. В нашем​

​Промежуточный итог​ же Вы можете​Нажатие на​ скрыты. Как это​​Вы можете найти​​Есть несколько вспомогательных столбцов​ в право:​ области таблицы Excel​ открыть правой кнопкой​​ столбец, можно предварительно​ при построении структуры​​». И при чем​до 2003-й версии​ сводной таблицы (столбец,​Месяцы​Со временем необходимость в​ примере это столбец​, Вы можете изучить​ обобщить различные группы​минус​ делается:​ команду​

​ с промежуточными расчётами​В конечном счете, после​ курсора мыши, при​ мыши быстрое меню.​​ нажать на кнопку​​ каждой таблицы. Особенно​ здесь защита листа?​ вкл-но: Данные -​ содержащий сгруппированные значения);​(Month) и нажмите​ промежуточных итогах пропадает,​

​Размер​ серию уроков Сортировка​​ при помощи команды​​скроет столбцы, и​

​Откройте лист Excel.​

office-guru.ru>

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Открывается небольшое окно, в котором нужно дать ответ, что мы хотим сгруппировать – строки или столбцы. Ставим переключатель в позицию «Строки» и жмем на кнопку «OK».

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

Чтобы заново развернуть группу, нужно нажать на знак «плюс».

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

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

1. Справа или слева от группируемых данных добавляем новый столбец и указываем в нём соответствующее наименование группы.
2. Выделяем ячейки в столбцах, которые собираемся сгруппировать, кроме столбца с наименованием. Кликаем на кнопку «Группировать».
3. В открывшемся окошке на этот раз ставим переключатель в позицию «Столбцы». Жмем на кнопку «OK».

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

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

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

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

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

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

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

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

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

Мы рады, что смогли помочь Вам в решении проблемы.

Опишите, что у вас не получилось.
Наши специалисты постараются ответить максимально быстро.

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

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

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

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

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

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

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

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

Группировка полей дат по неделям в сводной таблице Excel 2010 | Сводные таблицы Excel 2010

Диалоговое окно Группирование (Grouping) предлагает настройки группировки по секундам, минутам, часам, дням, месяцам, кварталам и годам. А что делать, если нужно сгруппировать данные по одной или двум неделям? Это вполне реально.

Прежде всего следует свериться с бумажным календарем данного года

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

Сверьтесь с календарем. Ближайший понедельник был 4 января 2010 года.

Выделите любой заголовок дат в сводной таблице. Перейдите на контекстную вкладку ленты Параметры (Options) и в разделе Группировать (Group) щелкните на кнопке Группировка по полю (Group Field).

В диалоговом окне Группирование (Grouping) отмените выделение всех параметров в списке с шагом (By), за исключением параметра Дни (Days). В результате станет доступным счетчик количество дней (Number of Days).

Чтобы создать недельный отчет, увеличьте количество дней с 1 до 7.

И наконец, следует установить флажок начиная с (Starting At) и определить дату. Если вы приняли настройки по умолчанию и начали с 4 января 2010 года, то все недельные периоды в отчете будут начинаться с понедельника и заканчиваться воскресеньем. Эти настройки нас вполне устраивают (рис. 4.6), поэтому оставим все без изменений.

Рис. 4.6. Для получения доступа к счетчику количество дней в списке с шагом выберите значение Дни

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

Рис. 4.7. Отчет, отображающий еженедельные объемы продаж

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

Группирование двух полей дат в один отчет

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

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

В подобном случае важно правильно переименовать поля

Группировка и несколько диапазонов консолидации

Вы можете создавать группы в сводной таблице, которая собрана из нескольких консолидированных диапазонов, например, из данных на разных листах рабочей книги. Для этого в Excel 2007 и 2010 на Панель быстрого доступа необходимо добавить команду PivotTable and PivotChart Wizard (Мастер сводных таблиц и диаграмм).

Чтобы сделать это, кликните выпадающее меню Панели быстрого доступа, выберите пункт More Commands (Другие команды), в открывшемся диалоговом окне установите группу All Commands (Все команды) и в списке команд найдите PivotTable and PivotChart Wizard (Мастер сводных таблиц и диаграмм). Нажмите кнопку Add (Добавить), чтобы добавить кнопку на Панель быстрого доступа.

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

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

Создайте новый лист в Вашей рабочей книге Excel, кликните только что добавленную кнопку PivotTable and PivotChart Wizard (Мастер сводных таблиц и диаграмм), выберите пункты Multiple Consolidation Ranges (В нескольких диапазонах консолидации) и PivotTable (Сводная таблица), а затем нажмите Next (Далее). Выберите I will create the page fields (Создать поля страницы) и снова нажмите Next (Далее). Теперь Вам нужно указать каждый из диапазонов. Зайдите на первый лист, выберите все данные, включая заголовки таблицы, и нажмите Add (Добавить). Повторите шаги, чтобы добавить информацию со следующего листа и так далее, пока не добавите данные со всех листов, которые планировали использовать в сводной таблице.

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

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

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

Так, например, диапазон, содержащий даты, может быть частью группы, которая разделяет месяцы по кварталам, а также частью группы, которая разделяет месяцы по сезонам: Winter (Зима), Spring (Весна), Summer (Лето) и Fall (Осень). Группировки могут быть по любому признаку, который Вы посчитаете подходящим для своих данных. Нажмите Next (Далее), кликните по ячейке, в которой должен будет находиться верхний левый угол сводной таблицы и нажмите Finish (Готово).

В Мастере сводных таблиц и диаграмм Вы можете связать диапазоны со страницами, как показано на рисунке выше – Quarters (Кварталы) и Seasons (Сезоны).

Сводная таблица будет сформирована автоматически, группы будут созданы как поля Report Filter (Фильтры) в списке полей сводной таблицы, но Вы, если пожелаете, можете перетащить эти поля в область Row Labels (Строки) или Column Labels (Колонны).

На рисунке выше показано: мы переместили страницу Page 2 (Сезоны) из области Report Filter (Фильтры) в область Row Labels (Строки).

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

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

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

Мы имеем отчет продаж по всем работавшим агентам за год. Информация представлена по месяцам. Но такая детализация для целей нашего анализа является избыточной, но в случае необходимости мы должны быстро ее получить. Поэтому удалять ее не нужно. Можно просто скрыть столбцы – и это удовлетворит нашей цели. Но если Вы отчет делаете не для себя, а для руководства или еще кого-либо. Нельзя быть на 100% уверенным, что те, кто будет работать с Вашими таблицами, будет хорошо владеть приложением Excel. Выход из ситуации – группировка. Находится она на вкладке «Данные», раздел «Структура».

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

Отчет после группировки:

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

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

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

Кнопка 1 скрывает все уровни групп, 2 раскрывает группы первого уровня. Если внутри какой-либо группы создается еще одна группа, то она будет относиться к уровню 3, и к кнопкам добавиться еще одна, с цифрой 3 и т.д.

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

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

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

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

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

1. ​Функция «Группировать» в​​ таблицы получился такой.​​ – перечень данных​

2. ​ в Excel по​ группы выделить в​​ планируем выводить наименование​​ в которые входит​При защите листа,​: Чеботарь, Вам уже​

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

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

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

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

1. ​ группированных строк и​ маленький квадратик со​Сводная таблица будет сформирована​ диаграмм​More Commands​ ряд причин, почему​ становится группой просто​ Вы работаете, являются​(Начиная с),​ статью «Суммирование ячеек​

2. ​ можно установить структуру​ раздел. А цифрами​ «Структура» нажимаем на​Excel​​ сгруппировать отдельно. Затем​​ произвольное наименование группы,​

3. ​ становится вопрос структурирования​ столбцов, становится невозможным.​​ стрелкой в блоке​​ автоматически, группы будут​​позволяет объединять сразу​​(Другие команды), в​

4. ​ это может произойти.​ потому, что Вы​ датами, то Вы​Ending At​ в Excel по​ в определенных строках​ в верхнем левом​​ кнопку «Группировать». Получилось​​можно несколькими способами​ следует провести одну​​ подходящее к ней​​ данных. В Экселе​

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

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

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

​(По) и​ условию».​

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

2. ​ по контексту.​ этого можно достичь​ решить проблему?​Чеботарь​Report Filter​​ одной сводной таблице.​​ установите группу​

3. ​ если Вы пытаетесь​ этом случае можно​ приём. Чтобы сгруппировать​​By​​Helen Bradley рассказывает, как​​ понять принцип работы​​ или сворачиваем все​

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

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

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

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

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

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

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

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

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

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

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

lumpics.ru>

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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



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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

• Проверьте, чтобы в начале всех столбцов, которые мы будем объединять, был заголовок, что все они содержат информацию одинакового типа, и нет пустых мест.
• Чтобы столбцы имели опрятный вид, в поле сортировки выберите команду «Сортировать от А до Я» или наоборот.
• Вставьте итоговые строки, то есть, те, что имеют формулы и ссылаются на объединяемые нами ячейки. Сделать это можно с помощью команды «Промежуточные итоги», которая находится в том же поле, что и кнопка «Группировать».

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

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

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

Подведение итогов в Excel

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

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

Создание промежуточного итога

В следующем примере мы воспользуемся командой Промежуточный итог, чтобы определить сколько заказано футболок каждого размера (S, M, L и XL). В результате рабочий лист Excel обретет структуру в виде групп по каждому размеру футболок, а затем будет подсчитано общее количество футболок в каждой группе.

1. Прежде всего отсортируйте данные, для которых требуется подвести итог. В этом примере мы подводим промежуточный итог для каждого размера футболок, поэтому информация на листе Excel должна быть отсортирована по столбцу Размер от меньшего к большему.
2. Откройте вкладку Данные, затем нажмите команду Промежуточный итог.
3. Откроется диалоговое окно Промежуточные итоги. Из раскрывающегося списка в поле При каждом изменении в, выберите столбец, который необходимо подытожить. В нашем случае это столбец Размер.
4. Нажмите на кнопку со стрелкой в поле Операция, чтобы выбрать тип используемой функции. Мы выберем Количество, чтобы подсчитать количество футболок, заказанных для каждого размера.
5. В поле Добавить итоги по выберите столбец, в который необходимо вывести итог. В нашем примере это столбец Размер.
6. Если все параметры заданы правильно, нажмите ОК.
7. Информация на листе будет сгруппирована, а под каждой группой появятся промежуточные итоги. В нашем случае данные сгруппированы по размеру футболок, а количество заказанных футболок для каждого размера указано под соответствующей группой.