Tutorials

Dynamische Dropdown-Menüs direkt aus Pivot-Tabellenwerten erstellen

Melden

Dieses Tutorial zeigt dir, wie du dynamische Dropdown-Menüs (Datenüberprüfung) direkt aus den Werten einer Pivot-Tabelle erstellst.

Das Problem

Excel erlaubt es standardmäßig nicht, eine Pivot-Tabelle direkt als Quelle für eine Datenüberprüfung auszuwählen. Wenn sich die Pivot-Tabelle erweitert (neue Daten), würde ein statischer Zellbezug die neuen Einträge ignorieren.

Hier sind die zwei besten Methoden, um dieses Problem zu lösen:

Methode 1: Der "Überlauf"-Trick (Für Excel 365 & Excel 2021)

Dies ist die einfachste und modernste Methode.

  1. Pivot-Tabelle vorbereiten: Erstelle deine Pivot-Tabelle (z. B. eine Liste von Produktnamen in Spalte A).
  2. Hilfs-Formel erstellen: Klicke in eine leere Zelle neben der Pivot-Tabelle und gib folgende Formel ein: =A4# (Ersetze A4 durch die erste Zelle deiner Pivot-Daten, ohne die Überschrift). Wichtig: Pivot-Tabellen unterstützen den #-Operator nicht direkt. Daher nutzen wir einen kleinen Umweg: Gib stattdessen einfach =A4:A100 ein (oder so weit die Liste gehen könnte) ODER nutze die EINDEUTIG-Funktion auf die Quelldaten.
  3. Die bessere Alternative für 365: Gib neben der Pivot-Tabelle ein: =A4:INDEX(A:A;VERGLEICH("Gesamtergebnis";A:A;0)-1) Dies greift alle Daten bis kurz vor das Gesamtergebnis ab.
  4. Datenüberprüfung:
    • Gehe zu der Zelle, in der das Dropdown erscheinen soll.
    • Daten > Datenüberprüfung > Liste.
    • Quelle: Klicke auf die Zelle mit der Hilfsformel und füge ein # hinzu (z.B. =$G$4#).

Methode 2: Dynamischer Benannter Bereich (Funktioniert in allen Excel-Versionen)

Dies ist die professionellste Methode, da sie keine Hilfszellen benötigt.

  1. Namen definieren:

    • Gehe auf den Reiter Formeln > Namensmanager.
    • Klicke auf Neu.
    • Name: MeinDropdownListe
    • Bezieht sich auf: Kopiere diese Formel hinein:
      =BEREICH.VERSCHIEBEN($A$4;0;0;ANZAHL2($A:$A)-2;1)
      • $A$4: Die erste Zelle deiner Daten in der Pivot.
      • ANZAHL2($A:$A)-2: Zählt alle Einträge in Spalte A und zieht 2 ab (für die Überschrift "Zeilenbeschriftung" und das "Gesamtergebnis").

  2. Datenüberprüfung anwenden:

    • Wähle die Zielzelle aus.
    • Daten > Datenüberprüfung.
    • Wähle Liste.
    • Quelle: Gib =MeinDropdownListe ein.

Methode 3: Die "Indirekt"-Lösung (Schnell & Dirty)

Wenn du die Pivot-Tabelle schnell referenzieren willst, ohne komplexe Formeln:

  1. Markiere den Datenbereich in deiner Pivot-Tabelle (ohne Überschriften).
  2. Gib oben links im Namensfeld (neben der Bearbeitungsleiste) einen Namen ein, z.B. Produkte.
  3. In der Datenüberprüfung nutzt du als Quelle =Produkte.
  4. Nachteil: Wenn die Pivot-Tabelle wächst, musst du den Namen manuell im Namensmanager anpassen. Deshalb ist Methode 2 vorzuziehen.

Profi-Tipps für Pivot-Listen:

  • Gesamtergebnis entfernen: Damit "Gesamtergebnis" nicht in deinem Dropdown erscheint, gehe in die Pivot-Optionen (Design > Gesamtergebnisse) und schalte sie für Zeilen und Spalten aus. Dann musst du in der Formel aus Methode 2 nur -1 (für die Überschrift) abziehen.
  • Leere Zellen vermeiden: Wenn deine Pivot-Tabelle "(leer)" anzeigt, kannst du das im Pivot-Filter ausschließen, damit es nicht im Dropdown landet.
  • Aktualisierung: Denke daran, dass sich das Dropdown erst aktualisiert, wenn die Pivot-Tabelle aktualisiert wurde (Rechtsklick > Aktualisieren).

Welche Methode soll ich wählen?

  • Wenn du Excel 365 hast: Nutze eine Hilfsspalte mit #-Überlauf.
  • Wenn du eine robuste Vorlage baust: Nutze Methode 2 (BEREICH.VERSCHIEBEN).
0