Tühjade ridade ja veergude eemaldamine andmetest

Tühjad read ja veerud võivad paljudel juhtudel tabelites vaeva näha. Standardfunktsioonid sortimiseks, filtreerimiseks, summeerimiseks, pivot-tabelite loomiseks jne tajuvad tühje ridu ja veerge tabeli katkemisena, ilma nende taga asuvaid andmeid üles võtmata. Kui selliseid lünki on palju, võib nende käsitsi eemaldamine olla väga kulukas ja kõiki korraga “hulgi” filtreerimise abil eemaldada ei õnnestu, sest filter “komistab” ka vaheaegadel.

Vaatame selle probleemi lahendamiseks mitmeid viise.

1. meetod. Otsige tühjad lahtrid

See ei pruugi olla kõige mugavam, kuid kindlasti väärib mainimist lihtsaim viis.

Oletame, et tegemist on sellise tabeliga, mis sisaldab palju tühje ridu ja veerge (selguse huvides esile tõstetud):

Oletame, et oleme kindlad, et meie tabeli esimene veerg (veerg B) sisaldab alati linna nime. Siis on selle veeru tühjad lahtrid märgiks tarbetutest tühjadest ridadest. Nende kõigi kiireks eemaldamiseks tehke järgmist.

  1. Valige linnadega vahemik (B2:B26)
  2. Vajutage klahvi F5 ja seejärel vajutage Esile tõstma (Mine Erialasse) või valige vahekaardil Avaleht — Otsi ja vali — lahtrite rühma valimine (Avaleht — Otsi ja vali — Mine eri).
  3. Avanevas aknas valige suvand Tühjad rakud (Tühjad) ja vajutage OK – tuleks valida kõik meie tabeli esimeses veerus olevad tühjad lahtrid.
  4. Nüüd valige vahekaardil Avaleht käsk Kustuta – ridade kustutamine lehelt (Kustuta – ridade kustutamine) või vajutage kiirklahvi Ctrl+miinus – ja meie ülesanne on lahendatud.

Tühjadest veergudest saab loomulikult lahti täpselt samamoodi, võttes aluseks tabeli päise.

2. meetod: otsige tühje ridu

Nagu olete juba aru saanud, töötab eelmine meetod ainult siis, kui meie andmed sisaldavad tingimata täielikult täidetud ridu ja veerge, mida saab tühjade lahtrite otsimisel konksutada. Aga mis siis, kui sellist usaldust pole ja andmed võivad sisaldada ka tühje lahtreid?

Vaadake just sellise juhtumi jaoks näiteks järgmist tabelit:

Siin on lähenemine veidi keerulisem:

  1. Sisestage lahtrisse A2 funktsioon COUNT (COUNTA), mis arvutab täidetud lahtrite arvu parempoolses reas ja kopeerib selle valemi alla kogu tabelisse:
  2. Valige lahter A2 ja lülitage filter käsuga sisse Andmed – filter (Andmed — filter) või klaviatuuri otsetee Ctrl+nihe+L.
  3. Filtreerime nullid välja arvutatud veeru järgi ehk kõik read, kus andmeid pole.
  4. Jääb valida filtreeritud read ja kustutada need käsuga Avaleht — Kustuta — Kustutage lehelt ridu (Avaleht — Kustuta — ridade kustutamine) või klaviatuuri otsetee Ctrl+miinus.
  5. Lülitame filtri välja ja saame oma andmed ilma tühjade ridadeta.

Kahjuks ei saa seda nippi enam veergudega teha – Excel pole veel õppinud veergude järgi filtreerimist.

3. meetod. Makro kõigi tühjade ridade ja veergude eemaldamiseks lehelt

Selle ülesande automatiseerimiseks saate kasutada ka lihtsat makrot. Vajutage kiirklahvi muu+F11 või valige vahekaardilt arendaja — Visual Basic (Arendaja – Visual Basicu redaktor). Kui vahelehed arendaja pole nähtav, saate selle lubada Fail – Valikud – Lindi seadistamine (Fail — Valikud — Kohanda linti).

Avanevas Visual Basicu redaktori aknas valige menüükäsk Sisesta – moodul ning kopeerige ja kleepige ilmuvas tühjas moodulis järgmised read:

   Sub DeleteEmpty() Dim r As Long, rng As Range 'удаляем пустые строки For r = 1 To ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count If Application.CountA(Rows(r)) = 0 rng ei ole midagi, siis määra rng = read(r) Muu Määra rng = Liit(rng, read(r)) Lõpeta, kui järgmine r Kui ei ole rng pole midagi, siis rng.Delete 'удаляем пустые столбцы Määra rng = mitte midagi jaoks r = 1 kuni ActiveSheet.KasutatudRange.Veerg – 1 + ActiveSheet.KasutatudRange.Veerud.Arv Kui Application.CountA(Veerud(r)) = 0 Siis Kui rng pole midagi, siis Set rng = Veerud(r) Else Set rng = Liit(rng, Veerud( r)) Lõpeta, kui järgmine r Kui ei ole, rng pole midagi, siis rng.Delete End Sub

Sulgege redaktor ja naaske Excelisse. 

Nüüd vajuta kombinatsiooni muu+F8 või nuppu Makrod tab arendaja. Avanevas aknas kuvatakse kõik praegu käitamiseks saadaolevad makrod, sealhulgas äsja loodud makro. Kustuta Tühi. Valige see ja klõpsake nuppu jooks (jookse) – kõik tühjad read ja veerud lehel kustutatakse koheselt.

4. meetod: Power Query

Teine viis meie probleemi lahendamiseks ja väga levinud stsenaarium on eemaldada Power Query tühjad read ja veerud.

Esiteks laadime oma tabeli Power Query päringuredaktorisse. Saate selle teisendada dünaamiliseks "nutikaks" kiirklahviga Ctrl+T või lihtsalt valida meie andmevahemiku ja anda sellele nimi (näiteks kuupäev) valemiribal, teisendades nimega:

Nüüd kasutame käsku Andmed – Hangi andmed – Tabelist/vahemikust (Andmed – Hangi andmed – Tabelist/vahemikust) ja laadime kõik Power Querysse:

Siis on kõik lihtne:

  1. Tühjad read kustutame käsuga Avaleht – Vähenda ridu – Kustuta read – Kustuta tühjad read (Kodu – Eemalda read – Eemalda tühjad read).
  2. Paremklõpsake esimese linna veeru pealkirjal ja valige kontekstimenüüst käsk Unpivot Other Columns. Meie tabel on, nagu seda tehniliselt õigesti nimetatakse, normaliseeritud – teisendatuna kolme veergu: linn, kuu ja väärtus linna ristumiskohast ning kuu algsest tabelist. Selle toimingu eripäraks Power Querys on see, et see jätab lähteandmetes tühjad lahtrid vahele, mida me vajame:
  3. Nüüd teeme pöördoperatsiooni – muudame saadud tabeli tagasi kahemõõtmeliseks, et taastada see algsel kujul. Valige veerg kuude ja vahekaardil Transformation vali meeskond Pivot veerg (Teisendus – Pivot Column). Avanevas aknas valige väärtuste veeruna viimane (Väärtus) ja täpsematest valikutest - toiming Mitte koondada (Ära koonda):
  4. Jääb üle tulemus käsuga Excelisse tagasi laadida Avaleht — sulge ja laadi — sulge ja laadi... (Avaleht — Sulge&laadi — Sule&laadi...)

  • Mis on makro, kuidas see töötab, kuhu kopeerida makro teksti, kuidas makrot käivitada?
  • Loendi kõigi tühjade lahtrite täitmine lähterakkude väärtustega
  • Kõigi tühjade lahtrite eemaldamine antud vahemikust
  • Töölehe kõigi tühjade ridade eemaldamine PLEX-i lisandmooduliga

Jäta vastus