Heute möchte ich eine meine Lieblingsformel Tricks für eine Datenanalyse mit Euch teilen.
Ausgangssituation: Ich möchte in Excel eine Auswertung über Daten machen, welche sich über mehrere Datenblätter oder auch sogar Dateien verteilen. Genauer, ich möchte aus einer Tabelle einen bestimmten Wert auslesen, nämlich einen Umsatztreiber. Pro Gesellschaft gibt es ein Tabellenblatt, alle sind gleich strukturiert und ich möchte nun in einer Übersicht in einem separaten Tabellenblatt erstellen. Ich muss also immer die gleiche Adresse aus verschieden Tabellenblättern auslesen und für Vergleiche darstellen.
So sieht meine Tabelle aus mit den zugehörigen Formeln:
Eine wichtige Voraussetzung ist zunächst, dass alle Input Tabellen gleich strukturiert sind – dann nämlich kann ich immer die gleiche Verweisformel verwenden und muss nur den Bezug auf das Tabellenblatt ändern.
Hier zum Beispiel meine Input Datei (oder Tabelle) „Land1“ mit einem Aufriss der Umsatztreiber nach Quartal:
Im einfachsten Fall kann ich eine SVerweis Formel nutzen und mit „Suche und Ersetzen“ pro Spalte das Bezugsblatt ändern.
Bei vielen Blättern kann das zum einen eine zeitraubende Aufgabe sein und zum anderen ist diese Methode auch fehleranfällig. Besser ist es daher, die Bezugsquelle indirekt anzusprechen. Hierzu hilft eine Kombination der Formel „Indirekt“ mit „Verketten“.
Mit „Indirekt“ kann man einen festen Zellbezug ansprechen, mit Verketten kann man 2 Zellen zu einer verbinden. Damit kann man „dynamische“ Verweise nutzen.
In meinem Beispiel lautet die Input Tabellenblätter Land1, Land2,… Hier hilft es ungemein, wenn die Tabellenblätter eine durchlaufende Nummerierung haben und einen festen Namensbestandteil. Warum? Mit der Kombination aus Indirekt und Verketten kann man wunderbar einen Verweis auf ein Tabellenblatt nachbauen.
Und das meine ich damit: Gäbe es keine SVerweis Formel etc müsste man ja für jeden Wert einen separaten Link erstellen, z.b. =Land1!D5 und dann =Land1!D6 etc und dann das gleiche nochmal für Land2.
Diese Formel =Land1!D5 lässt sich nun „nachbauen“: mit =INDIREKT() geben wir den Bezug an. Den Bezug erstellen wir duch VERKETTEN. Man kann nämlich die Bestandteile der Formel einfach auseinandernehmen. Zunächst zu den Tabellenblätternamen: ich schreibe über jede Spalte in der Übersicht eine fortlaufende Nummer. In der ersten Spalte schreibe ich in die Zelle nur „Land“. Nun kann ich schon mal mit der Verkettenfunktion eine einfache Formel erstellen =VERKETTEN(B24;C24) fügt in meinem Beispiel Land und 1 zusammen. =VERKETTEN(B24;D24) dann entsprechend Land2. Damit sich die Formel gut über Bereiche kopieren lässt stelle ich „Land“ immer fest und die Zeile der Ländernummern auch. Also =VERKETTEN($B$24;D$24). Damit kann ich die Formel nun über Bereiche runter und rüber kopieren und es bleibt immer Land und die laufende Nummer. Um die Bezugsformel komplett nachzubauen brauche ich noch das Ausrufezeichen hinter dem Tabellennamen und den Bereich oder die Zelladresse die angegeben werden soll. Also erstmal aus =Land1!D5 eine Verketten Formel machen: =VERKETTEN($B$24;C$24;“!“;“D5″). Wichtig ist, das Ausrufezeichen in Anführungszeichen zu setzen. Als nächstes kann man die Zieladresse „D5“ im Blatt Land2 noch dynamisch aufbauen: Am Ende möchte ich nämlich 10 verschiedene Umsatztreiber auflisten in meiner Übersicht, die natürlich alle untereinander stehen, sprich alle eine andere Zeilennummer haben. Dafür schreibe ich mir neben die Umsatztreiber die jeweilige Zeilennummer, in meinem Beispiel stehen die Werte in Spalte A ab Zeile 29. Dort steht z.B. „5“. In Zelle D25 habe ich die Zielspalte notiert „D“. Nun kann ich also die Verkettenformel erweitern auf =VERKETTEN($B$24;C$24;“!“;$D$25;$A29). Diese Formel gibt aus als Text „Land1!D5″. Kopiere ich die Formel nun einfach 3 Zellen weiter nach unten und eine nach rechts, ändert sich die Formel auf =VERKETTEN($B$24;D$24;“!“;$D$25;$A32) und gibt aus =Land2!D8
Das Ergebnis der VERKETTEN Formel ist eine reine Textausgabe. Um aus Text eine logische Formel zu machen braucht man nun =INDIREKT(). Wenn ich eingebe =INDIREKT(VERKETTEN($B$24;C$24;“!“;$D$25;$A29)) ist die Ausgabe der Wert, der in der Zelle Land1!D5 steht. Diese Formel kann ich nun munter kopieren um viele Werte aus verschiedenen Tabellen mit gleicher Struktur gegenüberstellen zu können. Bei vielen Werten und mehreren Tabellen geht das nicht nur schneller es vermeidet auch Flüchtigkeitsfehler die mit „Suche und Ersetzen“ passieren können.
Die Werte kann ich nun über die Zelle D25 steuern, in dieser steht dass immer auf Zielspalte „D“ gegangen werden soll, in meinem Beispiel ist das Quartal3. Möchte ich nun die Werte für Quartal4 haben ändere ich einfach den Wert auf „E“.
Was ist der Vorteil gegenüber der SVERWEIS Formel? Mit dieser Formel suche ich ein bestimmtes Attribut und lassen mir in einem bestimmten Bereich einen Wert dafür ausgeben. In meinem Beispiel würde ich also Umsatztreiber1 in Land1 und Land2 suchen und dann den Wert für das gesuchte Quartal ausgeben lassen. Die Formel sieht folgendermaßen aus: =SVERWEIS($B29;Land1!$A$5:$F$14;4)
Wenn ich diese Formel nun kopiere ändert sich zwar der Bezug (also z.B. Umsatztreiber2) es ändert sich aber nicht der Name der Zieltabelle und es ändert sich auch nicht die Spaltennummer, hier die vierte Spalte. Diese beiden Attribute müssen also mit „Suche und Ersetzen“ geändert werden, was anfällig ist für Fehler.
Was man aber machen kann, ist beide Formelansätze zu verbinden, wenn man z.B. nur einige Umsatztreiber auslesen möchte. Also z.B. bei SVERWEIS($B29;Land1!$A$5:$F$14;4) läßt sich das Land1! ersetzen durch einen Verketten Einsatz:
SVERWEIS($B29;indirekt(VERKETTEN($B$24;c$24;“!“;“$A$5:$F$14″));4)
Das scheint mir aber eine unnötige Verkomplizierung zu sein, wenn auch die Formel natürlich mächtig Eindruck macht.
Spannend wird es nur, wenn man die Daten, welche man auslesen will nicht in der gleichen Datei hat sondern in anderen Dateien. Auch dafür kann man wunderbar die INDIREKT VERKETTEN Funktion benutzen, natürlich auch wieder in der Kombi mit dem SVERWEIS. Dafür muss man dann den kompletten Link, z.B. =’C:\Temp\[land1.xlsx]Umsatz‘!$D$5 nachbauen mit der Verkettenfunktion.
Viel Spaß beim nachbauen und verwenden! Bin neugierig wo Ihr diese Formeln einsetzt!