Схаванне/паказ непатрэбных радкоў і слупкоў

Пастаноўка задачы

Выкажам здагадку, у нас ёсць такая табліца, з якой трэба кожны дзень «танчыць»:

 

To whom the table seems small – mentally multiply it twenty times by area, adding a couple more blocks and two dozen large cities. 

Задача складаецца ў тым, каб часова прыбраць з экрана непатрэбныя ў дадзены момант для працы радкі і слупкі, г. зн. 

  • схаваць дэталі па месяцах, пакінуць толькі кварталы
  • схаваць вынікі па месяцах і кварталах, пакінуўшы толькі вынік за паўгода
  • схаваць непатрэбныя на дадзены момант гарады (працую ў Маскве – навошта мне Санкт-Пецярбург?) і г.д.

У рэальным жыцці прыкладаў такіх сталоў мора.

Спосаб 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 - схаваць слупок Далей Для кожнай ячэйкі 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 Sub

Як няцяжка здагадацца, макрас Хаваць хаваецца і макрас шоў – Адлюстроўвае пазначаныя радкі і слупкі. Пры жаданні макрасам можна прызначыць гарачыя клавішы (Alt + F8 і кнопка параметры), або стварыць кнопкі непасрэдна на аркушы, каб запускаць іх з укладкі Распрацоўшчык – Уставіць – Кнопка (Распрацоўшчык — Уставіць — Кнопка).

Спосаб 4. Схаванне радкоў/слупкоў зададзеным колерам

Дапусцім, у прыведзеным вышэй прыкладзе мы, наадварот, хочам схаваць вынікі, гэта значыць фіялетавыя і чорныя радкі і жоўтыя і зялёныя слупкі. Тады наш папярэдні макрас трэба будзе крыху змяніць, дадаўшы замест праверкі на наяўнасць «х» праверку на адпаведнасць колеру залівання выпадкова выбраным выбаркам вочак:

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 = Праўда Калі cell.Interior.Color = Range("K2").Interior.Color Then cell.EntireColumn.Hidden = Праўда Далей Для кожнай ячэйкі ў ActiveSheet.UsedRange.Columns(2).Cells Калі cell.Interior.Color = Дыяпазон ("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 або старэй, то прыйдзецца прыдумляць іншыя спосабы.

  • Што такое макрас, куды ўставіць код макраса, як ім карыстацца
  • Аўтаматычная групоўка ў шматузроўневых спісах

 

Пакінуць каментар