Du bist hier::--Excel Formeln – Spalten mit leeren Zellen ohne Lücken auflisten

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)
01-excel-formeln-spalten-mit-leeren-zellen-ohne-luecken-auflisten-beginn-200.png?nocache=1309798712959

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.

02-excel-formeln-spalten-mit-leeren-zellen-ohne-luecken-auflisten-eine-spalte-470.png?nocache=1309798734816

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.

03-excel-formeln-spalten-mit-leeren-zellen-ohne-luecken-auflisten-beide-spalte-470.png?nocache=1309798759505

 

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.

Von |2018-07-25T14:16:54+00:00August 8th, 2011|Kategorien: Tabellenkalkulation|1 Kommentar

Über den Autor:

Ein Kommentar

  1. Klaus.2 8. August 2011 um 17:27 Uhr

    Hallo Supportnet-Admins, hallo yannick_b,

    ein Zehntel der Worte würde genügen, das Vorgehen und die Lösung verständlich zu beschreiben.

    Am Anfang erklärt der Autor, dass er demnächst etwas zu erklären gedenkt, dann erklärt er seine Absicht noch einmal etwas detaillierter und mutmaßt, dass der Leser vielleicht auch schon mal das Problem gehabt haben könnte …

    Dann wird langwierig und umständlich erklärt, wie man am besten Beispieldaten abschreibt oder eigene eingibt, und so zieht sich das noch mehrere Ewigkeiten hin, bis man dann kurz vor Schluss endlich etwas substanzielles erfährt. (Ok, als Leser hätte man auch gleich ans Ende scrollen können, wenn man jemals vorher einen Tipp von diesem Autor gelesen hat, die Machart ist immer die gleiche.)

    Als Lösung bekommt man dann definitiv nicht mehr, als man bei http://www.excelformeln.de/formeln.html?welcher=43 bekommt, ohne dass uns der Autor die Quelle seiner Inspiration verrät.

    Zum Schluss erklärt der Autor dann noch ein weiteres mal sehr ausführlich, dass er gerade eben etwas erklärt hat.

    Geradezu unverschämt empfinde ich, wie der Autor mit der Eingabe der Formel als Matrixformel umgeht. Die Formel zuerst als "normale" Formel anzugeben, um anschließend wiederum wortreich zu erklären, dass sie ja so eigentlich gar nicht funktionieren kann, dass man nun (nach diesem gewollten oder zumindest grob fahrlässig herbeigeführten Fehler) erst noch mal in die Zelle und dann in die Bearbeitungszeile klicken müsste, ist nicht nur falsch, sondern eine Frechheit!

    Falsch, weil ein weniger wortreich zu beschreibender Tastendruck auf F2 genügt, um dasselbe zu erreichen, nämlich die Formel anschließend mit STRG + UMSCHALT + EINGABE als Matrixformel einzugeben. Noch besser wäre natürlich gewesen, die Formel gleich mit geschweiften Klammern zu notieren und "(Eingabe als Matrixformel mit STRG + UMSCHALT + EINGABE)" hinzuzufügen.

    … Und eine Frecheheit deswegen, weil dies alles nichts als eine unerträgliche HONORAR-SCHINDEREI ist. Hier will ein Autor nicht bestmöglich seinen Lesern helfen, sondern vorrangig für sich selbst das Maximum heraus holen.

    Liebe Supportnet-Chefs, warum lasst Ihr die Kommerzialisierung einer so nützlichen und sinnvollen Einrichtung zu Gunsten einiger weniger und zum Nachteil aller anderen zu? Einer Einrichtung, deren Nutzen fast ausschließlich aus der unentgeltlichen Leistung vieler freiwilliger Helfer besteht. Wenn Ihr unbedingt eine bestimmte Menge an Honoraren für solche Artikel raushauen wollt, dann gebt einen (evtl. gestaffelten) Festpreis pro Artikel vor und achtet auf die Qualität. Gebt einen Artikel gegebenenfalls solange mit der Bitte um Überarbeitung/Kürzung an den Autor zurück, bis er in einem akzeptablen, publizierbaren Zustand ist. Glaubt mir, ein Autor wie yannick_b, der sofort begriffen hat (siehe alle seine früheren Artikel), wie man unter den gegebenen Voraussetzungen ein maximales Wort-Honorar herausschinden kann, begreift Qualitätsvorgaben ebenso schnell.

    Ein Wort-Honorar ohne "Regulierung" ist definitiv der falsche Weg!

    Mit freundlichen Grüßen
    Klaus

    (E-Mail-Adresse auf Anfrage)

Hinterlassen Sie einen Kommentar