Verwenden von strukturierten Verweisen für Excel-Tabellen

Hinweis: Wir möchten Ihnen die aktuellsten Hilfeinhalte so schnell wie möglich in Ihrer eigenen Sprache bereitstellen. Diese Seite wurde automatisiert übersetzt und kann Grammatikfehler oder Ungenauigkeiten enthalten. Unser Ziel ist es, Ihnen hilfreiche Inhalte bereitzustellen. Teilen Sie uns bitte über den Link am unteren Rand dieser Seite mit, ob die Informationen für Sie hilfreich sind. Hier finden Sie den englischen Artikel als Referenz.

Wenn Sie eine Excel-Tabelle erstellen, weist Excel der Tabelle und jeder Spaltenüberschrift in der Tabelle einen Namen zu. Wenn Sie einer Excel-Tabelle Formeln hinzufügen, können diese Namen automatisch angezeigt werden, wenn Sie die Formel eingeben, und die Zellbezüge in der Tabelle auswählen, anstatt Sie manuell einzugeben. Im folgenden finden Sie ein Beispiel dafür, was Excel ausführt:

Anstelle von expliziten Zellbezügen

verwendet Excel Tabellen- und Spaltennamen

=SUMME(C2:C7)

=SUMME(DeptSales[Sales Amount])

Diese Kombination aus Tabellen- und Spaltennamen wird als strukturierter Verweis bezeichnet. Die Namen in strukturierten Verweisen passen sich beim Hinzufügen oder Entfernen von Daten in einer Tabelle an.

Strukturierte Verweise treten auch auf, wenn Sie außerhalb einer Excel-Tabelle eine Formel erstellen, die sich auf Tabellendaten bezieht. Die Verweise vereinfachen gegebenenfalls das Auffinden von Tabellen in einer umfangreichen Arbeitsmappe.

Um strukturierte Verweise in Ihre Formel einzubeziehen, klicken Sie auf die Tabellenzellen, auf die Sie verweisen möchten, statt ihre Zellbezüge in die Formel einzugeben. Geben Sie anhand der folgenden Beispieldaten eine Formel ein, die automatisch strukturierte Verweise zum Berechnen des Betrags einer Vertriebsprovision verwendet.

Verkäufe Personen

Region

Sales Amount

% Commission

ProVisionsBetrag

Josef

Nord

260

10%

Robert

Süd

660

15%

Michelle

Ost

940

15%

Erich

West

410

12%

Daphne

Nord

800

15%

Werner

Süd

900

15%

  1. Kopieren Sie die Beispieldaten in der obigen Tabelle, einschließlich der Spaltenüberschriften, und fügen Sie Sie in Zelle a1 eines neuen Excel-Arbeitsblatts ein.

  2. Um die Tabelle zu erstellen, wählen Sie eine beliebige Zelle im Datenbereich aus, und drücken Sie STRG + T.

  3. Stellen Sie sicher, dass das Kontrollkästchen Tabelle hat Überschriften aktiviert ist, und klicken Sie auf OK.

  4. Geben Sie in Zelle E2 ein Gleichheitszeichen (=) ein, und klicken Sie auf Zelle C2.

    In der Bearbeitungsleiste wird der strukturierte Verweis [@[Sales Amount]] hinter dem Gleichheitszeichen angezeigt.

  5. Geben Sie direkt nach der schließenden eckigen Klammer ein Sternchen (*) ein, und klicken Sie auf Zelle D2.

    In der Bearbeitungsleiste wird der strukturierte Verweis [@[% Commission]] hinter dem Sternchen angezeigt.

  6. Drücken Sie die EINGABETASTE.

    Excel erstellt automatisch eine berechnete Spalte und kopiert die Formel in der gesamten Spalte nach unten, wobei sie für jede Zeile angepasst wird.

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. Klicken Sie im Beispielarbeitsblatt auf Zelle E2.

  2. Geben Sie in der Bearbeitungsleiste = c2 * d2 ein, und drücken Sie die Eingabe Taste.

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 " Tabellen Tools > Entwurf " im Menüband anzuzeigen.

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

In den Beispieldaten wurde der Name DeptSalesverwendet.

Wenden Sie für Tabellennamen die folgenden Regeln an:

  • Verwenden gültiger Zeichen  Beginnen Sie immer einen Namen mit einem Buchstaben, einem Unterstrich (_) oder einem umgekehrten Schrägstrich (\). Verwenden Sie Buchstaben, Zahlen, Punkte und Unterstriche für den Rest des Namens. Sie können nicht "C", "c", "R" oder "r" für den Namen verwenden, da Sie bereits als Verknüpfung zum Auswählen der Spalte oder Zeile für die aktive Zelle festgelegt werden, wenn Sie Sie im Feld Name oder Wechseln zu eingeben.

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

  • Verwenden eines Leerzeichens zum Trennen von Wörtern  Leerzeichen können im Namen nicht verwendet werden. Sie können den Unterstrich (_) und Punkt (.) als Wort Trennzeichen verwenden. Beispiel: DeptSales, Sales_Tax oder First. Quarter.

  • Nicht mehr als 255 Zeichen verwenden Ein Tabellennamen 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 einer Objekt-ID  Wenn Sie eine Kombination aus Tabellen, PivotTables und Diagrammen planen, empfiehlt es sich, ihre Namen mit dem Objekttyp zu versehen. Beispiel: tbl_Sales für eine Verkaufstabelle, pt_Sales für eine Verkaufs-PivotTable und chrt_Sales für ein Vertriebsdiagramm oder ptchrt_Sales für ein Sales-PivotChart. Dadurch bleiben alle Ihre Namen in einer geordneten Liste im Namens-Managererhalten.

Syntaxregeln für strukturierte Verweise

Sie können in der Formel auch strukturierte Bezüge manuell eingeben oder ändern, doch dies hilft Ihnen, die Syntax der strukturierten Verweise zu verstehen. Sehen wir uns das folgende Formel Beispiel an:

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

Diese Formel weist die folgenden Komponenten von strukturierten Verweisen auf:

  • Tabellenname:   DeptSales ist ein benutzerdefinierter Tabellenname. Sie verweist auf die Tabellendaten ohne Kopfzeile oder Ergebniszeile. Sie können einen Standardtabellennamen verwenden, z. b. Tabelle1, oder ihn ändern, um einen benutzerdefinierten Namen zu verwenden.

  • Spaltenbezeichner:   [Verkaufsbetrag]und[Provision Betrag] sind Spaltenbezeichner, die die Namen der von ihnen vertretenen Spalten verwenden. Sie verweisen auf die Spaltendaten ohne Spaltenüberschrift oder Ergebniszeile. Setzen Sie Spezifizierer immer in Klammern wie gezeigt ein.

  • Elementbezeichner:   [#Totals] und [#Data] sind Bezeichner für besondere Elemente, die auf bestimmte Teile der Tabelle (z. b. die Ergebniszeile) 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:

  • Verwenden von Klammern um Bezeichner    Alle Bezeichner für Tabellen, Spalten und besondere Elemente müssen in übereinstimmenden eckigen Klammern ([]) eingeschlossen werden. Ein Bezeichner, der andere Bezeichner enthält, erfordert äußere übereinstimmende Klammern, um die inneren übereinstimmenden Klammern der anderen Bezeichner einzuschließen. Beispiel: = DeptSales [[Vertriebsmitarbeiter]: [Region]]

  • Alle Spaltenüberschriften sind Textzeichenfolgen    Diese müssen jedoch bei Verwendung in einem strukturierten Verweis nicht in Anführungszeichen gesetzt 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:

  • Tabulator

  • 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 (/)

  • 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 (')

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.

Strukturierter Verweis:

Verweis auf:

Verwendung:

Zellbereich:

=DeptSales[[Sales Person]:[Region]]

Alle Zellen in zwei oder mehr angrenzenden Spalten

: (Doppelpunkt) Bereichsoperator

A2:B7

=DeptSales[Sales Amount],DeptSales[Commission Amount]

Eine Kombination aus zwei oder mehr Spalten

; (Semikolon) Vereinigungsoperator

C2:C7; E2:E7

=DeptSales[[Sales Person]:[Sales Amount]] DeptSales[[Region]:[% Commission]]

Die Schnittmenge von zwei oder mehr Spalten

 (Leerzeichen) Schnittmengen-Operator

B2:C7

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.

Bezeichner für besondere Elemente:

Verweis auf:

#All

Die gesamte Tabelle, einschließlich Spaltenüberschriften, Daten und Summen (falls vorhanden).

#Data

Nur die Datenzeilen.

#Headers

Nur die Kopfzeile.

#Totals

Nur die Summenzeile. Falls sie nicht vorhanden ist, wird null zurückgegeben.

#This Row

oder

@

oder

@[Spaltenname]

Nur die Zellen in der gleichen Zeile wie die Formel. Diese Bezeichner können nicht mit anderen Bezeichnern für besondere Elemente kombiniert werden. Damit können Sie für den Verweis ein implizites Schnittmengenverhalten erzwingen oder das implizite Schnittmengenverhalten überschreiben und auf einzelne Werte einer Spalte verweisen.

Excel ändert den Bezeichner "#This Row" in Tabellen mit mehr als einer Datenzeile automatisch in den kürzeren "@"-Bezeichner. Wenn die Tabelle jedoch nur eine Zeile aufweist, ersetzt Excel den Bezeichner "#This Row" nicht , was zu unerwarteten Ergebnissen führen kann, wenn weitere Zeilen hinzugefügt werden. Zur Vermeidung von Berechnungsproblemen sollten Sie darauf achten, Ihrer Tabelle mehrere Zeilen hinzuzufügen, bevor Sie Formeln mit strukturierten Verweisen eingeben.

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. Wenn Sie beispielsweise die berechnete Spalte "Commission Amount" erstellen möchten, mit der der Provisionsbetrag in Euro berechnet wird, können Sie folgende Formeln verwenden:

Typ des strukturierten Verweises

Beispiel

Kommentar

Nicht qualifiziert

=[Sales Amount]*[% Commission]

Multipliziert die entsprechenden Werte der aktuellen Zeile.

Vollständig qualifiziert

=DeptSales[Sales Amount]*DeptSales[% Commission]

Multipliziert die entsprechenden Werte aller Zeilen beider Spalten.

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.

Strukturierter Verweis:

Verweis auf:

Zellbereich:

=DeptSales[[#All],[Sales Amount]]

Alle Zellen in der Spalte "Sales Amount".

C1:C8

=DeptSales[[#Headers],[% Commission]]

Die Kopfzeile der Spalte "% Commission".

D1

=DeptSales[[#Totals],[Region]]

Die Summe der Spalte "Region". Falls keine Zeile "Summen" vorhanden ist, wird null zurückgegeben.

B8

=DeptSales[[#All],[Sales Amount]:[% Commission]]

Alle Zellen in "Sales Amount" und "% Commission".

C1:D8

=DeptSales[[#Data],[% Commission]:[Commission Amount]]

Nur die Daten der Spalten "% Commission" und "Commission Amount".

D2:E7

=DeptSales[[#Headers],[Region]:[Commission Amount]]

Nur die Überschriften der Spalten zwischen "Region" und "Commission Amount".

B1:E1

=DeptSales[[#Totals],[Sales Amount]:[Commission Amount]]

Die Summen der Spalten von "Sales Amount" bis zu "Commission Amount". Wenn keine Summenzeile vorhanden ist, wird Null zurückgegeben.

C8:E8

=DeptSales[[#Headers],[#Data],[% Commission]]

Nur die Überschrift und die Daten von "% Commission".

D1:D7

=DeptSales[[#This Row], [Commission Amount]]

oder

=DeptSales[@Commission Amount]

Die Zelle am Schnittpunkt der aktuellen Zeile und der Spalte proVisionsBetrag. Wenn Sie in derselben Zeile wie eine Kopf-oder Ergebniszeile verwendet wird, gibt dies einen #VALUE! -Fehler zurück.

Wenn Sie die längere Form dieses strukturierten Verweises ("#This Row") in eine Tabelle mit mehreren Datenzeilen eingeben, ersetzt Excel den Verweis automatisch durch die kürzere Form ("@"). Beide funktionieren gleich.

E5 (wenn die aktuelle Zeile die Zeile 5 ist)

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 beim Eingeben strukturierter Verweise sehr nützlich und erleichtert die Verwendung der richtigen Syntax. Weitere Informationen finden Sie unter Verwenden von AutoVervollständigen für Formeln.

  • Entscheiden, ob strukturierte Verweise für Tabellen in halb Auswahlen generiert werden sollen    Wenn Sie eine Formel erstellen und auf einen Zellbereich innerhalb einer Tabelle klicken, werden standardmäßig die Zellen ausgewählt, und anstelle des Zellbereichs in der Formel wird automatisch ein strukturierter Bezug eingegeben. Dieses Halbauswahl Verhalten 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 _GT_ - Optionen > Formeln >.

  • Verwenden von Arbeitsmappen mit externen Links zu Excel-Tabellen in anderen Arbeitsmappen    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 in der Zielarbeitsmappe zu vermeiden, die die Verknüpfungen enthält. Wenn Sie die Zielarbeitsmappe zuerst öffnen und #REF! Fehler angezeigt werden, werden Sie aufgelöst, wenn Sie die Quellarbeitsmappe öffnen. Wenn Sie zuerst die Quellarbeitsmappe öffnen, sollten keine Fehlercodes angezeigt werden.

  • Umwandeln eines Bereichs in eine Tabelle und eine Tabelle in einen Bereich    Wenn Sie eine Tabelle in einen Bereich konvertieren, ändern sich alle Zellbezüge in Ihre äquivalenten absoluten a1-Stil Bezüge. Wenn Sie einen Bereich in eine Tabelle konvertieren, ändert Excel keine Zellbezüge dieses Bereichs automatisch in die entsprechenden strukturierten Bezüge.

  • Deaktivieren von Spaltenüberschriften    Sie können die Tabellenspaltenüberschriften in der Kopfzeile der Registerkarte Tabellen Entwurf > aktivieren und deaktivieren. Wenn Sie Tabellenspaltenüberschriften deaktivieren, sind strukturierte Verweise, die Spaltennamen verwenden, nicht betroffen, und Sie können Sie weiterhin in Formeln verwenden. Strukturierte Bezüge, die direkt auf die Tabellenüberschriften verweisen (z. b. = DeptSales [[#Headers], [% Provision]]), führen zu #REF.

  • Hinzufügen oder Löschen von Spalten und Zeilen zu einer Tabelle    Da Tabellendatenbereiche häufig geändert werden, werden Zellbezüge für strukturierte Bezüge automatisch angepasst. Wenn Sie z. b. einen Tabellennamen in einer Formel verwenden, um alle Datenzellen in einer Tabelle zu zählen, und Sie dann eine Zeile mit Daten 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.

  • Verschieben, kopieren und Ausfüllen von strukturierten Verweisen    Alle strukturierten Bezüge bleiben beim Kopieren oder Verschieben einer Formel, die einen strukturierten Verweis verwendet, unverändert.

    Hinweis: Das Kopieren eines strukturierten Verweises und das Ausfüllen eines strukturierten Verweises sind nicht dasselbe. Beim Kopieren bleiben alle strukturierten Bezüge unverändert, während beim Ausfüllen einer Formel vollqualifizierte strukturierte Bezüge die Spaltenbezeichner wie eine Reihe wie in der folgenden Tabelle zusammengefasst anpassen.

Füllrichtung:

Tastendruck beim Ausfüllen:

Ergebnis:

NACH-OBEN, NACH-UNTEN

Nichts

Es findet keine Anpassung der Spaltenbezeichner statt.

NACH-OBEN oder NACH-UNTEN

STRG

Spaltenbezeichner werden wie eine Serie angepasst.

NACH-RECHTS oder NACH-LINKS

Kein

Spaltenbezeichner werden wie eine Serie angepasst.

NACH-OBEN, NACH-UNTEN, NACH-LINKS oder NACH-RECHTS

UMSCHALT

Statt dass die Werte der aktuellen Zellen überschrieben werden, werden die aktuellen Zellwerte verschoben und die Spaltenbezeichner eingefügt.

Benötigen Sie weitere Hilfe?

Sie können jederzeit einen Experten in der Excel Tech Community fragen, Unterstützung in der Answers Community erhalten oder aber ein neues Feature oder eine Verbesserung auf Excel User Voice vorschlagen.

Verwandte Themen

Übersicht über Excel-Tabellen
Video: Erstellen und Formatieren einer Excel
-TabelleGesamtsumme der Daten in einer
Excel-TabelleFormatieren einer Excel
-Tabelle 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-Tabellen Kompatibilitätsprobleme
Exportieren einer Excel-Tabelle in SharePoint
-Übersichten von Formeln in Excel

Ihre Office-Fähigkeiten erweitern
Schulungen erkunden
Neue Funktionen als Erster erhalten
Office Insider werden

War diese Information hilfreich?

Vielen Dank für Ihr Feedback!

Vielen Dank für Ihr Feedback. Es klingt, als ob es hilfreich sein könnte, Sie mit einem unserer Office-Supportmitarbeiter zu verbinden.

×