Laden Sie die Beispielarbeitsmappe herunter
Wir haben in anderen Artikeln darüber gesprochen, dass es Funktionen wie OFFSET und INDIRECT gibt, die volatil sind. Wenn Sie viele davon in einer Tabellenkalkulation verwenden oder viele Zellen von flüchtigen Funktionen abhängig sind, kann Ihr Computer jedes Mal, wenn Sie versuchen, eine Zelle zu ändern, eine beträchtliche Zeit damit verbringen, Neuberechnungen durchzuführen. Anstatt frustriert zu sein, dass Ihr Computer nicht schnell genug ist, werden in diesem Artikel alternative Wege zur Lösung der häufigen Situationen untersucht, in denen Benutzer OFFSET und INDIRECT verwenden.
Ersetzen von OFFSET, um eine dynamische Liste zu erstellen
Nachdem Sie sich mit der OFFSET-Funktion vertraut gemacht haben, ist es ein weit verbreiteter Irrtum, dass dies die einzige Möglichkeit ist, ein Ergebnis mit dynamischer Größe mit den letzten paar Argumenten zurückzugeben. Schauen wir uns eine Liste in Spalte A an, in der sich unser Benutzer später entscheiden könnte, zusätzliche Elemente hinzuzufügen.
Um eine Dropdown-Liste in Zelle C2 zu erstellen, können Sie einen benannten Bereich mit einer flüchtigen Formel wie definieren
=OFFSET($A$2, 0, 0, ANZAHLA($A:$A)-1, 1)
Mit dem aktuellen Setup würde dies sicherlich einen Verweis auf den Bereich A2:A5 zurückgeben. Es gibt jedoch eine andere Möglichkeit, den nichtflüchtigen INDEX zu verwenden. Um dies zu tun, denken Sie darüber nach, dass wir einen Verweis auf den Bereich von A2 bis A5 schreiben. Wenn Sie „A2:A5“ schreiben, stellen Sie sich dies nicht als einzelnes Datenelement vor, sondern als „StartingPoint“ und „EndingPoint“, die durch einen Doppelpunkt getrennt sind (z. B. StartingPoint:EndingPoint). In einer Formel können sowohl der StartingPoint als auch der EndingPoint das Ergebnis anderer Funktionen sein.
Hier ist die Formel, die wir verwenden werden, um den dynamischen Bereich mit der INDEX-Funktion zu erstellen:
=$A$2:INDEX($A:$A, ANZAHLA($A:$A))
Beachten Sie, dass wir angegeben haben, dass der Startpunkt für diesen Bereich immer A2 ist. Auf der anderen Seite des Doppelpunkts verwenden wir INDEX, um zu bestimmen, wo der EndingPoint sein soll. Der ANZAHLA bestimmt, dass in Spalte A 5 Zellen mit Daten vorhanden sind, und daher erstellt unser INDEX einen Verweis auf A5. Die Formel wird also wie folgt ausgewertet:
=$A$2:INDEX($A:$A, ANZAHLA($A:$A)) =$A$2:INDEX($A:$A, 5) =$A$2:$A5
Mit dieser Technik können Sie mit der INDEX-Funktion dynamisch einen Verweis auf eine beliebige Liste oder sogar eine zweidimensionale Tabelle erstellen. In einer Tabellenkalkulation mit einer Fülle von OFFSET-Funktionen kann Ihr Computer viel schneller starten, wenn Sie die OFFSETs durch INDEX ersetzen.
Ersetzen von INDIREKT für Blattnamen
Die Funktion INDIREKT wird häufig aufgerufen, wenn Arbeitsmappen mit Daten entworfen wurden, die über mehrere Arbeitsblätter verstreut sind. Wenn Sie nicht alle Daten auf ein einzelnes Blatt bringen können, aber keine flüchtige Funktion verwenden möchten, können Sie möglicherweise CHOOSE verwenden.
Betrachten Sie das folgende Layout, in dem wir Verkaufsdaten in 3 verschiedenen Arbeitsblättern haben. Auf unserem Übersichtsblatt haben wir ausgewählt, aus welchem Quartal wir die Daten anzeigen möchten.
Unsere Formel in B3 lautet:
=WÄHLEN(MATCH(B2, D2:D4, 0), Herbst!A2, Winter!A2, Frühling!A2)
In dieser Formel bestimmt die MATCH-Funktion, welchen Bereich wir zurückgeben möchten. Dies teilt der CHOOSE-Funktion dann mit, welcher der folgenden Bereiche als Ergebnis zurückgegeben werden soll.
Sie können auch die CHOOSE-Funktion verwenden, um einen größeren Bereich zurückzugeben. In diesem Beispiel haben wir auf jedem unserer drei Arbeitsblätter eine Tabelle mit Verkaufsdaten.
Anstatt eine INDIRECT-Funktion zu schreiben, um den Blattnamen zu erstellen, können Sie CHOOSE bestimmen lassen, in welcher Tabelle die Suche durchgeführt werden soll. In meinem Beispiel habe ich die drei Tabellen bereits tbFall, tbWinter und tbSpring genannt. Die Formel in B4 lautet:
=SVERWEIS(B3, WÄHLEN(VERGLEICH(B2, D2:D4, 0), tbFall, tbWinter, tbSpring), 2, 0)
In dieser Formel bestimmt die MATCH, dass wir die 2nd Artikel aus unserer Liste. CHOOSE nimmt dann diese 2 und gibt die Referenz an tbWinter zurück. Schließlich kann unser SVERWEIS die Suche in der angegebenen Tabelle abschließen und feststellen, dass der Gesamtumsatz für Banana im Winter 6000 USD betrug.
=SVERWEIS(B3, CHOOSE(MATCH(B2, D2:D4, 0), tbFall, tbWinter, tbSpring), 2, 0) =SVERWEIS(B3, CHOOSE(2, tbFall, tbWinter, tbSpring), 2, 0) = SVERWEIS(B3, tbWinter, 2, 0) =6000
Diese Technik wird dadurch eingeschränkt, dass Sie die CHOOSE-Funktion mit allen Bereichen ausfüllen müssen, aus denen Sie möglicherweise einen Wert abrufen möchten, aber Sie haben den Vorteil, dass Sie eine volatile Formel vermeiden. Je nachdem, wie viele Berechnungen Sie durchführen müssen, kann sich diese Fähigkeit als sehr wertvoll erweisen.
