Excel TOCOL und TOROW: 2D-Array in eine Spalte oder Zeile umwandeln

• TOCOL verwandelt ein zweidimensionales Array in eine einspaltige, dynamische Liste; TOROW macht dasselbe als einzeilige Liste.
• Beide Funktionen haben optionale Argumente zum Ignorieren von Leerzellen/Fehlern und zur Steuerung der Scan-Richtung (Reihe vs. Spalte).
• Kombiniert mit VSTACK/HSTACK, UNIQUE und SORT lassen sich mehrere Bereiche zusammenführen, bereinigen und alphabetisch ordnen.

Einführung

Excel bietet mit TOCOL und TOROW moderne, dynamische Werkzeuge, um tabellarische Bereiche (2D-Arrays) rasch in eindimensionale Listen zu transformieren. Diese Techniken vereinfachen Datenaufbereitung, Filtern und Automatisierung – besonders in Kombination mit Funktionen wie UNIQUE, SORT, VSTACK und HSTACK.

Kurzdefinitionen

• TOCOL: Wandelt ein 2D-Array in eine Spalte (1D-Array) um.
• TOROW: Wandelt ein 2D-Array in eine Zeile (1D-Array) um.

Verfügbarkeit

Diese Funktionen sind verfügbar in Excel 2024 oder neuer, Excel für Microsoft 365, Excel für das Web sowie in den Excel-Apps für Mobilgeräte und Tablets.

Syntax und Argumente

Die Syntax ist für beide Funktionen identisch im Aufbau:

=TOCOL(a,[b],[c])
=TOROW(a,[b],[c])

• a (erforderlich): Das Quell-Array oder der Bereich.
• b (optional): Steuerung, welche Werte ignoriert werden (1 = Leerzellen, 2 = Fehler, 3 = Leerzellen und Fehler). Wird b weggelassen, werden alle Werte übernommen.
• c (optional): Scanrichtung. Wird c weggelassen, wird standardmäßig zeilenweise (links nach rechts, obere nach untere Zeile) gescannt. TRUE bedeutet spaltenweise (oben nach unten, links nach rechts).

Hinweis: Das Ergebnis ist ein dynamisches Array, das automatisch aktualisiert wird, wenn sich die Quelldaten ändern. Dynamische Arrays sind jedoch nicht mit strukturierten Excel-Tabellen kompatibel — für eine statische Tabelle müssen die Ergebnisse als Werte eingefügt werden.

TOCOL: 2D-Array in eine Spalte umwandeln

Veranschaulichung

Stellen Sie sich vor, Sie haben in B2:F17 Manager-Namen in einem Bereich. Um alle Namen in einer Spalte aufzulisten, genügt die einfachste Formel:

=TOCOL(B2:F17)

Wenn Sie Leerzellen und Fehler ausschließen möchten, setzen Sie b = 3:

=TOCOL(B2:F17,3)

Wenn Sie die Werte spaltenweise (oben nach unten, dann nächste Spalte) statt zeilenweise auflisten möchten, setzen Sie c = TRUE:

=TOCOL(B2:F17,3,TRUE)

Wichtig

• Wenn b ausgelassen ist, erscheinen Leerzellen als 0 oder bleiben sichtbar; je nach Inhalt können Sie dadurch falsche Analysen erhalten.
• Dynamische Arrays überschreiben angrenzende Zellen, falls dort bereits Inhalte stehen.

Praxistipps

• Verwenden Sie immer b = 3, wenn Sie eine bereinigte Liste ohne leere Einträge und ohne Fehlerwerte möchten.
• Wenn die Reihenfolge wichtig ist (z. B. Datumsspalten), wählen Sie c so, dass die gewünschte Hauptrichtung zuerst gescannt wird.

TOROW: 2D-Array in eine Zeile umwandeln

TOROW funktioniert analog zu TOCOL, liefert aber eine einzeilige (horizontal ausgerichtete) Liste.

Beispiel: Alle Namen als Spaltenüberschriften verwenden:

=TOROW(B2:F17)

Um Leerzellen/Fehler zu ignorieren und spaltenweise zu scannen:

=TOROW(B2:F17,3,TRUE)

Praktische Anwendung

• TOROW ist nützlich, wenn Sie dynamische Spaltenüberschriften erstellen oder Daten horizontal weiterverarbeiten wollen (z. B. für Dashboard-Köpfe).

Mehrere Arrays zusammenführen: VSTACK und HSTACK

TOOLSET: VSTACK und HSTACK

• VSTACK stapelt Bereiche untereinander (vertikal).
• HSTACK fügt Bereiche nebeneinander (horizontal) an.

Um mehrere Bereiche zu einer einzigen Spalte zu kombinieren, verschachteln Sie VSTACK in TOCOL:

=TOCOL(VSTACK(B2:F17,B20:F35),3)

Wenn Sie HSTACK verwenden, kombiniert TOCOL die nebeneinander stehenden Bereiche entsprechend der Scanrichtung:

=TOCOL(HSTACK(B2:F17,B20:F35),3)

Beispielanwendung

• Konsolidierung regionaler Daten (Europa + Asien) in einer Analyse-Spalte.
• Vorbereiten von Datensätzen für PivotTables oder Diagramme, die eine eindimensionale Quelle benötigen.

Nur eindeutige und sortierte Werte erzeugen

Kombinieren Sie TOCOL/TOROW mit UNIQUE und SORT, um Duplikate zu entfernen und die Liste zu sortieren.

Beispiel: Einfache, alphabetische Liste eindeutiger Namen

=SORT(UNIQUE(TOCOL(B2:F17)))

Reverse-Sortierung (absteigend):

=SORT(UNIQUE(TOCOL(B2:F17)),,-1)

Wenn Sie TOROW statt TOCOL verwenden, müssen UNIQUE und SORT wissen, dass die Eingabe eine Zeile ist. Setzen Sie dazu die jeweiligen TRUE-Arguments:

=SORT(UNIQUE(TOROW(B2:F17),TRUE),,,TRUE)

Anwendungsfälle

• Erstellen einer alphabetischen Kontaktliste aus verstreuten Eingaben.
• Vorbereitung einer eindeutigen Auswahl für Dropdowns (Datenvalidierung).

Fehlerbehebung und typische Fallstricke

Wichtig

• Dynamische Arrays überschreiben angrenzende Daten und führen zum #SPILL!-Fehler, wenn der Spill-Bereich blockiert ist.
• Beim Arbeiten mit Formeln, die Fehler enthalten, prüfen Sie b-Argument, um Fehler zu filtern.

Häufige Probleme und Lösungen

• Problem: #SPILL! — Ursache: angrenzende Zellen sind nicht leer. Lösung: Bereich freiräumen oder Formel verschieben.
• Problem: Ergebnis enthält Nullen für Leerzellen — Ursache: b weggelassen. Lösung: b = 1 (Leerzellen ignorieren) oder b = 3 (Leerzellen und Fehler ignorieren).
• Problem: Ergebnis wird nicht als Tabelle akzeptiert — Grund: dynamische Arrays lassen sich nicht direkt als strukturierte Tabelle formatieren. Lösung: Werte kopieren und als Werte einfügen (Ctrl+C, Ctrl+Shift+V) — beachten: dann verliert die Liste die Dynamik.

Spezialfälle

• Wenn die Quelldaten formatierte Zahlen/Datumswerte enthalten, kann das Ergebnis Datumsformate beibehalten oder als serielle Zahl erscheinen — prüfen Sie die Zielzellenformatierung.
• Wenn Sie Spalten mit unterschiedlichen Datentypen zusammenführen (Text und Zahlen), werden alle Elemente als gemischte Typen zurückgegeben. Ggf. mit TEXT/FIXED vorformatieren.

Alternative Ansätze und wann sie sinnvoll sind

• TRANSPOSE: Dreht Zeilen und Spalten um; nützlich, wenn Sie gesamte Tabellenstruktur invertieren wollen, nicht aber, wenn Sie ein 2D-Array in eine Liste transformieren möchten.
• PivotTable: Ideal für Aggregation, Gruppierung und schnelle Zusammenfassungen, aber weniger geeignet, um eine einfache eindimensionale Liste zu erzeugen.
• Power Query: Leistungsfähiger ETL-Ansatz; empfehlenswert bei großen Datenmengen, komplexer Bereinigung (Trim, Split, Merge) oder beim wiederkehrenden Laden mehrerer Quellen.

Entscheidungshilfe

• Kleinere, ad-hoc-Transformationen innerhalb der Arbeitsmappe: TOCOL/TOROW + UNIQUE/SORT.
• Wiederkehrende oder komplexe Transformationen über mehrere Dateien: Power Query.
• Aggregationen und schnelle Pivot-Analysen: PivotTable.

Mini-Methodik: Daten-Transformation mit TOCOL/TOROW (SOP)

1. Ziel definieren: Spalte oder Zeile? Einzigartige Werte? Sortierung?
2. Bereich auswählen: Prüfen, ob der Bereich leere oder fehlerhafte Zellen enthält.
3. Formel bauen: TOCOL/TOROW mit geeigneten b-/c-Parametern.
4. Zusätzliche Bereinigung: UNIQUE, SORT, TEXT/NUMBERS.
5. Ausgabe prüfen: Auf #SPILL!, Typenkonsistenz, Formatierung.
6. Optional: Als Werte kopieren, wenn eine statische Liste benötigt wird.

Rollenbasierte Checkliste

• Analyst

  • Prüfen, ob Quelldaten konsistent (Datentypen) sind.
  • b = 3 verwenden, um saubere Listen zu erhalten.
  • Ergebnisse in Datenmodell einbinden.

• Manager / CEO

  • Prüfen, ob die Liste alle Regionen/Quellen enthält.
  • Ergebnis auf Geschäftsrelevanz und Vollständigkeit prüfen.

• Entwickler / Power-User

  • Automatisierung prüfen (Power Query oder VBA falls nötig).
  • Edge-Cases mit Fehler-Handling (IFERROR, ISBLANK) abdecken.

Testfälle und Akzeptanzkriterien

• Akzeptanzkriterium 1: Gegeben ein 2x3-Bereich mit Werten, gibt TOCOL alle 6 Werte in der korrekten Reihenfolge zurück.
• Akzeptanzkriterium 2: Bei b = 3 werden alle Leerzellen und Fehler entfernt.
• Akzeptanzkriterium 3: VSTACK(Bereich1,Bereich2) gefolgt von TOCOL gibt die Werte beider Bereiche ohne Duplikate zurück, wenn UNIQUE verwendet wird.
• Testfall: Spill-Bereich blockiert — erwarteter Fehler #SPILL!. Ergebnis: Formel liefert #SPILL! und Hinweis, welche Zelle blockiert.

Decision Tree (Mermaid)

flowchart TD
  A[Start: Soll die Ausgabe eine Spalte oder Zeile sein?] -->|Spalte| B[Verwende TOCOL]
  A -->|Zeile| C[Verwende TOROW]
  B --> D{Mehrere Bereiche?}
  C --> D
  D -->|Ja| E{Vertikal oder horizontal zusammenführen?}
  D -->|Nein| F[Weiter zur Bereinigung]
  E -->|Vertikal| G[VSTACK]
  E -->|Horizontal| H[HSTACK]
  G --> F
  H --> F
  F --> I{Leer/Fehler entfernen?}
  I -->|Ja| J[b=3]
  I -->|Nein| K[b weglassen]
  J --> L{Sortieren/Einzigartig?}
  K --> L
  L -->|Ja| M[UNIQUE + SORT]
  L -->|Nein| N[Ergebnis einsetzen]
  M --> N
  N --> O[Ende]

Rollback- und Incident-Runbook (kurz)

Wenn Transformations-Formel einen Fehler auslöst:

1. Prüfen Sie die Fehlermeldung (#SPILL!, #VALUE!, #REF!, #NAME?).
2. Bei #SPILL!: angrenzende Zellen leeren oder Formel umpositionieren.
3. Bei #VALUE! oder #REF!: Bereichsbezüge kontrollieren (sind die Quellen noch vorhanden?).
4. Wenn nötig, Formel temporär durch einfache Kopie (als Werte) ersetzen, um Berichte zu stabilisieren.
5. Nach Problembehebung Quelle wiederherstellen und dynamische Formel reaktivieren.

Beispiele aus der Praxis

Beispiel 1 — Managerliste (einfach):

=TOCOL(B2:F17,3)

Beispiel 2 — Konsolidierte, sortierte Teilnehmerliste (Europa + Asien):

=SORT(UNIQUE(TOCOL(VSTACK(B2:F17,B20:F35)),))

Beispiel 3 — TOROW als Kopfzeilen-Generator:

=TOROW(B2:F17,3)

Hinweis

• Um die Ausgabe in einer anderen Arbeitsmappe zu nutzen, verlinken Sie die Zelle(n) über klassische Zellbezüge oder kopieren Sie die Werte.

Ergänzende Tools und Best Practices

• Power Query ist optimal für wiederkehrende Exporte, Filterung, Typkonvertierung und Merging großer Tabellen.
• Verwenden Sie benannte Bereiche, um Formeln lesbarer und robuster zu machen.
• Dokumentieren Sie Transformationen (z. B. in einem separaten Blatt mit Eingabe-, Prozess- und Ausgabe-Beschreibungen).

Glossar (1‑Zeiler)

• Dynamisches Array: Eine Formel, die mehrere Zellen automatisch füllt und sich bei Änderungen aktualisiert.
• Spill-Bereich: Der Bereich, den ein dynamisches Array einnimmt; kann blockiert werden und #SPILL! verursachen.
• VSTACK/HSTACK: Funktionen zum vertikalen bzw. horizontalen Zusammenfügen mehrerer Arrays.

Zusammenfassung

• TOCOL und TOROW sind schnelle Wege, um 2D-Bereiche in saubere 1D-Listen zu verwandeln.
• Verwenden Sie b = 3 für bereinigte Listen und c = TRUE, um Spalten zuerst zu scannen.
• Kombinieren Sie mit VSTACK/HSTACK, UNIQUE und SORT für leistungsstarke Datenaufbereitung ohne Power Query.

FAQ

Funktionieren TOCOL und TOROW mit Excel-Tabellen (strukturierte Tabellen)?

Nein. Dynamische Arrays können nicht direkt in eine strukturierte Excel-Tabelle gespillt werden. Kopieren Sie bei Bedarf die Ergebnisse und fügen Sie sie als Werte ein.

Wie behebe ich den #SPILL!-Fehler?

Leeren Sie den Bereich rechts bzw. unterhalb der Formel, in den die Ausgabe gespillt werden soll, oder verschieben Sie die Formel an eine freie Stelle.

Wann ist Power Query die bessere Wahl?

Wenn die Transformation wiederholt ausgeführt werden soll, Daten aus mehreren Dateien/Sheets kombiniert werden müssen oder komplexe Bereinigungs- und Typkonvertierungsregeln erforderlich sind.

Microsoft 365 Personal

OS

Windows, macOS, iPhone, iPad, Android

Kostenlose Testversion

1 Monat

Microsoft 365 bietet Zugang zu Office-Apps wie Word, Excel und PowerPoint auf bis zu fünf Geräten, 1 TB OneDrive-Speicher und mehr.

$100 bei Microsoft

Expand

Collapse