Resumo
Para configurar COUNTIFS (ou COUNTIF) com um intervalo variável, você pode usar a função OFFSET. No exemplo mostrado, a fórmula em B11 é:
=COUNTIFS(OFFSET(B$5,0,0,ROW()-ROW(B$5)-1,1),"")
Esta fórmula conta células não vazias em um intervalo que começa em B5 e termina 2 linhas acima da célula onde a fórmula reside. A mesma fórmula é copiada e colada 2 linhas abaixo da última entrada nos dados, conforme mostrado.
Explicação
No exemplo mostrado, a fórmula em B11 é:
=COUNTIFS(OFFSET(B$5,0,0,ROW()-ROW(B$5)-1,1),"")
Trabalhando de dentro para fora, o trabalho de configuração de uma faixa variável é feito pela função OFFSET aqui:
OFFSET(B$5,0,0,ROW()-ROW(B$5)-1,1) // variable range
OFFSET tem cinco argumentos e é configurado assim:
- referência = B $ 5, começa na célula B5, linha bloqueada
- linhas = 0, deslocamento de zero linhas da célula inicial
- cols = 0, célula inicial de colunas com deslocamento zero
- altura = ROW () - ROW (B $ 5) -1 = 5 linhas de altura
- largura = 1 coluna de largura
Para calcular a altura do intervalo em linhas, usamos a função ROW desta forma:
ROW()-ROW(B$5)-1 // work out height
Como ROW () retorna o número da linha da célula "atual" (ou seja, a célula em que a fórmula reside), podemos simplificar assim:
=ROW()-ROW(B$5)-1 =11-5-1 =5
Com a configuração acima, OFFSET retorna o intervalo B5: B9 diretamente para COUNTIFS:
=COUNTIFS(B5:B9,"") // returns 4
Observe que a referência a B $ 5 na fórmula acima é uma referência mista, com a coluna relativa e a linha bloqueada. Isso permite que a fórmula seja copiada para outra coluna e ainda funcione. Por exemplo, uma vez copiado para C12, a fórmula é:
=COUNTIFS(OFFSET(C$5,0,0,ROW()-ROW(C$5)-1,1),"")
Nota: OFFSET é uma função volátil e pode causar problemas de desempenho em planilhas grandes ou complexas.
Com INDIRETO e ENDEREÇO
Outra abordagem é usar uma fórmula baseada nas funções INDIRETO e ENDEREÇO. Nesse caso, montamos um intervalo como texto e, em seguida, usamos INDIRETO para avaliar o texto como referência. A fórmula em B11 seria:
=COUNTIFS(INDIRECT(ADDRESS(5,COLUMN())&":"&ADDRESS(ROW()-2,COLUMN())),"")
A função ADDRESS é usada para construir um intervalo como este:
ADDRESS(5,COLUMN())&":"&ADDRESS(ROW()-2,COLUMN())
Na primeira instância de ADDRESS, fornecemos o número da linha como o valor codificado permanentemente 5 e fornecemos o número da coluna com a função COLUMN:
=ADDRESS(5,COLUMN()) // returns "$B$5"
Na segunda instância, fornecemos o número da linha "atual" menos 2 e a coluna atual com a função COLUMN:
=ADDRESS(ROW()-2,COLUMN()) // returns "$B$9"
Depois de concatenar esses dois valores, temos:
"$B$5:$B$9" // as text
Observe que esta é uma string de texto. Para converter em uma referência válida, precisamos usar INDIRETO:
=INDIRECT("$B$5:$B$9") // returns $B$5:$B$9 as valid range
Finalmente, a fórmula em B11 se torna:
=COUNTIFS($B$5:$B$9,"") // returns 4
Nota: INDIRETO é uma função volátil e pode causar problemas de desempenho em planilhas grandes ou complexas.
