Sisällys
Tyhjät rivit ja sarakkeet voivat monissa tapauksissa olla tuskaa taulukoissa. Tavalliset lajittelu-, suodatus-, yhteenveto-, pivot-taulukoiden jne. -toiminnot havaitsevat tyhjät rivit ja sarakkeet taulukon katkoksena poimimatta niiden takana olevaa dataa. Jos tällaisia aukkoja on monia, niiden manuaalinen poistaminen voi olla erittäin kallista, eikä kaikkia kerralla "irtotavarana" suodatuksen avulla voi poistaa, koska suodatin "kompastuu" myös tauoissa.
Katsotaanpa useita tapoja ratkaista tämä ongelma.
Menetelmä 1. Etsi tyhjiä soluja
Tämä ei ehkä ole kätevin, mutta ehdottomasti helpoin tapa on mainitsemisen arvoinen.
Oletetaan, että kyseessä on tällainen taulukko, joka sisältää monia tyhjiä rivejä ja sarakkeita (korostettu selvyyden vuoksi):
Oletetaan, että olemme varmoja, että taulukkomme ensimmäinen sarake (sarake B) sisältää aina kaupungin nimen. Tällöin tämän sarakkeen tyhjät solut ovat merkki tarpeettomista tyhjistä riveistä. Voit poistaa ne kaikki nopeasti seuraavasti:
- Valitse alue kaupungeilla (B2:B26)
- Paina näppäintä F5 ja paina sitten Korostaa (Siirry Special) tai valitse välilehdeltä Etusivu — Etsi ja valitse — Valitse soluryhmä (Etusivu — Etsi ja valitse — Siirry erikoiskohtaan).
- Valitse avautuvassa ikkunassa vaihtoehto Tyhjät solut (Tyhjät) ja paina OK – kaikki tyhjät solut taulukon ensimmäisessä sarakkeessa tulee valita.
- Valitse nyt välilehdeltä Koti Komento Poista – Poista rivit taulukosta (Poista - Poista rivejä) tai paina pikanäppäintä Ctrl+miinus – ja tehtävämme on ratkaistu.
Voit tietysti päästä eroon tyhjistä sarakkeista täsmälleen samalla tavalla käyttämällä taulukon otsikkoa pohjana.
Tapa 2: Etsi tyhjiä rivejä
Kuten olet ehkä jo havainnut, edellinen menetelmä toimii vain, jos tiedoissamme on välttämättä täysin täytettyjä rivejä ja sarakkeita, joihin voidaan kiinnittää haettaessa tyhjiä soluja. Mutta entä jos tällaista luottamusta ei ole ja tiedot voivat sisältää myös tyhjiä soluja?
Katso esimerkiksi seuraavaa taulukkoa juuri tällaista tapausta varten:
Tässä lähestymistapa on hieman hankalampi:
- Kirjoita soluun A2 funktio COUNT (COUNTA), joka laskee täytettyjen solujen määrän oikealla olevalla rivillä ja kopioi tämän kaavan koko taulukkoon:
- Valitse solu A2 ja ota suodatin käyttöön komennolla Tiedot – Suodatin (Data – suodatin) tai pikanäppäimellä Ctrl+siirtyminen+L.
- Suodatetaan nollat pois lasketun sarakkeen mukaan, eli kaikki rivit, joissa ei ole tietoja.
- Jäljelle jää valita suodatetut rivit ja poistaa ne komennolla Etusivu — Poista —' Poista rivit taulukosta (Etusivu — Poista — Poista rivejä) tai pikanäppäimellä Ctrl+miinus.
- Sammutamme suodattimen ja saamme tietomme ilman tyhjiä rivejä.
Valitettavasti tätä temppua ei voi enää tehdä sarakkeiden kanssa – Excel ei ole vielä oppinut suodattamaan sarakkeiden mukaan.
Tapa 3. Makro poistaa kaikki tyhjät rivit ja sarakkeet arkilta
Voit myös käyttää yksinkertaista makroa tämän tehtävän automatisoimiseen. Paina pikanäppäintä muut+F11 tai valitse välilehdeltä kehittäjä - Visual Basic (Kehittäjä – Visual Basic Editor). Jos välilehdet kehittäjä ei näy, voit ottaa sen käyttöön Tiedosto – Asetukset – Nauhan asetukset (Tiedosto — Asetukset — Mukauta nauhaa).
Valitse avautuvasta Visual Basic -editori-ikkunasta valikkokomento Insert – moduuli ja kopioi ja liitä seuraavat rivit näkyviin tulevaan tyhjään moduuliin:
Sub DeleteEmpty() Dim r As Long, rng As Range 'удаляем пустые строки For r = 1 To ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count, jos Application.CountA(Rows(r)) = 0 rng ei ole mitään, sitten Aseta rng = Rivit(r) Muuta Aseta rng = Union(rng, Rivit(r)) Lopeta, jos seuraava r Jos ei rng ei ole mitään Sitten rng.Delete 'удаляем пустые столбцы Aseta rng = ei mitään R = 1 To ActiveSheet.UsedRange.Column - 1 + ActiveSheet.UsedRange.Columns.Count If Application.CountA(Columns(r)) = 0 Then If rng ei ole mitään sitten Aseta rng = Sarakkeet(r) Else Set rng = Union(rng, Columns( r)) Lopeta, jos seuraava r Jos ei rng ei ole mitään sitten rng.Delete End Sub
Sulje editori ja palaa Exceliin.
Nyt nappaa yhdistelmä muut+F8 tai painiketta Makrot kieleke kehittäjä. Avautuva ikkuna listaa kaikki käytettävissäsi olevat makrot, mukaan lukien juuri luomasi makro. PoistaTyhjä. Valitse se ja napsauta painiketta ajaa (juosta) – kaikki arkin tyhjät rivit ja sarakkeet poistetaan välittömästi.
Tapa 4: Power Query
Toinen tapa ratkaista ongelmamme ja hyvin yleinen skenaario on poistaa tyhjät rivit ja sarakkeet Power Querysta.
Ensin ladataan taulukkomme Power Query Query Editoriin. Voit muuntaa sen dynaamiseksi "älykkääksi" pikanäppäimellä Ctrl+T tai vain valita tietoalueemme ja antaa sille nimen (esim. Päiväys) kaavapalkissa muuntaen muotoon named:
Nyt käytämme komentoa Data - Hanki tiedot - Taulukosta / alueesta (Data - Get Data - From Taulukko / alue) ja lataa kaikki Power Queryyn:
Sitten kaikki on yksinkertaista:
- Poistamme tyhjät rivit komennolla Koti – Pienennä rivejä – Poista rivejä – Poista tyhjät rivit (Koti – Poista rivit – Poista tyhjät rivit).
- Napsauta hiiren kakkospainikkeella ensimmäisen kaupunkisarakkeen otsikkoa ja valitse pikavalikosta Unpivot Other Columns -komento. Pöytämme on, kuten sitä teknisesti oikein kutsutaan, normalisoitu – muutettu kolmeen sarakkeeseen: kaupunki, kuukausi ja arvo kaupungin leikkauspisteestä ja kuukausi alkuperäisestä taulukosta. Tämän toiminnon erikoisuus Power Queryssä on, että se ohittaa tyhjät solut lähdetiedoissa, mitä tarvitsemme:
- Nyt suoritamme käänteisen toiminnon – muutamme tuloksena olevan taulukon takaisin kaksiulotteiseksi palauttaaksemme sen alkuperäiseen muotoonsa. Valitse sarake kuukausilla ja välilehdellä Muutos valitse joukkue Pivot-sarake (Muunnosta – Pivot Column). Valitse avautuvasta ikkunasta arvojen sarakkeeksi viimeinen (Arvo) ja lisäasetuksista operaatio Älä yhdistä (Älä yhdistä):
- Jäljelle jää lähettää tulos takaisin Exceliin komennolla Etusivu — Sulje ja lataa — Sulje ja lataa… (Etusivu — Sulje&Lataa — Sulje&Lataa...)
- Mikä on makro, miten se toimii, minne makron teksti kopioidaan, miten makro ajetaan?
- Täytä luettelon kaikki tyhjät solut pääsolujen arvoilla
- Kaikki tyhjät solut poistetaan annetulta alueelta
- Kaikkien tyhjien rivien poistaminen laskentataulukosta PLEX-lisäosalla