Pc Support: Verwenden von strukturierten Verweisen mit Excel-Tabellen

Excel für Microsoft 365 Excel für Microsoft 365 für Mac Excel 2021 Excel 2021 für Mac Excel 2019 Excel 2019 für Mac Excel 2016 Excel 2016 für Mac Excel 2013 Excel 2010 Excel 2007 Excel für Mac 2011 Excel Mobile Mehr... Weniger

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. Hier ist ein Beispiel dafür, was Excel tut:

Anstatt explizite Zellbezüge zu verwenden	Excel verwendet Tabellen- und Spaltennamen
=Summe(C2:C7)	=SUM(DeptSales[Verkaufsbetrag])

Diese Kombination aus Tabellen- und Spaltennamen wird als strukturierte Referenz bezeichnet. Die Namen in strukturierten Referenzen werden angepasst, wenn Sie Daten zur Tabelle hinzufügen oder daraus entfernen.

Strukturierte Verweise werden auch angezeigt, wenn Sie eine Formel außerhalb einer Excel-Tabelle erstellen, die auf Tabellendaten verweist. Die Verweise können das Auffinden von Tabellen in einer großen Arbeitsmappe erleichtern.

Um strukturierte Bezüge in Ihre Formel aufzunehmen, klicken Sie auf die Tabellenzellen, auf die Sie verweisen möchten, anstatt ihre Zellbezüge in die Formel einzugeben. Lassen Sie uns die folgenden Beispieldaten verwenden, um eine Formel einzugeben, die automatisch strukturierte Referenzen verwendet, um die Höhe einer Verkaufsprovision zu berechnen.

Verkäufer _	Region	Verkaufsbetrag _	% Kommission
Jo	Norden	260	10%
Robert	Süd	660	15%
Michelle	Ost	940	15%
Erich	Westen	410	12%
Dafna	Norden	800	15%
rauben	Süd	900	15%

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.
Wählen Sie zum Erstellen der Tabelle eine beliebige Zelle innerhalb des Datenbereichs aus und drücken Sie Strg+T .
Stellen Sie sicher, dass das Kontrollkästchen Meine Tabelle hat Kopfzeilen aktiviert ist, und klicken Sie auf OK .
Geben Sie in Zelle E2 ein Gleichheitszeichen ( = ) ein und klicken Sie auf Zelle C2.
In der Formelleiste erscheint nach dem Gleichheitszeichen die strukturierte Referenz [@[Sales Amount]] .
Geben Sie direkt nach der schließenden Klammer ein Sternchen ( * ) ein und klicken Sie auf Zelle D2.
In der Bearbeitungsleiste erscheint nach dem Stern der strukturierte Verweis [@[% Provision]] .
Drücken Sie die Eingabetaste .
Excel erstellt automatisch eine berechnete Spalte und kopiert die Formel für Sie in die gesamte Spalte und passt sie für jede Zeile an.

Was passiert, wenn ich explizite Zellbezüge verwende?

Wenn Sie explizite Zellbezüge in eine berechnete Spalte eingeben, kann es schwieriger sein, zu erkennen, was die Formel berechnet.

Klicken Sie in Ihrem Beispielarbeitsblatt auf Zelle E2
Geben Sie in der Formelleiste =C2*D2 ein und drücken Sie die Eingabetaste .

Beachten Sie, dass Excel zwar Ihre Formel in die Spalte kopiert, aber keine strukturierten Verweise verwendet. Wenn Sie beispielsweise eine Spalte zwischen den vorhandenen Spalten C und D hinzufügen, müssen Sie Ihre Formel überarbeiten.

Wie ändere ich einen Tabellennamen?

Wenn Sie eine Excel-Tabelle erstellen, erstellt Excel einen Standardtabellennamen (Tabelle1, Tabelle2 usw.), aber Sie können den Tabellennamen ändern, um ihn aussagekräftiger zu machen.

Wählen Sie eine beliebige Zelle in der Tabelle aus, um die Registerkarte Tabellentools > Entwurf im Menüband anzuzeigen.
Geben Sie den gewünschten Namen in das Feld Tabellenname ein und drücken Sie die Eingabetaste .

In unseren Beispieldaten haben wir den Namen DeptSales verwendet .

Verwenden Sie die folgenden Regeln für Tabellennamen:

Verwenden Sie gültige Zeichen Beginnen Sie einen Namen immer mit einem Buchstaben, einem Unterstrich ( _ ) oder einem umgekehrten Schrägstrich ( \ ). Verwenden Sie für den Rest des Namens Buchstaben, Zahlen, Punkte und Unterstriche. Sie können "C", "c", "R" oder "r" nicht für den Namen verwenden, da sie bereits als Abkürzung für die Auswahl der Spalte oder Zeile für die aktive Zelle vorgesehen sind, wenn Sie sie in das eingeben Feld „Name" oder „Gehe zu ".

Verwenden Sie keine Zellreferenzen. Namen dürfen nicht mit Zellreferenzen identisch sein, wie z. B. Z$100 oder R1C1.
Verwenden Sie kein Leerzeichen, um Wörter zu trennen. Leerzeichen dürfen nicht im Namen verwendet werden. Sie können den Unterstrich ( _ ) und den Punkt ( . ) als Worttrennzeichen verwenden. Beispiel: DeptSales, Sales_Tax oder First.Quarter.
Verwenden Sie nicht mehr als 255 Zeichen. Ein Tabellenname kann bis zu 255 Zeichen lang sein.
Verwenden Sie eindeutige 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 Sie eine Objektkennung Wenn Sie eine Mischung aus Tabellen, PivotTables und Diagrammen planen, empfiehlt es sich, Ihren Namen den Objekttyp voranzustellen. Beispiel: tbl_Sales für eine Verkaufstabelle, pt_Sales für eine Verkaufs-PivotTable und chrt_Sales für ein Verkaufsdiagramm oder ptchrt_Sales für ein Verkaufs-PivotChart. Dadurch werden alle Ihre Namen in einer geordneten Liste im Namensmanager gespeichert.

Syntaxregeln für strukturierte Referenzen

Sie können strukturierte Referenzen auch manuell in die Formel eingeben oder ändern, aber dazu ist es hilfreich, die Syntax strukturierter Referenzen zu verstehen. Gehen wir das folgende Formelbeispiel durch:

=SUM(DeptSales[[#Totals],[Sales Amount]],DeptSales[[#Data],[Provision Amount]])

Diese Formel hat die folgenden strukturierten Referenzkomponenten:

Tabellenname: DeptSales ist ein benutzerdefinierter Tabellenname. Es verweist auf die Tabellendaten ohne Kopf- oder Summenzeilen. Sie können einen Standardtabellennamen verwenden, z. B. Tabelle1, oder ihn ändern, um einen benutzerdefinierten Namen zu verwenden.
Spaltenbezeichner: [Sales Amount] und [Provision Amount ] sind Spaltenbezeichner, die die Namen der Spalten verwenden, die sie darstellen. Sie verweisen auf die Spaltendaten ohne Spaltenüberschrift oder Gesamtzeile. Schließen Sie Bezeichner immer wie gezeigt in Klammern ein.
Elementbezeichner: [#Totals] und [#Data] sind spezielle Elementbezeichner, die sich auf bestimmte Teile der Tabelle beziehen, z. B. die Summenzeile.
Tabellenspezifizierer: [[#Totals],[Sales Amount]] und [[#Data],[Provision Amount]] sind Tabellenspezifizierer, die die äußeren Teile der strukturierten Referenz darstellen. Äußere Referenzen folgen dem Tabellennamen, und Sie schließen sie in eckige Klammern ein.
Strukturierte Referenz: (DeptSales[[#Totals],[Sales Amount]] und DeptSales[[#Data],[Provision Amount]] sind strukturierte Referenzen, dargestellt durch eine Zeichenfolge, die mit dem Tabellennamen beginnt und mit dem Spaltenbezeichner endet.

Um strukturierte Referenzen manuell zu erstellen oder zu bearbeiten, verwenden Sie diese Syntaxregeln:

Verwenden Sie Klammern um Bezeichner Alle Tabellen-, Spalten- und Sonderelementbezeichner müssen in passende 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. Zum Beispiel: =DeptSales[[Vertriebsmitarbeiter]:[Region]]
Alle Spaltenüberschriften sind Textzeichenfolgen, aber sie erfordern keine Anführungszeichen, wenn sie in einer strukturierten Referenz verwendet werden. Zahlen oder Daten wie 2014 oder 1.1.2014 gelten ebenfalls als Textzeichenfolgen. Sie können keine Ausdrücke mit Spaltenüberschriften verwenden. Beispielsweise funktioniert der Ausdruck DeptSalesFYSummary[[2014]:[2012]] nicht.

Verwenden Sie 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. Zum Beispiel: =DeptSalesFYSummary[[Total $ Amount]]

Hier ist die Liste der Sonderzeichen, die zusätzliche Klammern in der Formel benötigen:

Tab
Zeilenvorschub
Wagenrückgabe
Komma (,)
Doppelpunkt (:)
Zeitraum (.)
Linke Klammer ([)
Rechte Klammer (])
Pfund-Zeichen (#)
Einfaches Anführungszeichen (')
Doppeltes Anführungszeichen (")
Linke Klammer ({)
Rechte Klammer (})
Dollarzeichen ($)
Caretzeichen (^)
Et-Zeichen (&)
Sternchen (*)
Pluszeichen (+)
Gleichheitszeichen (=)
Minuszeichen (-)
Größer-als-Symbol (>)
Kleiner-als-Zeichen (<)
Divisionszeichen (/)

Verwenden Sie ein Escape-Zeichen für einige Sonderzeichen in Spaltenüberschriften Einige Zeichen haben eine besondere Bedeutung und erfordern die Verwendung eines einfachen Anführungszeichens (') als Escape-Zeichen. Zum Beispiel: =DeptSalesFYSummary['#OfItems]

Hier ist die Liste der Sonderzeichen, die ein Escape-Zeichen (') in der Formel benötigen:

Linke Klammer ([)
Rechte Klammer (])
Pfund-Zeichen(#)
Einfaches Anführungszeichen (')

Verwenden Sie das Leerzeichen, um die Lesbarkeit in einer strukturierten Referenz zu verbessern Sie können Leerzeichen verwenden, um die Lesbarkeit einer strukturierten Referenz zu verbessern. Zum Beispiel: =DeptSales[ [Vertriebsmitarbeiter]:[Region] ] oder =DeptSales[[#Headers], [#Data], [% Commission]]

Es wird empfohlen, ein Leerzeichen zu verwenden:

Nach der ersten linken Klammer ([)
Vor der letzten rechten Klammer (]).
Nach einem Komma.

Referenzoperatoren

Für mehr Flexibilität bei der Angabe von Zellbereichen können Sie die folgenden Verweisoperatoren verwenden, um Spaltenbezeichner zu kombinieren.

Diese strukturierte Referenz:	Bezieht sich auf:	Durch die Verwendung von:	Welches ist der Zellbereich:
=Abt.Verkauf[[Vertriebsmitarbeiter]:[Region]]	Alle Zellen in zwei oder mehr benachbarten Spalten	: (Doppelpunkt) Bereichsoperator	A2:B7
=DeptSales[Verkaufsbetrag],DeptSales[Provisionsbetrag]	Eine Kombination aus zwei oder mehr Spalten	, (Komma) Vereinigungsoperator	C2:C7, E2:E7
=DeptSales[[Vertriebsmitarbeiter]:[Verkaufsbetrag]] DeptSales[[Region]:[Provision in %]]	Der Schnittpunkt von zwei oder mehr Spalten	(Leerzeichen) Schnittpunktoperator	B2:C7

Spezielle Artikelbezeichner

Um auf bestimmte Teile einer Tabelle zu verweisen, z. B. nur auf die Summenzeile, können Sie einen der folgenden speziellen Elementbezeichner in Ihren strukturierten Verweisen verwenden.

Dieser spezielle Artikelbezeichner:	Bezieht sich auf:
#Alle	Die gesamte Tabelle, einschließlich Spaltenüberschriften, Daten und Summen (falls vorhanden).
#Daten	Nur die Datenzeilen.
#Überschriften	Nur die Kopfzeile.
#Summen	Nur die Gesamtreihe. Wenn keine vorhanden ist, wird null zurückgegeben.
#Diese Reihe oder @ oder @[Spaltenname]	Nur die Zellen in derselben Zeile wie die Formel. Diese Spezifizierer können nicht mit anderen speziellen Artikelspezifizierern kombiniert werden. Verwenden Sie sie, um implizites Schnittmengenverhalten für die Referenz zu erzwingen oder um das implizite Schnittmengenverhalten zu überschreiben und auf Einzelwerte aus einer Spalte zu verweisen. Excel ändert in Tabellen mit mehr als einer Datenzeile automatisch #This Row-Bezeichner in den kürzeren @-Bezeichner. Wenn Ihre Tabelle jedoch nur eine Zeile enthält, ersetzt Excel den Bezeichner #This Row nicht, was zu unerwarteten Berechnungsergebnissen führen kann, wenn Sie weitere Zeilen hinzufügen. Um Berechnungsprobleme zu vermeiden, stellen Sie sicher, dass Sie mehrere Zeilen in Ihre Tabelle eingeben, bevor Sie strukturierte Referenzformeln eingeben.

Qualifizierende strukturierte Referenzen in berechneten Spalten

Wenn Sie eine berechnete Spalte erstellen, verwenden Sie häufig eine strukturierte Referenz, um die Formel zu erstellen. Diese strukturierte Referenz kann uneingeschränkt oder vollqualifiziert sein. Um beispielsweise die berechnete Spalte Provisionsbetrag zu erstellen, die den Provisionsbetrag in Dollar berechnet, können Sie die folgenden Formeln verwenden:

Art der strukturierten Referenz	Beispiel	Kommentar
Unqualifiziert	=[Verkaufsbetrag]*[% Provision]	Multipliziert die entsprechenden Werte aus der aktuellen Zeile.
Vollqualifiziert	=DeptSales[Verkaufsbetrag]*DeptSales[% Provision]	Multipliziert die entsprechenden Werte für jede Zeile für beide Spalten.

Als allgemeine Regel gilt: Wenn Sie strukturierte Referenzen innerhalb einer Tabelle verwenden, z. B. beim Erstellen einer berechneten Spalte, können Sie eine nicht qualifizierte strukturierte Referenz verwenden, aber wenn Sie die strukturierte Referenz außerhalb der Tabelle verwenden, müssen Sie eine vollqualifizierte strukturierte Referenz zu verwenden.

Beispiele für die Verwendung strukturierter Referenzen

Hier sind einige Möglichkeiten, strukturierte Referenzen zu verwenden.

Diese strukturierte Referenz:	Bezieht sich auf:	Welches ist der Zellbereich:
=DeptSales[[#All],[Verkaufsbetrag]]	Alle Zellen in der Spalte Verkaufsbetrag.	C1:C8
=DeptSales[[#Headers],[Provision in %]]	Die Überschrift der Spalte % Provision.	D1
=DeptSales[[#Totals],[Region]]	Die Summe der Spalte „Region". Wenn keine Summenzeile vorhanden ist, wird null zurückgegeben.	B8
=DeptSales[[#All],[Sales Amount]:[% Commission]]	Alle Zellen in Verkaufsbetrag und % Provision.	C1:D8
=DeptSales[[#Data],[Provision in %]:[Provisionsbetrag]]	Nur die Daten der Spalten % Provision und Provisionsbetrag.	D2:E7
=DeptSales[[#Headers],[Region]:[Provisionsbetrag]]	Nur die Überschriften der Spalten zwischen Region und Provisionsbetrag.	B1:E1
=DeptSales[[#Totals],[Sales Amount]:[Provision Amount]]	Die Summen der Spalten Verkaufsbetrag bis Provisionsbetrag. Wenn keine Summenzeile vorhanden ist, wird null zurückgegeben.	C8:E8
=DeptSales[[#Headers],[#Data],[% Provision]]	Nur der Header und die Daten von % Commission.	D1:D7
=DeptSales[[#This Row], [Provisionsbetrag]] oder =DeptSales[@Provisionsbetrag]	Die Zelle am Schnittpunkt der aktuellen Zeile und der Spalte Provisionsbetrag. Bei Verwendung in derselben Zeile wie eine Kopf- oder Summenzeile wird ein #WERT zurückgegeben! Fehler. Wenn Sie die längere Form dieser strukturierten Referenz (#Diese Zeile) in eine Tabelle mit mehreren Datenzeilen eingeben, ersetzt Excel sie automatisch durch die kürzere Form (@). Beide funktionieren gleich.	E5 (wenn die aktuelle Zeile 5 ist)

Strategien für die Arbeit mit strukturierten Referenzen

Beachten Sie Folgendes, wenn Sie mit strukturierten Referenzen arbeiten.

Formel-AutoVervollständigen verwenden Möglicherweise ist die Verwendung von Formel-AutoVervollständigen sehr nützlich, wenn Sie strukturierte Referenzen eingeben und die Verwendung der korrekten Syntax sicherstellen. Weitere Informationen finden Sie unter Formel-AutoVervollständigen verwenden .
Entscheiden, ob strukturierte Referenzen für Tabellen in Halbauswahlen generiert werden sollen Wenn Sie beim Erstellen einer Formel auf einen Zellbereich innerhalb einer Tabelle klicken, werden die Zellen standardmäßig halb ausgewählt und automatisch eine strukturierte Referenz anstelle des Zellbereichs in die Formel eingefügt. Dieses Halbauswahlverhalten erleichtert die Eingabe einer strukturierten Referenz erheblich. Sie können dieses Verhalten ein- oder ausschalten, indem Sie das Kontrollkästchen Tabellennamen in Formeln verwenden im Dialogfeld Datei > Optionen > Formeln > Arbeiten mit Formeln aktivieren oder deaktivieren.
Verwenden Sie 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, die die Links enthält. Wenn Sie zuerst die Zielarbeitsmappe öffnen und #REF! Wenn Fehler auftreten, werden sie behoben, wenn Sie dann die Quellarbeitsmappe öffnen. Wenn Sie zuerst die Quellarbeitsmappe ö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, werden alle Zellreferenzen in ihre entsprechenden absoluten A1-Stilreferenzen geändert. Wenn Sie einen Bereich in eine Tabelle konvertieren, ändert Excel nicht automatisch Zellbezüge dieses Bereichs in ihre entsprechenden strukturierten Bezüge.
Deaktivieren von Spaltenüberschriften Sie können Tabellenspaltenüberschriften auf der Registerkarte Entwurf der Tabelle > 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 Referenzen, die direkt auf die Tabellenüberschriften verweisen (zB =DeptSales[[#Headers],[%Provision]] ) ergeben #REF .
Hinzufügen oder Löschen von Spalten und Zeilen zur Tabelle Da sich Tabellendatenbereiche häufig ändern, werden Zellreferenzen für strukturierte Referenzen automatisch angepasst. Wenn Sie beispielsweise einen Tabellennamen in einer Formel verwenden, um alle Datenzellen in einer Tabelle zu zählen, und Sie dann eine Datenzeile hinzufügen, wird der Zellbezug automatisch angepasst.
Umbenennen einer Tabelle oder Spalte Wenn Sie eine Spalte oder Tabelle umbenennen, ändert Excel automatisch die Verwendung dieser Tabelle und Spaltenüberschrift in allen strukturierten Referenzen, die in der Arbeitsmappe verwendet werden.
Strukturierte Referenzen verschieben, kopieren und füllen Alle strukturierten Referenzen bleiben gleich, wenn Sie eine Formel kopieren oder verschieben, die eine strukturierte Referenz verwendet.
Hinweis: Das Kopieren einer strukturierten Referenz und das Ausfüllen einer strukturierten Referenz sind nicht dasselbe. Beim Kopieren bleiben alle strukturierten Verweise gleich, während beim Ausfüllen einer Formel vollqualifizierte strukturierte Verweise die Spaltenbezeichner wie eine Reihe anpassen, wie in der folgenden Tabelle zusammengefasst.

Wenn die Füllrichtung ist:	Und während des Füllens drücken Sie:	Dann:
Oben oder unten	Nichts	Es gibt keine Anpassung des Spaltenbezeichners.
Oben oder unten	Strg	Spaltenbezeichner werden wie eine Reihe angepasst.
Rechts oder links	Keiner	Spaltenbezeichner werden wie eine Reihe angepasst.
Oben, unten, rechts oder links	Schicht	Anstatt Werte in aktuellen Zellen zu überschreiben, werden aktuelle Zellenwerte verschoben und Spaltenbezeichner eingefügt.

Benötigen Sie weitere Hilfe?

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

Pc Support

Friday, February 24, 2023

Verwenden von strukturierten Verweisen mit Excel-Tabellen - Microsoft Support

Verwenden von strukturierten Verweisen mit Excel-Tabellen