Como definir um intervalo de valores no Excel

Ao usar o Microsoft Excel, muitas vezes o usuário não sabe de antemão quanta informação estará na tabela como resultado. Portanto, não entendemos em todas as situações qual faixa deve ser coberta. Afinal, um conjunto de células é um conceito variável. Para eliminar este problema, é necessário automatizar a geração do intervalo para que seja baseada apenas na quantidade de dados que foi inserida pelo usuário.

Alterando automaticamente os intervalos de células no Excel

A vantagem dos intervalos automáticos no Excel é que eles facilitam muito o uso de fórmulas. Além disso, eles permitem simplificar significativamente a análise de dados complexos que contêm um grande número de fórmulas, que incluem muitas funções. Você pode dar um nome a esse intervalo e, em seguida, ele será atualizado automaticamente, dependendo de quais dados ele contém.

Como fazer alteração automática de intervalo no Excel

Suponha que você seja um investidor que precisa investir em algum objeto. Como resultado, queremos obter informações sobre quanto você pode ganhar no total durante todo o tempo em que o dinheiro funcionará para este projeto. No entanto, para obter essas informações, precisamos monitorar regularmente quanto de lucro total esse objeto nos traz. Faça o mesmo relatório que nesta captura de tela.

À primeira vista, a solução é óbvia: basta somar a coluna inteira. Se as entradas aparecerem nele, o valor será atualizado de forma independente. Mas este método tem muitas desvantagens:

1. Se o problema for resolvido desta forma, não será possível utilizar as células incluídas na coluna B para outros fins.
2. Essa tabela consumirá muita RAM, o que impossibilitará o uso do documento em computadores fracos.

Portanto, é necessário resolver este problema através de nomes dinâmicos. Para criá-los, você deve executar a seguinte sequência de ações:

1. Vá para a guia “Fórmulas”, localizada no menu principal. Haverá uma seção “Nomes definidos”, onde há um botão “Atribuir um nome”, no qual precisamos clicar.
2. Em seguida, aparecerá uma caixa de diálogo na qual você precisa preencher os campos conforme mostrado na captura de tela. É importante notar que precisamos aplicar a função = DESLOCAMENTO junto com a função VERIFICAÇÃOpara criar um intervalo de atualização automática. 
3. Depois disso, precisamos usar a função SOMA, que usa nosso intervalo dinâmico como argumento. 

Após concluir essas etapas, podemos ver como a cobertura das células pertencentes à faixa “renda” é atualizada à medida que adicionamos novos elementos.

Função DESLOC no Excel

Vejamos as funções que registramos no campo “range” anteriormente. Usando a função DISPOSIÇÃO podemos determinar a quantidade de intervalo dado quantas células na coluna B são preenchidas. Os argumentos da função são os seguintes:

1. Iniciar célula. Com este argumento, o usuário pode indicar qual célula do intervalo será considerada superior esquerda. Ele reportará para baixo e para a direita.
2. Deslocamento de intervalo por linhas. Usando esse intervalo, definimos o número de células pelo qual um deslocamento deve ocorrer a partir da célula superior esquerda do intervalo. Você pode usar não apenas valores positivos, mas zero e menos. Nesse caso, o deslocamento pode não ocorrer ou será realizado na direção oposta.
3. Deslocamento de intervalo por colunas. Este parâmetro é semelhante ao anterior, apenas permite definir o grau de deslocamento horizontal da faixa. Aqui você também pode usar valores zero e negativos.
4. A quantidade de alcance em altura. De fato, o título deste argumento deixa claro para nós o que ele significa. Este é o número de células pelo qual o intervalo deve aumentar.
5. O valor do intervalo em largura. O argumento é semelhante ao anterior, só que diz respeito às colunas.

Você não precisa especificar os dois últimos argumentos se não precisar. Nesse caso, o valor do intervalo será apenas uma célula. Por exemplo, se você especificar a fórmula =DESLOC(A1;0;0), essa fórmula fará referência à mesma célula do primeiro argumento. Se o deslocamento vertical for definido para 2 unidades, nesse caso a célula se referirá à célula A3. Agora vamos descrever em detalhes o que a função significa VERIFICAÇÃO.

Função COUNT no Excel

Usando a função VERIFICAÇÃO determinamos quantas células na coluna B preenchemos no total. Ou seja, usando duas funções, determinamos quantas células do intervalo são preenchidas e, com base nas informações recebidas, determinamos o tamanho do intervalo. Portanto, a fórmula final será a seguinte: =СМЕЩ(Лист1!$B$2;0;0;СЧЁТ(Лист1!$B:$B);1)

Vamos dar uma olhada em como entender corretamente o princípio desta fórmula. O primeiro argumento aponta para onde nosso intervalo dinâmico começa. No nosso caso, esta é a célula B2. Outros parâmetros têm coordenadas zero. Isso sugere que não precisamos de deslocamentos em relação à célula superior esquerda. Tudo o que estamos preenchendo é o tamanho vertical do intervalo, que usamos a função como VERIFICAÇÃO, que determina o número de células que contêm alguns dados. O quarto parâmetro que preenchemos é a unidade. Assim, mostramos que a largura total do intervalo deve ser de uma coluna.

Assim, usando a função VERIFICAÇÃO o usuário pode usar a memória da forma mais eficiente possível carregando apenas as células que contêm alguns valores. Assim, não haverá erros adicionais no trabalho associados ao mau desempenho do computador no qual a planilha funcionará.

Assim, para determinar o tamanho do intervalo dependendo do número de colunas, você precisa executar uma sequência semelhante de ações, somente neste caso você precisa especificar a unidade no terceiro parâmetro e a fórmula no quarto VERIFICAÇÃO.

Vemos que com a ajuda de fórmulas do Excel, você pode não apenas automatizar cálculos matemáticos. Esta é apenas uma gota no oceano, mas na verdade eles permitem que você automatize quase qualquer operação que vier à mente.

Gráficos dinâmicos no Excel

Então, na última etapa, conseguimos criar um intervalo dinâmico, cujo tamanho depende completamente de quantas células preenchidas ele contém. Agora, com base nesses dados, você pode criar gráficos dinâmicos que serão alterados automaticamente assim que o usuário fizer alguma alteração ou adicionar uma coluna ou linha adicional. A sequência de ações neste caso é a seguinte:

1. Selecionamos nosso intervalo, após o qual inserimos um gráfico do tipo “Histograma com agrupamento”. Você pode encontrar este item na seção “Inserir” na seção “Gráficos – Histograma”.
2. Clicamos com o botão esquerdo do mouse em uma coluna aleatória do histograma, após o que a função =SERIES() será mostrada na linha da função. Na captura de tela você pode ver a fórmula detalhada. 
3. Depois disso, algumas alterações precisam ser feitas na fórmula. Você precisa substituir o intervalo após “Sheet1!” ao nome do intervalo. Isso resultará na seguinte função: =LINHA(Planilha1!$B$1;;Planilha1!renda;1)
4. Agora resta adicionar um novo registro ao relatório para verificar se o gráfico é atualizado automaticamente ou não.

Vamos dar uma olhada no nosso diagrama.

Vamos resumir como fizemos isso. Na etapa anterior, criamos um intervalo dinâmico, cujo tamanho depende de quantos elementos ele contém. Para fazer isso, usamos uma combinação de funções VERIFICAÇÃO и DISPOSIÇÃO. Fizemos esse intervalo nomeado e, em seguida, usamos a referência a esse nome como o intervalo do nosso histograma. Qual intervalo específico escolher como fonte de dados no primeiro estágio não é tão importante. O principal é substituí-lo pelo nome do intervalo posteriormente. Desta forma, você pode economizar muita memória RAM.

Intervalos nomeados e seus usos

Vamos agora falar com mais detalhes sobre como criar corretamente intervalos nomeados e usá-los para executar as tarefas definidas para o usuário do Excel.

Por padrão, usamos endereços de células regulares para economizar tempo. Isso é útil quando você precisa escrever um intervalo uma ou mais vezes. Se precisar ser usado o tempo todo ou precisar ser adaptável, os intervalos nomeados devem ser usados. Eles tornam a criação de fórmulas muito mais fácil e não será tão difícil para o usuário analisar fórmulas complexas que incluem um grande número de funções. Vamos descrever algumas das etapas envolvidas na criação de intervalos dinâmicos.

Tudo começa com a nomeação da célula. Para fazer isso, basta selecioná-lo e, em seguida, escrever o nome que precisamos no campo de seu nome. É importante que seja fácil de lembrar. Existem algumas restrições a serem consideradas ao nomear:

1. O comprimento máximo é de 255 caracteres. Isso é suficiente para atribuir um nome que seu coração deseja.
2. O nome não deve conter espaços. Portanto, se contiver várias palavras, é possível separá-las usando o caractere sublinhado.

Se posteriormente em outras planilhas deste arquivo precisarmos exibir esse valor ou aplicá-lo para realizar cálculos adicionais, não será necessário alternar para a primeira planilha. Você pode simplesmente escrever o nome dessa célula de intervalo.

A próxima etapa é criar um intervalo nomeado. O procedimento é basicamente o mesmo. Primeiro você precisa selecionar um intervalo e, em seguida, especificar seu nome. Depois disso, esse nome pode ser usado em todas as outras operações de dados no Excel. Por exemplo, intervalos nomeados são frequentemente usados ​​para definir a soma de valores.

Além disso, é possível criar um intervalo nomeado usando a guia Fórmulas usando a ferramenta Definir nome. Depois de selecioná-lo, aparecerá uma janela onde precisamos escolher um nome para nosso intervalo, além de especificar a área para a qual ele se estenderá manualmente. Você também pode especificar onde esse intervalo irá operar: em uma única folha ou em todo o livro.

Se um intervalo de nomes já foi criado, para usá-lo, existe um serviço especial chamado gerenciador de nomes. Ele permite não apenas editar ou adicionar novos nomes, mas também excluí-los se não forem mais necessários.

Deve-se ter em mente que ao usar intervalos nomeados em fórmulas, depois de excluí-lo, as fórmulas não serão substituídas automaticamente pelos valores corretos. Portanto, podem ocorrer erros. Portanto, antes de excluir um intervalo nomeado, você precisa certificar-se de que ele não seja usado em nenhuma das fórmulas.

Outra maneira de criar um intervalo nomeado é obtê-lo de uma tabela. Para fazer isso, existe uma ferramenta especial chamada “Criar a partir da Seleção”. Como entendemos, para usá-lo, você deve primeiro selecionar o intervalo que vamos editar e, em seguida, definir o local em que temos os títulos. Como resultado, com base nesses dados, o Excel processará automaticamente todos os dados e os títulos serão atribuídos automaticamente.

Se o título contiver várias palavras, o Excel as separará automaticamente com um sublinhado.

Assim, descobrimos como criar intervalos nomeados dinâmicos e como eles permitem automatizar o trabalho com grandes quantidades de dados. Como você pode ver, basta usar várias funções e as ferramentas do programa incorporadas à funcionalidade. Não há nada complicado, embora possa parecer para um iniciante à primeira vista.