Blattnamen mit Formel auflisten – Excel

Dieses Tutorial zeigt Ihnen, wie Sie die Blattnamen einer Arbeitsmappe mit einer Formel in Excel auflisten können.

Blattnamen mit einem benannten Bereich und Formel auflisten

In Excel gibt es keine eingebaute Funktion, die alle Arbeitsblätter in einer Arbeitsmappe auflisten kann. Stattdessen haben Sie zwei Möglichkeiten:

1. Verwenden eines VBA-Makros, um alle Blätter in der Arbeitsmappe aufzulisten
2. Eine Formel erstellen, um alle Blätter aufzulisten

Wenn Sie eine Formel verwenden möchten, gehen Sie folgendermaßen vor:

1. Einen benannten Bereich namens „Arbeitsblaetter“ erstellen
2. Eine Formel verwenden, um alle Blattnamen aufzulisten.

Namensbereich für Blattnamen erstellen

Um einen benannten Bereich für die Blattnamen zu erstellen, wählen Sie in der Excel-Hauptregisterkarte: Formeln > Namensmanager > Neu

Geben Sie „Arbeitsblaetter“ in das Feld „Name“ ein:

Im Abschnitt „Verweist auf“ des Dialogfelds müssen wir die folgende Formel eingeben:

=ARBEITSMAPPE.ZUORDNEN(1) & T(JETZT())

Diese Formel speichert die Namen aller Blätter (als Array in diesem Format: „[Arbeitsmappe.xlsm].Übersicht“) in der Arbeitsmappe im benannten Bereich „Arbeitsblaetter“.

Die Funktion „ARBEITSMAPPE“ ist eine Makrofunktion, d.h. Ihre Mappe muss als „Arbeitsmappe mit Makros“ (Dateiformat: .xlsm) gespeichert sein, damit die Blattnamen bei jedem Öffnen der Arbeitsmappe aktualisiert werden.

Hinweis: Beim Ausfüllen des Dialogfelds „Namen bearbeiten“ sollte die Arbeitsmappe als Bereich für den Namen ausgewählt werden.

Eine Formel zum Auflisten von Blattnamen verwenden

Jetzt verwenden wir eine Formel, um die Blattnamen aufzulisten. Dazu benötigen wir die Funktionen INDEX, TEIL, FINDEN und ZEILEN:

=INDEX(TEIL(Arbeitsblaetter;FINDEN("]";Arbeitsblaetter)+1;255);ZEILEN($B$5:B5))

 

• Die obige Formel nimmt den Bereich „Arbeitsblaetter“ und zeigt jeden Blattnamen basierend auf seiner Position an.
• Die Funktionen TEIL und FINDEN extrahieren die Blattnamen aus dem Array (ohne den Namen der Arbeitsmappe).
• Dann zeigen die Funktionen INDEX und ZEILEN jeden Wert in diesem Array an.
• Hier ist „Übersicht“ das erste Blatt in der Arbeitsmappe und „Bereinigung“ das letzte.

Klicken Sie auf den Link für weitere Informationen darüber, wie die Funktionen TEIL und FINDEN Blattnamen ermitteln.

Alternative Methode

Sie haben auch die Möglichkeit, die Liste der Blattnamen im Namensmanager zu erstellen. Anstelle von

=ARBEITSMAPPE.ZUORDNEN(1) & T(JETZT())

belegen Sie Ihr Feld „Verweist auf“ mit

=ERSETZEN(ARBEITSMAPPE.ZUORDNEN(1);1;FINDEN("]";ARBEITSMAPPE.ZUORDNEN(1));"")

Jetzt brauchen Sie TEIL, FINDEN und ZEILEN in Ihrer Formel nicht mehr. Ihr benannter Bereich besteht bereits nur aus Blattnamen.

Verwenden Sie diese einfachere INDEX-Formel, um die Blätter aufzulisten:

=INDEX(Blattname;B3)