Até agora, aprendemos como somar toda a coluna correspondente de uma tabela no Excel. Mas como somamos os valores quando precisamos combinar coluna e linha. Neste artigo, aprenderemos como fazer a soma das linhas e colunas correspondentes.
Existem duas fórmulas para fazer isso, mas primeiro, vamos ao cenário.
Aqui, tenho uma tabela que registra as vendas feitas pelos funcionários em diferentes meses. O nome dos funcionários pode se repetir. Veja a figura abaixo:
Precisamos obter a soma do mês de maio em que o vendedor é Donald.
Método 1: Resumindo o cabeçalho da coluna e o cabeçalho da linha correspondentes usando a função SUMPRODUCT.
o Função SUMPRODUCT é a função mais versátil quando se trata de somar e contar valores com critérios complicados. A função genérica para somar por correspondência de coluna e linha é:
| = SUMPRODUCT ((colunas) * (cabeçalhos_colunas = cabeçalhos_colunas) * (cabeçalhos_linhas = cabeçalhos_linhas) |
Colunas:É o intervalo bidimensional das colunas que você deseja somar. Não deve conter cabeçalhos. Na tabela acima, é C3: N7.
column_headers:É o intervalo de cabeçalho decolunas que você deseja somar. Nos dados acima, é C2: N2.
column_heading: É o título que você deseja corresponder. No exemplo acima, está em B13.
Sem mais delongas, vamos usar a fórmula.
row_headers:É o intervalo de cabeçalho defilas que você deseja somar. Nos dados acima, é B3: B10.
row_heading: É o título que você deseja combinar nas linhas. No exemplo acima, está em F13.
Sem mais delongas, vamos usar a fórmula.
Escreva esta fórmula na célula D13 e deixe o excel fazer sua mágica (mágica não existe) …
| = SUMPRODUTO ((C3: N10) * (C2: N2 = B13) * (B3: B10 = E13)) |
Isso retorna o valor:
Agora, quando você altera o mês ou o vendedor, a soma mudará de acordo com o título da linha e o título da coluna.
Como funciona?
Essa lógica booleana simples.
(C2: N2 = B13): Esta instrução retornará uma matriz TRUE e FALSE. Todos os valores correspondentes na coluna serão verdadeiros e os outros terão falso. Nesse caso, teremos apenas um True, pois o intervalo C2: N2 contém apenas uma instância de maio em 5º Localização.
(B3: B10 = E13): Isso funcionará da mesma forma que acima e retornará uma matriz TRUE e FALSE. Todos os valores correspondentes terão TRUE e outros terão FALSE. Nesse caso, teremos 2 TRUEs, pois o intervalo B3: B10 tem duas instâncias de "Donald".
(C2: N2 = B13) * (B3: B10 = E13): Agora estamos multiplicando as matrizes retornadas por instruções. Isso implementará uma lógica e obteremos uma matriz de 1s e 0s. Agora teremos um array 2D que conterá 2 1s e os restantes 0s.
(C3: N10) *(C2: N2 = B13) * (B3: B10 = E13)= Finalmente, multiplicamos o array 2D com a tabela 2D. Ele retornará novamente uma matriz de 0s e os números que correspondem aos critérios.
finalmente, o SUMPRODUTO função irá somar a matriz que resultará na saída desejada.
Método 2: resumindo o cabeçalho da coluna e o cabeçalho da linha correspondentes usando as funções SOMA e SE
A fórmula genérica para somar linhas e colunas correspondentes usando as funções SOMA e SE do Excel é:
| = SOMA (SE (cabeçalhos_coluna = cabeçalho_coluna, SE (cabeçalhos_linha = cabeçalho_linha, colunas))) |
Todas as variáveis são iguais às do método explicado acima. Aqui, eles só precisam ser usados em uma ordem diferente.
Escreva esta fórmula na célula D13:
| = SOMA (SE (C2: N2 = B13, SE (B3: B10 = E13, C3: N10))) |
Isso retorna a resposta correta. Veja a imagem abaixo:
Como funciona?
A lógica é a mesma do primeiro método SUMPRODCUT, mas o mecanismo é diferente. Se eu explicar isso resumidamente, a função IF interna retorna um array 2D da mesma dimensão da tabela. Esta matriz contém o número de duas linhas correspondentes. Em seguida, a função IF interna corresponde aos títulos de duas colunas nesta matriz e retorna a matriz 2D que contém apenas os números que correspondem a ambos, coluna e título. Todos os outros elementos do array serão FALSE.
Finalmente, a função SUM soma esse array e obtemos nossa soma.
Então, sim pessoal, é assim que você pode somar as linhas e colunas correspondentes de uma tabela no Excel. Espero que tenha sido explicativo e útil para você. Se você tiver alguma dúvida a respeito deste tópico ou tiver alguma outra dúvida relacionada ao excel / VBA, pergunte na seção de comentários abaixo.
Como somar coluna em um Excel por título de correspondência | Se quiser obter a soma de uma coluna apenas usando o nome da coluna, você pode fazer isso de 3 maneiras fáceis no Excel. A sintaxe do método SUMPRODUCT para somar a coluna correspondente é:
SUMIF com referência 3D no Excel |O fato engraçado é que a referência normal do Excel 3D não funciona com funções condicionais, como a função SOMASE. Neste artigo, aprenderemos como fazer a referência 3D funcionar com a função SUMIF.
Referência relativa e absoluta no Excel | Referenciar no Excel é um tópico importante para todo iniciante. Até mesmo usuários experientes do Excel cometem erros ao fazer referência.
Referência de planilha dinâmica | Forneça planilhas de referência dinamicamente usando a função INDIRETA do Excel. Isso é simples …
Expandindo referências no Excel | A referência de expansão se expande quando copiada para baixo ou para a direita. Usamos o sinal $ antes do número da coluna e da linha para fazer isso. Aqui está um exemplo …
Tudo sobre referência absoluta | O tipo de referência padrão no Excel é relativo, mas se você quiser que a referência de células e intervalos seja absoluta, use o sinal $. Aqui estão todos os aspectos da referência absoluta no Excel.
Artigos populares:
50 atalhos do Excel para aumentar sua produtividade | Torne sua tarefa mais rápida. Esses 50 atalhos tornarão seu trabalho ainda mais rápido no Excel.
A função VLOOKUP no Excel | Esta é uma das funções mais usadas e populares do Excel, que é usada para pesquisar valores em diferentes intervalos e planilhas.
CONT.SE no Excel 2016 | Conte valores com condições usando esta função incrível. Você não precisa filtrar seus dados para contar um valor específico. A função Countif é essencial para preparar seu painel.
Como usar a função SUMIF no Excel | Esta é outra função essencial do painel. Isso ajuda você a somar valores em condições específicas.