- 3. Datenimport mit Power Query
- 2. „Intelligente“ Automatisierung von Excel-Tabellen ohne Programmierung
- 1. Optimale Modellierung von Excel-Dateien
Wird eine Excel-Datentabelle erweitert oder reduziert, müssen Zellbezüge in den Formeln und Funktionen, die sich auf diese Tabelle beziehen, angepasst werden. Dies ist eine häufige Fehlerquelle. Rainer Pollmann stellt einen Automatismus vor, der in den Formeln und Funktionen dynamisch den Zellbezug für den Datenbereich erkennt und anpasst.
Aktualisierung einer „Datentabelle“ ist fehleranfällig
Anwender arbeiten in Excel in der Regel mit Datentabellen, die mit Formeln und Funktionen ergänzt, ausgewertet und analysiert werden. Wird die Datentabelle erweitert oder reduziert, müssen Zellbezüge in den Formeln und Funktionen, die sich auf diese Tabelle beziehen, angepasst werden. Auch die in den Datentabellen enthaltenen Formeln und Funktionen müssen auf den veränderten Datenbereich angepasst werden, eine häufige Quelle für Fehler.
Um solche Fehler zu vermeiden, wird ein Automatismus benötigt, der in den Formeln und Funktionen dynamisch den Zellbezug für den Datenbereich erkennt und anpasst. Bis zur Version Excel 2003 habe ich dafür die Funktion BEREICH.VERSCHIEBEN() genutzt. Mit Excel 2003 war diese Lösung obsolet, denn mit dieser Excel-Version wurde die „Liste“, seit Excel 2007 „Tabelle“ genannt, eingeführt. Tabelle ist ein als solcher definierter Bereich auf einem Tabellenblatt mit eindeutigen Spaltenüberschriften. Diese „Tabelle“ verhält sich wie eine Datenbank, d.h. Berechnungen und Bezüge werden automatisch an die Anzahl der Datensätze (in Excel = Zeilen) angepasst. Aus diesem Grund habe ich dieser Tabelle die Bezeichnung "Intelligente Tabelle gegeben“, um sie begrifflich von einer „normalen“ (=dummen) Tabelle in Excel zu unterscheiden.
„Intelligente“ Tabellen einrichten
Jede Excel-Tabelle/Liste, jeder Bereich lässt sich als "Intelligente Tabelle“ einrichten. Dazu benötigen Sie für jede Spalte Überschriften in der ersten Zeile des Datenbereichs. Über das Menü Einfügen ● Tabellen ● Tabelle oder den Short Cut <Strg> + <T> können Sie diesen Bereich als Tabelle definieren.
Sobald Sie diesen Befehl verwendet haben, wird auf den Definierten Bereich ein automatisches Tabellenformat mit gebänderten Zeilen angewendet sowie Filter in die Spaltenköpfe eingefügt. Beginnen Sie danach eine Eingabe in eine Zelle unmittelbar unterhalb oder neben der „Intelligenten Tabelle“, werden alle Attribute automatisch auf die neue Zeile oder Spalte übertragen. Im Menü gibt es für die "Intelligente" Tabelle ein zusätzliches Register (Entwurf / Tabellenentwurf abhängig von der Excel-Version), in dem Sie Eigenschaften für die Tabelle definieren können.
Strukturierte Verweise anlegen
Geben Sie eine Formel/Funktion in eine Zelle ein, wird diese bis zum letzten Datensatz (=Zeile) ergänzt (=kopiert), so dass Sie hier eine große Sicherheit bei der Vollständigkeit und Korrektheit Ihrer Berechnungen haben. Auffällig ist hier die andere Schreibweise von Zellbezügen (Strukturierte Verweise) =Tabellenname[Spaltenbezeichnung]
Ein strukturierter Verweis besteht aus folgenden Komponenten:
- Tabellenname: Der Name der intelligenten Tabelle.
- Spaltenbezeichnung: Der Name der Spalte innerhalb der Tabelle.
- Spezialbezeichnungen: Bezeichnungen für spezielle Teile der Tabelle, wie z.B. Kopfzeilen, Gesamtsummen usw.
Das entspricht der Adressierung von Spalten in der Datenbanksprache SQL.
Durch diese datenbankähnlichen Eigenschaften eignet sich die „Intelligente“ Tabelle hervorragend für den in Beitrag "Optimale Modellierung von Excel-Dateien" vorgeschlagenen Modellaufbau und dort speziell für die Eingabeebene. Mit den Fähigkeiten der „Intelligenten“ Tabelle können sehr gut Daten in die Verarbeitungsebene übertragen werden. Für den Einsatz von Excel im Controlling halte ich dieses Feature für sehr wichtig und sollte in das Repertoire eines jeden Controllers aufgenommen werden! Wenn man mit Power Query arbeitet, ist das Ergebnis des Datenimports immer eine "Intelligente Tabelle"!
Trotz gewisser Ähnlichkeiten mit einer Datenbank und den zunehmenden Fähigkeiten mit großen Datenmengen arbeiten zu können, ist Excel dennoch kein Datenbanksystem und sollte bestenfalls als Frontend für eine Datenbank genutzte werden! Wie das automatisiert eingerichtet werden kann, wird im nächsten Beitrag am Feature Power Query gezeigt.
Ist Excel noch zeitgemäß? Aber ganz sicher mit den Prinzipen des Modern Excel!
Das könnte Sie auch interessieren:
Nicht Excel ist das Problem, sondern die Anwender!
Von Excel zu Power BI: Warum der Umstieg für Controller sinnvoll sein kann