Скрытие/отображение ненужных строк и столбцов
Постановка задачи
Предположим, что у нас имеется вот такая таблица, с которой приходится "танцевать" каждый день:
Кому таблица покажется маленькой - мысленно умножьте ее по площади в двадцать раз, добавив еще пару кварталов и два десятка крупных российских городов.
Задача - временно убирать с экрана ненужные в данный момент для работы строки и столбцы, т.е.,
- скрывать подробности по месяцам, оставляя только кварталы
- скрывать итоги по месяцам и по кварталам, оставляя только итог за полугодие
- скрывать ненужные в данный момент города (я работаю в Москве - зачем мне видеть Питер?) и т.д.
В реальной жизни примеров таких таблиц - море.
Способ 1. Скрытие строк и столбцов
Способ, прямо скажем, примитивный и не очень удобный, но два слова про него сказать можно. Любые выделенные предварительно строки или столбцы на листе можно скрыть, щелкнув по заголовку столбца или строки правой кнопкой мыши и выбрав в контекстном меню команду Скрыть (Hide):
Для обратного отображения нужно выделить соседние строки/столбцы и, щелкнув правой кнопкой мыши, выбрать в меню, соответственно, Отобразить (Unhide).
Проблема в том, что с каждым столбцом и строкой придется возиться персонально, что неудобно.
Способ 2. Группировка
Если выделить несколько строк или столбцов, а затем выбрать в меню Данные - Группа и структура - Группировать (Data - Group and Outline - Group), то они будут охвачены прямоугольной скобкой (сгруппированы). Причем группы можно делать вложенными одна в другую (разрешается до 8 уровней вложенности):
Более удобный и быстрый способ - использовать для группировки выделенных предварительно строк или столбцов сочетание клавиш Alt+Shift+стрелка вправо, а для разгруппировки Alt+Shift+стрелка влево, соответственно.
Такой способ скрытия ненужных данных гораздо удобнее - можно нажимать либо на кнопку со знаком "+" или "-", либо на кнопки с цифровым обозначением уровня группировки в левом верхнем углу листа - тогда все группы нужного уровня будут сворачиваться или разворачиваться сразу.
Кроме того, если в вашей таблице присутствуют итоговые строки или столбцы с функцией суммирования соседних ячеек, то есть шанс (не 100%-ый правда), что Excel сам создаст все нужные группировки в таблице одним движением - через меню Данные - Группа и структура - Создать структуру (Data - Group and Outline - Create Outline). К сожалению, подобная функция работает весьма непредсказуемо и на сложных таблицах порой делает совершенную ерунду. Но попробовать можно.
В Excel 2007 и новее все эти радости находятся на вкладке Данные (Data) в группе Структура (Outline):
Способ 3. Скрытие помеченных строк/столбцов макросом
Этот способ, пожалуй, можно назвать самым универсальным. Добавим пустую строку и пустой столбец в начало нашего листа и отметим любым значком те строки и столбцы, которые мы хотим скрывать:
Теперь откроем редактор Visual Basic (ALT+F11), вставим в нашу книгу новый пустой модуль (меню Insert - Module) и скопируем туда текст двух простых макросов:
Sub Hide()
Dim cell As Range
Application.ScreenUpdating = False 'отключаем обновление экрана для ускорения
For Each cell In ActiveSheet.UsedRange.Rows(1).Cells 'проходим по всем ячейкам первой строки
If cell.Value = "x" Then cell.EntireColumn.Hidden = True 'если в ячейке x - скрываем столбец
Next
For Each cell In ActiveSheet.UsedRange.Columns(1).Cells 'проходим по всем ячейкам первого столбца
If cell.Value = "x" Then cell.EntireRow.Hidden = True 'если в ячейке x - скрываем строку
Next
Application.ScreenUpdating = True
End Sub
Sub Show()
Columns.Hidden = False 'отменяем все скрытия строк и столбцов
Rows.Hidden = False
End Sub
Как легко догадаться, макрос Hide скрывает, а макрос Show - отображает обратно помеченные строки и столбцы. При желании, макросам можно назначить горячие клавиши (Alt+F8 и кнопка Параметры), либо создать прямо на листе кнопки для их запуска с вкладки Разработчик - Вставить - Кнопка (Developer - Insert - Button).
Способ 4. Скрытие строк/столбцов с заданным цветом
Допустим, что в приведенном выше примере мы, наоборот, хотим скрыть итоги, т.е. фиолетовые и черные строки и желтые и зеленые столбцы. Тогда наш предыдущий макрос придется немного видоизменить, добавив вместо проверки на наличие "х" проверку на совпадение цвета заливки с произвольно выбранными ячейками-образцами:
Sub HideByColor()
Dim cell As Range
Application.ScreenUpdating = False
For Each cell In ActiveSheet.UsedRange.Rows(2).Cells
If cell.Interior.Color = Range("F2").Interior.Color Then cell.EntireColumn.Hidden = True
If cell.Interior.Color = Range("K2").Interior.Color Then cell.EntireColumn.Hidden = True
Next
For Each cell In ActiveSheet.UsedRange.Columns(2).Cells
If cell.Interior.Color = Range("D6").Interior.Color Then cell.EntireRow.Hidden = True
If cell.Interior.Color = Range("B11").Interior.Color Then cell.EntireRow.Hidden = True
Next
Application.ScreenUpdating = True
End Sub
Однако надо не забывать про один нюанс: этот макрос работает только в том случае, если ячейки исходной таблицы заливались цветом вручную, а не с помощью условного форматирования (это ограничение свойства Interior.Color). Так, например, если вы с помощью условного форматирования автоматически подсветили в своей таблице все сделки, где количество меньше 10:
...и хотите их скрывать одним движением, то предыдущий макрос придется "допилить". Если у вас Excel 2010-2013, то можно выкрутиться, используя вместо свойства Interior свойство DisplayFormat.Interior, которое выдает цвет ячейки вне зависимости от способа, которым он был задан. Макрос для скрытия синих строк тогда может выглядеть так:
Sub HideByConditionalFormattingColor()
Dim cell As Range
Application.ScreenUpdating = False
For Each cell In ActiveSheet.UsedRange.Columns(1).Cells
If cell.DisplayFormat.Interior.Color = Range("G2").DisplayFormat.Interior.Color Then cell.EntireRow.Hidden = True
Next
Application.ScreenUpdating = True
End Sub
Ячейка G2 берется в качестве образца для сравнения цвета. К сожалению, свойство DisplayFormat появилось в Excel только начиная с 2010 версии, поэтому если у вас Excel 2007 или старше, то придется придумывать другие способы.
Ссылки по теме
- Что такое макрос, куда вставлять код макроса, как их использовать
- Автоматическая группировка в многоуровневых списках
Круто!!!.
нужно будет написать
где 3 - код вашего цвета заливки
Что нужно поменять в макросе, чтобы проверял не весь столбец, а определенный диапазон (например, столбец 1, строки со 2 по 400).
Спасибо!
Подскажите пожалуйста, что нужно изменить в макросе, что бы проверяло диапазон D20: D59
и если там пусто то удаляло строку. (как проверять диапазон я понял)
P.S. и будет ли удалять если в строке есть формулы?
Заранее спасибо!
Sub DeleteRows for s=59 to 20 step -1 if Len(Range("D" & s))=0 Then Range("D" & s).EntireRow.Delete Next s End SubПодскажите, тогда, а что изменить в макросе, чтобы скрытие/отображение строк/столбцов при запуске макроса (как в примере) затрагивало не только конкретный лист, а всю книгу??
Sub HideColumns() With Worksheets("Лист1") .Columns(1).Hidden=True .Columns(5).Hidden=False ...и так далее End With End SubПри совместном доступе к файлу, все столбцы, скрытые вручную (Способ 1), автоматически отображаются при открытии. Победить это мы не можем уже давно...
Ваш макрос просто находка!
Заранее спасибо!
Подскажите пожалуйста, что нужно изменить в макросе, что бы скрывал по кнопке диапазон столбцов (D5:K5)
и возможно ли сделать таких кнопок около 30шт. с разными диапазонами.
Заранее спасибо!
если одновременно три ячейки строки в столбцах N, S и X равны нулю.
Спасибо пребольшое заранее!!
Спасибо за идею с макросом.
Вопрос: У меня 2500 тысячи строк. Макрос проходит, удаляет половину и останавливается. Запускаешь его он снова проходит, удаляет половину пустых строк и так далее. Т.е. для того чтобы со всего листа удалить пустые строки необходимо запустить макрос несколько 12 раз. Почему так происходит?
Cell.EntireRow.Delete.
Сам макрос выглядит так:
Sub ()
Dim Cell As Range
Application.ScreenUpdating = False
1 For Each Cell In ActiveSheet.UsedRange.Columns(6).Cells
If Cell.Value = "0" Then Cell.EntireRow.Delete
Next
'2 и далее идет повторение 12 раз.
Application.ScreenUpdating = False
End sub
Подскажите, если необходимо скрытие закрашенных строк в черный цвет (часть из них окрашиваются в черный цвет в ручную, вторая часть условным форматированием), что необходимо изменить в коде? И что нужно изменить, чтобы показывались только эти строки?
Спасибо.
подскажите, как прописать макрос скрытия НЕ выделенных курсором строк (т.е. выделяю нужные строки мышкой, макрос скрывает остальные строки).
А вообще задача более глобальная (возможно не для этой темы): 1) нажимаю на одну из множества ячеек с гиперссылкой; 2) когда перехожу по гиперссылке на другой лист, то вижу только те строки в таблице, которые входят в диапазон имени (диспетчера имен).
Подскажите пожалуйста:
1) Можно ли создать макрос, который будет скрывать и отображать строки автоматически, без кнопок и команд. Например появилась в ячейке А1 цифра 5 (ячейка соответственно равна другой ячейке), то строка появилась. А если ячейка А1 пустая, то строка скрылась.
2) Можно ли отображать строки при помощи макроса. Если например в ячейке А10 несколько цифр 8 903 123 45 67. А мне нужно отобразить строку, если есть совпадение с цифрами. Скажем 123. Если нет совпадения, то строка скрывается. Также без кнопок и команд.
Спасибо!
Подскажите, пожалуйста, как скорректировать макрос 3, чтобы при выборе определенного значения в ячейке из выпадающего списка, строки под ячейкой либо скрывались, либо раскрывались.
Николай, благодарю за пример!
Подскажите пож-та, для способа 3 - можно сделать так, чтобы при первом нажатии на кнопку всё скрывалось по условию..., а при втором - раскрывалось? Т.е. чтобы была одна кнопка, а не две? Благодарю!
Прошу помочь вот с чем:
Есть книга на кучу столбцов. Сейчас часть столбцов прячется разными кнопками. Разные кнопки - разные столбцы.
Как завязать макрос на выпадающий список?
Чтоб прятались столбцы в зависимости от значения в выпадающем списке
(к примеру:
показать все
1 квартал (стобцы D-G)
2 квартал (стобцы h-j)
3 квартал и т.д.
Присоединяюсь к предыдущему вопросу, только в плане отображения только строк из выпадающего списка. На примере Вашей таблицы (продажи / сервис / аренда / все)
Вопрос по вашему способу 4.
Сделал - все работает. Вопрос: как сделать чтобы теперь открывались строки определенного цвета?
Вообщем нужен макрос Show для открытия определенного цвета.
Спасибо!