Ich habe Probleme, eine Lösung zu finden, die es mir ermöglicht, dieselbe Formel zu kopieren, aber die Zeilenanzahl der Tabellen in Schritten von 15 zu erhöhen. Bei meinem ersten Versuch habe ich einfach versucht, manuell drei entsprechende Formeln zu erstellen, gefolgt von der gesamten Hand, in der Hoffnung, dass das Muster erkannt wird, aber ich hatte kein Glück.
Ich suche nach einer Ausgabe wie dieser:
=MAX(VLOOKUP($A2,Sheet2!$A2:$B17,2,0),VLOOKUP($A2,Sheet2!$E2:$F17,2,0),VLOOKUP($A2,Sheet2!$I2:$J17,2,0))
=MAX(VLOOKUP($A2,Sheet2!$A17:$B32,2,0),VLOOKUP($A2,Sheet2!$E17:$F32,2,0),VLOOKUP($A2,Sheet2!$I17:$J32,2,0))
=MAX(VLOOKUP($A2,Sheet2!$A32:$B47,2,0),VLOOKUP($A2,Sheet2!$E32:$F47,2,0),VLOOKUP($A2,Sheet2!$I32:$J47,2,0))
Ich bin ziemlich unerfahren, daher ist jeder Ratschlag willkommen. Vielen Dank im Voraus.
Antwort1
OFFSETwäre die Funktion, die die meisten Leute für diese Option bevorzugen würden. Es handelt sich jedoch um eine volatile Funktion. Volatile Funktionen sind nicht das Ende der Welt. Es bedeutet nur, dass sie jedes Mal neu berechnet werden, wenn sich eine Zelle auf dem Blatt ändert, selbst wenn diese Zelle keinen Einfluss auf die volatile Formel hat. Normale Formeln werden nur neu berechnet, wenn sich etwas ändert, das sie beeinflusst. Daher können Sie durch die Verwendung volatiler Funktionen viele zusätzliche Berechnungen verursachen. Insbesondere, wenn es sich um etwas handelt, das kopiert und in vielen Zellen wiederverwendet wird.
Die gleichen Ergebnisse wie bei OFFSETkönnen mit erreicht werden, INDEXwobei es sich um eine reguläre Formel (nicht flüchtig) handelt. INDEXEs wird häufig angenommen, dass der Wert in einer bestimmten Zeile (1D-Bereich) und Spalte (2D-Bereich) zurückgegeben wird. Was INDEXtatsächlich zurückgegeben wird, ist die Zelladresse, die dann den Wert aus dieser Adresse abruft. Mit dieser Adressrückgabe können Sie den Startpunkt eines Bereichs mit einer INDEXFunktion definieren und dann mit einer anderen INDEXFunktion das Ende des Bereichs definieren. Trennen Sie die beiden INDEXFunktionen mit einem : und Sie haben jetzt einen Bereich!
Sehen wir uns nun die Mathematik zur Bestimmung des Zeilenmusters an. Formel 1 beginnt in Zeile 2, Formel 2 beginnt in Zeile 17, Formel 3 beginnt in Zeile 32 … und so weiter. Das Muster hier ist also im Wesentlichen (Formel Nr. -1)*15+2. Jetzt müssen wir nur noch einen Zähler entwickeln, der um eins erhöht wird, wenn die Formel nach unten kopiert wird. Sie können eine Spalte mit 1, 2, 3 usw. füllen oder stattdessen einfach verwenden ROW(A1). Beim ersten Verwenden irgendwo wird 1 zurückgegeben. Beim Kopieren nach unten wird 2 zurückgegeben, dann 3 usw. Ein perfekter kleiner Zähler. (Hinweis: Column(A1)Kann zum horizontalen Zählen verwendet werden.)
Wie bereits erwähnt, INDEXhat es folgende Form:
INDEX(SELECT RANGE, ROW in SELECT RANGE, COLUMN in SELECT RANGE)
Ein paar Anmerkungen:
Die Zeilen- und Spaltennummer beziehen sich auf den ausgewählten Bereich und stimmen nicht mit dem Arbeitsblatt überein, es sei denn, der ausgewählte Bereich beginnt bei einem 2D-Bereich in A1 oder bei einem 1D-Bereich in Zeile 1 oder Spalte A.
In einem 1D-Auswahlbereich ist die Spaltennummer nicht erforderlich, nur die Zeilennummer. Wenn der Auswahlbereich horizontal ist, ist die Zeilennummer tatsächlich die Spaltennummer.
Wenn als Zeilen- oder Spaltennummer 0 eingegeben wird, interpretiert INDEX dies als Rückgabe der Eingabezeile oder -spalte des ausgewählten Bereichs.
Kommen wir zurück zum Erstellen der Formel. Beginnen wir damit, den Startpunkt des Bereichs zu finden. In diesem Fall möchten wir also den Index anweisen, A2, A17, A32 usw. zu finden.
=INDEX($A:$A,(ROW(A1)-1)*15+2)
Und um den Endpunkt des Bereichs B17, B32, B47 zu finden, würde die Formel ungefähr so aussehen:
=INDEX($B:$B,ROW(A1)*15+2)
Wenn Sie nun beides kombinieren, um Ihren Bereich zu definieren, sähe die Formel folgendermaßen aus:
=INDEX($A:$A,(ROW(A1)-1)*15+2):INDEX($B:$B,ROW(A1)*15+2)
Nun, diese Formel allein sieht nicht besonders aus, da Sie nicht mehr als den Wert einer Zelle in eine einzelne Zelle eingeben können. Sie funktioniert jedoch für Ihre Nachschlageformeln. Wenn Sie also die Bereichsgleichung in Ihre ursprüngliche Formel einsetzen, erhalten Sie:
=MAX(VLOOKUP($A2,INDEX(SHEET2!A:A,(ROW(A1)-1)*15+2):INDEX(SHEET2!B:B,ROW(A1)*15+2),2,0),VLOOKUP($A2,INDEX(SHEET2!E:E,(ROW(A1)-1)*15+2):INDEX(SHEET2!F:F,ROW(A1)*15+2),2,0),VLOOKUP($A2,INDEX(SHEET2!I:I,(ROW(A1)-1)*15+2):INDEX(SHEET2!J:J,ROW(A1)*15+2),2,0))