Как сделать группировку в excel 2003?

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

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

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

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

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

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

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

Есть два способа создать структуру листа: автоматический и ручной.

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

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

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

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

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

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

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

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

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

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

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

Если Вам больше не нужна структура – удалите ее. Для этого выполните на ленте Данные – Структура – Разгруппировать – Удалить структуру .

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

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

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

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

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

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

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

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

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

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

Читать еще:  Как сделать всплывающую картинку в excel в ячейку?

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  • В открывшемся окошке на этот раз ставим переключатель в позицию «Столбцы». Жмем на кнопку «OK».
  • Группа готова. Аналогично, как и при группировании столбцов, её можно сворачивать и разворачивать, нажимая на знаки «минус» и «плюс» соответственно.

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

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

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

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

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

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

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

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

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

    Как сделать группировку в excel 2003?

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

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

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

    Читать еще:  Как сделать временную диаграмму в excel?

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

    Normal testing

    From the programmer’s worst friend

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

    Можно сворачивать/разворачивать строки на листе Excel.

    Если строк много, то фича рулит.

    Группированные строки в Excel

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

    1. Выделить строки, которые хотим сгруппировать. Можно выделять как строки, так и просто ячейки «в столбце».
    2. Выбрать Данные >Группа и Структура >Группировать.
    3. Или в Office 2007 жмакнуть Shift+Alt+стрелка вправо. Появится диалог с вопросом: Что группировать будем — строки али колонки?

    Официальная справка (очень рекомендую ознакомиться):

    1. Для отображения строк уровня щелкните соответствующие символы структуры.
    2. Для отображения или скрытия данных в многоуровневой структуре щелкайте символы структуры и .

    Дело очень простое, как настройки маршрутизатора под Gentoo:

    1. После выделения столбца/строки жмем Alt+Shift+ для понижения уровня. Потом, при необходимости повысить уровень обратно — то же со .
    2. Уровней может быть (в Excel 2003) до восьми.
    3. Кроме минусов, можно кликать линии, от них идущие.
    4. Групповое изменение — маленькие кнопочки с цифрами в левом верхнем углу.

    Но придумать себе проблему на эту тему все еще можно:

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

    Office ХР

    Данные > Группа и Структура > Настройка > убрать галку у опции «Итоги в строках под данными»

    Читать еще:  Как сделать чтобы в excel не убирались нули?

    Office 2007

    en

    Data > Outline > Show the Outline dialog box > Settings > uncheck «Summary rows below detail»

    ru

    Данные > Структура > Показать меню (см. на картинке, куда надо нажать) > Настройки > убрать галку у опции «Итоги в строках под данными»

    Смотри, куда жмакаешь

    Раскрываемость списков неизменно и существенно повышается.

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

    ‘ Товарищ, это макрос для Office 2007

    With ActiveSheet.Outline
    .AutomaticStyles = False
    .SummaryRow = xlAbove
    .SummaryColumn = xlRight
    End With
    End Sub

    А кто не знает, как использовать макросы в офисном пакете, тот просто нам завидует.

    Плавная привязка записи к теме тестирования:

    Дык, очевидно же, ёптыть…

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

    То есть, не было никаких разрывов с ноября прошлого года.

    Внимательное внимание!

    Прием комментариев к этой записи завершён.

    Тут нам не то, и не это. Не надо задавать вопросы о том, как сделать ещё что-то с этими прекрасными свертывающимися списками. Здесь даже не форум по Excel. Это блог о тестировании программного обеспечения.

    Вы же любите тестировать, правда?

    Ваша оценка:

    Поделиться ссылкой:

    Понравилось это:

    Навигация по записям

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

    Не знаю, не пробовал.

    У нас в компании есть форма, в которой мало того, что плюсик сверху остается, так еще и первая строка сгруппированного диапазона, стоящая напротив этого плюсика — не пропадает.

    То есть в первую строку пишется заголовок, он виден и напротив него стоит «+», а потом выпадают составляющие проекта, например:

    «+» ВЕНТИЛЯЦИЯ
    — воздуховоды
    — вентиляторы
    — фильтры

    И «Вентиляция» видна всегда. Как такое сделать? Знает кто-нибудь?

    Первой линией группировки строк выберите линию выше первой составляющей проекта.

    Добрый день!
    Помогите, пожалуйста!
    Как защитить лист и оставить возможность работы со структурой?
    Необходимо защитить лист от внесения изменений и на листе имеется сгруппированные в структуру данные.
    При установке обычной защиты теряется возможность работы с этой структурой.
    Необходимо, чтобы наряду с защитой листа можно было еще пользоваться структурой.Т.е. чтобы пользователь мог просмотреть все в удобной форме, но не смог ничего изменить, что защищено.
    Формой будут пользоваться несколько пользователей.Есть ячейки открытые для редактирования и закрытые.
    Без группировок форма нечитабельна, но и без защиты она бесполезна((
    Подскажите, пожалуйста, есть ли возможность совмещения и защиты листа/книги и сохранения действующей структуры?

    красавы!. про плюсики и их место было полезно! два дня потратил!

    Спасибо, было очень полезно 🙂

    У меня к Вам 2 вопроса:

    1. Можно ли так же «с помощью плюсика» группировать листы. т.е., например у меня склады по одной тематике, а я хочу их объединить как в одну папку, чтоб при нажатии на плюс или что либо другое, раскрывался подробный перечень имеющихся листов?

    2. я выставляю защиту листа, оставляя лишь некоторые диапазоны для ввода пользователем. но тут проблема. на листе есть выпадающие списки и в случае защиты их нельзя менять, а пользователь должен это делать. уже снимала галочку защиты в формате объекта «выпадающий список», разрешала изменение диапазона с которым связана форма и ни-че-го! 😦

    1) не предполагаю, что подобное возможно.

    2) создайте новый лист, раз на старом не работает, и сделайте там все с нуля.

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

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

    Здесь был вопрос, который интересует и меня. Но он остался без ответа.

    Можно ли раскрывать/скрывать группу не только по +/-, но и нажатием, скажем, на заголовок этой группы?

    «На заголовок» не скажу — для этого нужно написать/найти функцию, которая меняет значение линка после клика по нему (или по ячейке, в которой он находится), а искать это нет времени, простите.

    1) Сделайте список на десять строк с таким вот содержимым:
    blu
    blo
    спрятать
    строку
    через
    макрос
    bla
    ble
    bli
    bldjad

    2) сгруппируйте, если угодно, строки, но обязательно с третью по шестую.

    3) Alt+F11 — добавьте два макроса

    Sub HideRows()
    Rows(«3:6»).SpecialCells(xlCellTypeConstants, 23).EntireRow.Hidden = True
    End Sub

    Sub ShowRows()
    Rows(«3:6»).SpecialCells(xlCellTypeConstants, 23).EntireRow.Hidden = False
    End Sub

    Смысел ясен? Строки с 3 по 6 будут скрыты полностью. Второй макрос их раскрывает. При выполнении макроса HideRows на листе в нашем примере останутся только строки с латинскими буквами, а те, что содержат кириллицу, будут скрыты.

    Кстати, чтобы управлять скрытием колонок, используйте такие команды
    Columns(«A:С»).SpecialCells(xlCellTypeConstants, 23).EntireColumn.Hidden = True/False

    4) Поставьте на лист две кнопки из меня «Формы», дайте им имена- «Спрятать» и «Показать», и назначьте выполнение этих макросов на эти кнопки. http://www.planetaexcel.ru/tip.php?aid=122 тут указано, как это делается.

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

    Спасибо! А нельзя ли при этом, чтобы скрывались не целые строки, а диапазон ячеек? Скажем, А3:В6.

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