Eu tenho uma planilha Excel bastante grande, onde as primeiras 20 colunas contêm dados de entrada, outras 15 colunas contêm fórmulas que usam os dados de entrada para cálculos.
Eu freqüentemente removo todo o conjunto de dados de entrada e o substituo por um novo, o volume de dados de entrada varia e varia de 4.000 a 10.000 linhas.
Existe suporte no Excel para ajustar automaticamente a parte da fórmula para o número de linhas de dados de entrada? Eu faço isso manualmente agora, excluindo linhas se os dados de entrada forem menores que o conjunto anterior ou copiando as células de fórmulas se os dados de entrada forem maiores. Isso é muito entediante, então estou procurando uma maneira de automatizar isso.
Algumas das fórmulas produzem # N / A se não houver dados de entrada e eu condensar os dados calculados com tabelas dinâmicas, portanto, preciso das fórmulas que correspondam exatamente às linhas de dados de entrada.
Estou no Excel 2003.
Para adicionar linhas, basta selecionar a última célula em cada coluna de fórmula e clicar duas vezes no canto inferior direito, como se fosse arrastar, mas clique duas vezes. Isso preencherá as fórmulas para corresponder aos valores à esquerda.
Antes (clique duas vezes onde está a pequena caixa preta):
Depois:
Algumas possíveis soluções ....
1) Você pode usar os campos calculados - isto é, fórmulas de tabela dinâmica? Os campos calculados funcionam muito bem se a sua fórmula se refere apenas às colunas de cada linha (em si). Como eles são parte do Pivot, eles não ocupam espaço nem diminuem muito sua planilha. Se suas fórmulas são verticais ou complexas, provavelmente não funcionará.
2) Use a formatação automática de tabela (Inserir tabela), em vez de apenas dados simples. As fórmulas inseridas no modo de tabela automática serão excluídas ou adicionadas à medida que você remover ou adicionar linhas.
3) Use IF (ISERROR (fórmula, em branco ou zero, mais fórmula) em suas fórmulas para ocultar resultados inválidos. Isso não funcionará bem se os dados da contagem de tabela dinâmica - como ele pode contar as linhas vazias, mas deve funcionar bem, se você confiar principalmente em somas de valores.
Se isso é algo que você faz muito, você pode criar um controle de botão do VBA em sua planilha para limpar todos os dados nas primeiras 20 colunas e deixar apenas as fórmulas na primeira linha das próximas 15 colunas. Dessa forma, você pode clicar no botão, colar os novos dados e, em seguida, preencher a linha 1 para col U a AJ com um clique duplo.