Kako skriti vrstice na podlagi vrednosti celice v Excelu (2 enostavni metodi)

bi radi vedeli, kako skrij vrstice na podlagi vrednosti celice v Excelu? Delo z velikimi nabori podatkov, ki vsebujejo vrstice za vrsticami podatkov, je lahko precej zapleteno. V takih primerih je iskanje podatkov, ki ustrezajo določenim kriterijem, lahko podobno iskanju igle v kupu sena. Na srečo Excel ponuja nekaj funkcij, ki vam omogočajo, da skrijete določene vrstice glede na vrednost celice, tako da vidite samo vrstice, ki jih želite videti. To lahko storite na dva načina:

• Uporaba filtrov
• Uporaba VBA

V tej vadnici bomo razpravljali o obeh metodah, vi pa lahko izberete način, ki vam najbolj ustreza.

Tukaj se lahko naučite o: Kako preprosto skriti vrstice v Excelu. 3 enostavne metode

Uporaba filtrov za skrivanje vrstic na podlagi vrednosti celice v Excelu

Recimo, da imate nabor podatkov, prikazan spodaj, in želite videti podatke samo o zaposlenih, ki so še zaposleni.

To je zelo enostavno narediti z uporabo filtri. To so koraki, ki jih morate upoštevati:

1. korak 1: Izberite delovno področje vašega nabora podatkov.
2. korak 2: V zavihku Podatkiizberite Gumb za filter. Najdete ga v skupini 'Razvrstite in filtrirajte".

1. korak 3: zdaj bi morali videti majhen puščični gumb v vsaki celici vrstice glave.

1. korak 4: Ti gumbi so namenjeni za pomoč pri filtriranju celic. Lahko kliknete katero koli puščico, da izberete filter za ustrezen stolpec.
2. korak 5: V tem primeru želimo filtrirati vrstice, ki vsebujejo status zaposlitve = «V službi«. Zato izberite puščico poleg glave Zaposlitveni status.
3. korak 6: Počistite polja poleg vseh držav, razen «V službi «. Lahko preprosto počistite » Izberi vse » za hitro počistite vse in nato izberite » V službi ".

1. korak 7: Kliknite V redu.

Zdaj bi morali videti samo vrstice z Zaposlitveni status = » V službi «. Vse druge vrstice bi morale biti zdaj skrite.

OPOMBA: Če želite prikazati skrite celice, preprosto znova kliknite gumb. Gumb za filter.

Uporaba VBA za skrivanje vrstic na podlagi vrednosti celice v Excelu

Druga metoda zahteva nekaj kodiranja. Če ste navajeni uporabljati makre in nekaj kodiranja z uporabo VBA, potem dobite veliko več možnosti in prilagodljivosti za manipulacijo s podatki, da se bodo obnašali točno tako, kot želite.

1. način: Pisanje kode VBA za skrivanje vrstic na podlagi vrednosti celice v Excelu

Naslednja koda vam bo pomagala prikazati samo vrstice, ki vsebujejo informacije o zaposlenih, ki so 'v službi' in skrije vse druge vrstice:

SubHideRows()

Začetna vrstica = 2

Končna vrstica = 19

Stolpec = 3

Za i = StartRow do EndRow

If Cells (i, ColNum).Vrednost <> «V uporabi» Potem

Cells(i, ColNum).EntireRow.Hidden = True

Ostalo

Cells(i, ColNum).EntireRow.Hidden = False

Končalo se bo, če

poleg mene

End Sub

Makro preleti vsako celico v stolpcu C in skrije vrstice, ki ne vsebujejo vrednosti "V službi". V bistvu morate razčleniti vsako celico od vrstic 2 do 19 in prilagoditi 'atributSkrit' vrstice, ki jo želite skriti.

Če želite vnesti zgornjo kodo, jo kopirajte in prilepite v okno razvijalca. Evo kako:

1. korak 1: Na traku menija razvijalec, Izberite Visual Basic.

Ko se odpre okno VBA, boste videli vse svoje datoteke in mape v Raziskovalcu projektov na levi strani. Če ne vidite raziskovalec projektakliknite Pogled->Raziskovalec projektov.

Prepričaj se 'Ta delovni zvezek' je izbran v projektu VBA z istim imenom kot vaš Excelov delovni zvezek.

2. korak 2: Kliknite Vstavi->Modul. Morali bi videti odprto okno novega modula.

Zdaj lahko začnete s kodiranjem. Kopirajte zgornje vrstice kode in jih prilepite v novo okno modula.

V tem primeru želimo skriti vrstice, ki ne vsebujejo vrednosti ' V službi ' v stolpcu 3. Lahko pa zamenjate vrednost števila Stolpec »3« v vrstici 4 s številko stolpca, ki vsebuje vrednosti vaših kriterijev.

3. korak 3: Zaprite okno VBA.

OPOMBA: Če vaš nabor podatkov zajema več kot 19 vrstic, lahko spremenite vrednosti spremenljivk StartRow in EndRow na njihove zahtevane številke vrstic.

Če ne vidite traku za razvijalce, v Meni Datoteka, Pojdi do možnosti . Izberite Prilagodi trak in na zavihkih označite možnost Razvijalec Glavni .

4. korak 4: Na koncu kliknite sprejemajo.

Vaš makro je zdaj pripravljen za uporabo.

2. način: Zagon makra za skrivanje vrstic na podlagi vrednosti celice v Excelu

Kadarkoli boste morali uporabiti zgornji makro, ga morate samo zagnati, in to:

1. korak 1: izberite zavihek Razvijalec
2. korak 2: Kliknite gumb Makri (v skupini Koda ).

1. korak 3: To bo odprlo Makro okno , kjer boste našli imena vseh makrov, ki ste jih ustvarili do sedaj.
2. korak 4: Izberite klicani makro (ali modul). "HideRows" in kliknite gumb Teči .

Morali bi videti vse vrstice, kjer je Zaposlitveni status Ni skrito kot » V službi ".

3. način: Razlaga kode za skrivanje vrstic na podlagi vrednosti celice v Excelu

Tukaj je razlaga zgornje kode po vrsticah:

V vrstici 1 določimo ime funkcije.

• SubHideRows()

V vrsticah 2, 3 in 4 definiramo spremenljivke za začetno vrstico in končno vrstico nabora podatkov ter indeks stolpca kriterijev.

• Začetna vrstica = 2
• Končna vrstica = 19
• Stolpec = 3

V vrsticah od 5 do 11 se premikamo po vsaki celici v stolpcu »3« (ali stolpcu C) aktivnega delovnega lista. Če celica ne vsebuje vrednosti «V službi«, potem nastavimo atribut "Skrit" celotne vrstice (ki ustreza tej celici) v Prav, kar pomeni, da želimo skriti celotno ustrezno vrstico.

• Za i = StartRow do EndRow
• If Cells (i, ColNum).Vrednost <> «V uporabi» Potem
• Cells(i, ColNum).EntireRow.Hidden = True
• Ostalo
• Cells(i, ColNum).EntireRow.Hidden = False
• Konec bo, če
• poleg mene

Vrstica 12 preprosto razmejuje konec funkcije HideRows .

• End Sub

Na ta način zgornja koda skrije vse vrstice, ki ne vsebujejo vrednosti ' V službi ' v stolpcu C.

Prikaži stolpce na podlagi vrednosti celice

Zdaj, ko smo lahko uspešno skrili neželene vrstice, kaj če želimo spet videti skrite vrstice?

To je zelo enostavno narediti. Narediti morate le majhno spremembo funkcije HideRows .

SubUnhideRows()

Začetna vrstica = 2

Končna vrstica = 19

Stolpec = 3

Za i = StartRow do EndRow

Cells(i, ColNum).EntireRow.Hidden = False

poleg mene

End Sub

Tukaj preprosto poskrbimo, da so ne glede na vrednost prikazane vse vrstice (nastavitev lastnosti Hidden vseh vrstic na False).

Ta makro lahko zaženete na povsem enak način kot HideRows.

Skrij vrstice na podlagi vrednosti celic v realnem času

V prvem primeru so stolpci skriti le, ko se izvaja makro. Vendar pa večino časa želimo skriti stolpce sproti, glede na vrednost določene celice.

Zdaj pa si poglejmo še en primer, ki to dokazuje. V tem primeru imamo naslednji niz podatkov:

Edina razlika od prvega niza podatkov je, da imamo v celici A21 vrednost, ki bo določila, katere vrstice naj bodo skrite. Torej, ko celica A21 vsebuje vrednost 'Upokojen', so skrite samo vrstice, ki vsebujejo Zaposlitveni status 'Upokojenec'.

Podobno, ko celica A21 vsebuje vrednost »Na poskusnem delu«, so skrite samo vrstice, ki vsebujejo status zaposlitve.V poskusnem obdobju".

Ko v celici A21 ni ničesar, želimo, da so prikazane vse vrstice.

Želimo, da se to zgodi v realnem času, vsakič, ko se spremeni vrednost v celici A21. Za to moramo uporabiti funkcijo Sprememba delovnega lista programa Excel.

Dogodek Worksheet_SelectionChange za skrivanje vrstic na podlagi vrednosti celice v Excelu

Postopek Sprememba delovnega lista je dogodek VBA, vgrajen v Excel

Prednameščen je z delovnim listom in se pokliče vsakič, ko uporabnik izbere celico in nato spremeni svojo izbiro v drugo celico.

Ker je ta funkcija vnaprej nameščena z delovnim listom, jo ​​morate postaviti v pravilen kodni modul delovnega lista, da jo lahko uporabljate.

V naši kodi bomo vse svoje vrstice postavili v to funkcijo, tako da se bodo izvajale vsakič, ko uporabnik spremeni vrednost v A21 in nato izbere drugo celico.

Pisanje kode VBA za skrivanje vrstic na podlagi vrednosti celice v Excelu

Tukaj je koda, ki jo bomo uporabili:

• Začetna vrstica = 2
• Končna vrstica = 19
• Stolpec = 3
• Za i = StartRow do EndRow
• Če Celice(i, Številka Stolpca).Vrednost = Obseg("A21"). Vrednost Potem
• Cells(i, ColNum).EntireRow.Hidden = True
• Ostalo
• Cells(i, ColNum).EntireRow.Hidden = False
• Konec bo, če
• poleg mene

Če želite vnesti prejšnjo kodo, jo morate znotraj postopka kopirati in prilepiti v okno razvijalca Sprememba delovnega lista s svojega lista delo. To storite tako:

1. korak 1: Na traku menija Razvijalec, Izberite Visual Basic.
2. korak 2: Ko se odpre okno VBA, boste videli vse datoteke in mape vašega projekta Raziskovalec projektov Na levi strani.
3. korak 3: Ob raziskovalec projekta , dvokliknite ime svojega delovnega lista pod projektom VBA z enakim imenom kot vaš Excelov delovni zvezek. V tem primeru delamo s Sheet1 .

1. korak 4: To bo odprlo novo okno modula za izbrani list.
2. korak 5: Kliknite spustno puščico na levi strani okna. Morali bi videti možnost, ki pravi «Delovni list« .

1. korak 6: Kliknite možnost 'delovni list'. Privzeto boste videli postopek Sprememba delovnega lista ustvarjen za vas v oknu modula.

1. korak 7: Zdaj lahko začnete kodirati. Kopirajte zgornje vrstice kode in jih prilepite v proceduro Sprememba delovnega lista , takoj za vrstico: Private Sub Worksheet_SelectionChange (ByVal Target As Range).

1. korak 8: Zaprite okno VBA.

Zagon makra za skrivanje vrstic na podlagi vrednosti celice v Excelu

Postopek Sprememba delovnega lista Zažene se takoj, ko končate kodiranje. Zato vam ni treba izrecno zagnati makra, da začne delovati.

1. korak 1: poskusite vnesti 'upokojen'v celici A21, nato kliknite katero koli drugo celico. Ugotoviti bi morali, da vse vrstice, ki vsebujejo vrednost 'Withdrawn' v stolpcu C, izginejo.

2. korak 2: Zdaj poskusite zamenjati vrednost v celici A21 z ' V poskusnem obdobju', nato kliknite katero koli drugo celico. Vse vrstice, ki vsebujejo vrednost 'On trial' v stolpcu C, bi morale izginiti.

V poskusnem obdobju

3. korak 3: Zdaj poskusite izbrisati vrednost v celici A21 in jo pustite prazno. Nato kliknite katero koli drugo celico. Ugotoviti morate, da obe vrstici znova postaneta vidni.

To pomeni, da koda deluje v realnem času, ko se celica B20 spremeni.

Koda Razlaga

Vzemimo si nekaj minut, da zdaj razumemo to kodo.

Vrstice 1, 2 in 3 določajo spremenljivke za začetno in končno vrstico nabora podatkov ter indeks stolpca s kriteriji. Vrednosti lahko spremenite glede na svoje potrebe.

• Začetna vrstica = 2
• Končna vrstica = 19
• Stolpec = 3

Vrstice od 4 do 10 potekajo skozi vsako celico v stolpcu »3« (ali stolpcu C) delovnega lista. Če celica vsebuje vrednost v celici A21, potem nastavimo atribut 'Skrito' celotne vrstice (ki ustreza tej celici) v Prav, kar pomeni, da želimo skriti celotno ustrezno vrstico.

• Za i = StartRow do EndRow
• Če Celice(i, Številka Stolpca).Vrednost = Obseg("A21"). Vrednost Potem
• Cells(i, ColNum).EntireRow.Hidden = True
• Ostalo
• Cells(i, ColNum).EntireRow.Hidden = False
• Konec bo, če
• poleg mene

Na ta način zgornja koda skrije vrstice nabora podatkov na podlagi vrednosti v celici A21. Če A21 ne vsebuje nobene vrednosti, koda prikaže vse vrstice.

Morda vas bo zanimalo tudi branje o: MS Excel: Kako uporabljati stavek FOR NEXT VBA v Excelu

Zaključek

V tej vadnici vam pokažemo, kako lahko uporabite Filtri in Excel VBA da skrijete vrstice glede na vrednost celice v Excelu. To smo storili s pomočjo dveh preprostih primerov: enega, ki izbriše zahtevane vrstice le, ko se makro izvede izrecno, in drugega, ki deluje v realnem času. Upamo, da smo vam uspešno pomagali razumeti koncept kode, da jo boste lahko prilagodili in uporabili v svojih aplikacijah.