Tutorials

Excel-Tutorial: Listen und Zeilen basierend auf Zellwerten automatisch befüllen

Melden

Hier ist ein umfassendes Tutorial, das die gängigsten Methoden beschreibt, um Listen oder Zeilen basierend auf einem Zellwert in Excel zu generieren.

In Excel gibt es zwei Hauptszenarien für dieses Thema:

  1. Listen filtern: Eine Liste von Daten anzeigen, die einem bestimmten Kriterium entspricht.
  2. Zeilen vervielfältigen: Eine Zeile x-mal wiederholen (basierend auf einer Zahl in einer Zelle).

In diesem Tutorial lernst du beide Lösungen kennen.

Teil 1: Dynamische Liste basierend auf Auswahl (Die FILTER-Funktion)

Dies ist ideal, wenn du z. B. eine Kategorie auswählst und alle zugehörigen Datensätze sofort darunter erscheinen sollen.

Voraussetzungen:

  • Excel 365 oder Excel 2021+

Schritt-für-Schritt-Anleitung:

  1. Daten vorbereiten: Erstelle eine Tabelle mit deinen Rohdaten (z. B. Spalte A: „Abteilung“, Spalte B: „Mitarbeiter“).
  2. Suchkriterium festlegen: Bestimme eine Zelle (z. B. D2), in die du den Namen der Abteilung schreibst.
  3. Die Formel anwenden: Klicke in die Zelle, in der die Liste beginnen soll (z. B. D4), und gib folgende Formel ein:
    =FILTER(A2:B10; A2:A10 = D2; "Keine Daten gefunden")
    • A2:B10: Der gesamte Datenbereich.
    • A2:A10 = D2: Die Bedingung (Suche in Spalte A nach dem Wert aus D2).
    • "Keine Daten gefunden": Text, falls kein Treffer erzielt wird.

Ergebnis: Sobald du den Wert in D2 änderst, passt sich die gesamte Liste darunter automatisch an.

Teil 2: Zeilen basierend auf einer Zahl vervielfältigen (Power Query)

Oft möchte man, dass eine Zeile mehrfach untereinander erscheint, wenn in einer Spalte z. B. die Anzahl „3“ steht. Dies ist mit Standard-Formeln schwierig, aber mit Power Query (integriert in Excel) sehr einfach.

Beispiel-Szenario:

Produkt Menge
Apfel 3
Birne 2

Ziel: Der „Apfel“ soll in 3 Zeilen erscheinen, die „Birne“ in 2 Zeilen.

Schritt-für-Schritt-Anleitung:

  1. Tabelle formatieren: Markiere deine Daten und drücke Strg + T, um sie in eine offizielle Excel-Tabelle umzuwandeln.
  2. In Power Query laden: Gehe auf den Reiter Daten -> Aus Tabelle/Bereich.
  3. Spalte hinzufügen:
    • Klicke im Power Query Editor auf den Reiter Spalte hinzufügen -> Benutzerdefinierte Spalte.
    • Gib der Spalte einen Namen (z. B. "Wiederholung").
    • Gib folgende Formel ein:
      ={1..[Menge]}

      (Hinweis: Ersetze [Menge] durch den Namen deiner Spalte, in der die Zahl steht. Die geschweiften Klammern erstellen eine Liste von 1 bis X).

  4. Liste erweitern:
    • In der neuen Spalte siehst du nun überall das Wort „List“.
    • Klicke auf das kleine Symbol mit den zwei Pfeilen oben rechts im Spaltenkopf der neuen Spalte.
    • Wähle Auf neue Zeilen ausweiten.
  5. Laden: Gehe auf Datei -> Schließen & laden.

Ergebnis: Excel erstellt ein neues Tabellenblatt, in dem die Zeilen entsprechend der Anzahl dupliziert wurden. Wenn du die Quellzahlen änderst, musst du nur auf Daten -> Alle aktualisieren klicken.

Teil 3: Abhängige Dropdown-Listen (Datengültigkeit)

Wenn du möchtest, dass Dropdown-Liste B nur Werte anzeigt, die zur Auswahl in Dropdown-Liste A passen.

  1. Listen benennen: Erstelle Listen für deine Kategorien (z. B. Obst, Gemüse). Markiere die Liste „Apfel, Birne“ und nenne den Bereich oben links im Namensfeld exakt so wie die Hauptkategorie (z. B. „Obst“).
  2. Erstes Dropdown: Wähle eine Zelle -> Daten -> Datenüberprüfung -> Liste -> Quelle: =Obst,Gemüse.
  3. Abhängiges Dropdown: Wähle die benachbarte Zelle -> Daten -> Datenüberprüfung -> Liste.
  4. Die Zauberformel: Gib bei Quelle folgendes ein:
    =INDIREKT(A1)

    (A1 ist hier die Zelle des ersten Dropdowns).

Ergebnis: Wenn du in A1 „Obst“ wählst, zeigt das zweite Dropdown nur die Produkte an, die im benannten Bereich „Obst“ liegen.

Zusammenfassung: Welche Methode für was?

  • Verwende FILTER, wenn du eine Übersicht aus einer großen Datenbank extrahieren willst.
  • Verwende Power Query, wenn du Zeilen für Etiketten, Buchungen oder Exporte vervielfältigen musst.
  • Verwende INDIREKT, wenn du eine geführte Dateneingabe über Dropdowns erstellen willst.
0