EXCEL – Alternative zu “Spur zum Vorgänger” ist Direkte Zellbearbeitung nicht zulassen

Ein richtiger old school Trick ist die Deaktivierung der direkten Zellbearbeitung. Oftmals hat man komplexe Tabellen mit vielen Formeln und Zellberechnungen vor sich und weiß dann gar nicht mehr genau, woher die einzelnen Werte kommen. Beim selbererstellen einer Tabelle macht es definitiv Sinn ab und an mal zu checken ob eine Formel sich auf die gewünschten Zellen bezieht. Klassischerweise wird dazu der “Detektiv” verwendet, unter Excel 2010 steht das etwas nüchterner im Reiter “Formeln” im Bereich “Formelüberwachung” und nennt sich z.B. “Spur zum Vorgänger”. Diese Funktion zeigt dann per blauer Linie alle Zellen auf, die in die aktuelle Zelle einfließen in die Berechnung.  Hat man eine “fremderstellte” Tabelle vor sich, ist das eine sehr hilfreiche Funktion um die Struktur und Funktionsweise der Tabelle besser zu verstehen.

Manchmal die schnellere und effizientere Hilfe ist mein folgender Tipp: Schalten Sie in den Option die direkte Zellbearbeitung aus. Dafür müssen Sie unter dem “Datei” Reiter links die Optionen auswählen, dann im Bereich “Erweitert” das 5. Häkchen von oben entfernen (“Direke Zellbearbeitung zulassen”).

Excel Option Zellbearbeitung zulassen

Was passiert? Durch die direkte Zellbearbeitung sieht man beim klick auf die Zelle den Zellinhalt welchen man dann in der Tabelle “direkt” bearbeiten kann. Durch die Deaktivierung bearbeitet man den Zelleninhalt nur noch in der Funktionsleiste oben. Dafür hat man diesen Vorteil: durch drücken der F2 Taste wenn man in der Zelle ist, welche man analysieren möchte, startet nicht nur die Bearbeitung sondern es werden Farbig alle Zellen angezeigt die in die Formel einfließen. Man kann auch statt F2 einfach mit der linken Maustaste in die Zelle klicken. Das geht oftmals schneller als der Detektiv und hat noch einen weiteren Vorteil: die Zellen, die in die Formel einfließen lassen sich einfach per Maus verschieben, man muss nicht händisch etwas ändern.

Steht z.B. in der Formel =A1+N1drin, eigentlich wollte man aber A1+B1 so kann man einfach mit der Maus das Kästchen von N1 auf B1 verschieben. Eine sehr hilfreiche Funktion wenn man seine Exceltabelle überprüfen möchte.

 

Hilfreiche Anleitung für Excel – SVERWEIS aufpeppen mit INDIREKT VERKETTEN

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:

Excel: Formel INDIREKT und VERKETTEN

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.

Um meine Ideen besser nachvollziehen zu können habe ich hier die 3 besprochenen Excel Dateien für den kostenlosen Download bereitgestellt:

indirekt verketten land1 land2

Viel Spaß beim nachbauen und verwenden! Bin neugierig wo Ihr diese Formeln einsetzt!