Verwenden von strukturierten Verweisen für Excel-Tabellen

Was passiert, wenn ich explizite Zellbezüge verwende?

Wenn Sie explizite Zellbezüge in eine berechnete Spalte eingeben, ist es möglicherweise schwieriger zu sehen, was mit der Formel berechnet wird.

1. Wählen Sie in Ihrem Beispielarbeitsblatt Zelle E2 aus.

2. Geben Sie in der Bearbeitungsleiste =C2*D2 ein, und drücken Sie die EINGABETASTE.

Wie Sie sehen, kopiert Excel die Formel in der Spalte nach unten; es werden keine strukturierten Verweise verwendet. Wenn Sie beispielsweise zwischen den vorhandenen Spalten C und D eine Spalte einfügen, müssen Sie die Formel anpassen.

Wie ändere ich einen Tabellennamen?

Wenn Sie eine Tabelle einfügen, wird in Excel ein Standardtabellenname (Tabelle1, Tabelle2 usw.) erstellt, den Sie jedoch ändern können, um ihn aussagekräftiger zu gestalten.

1. Wählen Sie eine beliebige Zelle in der Tabelle aus, um die Registerkarte Tabellenentwurf im Menüband anzuzeigen.

2. Geben Sie den gewünschten Namen in das Feld Tabellenname ein, und drücken Sie EINGABETASTE.

In den Beispieldaten wurde der Name DeptSalesverwendet.

Wenden Sie für Tabellennamen die folgenden Regeln an:

• Gültige Zeichen verwenden  Beginnen Sie einen Namen immer mit einem Buchstaben, einem Unterstrich (_) oder einem umgekehrten Schrägstrich (\). Verwenden Sie Buchstaben, Ziffern, Punkte und Unterstriche für den restlichen Namen. Die Buchstaben "C", "c", "R" oder "r" können nicht für den Namen verwendet werden, da sie bereits als Tastenkürzel zur Auswahl der Spalte oder Zeile für die aktive Zelle bei Eingabe im Feld Name oder Gehe zu festgelegt sind.

• Keine Zellbezüge verwenden  Namen dürfen nicht mit einem Zellbezug identisch sein, z. B. Z$100 oder Z1S1.

• Kein Leerzeichen zum Trennen von Wörtern verwenden  Leerzeichen können’im Namen nicht verwendet werden. Sie können den Unterstrich verwenden (_) sowie den Punkt (.) als Worttrennzeichen. Beispielsweise "DeptSales", "Sales_Tax" oder "First.Quarter".

• Nicht mehr als 255 Zeichen verwenden Ein Tabellenname kann bis zu 255 Zeichen umfassen.

• Eindeutige Tabellennamen verwenden Doppelte Namen sind nicht zulässig. In Excel wird nicht zwischen Groß- und Kleinbuchstaben in Namen unterschieden. Wenn Sie also "Umsatz" eingeben und bereits der Name "UMSATZ" in derselben Arbeitsmappe vorhanden ist, werden Sie aufgefordert, einen eindeutigen Namen zu wählen.

• Verwenden eines Objektbezeichners  Wenn Sie planen, eine Mischung aus Tabellen, PivotTables und Diagrammen zu verwenden, ist es ratsam, den Namen mit dem Objekttyp zu beginnen. Beispiel: tbl_Sales für eine Verkaufstabelle, pt_Sales für eine Vertriebs-PivotTable und chrt_Sales für ein Verkaufsdiagramm oder ptchrt_Sales für ein Vertriebs-PivotChart. Dadurch werden alle Namen in einer geordneten Liste im Namens-Manager beibehalten.

Syntaxregeln für strukturierte Verweise

Sie können strukturierte Verweise in der Formel manuell eingeben oder ändern, doch dabei ist es hilfreich, die Syntax eines strukturierten Verweises zu verstehen. Sehen Sie sich als Beispiel die folgende Formel an:

=SUMME(DeptSales[[#Totals],[Sales Amount]],DeptSales[[#Data],[Commission Amount]])

Diese Formel weist die folgenden Komponenten von strukturierten Verweisen auf:

• Tabellenname:    DeptSalesist ein benutzerdefinierter Tabellenname. Er verweist auf die Tabellendaten ohne Überschriften- oder Summenzeilen. Sie können einen Standardtabellennamen wie etwa "Tabelle1" verwenden oder ihn in einen benutzerdefinierten Namen ändern.

• Spaltenbezeichner:    [Sales Amount] und [Commission Amount] sind Spaltenbezeichner, die die Namen der Spalten verwenden, die sie darstellen. Sie verweisen auf die Spaltendaten ohne Spaltenüberschrift oder Summenzeile. Schließen Sie Bezeichner immer wie gezeigt in Klammern ein.

• Elementbezeichner:    [#Totals] und [#Data] sind Bezeichner für besondere Elemente, die auf bestimmte Teile der Tabelle wie etwa die Summenzeile verweisen.

• Tabellenbezeichner:    [[#Totals],[Sales Amount]] und [[#Data],[Commission Amount]] sind Tabellenbezeichner, die die äußeren Anteile des strukturierten Verweises darstellen. Äußere Verweise folgen auf den Tabellennamen und werden in eckige Klammern eingeschlossen.

• Strukturierter Verweis:    (DeptSales[[#Totals],[Sales Amount]] und DeptSales[[#Data],[Commission Amount]] sind strukturierte Verweise, die durch eine Zeichenfolge dargestellt werden, die mit dem Tabellennamen beginnt und mit dem Spaltenbezeichner endet.

Verwenden Sie zum manuellen Erstellen oder Bearbeiten strukturierter Verweise die nachfolgenden Syntaxregeln:

• Bezeichner in Klammern setzen    Alle Bezeichner für Tabellen, Spalten und besondere Elemente müssen in einander entsprechenden Klammern angegeben werden ([ ]). Für einen Bezeichner, der andere Bezeichner enthält, sind äußere Klammern erforderlich, mit denen die inneren Klammern der anderen Bezeichner umschlossen werden. Beispielsweise: =DeptSales[[Sales Person]:[Region]]

• Alle Spaltenüberschriften sind Textzeichenfolgen    Sie müssen jedoch nicht in Anführungszeichen gesetzt werden, wenn sie in einem strukturierten Verweis verwendet werden. Zahlen oder Datumsangaben, z. B. 2014 oder 1.1.2014, werden ebenfalls als Textzeichenfolgen betrachtet. Sie können keine Ausdrücke mit Spaltenüberschriften verwenden. Beispielsweise kann der Ausdruck DeptSalesFYSummary[[2014]:[2012]] nicht verwendet werden.

Spaltenüberschriften mit Sonderzeichen in Klammern setzen    Wenn Sonderzeichen vorhanden sind, muss die gesamte Spaltenüberschrift in Klammern gesetzt werden, d. h., dass bei einem Spaltenbezeichner doppelte Klammern erforderlich sind. Beispiel: =DeptSalesFYSummary[[Total $ Amount]]

Es folgt eine Liste der Sonderzeichen, für die zusätzliche Klammern in der Formel erforderlich sind:

• TAB

• Zeilenvorschub

• Wagenrücklauf

• Komma (,)

• Doppelpunkt (:)

• Punkt (.)

• Linke eckige Klammer ([)

• Rechte eckige Klammer (])

• Nummernzeichen (#)

• Einfaches Anführungszeichen (')

• Doppeltes Anführungszeichen (")

• Linke geschweifte Klammer ({)

• Rechte geschweifte Klammer (})

• Dollarzeichen ($)

• Caretzeichen (^)

• Kaufmännisches Und-Zeichen (&)

• Sternchen (*)

• Pluszeichen (+)

• Gleichheitszeichen (=)

• Minuszeichen (-)

• Größer-als-Zeichen (>)

• Kleiner-als-Zeichen (<)

• Divisionszeichen (/)

• Bei Vorzeichen (@)

• Umgekehrter Schrägstrich (\)

• Ausrufezeichen (!)

• Linke Klammer (()

• Rechte Klammer ())

• Prozentzeichen (%)

• Fragezeichen (?)

• Rückstau (')

• Semikolon (;)

• Tilde (~)

• Unterstrich (_)

• Escapezeichen für einige Sonderzeichen in Spaltenüberschriften verwenden    Einige Zeichen haben eine besondere Bedeutung und erfordern die Verwendung eines einfachen Anführungszeichens (') als Escapezeichen. Beispiel: =DeptSalesFYSummary['#OfItems]

Es folgt eine Liste der Sonderzeichen, für die ein Escapezeichen (') in der Formel erforderlich ist:

• Linke eckige Klammer ([)

• Rechte eckige Klammer (])

• Nummernzeichen (#)

• Einfaches Anführungszeichen (')

• Bei Vorzeichen (@)

Leerzeichen zur besseren Lesbarkeit eines strukturierten Verweises verwenden    Mit Leerzeichen können Sie die Lesbarkeit eines strukturierten Verweises verbessern. Beispiel: =DeptSales[ [Sales Person]:[Region] ] oder =DeptSales[[#Headers], [#Data], [% Commission]]

Die Verwendung eines Leerzeichens wird an folgenden Stellen empfohlen:

• Nach der ersten eckigen Klammer links ([)

• vor der letzten eckigen Klammer rechts (])

• Nach einem Komma.

Bezugsoperatoren

Mit den folgenden Bezugsoperatoren können Sie Spaltenbezeichner kombinieren und so Zellbereiche flexibler angeben.

Bezeichner für besondere Elemente

Zum Verweisen auf bestimmte Teile einer Tabelle, z. B. die Summenzeile, können Sie einen der folgenden Bezeichner für besondere Elemente in Ihren strukturierten Verweisen verwenden.

Qualifizieren von strukturierten Verweisen in berechneten Spalten

Wenn Sie eine berechnete Spalte erstellen, verwenden Sie häufig einen strukturierten Verweis, um die Formel zu erstellen. Dieser strukturierte Verweis kann entweder nicht qualifiziert oder vollständig qualifiziert sein. Um beispielsweise die berechnete Spalte namens "Commission Amount" (Provisionsbetrag) zu erstellen, die den Betrag der Provision in Dollar berechnet, können Sie die folgenden Formeln verwenden:

Dabei gilt folgende allgemeine Regel: Wenn Sie strukturierte Verweise innerhalb einer Tabelle verwenden, um zum Beispiel eine berechnete Spalte zu erstellen, können Sie einen nicht qualifizierten strukturierten Verweis verwenden. Wenn Sie den strukturierten Verweis jedoch außerhalb der Tabelle verwenden, dann müssen Sie einen vollständig qualifizierten strukturierten Verweis verwenden.

Beispiele für die Verwendung strukturierter Verweise

Hier einige Möglichkeiten zur Verwendung von strukturierten Verweisen.

Strategien für das Arbeiten mit strukturierten Verweisen

Berücksichtigen Sie beim Arbeiten mit strukturierten Verweisen folgende Punkte.

• AutoVervollständigen für Formeln verwenden    AutoVervollständigen für Formeln ist dann sehr hilfreich, wenn Sie strukturierte Verweise eingeben und die Verwendung der richtigen Syntax sicherstellen möchten. Weitere Informationen finden Sie unter Verwenden von AutoVervollständigen für Formeln.

• Entscheiden, ob strukturierte Verweise für Tabellen in Halbauswahl generiert werden    Wenn Sie eine Formel erstellen, werden beim Auswählen eines Zellbereichs innerhalb einer Tabelle standardmäßig die Zellen halb markiert und automatisch ein strukturierter Bezug anstelle des Zellbereichs in die Formel eingegeben. Dieses Verhalten der Halbauswahl erleichtert die Eingabe eines strukturierten Verweises. Sie können dieses Verhalten aktivieren oder deaktivieren, indem Sie das Kontrollkästchen Tabellennamen in Formeln verwenden im Dialogfeld Datei > Optionen > Formeln > Arbeiten mit Formeln aktivieren oder deaktivieren.

• Arbeitsmappen mit externen Links zu Excel-Tabellen in anderen Arbeitsmappen verwenden    Wenn eine Arbeitsmappe einen externen Link zu einer Excel-Tabelle in einer anderen Arbeitsmappe enthält, muss diese verknüpfte Quellarbeitsmappe in Excel geöffnet sein, um #REF!-Fehler zu vermeiden in der Zielarbeitsmappe, die die Links enthält. Wenn Sie die Zielarbeitsmappe zuerst öffnen und #REF!-Fehler angezeigt werden, werden diese behoben, wenn Sie dann die Quellarbeitsmappe öffnen. Wenn Sie die Quellarbeitsmappe zuerst öffnen, sollten keine Fehlercodes angezeigt werden.

• Bereich in eine Tabelle oder Tabelle in einen Bereich konvertieren    Wenn Sie eine Tabelle in einen Bereich konvertieren, ändern sich alle Zellbezüge in ihre entsprechenden absoluten A1-Formatvorlagenverweise. Wenn Sie einen Bereich in eine Tabelle konvertieren, ändert Excel nicht automatisch alle Zellverweise dieses Bereichs in ihre entsprechenden strukturierten Verweise.

• Deaktivieren von Spaltenüberschriften    Sie können Tabellenspaltenüberschriften auf der Registerkarte Tabellenentwurf > Kopfzeile ein- und ausschalten. Wenn Sie Tabellenspaltenüberschriften deaktivieren, sind strukturierte Verweise, die Spaltennamen verwenden, nicht betroffen, und Sie können sie weiterhin in Formeln verwenden. Strukturierte Verweise, die direkt auf die Tabellenüberschriften verweisen (z. B. =DeptSales[[#Headers],[%Commission]]), führen zum Fehler #REF.

• Spalten und Zeilen in einer Tabelle hinzufügen oder löschen    Da Tabellendatenbereiche häufig geändert werden, werden die Zellverweise für strukturierte Verweise automatisch angepasst. Wenn Sie beispielsweise in einer Formel einen Tabellennamen verwenden, um alle Datenzellen in einer Tabelle zu zählen und dann eine Datenzeile hinzufügen, wird der Zellbezug automatisch angepasst.

• Tabelle oder Spalte umbenennen    Wenn Sie eine Spalte oder eine Tabelle umbenennen, wird in Excel automatisch in allen in der Arbeitsmappe verwendeten strukturierten Verweisen die Verwendung der Tabelle und der Spaltenüberschriften geändert.

• Strukturierte Verweise verschieben, kopieren und füllen    Alle strukturierten Verweise bleiben gleich, wenn Sie eine Formel, die einen strukturierten Verweis enthält, kopieren oder verschieben.

  Hinweis: Das Kopieren eines strukturierten Verweises und das Ausfüllen eines strukturierten Verweises sind nicht dasselbe. Beim Kopieren bleiben alle strukturierten Verweise gleich. Wenn Sie eine Formel ausfüllen, passen vollqualifizierte strukturierte Verweise die Spaltenbezeichner wie eine Reihe an, wie in der folgenden Tabelle zusammengefasst.

Benötigen Sie weitere Hilfe?

Sie können jederzeit einen Experten in der Excel Tech Community fragen oder Unterstützung in den Communities erhalten.

Verwandte Themen

Übersicht über Excel-Tabellen
Video: Erstellen und Formatieren einer Excel-Tabelle
Gesamtergebnis der Daten in einer Excel-Tabelle
Formatieren einer Excel-Tabelle
Ändern der Tabellengröße durch Hinzufügen oder Entfernen von Zeilen und Spalten
Filtern von Daten in einem Bereich oder einer Tabelle
Konvertieren einer Tabelle in einen Bereich
Excel-Tabellenkompatibilitätsprobleme
Exportieren einer Excel-Tabelle in SharePoint
Übersicht über Formeln in Excel