☰

Wie kann ich den vorletzten Textwert aus einer Zeile zurückgeben?

tag-icon • tag-icon microsoft-excel worksheet-function
Wie kann ich den vorletzten Textwert aus einer Zeile zurückgeben?

Kann mir jemand die Excel-Formel nennen, um den vorletzten Textwert in einer Zeile zurückzugeben?

Ich habe diese Formel für den letzten Textwert gefunden:, =INDEX(U2:Y2,MATCH(REPT("z",255),U2:Y2))bin mir aber nicht sicher, wie ich den vorletzten Wert erhalte?

Antwort1

DIE FORMEL

Hier ist eine alternative Methode zur Rückgabe derNte Textwert vom Ende.

=IFERROR(INDEX(U2:Y2,LARGE(ISTEXT(U2:Y2)*COLUMN(U2:Y2),2)-COLUMN(U2)+1),"")

Diese Formel muss als Matrixformel mit Ctrl+ Shift+ Enterund nicht nur mit eingegeben werden Enter. Sie wissen, dass Sie es richtig gemacht haben, wenn an beiden Enden geschweifte Klammern { } erscheinen.

WIE ES FUNKTIONIERT

=IFERROR(...,"")gibt leer zurück, wenn der Rest der Formel ein Fehler ist. Sie erhalten einen Fehler `#VALUE!#, wenn nicht mindestensNTextwerte im Bereich.

INDEX(U2:Y2,...)nimmt das Array auf und gibt einen Wert innerhalb von zurück. INDEX(U2:Y2,4)würde das vierte Element im Array zurückgeben, das in diesem Fall das ist, was in steht X2.

LARGE(..,2)nimmt ein Array auf und gibt den zweitgrößten Wert aus diesem Array zurück. Ändern Sie den Wert 2in eine beliebige andere Zahl, um den Wert zu erhalten.Ngrößtes Element. Die SMALLFunktion ist ähnlich, gibt aber dasNthkleinsteWert stattdessen.

ISTEXT(U2:Y2)*COLUMN(U2:Y2)ist der Teil, der Sie dazu veranlasst, es als Arrayformel einzugeben. Die ISTEXTFunktion gibt ein Array von TRUE/ FALSEWerten zurück, die angeben, ob jede Zelle einen Textwert enthält. Dies könnte beispielsweise sein {TRUE,TRUE,FALSE,FALSE,TRUE}. Die COLUMNFunktion gibt die Spaltennummer zurück. In diesem Fall wäre dies {21,22,23,24,25}. Diese beiden werden multipliziert ( TRUE=1, FALSE=0). In meinem Beispiel wäre das endgültige Array {21,22,0,0,25}. Wenn Sie dies in die Formel von vorhin einsetzen, LARGEwürden Sie zurückgeben 22, da dies der zweitgrößte Wert ist.

-COLUMN(U2)+1Passen Sie einfach den in die Formel eingesetzten Wert an, INDEXum der Tatsache Rechnung zu tragen, dass wir in Spalte 22 beginnen, das Array aber nur 5 Spalten hat. Wir möchten einen Wert zwischen und U2:Y2zurückgeben , nicht zwischen und .152125

BEISPIEL

Nehmen wir an, Sie hätten die folgenden Daten im Bereich U2:Y2:

Hello | World | meep | 5 | boop

Der vorletzte Textwert lautet meepalso: Sehen wir uns an, wie die Formel funktioniert. Ich werde die Berechnungen Schritt für Schritt ausfüllen. Sie können einen ähnlichen Durchlauf davon sehen, indem Sie „Formel auswerten“ auf der Registerkarte „Formeln“ verwenden, obwohl die Reihenfolge möglicherweise nicht dieselbe ist.

=IFERROR(INDEX(U2:Y2,LARGE(ISTEXT(U2:Y2)*COLUMN(U2:Y2),2)-COLUMN(U2)+1),"")
=IFERROR(INDEX(U2:Y2,LARGE(ISTEXT(U2:Y2)*COLUMN(U2:Y2),2)-21+1),"")
=IFERROR(INDEX(U2:Y2,LARGE(ISTEXT(U2:Y2)*COLUMN(U2:Y2),2)-20),"")
=IFERROR(INDEX(U2:Y2,LARGE({TRUE,TRUE,TRUE,FALSE,TRUE}*COLUMN(U2:Y2),2)-21),"")
=IFERROR(INDEX(U2:Y2,LARGE({TRUE,TRUE,TRUE,FALSE,TRUE}*{21,22,23,24,25},2)-21),"")
=IFERROR(INDEX(U2:Y2,LARGE({21,22,23,0,25},2)-21),"")
=IFERROR(INDEX(U2:Y2,23-21),"")
=IFERROR(INDEX(U2:Y2,2),"")
=IFERROR(INDEX({"Hello","World","meep",5,"boop"},2),"")
=IFERROR("meep","")
="meep"

Antwort2

Ich vermute, dass Ihre Methodik nicht die effizienteste ist... aber mit dem, was Sie bereits erreicht haben, könnten Sie Ihre vorhandenen nutzen MATCH,subtrahiere einsum Ihnen die neue Suchgrenze zu geben und den letzten Text aus diesem neuen Bereich zurückzugeben. Mit dieser OFFSETMethode können Sie den Suchbereich mit Ihrer neuen Grenze wie folgt neu definieren:

=INDEX(U2:Y2,MATCH(REPT("z",255),OFFSET(U2,0,0,1,MATCH(REPT("z",255),U2:Y2)-1)))

Antwort3

=INDEX(D7:P7,MATCH(REPT("z",255),D7:P7)-1)

verwandte Informationen