Wie erstelle ich strukturierte Referenzen in Excel?
Strukturierte Referenzen beginnen mit Excel-Tabellen. Sobald die in Excel erstellten Tabellen automatisch strukturierte Referenzen für Sie erstellen.
Schauen Sie sich nun das folgende Bild an.
- Schritt 1: Ich habe einen Link zur Zelle B3 angegeben, anstatt den Link als B2 anzuzeigen, wird er als Tabelle 1 [@Sales] angezeigt. Hier ist Tabelle1 der Name der Tabelle und @Sales ist die Spalte, auf die wir uns beziehen. Alle Zellen in dieser Spalte werden mit einem Tabellennamen gefolgt von dem Namen der Spaltenüberschrift bezeichnet.
- Schritt 2: Jetzt werde ich den Tabellennamen in Data_Table ändern und die Spaltenüberschrift in Betrag ändern .
- Schritt 3: Um den Tabellennamen zu ändern, setzen Sie einen Cursor in die Tabelle> gehen Sie zu Design> Tabellenname.
- Schritt 4: Erwähnen Sie den Tabellennamen als Data_Table.
- Schritt 5: Ändern Sie nun einen Verweis auf die B3-Zelle.
Wir haben also verstanden, dass die strukturierte Referenz aus zwei Teilen besteht: Tabellenname und Spaltenname.
Beispiele
Sie können diese Excel-Vorlage für strukturierte Referenzen hier herunterladen - Excel-Vorlage für strukturierte ReferenzenBeispiel 1
Mit strukturierten Referenzen können Sie Ihre Formel dynamisch gestalten. Im Gegensatz zu normalen Zellreferenzen kann die Formel beim Hinzufügen und Löschen im Datenbereich aktiv sein.
Lassen Sie mich die SUMMEN-Formel sowohl für den Normalbereich als auch für die Excel-Tabelle anwenden.
SUM-Formel für den Normalbereich.
SUM-Formel für Excel-Tabelle.
Lassen Sie mich den Daten der Normal- und der Excel-Tabelle einige Zeilen hinzufügen. Ich habe den Daten 2 Werbebuchungen hinzugefügt, jetzt sehen Sie den Unterschied.
Die strukturierte Referenz in der Excel-Tabelle zeigt den aktualisierten Wert, aber der normale Datenbereich zeigt die aktualisierten Werte nur an, wenn Sie einige Änderungen an der Formel manuell vornehmen.
Beispiel 2
Schauen Sie sich nun ein weiteres Beispiel an. Ich habe Informationen zu Produktname, Menge und Preis. Mit diesen Informationen muss ich zum Verkaufswert gelangen.
Um den Verkaufswert zu erhalten, lautet die Formel Menge * Preis . Wenden wir diese Formel in der Tabelle an.
Die Formel sagt [@QTY] * [@PRICE]. Dies ist verständlicher als die normale Referenz von B2 * C2. Wir erhalten den Tabellennamen nicht, wenn wir die Formel in die Tabelle einfügen.
Probleme mit strukturierten Excel-Referenzen
Bei der Verwendung strukturierter Referenzen treten einige Probleme auf, die im Folgenden aufgeführt sind.
Problem Nr. 1
Strukturierte Referenzen haben auch ihre eigenen Probleme. Wir alle kennen die Excel-Formel und kopieren oder ziehen sie in die anderen verbleibenden Zellen. Dies ist nicht der gleiche Prozess in strukturierten Referenzen, er funktioniert etwas anders.
Schauen Sie sich nun das folgende Beispiel an. Ich habe die SUM-Formel in Excel für den normalen Bereich angewendet.
Wenn ich Preis und Verkaufswert summieren möchte, kopiere ich einfach die aktuelle Formel und füge sie ein oder ziehe sie in die beiden anderen Zellen. Dadurch erhalte ich den SUMMEN-Wert von Preis und Verkaufswert.
Wenden Sie nun dieselbe Formel für die Excel-Tabelle für die Spalte Menge an.
Jetzt haben wir die Summe der Menge Spalte. Kopieren Sie wie im normalen Bereich die aktuelle Formel und fügen Sie sie in die Spalte Preis ein, um die Gesamtsumme des Preises zu erhalten.
Oh mein Gott!!! Es wird nicht die Gesamtsumme der Spalte "Preis" angezeigt, sondern nur die Gesamtsumme der Spalte "Menge". Daher können wir diese Formel nicht kopieren und in die benachbarte Zelle oder eine andere Zelle einfügen, um auf die relative Spalte oder Zeile zu verweisen.
Ziehen Sie die Formel, um die Referenz zu ändern
Jetzt, da wir die Einschränkung kennen, können wir den Copy-Paste-Job mit strukturierten Referenzen nicht mehr ausführen. Wie überwinden wir dann diese Einschränkung?
Die Lösung ist sehr einfach. Wir müssen nur die Formel ziehen, anstatt sie zu kopieren. Wählen Sie die Formelzelle aus, verwenden Sie den Füllpunkt und ziehen Sie ihn in die verbleibenden zwei Zellen, um die Spaltenreferenz in Preis und Verkaufswert zu ändern.
Jetzt haben wir die Formeln aktualisiert, um die entsprechenden Summen zu erhalten.
Problem Nr. 2
Wir haben ein Problem mit den Strukturreferenzen gesehen und auch die Lösung gefunden, aber wir haben hier ein weiteres Problem: Wir können den Aufruf nicht als absolute Referenz ausführen, wenn wir die Formel in andere Zellen ziehen.
Schauen wir uns jetzt das folgende Beispiel an. Ich habe eine Verkaufstabelle mit mehreren Einträgen und möchte die Daten mithilfe der SUMIF-Funktion in Excel konsolidieren.
Jetzt werde ich die SUMIF-Funktion anwenden, um die konsolidierten Verkaufswerte für jedes Produkt zu erhalten.
Ich habe die Formel für den Januar-Monat angewendet, da es sich um eine strukturierte Referenz handelt, die wir nicht kopieren und in die verbleibenden zwei Spalten einfügen können. Die Referenz wird nicht in Feb & Mar geändert, daher ziehe ich die Formel.
Oh!! Ich habe keine Werte in der Spalte Feb & Mar erhalten. Was wäre das Problem ??? Schauen Sie sich die Formel genau an.
Wir haben die Formel vom Januar-Monat gezogen. In der SUMIF-Funktion ist das erste Argument der Kriterienbereich Sales_Table [Produkt], da wir die geänderte Formel in Sales _Table [Jan] gezogen haben.
Wie gehen wir damit um? Wir müssen das erste Argument, dh die Produktspalte, als absolut und andere Spalten als relative Referenz angeben. Im Gegensatz zur normalen Referenz haben wir nicht den Luxus, die F4-Taste zum Ändern des Referenzierungstyps zu verwenden.
Die Lösung besteht darin, dass wir die Referenzierungsspalte wie im folgenden Bild gezeigt duplizieren müssen.
Jetzt können wir die Formel auf zwei andere Spalten ziehen. Der Kriterienbereich ist konstant und andere Spaltenreferenzen ändern sich entsprechend.
Pro-Tipp: Um die ROW als absolute Referenz zu erstellen, müssen wir einen doppelten ROW-Eintrag vornehmen, aber das @ -Symbol vor dem ROW-Namen setzen.
= Sales_Table [@ [Produkt]: [Produkt]]
Wie deaktiviere ich die strukturierte Referenz in Excel?
Wenn Sie kein Fan von strukturierten Referenzen sind, können Sie diese deaktivieren, indem Sie die folgenden Schritte ausführen.
- Schritt 1: Gehen Sie zu DATEI> Optionen.
- Schritt 2: Formeln> Deaktivieren Sie Tabellennamen in Formeln verwenden.
Dinge, an die man sich erinnern sollte
- Um die absolute Referenz in einer strukturierten Referenz zu erstellen, müssen wir den Spaltennamen verdoppeln.
- Wir können die Formel der strukturierten Referenz nicht kopieren, sondern müssen die Formel ziehen.
- Wir können nicht genau sehen, auf welche Zelle wir uns in strukturierten Referenzen beziehen.
- Wenn Sie nicht an strukturierten Referenzen interessiert sind, können Sie diese deaktivieren.
