Съдържание
Формулиране на проблема
Да предположим, че имаме такава маса, с която трябва да „танцуваме“ всеки ден:
На когото таблицата изглежда малка - мислено я умножете двадесет пъти по площ, като добавите още няколко блока и две дузини големи градове.
Задачата е временно да премахнете от екрана редове и колони, които в момента не са необходими за работа, т.е.
- скрийте детайлите по месеци, оставяйки само тримесечия
- скриване на суми по месеци и тримесечия, оставяйки само сумата за половин година
- скриване на ненужни в момента градове (работя в Москва – защо да виждам Санкт Петербург?) и др.
В реалния живот има море от примери за такива таблици.
Метод 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 'Итериране на всички клетки в първия ред If cell.Value = "x " След това клетка .EntireColumn.Hidden = True 'ако в клетка x - скрий колона Следваща за всяка клетка в 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 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 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, тогава можете да излезете, като използвате вместо свойството Интериор имот DisplayFormat.Interior, който извежда цвета на клетката, независимо как е зададен. След това макросът за скриване на сините линии може да изглежда така:
Sub HideByConditionalFormattingColor() Dim cell As Range Application.ScreenUpdating = False За всяка клетка в ActiveSheet.UsedRange.Columns(1).Cells If cell.DisplayFormat.Interior.Color = Range("G2").DisplayFormat.Interior.Color Тогава клетка .EntireRow.Hidden = True Next Application.ScreenUpdating = True End Sub Клетка G2 се взема като проба за цветово сравнение. За съжаление имотът DisplayFormat се появи в Excel едва от версията 2010, така че ако имате Excel 2007 или по-стар, ще трябва да измислите други начини.
- Какво е макрос, къде да вмъкнете макро код, как да ги използвате
- Автоматично групиране в многостепенни списъци