Somases em tabelas diferentes - PROC Matricial Excel - Guia do 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.
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.
Categorias AvançadoTags Excel 2007, Excel 2010, Excel 2013, Excel 2016, Excel 365, excel avançado, Excel Matricial, Exemplos de funções de soma, Função matricial, Funções de soma Excel, Matricial, PROC, Seerro, Soma, Soma básico, Soma com auto ajuste, Soma com auto incremento, Soma com desloc, Soma condicional OU, Soma entre planilhas, Soma maior Excel, Soma matricial, Soma tabela, Somar agregar, Somar células visíveis, Somar com operadores e coringas, Somar com um critério, Somar dias, Somar horas, Somar maior, Somar mês, Somar top, Somar TOP X, Somar valor hora, Somarproduto, Somase, somase subtotal, Somases, SOMASES duas tabelas, SOMASES matricial, Subtotal automático, subtotal somase