Kako sakriti redove na osnovu vrijednosti ćelije u Excelu (2 jednostavna metoda)

želite li znati kako sakriti redove na osnovu vrijednosti ćelije u Excelu? Rad s velikim skupovima podataka koji sadrže redove po redovima podataka može biti prilično kompliciran. U takvim slučajevima, traženje podataka koji odgovaraju određenim kriterijima može biti poput traženja igle u plastu sijena. Srećom, Excel nudi neke funkcije koje vam omogućavaju da sakrijete određene redove na osnovu vrijednosti ćelije tako da vidite samo redove koje želite vidjeti. Postoje dva načina da to uradite:

• Korištenje filtera
• Koristeći VBA

U ovom vodiču ćemo razgovarati o obje metode, a vi možete odabrati način koji vam najviše odgovara.

Ovdje možete saznati o: Kako lako sakriti redove u Excelu. 3 lake metode

Korištenje filtera za sakrivanje redova na temelju vrijednosti ćelije u Excelu

Recimo da imate skup podataka prikazan ispod i želite da vidite podatke samo o zaposlenima koji su još uvijek u službi.

Ovo je zaista jednostavno za korištenje filteri. Ovo su koraci koje trebate slijediti:

1. Korak 1: Odaberite radnu oblast vašeg skupa podataka.
2. Korak 2: U kartici Podaciodaberite Dugme za filter. Naći ćete ga u grupi 'Poredaj i filtriraj'.

1. Korak 3: Sada biste trebali vidjeti malo dugme sa strelicom u svakoj ćeliji reda zaglavlja.

1. Korak 4: Ova dugmad su namijenjena da vam pomognu da filtrirate svoje ćelije. Možete kliknuti na bilo koju strelicu da odaberete filter za odgovarajuću kolonu.
2. Korak 5: U ovom primjeru želimo filtrirati redove koji sadrže status zaposlenja = «U službi«. Stoga, odaberite strelicu pored zaglavlja Zaposleni status.
3. Korak 6: Poništite izbor u poljima pored svih država osim «U službi «. Možete jednostavno poništiti kvačicu » Odaberite sve » da brzo poništite sve, a zatim odaberite » U službi ".

1. Korak 7: Kliknite OK.

Sada biste trebali moći vidjeti samo redove sa Status zaposlenja = » U službi «. Svi ostali redovi bi sada trebali biti sakriveni.

NOTA: Da biste prikazali skrivene ćelije, jednostavno kliknite na dugme ponovo. Dugme za filter.

Korišćenje VBA za sakrivanje redova na osnovu vrednosti ćelije u Excelu

Druga metoda zahtijeva malo kodiranja. Ako ste navikli na korištenje makronaredbi i malo kodiranja pomoću VBA, tada ćete dobiti mnogo više opsega i fleksibilnosti za manipulaciju svojim podacima kako bi se ponašali točno onako kako želite.

Metoda 1: Pisanje VBA koda za sakrivanje redova na osnovu vrijednosti ćelije u Excelu

Sljedeći kod će vam pomoći da prikažete samo redove koji sadrže informacije o zaposlenima koji su 'u službi' i sakriti će sve ostale redove:

SubHideRows()

StartRow = 2

Krajnji red = 19

Broj stupaca = 3

Za i = StartRow do EndRow

Ako Ćelije (i, ColNum).Vrijednost <> «U službi» Onda

Ćelije(i, ColNum).CijeliRow.Hidden = Tačno

Ostalo

Ćelije(i, ColNum).CijeliRed.Skriven = False

Završiće ako

pored mene

End Sub

Makro se kreće kroz svaku ćeliju u stupcu C i skriva redove koji ne sadrže vrijednost «U službi». U osnovi, trebate raščlaniti svaku ćeliju od redova 2 do 19 i prilagoditi 'atributSkrivena' reda koji želite sakriti.

Da unesete gornji kod, kopirajte ga i zalijepite u prozor programera. Evo kako:

1. Korak 1: Na traci menija programer, Odaberite Visual Basic.

Kada se otvori VBA prozor, videćete sve svoje datoteke i fascikle u Project Explorer-u na levoj strani. Ako ne vidite istraživač projektaklikni View->Project Explorer.

pobrini se 'ThisWorkbook' je izabran u VBA projektu sa istim imenom kao i vaša Excel radna sveska.

2. Korak 2: Kliknite Insert->Module. Trebali biste vidjeti otvoren prozor novog modula.

Sada možete početi s kodiranjem. Kopirajte gornje linije koda i zalijepite ih u novi prozor modula.

U ovom primjeru želimo sakriti redove koji ne sadrže vrijednost ' U službi ' u koloni 3. Ali možete zamijeniti vrijednost broja Kolum od "3" na liniji 4 brojem kolone koja sadrži vrijednosti vaših kriterija.

3. Korak 3: Zatvorite VBA prozor.

NOTA: Ako vaš skup podataka pokriva više od 19 redova, možete promijeniti vrijednosti varijabli StartRow i EndRow na njihove potrebne brojeve redova.

Ako ne vidite vrpcu za programere, u File meni, idi opcije . Odaberite Prilagodi vrpcu i provjerite opciju Developer na karticama principales .

4. Korak 4: Konačno, kliknite prihvatiti.

Vaš makro je sada spreman za upotrebu.

Metoda 2: Pokretanje makroa za sakrivanje redova na osnovu vrijednosti ćelije u Excelu

Kad god trebate koristiti gornji makro, sve što trebate učiniti je da ga pokrenete, a evo kako:

1. Korak 1: Odaberite karticu Programer
2. Korak 2: Kliknite na dugme Makroi (u grupi Kod ).

1. Korak 3: Ovo će otvoriti Makro prozor , gdje ćete pronaći nazive svih makroa koje ste do sada kreirali.
2. Korak 4: Odaberite makro (ili modul) koji se zove 'HideRows' i kliknite na dugme Bježi .

Trebali biste vidjeti sve redove gdje je Zaposleni status Nije skriveno kao » U službi ".

Metoda 3: Objašnjenje koda za sakrivanje redova na osnovu vrijednosti ćelije u Excelu

Evo redak po red objašnjenja gornjeg koda:

Na liniji 1 definiramo naziv funkcije.

• SubHideRows()

U redovima 2, 3 i 4 definiramo varijable koje drže početni red i krajnji red skupa podataka, kao i indeks kolone kriterija.

• StartRow = 2
• Krajnji red = 19
• Broj stupaca = 3

U redovima 5-11 prolazimo kroz svaku ćeliju u koloni “3” (ili stupcu C) aktivnog radnog lista. Ako ćelija ne sadrži vrijednost «U službi«, zatim postavljamo atribut 'Skrivena' cijelog reda (koji odgovara toj ćeliji) u Istinito, što znači da želimo sakriti cijeli odgovarajući red.

• Za i = StartRow do EndRow
• Ako Ćelije (i, ColNum).Vrijednost <> «U službi» Onda
• Ćelije(i, ColNum).CijeliRow.Hidden = Tačno
• Ostalo
• Ćelije(i, ColNum).CijeliRed.Skriven = False
• Prekinut će ako
• pored mene

Red 12 jednostavno ograničava kraj funkcije HideRows .

• End Sub

Na ovaj način, gornji kod sakriva sve redove koji ne sadrže vrijednost ' U službi ' u koloni C.

Prikaži kolone na osnovu vrijednosti ćelije

Sada kada smo uspjeli uspješno sakriti neželjene redove, šta ako želimo ponovo vidjeti skrivene redove?

Uraditi ovo je prilično lako. Samo trebate napraviti malu promjenu u funkciji HideRows .

SubUnhideRows()

StartRow = 2

Krajnji red = 19

Broj stupaca = 3

Za i = StartRow do EndRow

Ćelije(i, ColNum).CijeliRed.Skriven = False

pored mene

End Sub

Ovdje jednostavno vodimo računa da bez obzira na vrijednost, svi redovi budu prikazani (postavljanje svojstva Hidden svih redova na False).

Ovaj makro možete pokrenuti na potpuno isti način kao HideRows.

Sakrij redove na osnovu vrijednosti ćelija u realnom vremenu

U prvom primjeru, kolone su skrivene samo kada je makro pokrenut. Međutim, većinu vremena želimo sakriti stupce u hodu, na osnovu vrijednosti određene ćelije.

Pogledajmo sada još jedan primjer koji to pokazuje. U ovom primjeru imamo sljedeći skup podataka:

Jedina razlika u odnosu na prvi skup podataka je u tome što imamo vrijednost u ćeliji A21 koja će odrediti koje redove treba sakriti. Dakle, kada ćelija A21 sadrži vrijednost 'Penzionisan', sakriveni su samo redovi koji sadrže status zaposlenja 'U penziji'.

Slično tome, kada ćelija A21 sadrži vrijednost 'Na probnom radu', sakriveni su samo redovi koji sadrže status zaposlenja.U probnom periodu'.

Kada u ćeliji A21 nema ništa, želimo da se prikažu svi redovi.

Želimo da se to dešava u realnom vremenu, svaki put kada se promijeni vrijednost u ćeliji A21. Za ovo trebamo koristiti funkciju Worksheet_SelectionChange programa Excel.

Događaj Worksheet_SelectionChange za skrivanje redova na osnovu vrijednosti ćelije u Excelu

Procedura Worksheet_SelectionChange je VBA događaj ugrađen u Excel

Dolazi unaprijed instaliran uz radni list i poziva se kad god korisnik odabere ćeliju, a zatim promijeni svoj odabir u drugu ćeliju.

Pošto ova funkcija dolazi unapred instalirana uz radni list, morate je postaviti u odgovarajući modul koda radnog lista da biste je koristili.

U našem kodu ćemo staviti sve naše linije unutar ove funkcije, tako da se izvršavaju svaki put kada korisnik promijeni vrijednost u A21, a zatim odabere neku drugu ćeliju.

Pisanje VBA koda za sakrivanje redova na osnovu vrijednosti ćelije u Excelu

Evo koda koji ćemo koristiti:

• StartRow = 2
• Krajnji red = 19
• Broj stupaca = 3
• Za i = StartRow do EndRow
• Ako ćelije(i, ColNum).Vrijednost = Opseg("A21"). Vrijednost Tada
• Ćelije(i, ColNum).CijeliRow.Hidden = Tačno
• Ostalo
• Ćelije(i, ColNum).CijeliRed.Skriven = False
• Prekinut će ako
• pored mene

Da biste unijeli prethodni kod, morate ga kopirati i zalijepiti u prozor programera, unutar procedure Worksheet_SelectionChange sa tvog lista posao. Evo kako biste to trebali učiniti:

1. Korak 1: Na traci menija Programer, Odaberite Visual Basic.
2. Korak 2: Kada se otvori VBA prozor, vidjet ćete sve datoteke i mape vašeg projekta u njima Project Explorer Na lijevoj strani.
3. Korak 3: U istraživač projekta , dvaput kliknite na naziv vašeg radnog lista ispod VBA projekta sa istim imenom kao i vaša Excel radna sveska. U ovom primjeru radimo s List1 .

1. Korak 4: Ovo će otvoriti novi prozor modula za odabrani list.
2. Korak 5: Kliknite na padajuću strelicu na lijevoj strani prozora. Trebali biste vidjeti opciju koja kaže «Radni list« .

1. Korak 6: Kliknite na opciju 'Radni list'. Podrazumevano, vidjet ćete proceduru Worksheet_SelectionChange kreiran za vas u prozoru modula.

1. Korak 7: Sada možete početi s kodiranjem. Kopirajte gornje linije koda i zalijepite ih u proceduru Worksheet_SelectionChange , odmah nakon linije: Privatni pod radni list_Izmjena izbora (prema vrijednosti cilja kao raspona).

1. Korak 8: Zatvorite VBA prozor.

Pokretanje makroa za sakrivanje redova na osnovu vrijednosti ćelije u Excelu

Procedura Worksheet_SelectionChange Počinje da radi čim završite sa kodiranjem. Stoga, ne morate eksplicitno pokretati makro da bi počeo raditi.

1. Korak 1: Pokušajte upisati 'u penziji'u ćeliji A21, a zatim kliknite na bilo koju drugu ćeliju. Trebali biste otkriti da svi redovi koji sadrže vrijednost 'Povučeno' u koloni C nestaju.

2. Korak 2: Sada pokušajte zamijeniti vrijednost u ćeliji A21 sa ' U probnom periodu', a zatim kliknite na bilo koju drugu ćeliju. Trebali biste pronaći da svi redovi koji sadrže vrijednost 'On probno' u koloni C nestaju.

U probnom periodu

3. Korak 3: Sada pokušajte da obrišete vrijednost u ćeliji A21 i ostavite je praznu. Zatim kliknite na bilo koju drugu ćeliju. Trebali biste otkriti da oba reda ponovo postaju vidljiva.

To znači da kod radi u realnom vremenu kada se izvrše promjene u ćeliji B20.

Objašnjenje koda

Odvojimo nekoliko minuta da sada shvatimo ovaj kod.

Linije 1, 2 i 3 definiraju varijable koje drže početni red i krajnji red skupa podataka, kao i indeks kolone kriterija. Možete mijenjati vrijednosti prema vašim potrebama.

• StartRow = 2
• Krajnji red = 19
• Broj stupaca = 3

Redovi od 4 do 10, prolaze kroz svaku ćeliju u koloni “3” (ili koloni C) radnog lista. Ako ćelija sadrži vrijednost u ćeliji A21, tada postavljamo atribut 'Skriveno' cijelog reda (koji odgovara toj ćeliji) u Istinito, što znači da želimo sakriti cijeli odgovarajući red.

• Za i = StartRow do EndRow
• Ako ćelije(i, ColNum).Vrijednost = Opseg("A21"). Vrijednost Tada
• Ćelije(i, ColNum).CijeliRow.Hidden = Tačno
• Ostalo
• Ćelije(i, ColNum).CijeliRed.Skriven = False
• Prekinut će ako
• pored mene

Na ovaj način, gornji kod sakriva redove skupa podataka na osnovu vrijednosti u ćeliji A21. Ako A21 ne sadrži nijednu vrijednost, kod prikazuje sve redove.

Možda će vas zanimati i čitanje o: MS Excel: Kako koristiti FOR NEXT VBA naredbu u Excelu

zaključak

U ovom vodiču ćemo vam pokazati kako možete koristiti Filteri i Excel VBA da sakrijete redove na osnovu vrednosti ćelije u Excelu. To smo učinili uz pomoć dva jednostavna primjera: jednog koji briše tražene redove samo kada se makro izvršava eksplicitno i drugog koji radi u realnom vremenu. Nadamo se da smo uspjeli da vam pomognemo da shvatite koncept koda kako biste ga mogli prilagoditi i koristiti u svojim aplikacijama.