Die OFFSET-Funktion von Excel verlangsamt die Berechnung Ihrer Arbeitsmappe. Es gibt eine bessere Alternative: eine ungewöhnliche Syntax von INDEX.
Dies ist ein Nischentipp. Es gibt eine erstaunlich flexible Funktion namens OFFSET. Es ist flexibel, da es auf einen Bereich unterschiedlicher Größe verweisen kann, der im laufenden Betrieb berechnet wird. Wenn im folgenden Bild jemand die Dropdown-Liste # Qtrs in H1 von 3 auf 4 ändert, stellt das vierte Argument von OFFSET sicher, dass der Bereich auf vier Spalten erweitert wird.
Spreadsheet-Gurus hassen OFFSET, weil es eine flüchtige Funktion ist. Wenn Sie zu einer völlig unabhängigen Zelle gehen und eine Zahl eingeben, werden alle OFFSET-Funktionen berechnet. Auch wenn diese Zelle nichts mit H1 oder B2 zu tun hat. In den meisten Fällen achtet Excel sehr darauf, nur Zeit mit der Berechnung der zu berechnenden Zellen zu verschwenden. Sobald Sie OFFSET einführen, werden jedoch nach jeder Änderung im Arbeitsblatt alle OFFSET-Zellen sowie alle Downlines aus dem OFFSET berechnet.
Ich habe das ModelOff-Finale 2013 in New York City beurteilt, als einige meiner Freunde aus Australien auf eine bizarre Problemumgehung hinwiesen. In der folgenden Formel steht vor der INDEX-Funktion ein Doppelpunkt. Normalerweise würde die unten gezeigte INDEX-Funktion den 1403 aus Zelle D2 zurückgeben. Wenn Sie jedoch einen Doppelpunkt auf beide Seiten der INDEX-Funktion setzen, wird anstelle des Inhalts von D2 die Zellenadresse D2 zurückgegeben. Das ist wild, dass es funktioniert.
Warum ist das wichtig? INDEX ist nicht volatil. Sie erhalten die flexible Flexibilität von OFFSET ohne die zeitraubenden Neuberechnungen immer wieder.
Diesen Tipp habe ich zuerst von Dan Mayoh in Fintega gelernt. Vielen Dank an Access Analytic für den Vorschlag dieser Funktion.
Schau Video
Video-Transkript
Lernen Sie Excel aus dem Podcast, Folge 2048 -: INDEX ersetzt ein flüchtiges OFFSET!
Hey, ich podcaste alle meine Tipps aus diesem Buch. Klicken Sie auf das "i" in der oberen rechten Ecke, um zur Wiedergabeliste zu gelangen!
Okay, OFFSET ist eine erstaunliche Funktion! Mit OFFSET können wir eine Zelle in der oberen linken Ecke angeben und dann mithilfe von Variablen definieren, wie viele Zeilen nach unten, wie viele Zeilen über und wie viele Formen in Ordnung sind. Wenn ich also hier addieren oder einen Durchschnitt von beispielsweise 3/4 machen möchte, wird diese Formel hier einen Blick auf die Formel werfen. Der OFFSET sagt, wir beginnen bei B2, beginnen bei Q1, gehen von 0 nach unten, über 0, die Form wird 1 Zeile hoch sein und es wird H1 sein, mit anderen Worten 3 Zellen breit, in Ordnung. In diesem Fall ändern wir also nur die Anzahl der Zellen, die wir addieren. Wir beginnen immer wieder mit B2 oder B3 oder B4, während ich nach unten kopiere, und entscheiden dann, wie viele Zellen breit sind. Okay, OFFSET ist diese coole Sache, es macht alle Arten von erstaunlichen Funktionen, aber hier ist der Ärger, es ist flüchtig!Das heißt, auch wenn sich etwas nicht in der Berechnungskette befindet, wird sich Excel die Zeit nehmen, es neu zu berechnen, was die Dinge in Ordnung verlangsamen wird.
Diese erstaunliche Version von INDEX, normalerweise, wenn ich nach dem INDEX dieser 4 Zellen 3 fragen würde, wird die Nummer 1403 zurückgegeben. Wenn ich jedoch einen Doppelpunkt vor oder nach dem INDEX setze, passiert etwas ganz anderes. Wir werden uns diese Formel hier ansehen. Also Index der 4 Zellen, welche will ich, ich will die dritte, aber Sie sehen, es gibt ein: genau dort. Wir gehen also immer von B2: E2 aus, und ich zeige Ihnen, ob wir zu Formeln gehen, Formel auswerten, in Ordnung. Hier wird also die Zahl 3 berechnet. Und hier der INDEX mit dem: next Anstatt uns 1403 zu sagen, wie INDEX normalerweise berechnen würde, wird es $ D2 zurückgeben, und dann wird der DURCHSCHNITT den Durchschnitt dieser 3 machen. Absolut erstaunlich, wie das funktioniert und der zusätzliche Nutzen, es ist nicht flüchtig, in Ordnung,und dies kann sogar verwendet werden, um ein unglaublich komplexes OFFSET zu ersetzen.
Hier mit diesem OFFSET basieren wir also auf diesen Werten. Wenn ich Q2 und Central wähle, verwenden diese Formeln MATCH und COUNTIF, um herauszufinden, wie viele Zeilen nach unten, wie viele Spalten darüber, wie hoch und wie breit. Und dann verwendet der OFFSET hier all diese Werte, um den Median herauszufinden. Wir werden nur einen Test machen, um sicherzustellen, dass es funktioniert, also = MEDIAN von diesem blauen Bereich da drüben, besser 71, okay, und wir werden hier etwas anderes wählen, Q3 und West, also. Drücken Sie F2. Ich ziehe dies einfach über die Größe und ändere die Größe, um diese Formel neu zu schreiben. Drücken Sie die Eingabetaste, und es funktioniert mit dem OFFSET.
Nun, hier, mit einem INDEX, habe ich tatsächlich einen Doppelpunkt in der Mitte mit einem INDEX auf der linken Seite und einem INDEX auf der rechten Seite. Beobachten Sie, wie dieses Ding in der Bewertungsformel berechnet wird. Es beginnt also mit Evaluieren, Evaluieren und genau hier wird INDEX es in eine Zellenadresse verwandeln. Also ist H16 der 1., West, Q3, und drüben auf der rechten Seite wird ausgewertet, mehr gekoppelt, und dann wird sie richtig zu I20. Also das coole, coole nichtflüchtige, um ein OFFSET mit der INDEX-Funktion zu ersetzen. Okay, dieser Tipp und viele mehr in diesem Buch, klicken Sie auf das "i" in der oberen rechten Ecke, um das Buch zu kaufen.
Okay, Fazit: OFFSET, fantastische, flexible Funktion, sobald Sie es beherrschen, können Sie alle möglichen Dinge tun. Zeigen Sie auf eine variable Zelle oben links, zeigen Sie auf einen Bereich, der eine variable Form hat, aber flüchtig ist, und berechnen Sie daher bei jeder Berechnung. Excel führt normalerweise eine intelligente Berechnung durch oder berechnet die zu berechnenden Zellen neu, aber mit OFFSET wird es immer berechnet. Anstelle von OFFSET können Sie INDEX: oder: INDEX oder sogar INDEX: INDEX verwenden. Immer wenn INDEX einen Doppelpunkt daneben hat, wird anstelle des Werts dieser Zelle eine Zellenadresse zurückgegeben. Und der Vorteil ist, dass INDEX nicht volatil ist!
OK, ich möchte mich bei Ihnen für Ihren Besuch bedanken. Wir sehen uns beim nächsten Mal für einen weiteren Netcast von!
Download-Datei
Laden Sie die Beispieldatei hier herunter: Podcast2048.xlsm
