Wenn Sie eine Excel-Tabelle erstellen, weist Excel der Tabelle und jeder Spaltenüberschrift in der Tabelle einen Namen zu. Wenn Sie der Excel-Tabelle dann Formeln hinzufügen, können diese Namen bei der Eingabe der Formel automatisch angezeigt werden, sodass Sie die Zellbezüge in der Tabelle auswählen können, statt sie manuell einzugeben. Das folgende Beispiel erläutert, wie Excel dabei vorgeht:
| 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.
Wenn Sie strukturierte Bezüge in Ihre Formel einschließen möchten, wählen Sie die Tabellenzellen aus, auf die Sie verweisen möchten, anstatt deren Zellbezug in die Formel einzugeben. Verwenden Sie die folgenden Beispieldaten, um eine Formel einzugeben, die automatisch strukturierte Verweise verwendet, um die Höhe einer Verkaufsprovision zu berechnen.
| Sales 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% |
- Kopieren Sie die Beispieldaten in der Tabelle oben, einschließlich der Spaltenüberschriften, und fügen Sie sie in Zelle A1 eines neuen Excel-Arbeitsblatts ein.
- Um die Tabelle zu erstellen, markieren Sie eine beliebige Zelle innerhalb des Datenbereichs und drücken Sie STRG+T.
- Stellen Sie sicher, dass das Kontrollkästchen Meine Tabelle hat Überschriften aktiviert ist, und wählen Sie OK aus.
- Geben Sie in Zelle E2 ein Gleichheitszeichen (=) ein, und wählen Sie Zelle C2 aus.
In der Bearbeitungsleiste wird der strukturierte Verweis [@[Sales Amount]] hinter dem Gleichheitszeichen angezeigt. - Geben Sie direkt hinter der schließende Klammer ein Sternchen (*) ein, und wählen Sie Zelle D2 aus.
In der Bearbeitungsleiste wird der strukturierte Verweis [@[% Commission]] hinter dem Sternchen angezeigt. - 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.
- Wählen Sie in Ihrem Beispielarbeitsblatt Zelle E2 aus.
- 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.
- Wählen Sie eine beliebige Zelle in der Tabelle aus, um die Registerkarte Tabellenentwurf im Menüband anzuzeigen.
- Geben Sie den gewünschten Namen in das Feld Tabellenname ein, und drücken Sie EINGABETASTE.
In unseren Beispieldaten haben wir den Namen DeptSales verwendet.
Wenden Sie für Tabellennamen die folgenden Regeln an:
- Verwenden gültiger Zeichen 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. Sie können "C", "c", "R" oder "r" für den Namen nicht verwenden, da sie bereits als Verknüpfung zum Auswählen der Spalte oder Zeile für die aktive Zelle festgelegt sind, wenn Sie sie in das Feld Name oder Gehe zu eingeben.
- Keine Zellbezüge verwenden Namen dürfen nicht mit einem Zellbezug identisch sein, z. B. Z$100 oder R1C1.
- Verwenden Sie kein Leerzeichen, um Wörter zu trennen . Leerzeichen können im Namen nicht verwendet werden. Sie können den Unterstrich (_) und den Punkt (.) als Worttrennzeichen verwenden. Beispielsweise "DeptSales", "Sales_Tax" oder "First.Quarter".
- Verwenden Sie nicht mehr als 255 Zeichen Ein Tabellenname kann bis zu 255 Zeichen umfassen.
- Verwenden eindeutiger Tabellennamen Doppelte Namen sind nicht zulässig. Excel unterscheidet nicht zwischen Groß- und Kleinbuchstaben in Namen. Wenn Sie also "Sales" eingeben, aber bereits einen anderen Namen namens "SALES" in derselben Arbeitsmappe haben, werden Sie aufgefordert, einen eindeutigen Namen auszuwählen.
- Verwenden eines Objektbezeichners Wenn Sie eine Mischung aus Tabellen, PivotTables und Diagrammen planen, ist es eine gute Idee, Ihren Namen den Objekttyp voranzustellen. 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 wir uns das folgende Formelbeispiel 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. 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.
- Spaltenspezifizierer:[Sales Amount]and[Commission Amount] sind Spaltenspezifizierer, 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 die folgenden Syntaxregeln, um strukturierte Verweise manuell zu erstellen oder zu bearbeiten:
- Verwenden von Klammern um Spezifizierer Alle Tabellen-, Spalten- und Sonderelementbezeichner müssen in übereinstimmende Klammern ([ ]) eingeschlossen 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 erfordern jedoch keine Anführungszeichen, 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. Der Ausdruck DeptSalesFYSummary[[2014]:[2012]] funktioniert beispielsweise nicht.
Verwenden von Klammern um Spaltenüberschriften mit Sonderzeichen Wenn Sonderzeichen vorhanden sind, muss die gesamte Spaltenüberschrift in Klammern eingeschlossen werden, was bedeutet, dass in einem Spaltenbezeichner doppelte Klammern erforderlich sind. Beispiel: =DeptSalesFYSummary[[Total $ Amount]]
Hier ist die Liste der Sonderzeichen, die zusätzliche Klammern in der Formel benötigen:
- 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 Symbol (>)
- Kleiner als Symbol (<)
- Divisionszeichen (/)
- Bei Vorzeichen (@)
- Umgekehrter Schrägstrich (\)
- Ausrufezeichen (!)
- Linke Klammer (()
- Rechte Klammer ())
- Prozentzeichen (%)
- Fragezeichen (?)
- Rückstau (')
- Semikolon (;)
- Tilde (~)
- Unterstrich (_)
- Verwenden eines Escapezeichens für einige Sonderzeichen in Spaltenüberschriften Einige Zeichen haben eine besondere Bedeutung und erfordern die Verwendung eines einfachen Anführungszeichens (') als Escapezeichen. Beispiel: =DeptSalesFYSummary['#OfItems]
Hier ist die Liste der Sonderzeichen, die ein Escapezeichen (') in der Formel benötigen:
- Linke eckige Klammer ([)
- Rechte eckige Klammer (])
- Nummernzeichen (#)
- Einfaches Anführungszeichen (')
- Bei Vorzeichen (@)
Verwenden des Leerzeichens zum Verbessern der Lesbarkeit in einem strukturierten Verweis Sie können Leerzeichen verwenden, um die Lesbarkeit eines strukturierten Verweises zu verbessern. Beispiel: =DeptSales[ [Sales Person]:[Region] ] oder =DeptSales[[#Headers], [#Data], [% Commission]]
Es wird empfohlen, einen Bereich zu verwenden:
- 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 speziellen Elementbezeichnern 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 Ihre Tabelle jedoch nur eine Zeile enthält, ersetzt Excel den #This Zeilenbezeichner nicht, was zu unerwarteten Berechnungsergebnissen führen kann, wenn Sie weitere Zeilen hinzufügen. 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. 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:
| 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. |
Die allgemeine Regel lautet: Wenn Sie strukturierte Verweise innerhalb einer Tabelle verwenden, z. B. beim Erstellen einer berechneten Spalte, können Sie einen nicht qualifizierten strukturierten Verweis verwenden, aber wenn Sie den strukturierten Verweis außerhalb der Tabelle verwenden, müssen Sie einen vollqualifizierten 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] |
The Zelle am Schnittpunkt der aktuellen Zeile mit der Spalte "Commission Amount". Wenn sie in derselben Zeile wie eine Überschrift oder eine Ergebniszeile verwendet wird, wird ein #VALUE-Fehler zurückgegeben. 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 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 Semiauswahlen generiert werden sollen 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 im DialogfeldDateioptionen>>Formeln>arbeiten mit Formeln das Kontrollkästchen Tabellennamen in Formeln verwenden aktivieren oder deaktivieren.
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 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.
Konvertieren eines Bereichs in eine Tabelle und einer Tabelle in einen Bereich Wenn Sie eine Tabelle in einen Bereich konvertieren, ändern sich alle Zellbezüge in ihre entsprechenden absoluten A1-Formatbezü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 Tabellenspaltenüberschriften auf der Registerkarte >TabellenentwurfKopfzeile 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.
Hinzufügen oder Löschen von Spalten und Zeilen zur Tabelle Da sich Tabellendatenbereiche häufig ändern, werden Zellbezüge für strukturierte Bezüge 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.
Verschieben, Kopieren und Ausfüllen strukturierter Verweise Alle strukturierten Verweise bleiben gleich, wenn Sie eine Formel kopieren oder verschieben, die einen strukturierten Verweis verwendet.
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.
| Füllrichtung: | Beim Ausfüllen drücken Sie: | 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 | Anstatt Werte in aktuellen Zellen zu überschreiben, werden aktuelle Zellenwerte verschoben, und Spaltenspezifizierer werden eingefügt. |
Benötigen Sie weitere Hilfe?
Sie können jederzeit einen Experten in der Excel Tech Community fragen oder Unterstützung in Communitys erhalten.
Verwandte Themen
Übersicht zu Excel-Tabellen
Erstellen und Formatieren von Tabellen
Bilden 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 in einer Tabelle
Konvertieren einer Tabelle in einen Bereich
Kompatibilitätsprobleme bei Excel-Tabellen
Exportieren einer Excel-Tabelle nach SharePoint
Übersichten über Formeln in Excel