Главная » Статьи » Excel » Макросы и программы VBA

Автоматическое скрытие/показ столбцов и строк

Чего хотел то?

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

Синтез

Каждый приём, примененный в этом учебном примере, - банальщина, а сведенные воедино - уже некая магия. Какие же приёмы мы объединили:

  • Формулы

  • Форматы данных

  • Условное форматирование

  • Макрос

  • Элементы управления

Суть учебного примера

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

Скачать пример

Разбор решения по шагам:

  1. Смотрим лист Шаг 1. Рисуем жирную рамку вокруг диапазона D2:AY23. Зона для построения прямоугольника - E3:AX22.

  2. В ячейке A1 будем хранить ширину прямоугольника, который нам надо будет нарисовать звёздочками. В ячейке A2 - высоту.

  3. На пересечении строки 25 и столбца BA намечаем единичками наш будущий пояс видимости. Что это такое, расскажу позже.

  4. Создаём 2 именованных диапазона для A1 и A2 с названиями Ширина и Высота соответственно.

  5. В ячейку E3 вводим формулу
    =ЕСЛИ(И(СТОЛБЕЦ()<=Ширина;СТРОКА()<=Высота);1;0) или
    =IF(AND(COLUMN()<=Ширина;ROW()<=Высота);1;0),
    распространяем на весь диапазон E3:AX22.

  6. Смотрим лист Шаг 2. Меняем формат данных диапазона E3:AX22 на ";;;". Так же поступаем с A1:A2. Этот пустой формат данных очень удобен тогда, когда наши ячейки содержат служебную вспомогательную информацию и мы не хотим визуализации на листе их значений.

  7. Через ленту РАЗРАБОТЧИК при включенном Режиме конструктора, через кнопку Вставить, добавляем 2 элемента управления типа Счётчик. Изменяем их как показано на экранах. Данные счётчики будут использованы для изменения значения диапазонов Ширина и Высота.



  8. Теперь разберемся с поясом видимости. Этот пояс будет обрабатываться макросом, который в чётком соответствии с ним будет скрывать или показывать соответствующие строки или столбцы. Например, если в F25 будет 1, то столбец F будет видимым, а если 0, то макрос скроет этот столбец. Соответственно, если BA5 будет содержать 1, то строка 5 будет видима, а если 0, то будет скрыта. Ячейка BA25 будет отвечать за видимость и строки 25, и столбца BA одновременно.
    В ячейки, чьи столбцы/строки, всегда должны быть видимыми (например A25:D25, которые отвечают за столбцы A:D) можно ввести просто единички. А вот в ячейки, которые должны управлять видимостью в динамике, мы введём формулы. E25, например, будет содержать =ЕСЛИ(ИЛИ(СУММ(E3:E22)>0;Видимость);1;0) или =IF(OR(SUM(E3:E22)>0;Видимость);1;0). Для нашего удобства я ввёл ещё один именованный диапазон Видимость, который располагается на листе Настройки и позволяет для наших нужд сделать все строки/столбцы видимыми, что может потребоваться вам для внесения изменений на листе (в противном случае вам пришлось бы делать видимыми столбцы и строки вручную, что довольно неудобно). Если Видимость истинна, то все наши сигнальные ячейки пояса верности видимости становятся равными всегда 1, вне зависимости от значений ячеек диапазона E3:AX22. В этом виновата формула ИЛИ (OR), которая возвращает ИСТИНА, если хотя бы 1 из её авгументов принял значение ИСТИНА.

  9. Займёмся рисованием звёздочек на месте единичек в диапазоне E3:AX22. Создадим правило условного форматирования, как показано на экранах.



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

  11. Макрос, оформленный в виде процедуры ShowHide, будем вызывать из событий изменения счётчиков

    а также события активации листа


Сводим всё воедино

Итак, на итоговом листе Шаг 3 все компоненты собраны воедино. Элементы управления счётчики настроены на изменение именованных диапазонов Ширина и Высота, которые через формулы влияют на появление единичек в диапазоне E3:AX22. На эти единички реагирует условное форматирование, рисуя на их месте звёздочки, и на них же реагируют ячейки пояса видимости, принимая значения 1 для тех столбцов/строк, которые должны быть видимыми, и 0 - для тех, что должны быть скрыты. Макрос, который скрывает/показывает строки и столбцы вызывается из события изменения счётчика и события активации листа.

Вывод

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

Читайте также:

Категория: Макросы и программы VBA | Добавил: dsb75 (07.08.2014) | Автор: Батьянов Денис E W
Просмотров: 6346 | Теги: hide/show columns or rows | Рейтинг: 5.0/2
Всего комментариев: 0
Добавлять комментарии могут только зарегистрированные пользователи.
[ Регистрация | Вход ]
Яндекс.Метрика