Innehåll
Tomma rader och kolumner kan vara jobbigt i tabeller i många fall. Standardfunktioner för sortering, filtrering, sammanfattning, skapande av pivottabeller etc. uppfattar tomma rader och kolumner som en tabellbrytning, utan att plocka upp data som ligger längre bakom dem. Om det finns många sådana luckor kan det vara mycket kostsamt att manuellt ta bort dem, och det kommer inte att fungera att ta bort alla på en gång "i bulk" med filtrering, eftersom filtret också kommer att "snabbla" vid raster.
Låt oss titta på flera sätt att lösa detta problem.
Metod 1. Sök efter tomma celler
Detta kanske inte är det mest bekväma, men definitivt det enklaste sättet är värt att nämna.
Anta att vi har att göra med en sådan tabell som innehåller många tomma rader och kolumner inuti (markerat för tydlighetens skull):
Anta att vi är säkra på att den första kolumnen i vår tabell (kolumn B) alltid innehåller namnet på en stad. Då kommer tomma celler i den här kolumnen att vara ett tecken på onödiga tomma rader. Gör följande för att snabbt ta bort dem alla:
- Välj intervall med städer (B2:B26)
- Tryck på knappen F5 och tryck sedan på Markera (Gå till Special) eller välj på fliken Hem — Hitta och välj — Välj en grupp av celler (Hem — Hitta&Välj — Gå till special).
- Välj alternativet i fönstret som öppnas Tomma celler (Tomt) och tryck OK – alla tomma celler i den första kolumnen i vår tabell ska väljas.
- Välj nu på fliken Hem Kommando Ta bort – Ta bort rader från arket (Ta bort – Ta bort rader) eller tryck på kortkommandot ctrl+minus – och vår uppgift är löst.
Naturligtvis kan du bli av med tomma kolumner på exakt samma sätt, med tabellhuvudet som grund.
Metod 2: Sök efter tomma rader
Som du kanske redan har räknat ut kommer den tidigare metoden bara att fungera om vår data nödvändigtvis innehåller helt ifyllda rader och kolumner, som kan hakas på när du söker efter tomma celler. Men vad händer om det inte finns något sådant förtroende, och data kan också innehålla tomma celler?
Ta en titt på följande tabell, till exempel, för just ett sådant fall:
Här kommer tillvägagångssättet att vara lite knepigare:
- Ange funktionen i cell A2 RÄKNA (ANTAL), som kommer att beräkna antalet fyllda celler i raden till höger och kopiera denna formel ner till hela tabellen:
- Välj cell A2 och slå på filtret med kommandot Data – Filter (Data — Filter) eller kortkommandon ctrl+shift+L.
- Låt oss filtrera bort nollor efter den beräknade kolumnen, dvs alla rader där det inte finns några data.
- Det återstår att välja de filtrerade raderna och ta bort dem med kommandot Hem — Ta bort -' Ta bort rader från arket (Hem — Ta bort — Ta bort rader) eller kortkommandon ctrl+minus.
- Vi stänger av filtret och får vår data utan tomma rader.
Tyvärr kan detta trick inte längre göras med kolumner – Excel har ännu inte lärt sig hur man filtrerar efter kolumner.
Metod 3. Makro för att ta bort alla tomma rader och kolumner på ett ark
Du kan också använda ett enkelt makro för att automatisera denna uppgift. Tryck på kortkommandot andra+F11 eller välj från fliken utvecklare - Visual Basic (Utvecklare – Visual Basic Editor). Om flikar utvecklare inte är synlig, kan du aktivera den genom Arkiv – Alternativ – Ribbon Setup (Arkiv — Alternativ — Anpassa menyfliksområdet).
Välj menykommandot i Visual Basic-redigeringsfönstret som öppnas Infoga – Modul och i den tomma modulen som visas, kopiera och klistra in följande rader:
Sub DeleteEmpty() Dim r As Long, rng As Range 'удаляем пустые строки För r = 1 Till ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count If Application.CountA(Rows(r)) = 0 rng Is Nothing Then Set rng = Rows(r) Else Set rng = Union(rng, Rows(r)) End If Next r If Not rng Is Nothing Then rng.Delete 'удаляем пустые столбцы Set rng = Nothing For r = 1 Till ActiveSheet.UsedRange.Column - 1 + ActiveSheet.UsedRange.Columns.Count If Application.CountA(Columns(r)) = 0 Then If rng Is Nothing Then Set rng = Columns(r) Else Set rng = Union(rng, Columns( r)) End If Next r If Not rng Is Nothing Then rng.Delete End Sub
Stäng redigeraren och återgå till Excel.
Hit kombination andra+F8 eller knapp Makron fliken utvecklare. Fönstret som öppnas kommer att lista alla makron som för närvarande är tillgängliga för dig att köra, inklusive det makro du just skapade. Ta bortTom. Välj den och klicka på knappen Körning (springa) – alla tomma rader och kolumner på arket kommer att raderas omedelbart.
Metod 4: Power Query
Ett annat sätt att lösa vårt problem och ett mycket vanligt scenario är att ta bort tomma rader och kolumner i Power Query.
Låt oss först ladda vår tabell i Power Query Query Editor. Du kan konvertera den till en dynamisk "smart" med kortkommandot Ctrl+T eller bara välja vårt dataområde och ge det ett namn (till exempel Data) i formelfältet, konverterar till namngiven:
Nu använder vi kommandot Data – Get data – From table / range (Data – Get Data – From table / range) och laddar allt i Power Query:
Då är allt enkelt:
- Vi tar bort tomma rader med kommandot Hem – Förminska rader – Ta bort rader – Ta bort tomma rader (Hem – Ta bort rader – Ta bort tomma rader).
- Högerklicka på rubriken i den första stadskolumnen och välj kommandot Unpivot Other Columns från snabbmenyn. Vårt bord blir, som det tekniskt korrekt heter, normaliserad – omvandlas till tre kolumner: stad, månad och värde från skärningspunkten mellan staden och månad från den ursprungliga tabellen. Det speciella med denna operation i Power Query är att den hoppar över tomma celler i källdata, vilket är vad vi behöver:
- Nu utför vi den omvända operationen - vi gör tillbaka den resulterande tabellen till en tvådimensionell för att återställa den till sin ursprungliga form. Välj kolumnen med månader och på fliken Transformation välja ett lag Pivotkolumn (Transform – Pivot Column). I fönstret som öppnas, som en kolumn med värden, välj det sista (Värde), och i de avancerade alternativen - operationen Aggregera inte (Sammanställ inte):
- Det återstår att ladda upp resultatet tillbaka till Excel med kommandot Hem — Stäng och ladda — Stäng och ladda in... (Hem — Stäng&Ladda — Stäng&Ladda till...)
- Vad är ett makro, hur fungerar det, var kopierar man texten i ett makro, hur kör man ett makro?
- Fyller alla tomma celler i listan med värdena för de överordnade cellerna
- Ta bort alla tomma celler från ett givet intervall
- Ta bort alla tomma rader i ett kalkylblad med PLEX-tillägget