Computertricks

Strukturierte Verweise in Excel: Vereinfachen Sie Ihre Formeln

Posted on by Wilhelm von Hohenberg

Excel bietet eine leistungsstarke Funktion namens “strukturierte Verweise”, die das Arbeiten mit Tabellen und Formeln erheblich vereinfacht. Anstatt sich mit komplizierten Zellbezügen herumzuschlagen, ermöglichen Ihnen strukturierte Verweise, auf Tabellen und deren Spalten anhand klarer Namen zuzugreifen. Dies führt nicht nur zu übersichtlicheren und leichter verständlichen Formeln, sondern passt sich auch dynamisch an Änderungen in Ihrer Tabelle an.

Die Grundidee hinter strukturierten Verweisen ist, dass Excel jeder Tabelle und jeder Spaltenüberschrift einen eindeutigen Namen zuweist. Wenn Sie dann eine Formel erstellen, die sich auf Daten innerhalb dieser Tabelle bezieht, können Sie anstelle von Zelladressen wie C2:C7 einfach den Tabellen- und Spaltennamen verwenden, beispielsweise =SUMME(DeptSales[Sales Amount]). Diese Kombination aus Tabellen- und Spaltennamen ist das, was wir als strukturierten Verweis bezeichnen.

Wie funktionieren strukturierte Verweise?

Wenn Sie Daten in Excel als Tabelle formatieren (mithilfe von STRG+T), erstellt Excel automatisch diese Namenszuweisungen. Das Besondere an strukturierten Verweisen ist ihre Anpassungsfähigkeit. Werden der Tabelle neue Daten hinzugefügt oder Daten entfernt, passen sich die Verweise automatisch an. Dies ist ein großer Vorteil gegenüber festen Zellbezügen, die manuell aktualisiert werden müssten, um Fehler zu vermeiden.

Betrachten wir ein praktisches Beispiel, um die Anwendung zu verdeutlichen. Angenommen, Sie haben eine Tabelle mit Verkaufsdaten, einschließlich Spalten wie “Sales Person”, “Region”, “Sales Amount” und “% Commission”. Sie möchten die Provision für jeden Verkauf berechnen.

Stellen Sie sich die folgenden Beispieldaten vor:

Sales PersonRegionSales Amount% CommissionCommission Amount
JosefNord26010%
RobertSüd66015%
MichelleOst94015%
ErichWest41012%
DaphneNord80015%
WernerSüd90015%
Weiterlesen >>  Excel think-cell: Präzise Datenaufbereitung und Analyse für beeindruckende Präsentationen

Schritte zur Erstellung einer Formel mit strukturierten Verweisen:

  1. Kopieren Sie die obigen Daten einschließlich der Spaltenüberschriften und fügen Sie sie in Zelle A1 eines neuen Excel-Arbeitsblatts ein.
  2. Markieren Sie eine beliebige Zelle innerhalb dieses Datenbereichs und drücken Sie STRG+T, um die Daten in eine offizielle Excel-Tabelle zu konvertieren. Stellen Sie sicher, dass das Kontrollkästchen “Meine Tabelle hat Überschriften” aktiviert ist, und klicken Sie auf “OK”.
  3. Geben Sie in Zelle E2 (die erste Zelle in der Spalte “Commission Amount”) ein Gleichheitszeichen (=) ein.
  4. Wählen Sie Zelle C2 (“Sales Amount”) aus. In der Bearbeitungsleiste sehen Sie nun den strukturierten Verweis =[@Sales Amount].
  5. Geben Sie direkt hinter der schließenden Klammer ein Sternchen (*) ein, und wählen Sie dann Zelle D2 (“% Commission”) aus. Die Bearbeitungsleiste zeigt nun =[@Sales Amount]*[% Commission] an.
  6. Drücken Sie die EINGABETASTE.

Excel erstellt automatisch eine berechnete Spalte und kopiert die Formel nach unten, wobei sie für jede Zeile korrekt angepasst wird. Das Ergebnis in E2 wäre beispielsweise 40,98 (260 * 0,10).

Vielfältige Anwendungsmöglichkeiten strukturierter Verweise

Die Flexibilität von strukturierten Verweisen zeigt sich in ihrer breiten Anwendbarkeit. Hier sind einige Beispiele, wie Sie sie einsetzen können, wobei DeptSales als Beispiel-Tabellenname dient:

Strukturierter Verweis:Verweis auf:Typischer Zellbereich:
=DeptSales[[#All];[Sales Amount]]Alle Zellen in der Spalte “Sales Amount”, einschließlich Überschrift und ggf. Summenzeile.C1:C8
=DeptSales[[#Headers];[% Commission]]Nur die Kopfzeile der Spalte “% Commission”.D1
=DeptSales[[#Totals];[Region]]Die Summe der Spalte “Region” (falls eine Summenzeile vorhanden ist).B8
=DeptSales[[#All];[Sales Amount]:[% Commission]]Alle Zellen in den Spalten “Sales Amount” und “% Commission”.C1:D8
=DeptSales[[#Data];[% Commission]:[Commission Amount]]Nur die reinen Daten (ohne Überschriften und Summenzeilen) der Spalten “% Commission” und “Commission Amount”.D2:E7
=DeptSales[[#Headers];[Region]:[Commission Amount]]Nur die Überschriften der Spalten, die sich zwischen “Region” und “Commission Amount” befinden.B1:E1
=DeptSales[[#Totals];[Sales Amount]:[Commission Amount]]Die Summen der Spalten von “Sales Amount” bis “Commission Amount” (falls eine Summenzeile vorhanden ist).C8:E8
=DeptSales[[#Headers];[#Data];[% Commission]]Nur die Überschrift und die reinen Daten der Spalte “% Commission”.D1:D7
=DeptSales[[#This Row]; [Commission Amount]] oder =DeptSales[@Commission Amount]Die Zelle an der Schnittstelle der aktuellen Zeile mit der Spalte “Commission Amount”. Wenn diese Formel in einer Überschrift- oder Summenzeile steht, gibt sie einen Fehler zurück. Excel ersetzt automatisch die längere Form “#This Row” durch die kürzere Form “@”, wenn mehrere Datenzeilen vorhanden sind.E5 (wenn die aktuelle Zeile die Zeile 5 ist)
Weiterlesen >>  Schaltplan Erstellen Kostenlos: Die Ultimative Anleitung für ProfiCAD

Die Verwendung strukturierter Verweise ist ein wesentlicher Bestandteil für effizientes Arbeiten mit Excel-Tabellen. Sie machen Ihre Arbeitsmappen robuster, besser lesbar und erleichtern die Wartung komplexer Berechnungen.

Benötigen Sie weitere Hilfe?

Für weiterführende Fragen oder zur Lösung spezifischer Probleme können Sie jederzeit die Excel Tech Community besuchen oder Unterstützung in den Microsoft Office Foren suchen.

Verwandte Themen