Excel Formeln – Spalten mit leeren Zellen ohne Lücken auflisten

In diesem Excel Artikel werde ich ihnen heute zeigen, wie Sie es schaffen, dass eine Spalte, in der leere Zellen auftreten, diese gelöscht werden. Wie Sie gleich sehen werden, geht das aber nicht nur für eine einzelne Spalte, sondern man kann dieses Verfahren auch auf mehrere Spalten gleichzeitig anwenden. Welche Formeln Sie dafür benötigen, sehen Sie nun.

Vielleicht standen Sie schon selbst einmal vor dem Problem, dass man zwei Spalten hat, in denen versetzt Begriffe stehen. Dabei findet man in den Spalten auch immer wieder leere Zellen. Möchten Sie nun, dass die Begriffe alle in einer Spalte aufgelistet werden, so haben Sie als „normaler“ Excel Benutzer keine Möglichkeit das Problem mit einer Formel zu lösen. Das liegt daran, dass so etwas nicht als Standardformel bei Excel programmiert wurde. Also mussten Sie die Begriffe alle von Hand eintippen, wodurch ihnen noch zusätzliche Fehler unterlaufen könnten. Damit alles sehr einfach und korrekt stattfinden, finden Sie hier nun zwei Formeln, mit denen Sie genau das gerade beschriebene Problem lösen können.

Beginn:

Zu aller Erst müssen Sie in ihre Excel Datei die Spalten mit den Begriffen eintragen. Wenn Sie die Funktion testen möchten, so achten Sie nun darauf, dass in den Spalten auch einmal Zellen ohne Inhalt auftreten. Auch mit Zeilen in denen in beiden Spalten Begriffe stehen, geht die Formel fehlerfrei um. Um später die Formeln nicht verändern zu müssen, empfehle ich ihnen ihre Begriffe in die Spalten A und B einzutragen. Wie weit Sie dabei nach unten gehen möchten, also wie viele verschiedene Begriffe Sie eintragen, ist ihnen fast grenzenlos überlassen. Möchten Sie die genauen Formeln verwenden, so sollten Sie nicht weiter als Zeile 1000 schreiben. Haben Sie ihre Begriffe nun alle in die Spalten eingetragen und ihre Tabelle sieht nun ungefähr wie meine im Screenshot aus, dann gehen Sie nun zum nächsten Schritt weiter, in dem ich ihnen erklären werden, wie Sie es schaffen, die Begriffe ohne Lücke aus einer Spalte aufzulisten.

(Zur Darstellung in Originalgröße Abbildungen anklicken)

Lückenlos in einer Spalte:

Wie gerade kurz angesprochen, zeige ich ihnen jetzt, wie es mit einer Formel möglich ist, dass die Begriffe aus einer Spalte ohne leere Zelle aufgelistet werden. Dazu benötigen Sie selbstverständlich eine Formel. Eigentlich ist es egal, in welche Zelle Sie die Formel eintragen, jedoch bekommen Sie ein Problem, wenn Sie versuchen die Formel in eine Zelle der Spalte A einzutragen. Das liegt daran, dass die Formel auf den ganzen Bereich der Spalte A verweist und somit auch die Formel selbst in der Formel mit inbegriffen ist. So etwas nennt man dann einen Zirkelbezug. Um diesen Fehler zu vermeiden, tragen Sie die Formel am besten in die Zelle D1 ein. Schreiben Sie hier nun folgendes:

=WENN(ZEILE(A1)>ANZAHL2(A:A);““;INDEX(A:A;KKLEINSTE(WENN(A$1:A$1000<>““;ZEILE($1:$1000));ZEILE(A1))))

Nachdem Sie die Formel eingegeben haben, bekommen Sie entweder eine Fehlermeldung oder ein falsches Ergebnis angezeigt. Das hat aber eine leichte Ursache. Diese Formel ist eine Matrixformel. Das bedeutet, dass Sie noch weitere Schritte durchführen müssen, wenn Sie die Formel korrekt ausführen möchten. Klicken Sie dazu zuerst nochmals in die Zelle, in der die Formel steht, in meinem Fall also D1. Anschließend klicken Sie oben in die Bearbeitungszeile der Formel. Haben Sie auch das gemacht, so drücken Sie auf ihrer Tastatur nun abschließend STRG+SHIFT+Enter, um die Formel korrekt auszuführen. Nun wird ihnen umgehend das korrekte Ergebnis angezeigt. In meinem Fall war das das erste Wort in der Spalte A, nämlich „Fußball“. Neben der korrekten Ausgabe hat sich aber auch die Formel selbst verändert. Sie sieht nun so aus:

{=WENN(ZEILE(A1)>ANZAHL2(A:A);““;INDEX(A:A;KKLEINSTE(WENN(A$1:A$1000<>““;ZEILE($1:$1000));ZEILE(A1))))}

Um die anderen Wörter aus der Spalte A aufzulisten, benötigen Sie immer noch diese Formel. Das bedeutet für Sie ganz konkret, dass Sie das Ergebnis in der Zelle D1 nun nach unten kopieren müssen. Klicken Sie dazu nun die Zelle D1 an. Haben Sie das gemacht, so fahren Sie nun mit ihrem Mauszeiger über die rechte untere Ecke der Zelle. Anschließend drücken Sie die linke Maustaste und halten Sie gedrückt, während Sie die Maus nach unten bewegen. Wie weit Sie das machen müssen, hängt davon ab, wie viele Begriffe Sie in der Spalte A stehen haben. Haben Sie ihre Maus nun genügend weit nach unten bewegt, so lassen Sie jetzt den Mauszeiger los. Nun sind in den anderen Zellen auch die korrekten Ergebnisse erschienen. Die Wörter aus der Spalte A sind nun ohne leere Zellen aufgelistet. Das können Sie natürlich auch für Spalte B oder jede andere beliebige Spalte verwenden, jedoch müssen Sie dann natürlich die Zellbezüge verändern. Am einfachsten ist es einfach den Buchstaben „A“ bei allen Zellbezügen in der Formel auszutauschen.

Lückenlos über mehrere Spalten:

Nun wissen Sie, wie man es schafft, dass die Begriffe einer Spalte ohne Lücken aufgelistet werden. Jedoch funktioniert diese Formel aber eben nur für eine Spalte. Haben Sie wie ich in meinem Beispiel aber mehrere Spalten verwendet, in denen Lücken auftreten, so benötigen Sie hier wieder eine neue Formel. Löschen Sie zuerst einmal wieder den Inhalt der Zelle D1, um anschließend die neue Formel einzugeben. Schreiben Sie in D1 nun:

=WENN(ZEILE(A1)>ANZAHL2(A:B);““;WENN(ZEILE(A1)>ANZAHL2(A:A);INDEX(B:B;KKLEINSTE(WENN(B$1:B$99<>““;ZEILE($1:$99));ZEILE(A1)-ANZAHL2(A:A)));INDEX(A:A;KKLEINSTE(WENN(A$1:A$99<>““;ZEILE($1:$99));ZEILE(A1)))))

Wie auch die Formel vorhin, ist diese Formel eine Matrixformel. Wunden Sie sich also nicht, wenn Sie hier noch kein richtiges Ergebnis angezeigt bekommen. Um die Formel richtig auszuführen, sind wieder die drei Schritte von vorhin nötig. Klicken Sie also nun erneut in die Zelle, in der die Formel steht, in meinem Beispiel also D1. Danach klicken Sie oben in die Bearbeitungszeile der Formel. Haben Sie auch das gemacht, so drücken Sie auf ihrer Tastatur nun gleichzeitig die Tasten STRG+SHIFT+Enter. Nun ist in der Zelle der richtige Ausgabewert erschienen. Bei mir wieder das Wort „Fußball“. Wie auch vorhin schon, hat sich die Formel natürlich wieder verändert. Im Matrixstil sieht sie nun so aus:

{=WENN(ZEILE(A1)>ANZAHL2(A:B);““;WENN(ZEILE(A1)>ANZAHL2(A:A);INDEX(B:B;KKLEINSTE(WENN(B$1:B$99<>““;ZEILE($1:$99));ZEILE(A1)-ANZAHL2(A:A)));INDEX(A:A;KKLEINSTE(WENN(A$1:A$99<>““;ZEILE($1:$99));ZEILE(A1)))))}

Auch diese Formel ist wieder für die anderen Begriffe zuständig. Das bedeutet, dass die Formel wieder nach unten kopiert werden muss. Klicken Sie dazu noch einmal in die Zelle, in der Sie die Formel eingetragen haben, in meinem Fall also D1. Danach fahren Sie mit ihrer Maus wieder über die rechte untere Ecke der Zelle. Haben Sie das gemacht, so drücken Sie nun ihre linke Maustaste und halten Sie die Maus gedrückt, während Sie die Maus nach unten bewegen. Nach einer gewissen Anzahl an Zeilen – die wieder von der Anzahl der Begriffe in den Spalten A und B abhängt – lassen Sie die Maus wieder los. Nun sind die restlichen Begriffe in den Zellen erschienen. Wie Sie sehen können, sind diese so geordnet, dass zuerst die Begriffe aus Spalte A aufgelistet werden, und die aus Spalte B anschließend folgen. Jetzt wissen Sie auch, wie man die Begriffe aus zwei Spalten lückenlos darstellen kann. Haben Sie andere Spalten als ich gewählt, müssen Sie die Zellbezüge natürlich wieder entsprechend anpassen.

Nun wissen Sie, wie man die Begriffe aus einer Spalte oder aus mehreren Spalten lückenlos darstellen kann. Möchten Sie auf die zweite Formel verzichten, so könnten Sie einfach auch zweimal die erste Formel benutzen und dann entsprechend die Zellbezüge so anpassen, dass die Formel die Spalten abdeckt. Jedoch müssen sie dann erst ausprobieren, in welcher Zeile die Formel eingetragen werden muss, damit in der neuen Spalte keine Lücken vorkommen.