Fórmula do Excel para obter o penúltimo valor em uma linha

Question

Imagine que eu tenha uma linha de dados no excel, assim:

     [A]     [B]   [C]   [D]   [E]   [F]   [G]   [H]
[1]  Data     5     8    12    32    15     9    89

Eu quero uma fórmula que retorne o valor 9 .

Em seguida, se eu adicionar um novo valor a I1 , ele deverá atualizar para retornar o valor 89 , que seria o novo penúltimo valor.

Idealmente, isso deve funcionar em intervalos arbitrários, e não apenas em linhas inteiras.

Pontos de bônus opcionais se o método puder retornar um deslocamento arbitrário do final da linha (digamos, retornar o quinto do último item).

    

Answer 1

Isso funciona tanto com texto quanto com números e também não importa se há células em branco, isto é, ele retornará a segunda para a última célula não vazia. Ele precisa ser inserido por matriz, o que significa que você pressiona ctrl-shft-enter depois de digitar ou colá-lo. Ajuste o último argumento para alterar o deslocamento:

=INDEX(1:1,LARGE((1:1<>"")*(COLUMN(1:1)),2))

    

Answer 2

Supondo que não há células vazias:

=INDEX(1:1,1,COUNT(1:1))

=INDEX(1:1,1,COUNTA(1:1)-1)

INDEX retorna um determinado valor dentro de um intervalo, especificando:

• array: um intervalo arbitrário ou a linha inteira (1: 1)
• row_num: o número da linha dentro do intervalo (1)
• column_num: o número da coluna dentro do intervalo. Essa é a parte complicada.

No meu exemplo, usei COUNT () para contar quantas entradas numéricas você tem na linha, que é 7. COUNTA () também inclui sua primeira linha não numérica "Data" e retorna 8, então você tem para subtrair 1.

Observe que isso não funcionará se alguma célula for deixada em branco. Em vez de deixar uma célula em branco, você poderia colocar um traço (-) para indicar uma entrada vazia e usar COUNTA ().

BÔNUS: Receba a 5ª última entrada. Fácil:

=INDEX(1:1,1,COUNT(1:1)-3) or INDEX(1:1,1,COUNTA(1:1)-4)

    

Answer 3

Isso pode aproximá-lo do que você deseja - o número negativo no final ajusta o deslocamento para a célula cujo valor é retornado.

=INDEX(A1:Z1,COUNTA(A1:Z1)-0)

Esta é uma fórmula de matriz, portanto, você precisa pressionar CTRL + SHIFT + ENTER para inseri-la

    

Answer 4

Você deve ser capaz de fazer algo assim:

=OFFSET(A1, 0, COUNTA(A1:I1) - 2)

Nota: Código não testado, pode gerar um erro. Além disso, se você quiser contar células com fórmulas que retornam vazias:

=OFFSET(A1, 0, COLUMNS(A1:I1) - COUNTBLANK(A1:I1) - 2)

Fonte de substituição COUNTA : link

O mesmo aviso acima.