Inhalte
Formulierung des Problems
Wir haben eine Reihe von Zellen mit Daten, die leere Zellen enthalten:
Die Aufgabe besteht darin, leere Zellen zu entfernen und nur Zellen mit Informationen zu belassen.
Methode 1. Rau und schnell
- Auswählen des ursprünglichen Bereichs
- Drücken Sie die Taste F5, nächster Knopf Hervorheben (Speziell). Wählen Sie im sich öffnenden Fenster aus Leere Zellen(Leerzeichen) und klicken auf OK.
Alle leeren Zellen im Bereich werden ausgewählt.
- Wir geben im Menü einen Befehl, um ausgewählte Zellen zu löschen: Rechtsklick- Zellen löschen (Zellen löschen) mit Verschiebung nach oben.
Methode 2: Array-Formel
Zur Vereinfachung benennen wir unsere Arbeitsbereiche mit Name Manager (Namensmanager) Tab Formel (Formeln) oder in Excel 2003 und älter das Menü Einfügen – Name – Zuordnen (Einfügen – Name – Definieren)
Nennen Sie den Bereich B3:B10 HabeLeer, Bereich D3:D10 – KeineLeer. Bereiche müssen genau die gleiche Größe haben und können sich an beliebiger Stelle relativ zueinander befinden.
Wählen Sie nun die erste Zelle des zweiten Bereichs (D3) und geben Sie diese gruselige Formel ein:
=IF(ROW() -ROW(NoEmpty)+1>NOTROWS(YesEmpty)-COUNTBLANK(YesEmpty);““;INDIRECT(ADDRESS(LOWEST((IF(Empty<>““,ROW(Empty);ROW() + ROWS(There are Empty))); LINE()-ROW(No Empty)+1); COLUMN(There are Empty); 4)))
In der englischen Version wird es sein:
=IF(ROW()-ROW(NoEmpty)+1>ROWS(Empty)-COUNTBLANK(Leer)“,“,INDIRECT(ADDRESS(SMALL((IF(Empty<>““,ROW(Empty),ROW() +ROWS(HaveEmpty))),ROW()-ROW(NoEmpty)+1),COLUMN(HaveEmpty),4)))
Außerdem muss sie als Matrixformel eingegeben werden, dh nach dem Einfügen drücken Enter (wie immer) und Strg + Umschalt + Enter. Nun kann die Formel per Autovervollständigung nach unten kopiert werden (schwarzes Kreuz in die untere rechte Ecke der Zelle ziehen) – und wir erhalten den ursprünglichen Bereich, aber ohne leere Zellen:
Methode 3. Benutzerdefinierte Funktion in VBA
Besteht der Verdacht, dass Sie den Vorgang zum Entfernen leerer Zellen aus Bereichen öfters wiederholen müssen, dann ist es besser, eine eigene Funktion zum Entfernen leerer Zellen einmalig in den Standardsatz aufzunehmen und in allen weiteren Fällen zu verwenden.
Öffnen Sie dazu den Visual Basic Editor (ALT + F11), fügen Sie ein neues leeres Modul ein (menu Einfügen – Modul) und kopiere den Text dieser Funktion dorthin:
Funktion 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) Für jeden Rng in DataRange.Cells If Rng.Value <> vbNullString Then N = N + 1 Result(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-Funktion
Vergessen Sie nicht, die Datei zu speichern und vom Visual Basic-Editor zurück zu Excel zu wechseln. So verwenden Sie diese Funktion in unserem Beispiel:
- Wählen Sie einen ausreichenden Bereich leerer Zellen aus, z. B. F3:F10.
- Gehen Sie zum Menü Einfügen – Funktion (Einfügen — Funktion)oder klicken Sie auf die Schaltfläche Funktion einfügen (Funktion einfügen) Tab Formel (Formeln) in neueren Versionen von Excel. In der Kategorie Benutzerdefiniert (Benutzerdefinierten) Wählen Sie unsere Funktion Keine Leerzeichen.
- Geben Sie den Quellbereich mit Lücken (B3:B10) als Funktionsargument an und drücken Sie Strg + Umschalt + Enterum die Funktion als Matrixformel einzugeben.
:
- Mit einem einfachen Makro alle leeren Zeilen in einer Tabelle auf einmal löschen
- Alle leeren Zeilen in einem Arbeitsblatt auf einmal mit dem PLEX-Add-on entfernen
- Füllen Sie schnell alle leeren Zellen aus
- Was sind Makros, wo fügt man Makrocode in VBA ein