Formel zum Zurückgeben eines Werts aus einer Spalte, wenn die Bedingung zum ersten Mal in einer anderen erfüllt wird

Question

Beispiel:

Gegeben sei eine Tabelle mit vier Spalten – A, B, C und D – und Zeile 1 ist eine Kopfzeile …

A2=1
B2=(Zufällige positive Ganzzahl)
C2=(Eine andere zufällige positive Ganzzahl)

A3 = A2 + 1
B3 = B2 – C2
A4 = A3 + 1
B4 = B3 – C2

...

D2 ist die Problemzelle. In D2 möchte ich den Wert für Spalte A zurückgeben, der mit der ersten Instanz eines Werts in Spalte B übereinstimmt, der <=0 ist.

Answer 1

In ähnlicher Weise wie Pauls Antwort würde ich verwenden IndexundMatch

=INDEX($A:$A,MATCH(0,$B:$B,-1)+1)

1. Verwenden Sie ganze Spalten, sodass keine Anpassung an die Datenmenge in der Spalte erforderlich ist.
2. Verwenden Sie Indexanstelle von Offset, da Offsetes volatil ist und Indexnicht volatil ist. (Volatile Funktionen werden bei jeder Blattberechnung neu berechnet, während nicht-volatile Funktionen nur neu berechnen, wenn sich die referenzierten Daten ändern. Zu viele volatile Funktionen können Excel verlangsamen.)

Answer 2

Mit MATCH können Sie bekommen, was Sie brauchen.

MATCH(0,B2:B6,-1)+1sucht nach B2:B6dem ersten Wert, der gleich oder kleiner als die Zahl ist, 0und gibt seine relative Position zurück. Der Index beginnt bei Null, also fügen wir eins hinzu, damit er mit Ihrem Index übereinstimmt.

Die Indizierung ab Null bedeutet, dass Zeile B2 0, B3 1, B4 2 und B5 3 ist. Daher gibt MATCH „3“ zurück.

Sie haben Ihren eigenen Index in Spalte A, und wenn dieser nicht sequenziell wäre oder andere Werte enthalten würde oder nicht bei 1 beginnen würde, könnten Sie den Offset verwenden, um dorthin zu gelangen:

=OFFSET(A2,MATCH(0,B2:B6,-1),0)

Hier wird das Ergebnis von MATCH verwendet, um von A2 herunterzuzählen und den Wert an dieser Zeilenposition zu finden.

Answer 3

könnten Sie das nicht nur mit B2 und C2 berechnen?

=CEILING(B2/C2,1)+1

Gemäß meinem Kommentar in Chris‘ Antwort -aktualisierte Version

=INDEX($A:$A,MATCH(TRUE,INDEX($B:$B<=0,0),0))

Dadurch sollte das in Chris‘ Vorschlag inhärente Problem vermieden werden, dass Sie den falschen Wert erhalten, wenn der erste Wert <=0 selbst 0 ist.

Der Teil $B:$B<0gibt ein „Array“ von TRUE/FALSEWerten zurück, wobei der erste TRUEoffensichtlich mit dem ersten Wert in Spalte B <0 übereinstimmt. MATCH findet dann die Position dieser ersten Instanz und INDEX ruft dann den entsprechenden Wert aus Spalte A ab.

Der zweite INDEX ist nur dazu da, "Array-Einträge" zu vermeiden - es geht auch ohne, d.h.

=INDEX($A:$A,MATCH(TRUE,$B:$B<=0,0))

.......aber diese Version müsste „Array eingegeben“ werden – also mit bestätigt werden CTRL+SHIFT+ENTER.

Dies ist etwas ineffizienter als die vorherigen Vorschläge, bei denen die ganze Spalte verwendet wird (und funktioniert nicht in Excel 2003 oder früheren Versionen – in diesen Versionen müssen Sie einen bestimmten Bereich verwenden).

Beachten Sie, dass MATCH mit dem „Übereinstimmungstyp“ -1 gemäß Chris‘ Vorschlag absteigende Werte in Spalte B haben muss – diese Formel funktioniert unabhängig von der Reihenfolge der Spalte B.