Verwenden von strukturierten Verweisen für Excel-Tabellen

Wichtig :  Dieser Artikel wurde maschinell übersetzt. Bitte beachten Sie den Haftungsausschluss. Die englische Version des Artikels ist als Referenz hier verfügbar: hier.

Wenn weist Sie eine Excel-Tabelle erstelltExcel einen Namen der Tabelle, und klicken Sie auf jede Spaltenüberschrift in der Tabelle. Wenn Sie Formeln in einer Excel-Tabelle hinzufügen, können automatisch lediglich die Namen angezeigt, während der Eingabe der Formel und wählen die Zellbezüge in der Tabelle, statt sie manuell eingeben. Hier ist ein Beispiel für die Funktionsweise von Excel aus:

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.

Umsatz Person

Region

Sales Amount

% Commission

Commission Amount

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 es in Zelle A1 ein neues Excel-Arbeitsblatt.

  2. Um die Tabelle zu erstellen, wählen Sie eine beliebige Zelle in dem 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 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 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, um die Tabellentools anzuzeigen > im Menüband auf die Registerkarte Entwurf.

  2. Geben Sie den Namen, die Sie im Feld Tabellenname aus, und drücken Sie die EINGABETASTE.

In den Beispieldaten wurde der Name DeptSalesverwendet.

Verwenden Sie die folgenden Regeln für Tabellennamen ein:

  • Gültige Zeichen verwenden  Starten Sie einen Namen immer mit einem Buchstaben, einem Unterstrich (_) oder ein umgekehrter Schrägstrich (\). Verwenden von Buchstaben, Zahlen, Punkte und Unterstriche für die restlichen den Namen. Können keine "C", "c", "R" oder "R" für den Namen, weil sie als Abkürzung für die Auswahl der Spalte oder Zeile für die aktive Zelle bei der Eingabe in das Feld Name oder Gehe zu bereits zugewiesen sind.

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

  • Kein Leerzeichen zum Trennen von Wörtern verwenden  Im Namen können keine Leerzeichen verwendet werden. Sie können die Unterstrich (_) und Punkt (.) als Word-Trennzeichen verwenden. Beispielsweise DeptSales, Mehrwertsteuer 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 beabsichtigen, müssen eine Kombination aus Tabellen, PivotTables und Diagramme, ist es eine gute Idee, den Namen Ihrer mit dem Objekttyp Präfix. Beispiel: Tbl_Sales für eine Verkaufstabelle, Pt_Sales für eine PivotTable-Einkaufs- und Chrt_Sales für ein Diagramm sales oder Ptchrt_Sales für ein PivotChart sales. Dadurch bleiben alle Ihre Namen in einer Ziffern oder Buchstaben sortierte Liste der Namens-Manager.

Syntaxregeln für strukturierte Verweise

Sie können auch eingeben oder Ändern von strukturierten Verweisen in die Formel manuell um werden, werden jedoch besser um zu verstehen, strukturierten Verweises Syntax. Sehen wir uns die folgenden Formelbeispiel einen:

=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 alle Zeilen Kopf- oder Summe. Sie können ein Standardtabellenname, wie z. B. Tabelle1, verwenden oder ändern, um einen benutzerdefinierten Namen verwenden.

  • Spaltenbezeichner:   [Sales Amount]und[Commission Amount] sind Bezeichner für Tabellenspalten, die die Namen der Spalten verwenden sie darstellen. Diese Spaltendaten an, ohne eine beliebige Spalte Kopf- oder Summe Zeile verwiesen werden. Schließen Sie immer Bezeichner in Klammern ein, wie dargestellt.

  • 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.

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

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

  • Bezeichner in Klammern verwenden    Alle Tabelle, Spalte und Bezeichner für besondere Elemente in Klammern ([]) eingeschlossen werden müssen. Ein Spaltenbezeichner, der andere Bezeichner enthält erfordert äußere übereinstimmende Klammern setzen Sie die inneren Klammern der anderen Bezeichner. Beispiel: = DeptSales [[Sales Person]: [Region]]

  • Alle Spaltenüberschriften sind Textzeichenfolgen    Ohne diese verbindliche Angebote, wenn sie in einem strukturierten Verweis verwendet werden. Zahlen oder Datumsangaben, wie etwa 2014 oder 1/1/2014, sind auch als Textzeichenfolgen behandelt. Sie können keine Ausdrücke mit Spaltenüberschriften verwenden. Beispielsweise den Ausdruck DeptSalesFYSummary [[2014]: [2012]] funktioniert nicht.

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 Commission Amount-Spalte. Wenn in derselben Zeile als Kopfzeile oder Ergebniszeile verwendet haben, diese zurückgegeben werden kann ein #VALUE! 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.

  • Verwenden von AutoVervollständigen    Möglicherweise erachten Sie AutoVervollständigen-Formel äußerst hilfreich ist, wenn Sie strukturierte Verweise eingeben und die Verwendung der richtigen Syntax sicherstellen. Weitere Informationen finden Sie unter Verwenden von AutoVervollständigen.

  • Entscheidung zur Generierung strukturierter Verweise für Tabellen in Semikolon Auswahl    Standardmäßig beim Erstellen eine Formel, die Sie auf eine Zelle Bereichs innerhalb einer Tabelle halb ausgewählt die Zellen, und fügt automatisch einen strukturierten Verweis anstelle der Zellbereich in der Formel. Diese Halbauswahl erleichtert ein strukturiertes Verweises eingeben. Sie können dieses Verhalten aktivieren oder deaktivieren aktivieren, durch Aktivieren oder deaktivieren das Kontrollkästchen Tabellennamen in Formeln verwenden, in der Datei > Optionen > Formeln > Dialogfeld Arbeiten mit Formeln.

  • Verwenden von Arbeitsmappen mit externen Links zu Excel-Tabellen in anderen Arbeitsmappen    Wenn eine Arbeitsmappe eine externe Verknüpfung zu einer Excel-Tabelle in eine andere Arbeitsmappe enthält, muss diese Arbeitsmappe verknüpfte Datenquelle in Excel zu vermeiden geöffnet sein #REF! Fehler in der Zielarbeitsmappe, die die Verknüpfungen enthält. Wenn Sie die Zielarbeitsmappe zuerst öffnen und #REF! Fehler angezeigt werden, sie werden aufgelöst werden, wenn Sie die Quellarbeitsmappe öffnen. Wenn Sie die Quellarbeitsmappe zuerst öffnen, sollte keine Fehlercodes angezeigt werden.

  • Bereich einer Tabelle zu einer Tabelle in einen Bereich konvertieren    Wenn Sie eine Tabelle in einen Bereich konvertieren, ändern Sie alle Zellbezüge in deren entspricht absoluter A1-Bezugsart. Wenn Sie einen Bereich in eine Tabelle konvertieren, ändern nicht Excel Zellbezüge dieses Bereichs automatisch in deren entspricht strukturierte Verweise.

  • Deaktivieren von Spaltenüberschriften    Sie können Tabellenspaltenüberschriften aktivieren und Deaktivieren von Tabelle Entwurf umschalten > Kopfzeile. Wenn Sie Tabellenspaltenüberschriften deaktivieren, strukturierte Verweise, die Spaltennamen verwenden, sind nicht betroffen, und Sie können sie weiterhin in Formeln verwenden. Strukturierten verweisen, die direkt auf den Tabellenüberschriften verweisen (z. B. = DeptSales [[#Headers], [% Commission]]) führt zu Fehlern im #REF.

  • Hinzufügen oder Löschen von Spalten und Zeilen in einer Tabelle    Da Tabellendatenbereiche häufig geändert werden, werden die Zellbezüge für strukturierte Verweise automatisch angepasst. Beispielsweise, wenn Sie verwenden einen Tabellennamen in einer Formel ein, um alle Datenzellen in einer Tabelle zu zählen, und Sie dann eine Zeile mit Daten hinzufügen, der Zellbezug wird 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 füllen Sie strukturierte Verweise    Beim Kopieren oder Verschieben einer Formel, die einen strukturierten Verweis verwendet, bleiben alle strukturierten Verweise gleich.

    Hinweis : Kopieren eines strukturierten Verweises und Ausführen einer Füllung eines strukturierten Verweises sind nicht das gleiche. Beim Kopieren, bleiben alle strukturierten Verweise gleich, während, wenn Sie eine Formel füllen, passen Sie vollständig qualifizierte strukturierte Verweise die Spaltenbezeichner wie eine Serie in der folgenden Tabelle zusammengefasst.

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 eine Excel-Tabelle
von Gesamtergebnissen in einer Excel-Tabelle
Formatieren einer Excel-Tabelle
Ändern der Größe einer 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
Kompatibilitätsprobleme bei Excel-Tabellen
Exportieren eine Excel-Tabelle in SharePoint
eine Übersicht über Formeln in Excel

Hinweis : Haftungsausschluss für maschinelle Übersetzungen: Dieser Artikel wurde mithilfe eines Computersystems und ohne jegliche Bearbeitung durch Personen übersetzt. Microsoft bietet solche maschinellen Übersetzungen als Hilfestellung für Benutzer ohne Englischkenntnisse an, damit Sie von den Informationen zu Produkten, Diensten und Technologien von Microsoft profitieren können. Da es sich bei diesem Artikel um eine maschinelle Übersetzung handelt, enthält er möglicherweise Fehler in Bezug auf (Fach-)Terminologie, Syntax und/oder Grammatik.

Ihre Fähigkeiten erweitern
Schulung erkunden
Neue Funktionen als Erster erhalten
An Office Insider teilnehmen

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.

×