4 effektive Methoden zur Nutzung strukturierter Referenzen in Microsoft Excel

Bei der Verwendung von Excel geht es häufig darum, Beziehungen zwischen verschiedenen Datensätzen zu identifizieren. Beim Einfügen komplexer Formeln kann jedoch die übermäßige Verwendung sowohl relativer als auch absoluter expliziter Zellbezüge – wie „B7“ und seine Variationen – zu einer überladenen und chaotischen Formelleiste führen.

Die Verwendung strukturierter Referenzen in Excel kann diesen Prozess verbessern, indem Sie Tabellen und ihren Überschriften Namen zuweisen können. Diese zugewiesenen Namen dienen als implizite Zellreferenzen, sodass Excel die strukturierten Daten automatisch abrufen und berechnen kann.

Nachfolgend finden Sie mehrere effektive Methoden zum Einsatz strukturierter Referenzen in Excel.

1. Berechnungen innerhalb von Tabellen durchführen

Strukturierte Referenzen funktionieren ausschließlich mit Tabellen, daher ist ihre Nutzung innerhalb dieser Tabellentypen optimal.

Erstellen Sie beispielsweise eine einfache Tabelle mit den Zeilen B2 bis F8, die Verkaufsinformationen für ein Geschäft enthält. Achten Sie darauf, die Tabelle mit „Verkauf“ zu beschriften (überprüfen Sie den „Tabellennamen“ in der oberen linken Ecke).

Als nächstes berechnen wir den Gesamtbetrag für jeden einzelnen Verkauf:

Schritt 1: Wählen Sie Zelle F2 aus (achten Sie darauf, dass das Dropdown-Symbol nicht angeklickt ist). Navigieren Sie zu „Start“, dann zu „Einfügen“ und wählen Sie „Tabellenspalten rechts einfügen“, um automatisch eine neue Spalte in die Tabelle einzufügen.

Schritt 2: Benennen Sie die Überschrift der neuen Spalte G als „Gesamt“.

Schritt 3: Geben Sie in Zelle G3 Folgendes ein =[@PricePerUnit]*[@Quantity]und drücken Sie die Eingabetaste. Passen Sie die Zellenformatierung nach Bedarf an.

Die Verweise „[@PricePerUnit]“ und „[@Quantity]“ entsprechen den entsprechenden Feldern in den Spalten. Das „@“-Symbol vor den Spaltennamen zeigt an, dass jede Ergebniszelle ihre Werte aus derselben Tabellenzeile bezieht.

Zur Verdeutlichung: Die Formel =[@PricePerUnit]*[@Quantity]in G3 entspricht tatsächlich =$C3*$D3.

2. Abrufen eines Bereichs außerhalb der Tabelle

Wenn Sie einen strukturierten Verweis in einer Zelle außerhalb der Tabelle verwenden möchten, müssen Sie den Verweis mit dem Tabellennamen beginnen. In unserem vorherigen Beispiel wird beispielsweise durch die Verwendung von „Sales[Total]“ der gesamte Wertebereich unter der Spalte „Total“ aus der Tabelle „Sales“ abgerufen. Dadurch wird ein Array mit mehreren Werten zur weiteren Bearbeitung bereitgestellt.

So wird dies in Excel angezeigt, insbesondere in Zelle I3, wo ausreichend Platz für die Ausweitung des Bereichs nach unten gelassen wird.

Abrufen eines Bereichs außerhalb der Tabelle

3. Summieren und teilweises Summieren einer Spalte

Um schnell eine ganze Spalte zu summieren, nutzen Sie das Kontrollkästchen „Gesamtzeile“ in den Einstellungen für „Tabellendesign“ (unter „Tabellenstiloptionen“). So erhalten Sie die Summen für die Spalten „Menge“ und „Gesamt“.

Obwohl die Zeile „Gesamt“ nicht verschoben werden kann und stets am Ende der Tabelle positioniert wird (um zusätzliche Einträge zu ermöglichen), können Sie ihr Ergebnis an anderer Stelle duplizieren:

Um die Summe aller Einträge in der Spalte „Gesamt“ zu berechnen, verwenden Sie =SUM(Sales[Total]).
Um nur die Summe der sichtbaren Einträge zu erhalten, wie sie beispielsweise nach dem Filtern der Tabelle erscheinen, wenden Sie an =SUBTOTAL(109,Sales[Total]). Diese Formel spiegelt wider, was die Option „Gesamtzeile“ im Tabellenformat für die entsprechende Zeile tut.

Sie können auch eine Teilsumme basierend auf einem bestimmten Kriterium in der Tabelle berechnen, ohne es formatieren zu müssen. Beispiel:

Um den Mike zugeschriebenen Gesamtumsatz zu ermitteln, verwenden Sie =SUMIF(Sales[Seller]," Mike" ,Sales[Total]). Hier ist „Mike“ eine manuell eingegebene Zeichenfolge.
Um alle Produkte zu summieren, die durch die Produkt-ID „41230“ dargestellt werden, verwenden Sie =SUMIF(Sales[ProductID],41230,Sales[Total]). Beachten Sie, dass Sie die Nummer direkt eingeben können, da die Spalte ProductID als „Allgemein“ formatiert ist.

4. Implementieren der Datenvalidierung aus einer Tabelle mit INDIRECT

Angenommen, Sie arbeiten mit der zuvor erwähnten Verkaufstabelle. Sie können benutzerdefinierte Datenüberprüfungseinstellungen festlegen, um die Suche in den Daten zu vereinfachen. Lassen Sie uns eine kleinere Tabelle erstellen, die Auswahlmöglichkeiten für Produkt-IDs, Daten oder Verkäufer bietet. So können Sie aus diesen Gruppen ein einzelnes Element auswählen, um den Zwischenbetrag anzuzeigen.

Schritt 1: Richten Sie in Zelle B13 die Datenüberprüfung ein (Registerkarte „Daten“ > „Datentools“ > „Datenüberprüfung“).

Datenvalidierung aus einer Tabelle über INDIRECT 1

Schritt 2: Wählen Sie im angezeigten Popup-Fenster „Liste“ aus der Dropdown-Liste „Zulassen“ und geben Sie die Spaltenwerte manuell durch Kommas getrennt in das Feld „Quelle“ ein. Für dieses Beispiel haben wir „ProductID, Verkäufer, Datum“ eingegeben.

Datenvalidierung aus einer Tabelle über INDIRECT 2

Schritt 3: Erstellen Sie in Zelle C13 eine weitere Instanz der Datenüberprüfung. Wählen Sie erneut „Liste“ aus. Fügen Sie für „Quelle“ die folgende Formel ein: =INDIRECT("Sales["&B13&"]").