Conteúdo
Você tem tabelas com dados no Excel que podem ser redimensionadas, ou seja, o número de linhas (colunas) pode aumentar ou diminuir no decorrer do trabalho? Se os tamanhos da tabela “flutuarem”, você terá que monitorar constantemente esse momento e corrigi-lo:
- links em fórmulas de relatório que se referem à nossa tabela
- intervalos iniciais de tabelas dinâmicas que são construídas de acordo com nossa tabela
- intervalos iniciais de gráficos construídos de acordo com nossa tabela
- intervalos para listas suspensas que usam nossa tabela como fonte de dados
Tudo isso no total não vai deixar você ficar entediado 😉
Será muito mais conveniente e correto criar um intervalo dinâmico de “borracha”, que se ajustará automaticamente em tamanho ao número real de linhas e colunas de dados. Para implementar isso, existem várias maneiras.
Método 1. Tabela inteligente
Realce seu intervalo de células e selecione na guia Home – Formatar como Tabela (Home – Formatar como Tabela):
Se você não precisar do design listrado adicionado à tabela como efeito colateral, poderá desativá-lo na guia exibida Construtor (Projeto). Cada tabela criada desta forma recebe um nome que pode ser substituído por um mais conveniente no mesmo local da aba Construtor (Projeto) no campo Nome da mesa (Nome da tabela).
Agora podemos usar links dinâmicos para nossa “tabela inteligente”:
- tabela 1 – link para toda a tabela, exceto para a linha de cabeçalho (A2:D5)
- Tabela1[#Todos] – link para a tabela inteira (A1:D5)
- Tabela 1[Pedro] – referência a uma coluna de intervalo sem o primeiro cabeçalho de célula (C2:C5)
- Tabela1[#Headers] – link para o “cabeçalho” com os nomes das colunas (A1:D1)
Tais referências funcionam muito bem em fórmulas, por exemplo:
= SUMA (Tabela 1[Moscou]) – cálculo da soma para a coluna “Moscou”
or
=RPV(F5;tabela 1;3;0) – procure na tabela o mês da célula F5 e emita a soma de São Petersburgo para ele (o que é PROCV?)
Esses links podem ser usados com sucesso ao criar tabelas dinâmicas selecionando na guia Inserir – Tabela Dinâmica (Inserir – Tabela Dinâmica) e inserindo o nome da tabela inteligente como fonte de dados:
Se você selecionar um fragmento dessa tabela (por exemplo, as duas primeiras colunas) e criar um diagrama de qualquer tipo, ao adicionar novas linhas, elas serão adicionadas automaticamente ao diagrama.
Ao criar listas suspensas, links diretos para elementos de tabelas inteligentes não podem ser usados, mas você pode facilmente contornar essa limitação usando um truque tático - use a função INDIRETO (INDIRETO), que transforma o texto em um link:
Aqueles. um link para uma tabela inteligente na forma de uma string de texto (entre aspas!) se transforma em um link completo, e a lista suspensa normalmente o percebe.
Método 2: intervalo nomeado dinâmico
Se transformar seus dados em uma tabela inteligente é indesejável por algum motivo, você pode usar um método um pouco mais complicado, mas muito mais sutil e versátil – crie um intervalo nomeado dinâmico no Excel que se refira à nossa tabela. Então, como no caso de uma tabela inteligente, você pode usar livremente o nome do intervalo criado em qualquer fórmula, relatório, gráfico etc. Vamos começar com um exemplo simples:
Tarefa: crie um intervalo nomeado dinâmico que se refira a uma lista de cidades e aumente e diminua automaticamente de tamanho ao adicionar novas cidades ou excluí-las.
Precisaremos de duas funções internas do Excel disponíveis em qualquer versão - POICPOZ (COMBINE) para determinar a última célula do intervalo, e ÍNDICE (ÍNDICE) para criar um link dinâmico.
Encontrando a última célula usando MATCH
CORRESP(valor_procurado, intervalo, tipo_correspondência) – uma função que procura um determinado valor em um intervalo (linha ou coluna) e retorna o número ordinal da célula onde foi encontrado. Por exemplo, a fórmula CORRESP(“Março”;A1:A5;0) retornará o número 4 como resultado, pois a palavra “Março” está localizada na quarta célula da coluna A1:A5. O último argumento da função Match_Type = 0 significa que estamos procurando uma correspondência exata. Se este argumento não for especificado, então a função irá mudar para o modo de busca para o menor valor mais próximo – isso é exatamente o que pode ser usado com sucesso para encontrar a última célula ocupada em nosso array.
A essência do truque é simples. MATCH procura células no intervalo de cima para baixo e, em teoria, deve parar quando encontrar o menor valor mais próximo ao dado. Se você especificar um valor que é obviamente maior do que qualquer um disponível na tabela como o valor desejado, então MATCH chegará ao final da tabela, não encontrará nada e fornecerá o número de sequência da última célula preenchida. E nós precisamos disso!
Se houver apenas números em nosso array, podemos especificar um número como o valor desejado, que é obviamente maior que qualquer um na tabela:
Como garantia, você pode usar o número 9E + 307 (9 vezes 10 elevado a 307, ou seja, 9 com 307 zeros) – o número máximo com o qual o Excel pode trabalhar em princípio.
Se houver valores de texto em nossa coluna, então como equivalente ao maior número possível, você pode inserir a construção REPEAT(“i”, 255) – uma string de texto composta por 255 letras “i” – a última letra de o alfabeto. Como o Excel realmente compara códigos de caracteres ao pesquisar, qualquer texto em nossa tabela será tecnicamente “menor” do que uma linha tão longa “yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy”:
Gerar um link usando INDEX
Agora que sabemos a posição do último elemento não vazio na tabela, resta formar um link para todo o nosso intervalo. Para isso usamos a função:
INDEX(intervalo; núm_linha; núm_coluna)
Ele fornece o conteúdo da célula do intervalo por número de linha e coluna, ou seja, por exemplo, a função =INDEX(A1:D5;3;4) em nossa tabela com cidades e meses do método anterior dará 1240 – o conteúdo da 3ª linha e 4ª coluna, ou seja, células D3. Se houver apenas uma coluna, seu número pode ser omitido, ou seja, a fórmula INDEX(A2:A6;3) dará “Samara” na última captura de tela.
E há uma nuance não totalmente óbvia: se o ÍNDICE não for apenas inserido na célula após o sinal =, como de costume, mas for usado como a parte final da referência ao intervalo após os dois pontos, ele não será mais fornecido o conteúdo da célula, mas seu endereço! Assim, uma fórmula como $A$2:INDEX($A$2:$A$100;3) dará uma referência ao intervalo A2:A4 na saída.
E é aí que entra a função MATCH, que inserimos dentro do INDEX para determinar dinamicamente o final da lista:
=$A$2:ÍNDICE($A$2:$A$100; CORRESP(REP(“I”;255);A2:A100))
Criar um intervalo nomeado
Resta empacotar tudo em um único todo. Abra uma guia Fórmula (Fórmulas) E clique no botão Gerente de Nomes (Gerenciador de Nomes). Na janela que se abre, clique no botão Crie (Novo), insira nosso nome de intervalo e fórmula no campo Variação (Referência):
Resta clicar em OK e o intervalo pronto pode ser usado em quaisquer fórmulas, listas suspensas ou gráficos.
- Usando a função PROCV para vincular tabelas e valores de pesquisa
- Como criar uma lista suspensa de preenchimento automático
- Como criar uma tabela dinâmica para analisar uma grande quantidade de dados