Содержание:
Постановка задачи
Предположим, у нас есть такая таблица, с которой нам приходится «танцевать» каждый день:
Кому таблица покажется маленькой – мысленно умножьте ее раз двадцать на площадь, добавив еще пару кварталов и два десятка крупных городов.
Задача состоит в том, чтобы временно убрать с экрана ненужные в данный момент для работы строки и столбцы, т.е.
- скрыть детали по месяцам, оставив только кварталы
- скрыть итоги по месяцам и кварталам, оставив только итог за полгода
- скрыть ненужные на данный момент города (я работаю в Москве – зачем мне видеть Питер?) и т.д.
В реальной жизни примеров таких таблиц море.
Способ 1: скрытие строк и столбцов
Метод, прямо скажем, примитивный и не очень удобный, но два слова о нем сказать можно. Любые ранее выбранные строки или столбцы на листе можно скрыть, щелкнув правой кнопкой мыши заголовок столбца или строки и выбрав команду из контекстного меню. Спрятать (Спрятать):
Для обратного отображения выделите соседние строки/столбцы и, щелкнув правой кнопкой мыши, выберите в меню соответственно дисплей (Показать).
Проблема в том, что с каждым столбцом и строкой приходится иметь дело индивидуально, а это неудобно.
Способ 2. Группировка
Если вы выберете несколько строк или столбцов, а затем выберите в меню Данные – Группа и Структура – Группа (Данные — Группа и Структура — Группа), то они будут заключены в квадратную скобку (сгруппированы). При этом группы могут быть вложены одна в другую (допускается до 8 уровней вложенности):
Более удобный и быстрый способ — использовать сочетание клавиш для группировки заранее выбранных строк или столбцов. Alt+Shift+Стрелка вправои для разгруппировки Alt+Shift+Стрелка влево, Соответственно.
Этот способ сокрытия ненужных данных гораздо удобнее – можно либо нажать на кнопку с надписью «+, либо-«, либо на кнопки с числовым уровнем группировки в левом верхнем углу листа — тогда все группы нужного уровня будут свернуты или развернуты сразу.
Таким образом, если ваша таблица содержит итоговые строки или столбцы с функцией суммирования соседних ячеек, то есть вероятность (не 100% правда), что Excel он создаст все необходимые группы в таблице одним движением – через меню Данные – Группа и структура – Создать структуру (Данные — Группировать и Структура — Создать структуру). К сожалению, такая функция работает очень непредсказуемо и иногда творит полную ерунду на сложных таблицах. Но вы можете попробовать.
В Excel 2007 и новее все эти радости находятся на вкладке Данные (Дата) в группе Структура (Контур):
Способ 3. Скрытие выделенных строк/столбцов с помощью макроса
Этот метод, пожалуй, самый универсальный. Добавим пустую строку и пустой столбец в начало нашего листа и отметим любым значком те строки и столбцы, которые мы хотим скрыть:
Теперь давайте откроем редактор Visual Basic (ALT + F11), вставляем в нашу книгу новый пустой модуль (меню Вставка – Модуль) и скопируйте туда текст двух простых макросов:
Sub Hide() Затемнить ячейку как диапазон Application.ScreenUpdating = False 'Отключить обновление экрана для ускорения для каждой ячейки в ActiveSheet.UsedRange.Rows(1).Cells 'Перебрать все ячейки в первой строке, если cell.Value = "x " Тогда ячейка .EntireColumn.Hidden = True 'если в ячейке x - скрыть столбец Далее для каждой ячейки In ActiveSheet.UsedRange.Columns(1).Cells 'пройти через все ячейки первого столбца If cell.Value = "x" Тогда cell.EntireRow.Hidden = True 'если в ячейке x - скрыть строку Next Application.ScreenUpdating = True End Sub Sub Show() Columns.Hidden = False 'отменить скрытие всех строк и столбцов Rows.Hidden = False End Sub
Как вы могли догадаться, макрос Спрятать скрывается и макрос Показать – Отображает строки и столбцы с обратными метками. При желании макросам можно назначить горячие клавиши (Alt + F8 и кнопка параметры) или создайте кнопки прямо на листе, чтобы запускать их с вкладки. Разработчик – Вставка – Кнопка (Разработчик — Вставка — Кнопка).
Способ 4. Скрытие строк/столбцов заданным цветом
Допустим, в приведенном выше примере мы, наоборот, хотим скрыть итоги, то есть фиолетовые и черные строки и желтые и зеленые столбцы. Тогда наш предыдущий макрос придется немного доработать, добавив вместо проверки на наличие «х» проверку на соответствие цвета заливки случайно выбранным ячейкам выборки:
Sub HideByColor() Затемнить ячейку как диапазон Application.ScreenUpdating = False для каждой ячейки в ActiveSheet.UsedRange.Rows(2).Cells Если cell.Interior.Color = Range("F2").Interior.Color Тогда cell.EntireColumn.Hidden = Истина Если cell.Interior.Color = Диапазон("K2").Interior.Color Тогда cell.EntireColumn.Hidden = Истина Далее Для каждой ячейки в ActiveSheet.UsedRange.Columns(2).Cells Если cell.Interior.Color = Диапазон ("D6").Interior.Color Тогда cell.EntireRow.Hidden = True Если cell.Interior.Color = Range("B11").Interior.Color Тогда cell.EntireRow.Hidden = True Следующее приложение.ScreenUpdating = True End Sub Однако нельзя забывать об одном нюансе: этот макрос работает только в том случае, если ячейки исходной таблицы были заполнены цветом вручную, а не с использованием условного форматирования (это ограничение свойства Interior.Color). Так, например, если вы автоматически выделили в таблице все сделки, число которых меньше 10, с помощью условного форматирования:
… и вы хотите их скрыть одним движением, тогда предыдущий макрос придется «доделать». Если у вас Excel 2010-2013, то можно выйти используя вместо свойства Дизайн собственность DisplayFormat.Interior, который выводит цвет ячейки независимо от того, как он был установлен. Макрос для скрытия синих линий может выглядеть так:
Sub HideByConditionalFormattingColor() Тусклая ячейка как диапазон Application.ScreenUpdating = False для каждой ячейки в ActiveSheet.UsedRange.Columns(1).Cells If cell.DisplayFormat.Interior.Color = Range("G2").DisplayFormat.Interior.Color Тогда ячейка .EntireRow.Hidden = True Следующее приложение.ScreenUpdating = True End Sub Ячейка G2 используется в качестве образца для сравнения цветов. К сожалению, собственность Формат отображения появился в Excel только начиная с 2010 версии, поэтому если у вас Excel 2007 или старше, вам придется придумывать другие способы.
- Что такое макрос, куда вставлять код макроса, как их использовать
- Автоматическая группировка в многоуровневые списки