Neste artigo você aprenderá como fazer uso do subtotal com cont.ses, permitindo contar somente as células visíveis com condições no Excel.
No exemplo iremos mostrar duas formas de contarmos somente as linhas visíveis do Excel.
Veja também como contar somente células visíveis no Excel: https://www.guiadoexcel.com.br/excel-funcao-subtotal-com-somase-subtotalsomase/.
No vídeo abaixo você tem a aula explicando como fazer cont.ses com subtotal, e logo em seguida você pode ver passo-a-passo como fazer.
Exemplo de Aplicação
É uma situação comum termos listas ou tabelas que precisamos filtrar no Excel para analisar os dados.
No nosso exemplo temos uma lista de vendas de 4 empresas de roupas.
Nela você tem também outros dados que queremos filtrar, região, ano e mês. Veja a planilha:
No quadro acima queremos colocar uma contagem condicional somente das células visíveis.
No intervalo de B9 à F12 iremos colocar as fórmulas para realizar estes filtros.
Cont.ses e Subtotal no Excel – Solução 1
Uma das soluções para usar as funções cont.ses e subtotal é utilizando uma coluna auxiliar.
Para isso clique na sua lista ou tabela e inclua uma coluna, podemos chamar de Auxiliar.
Digite a seguinte fórmula: =SUBTOTAL(3;[@Empresa])
A função subtotal aplicada nesta coluna tem por finalidade retornar 1 ou 0, pois está aplicada à coluna empresa.
Se esta informação estiver visível ela retornará 1, senão retornará 0.
A opção que foi passada foi 3, que é a contagem na função Subtotal.
Com isso nós já temos um controle das células que estão visíveis, e conseguimos colocar na função CONT.SES a verificação se esta coluna está como 1 ou 0.
A função que usamos na coluna Total é =CONT.SES(tVendas[Empresa];$A9;tVendas[Auxiliar];1).
Nela usamos a função CONT.SES, somando quando a coluna tVendas[Empresa] for igual a informação na célula A9, Roupas Infantis E também quando a coluna Auxiliar (tVendas[Auxiliar]) estiver como 1, ou seja, visível.
O resultado que temos é que somente serão contadas as células visíveis que atendam a condição de ser a empresa selecionada.
Podemos adicionar também novos critérios para contagem, quantos quiser.
No exemplo incluímos nas colunas Norte, Sul, Leste e Oeste uma função para contar somente as que são daquela empresa e região.
A fórmula que usamos foi: =CONT.SES(tVendas[Empresa];$A9;tVendas[Região];C$8;tVendas[Auxiliar];1).
Temos então a contagem agora apenas das células visíveis que atendem as condições.
Veja abaixo, que temos 88 no total da empresa e 22 em cada uma das regiões da empresa.
Aplicando o mesmo procedimento você pode colocar várias condições na função CONT.SES e também pode aplicar filtros na tabela manualmente ou pela segmentação de dados.
Cont.ses + Subtotal Sem Coluna Auxiliar – Segunda Solução
O procedimento de contagem condicional somente das células visíveis pode ser feito também sem coluna auxiliar.
Nesta segunda solução nós não aplicamos uma coluna auxiliar.
Para isso nós usamos 5 funções na fórmula que irei explicar abaixo.
Veja a função abaixo na imagem:
Entenda detalhes da função:
=SOMARPRODUTO(SE(tRoupas[Empresa]=$A8;1)*SUBTOTAL(3;DESLOC($A$15;LIN(tRoupas[Empresa])-LIN($A$15);)))
A função SOMARPRODUTO realiza a soma do resultado das multiplicações.
Nela colocamos então várias condições, veja cada uma delas:
- SE(tRoupas[Empresa]=$A8;1) = Verifica se a coluna empresa tem a informação da empresa. O resultado desta operação é VERDADEIRO ou FALSO, para o Excel é interpretado como 1 ou 0, gerando então uma lista de {1;1;1;1;0;0;0;0;}, conforme atendem ou não o critério.
LIN(tRoupas[Empresa])-LIN($A$15) = Nesta parte estamos criando uma Matriz de dados, com números começando de 0 até a quantidade total de linhas que temos na lista. {0;1;2;3;4;5;6;7;8;9}, esta lista é utilizada para aplicar o subtotal. Esta rotina fará com que sejam listados da função DESLOC todas as posições que temos de dados desde a célula A15.
DESLOC($A$15;LIN(tRoupas[Empresa])-LIN($A$15);) = Aqui temos a criação da seguinte lista: {“Roupas infantis Ltda”;”Roupas infantis Ltda”;”Roupas infantis Ltda”;….}, retornando então não os dados, mas as células à partir de A15, uma a uma.
*SUBTOTAL(3;DESLOC( = Neste ponto temos a contagem pela função subtotal de todos os dados de cada uma das linhas, retornando {0;0;0;0;0;0;0;0;0;….}. Na prática ela faz exatamente o que foi feito com a coluna auxiliar.
Por fim temos a multiplicação dos critérios do SE… com o SUBTOTAL… e temos então o retorno a contagem apenas dos itens que aparecem na lista.
Contar com Subotltal e Sem Coluna Auxiliar com Vários Critérios no Excel
No tópico anterior, você viu como fazer uma fórmula para contar somente as células visíveis e sem usar uma coluna auxiliar.
No exemplo anterior tínhamos apenas um critério, a empresa.
Para realizar a contagem com várias condições você pode aplicar da seguinte forma.
A fórmula utilizada é:
=SOMARPRODUTO(SE(tRoupas[Empresa]=$A8;SE(tRoupas[Região]=C$7;1))*SUBTOTAL(3;DESLOC($A$15;LIN(tRoupas[Empresa])-LIN($A$15);))).
Na fórmula acima está destacada apenas a primeira parte, antes da multiplicação.
Aqui temos a condição anterior: SE(tRoupas[Empresa]=$A8 e caso ela seja atendida é feita mais uma verificação: SE(tRoupas[Região]=C$7, ou seja, se a empresa é a Roupas Infantis Ltda e se for verifica se a Região é a Norte.
Se as duas condições forem satisfeitas ela retorna 1, senão 0.
Desta forma temos a seguinte multiplicação, como no anterior {1;0;1;0;1;1;1;1;0…}* {0;0;0;0;0;1;1;1;1…} , tendo como resultado uma matriz única {0;0;0;0;0;0;1;1;1;1…}, que será somada e será o resultado a quantidade de itens.
Como pode notar acima, temos a contagem apenas das células que estão definidas na segmentação de dados, podendo aplicar também no filtro ou simplesmente ocultar manualmente células.
Download Exemplo de Planilha Cont.ses + Subtotal
Realize o download da planilha do exemplo. Basta se inscrever na nossa newsletter gratuita para o download automático.
