Modelle Automatisch Erweitern
Swipe um das Menü anzuzeigen
Excel-Tabellen erweitern strukturierte Verweise automatisch, aber einige Berechnungen basieren weiterhin auf Standardbereichen. In diesem Kapitel werden dynamische Bereiche erstellt, die zusammen mit dem Datensatz automatisch wachsen.
COUNTA-Struktur
=COUNTA(value1, [value2], ...)
value1: erster Bereich oder Wert, der gezählt wird;[value2]: optional zusätzliche Bereiche oder Werte.
COUNTA zählt alle nicht-leeren Zellen.
In diesem Kapitel verwendete Formel:
=COUNTA(Sales_Data[Revenue])
Dies gibt die aktuelle Anzahl der ausgefüllten Revenue-Zeilen zurück.
OFFSET-Struktur
=OFFSET(reference, rows, cols, [height], [width])
reference: Startzelle;rows: vertikale Verschiebung;cols: horizontale Verschiebung;[height]: Höhe des zurückgegebenen Bereichs;[width]: Breite des zurückgegebenen Bereichs.
OFFSET erstellt einen Bereich dynamisch relativ zu einer Startposition.
Dynamische OFFSET-Formel
=SUM(OFFSET(Sales_Data!$H$1,1,0,COUNTA(Sales_Data!$H:$H)-1,1))
Sales_Data!$H$1: Startreferenz;1: überspringt die Kopfzeile;COUNTA(...) - 1: dynamische Datensatzhöhe;1: Breite des zurückgegebenen Bereichs.
Die Formel passt sich automatisch an, wenn neue Zeilen hinzugefügt werden.
Im Blatt Summary eingeben:
=COUNTA(Sales_Data[Revenue])
Überprüfen, ob das Ergebnis der aktuellen Anzahl der Revenue-Zeilen entspricht.
Eingeben:
=SUM(OFFSET(Sales_Data!$H$1,1,0,COUNTA(Sales_Data!$H:$H)-1,1))
Die Berechnung des Gesamtumsatzes erweitert sich nun dynamisch mit dem Datensatz.
Eine neue Transaktionszeile in Sales_Data hinzufügen.
Überprüfen, ob sich der Umsatz-Gesamtwert automatisch aktualisiert.
Den Namensmanager öffnen und erstellen:
DynamicRevenue
Folgende Formel zuweisen:
=OFFSET(Sales_Data!$H$1,1,0,COUNTA(Sales_Data!$H:$H)-1,1)
Die vorherige Formel ersetzen durch:
=SUM(DynamicRevenue)
Die Logik bleibt dynamisch, aber die Formel wird lesbarer und leichter zu pflegen.
Einen weiteren benannten Bereich erstellen:
DynamicProfit
Zuweisen:
=OFFSET(Sales_Data!$J$1,1,0,COUNTA(Sales_Data!$J:$J)-1,1)
Eingeben:
=SUM(DynamicProfit)
Weitere Zeilen in Sales_Data hinzufügen.
Überprüfen, ob:
- Umsatzsummen sich automatisch aktualisieren;
- Gewinnsummen sich automatisch aktualisieren;
- Benannte Bereiche weiterhin dynamisch erweitert werden.
1. Was ist der Hauptzweck der Kombination von COUNTA mit OFFSET?
2. Warum sind benannte Bereiche bei der Arbeit mit OFFSET nützlich?
3. Was bedeutet es, dass OFFSET eine volatile Funktion ist?
Danke für Ihr Feedback!
Fragen Sie AI
Fragen Sie AI
Fragen Sie alles oder probieren Sie eine der vorgeschlagenen Fragen, um unser Gespräch zu beginnen
