So erstellen Sie Inhalte automatisch in Excel. Formel für Blatt- und Seitenzahlen in Excel

Wenn die Anzahl der Blätter in Ihrer Excel-Arbeitsmappe die zweiten zehn überschreitet, wird das Navigieren durch die Blätter zum Problem. Eine der schönen Möglichkeiten, dieses Problem zu lösen, besteht darin, ein Inhaltsverzeichnis mit Hyperlinks zu erstellen, die zu den entsprechenden Seiten des Buches führen:

Es gibt mehrere Möglichkeiten, dies umzusetzen.

Video

Im Dokument platzieren

HYPERLINK Schicht und/oder Strg

Inhaltsverzeichnis.

Offen Namensmanager auf der Registerkarte Inhaltsverzeichnis. Auf dem Feld Bereich (Referenz) Geben Sie diese Formel ein:

GET.WORK.BOOK(1)
=GET.WORKBOOK(1)

Jetzt in der Variablen Inhaltsverzeichnis INDEX

Funktion REIHE

ERSETZEN (SUBST) Und FINDEN Namensmanager aus Tab Inhaltsverzeichnis und ändern Sie die Formel:


Inhaltsverzeichnis Strg+Alt+F9

REPLACE(GET.WORKBOOK(1),1,FIND("]",GET.WORKBOOK(1));"") &T(TDATE())

Funktion TDATE (JETZT) T TDATE

Um Fehler zu verbergen #REFERENZ (#REF) INDEXIFERROR

HYPERLINK

Methode 3. Makro

Methode 2

Alt+F11 Einfügen - Modul

Sub SheetList() Blatt als Arbeitsblatt abblenden Zelle als Bereich mit ActiveWorkbook für jedes Blatt in ActiveWorkbook.Worksheets abblenden. Cell = Worksheets(1).Cells(sheet.Index, 1) .Worksheets(1).Hyperlinks.Add Anchor:=Cell festlegen , Address:="", SubAddress:=""" & sheet.Name & """ & "!A1" cell.Formula = sheet.Name Next End With End Sub

Schließen Sie den Visual Basic-Editor und kehren Sie zu Excel zurück. Fügen Sie dem Buch ein leeres Blatt hinzu und legen Sie es zuerst ein. Dann klick Alt+F8 oder öffnen Sie das Menü SheetList

Methode 1.

Verwandte Links

  • Was ist ein Makro, wie erstellt man es, wohin kopiert man den Text des Makros, wie führt man das Makro aus?
  • Erstellen Sie automatisch ein Buchinhaltsverzeichnis mit einer Schaltfläche (PLEX-Add-in)
  • Versenden von E-Mails mit der HYPERLINK-Funktion
  • Wechseln Sie schnell zwischen Blättern in einer Excel-Arbeitsmappe

Methode 1: Manuell erstellte Hyperlinks

Fügen Sie ein leeres Blatt in die Arbeitsmappe ein und fügen Sie mit dem Befehl Hyperlinks zu den benötigten Blättern hinzu Hyperlink einfügen. Wählen Sie im sich öffnenden Fenster die Option links aus Im Dokument platzieren und legen Sie die externe Textanzeige und die Adresse der Zelle fest, zu der der Link führen soll:

Der Einfachheit halber können Sie auch auf allen Seiten Ihres Buchs Backlinks erstellen, die zurück zum Inhaltsverzeichnis führen. Um Hyperlinks nicht manuell erstellen und dann auf jedes Blatt kopieren zu müssen, ist es besser, eine andere Methode zu verwenden – die Funktion HYPERLINK. Wir wählen alle Blätter im Buch aus, denen wir einen Backlink hinzufügen möchten (Sie können die Tasten zur Massenauswahl von Blättern verwenden). Schicht und/oder Strg) und in jede geeignete Zelle geben wir eine Funktion der folgenden Form ein:

Diese Funktion erstellt in der aktuellen Zelle auf allen ausgewählten Blättern einen Hyperlink mit dem Text „Zurück zum Inhaltsverzeichnis“. Wenn Sie darauf klicken, gelangen Sie zum Blatt zurück Inhaltsverzeichnis.

Methode 2: Dynamisches Inhaltsverzeichnis mithilfe von Formeln

Dies ist zwar etwas exotisch, aber eine sehr schöne und praktische Möglichkeit, ein automatisches Inhaltsverzeichnis für Ihr Buch zu erstellen. Exotisch – weil es eine undokumentierte XLM-Funktion verwendet GET.WORKBOOK, von den Entwicklern aus Gründen der Kompatibilität mit älteren Excel-Versionen hinterlassen. Diese Funktion speichert eine Liste aller Blätter in der aktuellen Arbeitsmappe in einer bestimmten Variablen, aus der wir sie dann extrahieren und in unserem Inhaltsverzeichnis verwenden können.

Offen Namensmanager auf der Registerkarte Formeln (Formeln – Namensmanager) und erstellen Sie einen neuen benannten Bereich mit dem Namen, sagen wir Inhaltsverzeichnis. Auf dem Feld Bereich (Referenz) Geben Sie diese Formel ein:

GET.WORK.BOOK(1)
=GET.WORKBOOK(1)

Jetzt in der Variablen Inhaltsverzeichnis enthält unsere gesuchten Namen. Um sie von dort auf das Blatt zu extrahieren, können Sie die Funktion verwenden INDEX, das Elemente anhand ihrer Anzahl aus dem Array „herauszieht“:

Funktion REIHE gibt die Nummer der aktuellen Zeile an und wird in diesem Fall nur benötigt, um das manuelle Erstellen einer separaten Spalte mit den Seriennummern der extrahierten Elemente (1,2,3...) zu vermeiden. Somit haben wir in Zelle A1 den Namen des ersten Blattes, in A2 den Namen des zweiten usw.

Nicht schlecht. Wie Sie sehen, gibt die Funktion jedoch nicht nur den Blattnamen zurück, sondern auch den Arbeitsmappennamen, den wir nicht benötigen. Um es zu entfernen, verwenden wir die Funktionen ERSETZEN (SUBST) Und FINDEN, das das schließende eckige Klammerzeichen (]) findet und den gesamten Text bis und einschließlich dieses Zeichens durch die leere Zeichenfolge („“) ersetzt. Öffnen wir es noch einmal Namensmanager aus Tab Formeln (Formeln - Namensmanager), doppelklicken Sie, um den erstellten Bereich zu öffnen Inhaltsverzeichnis und ändern Sie die Formel:

REPLACE(GET.WORKBOOK(1),1,FIND("]",GET.WORKBOOK(1));"")
=SUBST(GET.WORKBOOK(1);1;FIND("]";GET.WORKBOOK(1));"")

Jetzt wird unsere Blattliste viel besser aussehen:

Eine kleine Nebenkomplikation besteht darin, dass unsere Formel in einem benannten Bereich liegt Inhaltsverzeichnis wird nur dann neu berechnet, wenn sie eingegeben wird oder wenn das Buch durch Drücken einer Tastenkombination zur Neuberechnung gezwungen wird Strg+Alt+F9. Um diesen unangenehmen Moment zu umgehen, fügen wir unserer Formel einen kleinen „Schwanz“ hinzu:

REPLACE(GET.WORKBOOK(1),1,FIND("]",GET.WORKBOOK(1));"") &T(TDATE())=SUBST(GET.WORKBOOK(1);1;FIND("]";GET.WORKBOOK(1));"")&T(NOW())

Funktion TDATE (JETZT) gibt das aktuelle Datum (mit Uhrzeit) und die Funktion an T wandelt dieses Datum in eine leere Textzeichenfolge um, die dann mithilfe des Verkettungsoperators (&) mit unserem Blattnamen verkettet wird. Diese. Der Blattname ändert sich eigentlich nicht, sondern seit der Funktion TDATE neu berechnet wird und für jede Änderung im Blatt eine neue Uhrzeit und ein neues Datum erzeugt, wird der Rest unserer Formel ebenfalls zu einer Neuberechnung gezwungen und – als Ergebnis – werden die Namen der Blätter ständig aktualisiert.

Um Fehler zu verbergen #REFERENZ (#REF), die erscheint, wenn wir unsere Formel mit der Funktion kopieren INDEX Für mehr Zellen als wir Blätter haben, können wir die Funktion verwenden IFERROR Dadurch werden alle Fehler abgefangen und durch die leere Zeichenfolge ("") ersetzt:

Und schließlich können Sie dieselbe Funktion verwenden, um „Live“-Hyperlinks zu Blattnamen hinzuzufügen, um eine schnelle Navigation zu ermöglichen HYPERLINK, die aus dem Blattnamen die Adresse für den Übergang bildet:

Methode 3. Makro

Und schließlich können Sie mit einem einfachen Makro ein Inhaltsverzeichnis erstellen. Stimmt, Sie müssen es jedes Mal ausführen, wenn sich die Struktur des Buches ändert – im Gegensatz dazu Methode 2, das Makro selbst verfolgt sie nicht.

Öffnen Sie den Visual Basic Editor, indem Sie auf klicken Alt+F11 oder durch Auswahl (in älteren Excel-Versionen) aus dem Menü Extras – Makro – Visual Basic-Editor(Extras – Makro – Visual Basic Editor). Erstellen Sie im sich öffnenden Editorfenster ein neues leeres Modul (Menü Einfügen - Modul ) und kopieren Sie den Text dieses Makros dorthin:

Sub SheetList()
Dim-Blatt als Arbeitsblatt
Zelle als Bereich dimmen
Mit ActiveWorkbook
Für jedes Blatt in ActiveWorkbook.Worksheets
Setze Zelle = Worksheets(1).Cells(sheet.Index, 1)
.Worksheets(1).Hyperlinks.Anker hinzufügen:=Zelle, Adresse:="", Unteradresse:="'" & Blatt.Name & "'" & "!A1"
cell.Formula = sheet.Name
Nächste
Ende mit
End Sub Schließen Sie den Visual Basic-Editor und kehren Sie zu Excel zurück. Fügen Sie dem Buch ein leeres Blatt hinzu und legen Sie es zuerst ein. Dann klick Alt+F8 oder öffnen Sie das Menü Extras – Makro – Makros. Suchen Sie dort das Makro, das Sie erstellt haben SheetList und führen Sie es aus. Das Makro erstellt eine Liste von Hyperlinks mit Blattnamen auf dem ersten Blatt der Arbeitsmappe. Wenn Sie auf eines davon klicken, gelangen Sie zum gewünschten Blatt.

Der Einfachheit halber können Sie auch Backlinks auf allen Blättern Ihres Buchs erstellen, die zurück zum Inhaltsverzeichnis führen, wie in beschrieben Methode 1.

Auf meine Art. Meine Version

T
Blattname - =IFERROR(REPLACE(INDEX(Table of Contents,ROW()-3),1,FIND("]",INDEX(Table of Contents,ROW()-3));"");"")

Datum - =IFERROR(IF(INDIRECT("'"&B4&"'"&"!A1″)=0;"";INDIRECT("'"&B4&"'"&"!A1″));"")

Name - =INDIRECT(“‘“&B4&“‘“&“!A3″)

PO - =INDIRECT(“‘“&B4&“‘“&“!E5″)

Lohnsteuer - =INDIRECT(“‘“&B4&“‘“&“!E6″)

Abschreibung - =INDIRECT(“‘“&B4&“‘“&“!E7″)

Materialien - =INDIRECT(“‘“&B4&“‘“&“!E8″)
vsp-Materialien - =INDIRECT(“‘“&B4&“‘“&“!E9″)

INDIREKT("'"&B4&"'"&"!E10″)

INDIRECT("'"&B4&"'"&"!E11″)=INDIRECT("'"&B4&"'"&"!E12″)=INDIRECT("'"&B4&"'"&"!E13″)=INDIRECT( "'"&B4&"'"&"!E18″)=INDIRECT("'"&B4&"'"&"!E19″)

Wenn die Anzahl der Blätter in Ihrem Buch zunimmt und die Navigation dadurch problematisch wird, schlage ich vor, ein Inhaltsverzeichnis für das Buch mit Links zu den erforderlichen Blättern zu erstellen.

Legen Sie ein leeres Blatt in das Buch ein

Wählen Sie im Fenster „Hyperlink einfügen“ aus, womit der Hyperlink verknüpft werden soll: „Link zu einer Stelle im Dokument“. Zellenadresse – zu welcher Zelle des Blattes der Cursor bewegt wird. und wählen Sie das Ortsblatt Singapur aus. Geben Sie im Feld „Text“ den Namen des Blattes an. Klicken Sie nach Auswahl der Parameter auf OK.

Der Text in der Zelle hat sein Aussehen geändert. Das bedeutet, dass ein Hyperlink dafür erstellt wurde. Lassen Sie uns auf die gleiche Weise Hyperlinks zu anderen Blättern im Buch einrichten. Bitte beachten Sie, dass der zur Navigation verwendete Hyperlink seine Farbe ändert.

Der Einfachheit halber können Sie auch auf allen Seiten Ihres Buchs Backlinks erstellen, die zurück zum Inhaltsverzeichnis führen. Um Hyperlinks nicht manuell erstellen und dann auf jedes Blatt kopieren zu müssen, ist es besser, eine andere Methode zu verwenden – die HYPERLINK-Funktion.

Wir wählen alle Blätter im Buch aus, denen wir einen Backlink hinzufügen möchten (um Blätter massenhaft auszuwählen, müssen Sie die Strg-Taste gedrückt halten und die gewünschten Blätter mit der Maus auswählen) und geben die folgende Funktion in eine beliebige geeignete Zelle ein:

Diese Funktion erstellt in der aktuellen Zelle auf allen ausgewählten Blättern einen Hyperlink mit dem Text „Zurück zum Inhaltsverzeichnis“. Wenn Sie darauf klicken, gelangen Sie zum Blatt „Inhaltsverzeichnis“.

So erstellen Sie eine Schaltfläche in einem Menü:

Um das Menü optisch ansprechender zu gestalten, fügen wir Schaltflächen hinzu

Erstellen wir zunächst die Form der zukünftigen Schaltfläche: Einfügen → Formen → Beliebige Form auswählen:

Lassen Sie uns den Text innerhalb der Form drucken. So haben wir den Knopf gezeichnet.

Wählen Sie die Form aus → gehen Sie zur Registerkarte Einfügen → Hyperlink. Weisen Sie ihm anschließend wie im ersten Absatz Parameter zu und klicken Sie auf OK. Ebenso können Sie andere Hyperlink-Schaltflächen auf verschiedenen Blättern des Buchs erstellen. Und fügen Sie mit dem FORMAT-Menü ein wenig Kreativität hinzu

Microsoft Excel eignet sich zum Erstellen von Tabellen und zum Durchführen von Berechnungen. Ein Arbeitsbereich besteht aus einer Reihe von Zellen, die mit Daten gefüllt werden können. Anschließend formatieren und zum Erstellen von Diagrammen, Diagrammen und zusammenfassenden Berichten verwenden.

Die Arbeit in Excel mit Tabellen für unerfahrene Benutzer mag auf den ersten Blick schwierig erscheinen. Es unterscheidet sich erheblich von den Prinzipien der Tabellenerstellung in Word. Aber wir fangen klein an: mit der Erstellung und Formatierung einer Tabelle. Und am Ende des Artikels werden Sie bereits verstehen, dass Sie sich kein besseres Tool zum Erstellen von Tabellen als Excel vorstellen können.

So erstellen Sie eine Tabelle in Excel für Dummies

Das Arbeiten mit Tabellen in Excel für Dummies ist nicht überstürzt. Sie können eine Tabelle auf unterschiedliche Weise erstellen, und für bestimmte Zwecke hat jede Methode ihre eigenen Vorteile. Lassen Sie uns daher zunächst die Situation visuell beurteilen.

Schauen Sie sich das Arbeitsblatt der Tabellenkalkulation genau an:

Dies ist eine Reihe von Zellen in Spalten und Zeilen. Im Wesentlichen ein Tisch. Spalten werden in lateinischen Buchstaben angegeben. Linien sind Zahlen. Wenn wir dieses Blatt ausdrucken, erhalten wir eine leere Seite. Ohne Grenzen.

Lassen Sie uns zunächst lernen, wie man mit Zellen, Zeilen und Spalten arbeitet.



So wählen Sie eine Spalte und Zeile aus

Um die gesamte Spalte auszuwählen, klicken Sie mit der linken Maustaste auf ihren Namen (lateinischer Buchstabe).

Um eine Zeile auszuwählen, verwenden Sie den Zeilennamen (nach Nummer).

Um mehrere Spalten oder Zeilen auszuwählen, klicken Sie mit der linken Maustaste auf den Namen, halten Sie die Maustaste gedrückt und ziehen Sie.

Um eine Spalte mit Hotkeys auszuwählen, platzieren Sie den Cursor in einer beliebigen Zelle der gewünschten Spalte – drücken Sie Strg + Leertaste. Um eine Zeile auszuwählen – Umschalt + Leertaste.

So ändern Sie Zellränder

Passen die Angaben beim Ausfüllen der Tabelle nicht, müssen Sie die Zellränder ändern:

Um die Breite der Spalten und die Höhe der Zeilen gleichzeitig in einem bestimmten Bereich zu ändern, wählen Sie einen Bereich aus, erhöhen Sie 1 Spalte/Zeile (manuell verschieben) – die Größe aller ausgewählten Spalten und Zeilen ändert sich automatisch.


Notiz. Um zur vorherigen Größe zurückzukehren, können Sie auf die Schaltfläche „Abbrechen“ oder die Tastenkombination STRG+Z klicken. Aber es funktioniert, wenn man es sofort macht. Später wird es nicht helfen.

Um die Linien wieder an ihre ursprünglichen Grenzen anzupassen, öffnen Sie das Werkzeugmenü: „Startseite“ – „Format“ und wählen Sie „Linienhöhe automatisch anpassen“.

Diese Methode ist für Spalten nicht relevant. Klicken Sie auf „Format“ – „Standardbreite“. Erinnern wir uns an diese Zahl. Wählen Sie eine beliebige Zelle in der Spalte aus, deren Ränder „zurückgegeben“ werden müssen. Wieder „Format“ – „Spaltenbreite“ – geben Sie den vom Programm vorgegebenen Indikator ein (normalerweise 8,43 – die Anzahl der Zeichen in der Schriftart Calibri mit einer Größe von 11 Punkt). OK.

So fügen Sie eine Spalte oder Zeile ein

Wählen Sie die Spalte/Zeile rechts/unter der Stelle aus, an der Sie den neuen Bereich einfügen möchten. Das heißt, die Spalte wird links von der ausgewählten Zelle angezeigt. Und die Linie ist höher.

Klicken Sie mit der rechten Maustaste und wählen Sie „Einfügen“ aus dem Dropdown-Menü (oder drücken Sie die Tastenkombination STRG+UMSCHALT+"=").

Markieren Sie die „Spalte“ und klicken Sie auf „OK“.

Beratung. Um eine Spalte schnell einzufügen, wählen Sie die Spalte an der gewünschten Stelle aus und drücken Sie STRG+UMSCHALT+"=".

All diese Fähigkeiten werden sich beim Erstellen einer Tabelle in Excel als nützlich erweisen. Wir müssen die Grenzen erweitern und im Laufe der Arbeit Zeilen/Spalten hinzufügen.

Schritt-für-Schritt-Erstellung einer Tabelle mit Formeln

Spalten- und Zeilenränder sind jetzt beim Drucken sichtbar.

Mit dem Menü „Schriftart“ können Sie Excel-Tabellendaten wie in Word formatieren.

Ändern Sie beispielsweise die Schriftgröße, machen Sie die Überschrift „fett“. Sie können den Text zentrieren, Bindestriche zuweisen usw.

So erstellen Sie eine Tabelle in Excel: Schritt-für-Schritt-Anleitung

Der einfachste Weg, Tabellen zu erstellen, ist bereits bekannt. Excel bietet jedoch eine komfortablere Option (hinsichtlich der nachträglichen Formatierung und der Arbeit mit Daten).

Lassen Sie uns eine „intelligente“ (dynamische) Tabelle erstellen:

Notiz. Sie können auch einen anderen Weg einschlagen: Wählen Sie zunächst einen Zellbereich aus und klicken Sie dann auf die Schaltfläche „Tabelle“.

Geben Sie nun die notwendigen Daten in den fertigen Rahmen ein. Wenn Sie eine zusätzliche Spalte benötigen, platzieren Sie den Cursor in der für den Namen vorgesehenen Zelle. Geben Sie den Namen ein und drücken Sie ENTER. Der Bereich wird automatisch erweitert.


Wenn Sie die Anzahl der Zeilen erhöhen müssen, haken Sie es in der unteren rechten Ecke an die Markierung für das automatische Ausfüllen an und ziehen Sie es nach unten.

So arbeiten Sie mit einer Tabelle in Excel

Mit der Veröffentlichung neuer Versionen des Programms ist die Arbeit mit Tabellen in Excel interessanter und dynamischer geworden. Wenn eine intelligente Tabelle auf einem Blatt erstellt wird, wird das Werkzeug „Arbeiten mit Tabellen“ – „Design“ verfügbar.

Hier können wir der Tabelle einen Namen geben und ihre Größe ändern.

Es stehen verschiedene Stile zur Verfügung, darunter die Möglichkeit, die Tabelle in einen regulären Bereich oder einen zusammenfassenden Bericht umzuwandeln.

Funktionen dynamischer MS Excel-Tabellen riesig. Beginnen wir mit den grundlegenden Fähigkeiten zur Dateneingabe und zum automatischen Ausfüllen:

Wenn wir auf den Pfeil rechts neben jeder Überschrift klicken, erhalten wir Zugriff auf zusätzliche Tools für die Arbeit mit Tabellendaten.

Manchmal muss der Benutzer mit riesigen Tabellen arbeiten. Um die Ergebnisse zu sehen, müssen Sie durch mehr als tausend Zeilen scrollen. Das Löschen von Zeilen ist keine Option (die Daten werden später benötigt). Aber man kann es verstecken. Nutzen Sie dazu numerische Filter (Bild oben). Deaktivieren Sie die Kontrollkästchen neben den Werten, die ausgeblendet werden sollen.

So erstellen Sie ein Inhaltsverzeichnis in Excel, wenn es keine Sonderfunktion gibt? Sie können die Hyperlink-Funktion in Excel verwenden.

Wir haben zum Beispiel einen riesigen Tisch mit mehreren Blättern. Auf der ersten Seite erstellen wir ein Inhaltsverzeichnis dieses Buches. Die zweite und die folgenden Seiten sind Tabellen verschiedener Abteilungen (Verkaufsabteilung, Einkaufsabteilung usw.). Etwas in der Art (im Beispiel sind die Tabellen klein).

Das Prinzip der Zusammenstellung von Inhalten besteht darin, Hyperlinks zu erstellen.

Aber, Es gibt einen Trick. Wir werden Hyperlinks nicht zu Zellen erstellen, sondern zu Zellname, Abteilungsname. Wenn wir einen Hyperlink zu einer Zelle erstellen und über dieser Zelle eine Zeile einfügen, funktioniert der Hyperlink nicht, er wird verschoben.

Daher weisen wir auf jeder Seite den Zellen Namen mit den Namen der Abteilungen zu – „Verkaufsabteilung“, „Einkaufsabteilung“. Wenn die Tabellen lang sind, können Sie den Abteilungen Namen zuweisen – „1. Abteilung“, „2. Abteilung“. Vergessen Sie bei der Namensvergabe nicht, das Blatt anzugeben, auf dem sich dieser Name befindet. Wie Sie einer Zelle einen Namen zuweisen, erfahren Sie im Artikel „Zuweisen eines Namens zu einer Zelle, einem Bereich oder einer Formel in Excel“. Alle Namen sind im Namensmanager sichtbar.


Wenn wir nun eine Zeile in die Tabelle einfügen, ändert sich die Adresse der Zelle mit dem Namen, d. h. Der Name ist an den Abteilungsnamen gebunden.

Auf der ersten Seite erstellen wir ein Inhaltsverzeichnis aus den Namen der Abteilungen und Abteilungen.

Jetzt müssen nur noch Hyperlinks zu diesen Wörtern erstellt werden. Wie das geht, erfahren Sie im Artikel „So erstellen Sie einen Hyperlink in Excel“. Wir stehen auf der Zelle mit dem Namen, zu dem wir einen Hyperlink erstellen möchten. Wählen Sie im Kontextmenü die Funktion „Hyperlink“. Wählen Sie im Dialogfenster den gewünschten Namen aus der Liste aus. OK klicken". Das war's, der Link ist fertig.

Das war's, das Inhaltsverzeichnis ist fertig. Nun können Sie schnell zur gewünschten Seite im gewünschten Unterbereich gelangen.

Der Link zum Blatt ist derselbe wie in Zelle A2. Aber in der Bearbeitungsleiste können Sie den Namen beispielsweise wie in Zelle A3 korrigieren – das Ausrufezeichen und die Zellenadresse wurden entfernt. Wenn die Blätter unterschiedlich beschriftet sind, werden diese Blattnamen in der Liste angezeigt. Siehe Zelle A4.

Kann auf jedem Blatt des Buches durchgeführt werden Hyperlink zurück zum Inhaltsverzeichnis des BuchesExcel.

Zunächst vergeben wir mit der Funktion „Namen vergeben“ einen Namen für das Inhaltsverzeichnis. Beispiel: „Inhaltsverzeichnis“. Das Inhaltsverzeichnis befindet sich auf der ersten Seite. Auf dem zweiten Blatt erstellen wir einen Hyperlink zum ersten Blatt „Inhaltsverzeichnis“.

Kopieren Sie nun diesen Hyperlink. Gehen Sie zu Blatt 3, drücken Sie die „Strg“-Taste, halten Sie sie gedrückt und klicken Sie mit der linken Maustaste auf die Beschriftungen aller Blätter des Buches, in denen Sie einen Link zum Inhaltsverzeichnis setzen müssen. Also haben wir alle Blätter auf einmal ausgewählt.

Fügen Sie nun den Hyperlink in die gewünschte Blattzelle ein. Das war's, der Link „Inhaltsverzeichnis“ ist auf allen ausgewählten Blättern erschienen. Es bleibt nur noch, die Gruppierung der Blätter aufzuheben. Klicken Sie dazu mit der rechten Maustaste auf eine beliebige ausgewählte Verknüpfung und wählen Sie im Kontextmenü die Funktion „Blättergruppierung aufheben“.

Lassen Sie uns die Formeln analysieren Blatt- und Seitenzahlen in Excel(sowohl Standard- als auch benutzerdefinierte Funktionen) zum Erstellen eines Inhaltsverzeichnisses und zur bequemen Navigation durch das Dokument.

Beim Erstellen eines Inhaltsverzeichnisses für einen Bericht, ein Buch oder einen Artikel ist es für uns wichtig, nicht nur die Namen der Kapitel zu kennen und Hyperlinks zu ihnen einzufügen, sondern auch deren Position im Dokument, damit das Lesen einfacher ist Dies erleichtert uns die Navigation durch den Inhalt des Dokuments und erleichtert das Auffinden der benötigten Informationen.
Sie können Blattnummern manuell zum Inhaltsverzeichnis hinzufügen, aber in diesem Fall kann das Hinzufügen von Nummern ziemlich viel Zeit in Anspruch nehmen. Wenn Sie außerdem die Seitenzahl eines Kapitels ändern, ändern sich auch die Nummern aller nachfolgenden Kapitel, sodass diese Methode nicht als bequem und effektiv bezeichnet werden kann.

Betrachten wir Optionen, mit denen wir die Anzahl der Blätter und Seiten automatisch ermitteln können.
Lassen Sie uns jedoch zunächst verstehen, was genau der Unterschied zwischen einem Blatt und einer Seite in Excel ist.
Eine Excel-Tabelle ist eine Tabelle, die aus Zellen besteht, die als Daten in Zeilen und Spalten strukturiert sind, und eine Seite ist eine druckbare Darstellung der Tabelle. Mit anderen Worten: Abhängig von Ihren Druckeinstellungen Ein Blatt kann mehrere Seiten enthalten, also werden wir das Problem lösen, Blatt- und Seitenzahlen getrennt zu finden.

Blattnummernformel in Excel

Beginnend mit Excel 2013, einem Standard SHEET-Funktion, das die Blattnummer aus dem vorhandenen Link zurückgibt.
Allerdings gibt es in früheren Versionen von Excel leider keine Standardfunktionen, daher nutzen wir in diesem Fall die Fähigkeiten von VBA und schreiben eine benutzerdefinierte Funktion, die ihre Seriennummer basierend auf dem Blattnamen zurückgibt.

SHEET-Funktion (ab Excel 2013)

Syntax und Beschreibung der Funktion:

BLATT (Wert)
Gibt die Nummer des angegebenen Blattes zurück.

  • Bedeutung (optionales Argument)— Name des Blattes oder Link, für den Sie die Blattnummer herausfinden müssen.

Wenn das Argument nicht angegeben ist, gibt die Funktion standardmäßig die Nummer des Blattes zurück, auf dem die Funktion angegeben ist.
Schauen wir uns die Prinzipien der Verwendung der Funktion anhand von Beispielen an:

Beachten Sie beim Arbeiten mit einer Funktion, dass die Formeln =BLATT("Blatt2") Und =BLATT(A1)(In diesem Fall enthält Zelle A1 beispielsweise den Text „Blatt2“) kann unterschiedliche Ergebnisse zurückgeben, da im ersten Fall das Argument als Text dargestellt wird (Suche nach einem Blatt anhand des Textnamens) und im zweiten Fall als ein Link (suchen Sie nach einem Blatt anhand der verweisenden Zelle).

Blattnummer in VBA

Um eine benutzerdefinierte Funktion zu erstellen, gehen Sie zum Editor (Alt + F11), erstellen Sie dann ein neues Modul und fügen Sie den folgenden Code hinzu:

Visual Basic

Funktion SheetNumber(SheetName As String) As Integer „SheetNumber-Funktion (SheetName-Argument als Text) gibt ganzzahlige Werte zurück SheetNumber = Worksheets(SheetName).Index „setzt die Blattindexnummer mit dem Rückgabewert gleich End Function

Jetzt können wir diese Funktion auf eine für uns bequeme Weise verwenden – entweder geben Sie die Formel direkt in eine beliebige Zelle ein oder verwenden Sie den Funktionsassistenten (die Funktion finden Sie in der Kategorie). Benutzerdefinierte):

Seitenzahl in VBA

Sie können Kopf- und Fußzeilen verwenden, aber wenn wir einer Zelle auf einem Blatt eine Seitenzahl hinzufügen möchten, ist diese Methode nicht geeignet. Lassen Sie uns den Blattparameter verwenden PageSetup.Pages.Count, die die Anzahl der Seiten eines bestimmten Blatts bestimmt, die gedruckt werden sollen.
Der Algorithmus zur Suche nach einer Seitenzahl lautet wie folgt: Für alle Blätter, die sich zwischen zwei bestimmten Blättern befinden, summieren wir die Anzahl der darauf befindlichen Seiten.
Wir kehren zum Visual Basic-Editor zurück und fügen dem Modul eine neue Funktion hinzu:

Visual Basic

Funktion PageNumber(SheetName1 As String, SheetName2 As String) As Integer „PageNumber-Funktion (Argumente SheetName1 und SheetName2 als Text) gibt ganzzahlige Werte zurück. Dim FirstPage As Integer, LastPage As Integer „Initialisierung von Application.Volatile True-Variablen“ automatische Neuberechnung von PageNumber = 0 " Rückgabewert = 0 FirstPage = Worksheets(SheetName1).Index "ruft die Startblattnummer ab. LastPage = Worksheets(SheetName2).Index "ruft die Endblattnummer ab. For i = FirstPage To LastPage - 1 "durchläuft Blätter PageNumber = PageNumber + Sheets(i) .PageSetup.Pages.Count "füge die Anzahl der Seiten des aktuellen Blattes zum Rückgabewert der Next i End Function hinzu

Funktion Seitennummer (Blattname1 als Zeichenfolge, Blattname2 als Zeichenfolge) als Ganzzahl „Funktion PageNumber (Argumente SheetName1 und SheetName2 als Text), die ganzzahlige Werte zurückgibt

Dimmen Sie FirstPage als Ganzzahl, LastPage als Ganzzahl „Variablen initialisieren

Anwendung. Volatil Wahr „Automatische Neuberechnung

Seitennummer = 0 „Rückgabewert = 0

FirstPage = Worksheets(SheetName1) . Index „Ermitteln der Startblattnummer

LastPage = Worksheets(SheetName2) . Index "Abrufen der Endblattnummer

Als nächstes i

Funktion beenden

Daher sucht die resultierende Funktion nach der Anzahl der Seiten zwischen zwei Blättern, die als Argumente angegeben werden.
Kehren wir zu Excel zurück und geben eine neue Formel zum Zählen der Seitenzahlen ein:


Gleichzeitig haben wir auf den Blättern „Teil 3“ und „Teil 4“ (aus dem vorherigen Beispiel) zusätzlich Daten hinzugefügt, sodass das Blatt mehrere Seiten umfasste.

Der Nachteil dieser Möglichkeit zur Ermittlung der Seitenzahl ist leider die Geschwindigkeit der Funktion.
Bei der Ausführung der Operation PageSetup.Pages.Count greift Excel für jede Seite auf die Druckeinstellungen des Druckers zu, was bei großen Büchern zu einer langen Berechnungszeit der Funktion führt.

Berechnungsfunktionen

Da die Funktionen Seitenzahl Und Seitennummer hängen nur von Textvariablen ab. Wenn sich die Anzahl der Blätter oder Seiten im Buch ändert, werden sie nicht automatisch neu berechnet.
Um das Neuberechnungsproblem teilweise zu vermeiden, haben wir den Befehl zum Funktionscode hinzugefügt (3. Zeile). Application.Volatile True, das das Ergebnis der Funktion neu berechnet, wenn sich der Inhalt der Zellen ändert (analog zur vollständigen Neuberechnung von Formeln durch Drücken von F9).
Wenn die Funktion die Arbeit immer noch erheblich verlangsamt, können Sie diese Zeile aus dem Code ausschließen. Vergessen Sie in diesem Fall jedoch nicht, sicherzustellen, dass die Formeln in der endgültigen Version des Dokuments neu berechnet werden.



 

Es könnte nützlich sein zu lesen: