Listen umbauen, nummerieren und Beträge verteilen in Excel

Vier häufige Aufgaben mit Listen, jeweils mit einer klassischen Formel und, wo es sie gibt, einer kürzeren Lösung für Microsoft 365.

Mehrzeilige Datensätze in Zeilen umbauen

Nach einem Import steht eine Adresse über vier Zeilen verteilt untereinander: Name, Straße, Ort, Telefon, dann die nächste Adresse. Für eine Tabelle soll jede Adresse in einer Zeile stehen.

In Microsoft 365 erledigt das eine Formel:

=ZEILENUMBRUCH(A1:A400;4)

ZEILENUMBRUCH bricht die Spalte nach je vier Einträgen in eine neue Zeile um. Ohne diese Funktion hilft INDEX. In die erste Zelle der Zieltabelle schreiben, nach rechts und unten kopieren:

=INDEX($A:$A;(ZEILE(A1)-1)*4+SPALTE(A1))

ZEILE(A1) und SPALTE(A1) dienen als Zähler, die sich beim Kopieren verändern. Ältere Lösungen setzen die Adresse mit INDIREKT zusammen. INDEX ist kürzer und wird nicht bei jeder Änderung neu berechnet. Beide Wege setzen voraus, dass jeder Datensatz genau gleich viele Zeilen hat.

Nur gefüllte Zeilen nummerieren

In Spalte A soll eine laufende Nummer stehen, aber nur, wenn in Spalte B etwas eingetragen ist. Leere Zeilen werden übersprungen:

=WENN(B2<>"";ANZAHL2($B$2:B2);"")

Der Bereich $B$2:B2 wächst beim Kopieren nach unten mit, ANZAHL2 zählt die gefüllten Zellen bis zur aktuellen Zeile. Mit einer Lücke in Zeile 6 ergibt sich 1, 2, 3, 4, leer, 5, 6, 7. Für gefilterte Listen, in denen nur sichtbare Zeilen gezählt werden sollen, gibt es einen eigenen Beitrag.

Beträge ohne Rundungsdifferenz verteilen

Ein Jahresbudget, eine Rechnung oder Kosten sollen nach Anteilen auf mehrere Zeilen verteilt werden. Mit dem Betrag in A1 und den Gewichten in B1:B3:

=RUNDEN($A$1*B1/SUMME($B$1:$B$3);2)

Das Problem: Gerundete Teile ergeben nicht immer die Summe. 1.000 € auf drei gleiche Anteile ergibt dreimal 333,33 €, zusammen 999,99 €. Die Lösung ist, die letzte Zeile als Rest zu berechnen:

=$A$1-SUMME($D$1:D2)

Damit ergeben sich 333,33 €, 333,33 € und 333,34 €, zusammen genau 1.000 €. Mit den Gewichten 5, 3 und 2 verteilen dieselben Formeln 500, 300 und 200. Für eine gleichmäßige Verteilung setzen Sie alle Gewichte auf 1.

Wörter in einer Zelle zählen

Häufig wird =LÄNGE(A1)-LÄNGE(WECHSELN(A1;" ";""))+1 empfohlen: Anzahl der Leerzeichen plus eins. Doppelte Leerzeichen oder ein Leerzeichen am Ende zählen dabei als zusätzliche Wörter, und eine leere Zelle ergibt 1. Für „Dies ist ein Beispieltext “ liefert die Formel sechs statt vier Wörter. Sicherer:

=WENN(GLÄTTEN(A1)="";0;LÄNGE(GLÄTTEN(A1))-LÄNGE(WECHSELN(GLÄTTEN(A1);" ";""))+1)

GLÄTTEN entfernt Leerzeichen am Anfang und Ende und reduziert mehrfache auf eines. In Microsoft 365 geht es kürzer mit =ANZAHL2(TEXTTEILEN(GLÄTTEN(A1);" ")).

Typische Fehler

  • #ÜBERLAUF! bei ZEILENUMBRUCH: Rechts und unterhalb der Formel muss genug Platz sein.
  • Datensätze unterschiedlich lang: Fehlt bei einer Adresse die Telefonzeile, verrutschen alle folgenden. Prüfen Sie die Rohdaten vorher, etwa mit einer Hilfsspalte, die jeden Namen markiert.
  • Rest in der falschen Zeile: Steht die Restformel nicht in der letzten Zeile, stimmt die Summe, aber die Verteilung nicht. Kopieren Sie die Rundungsformel nur bis zur vorletzten Zeile.

Passende Beiträge

Lernen oder machen lassen?

Excel-Formeln, die im Alltag sitzen.

In einer Schulung arbeiten wir mit Ihren eigenen Dateien, damit Sie das Gelernte am nächsten Tag nutzen. Oder wir bauen die Lösung direkt für Sie.