Uma maneira de determinar automaticamente intervalos no Excel?

Question

Eu tenho um modelo de pasta de trabalho em que a primeira planilha usará dados de outras planilhas, e as outras planilhas serão substituídas por uma exportação regular do banco de dados.

Em vários casos, as informações na planilha de resumo referem-se às planilhas de dados subsequentes como um intervalo para uma tabela dinâmica. O problema é que é desconhecido quantas linhas estarão no intervalo. Quando um intervalo como A $: D $ é usado como fonte para a tabela dinâmica, há um item "(em branco)" que aparece. Isso pode ser suprimido com o recurso de filtro, mas estou preocupado que isso faça com que o modelo perca algumas linhas se novas categorias forem adicionadas.

Parece que o problema com isso é que o intervalo não está confinado à parte da planilha que realmente contém dados.

Existe uma maneira de ter um intervalo terminado automaticamente na última linha com dados?

    

Answer 1

Existem várias maneiras de atingir intervalos dinâmicos:

Usando OFFSET e COUNTA em um intervalo nomeado

Se você usar o =COUNTA($A:$A) , receberá todos os itens na coluna A. Certifique-se de escolher uma coluna que esteja sempre preenchida, por exemplo, a coluna ID . Se a sua tabela de dados começar em algum lugar abaixo, você também deve considerar: =COUNTA($A:$A)-COUNTA($A$1:$A10) .

Agora, você pode usar a fórmula =OFFSET($A$1,0,0,COUNTA($A:$A),4) para determinar um intervalo começando em A1 com 4 colunas de largura e contendo todas as linhas.

O truque para usar esse intervalo definido é inseri-lo como um intervalo nomeado! Para fazer isso, vá até o Gerenciador de Nomes (na guia Fórmula) e insira um novo nome (por exemplo, dados ). Aqui, em vez de fornecer a referência a um intervalo fixo, copie a fórmula acima. (Nota: se você quiser editar a fórmula, não se esqueça de pressionar F2 antes de usar as teclas de seta, senão a fórmula será confundida por referências de célula). Certifique-se de usar o $ para todas as referências de células.

Agora você pode usar o nome data como fonte para o seu pivô. Se você quiser verificar se o intervalo completo é usado, basta digitar o nome na caixa Nome à esquerda da barra de fórmulas (você precisa realmente digitá-lo lá) - ou apenas clicar no pequeno ícone ao lado da fórmula no Gerenciador de nomes. .

Usando INDEX e COUNTA

A pequena desvantagem de OFFSET é que ela é volátil, isto é, que é calculada com cada execução de cálculo, mesmo que os dados referidos não sejam alterados. Isso pode atrasar seu modelo caso você use o nome dinâmico com frequência nas fórmulas.

Para evitar isso, substitua OFFSET por INDEX da seguinte maneira:

=INDEX($A:$A,COUNTA($A:$A)):INDEX($D:D$,COUNTA($A:$A))

Isso fará o mesmo trabalho, mas não é volátil. Além disso, se você inserir uma nova coluna antes da coluna D, essa fórmula será ajustada enquanto na opção anterior você teria que substituir o 4 por um 5 (ou usar COUNTA($1:$1) ).

Use a tabela do Excel

Uma maneira muito mais elegante que os intervalos nomeados são as tabelas do Excel. Se você importar dados, o Excel geralmente armazena os dados importados já em uma tabela (você pode reconhecê-los pela cor da linha alternada e pela guia Tabela que está disponível na faixa após a seleção). Como alternativa, você pode inserir uma tabela manualmente (guia Inserir - Inserir tabela).

Forneça a tabela com um nome de fala (no campo de nome na guia Tabela) - e, em seguida, simplesmente use esse nome como a origem do seu pivô! Se os dados forem alterados, a tabela e, portanto, o intervalo associado ao pivô se ajustará automaticamente.

No entanto, esteja ciente de que você ainda precisará atualizar sua (s) tabela (s) dinâmica (s) após qualquer atualização!