Como usar a função excel sumproduct em modelos de dados

Video: How to Use the SUMPRODUCT Function in Excel

o SUMPRODUCT função é realmente listados sob a matemática e trigonometria categoria de funções do Excel. Porque o objetivo principal de SUMPRODUCT é para calcular o produto da soma, a maioria das pessoas não sabe que você pode realmente usá-lo para procurar valores. Na verdade, você pode usar esta função versátil bastante eficaz na maioria dos modelos de dados.

básico SUMPRODUCT

o SUMPRODUCT função é projetada para multiplicar os valores de dois ou mais intervalos de dados e, em seguida, adicione os resultados para retornar a soma dos produtos. Dê uma olhada na figura a seguir para ver um cenário típico em que o SUMPRODUCT é útil.

SUMPRODUCT, ficando as vendas totais envolve multiplicando pric”width =” 458 "/>

Você vê uma análise comum em que você precisa do total de vendas para os anos de 2011 e 2012. Como você pode ver, para obter o total de vendas para cada ano, você primeiro tem que multiplicar preço pelo número de unidades para obter o total para cada Região. Então você tem que somar esses resultados para obter o total de vendas para cada ano.

Com o SUMPRODUCT função, você pode realizar a análise de duas etapas com apenas uma fórmula. A figura seguinte mostra a mesma análise com SUMPRODUCT fórmulas. Ao invés de usar 11 fórmulas, você pode realizar a mesma análise com apenas 3!

função SUMPRODUCT permite realizar a mesma análise com apenas”width =” 535 "/ gt;

A sintaxe do SUMPRODUCT função é bastante simples:

SUMPRODUCT (array1, array2, ...)

ordem: ordem representa um intervalo de dados. Você pode usar em qualquer lugar de 2 a 255 matrizes em um SUMPRODUCT Fórmula. As matrizes são multiplicados em conjunto e, em seguida, adicionado. A única regra rápida dura e você tem que lembrar é que todas as matrizes devem ter o mesmo número de valores. Ou seja, você não pode usar o SUMPRODUCT Se gama X tem 10 valores e gama Y tem 11 valores. Caso contrário, você começa a #VALOR! erro.

Uma torção na função SUMPRODUCT

A coisa interessante sobre o SUMPRODUCT função é que ele pode ser usado para filtrar os valores. Dê uma olhada na figura a seguir:

SUMPRODUCT função pode ser utilizado para filtrar dados com base em critérios.”largura =” 535 "/>

A fórmula na célula E12 é puxando a soma do total de unidades para apenas a região do Norte. Enquanto isso, a célula E13 está puxando as unidades registradas para a região Norte no ano de 2011.

Para entender como isso funciona, dê uma olhada na fórmula na célula E12. Essa fórmula lê SUMPRODUCT ((C3: C10 =“Norte“) * (E3: E10)).

Video: Excel Magic Trick 777: SUMPRODUCT Function -- Basics To Advanced (14 Examples)

No Excel, VERDADE avalia a 1 e FALSO avalia a 0. Cada valor na coluna C, que é igual a Norte avalia a VERDADE ou 1. Quando o valor não é Norte, avalia a FALSO ou 0. O parte da fórmula que lê (C3: C10 =“Norte“) enumera através de cada valor no intervalo C3: C10, atribuir um 1 ou 0 para cada valor. Em seguida, internamente, o SUMPRODUCT fórmula traduz

(1 * E3) + (0 * E4) + (0 * E5) + (0 * E6) + (1 * E7) + (0 * E8) + (0 * E9) + (0 * E10).

Isto dá-lhe a resposta de 1628 porque

(1 * 751) + (0 * 483) + (0 * 789) + (0 * 932) + (1 * 877) + (0 * 162) + (0 * 258) + (0 * 517)

é igual a 1,628.

Aplicação de fórmulas SUMPRODUCT em um modelo de dados

Como sempre no Excel, você não tem que codificar os critérios em suas fórmulas. Em vez de usar explicitamente “Norte“ no SUMPRODUCT fórmula, pode fazer referência a uma célula que contém o valor do filtro. Você pode imaginar que a célula A3 contém a palavra Norte, caso em que você pode usar (C3: = C10 A3) ao invés de (C3: C10 =“Norte“). Dessa forma, você pode alterar dinamicamente seus critérios de filtro, e sua fórmula mantém-se.

A figura a seguir demonstra como você pode usar este conceito para puxar os dados em uma tabela de teste com base em vários critérios. Note-se que cada um dos SUMPRODUCT fórmulas mostradas aqui faz referência a células B3 e C3 para filtrar por conta e linha de produtos. Novamente, você pode adicionar listas drop-down de validação de dados para as células B3 e C3, o que lhe permite mudar facilmente critérios.

SUMPRODUCT função pode ser usada para puxar números resumidos da d”largura =” 535 "/ gt;