Структура в excel

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

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

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

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

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

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

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

Программа может попытаться создать структуру автоматически. Для этого нажмите на ленту: Структура - Группа - Создать структуру .

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

Ручное создание структуры

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

1. Форматирование и заполнение таблицы, создание итоговых строк и столбцов
2. Нажмите на значок маленькой стрелки в правом нижнем углу группы ленты Данные - Структура . В появившемся окне задайте положение строк и столбцов сводки

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

1. Щелкните на ленте Структура - Группа (или нажмите Alt+Shift+стрелка вправо). Будет создана новая группировка.

1. Повторяйте шаги 3-4, пока данные не будут полностью структурированы. Опять же, начните с подгрупп, а затем поднимитесь на уровень выше. Т.е. в таблице в примере мы сгруппируем менеджеров по отделам, а затем отделы по компаниям.

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

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

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

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

Вот и все, практикуйтесь и применяйте структурирование данных Excel на практике. Не стесняйтесь комментировать представленный материал, задавать вопросы и делиться своим опытом!

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

Добавить комментарий Отменить ответ

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

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

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

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

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

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

Подробнее: круговая диаграмма в Excel

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

 

Структура в excel

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

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

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

 

 

 

 

Рисунок 8.8: Выдержка из окна структурированных данных

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

Если итоговые строки расположены над строками данных или итоговые столбцы находятся слева от столбцов данных, вы можете изменить параметры компоновки. Это можно сделать, выбрав Data=>Group and Structure => Customise и сняв флажок для итогов в строках под данными, флажок для итогов в столбцах справа от данных или оба флажка.

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

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

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

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

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

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

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

 

 

 

 

Рисунок 8.9: Снимок экрана окна "Создать группу одежды

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

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

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

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

 

 

 

 

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

Читайте также: Как сохранить рабочий лист в excel

 

 

 

 

Рис.8.11. Результат применения автоматических стилей к структуре

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

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

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

или Groups, а затем нажмите Data => Group and Structure => Ungroup. На рисунках 8.12 и 8.13 показан пример удаления структуры для группы Иванова.

 

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

 

Рисунок 8.13 . Вид окна после отключения группы Иванова

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

 

 

, и на вкладке Вид диалогового окна Параметры (меню Сервис) снимите флажок Символы структуры.

Авторское право 2010-2020
Ющик Е. В. Все права защищены

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

 

 

 

 

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

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

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

 

1. Перейдите на вкладку Данные.

 

 

 

 

 

 

 

 

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

 

 

 

 

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

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

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

 

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

 

 

 

 

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

 

 

 

 

Нажмите кнопку Группа на панели инструментов Структура на ленте.

 

 

 

 

 

 

 

 

На этом создание группы завершено. Чтобы свернуть группу, просто нажмите на знак "минус".

 

 

 

 

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

 

 

 

 

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

Колонки можно группировать аналогичным образом.

 

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

 

 

 

 

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

 

 

 

 

• В появившемся окне на этот раз установите переключатель на "Колонки". Нажмите OK.

 

 

 

 

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

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

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

 

 

 

 

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

 

 

 

 

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

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

 

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

 

 

 

 

• В появившемся окне выберите, хотите ли вы разгруппировать строки или столбцы. Затем нажмите "OK".

 

 

 

 

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

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

Поблагодарите автора, поделившись этой статьей в социальных сетях.

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

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

Читайте также: Как обновить данные на диаграмме в Excel

Инструкция

Откройте файл Excel и начните группировку:

• Выделите нужные строки;
• Откройте вкладку "Данные" в меню вверху;
• Под ним в поле "Структура" найдите команду "Группа";

• В появившемся окне установите флажки напротив строк;

Ничего сложного, верно?
Области, объединенные с помощью Excel, имеют полосу рядом с и/или вверху. Под ним находится знак минус. Нажмите на него. Сгруппированные ячейки сворачиваются. Чтобы увидеть их снова, нажмите на знак "плюс" вверху.

Задаем название

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

• Выполните те же действия, что описаны в инструкциях выше. Однако не спешите применять команду "Группа".
• Сначала нажмите на маленький квадратик рядом со словом "Структура".
• В появившемся окне "Расположение итогов" снимите все флажки.

Теперь нам нужно исправить предыдущий макет:

 

• В поле "Структура" нажмите "Разгруппировать". Окно появляется снова, верно? Выберите "Строки". И теперь, когда имя прозвучало, мы повторяем порядок действий, разобранный в самом начале.

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

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

Это гарантирует, что стол не займет слишком много места.

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

О том, как отменить ручную группировку, вы узнали выше. Как это сделать после применения автоматического метода? На той же вкладке "Группировка" нажмите "Очистить структуру".

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

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

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

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

• На вкладке "Данные" перейдите к полю "Сортировка и фильтр".
• В зависимости от версии Excel нужная нам команда может называться просто "Сортировка" или "Пользовательская". После нажатия на него должно появиться новое окно.

• В разделе "Колонка" в группе "Сортировать по" выберите нужную колонку.
• В разделе сортировки нажмите на условие, по которому вы хотите разделить. Нужно ли вам группировать по цвету ячеек? Выберите этот параметр.
• Нажмите на стрелку под "Заказать", чтобы указать цвет. Рядом можно порекомендовать, куда переместить отсортированные данные. Если вы нажмете "Вверх", они будут перемещаться вверх по колонке, "Влево" - вниз по ряду.

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

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

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

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

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

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

 

• Убедитесь, что все колонки, которые мы собираемся объединить, имеют заголовок в начале, что все они содержат информацию одного типа и что в них нет пустых мест.
• Чтобы столбцы выглядели аккуратно, в поле сортировки выберите "Сортировать от A до Z" или наоборот.
• Вставьте итоговые строки, т.е. строки, содержащие формулы и относящиеся к объединяемым ячейкам. Это можно сделать с помощью команды Промежуточные итоги, которая находится в том же блоке, что и кнопка Группировать.

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