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

Автор: | 18.03.2009

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

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

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

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

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

  1. Выделить строки, которые хотим сгруппировать. Можно выделять как строки, так и просто ячейки «в столбце».
  2. Выбрать Данные > Группа и Структура >Группировать.

  3. Или в Office 2007 жмакнуть Shift+Alt+стрелка вправо. Появится диалог с вопросом: Что группировать будем — строки али колонки?

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

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

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

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

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

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

Office ХР

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

Office 2007

en

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

ru

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

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

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

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

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

Sub OutlinePlusesInTop()

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

With ActiveSheet.Outline

.AutomaticStyles = False

.SummaryRow = xlAbove

.SummaryColumn = xlRight

End With

End Sub

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

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

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

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

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

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

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

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

Группирование данных в Excel: 42 комментария

  1. dew

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

  2. olegko

    А как же без группировки раньше жили то?
    Вот список других фич Excel-я, без которых я уже не представляю себе нормальную работу с тест кейсами:
    — Freeze Panes («заморозка» левой и/или верхней части листа)
    — Data Validation (выпадающие списки)
    — Conditional Formatting (цветовая автоподсветка)
    — Sharing (совместная работа)

  3. ruVarvara

    Огого! Спасибо огромное! Очень полезная информация)))))

  4. Сергей

    Несколько раз натыкался на такую проблемму. Вот выделил несколько ячеек и вбил в левое верхнее окошко «станки» (к примеру) — получился именованный список. А как потом этот список расширить-уменьшить не удаляя-вставляя ячейки? и где вообще эти списки хранятся где можно пощелкать их свойства и тд.!!!???

  5. Алексей Лупан

    Cергей, посмотрите запись Выпадающие списки в Excel и Calc глава «Мудрейший способ» — там указано, как создать список, который можно будет впоследствии расширять/изменять, не трогая ячейки, в которых он отображается.
    Если все еще останутся неясности — обращайтесь.

  6. Дмитрий

    Спасибо за совет с «плюсиками», касаемый группировки данных!Очень пригодился!)))

  7. Таня

    Всегда считала, что хорошо знаюсь в Excel:) (на том уровне, на котором он мне нужен)…
    Но отображенные плюсики вверху — это круто!
    Нет пределам совершенству!!!

  8. Олеся

    Спасибо большое, долго искала способ создания такого списка. А оказалось так легко! Спасибо за доступное объяснение!

  9. Роман

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

  10. ssvukr

    Пытаюсь сделать так, чтобы “плюсики” отображались вверху, а не внизу скрываемо/раскрываемого списка.
    После того, как убрал галку в опции «Итоги в строках под данными« в Excel2007 плюсики пропали и остались одни точки.
    Подскажите как решить проблему.

  11. Алексей Лупан

    По указанным шагам у меня эта проблема не воспроизвелась.
    А обратный эффект наблюдается? Если снова поставить галку.

  12. doctor

    Уважаемые! При пересортировке группы нарушаются. Нет ли возможности перемещать свернутую группу как единое целое?

  13. Виталий

    большое спасибо! очень дельный совет!!!
    давно искал как это замутить.

  14. Дима

    Подскажите, как сделать чтобы структуру можно было развернуть нажатием не только на кнопы + /- , но и привязать эту функцию например к текстовой ссылке

  15. qwerty

    За «как перенести плюсик» — РЕСПЕКТ)

  16. bones

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

  17. очень гут

    большое спасибо!
    и приятно осознавать, что я был не одинок

  18. Виктор

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

  19. Алексей Лупан

    Виктор, вопрос не понятен. Переформулируйте как-нибудь иначе. Список-пример напишите, или укажите файл, в котором это можно увидеть.
    Бо не понятно, откуда в ячейки «B» у вас еще фильмы появляются.

  20. maugli12

    Помогите пожалуйста:
    Хотелось бы создать для себя что-то вроде органайзера в Excel. Возникла следующая задача:
    В каждой строке указано имя клиента, в тойже строке, но в другой ячейке я указываю дату, когда с этим клиентом надо связаться. Как сделать так, чтобы при наступлении этой даты вся строка автоматически становилась на первое место в списке клиентов?

  21. Алексей Лупан

    Вам пришло время программировать на VBA. Штатными средствами Excel такие задачи не решаются.
    Может быть, вам будет удобнее пользоваться фильтрами или сортировкой по колонке с датами?

  22. maugli12

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

  23. Алексей Лупан

    Если у вас Excel 2007, то нажмите в нем клавосочетание «Alt+f11′ — откроется редактор Visual Basic.
    Или пройдите в меню «Разработчик > Visual Basic» — та же шняга.
    Если окружение будет незнакомо — спросите у яндекса об основах ‘Excel visual basic’, ибо вам будет нужен небольшой, но вводный курс.
    Дальше все пойдет просто.

  24. smolin

    Здравствуйте!
    Здесь был вопрос, который интересует и меня. Но он остался без ответа.
    Можно ли раскрывать/скрывать группу не только по +/-, но и нажатием, скажем, на заголовок этой группы?

  25. Алексей Лупан

    «На заголовок» не скажу — для этого нужно написать/найти функцию, которая меняет значение линка после клика по нему (или по ячейке, в которой он находится), а искать это нет времени, простите.
    Вот более простое решение:
    — нашел решение тут — http://www.planetaexcel.ru/tip.php?aid=168
    — скачайте мой файл с примером.
    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 тут указано, как это делается.
    Если найдете пример с обработкой клика по одной ячейке (которая будет заголовком группы) — опубликуйте тут, пожалуйста.

  26. smolin

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

  27. Mas

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

  28. Алексей Лупан

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

  29. miamia

    Добрый день!
    У меня к Вам 2 вопроса:
    1. Можно ли так же «с помощью плюсика» группировать листы??? т.е., например у меня склады по одной тематике, а я хочу их объединить как в одну папку, чтоб при нажатии на плюс или что либо другое, раскрывался подробный перечень имеющихся листов?
    2. я выставляю защиту листа, оставляя лишь некоторые диапазоны для ввода пользователем. но тут проблема. на листе есть выпадающие списки и в случае защиты их нельзя менять, а пользователь должен это делать. уже снимала галочку защиты в формате объекта «выпадающий список», разрешала изменение диапазона с которым связана форма и ни-че-го! 🙁
    Помогите, пожалуйста!

  30. Алексей Лупан

    1) не предполагаю, что подобное возможно.
    2) создайте новый лист, раз на старом не работает, и сделайте там все с нуля.

  31. Danila

    СПАСИБО!

  32. vaz800

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

  33. Airi

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

  34. Alfie

    У нас в компании есть форма, в которой мало того, что плюсик сверху остается, так еще и первая строка сгруппированного диапазона, стоящая напротив этого плюсика — не пропадает.
    То есть в первую строку пишется заголовок, он виден и напротив него стоит «+», а потом выпадают составляющие проекта, например:
    «+» ВЕНТИЛЯЦИЯ
    — воздуховоды
    — вентиляторы
    — фильтры
    И «Вентиляция» видна всегда. Как такое сделать? Знает кто-нибудь?

  35. Алексей Лупан

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

  36. graf

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

  37. Vladimir

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

  38. Уведомление: как в excel сделать плюсы | Удобный поиск ответов на все ваши вопросы

Обсуждение закрыто.