Contagem de uma determinada célula até uma célula em branco

Question

Estou tentando criar uma fórmula dinâmica que conta todas as linhas da célula D3 para baixo até que uma célula em branco seja alcançada. Eu quero ser capaz de adicionar novos dados todos os dias e ter o intervalo automaticamente atualizar e incluir os dados mais recentes para dar uma série de subtotais e médias.

Qual é a fórmula para ter um intervalo que inclua células de D3 até a próxima célula em branco que atualizará e incluirá novos dados quando a última célula em branco for preenchida com dados de um novo dia?

    

Answer 1

Deixe-me ter certeza de que estou certo:

• Você tem muitos dados na coluna D que deseja totalizar.
• Linhas 1 e amp; 2 são linhas de cabeçalho e contêm texto.
• As linhas 3 e abaixo, na coluna D, contêm valores a serem totalizados.
• Abaixo dos dados a serem totalizados, há uma célula na qual você deseja colocar o total.
• Pode ou não haver células em branco entre os dados a serem totalizados e a célula que exibe os dados.

Dado o acima, se você não precisa de um intervalo nomeado para outros propósitos, a fórmula é bem simples:

=SUM(INDIRECT(CONCATENATE("D3:D",CELL("row")-1)))

Passo a passo:

• SUM adiciona todos os valores numéricos (excluindo automaticamente espaços em branco e valores não numéricos) nas células especificadas.
• INDIRECT permite criar uma referência de célula por meio de fórmula.
• CONCATENATE permite-lhe juntar porções de cadeias de caracteres e resultados de fórmulas numa única cadeia.
• "D3: D" é a primeira parte da referência que eventualmente desejaremos passar para SUM.
  • Isso identifica um intervalo de células que começa em D3 e termina em outra célula na coluna D.
  • Como está sendo fornecido como uma string em vez de uma referência de célula real, isso não será alterado se a célula for movida ou copiada - o intervalo sempre apontará para a coluna D e sempre começará na linha 3.
• CELL permite obter informações sobre uma célula. Aqui, estamos recebendo o número da linha da célula atual. Isso é o que permite que a fórmula ajuste automaticamente o número da linha, não importa onde ela seja colocada.
• -1 é usado para subtrair 1 do número da linha retornado pelo CELL, fazendo referência à linha acima da célula atual.

Para o D303, isso será efetivamente o mesmo que:

=SUM(D3:D302)

    

Answer 2

Acho que minha resposta aqui: No Excel 2010, como posso criar um modelo com uma função que termina automaticamente na última célula com dados? pode ser útil.

Eu recomendo strongmente tabelas, se você puder, elas também têm uma função de linha total integrada.

Se não recair no intervalo com nome dinâmico.


EDITAR
Conforme discutimos, você deseja usar um intervalo de nome dinâmico como o intervalo de origem de uma tabela dinâmica:

Isso requer algumas pequenas modificações no intervalo de nome dinâmico. Em primeiro lugar, você precisará do seu intervalo para incluir os títulos das colunas, de modo que a tabela dinâmica tenha títulos e, em seguida, você precisará expandir seu alcance em todas as colunas relevantes.

=OFFSET(Sheet1!$F$1,0,0,COUNTA(Sheet1!$F$1:$F$12),COUNTA(Sheet1!$F$1:$G$1))

Aqui, você pode ver que, em vez de terminar a fórmula com ),) , adicionamos uma referência de coluna que retorna o intervalo de colunas cobrindo a primeira linha em todas as colunas necessárias.

Quando você insere uma tabela dinâmica, use o nome desse intervalo.


NOTAS
Não é possível envolver o OFFSET em uma CONTAGEM de qualquer tipo se você quiser usar o intervalo nomeado em uma tabela dinâmica, mas isso não é necessário, pois você pode executar a contagem na tabela dinâmica.

Se você e seus usuários estiverem usando 2007 ou acima e, em seguida, usarem uma tabela, isso permanecerá dinâmico mesmo se você adicionar mais colunas, e nunca exigirá que você vá em a e aumente a altura do intervalo nomeado. Você ainda poderá encaminhar a tabela para uma tabela dinâmica.