
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 2
para qualquer outro número para obter ono maior item. A SMALL
funçã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 ISTEXT
função 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}
. As COLUMN
funçõ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 LARGE
fórmula anterior retornaria 22
porque é o segundo maior valor.
-COLUMN(U2)+1
simplesmente ajuste o valor que está sendo inserido na INDEX
fórmula para levar em conta o fato de que estamos começando na coluna 22, mas a matriz U2:Y2
tem apenas 5 colunas. Queremos retornar 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 penúltimo valor do texto é meep
entã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 OFFSET
mé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)