Skip to content

Este truque do Excel permite somar um número de células com um intervalo variável

14 de maio de 2021

No Excel, você pode somar um número de células usando um intervalo de variável com a função INDIRETO. A função INDIRETO atualiza automaticamente o intervalo de células que você referenciou sem editar manualmente a própria fórmula. Você pode usar a função INDIRETO com qualquer número de funções do Excel, mas a mais comum (e útil) é quando você usa a função SOMA. As informações no artigo se aplicam às versões do Excel 2019, 2016, 2013, 2010 e Excel para Mac.

Como somar um número de células usando um intervalo variável

Você pode incorporar a função INDIRETA como um argumento dentro da função SUM para criar um intervalo variável de referências de células para a função SUM adicionar. A função INDIRETO faz isso referenciando o intervalo de células indiretamente, por meio de uma referência de célula intermediária. O formato de ambas as funções usadas em conjunto é assim: = SOMA (INDIRETO (“D1”: D4)

type = “code”> Isso bloqueia o intervalo para começar em D1 e permite que D4 mude se você inserir ou excluir qualquer célula na coluna D. Você também pode usar outras células na planilha para modificar as referências das células. Por exemplo, se você usar E1 para fazer referência à primeira célula do intervalo e E2 para fazer referência à última célula do intervalo, a fórmula terá a seguinte aparência: = SUM (INDIRECT (“D” & E1 & “: D” & E2))

type = “code”> Ao alterar os números localizados nas células E1 e E2, você pode modificar o intervalo na fórmula sem ter que editar manualmente a fórmula.

Experimente as funções SUM e INDIRECT

Crie uma planilha para testar as funções SOMA e INDIRETO você mesmo. Comece criando uma planilha em branco e inserindo os seguintes dados nas colunas D e E: Dados da célula
D1 – 5
D2 – 10
D3 – 15
D4 – 20
D5 – 25
D6 – 30
E1 – 1
E2 – 4

Em seguida, crie a fórmula na célula F1. Veja como:

  1. Selecione a célula F1. É aqui que o resultado deste exemplo será exibido.
  2. Selecione Fórmulas.
  3. Escolher Matemática e Trig para abrir a lista suspensa de funções.
  4. Selecione SOMA na lista para abrir a caixa de diálogo Argumentos da função SUM.

Aninhe a função INDIRETA na função SUM

Inserindo a função INDIRETA aninhada no Excel

A seguir, insira a função INDIRETA na função SOMA usando esta caixa de diálogo.

  1. No Número 1 campo, insira a seguinte função INDIRETA: INDIRETO (“D” e E1 e “: D” e E2)
  2. Selecione OK para completar a função e fechar a caixa de diálogo.
  3. O número 50 aparece na célula F1. Esta é a soma das células D1 a D4.
  4. Quando você seleciona a célula F1, o campo de fórmula mostra: = SUM (INDIRETO (“D” & E1 & “: D” & E2))

Quando você entender como formatar as funções SOMA e INDIRETO aninhadas, poderá digitar a função inteira mostrada na etapa 4 diretamente na célula de resultado (neste exemplo, célula F1).

Construa o argumento para a função INDIRETA

No exemplo acima, as referências às células E1 e E2 estão fora das aspas. Isso, combinado com o E concatenar símbolo, incorpora tudo o que está contido dentro das células E1 e E2 na própria fórmula. Veja como funcionam os elementos da fórmula:

  • E é usado para juntar dados de texto (neste caso, a letra D) com uma referência de célula (E1 e E2) que contém um valor numérico.
  • “” deve envolver dados de texto dentro das referências de célula (neste exemplo, a letra D).
  • : é um separador entre a primeira célula e a última célula no intervalo SOMA.

No exemplo acima, o ponto inicial do intervalo é definido pelos caracteres: “D” e E1. O ponto final do intervalo é definido pelos caracteres: “: D” e E2

Os dois pontos devem ser incluídos entre aspas. O terceiro e comercial no meio é usado para concatenar os dois segmentos em um argumento: “D” & E1 & “: D” & E2

type = “código”>

O intervalo de variáveis ​​da função SUM

Alterar dinamicamente o intervalo da fórmula no Excel

A função INDIRETO aninhada dentro da função SUM torna fácil alterar o início e o fim do intervalo totalizado pela função SUM, sem ter que editar a própria função. Usando a técnica de concatenação na etapa 3, use os números armazenados nas células E1 e E2 na planilha para alterar o intervalo de células para a função SUM. Teste isso em sua própria planilha modificando as células E1 e E2 e observe como os resultados da função SUM mudam.

  1. Selecione a célula E1.
  2. Digite o número 3.
  3. Aperte Entrar.
  4. Selecione a célula E2.
  5. Digite o número 6.
  6. Aperte Entrar.
  7. A resposta na célula F1 muda para 90. Esta é a soma dos números contidos nas células D3 a D6.

Para ver a função INDIRETA em ação, insira uma nova célula na célula D3. Isso desloca todas as outras células para baixo. A nova soma é o total das células D3 a D7. A função INDIRETO altera dinamicamente todo o intervalo para incluir a lista inteira inserida inicialmente, além da nova célula inserida também.

O #REF! Valor de Erro

O #REF! o valor do erro aparece na célula F1 se a função INDIRETA:

  • Contém uma referência de célula inválida.
  • Contém uma referência externa a uma planilha diferente que não existe.
  • Refere-se a um intervalo de células fora dos limites da planilha.
  • Refere-se a uma célula que está em branco.