Veja neste artigo como somar colunas dinâmicas no Excel, colunas variáveis.
1. Introdução Somar Colunas Dinâmicas no Excel
Em virtude de termos dados desestruturados, por diversas vezes resolver problemas não é tão trivial no Excel. A base a seguir mostra um exemplo de dados que cresceram horizontalmente, dificultando nosso trabalho de encontrar o total referente à uma filial escolhida.
Em verdade, um grande problema de muitos usuários é dispor dados em formato de resumos, o que dificulta a modelagem para outras visões. Neste nosso caso, ao escolhermos uma certa filial, temos que identificar em que coluna ela está situada para obter o intervalo a somar. Dados com esta apresentação são facilmente tratados com o Power Query para permitir as mais diversas tarefas de maneira otimizada, como, por exemplo, uma comparativo mensal de faturamento das filiais.
A imagem a seguir exibe uma amostra da disposição ideal para este mesmo conjunto de dados.
Este artigo apontará como podemos obter a soma de colunas dinâmicas, tomando como base os dados desestruturados exibidos na primeira imagem, sem fazer tratamento, usando exclusivamente fórmulas. Acompanhe este assunto abordado em vídeo: https://youtu.be/06sB5S4tJOc.
2. O problema das colunas dinâmicas
Como desejamos filtrar uma filial dentre as 9 existentes (da coluna D, Joinville, até a coluna L, Campo Grande), o intervalo de valores a somar não ocupará posição constante, o que ocorreria caso os dados estivessem melhor dispostos (como apresentado na imagem anterior). Teremos então colunas dinâmicas, com posições variáveis a identificar.
Por conta disso, precisamos de algum artifício para identificar o intervalo, com base na filial escolhida, a fim de somar estes valores e chegar ao total desejado.
Conforme imagem anterior, nossa filial filtrada está definida em O2. Este cenário simulado será abordado para expor cada uma das 5 soluções propostas com fórmulas.
3. Soluções com fórmulas
Este artigo objetiva expor 5 fórmulas úteis para resolvermos o nosso problema, sem precisar de linha auxiliar que totalize cada filial. Em todas as fórmulas sempre usaremos a função SOMA. O nosso foco será inserir uma fórmula que retorne somente os valores referentes à filial escolhida, à qual ficará encapsulada como argumento da função SOMA.
Para isso, teremos solução que não inclui mais funções além de SOMA, além de soluções com as funções SE, PROCX, ÍNDICE e FILTRO.
a) Usando apenas a função SOMA
Esta solução é a mais enxuta apresentada neste artigo. Exceto esta, todas as demais utilizam mais funções além da função SOMA.
Nesta fórmula, comparamos com a filial escolhida (em azul na imagem a seguir) todo o intervalo que contém as 9 filiais (em vermelho na imagem a seguir). Isto gera um vetor com FALSO (8 vezes) e VERDADEIRO (apenas 1 vez). Este vetor é multiplicado por todos os valores existentes, referentes a todas as filiais (em verde na imagem a seguir). O resultado desta multiplicação é finalmente somado com a função SOMA. Veja a imagem a seguir.
A fórmula inserida foi: =SOMA((D2:L2=O2)*(D3:L22)).
b) Usando a função SE
Esta solução está baseada na função SE. Com ela, testamos se cada célula das filiais (em vermelho na imagem a seguir) trata-se da filial escolhida (em azul na imagem a seguir). Quando isso ocorre, retornamos o conjunto de todos os valores existentes, referentes a todas as filiais (em verde na imagem a seguir). A correspondência verdadeira, que ocorre quando a filial escolhida é encontrada, faz com que apenas os seus valores sejam retornados. Este resultado é finalmente somado com a função SOMA. Veja a imagem a seguir.
A fórmula inserida foi: =SOMA(SE(D2:L2=O2;D3:L22)).
c) Usando a função PROCX
Esta solução está baseada na função PROCX. Com ela, procuramos a filial escolhida (em azul na imagem a seguir) no intervalo de todas as filiais (em vermelho na imagem a seguir). Ela localiza esta filial corretamente e retorna exatamente os valores que precisamos, referentes apenas à filial escolhida, dentre todos os valores possíveis das filiais (em verde na imagem a seguir). Este conjunto de valores é somado com a função SOMA. Veja a imagem a seguir.
À época da publicação deste artigo, apenas assinantes do Microsoft 365 possuíam acesso à função FILTRO. A fórmula inserida foi: =SOMA(PROCX(O2;D2:L2;D3:L22)).
d) Usando a função FILTRO
Esta solução está baseada na função FILTRO, poderosa função do grupo de funções de matrizes dinâmicas. Com ela, dentre todos os valores possíveis de serem somados (em verde na imagem a seguir), é retornada apenas a coluna de valores da filial escolhida (em azul na imagem a seguir). Dentre as 9 colunas das filiais disponíveis (em vermelho na imagem a seguir), o que faz o filtro/seleção da coluna desejada é o segundo argumento de FILTRO, disponibilizado pela função CONT.SE, que conta quantas vezes cada uma das filiais existentes (em vermelho na imagem a seguir) ocorre no intervalo da filial escolhida (em azul na imagem a seguir). Com os valores corretos retornados, a função SOMA finaliza, totalizando a filial escolhida. Veja a imagem a seguir.
À época da publicação deste artigo, apenas assinantes do Microsoft 365 possuíam acesso à função FILTRO. A fórmula inserida foi: =SOMA(FILTRO(D3:L22;CONT.SE(O2;D2:L2))).
e) Usando a função ÍNDICE
Esta solução baseada na função ÍNDICE é bastante interessante. Você pode acompanhar melhor o princípio abordado nesta função através do artigo no link https://www.guiadoexcel.com.br/indice-corresp-excel-varios-mais-de-uma-linha/. Com a função ÍNDICE, dentre todos os valores possíveis de serem somados (em verde na imagem a seguir), conseguimos obter a coluna com os valores referentes à filial escolhida (em azul na imagem a seguir). A localização da coluna de nosso interesse, correspondente à filial filtrada (em azul na imagem a seguir), é disponibilizada pela função CORRESP, que procura a posição da filial que desejamos (em azul na imagem a seguir) dentre as filiais disponíveis (em vermelho na imagem a seguir). O ponto principal a destacar é que, na função ÍNDICE, o seu 2º argumento, que indica o número da linha, fica em branco (por isso temos ponto e vírgula duas vezes consecutivas). Quando não definimos uma linha específica, todas serão retornadas, permitindo assim que obtenhamos todos os valores da coluna selecionada. Por fim, a função SOMA encerra a nossa tarefa, somando os valores.
A fórmula inserida foi: =SOMA(ÍNDICE(D3:L22;;CORRESP(O2;D2:L2;0))).
Download planilha Como Somar Colunas Dinâmicas no Excel
Clique no botão abaixo para realizar o download da planilha de somar colunas dinâmicas no Excel, com exemplo de dados:
