Excel - usando um endereço dinâmico em uma fórmula

Question

Como inserir um endereço de célula dinâmica em uma fórmula. Por exemplo, tenho dados em células intermitentes em uma linha. Eu preciso usar a fórmula "countblank" para contar o número de células em branco após a última célula onde há dados. Eu usei a fórmula "lookup (2,1 / B: B <", "row (B: B))" para encontrar a última célula que possui dados. Por exemplo, esta fórmula retorna uma resposta 18 na coluna "B". Eu posso usar a fórmula de concatenar para obter este endereço que é B18. Para obter este endereço (B18) na fórmula do countblank, o que devo fazer?

    

Answer 1

Com base no que entendi, você pode usar a combinação de CELL, OFFSET e INDIRECT e 2 Helper Cells para obter o que deseja.

No exemplo abaixo, os dados de amostra estão nas células B: B. A última linha em B é a linha 14.

A fórmula na célula auxiliar E4 é

="B"&LOOKUP(2,1/(B:B<>""),ROW(B:B))

Isso retorna B14, que é a última referência de célula preenchida no Col B.

No Helper Cell E5, coloque a seguinte fórmula para obter a referência da última linha em Col B

="B"&ROWS(B:B)

No meu Excel 2013, isso retorna B1048576

Agora a fórmula em G4 é

=COUNTBLANK(INDIRECT(CELL("address",OFFSET(INDIRECT(CELL("Address",INDIRECT(E4))),1,0))):INDIRECT(E5))

A parte da fórmula CELL("Address",INDIRECT(E4)) retorna a referência $ B $ 14 e o OFFSET adiciona 1 linha a ela, o que significa $ B $ 15. O COUNTBLANK usa estas referências de células INDIRECT para obter as referências de células esperadas e retorna o resultado esperado.

Na imagem abaixo Células E6 & E8 são apenas para referência. Eles não são usados em qualquer lugar.