Kubisa/kusonyeza mizere ndi mizati yosafunikira

Kupanga vuto

Tiyerekeze kuti tili ndi tebulo lomwe tiyenera "kuvina" tsiku lililonse:

 

Kwa omwe tebulo likuwoneka laling'ono - muwachulukitse nthawi makumi awiri ndi dera, ndikuwonjezera midadada ingapo ndi mizinda yayikulu khumi ndi iwiri. 

Ntchito ndikuchotsa kwakanthawi pamizere yotchinga ndi mizati yomwe pakali pano ili yosafunikira kuntchito, mwachitsanzo, 

  • kubisa zambiri mwezi ndi mwezi, kusiya kotala basi
  • kubisa zonse m'miyezi ndi kotala, ndikusiyira theka la chaka
  • kubisa mizinda yomwe ili yosafunikira panthawiyi (ndimagwira ntchito ku Moscow - chifukwa chiyani ndiyenera kuwona St. Petersburg?), Ndi zina zotero.

Mu moyo weniweni, pali nyanja ya zitsanzo za matebulo amenewa.

Njira 1: Kubisa mizere ndi mizati

Njira, moona, ndi yakale komanso si yabwino kwambiri, koma mawu awiri anganene za izo. Mizere kapena mizere iliyonse yomwe yasankhidwa kale papepala ikhoza kubisika ndikudina kumanja pamzere kapena mutu wa mzere ndikusankha lamulo kuchokera pazosankha. Bisani (Bisani):

 

Kuti muwonetsere kumbuyo, sankhani mizere / mizere yoyandikana ndipo, podina kumanja, sankhani kuchokera pamenyu, motsatana, Chionetsero (Osabisala).

Vuto ndiloti muyenera kuthana ndi mzere uliwonse ndi mzere payekha, zomwe zimakhala zovuta.

Njira 2. Magulu

Ngati musankha mizere ingapo kapena mizati ndiyeno kusankha kuchokera menyu Deta - Gulu ndi Kapangidwe - Gulu (Deta - Gulu ndi Mauthenga - Gulu), kenako Adzakulungidwa m’gulu lalikulu (lamagulumagulu). Kuphatikiza apo, magulu amatha kukhala m'malo amodzi (mpaka zisa 8 zimaloledwa):

Njira yabwino komanso yachangu ndiyo kugwiritsa ntchito njira yachidule ya kiyibodi kuti muyike mizere yosankhidwa kale kapena mizati. Alt+Shift+Kumanja Muvi, ndi kulekanitsa Alt+Shift+Kumanzere Muvi, motero.

Njira yobisalira deta yosafunikira ndiyosavuta kwambiri - mutha dinani batani ndi "+"Kapena"-", kapena pa mabatani omwe ali ndi chiwerengero chamagulu pakona yakumanzere kwa pepala - ndiye kuti magulu onse omwe akufunidwa adzagwa kapena kukulitsidwa nthawi imodzi.

komanso, ngati tebulo lanu lili ndi mizere yachidule kapena mizati yokhala ndi ntchito yofotokozera mwachidule ma cell oyandikana nawo, ndiko kuti, mwayi (osati 100% woona) kuti Excel adzalenga magulu onse ofunikira mu tebulo ndi kayendedwe kamodzi - kudzera menyu Deta - Gulu ndi Kapangidwe - Pangani Kapangidwe (Deta - Gulu ndi Mauthenga - Pangani autilaini). Tsoka ilo, ntchitoyi imagwira ntchito mosayembekezereka ndipo nthawi zina imakhala yopanda pake pamatebulo ovuta. Koma mukhoza kuyesa.

Mu Excel 2007 ndi zatsopano, zosangalatsa zonsezi zili pa tabu Deta (Tsiku) pagulu   kapangidwe (Zotsatira):

Njira 3. Kubisa mizere/mizere yolembedwa ndi makuro

Njira imeneyi mwina ndiyo yosunthika kwambiri. Tiyeni tiwonjezere mzere wopanda kanthu ndi gawo lopanda kanthu kumayambiriro kwa pepala lathu ndikuyika chizindikiro ndi mizere iliyonse yomwe tikufuna kubisa:

Tsopano tiyeni titsegule Visual Basic Editor (ALT + F11), ikani gawo latsopano lopanda kanthu m'buku lathu (menyu Ikani - Module) ndikukopera zolemba za macros awiri osavuta pamenepo:

Sub Hide() Dim cell As Range Application.ScreenUpdating = Zabodza 'Zimitsani kukonzanso zenera kuti mufulumire Selo Lililonse Mu ActiveSheet.UsedRange.Rows(1).Maselo 'Iterate over all cell mumzere woyamba If cell.Value = "x " Kenako selo .EntireColumn.Hidden = Zoona 'ngati mu selo x - bisalani gawo Lotsatira Pa selo Lililonse Mu ActiveSheet.UsedRange.Columns(1).Maselo 'amadutsa mu maselo onse a ndime yoyamba Ngati selo.Value = "x" Ndiye cell.EntireRow.Hidden = Zoona 'ngati mu selo x - bisani mzere Wotsatira Ntchito.ScreenUpdating = True End Sub Sub Show() Columns.Hidden = Zabodza 'kuletsa mizere yobisala ndi mizati Mizere.Hidden = False End Sub

Monga momwe mungaganizire, macro Bisani zikopa ndi macro Onetsani - Imawonetsa mizere yolembedwa kumbuyo ndi mizere. Ngati mungafune, ma macros atha kupatsidwa ma hotkeys (Alt + F8 ndi batani magawo), kapena pangani mabatani papepala kuti muwatsegule pa tabu Wopanga - Ikani - Batani (Madivelopa - Ikani - Batani).

Njira 4. Kubisa mizere/mizere yokhala ndi mtundu woperekedwa

Tinene kuti mu chitsanzo pamwambapa, ife, m'malo mwake, tikufuna kubisa zonse, mwachitsanzo, mizere yofiirira ndi yakuda ndi mizati yachikasu ndi yobiriwira. Kenako macro athu am'mbuyomu akuyenera kusinthidwa pang'ono ndikuwonjezera, m'malo moyang'ana kukhalapo kwa "x", cheke chofananiza mtundu wodzaza ndi ma cell osankhidwa mwachisawawa:

Sub HideByColor() Dim cell As Range Application.ScreenUpdating = Zabodza Paselo Lililonse Mu ActiveSheet.UsedRange.Rows(2).Maselo Ngati selo.Interior.Color = Range("F2").Mkati.Color Kenako selo.Column Yonse.Yobisika. = Zoona Ngati selo.Mkati.Color = Range("K2").Mkati.Color Ndiye selo.EntireColumn.Hidden = True Next For Lililonse Selo Mu ActiveSheet.UsedRange.Columns(2).Maselo Ngati selo.Mkati.Color = Range ("D6").Mkati.Mkati Kenako selo.RowEntire.Obisika = Zoona Ngati selo.Mkati.Color = Range("B11").Mkati.Color Kenako cell.EntireRow.Hidden = True Next Application.ScreenUpdating = True End Sub

Komabe, tisaiwale za chenjezo limodzi: zazikuluzikuluzi zimagwira ntchito ngati maselo a gwero la tebulo adadzazidwa ndi mtundu pamanja, ndipo osagwiritsa ntchito masanjidwe ovomerezeka (izi ndizochepa za Interior.Color katundu). Chifukwa chake, mwachitsanzo, ngati mwangowunikira zotsatsa zonse patebulo lanu pomwe nambala ndi yochepera 10 pogwiritsa ntchito masanjidwe okhazikika:

Kubisa/kusonyeza mizere ndi mizati yosafunikira

... ndipo mukufuna kuwabisa mukuyenda kumodzi, ndiye kuti macro apitawo ayenera "kutha". Ngati muli ndi Excel 2010-2013, ndiye kuti mutha kutuluka pogwiritsa ntchito m'malo mwa katunduyo M'katikati katundu DisplayFormat.Mkati, yomwe imatulutsa mtundu wa selo, mosasamala kanthu kuti idakhazikitsidwa bwanji. Macro yobisa mizere yabuluu imatha kuwoneka motere:

Sub HideByConditionalFormattingColor() Dim cell As Range Application.ScreenUpdating = Zabodza Paselo Lililonse Mu ActiveSheet.UsedRange.Columns(1).Maselo Ngati selo.DisplayFormat.Interior.Color = Range("G2").DisplayFormat.Mkati.Color Kenako selo. .EntireRow.Hidden = True Next Application.ScreenUpdating = True End Sub

Cell G2 imatengedwa ngati chitsanzo poyerekezera mitundu. Tsoka ilo katundu DisplayFormat adawonekera mu Excel kuyambira mtundu wa 2010, ndiye ngati muli ndi Excel 2007 kapena kupitilira apo, muyenera kupeza njira zina.

  • Kodi macro ndi chiyani, momwe mungayikitsire macrocode, momwe mungagwiritsire ntchito
  • Kuyika m'magulu m'magulu ambiri

 

Siyani Mumakonda