Como faço para obter uma referência à última ocorrência de um valor em uma matriz no Excel?

Question

Então, se

A1: A5 = {3, 3, 4, 4, 5}

que função posso colocar em B1 para obter uma referência a A4 (a célula que contém a última ocorrência de 4)?

    

Answer 1

Estou assumindo que A1: A5 são classificados em ordem crescente. Se for esse o caso, você pode usar a seguinte fórmula:

=ADDRESS(ROW(A1)-1+MATCH(4,A1:A5,1),COLUMN(A1))

Isso dará uma saída de $ A $ 4.

Veja como funciona:

MATCH(4,A1:A5,1) encontra o índice do maior valor que é < = 4, supondo que A1: A5 esteja classificado em ordem crescente. O que isso realmente significa é que ele encontra o primeiro valor maior do que 4 e simplesmente retorna o índice antes desse índice.

ADDRESS(row,col) converte um número de linha e um número de coluna em uma referência de célula. Para o número da coluna, simplesmente usei a coluna da lista: COLUMN(A1) . Para o número da linha, usei o índice retornado da função MATCH como um deslocamento desde o início da lista ( ROW(A1)-1 ). Você poderia omitir ROW(A1)-1 e ele ainda funcionaria nesse caso, mas falharia assim que sua lista fosse iniciada em outro lugar que não a linha 1.

Note que para usar este valor de referência em outro lugar, você precisará usar: INDIRECT(B1) .

    

Answer 2

Você pode usar uma fórmula de matriz para encontrar o MAX das linhas

{=INDEX(A1:A5,MAX((A1:A5=4)*(ROW(A1:A5))),1)}

Entre com controle + shift + enter. A parte MAX retornará o maior número de linha de todas as células que contêm um '4'. Isso pode ser tudo o que você precisa, mas envolvi isso em uma função INDEX que aponta para a célula no caso de você precisar dessa etapa extra.

    

Answer 3

supondo que sua matriz está sendo executada de uma correspondência de valores de coluna, sugiro que você apenas inverta os valores da coluna para que os valores na coluna 5 da matriz agora estejam na coluna 1 e, em seguida, use min (). Obter a primeira ocorrência é sempre mais fácil que a última.

    

Answer 4

Há uma explicação aqui (procure por "F94"), mas esteja avisado, não é bonito!