Somases em tabelas diferentes – PROC Matricial Excel
Um amigo me pediu uma ajuda com um problema diferente, ele tem uma lista aonde parte dos critérios está em uma tabela e os valores estão em outra, o objetivo é somar na segunda lista quando os critérios da primeira lista forem atendidos. Veja também sobre fórmulas matriciais em: http://www.linhadecodigo.com.br/artigo/781/excel-formulas-matriciais.aspx
Você pode achar interessante também este artigo: PROCV com mais de um resultado.
Usando uma coluna a mais na segunda lista nós conseguiríamos resolver este problema fazendo a relação e retornando via PROCV a coluna preenchida, porém não queremos usar colunas auxiliares.
Solução do problema
Para realizar o somases com duas listas diferentes nós utilizamos uma fórmula matricial aonde usamos as funções SOMA, SE e PROC.
A função PROC é o ponto chave da fórmula, pois nós precisamos retornar dados da primeira lista para a segunda, de modo que possamos filtrar somente os dados que desejamos somar.
O PROC no Excel funciona em fórmulas matriciais, diferente do PROCV. Vamos entender toda a fórmula:
Na tabela um nós temos a coluna CTA com os valores 1.1.1, 1.1.2, 1.2 e 1.3, eles se ligam com a tabela 2, aonde temos os mesmos valores. Cabe salientar que não é necessário que estejam em ordem ou que não se repitam, isso é apenas coincidência do exemplo.
Na tabela resultado, desejamos somar apenas os valores da lista 2, em que na lista 1 esteja preenchida a coluna AGL com os valores que digitarmos, como no exemplo A, B e C.
A fórmula que utilizamos para resolver o problema é:
=SOMA(SE(PROC($F$4:$F$7;$B$4:$C$7)=D14;$G$4:$G$7)) e CTRL + SHIFT + ENTER, para ela ficar matricial.
Vamos ver as partes da fórmula.
- PROC($F$4:$F$7;$B$4:$C$7) -> Realiza a busca dos valores de F4 à F7, que estão na tabela 2, na lista de dados da tabela 1, a função PROC busca na coluna B e retorna os valores da última coluna, no caso a coluna C, aonde temos os valores do filtro de dados.
- SE(PROC($F$4:$F$7;$B$4:$C$7)=D14;$G$4:$G$7) -> Se os valores retornados forem o que está sendo filtrado, no exemplo o filtro é o A, então são somados os valores de G4 à G7. Dois pontos aqui, veja que se selecionamos a parte da função PROC e pressionamos F9 podermos ver o resultado e ficar mais simples de compreender a fórmula, SE({“A”;”A”;”B”;”C”}=D14;$G$4:$G$7), e também a quantidade de linhas selecionadas nas fórmulas deve ser sempre igual, pois senão a fórmula matricial incorrerá em um erro, por isso veja que todos vão do mesmo intervalo das linhas 4 à 7.
- SOMA(SE(PROC($F$4:$F$7;$B$4:$C$7)=D14;$G$4:$G$7)) -> Por fim temos a soma dos valores, aonde os resultados que foram filtrados na tabela 1 são somados na tabela 2, veja como fica se pressionarmos F9 para toda a fórmula =SOMA({10;15;FALSO;FALSO}), são retornados os valores 10 e 15, que correspondem aos valores das CTA 1.1.1 e 1.1.2 que tem na coluna AGL o valor A, e retornam FALSO para as demais que não atendem a este critério.
Então é esta a fórmula utilizada, lembrando que ao final dela, para que ela atue de forma matricial, é necessário que seja pressionado CTRL+SHIFT+ENTER.
E perceba também que nem todas as funções do Excel operam em fórmulas matriciais, como é o caso do PROCV, aqui substituído pelo PROC, e também veja que o retorno FALSO, é ignorado do SOMA, não incorrendo em erro.
Espero que este artigo tenha te ajudado e para maiores dúvidas você pode realizar o download desta planilha assinando nossa newsletter gratuita.
Download
Para o download do exemplo utilizado neste artigo preencha o seu nome e e-mail no formulário abaixo.
Abraço
Marcos Rieper
Curso Excel Completo – Do Básico ao VBA
Quer aprender Excel do Básico, passando pela Avançado e chegando no VBA? Clique na imagem abaixo:
