Tipps und Tricks rund um Excel
| A | B | C | D | E | F | G | H | I/J | K |
| L | M | N | O | P/Q | R | S | T | U-W | X-Z |
Inhaltsverzeichnis
Eine umfangreiche Arbeitsmappe mit vielen Tabellenblättern soll ein Inhaltsverzeichnis über alle Blätter bekommen, dabei sollen die Blattnamen automatisch ermittelt und auch gleich als Hyperlink angelegt werden. Die Formel stammt soweit ich weiß von Thomas Ramel.
 |
Mit der Funktion ARBEITSMAPPE.ZUORDNEN wird eine Funktion verwendet, die Excel als "Makro" versteht. Die Datei muss darum als "Makro enabled" mit der Dateiendung XLSM gespeichert werden. Sie müssen die Makroausführung erlauben bzw. die Arbeismappe in einem vertrauenswürdigen Speicherort speichen. |
Gebraucht wird dazu die sehr alte Excel-4.0-Funktion ARBEITSMAPPE.ZUORDNEN(1). Damit werden die Namen der Tabellenblätter ausgegeben. Die Funktion hat einen Nachteil: sie aktualisiert sich nicht von alleine. Neue Blätter würden nicht angezeigt, längst gelöschte oder umbenannte nicht verschwinden.
Darum verbindet man die Funktion mit der Zeitfunktion JETZT(), die sekündlich einen anderen Wert annimmt.
=ARBEITSMAPPE.ZUORDNEN(1+0*JETZT())
Die Funktion kann nicht in einem Tabellenblatt wie andere Funktionen verwendet werden. Sie ist ausschließlich als Inhalt eines Namens möglich. Es muss also ein Name (x) angelegt werden, diesem Namen wird als Bezug die Funktion zugewiesen.
|
|
- "Bezieht sich auf" ist die Funktion: =ARBEITSMAPPE.ZUORDNEN(1+0*JETZT())
Bestätigen Sie den Namen mit OK.
Legen Sie jetzt ein Tabellenblatt für das Inhaltsverzeichnis an. Beginnen Sie in Zelle A1 mit dieser Formel (alles in einer Zeile, keine Umbrüche in Excel machen!):
=WENN(ZEILE(A1)>ANZAHL2(x);"";HYPERLINK("#'"&INDEX(x;ZEILE(A1))&"'!A1";
TEIL(INDEX(x;ZEILE(A1));FINDEN("]";INDEX(x;ZEILE(A1)))+1;31)))
Überall, wo in der Formel "x" steht, liefert der Name "x" über die Funktion "ARBEITSMAPPE.ZUORDNEN" alle Blattnamen zurück. Man kann das sehr schön erkennen, wenn man in der Formel das "x" auflöst.
| Stellen Sie sich in A1 und wählen Sie "Formeln | Formelauswertung". Drücken Sie zweimal auf "Auswerten" - die Auswertung startet links mit "ZEILE(A1)" und geht dann weiter auf das "x" in "ANZAHL2(x). |
|
In meinem Beispiel befinden sich zwei Tabellenblätter in der Mappe: "Inhalt" und "Januar". ARBEITSMAPPE.ZUORDNEN liefert für diese Blätter den kompletten Namen zurück: Dateiname plus Blattname. Ich erhalt also ein sogenanntes Array:
{"[Inhaltsverzeichnis.xlsm]Inhalt"."[Inhaltsverzeichnis.xlsm]Januar"}
Wie funktioniert dieser Moloch? Ich drösle mal von links nach rechts auf:
=WENN(ZEILE(A1)>ANZAHL2(x);"";
Im ersten Schritt wird die Zeilennummer ermittelt (von A1 ist das 1) und es wird gezählt wieviele Blattnamen vorhanden sind(ANZAHL2 von Blattname 1, Blattname 2...). Ist die Zeilennummer größer als die Anzahl der Blätter, wird nichts mehr ausgegeben ("").
HYPERLINK("#'"&INDEX(x;ZEILE(A1))&"'!A1"
Dann erstellen wir den Hyperlink. INDEX(x;ZEILE(A1)) ermittelt aus unserem Array (den beiden Blattnamen) den für die Position der Zelle A1 - A1 ist die erste Zeile, der erste Blattname ist [Inhaltsverzeichnis.xlsm]Inhalt. Darum herum wird mit HYPERLINK der Hyperlink zusammengesetzt. Zuerst das # gefolgt von einem Hochkomma ', dann der Blattname mit Dateiname, dann wieder ein Hochkomma ', gefolgt von !A1 für einen Zellbezug.
TEIL(INDEX(x;ZEILE(A1));FINDEN("]";INDEX(x;ZEILE(A1)))+1;31)
Das ist der "friendly name" des Hyperlinks - die Bezeichnung, die in der Zelle als Text angezeigt wird. Hier wird zuerst wieder mit INDEX genauso wie beim Hyperlink der erste Blattname ermittelt. Dann wird mit FINDEN die Position der schließenden eckige Klammer ermittelt. Die alles umschließende Funktion TEIL hat jetzt den Text (den Datei- plus Blattnamen), das erste Zeichen (die Position der eckigen KLammer) und die Anzahl der Zeichen, die von dort übernommen werden wollen (31 - weil ein Tabellenblattname maximal 31 Zeichen lang sein darf.
Schreiben Sie diese gesamte Formel in Zelle A1 und füllen Sie sie solange nach unten aus, wei Tabellenblätter vorkommen können. Dann werden neue Blätter automatisch dem Inhaltsverzeichnis hinzugefügt. Sie können das in der Beispieltabelle ausprobieren: Fügen Sie neue Blätter hinzu, sie werden im gelb unterlegten Bereich ergänzt.
Wenn das erste Blatt mit dem Inhaltsverzeichnis nicht im Inhaltsverzeichnis erscheinen soll, lassen Sie A1 frei und beginnen erst in A2. Alternativ können Sie auch in A1 beginnen und zu ZEILE(A1) jeweils 1 addieren: ZEILE(A1)+1.
Für den Fall, dass Sie die Tabellenblattnamen im Inhaltsverzeichnis nicht untereinander schreiben möchten, sondern nebeneinander: wechseln Sie ZEILE gegen SPALTE aus.
Ein weitere Möglichkeit ist, statt mit ZEILE(A1) mit einer Zelle zu arbeiten, in der die erforderliche Ziffer für die Blattnummer steht. Damit kann das Inhaltsverzeichnis an jeder beliebigen Stelle im Blatt stehen, allerdings muss ich die Nummerierung vorgeben.
In Spalte H trage ich die Nummern ein, in I2 steht folgende Formel: =WENN(H2>ANZAHL2(x);""; Statt "ZEILE(xx)" verwende ich hier die Zellen H2 ... H8, um die Blattnummer zu ermitteln.
|
 |
Soll die Inhaltstabelle (Blattnummer 1) nicht angegeben werden, wird nicht H2 verwendet sondern "H2 +1".
10.02.2014/11.02.2015