Como usar um intervalo dinâmico no Excel com CONT.SE e INDIRETO

Índice:

2024 Autor: Abigail Brown | [email protected]. Última modificação: 2023-12-17 06:58

O que saber

A função INDIRETO altera o intervalo de referências de células em uma fórmula sem editar a fórmula.
Use INDIRETO como argumento para CONT. SE para criar um intervalo dinâmico de células que atendam aos critérios especificados.
Os critérios são estabelecidos pela função INDIRETO, e apenas as células que atendem aos critérios são contadas.

Este artigo explica como usar a função INDIRETO em fórmulas do Excel para alterar o intervalo de referências de células usadas em uma fórmula sem precisar editar a própria fórmula. Isso garante que as mesmas células sejam usadas, mesmo quando sua planilha for alterada. As informações se aplicam ao Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel para Mac e Excel Online.

Use um intervalo dinâmico com a fórmula CONT. SE - INDIRETO

A função INDIRETO pode ser usada com várias funções que aceitam uma referência de célula como argumento, como as funções SOMA e CONT. SE.

Usar INDIRETO como argumento para CONT. SE cria um intervalo dinâmico de referências de células que podem ser contadas pela função se os valores das células atenderem a um critério. Ele faz isso transformando dados de texto, às vezes chamados de string de texto, em uma referência de célula.

Este exemplo é baseado nos dados mostrados na imagem acima. A fórmula CONT. SE - INDIRETO criada no tutorial é:

=CONT. SE(INDIRETO(E1&":"&E2), ">10")

Nesta fórmula, o argumento para a função INDIRETA contém:

A célula faz referência a E1 e E2, que contém os dados de texto D1 e D6.
O operador de intervalo, os dois pontos (:) entre aspas duplas (" ") que transforma os dois pontos em um texto string.
Dois e comerciais (&) que são usados para concatenar, ou unir, os dois pontos com as referências de célula E1 e E2.

O resultado é que INDIRETO converte a string de texto D1:D6 em uma referência de célula e a passa para a função CONT. SE a ser contada se as células referenciadas forem maiores que 10.

A função INDIRETO aceita qualquer entrada de texto. Podem ser células na planilha que contêm texto ou referências de células de texto inseridas diretamente na função.

Alterar dinamicamente o intervalo da fórmula

Lembre-se, o objetivo é criar uma fórmula com um intervalo dinâmico. Um intervalo dinâmico pode ser alterado sem editar a própria fórmula.

Ao alterar os dados de texto localizados nas células E1 e E2, de D1 e D6 para D3 e D7, o intervalo totalizado pela função pode ser facilmente alterado de D1:D6 para D3:D7. Isso elimina a necessidade de editar diretamente a fórmula na célula G1.

A função CONT. SE neste exemplo conta apenas células contendo números se forem maiores que 10. Embora quatro das cinco células no intervalo de D1:D6 contenham dados, apenas três células contêm números. As células que estão em branco ou contêm dados de texto são ignoradas pela função.

Contando texto com CONT. SE

A função CONT. SE não se limita a contar dados numéricos. Ele também conta as células que contêm texto, verificando se elas correspondem a um determinado texto.

Para fazer isso, a seguinte fórmula é inserida na célula G2:

=CONT. SE(INDIRETO(E1&":"&E2), "dois")

Nesta fórmula, a função INDIRETA referencia as células B1 a B6. A função CONT. SE totaliza o número de células que têm o valor de texto dois nelas.

Neste caso, o resultado é 1.

COUNTA, COUNTBLANK e INDIRETO

Duas outras funções de contagem do Excel são COUNTA, que conta células contendo qualquer tipo de dados, ignorando apenas células em branco ou vazias, e COUNTBLANK, que conta apenas células em branco ou vazias em um intervalo.

Como essas duas funções têm sintaxe semelhante à função CONT. SE, elas podem ser substituídas no exemplo acima por INDIRETO para criar as seguintes fórmulas:

=CONTA(INDIRETO(E1&":"&E2))

=CONTAR EM BRANCO(INDIRETO(E1&":"&E2)

Para o intervalo D1:D6, COUNTA retorna uma resposta de 4, pois quatro das cinco células contêm dados. COUNTBLANK retorna uma resposta de 1, pois há apenas uma célula em branco no intervalo.

Por que usar uma função INDIRETA?

O benefício de usar a função INDIRETO em todas essas fórmulas é que novas células podem ser inseridas em qualquer lugar do intervalo.

O intervalo muda dinamicamente dentro das várias funções, e os resultados são atualizados de acordo.

Sem a função INDIRETO, cada função precisaria ser editada para incluir todas as 7 células, incluindo a nova.

Os benefícios da função INDIRETO são que os valores de texto podem ser inseridos como referências de célula e que ela atualiza dinamicamente os intervalos sempre que sua planilha muda.

Isso torna a manutenção geral da planilha muito mais fácil, especialmente para planilhas muito grandes.