Innhold
Formulering av problemet
Vi har en rekke celler med data som inneholder tomme celler:
Oppgaven er å fjerne tomme celler, og etterlate bare celler med informasjon.
Metode 1. Grovt og raskt
- Velge det opprinnelige området
- Trykk på tasten F5, neste knapp Uthev (Spesiell). Velg i vinduet som åpnes Tomme celler(Blanker) og klikk OK.
Alle tomme celler i området er valgt.
- Vi gir en kommando i menyen for å slette valgte celler: høyreklikk- Slett celler (Slett celler) med forskyvning oppover.
Metode 2: Matriseformel
For å forenkle, la oss navngi arbeidsområdene våre ved hjelp av Navnesjef (navneansvarlig) tab formel (Formler) eller, i Excel 2003 og eldre, menyen Sett inn – Navn – Tildel (Sett inn - Navn - Definer)
Navngi området B3:B10 Har tom, område D3:D10 – Ingen tom. Rekkevidder må være strengt tatt like store, og kan plasseres hvor som helst i forhold til hverandre.
Velg nå den første cellen i det andre området (D3) og skriv inn denne skumle formelen i den:
=HVIS(RAD() -RAD(NeiTom)+1>NOTROWS(JaTom)-COUNTBLANK(JaTom);””;INDIREKTE(ADRESSE(LASTE((HVIS(Tom<>“”,RAD(Tom);RAD() + RADER(Det er tomme))); LINJE()-RAD(Ingen tomme)+1); KOLONNE(Det er tomme); 4)))
I den engelske versjonen blir det:
=HVIS(RAD()-RAD(IngenTom)+1>RADER(Tom)-ANTELLBLANK(Tom),",",INDIREKTE(ADRESSE(LITEN((HVIS(Tom<>“”,RAD(Tom),RAD() +RADER(Har Tom))),RAD()-RAD(Ingen Tom)+1),KOLONNE(Har Tom),4)))
Dessuten må den legges inn som en matriseformel, dvs. trykk etter liming Enter (som vanlig) og Ctrl + Shift + Enter. Nå kan formelen kopieres ned ved hjelp av autofullføring (dra det svarte krysset i nedre høyre hjørne av cellen) – og vi får det opprinnelige området, men uten tomme celler:
Metode 3. Egendefinert funksjon i VBA
Hvis det er mistanke om at du ofte må gjenta prosedyren for å fjerne tomme celler fra områder, er det bedre å legge til din egen funksjon for å fjerne tomme celler i standardsettet én gang, og bruke den i alle påfølgende tilfeller.
For å gjøre dette, åpne Visual Basic Editor (ALT + F11), sett inn en ny tom modul (meny Sett inn – modul) og kopier teksten til denne funksjonen dit:
Funksjon NoBlanks(DataRange As Range) As Variant() Dim N As Long Dim N2 As Long Dim Rng As Range Dim MaxCells As Long Dim Result() As Variant Dim R As Long Dim C As Long MaxCells = Application.WorksheetFunction.Max( _ Application.Caller.Cells.Count, DataRange.Cells.Count) ReDim Result(1 To MaxCells, 1 To 1) For Hver Rng I DataRange.Cells Hvis Rng.Value <> vbNullString Deretter N = N + 1 Resultat(N, 1 ) = Rng.Value End If Next Rng For N2 = N + 1 To MaxCells Result(N2, 1) = vbNullString Next N2 If Application.Caller.Rows.Count = 1 Then NoBlanks = Application.Transpose(Result) Else NoBlanks = Result End If End-funksjon
Ikke glem å lagre filen og bytte tilbake fra Visual Basic Editor til Excel. For å bruke denne funksjonen i vårt eksempel:
- Velg et tilstrekkelig utvalg av tomme celler, for eksempel F3:F10.
- Gå til menyen Sett inn – Funksjon (Sett inn — funksjon)eller klikk på knappen Sett inn funksjon (Sett inn funksjon) tab formel (Formler) i nyere versjoner av Excel. I kategori Brukerdefinert (Brukerdefinert) velge vår funksjon Noblanks.
- Spesifiser kildeområdet med tomrom (B3:B10) som funksjonsargument og trykk Ctrl + Shift + Enterfor å angi funksjonen som en matriseformel.
:
- Slette alle tomme rader i en tabell samtidig med en enkel makro
- Fjerne alle tomme rader i et regneark samtidig ved å bruke PLEX-tillegget
- Fyll raskt ut alle tomme celler
- Hva er makroer, hvor skal man sette inn makrokode i VBA