Então, estou tentando fazer com que uma equação funcione em um número variável de células. Então o que eu tenho é uma planilha com os custos atuais e os custos projetados. Gostaria que o número da previsão fosse calculado apenas com base nos custos atuais. Isso é indicado por um ACT no início do mês. Minha fórmula inicial de somas/contagens foi rejeitada por gerar um erro de referência circular, então estou me perguntando se existe uma maneira de atualizar as equações dinamicamente dependendo de outra célula.
Prefiro evitar o uso de intervalos dinâmicos, já que estamos falando de cerca de 400 linhas que precisam ser atualizadas, e entendo que os intervalos precisam ser adicionados um de cada vez.
Um exemplo de algo que representa o que eu espero que a fórmula faça: =Soma(A1:A(1+B1)), em que o intervalo aumenta à medida que B1 aumenta.
Um exemplo de dados:
Act Act Act Proj Proj
A: 1 2 3 2 2
B: 1 3 5 3 3
C: 4 5 6 5 5
- Projeção A: 2
- Projeção B: 3
- Projeção C: 5
Então, quando eu adiciono os números reais do 4º mês e mudo o Proj para Act, o intervalo no qual a projeção A, a projeção B, etc. calcula as alterações de 3 para 4, sem qualquer contribuição adicional minha.
Responder1
Você pode usar OFFSET()para criar uma referência a um intervalo de colunas X por Y, por exemplo:
=OFFSET(A1,0,0,10,10)criará uma referência 10 por 10 que se refere a A1:J10.
Você pode substituir os 10 codificados na fórmula acima por um COUNTIF()que infere a quantas linhas e colunas o intervalo deve se referir.
Para evitar um erro de circularidade, você pode tentar definir um intervalo nomeado ( AltMMD) com esta fórmula e usar o intervalo nomeado sempre que usar a fórmula.
Alternativamente, você pode construir uma string de texto com a referência X por Y usando OFFSET()sem os parâmetros de largura e altura (ou seja, os 10 na fórmula acima) e, em vez disso, encontrando a primeira e a última célula em um intervalo. Em seguida, envolva-os em uma CELL("address",...)chamada que retornará seu endereço.(NB: Se o seu ponto de partida for $A$1 você só precisa encontrar a célula final).
Concatene-os com um :ponto intermediário e, em seguida, envolva tudo em INDIRECT()qualquer lugar que você precisar usá-lo. Em suma, assumindo um início codificado em $A$1:
=INDIRECT("Sheet1!$A$1:"&CELL("address",OFFSET(Sheet1!$A$1,COUNTIF(..x-rows..),COUNTIF(..y-cols..))
COUNTIF()lógica para encontrar o número de linhas e colunas restantes como um exercício para o leitor