Fórmula genérica
=SUM(firstcell:INDEX(data,rows,cols))
Resumo
Para definir um intervalo com base em um valor em outra célula, você pode usar a função INDEX. No exemplo mostrado, a fórmula em J7 é:
=SUM(C5:INDEX(data,J5,J6))
onde "dados" é o intervalo nomeado B5: G9.
Explicação
Esta fórmula depende de um comportamento específico de INDEX - embora pareça que INDEX retorne o valor em um local específico, na verdade ele retorna a referência para o local. Na maioria das fórmulas, você não notaria a diferença - o Excel simplesmente avalia a referência e retorna o valor. Esta fórmula usa esse recurso para construir um intervalo dinâmico com base na entrada da planilha.
Dentro da função de soma, a primeira referência é simplesmente a primeira célula no intervalo que cobre todas as células possíveis:
=SUM(C5:
Para obter a última célula, usamos INDEX. Aqui, fornecemos a INDEX o intervalo denominado "dados", que é o intervalo máximo possível de valores, e também os valores de J5 (linhas) e J6 (colunas). INDEX não retorna um intervalo, ele retorna apenas uma única célula naquele local, E9 no exemplo:
INDEX(data,J5,J6) // returns E9
A fórmula original é reduzida a:
=SUM(C5:E9)
que retorna 300, a soma de todos os valores em C5: E9.
A fórmula em J8 é quase a mesma, mas usa AVERAGE em vez de SUM para calcular uma média. Quando um usuário altera os valores em J5 ou J6, o intervalo é atualizado e novos resultados são retornados.
Alternativa com OFFSET
Você pode construir fórmulas semelhantes com a função OFFSET, mostrada abaixo:
=SUM(OFFSET(C5,0,0,J5,J6)) // sum =AVERAGE(OFFSET(C5,0,0,J5,J6)) // average
OFFSET é projetado para retornar um intervalo, então as fórmulas talvez sejam mais simples de entender. No entanto, OFFSET é uma função volátil e pode causar problemas de desempenho quando usada em planilhas maiores e mais complexas.
