Përmbajtje
Rreshtat dhe kolonat bosh mund të jenë një dhimbje në tabela në shumë raste. Funksionet standarde për renditjen, filtrimin, përmbledhjen, krijimin e tabelave kryesore, etj. i perceptojnë rreshtat dhe kolonat boshe si një ndarje tabele, pa marrë të dhënat e vendosura më tej pas tyre. Nëse ka shumë boshllëqe të tilla, atëherë heqja manuale e tyre mund të jetë shumë e kushtueshme dhe nuk do të funksionojë për të hequr të gjitha menjëherë "në masë" duke përdorur filtrimin, sepse filtri gjithashtu do të "pengohet" në prishje.
Le të shohim disa mënyra për të zgjidhur këtë problem.
Metoda 1. Kërkoni për qeliza boshe
Kjo mund të mos jetë më e përshtatshme, por definitivisht mënyra më e lehtë ia vlen të përmendet.
Supozoni se kemi të bëjmë me një tabelë të tillë që përmban shumë rreshta dhe kolona boshe brenda (të theksuara për qartësi):
Supozoni se jemi të sigurt se kolona e parë e tabelës sonë (kolona B) përmban gjithmonë emrin e një qyteti. Pastaj qelizat boshe në këtë kolonë do të jenë një shenjë e rreshtave të panevojshëm bosh. Për t'i hequr shpejt të gjitha, bëni sa më poshtë:
- Zgjidh gamën me qytete (B2:B26)
- Shtypni tastin F5 dhe pastaj shtypni Theksoj (Shko te Speciali) ose zgjidhni në skedën Faqja kryesore — Gjeni dhe zgjidhni — Zgjidhni një grup qelizash (Faqja kryesore - Gjeni&Zgjidh - Shkoni te speciale).
- Në dritaren që hapet, zgjidhni opsionin Qeliza boshe (Bosh) dhe shtypni OK – duhet të zgjidhen të gjitha qelizat boshe në kolonën e parë të tabelës sonë.
- Tani zgjidhni në skedën Fillimi Komandë Fshi - Fshi rreshtat nga fleta (Fshi - Fshi rreshtat) ose shtypni shkurtoren e tastierës Ctrl+minus – dhe detyra jonë është zgjidhur.
Sigurisht, ju mund të hiqni qafe kolonat boshe në të njëjtën mënyrë, duke përdorur kokën e tabelës si bazë.
Metoda 2: Kërkoni për rreshta bosh
Siç mund ta keni kuptuar tashmë, metoda e mëparshme do të funksionojë vetëm nëse të dhënat tona përmbajnë domosdoshmërisht rreshta dhe kolona të mbushura plotësisht, të cilat mund të fiksohen kur kërkoni për qeliza boshe. Por, çka nëse nuk ka një besim të tillë dhe të dhënat mund të përmbajnë edhe qeliza boshe?
Hidhini një sy tabelës së mëposhtme, për shembull, për një rast të tillë:
Këtu qasja do të jetë pak më e ndërlikuar:
- Futni funksionin në qelizën A2 COUNT (COUNTA), i cili do të llogarisë numrin e qelizave të mbushura në rreshtin në të djathtë dhe do ta kopjojë këtë formulë në të gjithë tabelën:
- Zgjidhni qelizën A2 dhe aktivizoni filtrin me komandën Të dhënat - Filtri (Të dhënat - Filtër) ose shkurtore të tastierës Ctrl+Ndryshim+L.
- Le të filtrojmë zero sipas kolonës së llogaritur, pra të gjitha rreshtave ku nuk ka të dhëna.
- Mbetet të zgjidhni linjat e filtruara dhe t'i fshini ato me komandën Faqja kryesore — Fshi -' Fshi rreshtat nga fleta (Shtëpia - Fshi - Fshi rreshtat) ose shkurtore të tastierës Ctrl+minus.
- Ne fikim filtrin dhe marrim të dhënat tona pa vija boshe.
Fatkeqësisht, ky mashtrim nuk mund të bëhet më me kolona - Excel nuk ka mësuar ende se si të filtrohet sipas kolonave.
Metoda 3. Makro për të hequr të gjitha rreshtat dhe kolonat boshe në një fletë
Ju gjithashtu mund të përdorni një makro të thjeshtë për të automatizuar këtë detyrë. Shtypni shkurtoren e tastierës Ndalet+F11 ose zgjidhni nga skeda zhvillues - Bazë vizuale (Zhvilluesi - Redaktori i Visual Basic). Nëse skedat zhvillues nuk është i dukshëm, mund ta aktivizoni përmes Skedari – Opsionet – Konfigurimi i shiritit (Skedar - Opsione - Personalizo shiritin).
Në dritaren e redaktuesit të Visual Basic që hapet, zgjidhni komandën e menusë Insert – Moduli dhe në modulin bosh që shfaqet, kopjoni dhe ngjisni rreshtat e mëposhtëm:
Nën DeleteEmpty() Dim r Për aq kohë sa, rng Sa Range 'удаляем пустые строки Për r = 1 Tek ActiveSheet.UsedRange.Rresht - 1 + ActiveSheet.UsedRange.Rows.Count If Application.CountA(Rreshtat(r)) = 0 Pastaj Nëse rng nuk është asgjë, atëherë vendos rng = rreshta(r) tjetër vendos rng = bashkim(rng, rreshta(r)) Fundi nëse tjetër r Nëse jo rng nuk është asgjë, atëherë rng.Fshi 'удаляем пустые столбцы Vendos rng = Asgjë Për r = 1 Për ActiveSheet.UsedRange.Column - 1 + ActiveSheet.UsedRange.Columns.Count If Application.CountA(Columns(r)) = 0 Atëherë nëse rng nuk është asgjë, atëherë Cakto rng = Kolonat(r) Tjetër Vendos rng = Union(rng, Columns( r)) Fundi Nëse Next r Nëse Jo rng Nuk Është Asgjë Atëherë rng.Fshi Fundi Nën
Mbyllni redaktorin dhe kthehuni në Excel.
Tani goditi kombinim Ndalet+F8 ose butoni macros tab zhvillues. Dritarja që hapet do të listojë të gjitha makrot që janë aktualisht të disponueshme për t'u ekzekutuar, duke përfshirë makron që sapo keni krijuar. FshiEmpty. Zgjidhni atë dhe klikoni butonin run (vrapim) - të gjitha rreshtat dhe kolonat boshe në fletë do të fshihen menjëherë.
Metoda 4: Query Power
Një mënyrë tjetër për të zgjidhur problemin tonë dhe një skenar shumë i zakonshëm është heqja e rreshtave dhe kolonave boshe në Power Query.
Së pari, le të ngarkojmë tabelën tonë në Redaktuesin e Query Query Power. Mund ta konvertoni në një "të zgjuar" dinamik me shkurtoren e tastierës Ctrl+T ose thjesht zgjidhni gamën tonë të të dhënave dhe i jepni një emër (për shembull Data) në shiritin e formulave, duke u konvertuar në të emërtuar:
Tani përdorim komandën Të dhëna – Merr të dhëna – Nga tabela/vargu (Të dhënat – Merr të Dhëna – Nga tabela/vargu) dhe ngarkojmë gjithçka në Power Query:
Atëherë gjithçka është e thjeshtë:
- Fshijmë linjat boshe me komandën Home – Reduce lines – Delete lines – Delete bosh lines (Home – Remove Rows – Remove bosh rows).
- Klikoni me të djathtën në titullin e kolonës së parë të qytetit dhe zgjidhni komandën Unpivot Other Columns nga menyja e kontekstit. Tabela jonë do të jetë, siç quhet teknikisht saktë, normalizuar – konvertuar në tre kolona: qyteti, muaji dhe vlera nga kryqëzimi i qytetit dhe muaji nga tabela origjinale. E veçanta e këtij operacioni në Power Query është se ai kapërcen qelizat boshe në të dhënat e burimit, gjë që na nevojitet:
- Tani kryejmë operacionin e kundërt - e kthejmë tabelën që rezulton në një dy-dimensionale në mënyrë që ta kthejmë atë në formën e saj origjinale. Zgjidhni kolonën me muaj dhe në skedën Transformim zgjidhni një ekip Kolona kryesore (Transformo - Kolona kryesore). Në dritaren që hapet, si kolonë vlerash, zgjidhni të fundit (Vlera), dhe në opsionet e avancuara - operacionin Mos agregoni (Mos u përmbledh):
- Mbetet për të ngarkuar rezultatin përsëri në Excel me komandën Faqja kryesore — Mbyll dhe ngarko — Mbylle dhe ngarko në… (Shtëpia - Mbyll&Ngarko - Mbylle&Ngarko për…)
- Çfarë është një makro, si funksionon, ku të kopjoni tekstin e një makro, si të ekzekutoni një makro?
- Plotësimi i të gjitha qelizave boshe në listë me vlerat e qelizave mëmë
- Heqja e të gjitha qelizave boshe nga një gamë e caktuar
- Heqja e të gjitha rreshtave bosh në një fletë pune me shtesën PLEX