No Excel, você pode definir uma célula ou intervalo a ser usado em uma fórmula com base no conteúdo de outra célula ou fórmula?

Question

O que eu quero fazer é isto:

• Uma célula calcula o número de itens presentes em uma coluna específica, por exemplo, C1 = 129
• Uma segunda célula usa esse número como parte da definição, por exemplo D1 = MÉDIA ($ A $ 1: $ A $ (C1)), significando ($ A $ 1: $ A $ 129)

Por que eu quero fazer isso é isso:

Eu tenho uma fórmula, formada por outro site, que conta o número de itens distintos em uma coluna. Esta fórmula não irá tolerar espaços em branco, nem (afaik) pode ser feita para fazê-lo. Eu uso essa fórmula em um relatório semanal e o número de itens na coluna muda de semana para semana. Em vez de alterar manualmente o valor nos vários locais em que é usado (como faço agora), eu adoraria simplesmente inserir os novos dados, fazer com que a planilha conte o número de itens e usar esse número na fórmula do script. / p>

Veja a fórmula em questão em questão:

=SUM(IF(FREQUENCY(MATCH(Data!A2:A100,Data!A2:A100,0),MATCH(Data!A2:A100,Data!A2:A100,0))>0,1))

Onde o "A100" é a extensão inferior da coluna preenchida por dados desta semana. Na próxima semana, pode ser A200 ou A50. No entanto, para esta semana, eu deveria mudar A100 para A101 (onde não existem dados), a fórmula falhará gloriosamente com um "Valor Não Disponível Erro".

Qualquer maneira de fazer o que eu preciso?

    

Answer 1

Use a função de planilha INDIRECT ().

Em suma, a fórmula =INDIRECT("A1") retorna o valor que está contido na célula A1 . Portanto, tudo o que você precisa fazer é, em vez de "A1" , substituir esse valor estático pelo cálculo que estiver usando para criar a linha / coluna da célula. O resultado de toda a fórmula será o valor contido na célula especificada no argumento para a função de planilha INDIRECT() .

Aqui está um exemplo simplista

Valor da célula A1 = 1
Valor da célula A2 = 2
Valor da célula A3 = 3
Valor da célula A4 = branco / nulo
Valor da célula A5 = =COUNT(A1:A3) (substitua isso por sua fórmula de desvio)
Valor da célula A6 = =AVERAGE($A$1:INDIRECT("$A$" & INDIRECT("A5")))

Ir para fórmulas - > Avalie a fórmula e percorra a avaliação se quiser ver como ela funciona. Basicamente, ele concatena a string "$ A $" com o valor "3". O "3" é obtido do valor da célula A5, que é 3, porque a contagem de 3 células retorna 3 neste exemplo. Em seguida, chamamos INDIRECT() uma segunda vez, para obter o valor de $A$3 , mas o que é realmente retornado é uma referência de célula! Se você não acredita em mim, avalie a fórmula e observe o que o Excel transforma a chamada INDIRECT("$A$3") durante a avaliação. É estranho, mas funciona - INDIRECT() se comporta de maneira diferente, dependendo de estar sendo enviado para uma referência de intervalo ou para um valor.

    

Answer 2

É possível alterar sua fórmula original para permitir (mas não contar) espaços em branco, ou seja, essa versão

=SUM(IF(FREQUENCY(IF(Data!A2:A100<>"",MATCH(Data!A2:A100,Data!A2:A100,0)),ROW(Data!A2:A100)-ROW(Data!A2)),1))

confirmado com CTRL + SHIFT + ENTER

..... mas você pode querer tentar este também - mais curto e não precisa de "entrada de array"

=SUMPRODUCT((Data!A2:A100<>"")/COUNTIF(Data!A2:A100,Data!A2:A100&""))

    

Answer 3

Em vez de INDIRECT , você também pode considerar usar INDEX . Supondo que sua contagem esteja em J3 e os dados estejam na coluna A), essa fórmula somaria todos os elementos: =SUM(A2:INDEX(A2:A10000,J3))

Para o seu problema específico, isso deve funcionar:

=SUM(IF(FREQUENCY(MATCH(Data!A2:INDEX(A2:A10000,J3),Data!A2:INDEX(A2:A10000,J3),0),MATCH(Data!A2:INDEX(A2:A10000,J3),Data!A2:INDEX(A2:A10000,J3),0))>0,1))

A grande vantagem do INDEX comparado ao INDIRECT é que ele é não volátil e geralmente mais rápido no cálculo. A não volatilidade significa que o Excel só precisa calcular a fórmula quando qualquer predecessor é alterado. No entanto, INDIRECT será calculado após qualquer cálculo / alteração feita na pasta de trabalho - assim, esp. no modelo grande retardando o modelo significativamente!