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

Question

Alguém poderia me dizer a fórmula do Excel para retornar o segundo último valor de texto em uma linha?

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

    

Answer 1

A FÓRMULA

Aqui está um método alternativo para retornar o valor do texto n th do final.

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

Esta fórmula deve ser introduzida como uma fórmula de matriz usando Ctrl + Deslocar + Enter em vez de apenas Enter . Você saberá que fez isso corretamente se as chaves {} aparecerem nas duas extremidades.

COMO FUNCIONA

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

INDEX(U2:Y2,...) aceita a matriz e retorna algum valor dentro de. INDEX(U2:Y2,4) retornaria o 4º elemento na matriz que, nesse caso, é o que estiver em X2 .

LARGE(..,2) recebe uma matriz e retorna o segundo maior valor de dentro dessa matriz. Altere o 2 para qualquer outro número para obter o n th maior item. A função SMALL é semelhante, mas retorna o valor n th menor .

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

-COLUMN(U2)+1 simplesmente ajuste o valor que está sendo plugado na fórmula INDEX para considerar o fato de que estamos começando na coluna 22, mas a matriz U2:Y2 tem apenas 5 colunas. Queremos devolver um valor entre 1 e 5 , não 21 e 25 .

EXEMPLO

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

Hello | World | meep | 5 | boop

O valor do segundo para o último texto é meep , então vamos ver como a fórmula funciona. Eu vou preencher os cálculos um passo de cada vez. Você pode ver uma execução semelhante, embora 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"

    

Answer 2

Eu suspeito que sua metodologia pode não ser a mais eficiente ... mas usando o que você já conseguiu, você poderia utilizar seu MATCH existente, subtrair um para dar a você o novo limite pesquisar e retornar o último texto dessa nova área. O método OFFSET permitiria que você redefinisse 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)))

    

Answer 3

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