Como posso retornar o penúltimo valor de texto de uma linha?

Como posso retornar o penúltimo valor de texto de uma linha?

Alguém poderia me dizer a fórmula do Excel para retornar o penúltimo valor de texto consecutivo?

Consegui encontrar esta fórmula para o último valor do texto: =INDEX(U2:Y2,MATCH(REPT("z",255),U2:Y2)), mas não tenho certeza de como obtenho o penúltimo valor?

Responder1

A FÓRMULA

Aqui está um método alternativo para retornar ono valor do texto a partir do final.

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

Esta fórmula deve ser inserida como uma fórmula de matriz usando Ctrl+ Shift+ Enterem vez de apenas Enter. Você saberá que fez certo se chaves { } aparecerem em ambas as extremidades.


COMO FUNCIONA

=IFERROR(...,"")retornará em branco se o restante da fórmula for um erro. Você receberá um erro `#VALUE!# se não houver pelo menosnvalores de texto no intervalo.

INDEX(U2:Y2,...)pega o array e retorna algum valor dentro de in. INDEX(U2:Y2,4)retornaria o quarto elemento do array que, neste caso, é o que quer que esteja in X2.

LARGE(..,2)pega algum array e retorna o segundo maior valor desse array. Mude 2para qualquer outro número para obter ono maior item. A SMALLfunção é semelhante, mas retorna onºmenorvalor em vez disso.

ISTEXT(U2:Y2)*COLUMN(U2:Y2)é a parte que faz você inseri-lo como uma fórmula de matriz. A ISTEXTfunção retornará uma matriz de valores TRUE/ FALSEpara saber se cada célula contém um valor de texto. Por exemplo, isso poderia ser {TRUE,TRUE,FALSE,FALSE,TRUE}. As COLUMNfunções retornam o número da coluna. Neste caso, será {21,22,23,24,25}. Esses dois são multiplicados ( TRUE=1, FALSE=0). Para meu exemplo, o array final seria {21,22,0,0,25}. Inserir isso na LARGEfórmula anterior retornaria 22porque é o segundo maior valor.

-COLUMN(U2)+1simplesmente ajuste o valor que está sendo inserido na INDEXfórmula para levar em conta o fato de que estamos começando na coluna 22, mas a matriz U2:Y2tem apenas 5 colunas. Queremos retornar um valor entre 1e 5, não 21e 25.


EXEMPLO

Digamos que você tenha os seguintes dados no intervalo U2:Y2:

Hello | World | meep | 5 | boop

O penúltimo valor do texto é meepentão vamos ver como a fórmula funciona. Vou preencher os cálculos um passo de cada vez. Você pode ver uma execução semelhante usando "Avaliar Fórmula" na faixa "Fórmulas", embora possa não estar na mesma ordem.

=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"

Responder2

Suspeito que sua metodologia possa não ser a mais eficiente... mas usando o que você já conseguiu, você poderia utilizar o que já existe MATCH,subtrair umpara fornecer o novo limite para pesquisa e retornar o último texto dessa nova área. O OFFSETmétodo permitiria redefinir a área de pesquisa com seu novo limite, assim:

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

Responder3

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

informação relacionada