Intervalos dinâmicos no Excel: quando usar OFFSET ou uma tabela
Atualize os totais e as opções da lista suspensa ao adicionar linhas. Veja como usar OFFSET e COUNTA, o que acontece quando falta um ID e como usar tabelas do Excel.
Você adiciona um produto à lista do Excel, mas o total e as opções da lista suspensa não mudam. Se a referência ainda for A2:A10, a 11ª linha está fora. Um intervalo dinâmico permite que o Excel determine onde a lista termina, sem exigir ajustes nas fórmulas a cada atualização.
Calcular altura e largura com OFFSET
Se os dados forem contíguos, conte os cabeçalhos e IDs de produto para passar a altura e a largura para OFFSET. Monte a amostra em Sheet1 e insira a fórmula de soma em outra planilha. Digite os cabeçalhos em japonês exatamente como indicados para que as referências estruturadas funcionem.
| Célula | Coluna A | Coluna B | Coluna C |
|---|---|---|---|
| Linha 1 | 商品ID (ID do produto) |
商品名 (nome do produto) |
数量 (quantidade) |
| Linha 2 | A01 | Caneta | 4 |
| Linha 3 | A02 | Caderno | 6 |
=SUM(OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A$1:$A$1000),COUNTA(Sheet1!$A$1:$Z$1)))
Comece em A1, sem deslocar linhas ou colunas. A contagem da coluna A determina a altura, e a de cabeçalhos na linha 1 determina a largura. A amostra resulta em A1:C3, com três linhas e três colunas e total 10. Acrescente A03, Régua e 2 à quarta linha para obter A1:C4 e total 12. Os IDs são texto e não há outros dados nos intervalos contados. Referência da Microsoft para OFFSET
Agora apague apenas o ID de A3. A coluna A passa a ter três células contadas: o cabeçalho A1 e os IDs em A2 e A4. Assim, OFFSET retorna as três primeiras linhas, A1:C3. O 6 da linha 3 continua incluído, mas o 2 da linha 4 fica de fora. O total volta a 10.
Um cabeçalho vazio no meio também pode deixar as últimas colunas fora do intervalo. COUNTA conta células com fórmulas que retornam texto vazio (""). Em uma pasta de trabalho existente, mantenha os cabeçalhos e IDs contíguos e verifique os limites e células vazias. Uma quantidade vazia não altera a altura quando o ID está presente. Referência da Microsoft para COUNTA
Quando os cabeçalhos começam na segunda linha
Se a primeira linha contém o título do relatório, comece em A2 e faça a contagem da altura e da largura a partir da linha 2. Ao mover a amostra uma linha para baixo, o intervalo para o cabeçalho e os dois produtos vira A2:C4 e o total continua 10.
=SUM(OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A$2:$A$1000),COUNTA(Sheet1!$A$2:$Z$2)))
Usar uma tabela que se expande com novas linhas
Para uma lista nova, selecione o intervalo com os cabeçalhos e escolha Inserir → Tabela. Nomeie a tabela Items. A fórmula a seguir soma a coluna de quantidade a partir de uma célula fora dela. Ao adicionar linhas, o intervalo da soma também cresce. Guia da Microsoft para referências estruturadas
=SUM(Items[数量])
Somar apenas as linhas com ID do produto
Ao transformar A1:C4 na tabela Items, mesmo que A3 esteja vazia, a fórmula anterior soma 4 + 6 + 2 e retorna 12. Para incluir apenas as linhas com ID, use SUMIF com a condição de que a coluna de IDs não esteja vazia. O exemplo retorna 6 (4 + 2 das linhas 2 e 4). Referência da Microsoft para SUMIF
=SUMIF(Items[商品ID],"<>",Items[数量])
Vincular as opções da lista suspensa à tabela
Para oferecer os nomes de produto em outra planilha, abra Fórmulas → Gerenciador de Nomes no Excel para desktop. Crie ItemChoices no escopo Pasta de Trabalho, com =Items[商品名] em Refere-se a. Para a célula de entrada, escolha Lista na Validação de Dados e informe =ItemChoices em Fonte. O cabeçalho fica fora e as linhas adicionadas ao final trazem novas opções. Preencha o nome de cada nova linha para evitar opções vazias. Guia da Microsoft para criar listas suspensas