зміст
Постановка проблеми
Припустимо, у нас є такий стіл, з яким потрібно «танцювати» кожен день:
Кому таблиця здається маленькою – подумки помножте її двадцять разів на площу, додавши ще пару кварталів і два десятки великих міст.
Завдання полягає в тому, щоб тимчасово видалити з екрану рядки і стовпці, які на даний момент непотрібні для роботи, тобто
- приховати деталі по місяцях, залишивши лише квартали
- приховати підсумки по місяцях і кварталах, залишивши тільки підсумок за півроку
- приховати непотрібні на даний момент міста (працюю в Москві – навіщо мені Петербург дивитися?) тощо.
У реальному житті прикладів таких таблиць море.
Спосіб 1: Приховання рядків і стовпців
Спосіб, відверто кажучи, примітивний і не дуже зручний, але про нього можна сказати два слова. Будь-які раніше вибрані рядки або стовпці на аркуші можна приховати, клацнувши правою кнопкою миші заголовок стовпця або рядка та вибравши команду з контекстного меню приховувати (Приховати):
Для реверсивного відображення виділіть сусідні рядки/стовпці та, клацнувши правою кнопкою миші, виберіть у меню відповідно дисплей (Показати).
Проблема в тому, що вам доводиться мати справу з кожним стовпцем і рядком окремо, що незручно.
Спосіб 2. Групування
Якщо вибрати кілька рядків або стовпців, а потім вибрати з меню Дані – Група та Структура – Група (Дані — Група та Схема — Група), то вони будуть укладені в квадратні дужки (згруповані). Крім того, групи можуть бути вкладені одна в іншу (допускається до 8 рівнів вкладеності):
Зручнішим і швидшим способом є використання комбінації клавіш для групування попередньо вибраних рядків або стовпців. Alt+Shift+стрілка вправо, і для розгрупування Alt+Shift+стрілка вліво, відповідно.
Такий спосіб приховування непотрібних даних набагато зручніший – можна або натиснути на кнопку з «+"Або"-», або на кнопках з числовим рівнем угруповання у верхньому лівому кутку аркуша – тоді всі групи потрібного рівня будуть згорнуті або розгорнуті відразу.
Крім того, якщо ваша таблиця містить підсумкові рядки або стовпці з функцією підсумовування сусідніх клітинок, тобто шанс (не на 100%), що Excel він створить усі необхідні групи в таблиці одним рухом – через меню Дані – Група та структура – Створення структури (Дані — Група та структура — Створити структуру). На жаль, така функція працює дуже непередбачувано і іноді робить повну нісенітницю на складних таблицях. Але спробувати можна.
У Excel 2007 і новіших версіях усі ці радості є на вкладці дані (Дата) В групі структура (Контур):
Спосіб 3. Приховування позначених рядків/стовпців за допомогою макросу
Цей спосіб є, мабуть, найбільш універсальним. Давайте додамо порожній рядок і порожній стовпець на початку нашого аркуша і позначимо будь-яким значком ті рядки і стовпці, які ми хочемо приховати:
Тепер давайте відкриємо редактор Visual Basic (ALT + F11), вставте новий порожній модуль в нашу книгу (меню Вставка – модуль) і скопіюйте туди текст двох простих макросів:
Sub Hide() Dim cell As Range Application.ScreenUpdating = False 'Вимкнути оновлення екрана для прискорення Для кожної комірки в ActiveSheet.UsedRange.Rows(1).Cells 'Перебирати всі клітинки в першому рядку, якщо cell.Value = "x " Тоді клітинка .EntireColumn.Hidden = True 'якщо в клітинці x - приховати стовпець Далі Для кожної клітинки в ActiveSheet.UsedRange.Columns(1).Cells 'пройти через усі клітинки першого стовпця Якщо cell.Value = "x" Тоді cell.EntireRow.Hidden = True 'якщо в комірці x - приховати рядок Next Application.ScreenUpdating = True End Sub Sub Show() Columns.Hidden = False 'скасувати всі приховані рядки та стовпці Rows.Hidden = False End Sub Sub
Як неважко здогадатися, макрос приховувати хованки та макрос шоу – Відображає рядки та стовпці з позначками. При бажанні макросам можна призначити гарячі клавіші (Alt + F8 і кнопку параметри), або створіть кнопки безпосередньо на аркуші, щоб запускати їх із вкладки Розробник – Вставити – Кнопка (Розробник — Вставити — Кнопка).
Спосіб 4. Приховування рядків/стовпців заданим кольором
Скажімо, у наведеному вище прикладі ми, навпаки, хочемо приховати підсумки, тобто фіолетові та чорні рядки та жовті та зелені стовпці. Тоді наш попередній макрос доведеться дещо змінити, додавши замість перевірки на наявність «x» перевірку відповідності кольору заливки з випадково вибраними клітинками зразка:
Sub HideByColor() Dim cell As Range Application.ScreenUpdating = False Для кожної клітинки в 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 Для кожної комірки в ActiveSheet.UsedRange.Columns(2).Cells Якщо 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, ви можете вийти, використовуючи замість властивості Інтер'єр власність DisplayFormat.Інтер'єр, який виводить колір комірки, незалежно від того, як його було встановлено. Макрос для приховування синіх ліній може виглядати так:
Sub HideByConditionalFormattingColor() Dim cell As Range Application.ScreenUpdating = False Для кожної клітинки в 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 або старше, доведеться придумувати інші способи.
- Що таке макрос, куди вставляти код макросу, як ним користуватися
- Автоматичне групування в багаторівневі списки