Tabellennamen mit Formel auflisten - Excel & Google Sheets

Beispielarbeitsbuch herunterladen

Laden Sie die Beispielarbeitsmappe herunter

In diesem Tutorial wird gezeigt, wie Sie die Blattnamen einer Arbeitsmappe mit einer Formel in Excel auflisten.

Listenblattnamen mit benanntem Bereich und Formel auflisten

Es gibt keine integrierte Funktion in Excel, die alle Arbeitsblätter in einer Arbeitsmappe auflisten kann. Dies kann jedoch durch eine Kombination verschiedener Funktionen erreicht werden.

Um die Blattnamen in einer Arbeitsmappe aufzulisten, gehen wir wie folgt vor:

1. Erstellen Sie einen benannten Bereich "Arbeitsblätter"
2. Verwenden Sie eine Formel, um alle Blattnamen aufzulisten.

Namensbereich für Blattnamen erstellen

Um einen benannten Bereich für die Blattnamen zu erstellen, im Excel Ribbon: Formeln > Namensmanager > Neu

Geben Sie „Arbeitsblätter“ in das Namensfeld ein:

Im Abschnitt „Bezieht sich auf“ des Dialogfelds müssen wir die Formel schreiben

1	=ARBEITSBUCH ERHALTEN(1) & T(JETZT())"

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

Die Funktion „GET.WORKBOOK“ ist eine Makrofunktion, daher muss Ihre Arbeitsmappe als makrofähige Arbeitsmappe (Dateiformat: .xlsm) gespeichert werden, damit die Blattnamen bei jedem Öffnen der Arbeitsmappe aktualisiert werden.

Hinweis: Beim Ausfüllen des Dialogfelds Name bearbeiten sollte die Arbeitsmappe als Bereich des Namensbereichs ausgewählt werden.

Verwenden von Formeln zum Auflisten von Blattnamen

Jetzt verwenden wir eine Formel, um die Blattnamen aufzulisten. Wir benötigen die Funktionen INDEX, MID, FIND und ROWS:

1	=INDEX(MID(Arbeitsblätter,FIND("]",Arbeitsblätter)+1,255),ZEILEN($B$5:B5))

• Die obige Formel verwendet das Array "Arbeitsblätter" und zeigt jeden Blattnamen basierend auf seiner Position an.
• Die Funktionen MID und FIND extrahieren die Blattnamen aus dem Array (Entfernen des Arbeitsmappennamens).
• Dann zeigen die INDEX- und ROW-Funktionen jeden Wert in diesem Array an.
• Hier ist „Übersicht“ das erste Blatt in den Arbeitsmappen und „Reinigung“ das letzte.

Weitere Informationen zur Funktionsweise der MID- und FIND-Funktionen zum Abrufen von Blattnamen finden Sie unter get-sheet-name link.

Alternative Methode

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

1	=ARBEITSBUCH ERHALTEN(1) & T(JETZT())

Setzen Sie Ihr Feld „Bezieht sich auf“ auf

1	=ERSETZEN(GET.WORKBOOK(1),1,FIND("]",GET.WORKBOOK(1)),"")

Jetzt sind MID, FIND und ROWS in Ihrer Formel nicht mehr erforderlich. Ihr benannter Bereich besteht bereits nur aus Blattnamen.

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

1	=INDEX(Blattname,B3)