Baixe a apostila de exemplo
Este tutorial demonstrará como somar dados correspondentes a números específicos da semana no Excel e no Google Sheets.
Soma se pelo número da semana
Para “somar se” pelo número da semana, usaremos a função SUMIFS. Mas primeiro precisamos adicionar uma coluna auxiliar contendo a função WEEKNUM.
o Número da semana a coluna auxiliar é calculada usando a função WEEKNUM:
| 1 | = WEEKNUM (B3,1) |
A seguir, usaremos a função SUMIFS para somar todos Vendas que acontecem em um específico Número da semana.
| 1 | = SUMIFS (D3: D9, C3: C9, F3) |
Soma se pelo número da semana - sem coluna auxiliar
O método da coluna auxiliar é fácil de seguir, mas você também pode replicar o cálculo em uma única fórmula usando a função SUMPRODUCT em combinação com a função WEEKNUM para somar Número Total de Vendas por Número da semana.
| 1 | = SUMPRODUCT (- (WEEKNUM (B3: B9 + 0,1) = E3), C3: C9) |
Neste exemplo, podemos usar a função SUMPRODUCT para realizar cálculos complicados de “soma se”. Vamos examinar o exemplo acima.
Esta é a nossa fórmula final:
| 1 | = SUMPRODUCT (- (WEEKNUM (B3: B9 + 0,1) = E3), C3: C9) |
Primeiro, a função SUMPRODUCT lista a matriz de valores dos intervalos de células:
| 1 | =(--(({"1/3/2020"; "1/6/2020"; "1/9/2020"; "1/12/2020"; "1/15/2020"; "1/18/2020"; "1/21/2020"}+0,1)=1), {4; 9; 1; 7; 6; 2; 5}) |
Então, a função WEEKNUM calcula o Número da semana de cada um dos Datas de vendas.
A função WEEKNUM não foi projetada para funcionar com valores de matriz, portanto, devemos adicionar zero (“+0”) para que WEEKNUM processe os valores adequadamente.
| 1 | = SUMPRODUTO (- ({1; 2; 2; 3; 3; 3; 4} = 1), {4; 9; 1; 7; 6; 2; 5}) |
Número da semana valores iguais a 1 são alterados para valores TRUE.
| 1 | = SUMPRODUTO (- ({VERDADEIRO; FALSO; FALSO; FALSO; FALSO; FALSO; FALSO}), {4; 9; 1; 7; 6; 2; 5}) |
Em seguida, os travessões duplos (-) convertem os valores VERDADEIRO e FALSO em 1s e 0s:
| 1 | = SUMPRODUTO ({1; 0; 0; 0; 0; 0; 0}, {4; 9; 1; 7; 6; 2; 5}) |
A função SUMPRODUCT então multiplica cada par de entradas nas matrizes para produzir uma matriz de Número de Vendas que tem um Número da semana de 1:
| 1 | = SUMPRODUTO ({4; 0; 0; 0; 0; 0; 0}) |
Finalmente, os números na matriz são somados:
| 1 | =4 |
Esta fórmula é então repetida para os outros valores possíveis de Número da semana.
Mais detalhes sobre o uso de instruções booleanas e o comando “-” em uma função SUMPRODUCT podem ser encontrados aqui.
Referências de Célula de Bloqueio
Para tornar nossas fórmulas mais fáceis de ler, mostramos as fórmulas sem referências de células bloqueadas:
| 1 | = SUMPRODUCT (- (WEEKNUM (B3: B9 + 0,1) = E3), C3: C9) |
Mas essas fórmulas não funcionarão corretamente quando copiadas e coladas em outro lugar do arquivo. Em vez disso, você deve usar referências de células bloqueadas como este:
| 1 | = SUMPRODUCT (- (WEEKNUM ($ B $ 3: $ B $ 9 + 0,1) = E3), $ C $ 3: $ C $ 9) |
Leia nosso artigo sobre Bloqueio de referências de células para saber mais.
Soma se pelo número da semana no Planilhas Google
Essas fórmulas funcionam exatamente da mesma forma no Planilhas Google e no Excel.
No entanto, a função WEEKNUM é mais flexível no Planilhas Google do que no Excel e aceita entradas e saídas de matriz. Portanto, a operação {Array} +0 na fórmula WEEKNUM (B3: B9 + 0,1) não é necessária.
A fórmula SUMPRODUCT completa pode ser escrita no Planilhas Google como:
| 1 | =SUMPRODUTO(--(WEEKNUM($ B $ 3: $ B $ 9+0,1)=E3),$ C $ 3: $ C $ 9) |
