Com base nos valores a seguir, como podemos determinar qual a soma dos valores que ocorreram desde a 5ª semana até a 12ª semana de 2011?
|
|
1 |
2 |
|
A |
Data |
Valor |
|
B |
15/01/2011 |
2.500 |
|
C |
30/01/2011 |
3.500 |
|
D |
13/02/2011 |
2.750 |
|
E |
28/02/2011 |
3.250 |
|
F |
14/03/2011 |
3.000 |
|
G |
31/03/2011 |
2.900 |
|
H |
15/04/2011 |
3.350 |
|
I |
30/04/2011 |
2.950 |
|
J |
16/05/2011 |
3.150 |
|
K |
31/05/2011 |
2.650 |
Poderíamos criar uma coluna adicional para calcular o número da semana correspondente a cada uma das datas e em seguida contar os valores que satisfaçam a condição acima. Mas vamos encurtar o trabalho utilizando a função SOMARPRODUTO. (Veja mais detalhes sobre SOMARPRODUTO em Somas e contagens condicionais).
Desta forma podemos fazer:
=SOMARPRODUTO(
(NÚMSEMANA(1*(A2:A11))>=5)*
(NÚMSEMANA(1*(A2:A11))<=12)*
(B2:B11)
)
Significado dos fatores:
NÚMSEMANA(1*(A2:A11))>=5: Retorna uma matriz com VERDADEIRO (V) para os valores que satisfazem à condição (nº da semana ≥ 5) e FALSO (F) para os demais valores.
Resultado: {F;V;V;V;V;V;V;V;V;V}
NÚMSEMANA(1*(A2:A11))<=12: Retorna uma matriz com VERDADEIRO para os valores que satisfazem à condição (nº da semana ≤ 12) e FALSO para os demais valores.
Resultado: {V;V;V;V;V;F;F;F;F;F}
Ao realizarmos o produto das duas matrizes acima obtemos 1(um) caso multipliquemos VERDADEIRO por VERDADEIRO e 0(zero) para quaisquer outras combinações.
Resultado: {0;1;1;1;1;0;0;0;0;0}
B2:B11: A série de valores numéricos cuja soma devemos obter
Resultado: {2500;3500;2750;3250;3000;2900;3350;2950;3150;2650}
Ao multiplicarmos esta matriz pelo resultado intermediário anterior, obteremos os valores que satisfazem às duas condições.
Resultado: {0;3500;2750;3250;3000;0;0;0;0;0}
Estes valores são somados por meio de SOMARPRODUTO, levando ao resultado final: 12.500.
Além de SOMARPRODUTO podemos utilizar a fórmula matricial:
=SOMA(
(NÚMSEMANA(1*(A2:A11))>=5)*
(NÚMSEMANA(1*(A2:A11))<=12)*
(B2:B11)
)
In Excel We Trust
Usuários do Excel 
Somarproduto é uma função que, depois de pronta na fórmula, fica fácil de entender.
Mas pra “montar” não é tão simples.
Por exemplo: se ao invês de somar o total da coluna B eu quisesse CONTAR quantas ocorrências satisfazem as condições. Como ficaria o somarproduto?
já quebrei a cabeça aqui e não consegui :(
Oi, Flavio.
Observe que no 3º passo da explicação (o produto dos dois vetores que testam as condições) é gerado um vetor de valores binários. Os valores 1 (um) correspondem às posições nas quais ambas as condições foram satisfeitas. A soma dos elementos deste vetor é, portanto, a contagem dos valores que satisfazem às condições testadas.
Veja a resposta a seguir:
=SOMARPRODUTO(
(NÚMSEMANA(1*(A2:A11))>=5)*
(NÚMSEMANA(1*(A2:A11))<=12)
)
Abraços
Boa !
Voce, com estas aulas de somarproduto envolvendo datas,
já eliminou 4 colunas extras que eu tinha numa planilha aqui.
Agora ainda resta uma coluna extra (e só uma. Eram 5).
Numa relação de
Data x Produto x Valor
a data pode repetir (num mesmo dia vende-se produtos diferentes)
e eu queria saber quantas segundas-feiras houve na coluna A (sem repetir).
Ou seja,
4/7/11 V
4/7/11 F
4/7/11 F
5/7/11 F
6/7/11 F
11/7/11 V
15/7/11 F
25/7/11 V
Neste caso queria retornar 3 (e não a soma das segundas-feiras, que seria 5)
Tem uma fórmula num post aqui que mostra quantas segundas-feiras houve num intervalo de datas. A fórmula do post retornaria 4.
Mas o problema é que a coluna A eventualmente pode pular alguma data.
No caso acima a resposta que eu preciso seria 3.
Será que tem um somarproduto para este caso?
Bom dia, Flavio.
A fórmula a seguir atende ao teu problema. Ao contrário do que normalmente ocorre, esta deve ser inserida como fórmula matricial.
=SOMARPRODUTO(
SEERRO(N(FREQÜÊNCIA(CORRESP($A$2:$A$9;$A$2:$A$9;0);
CORRESP($A$2:$A$9;$A$2:$A$9;0))0)*
(DIA.DA.SEMANA(A2:A9)=2);0)
)
[ ]s
no exemplo acima a coluna A não teve registro em 18/7 (uma segunda-feira)
e eu não queria contar este dia. Por isso a fórmula do post sobre quantas segundas-feiras houve num intervalo não resolve este problema.
É uma pena que não esteja mais desponibilizando arquivos com exemplos.
Peço desculpas,
Procuro postar arquivos nos casos em que haja uma maior complexidade, como é o caso do post de amanhã (04/07).
Gostaria do arquivo exemplo de algum post em particular?
Abraços
Adilson,
copiei e colei a fórmula acima e tá dando erro nesta parte:
CORRESP($A$2:$A$9;$A$2:$A$9;0))0)*
dá erro e fica negritado o último zero, depois de 0)) e antes do )*
eu já tentei fechar o parêntese antes, colocar ; e já tentei “entender” a lógica – mas nada.
Verifique, por favor, se faltou algum caracter neste ponto ou se é um caso em que o caracter não é permitido aqui. O fato é que, por enquanto, não funcionou.
Mas obrigado pela ajuda. Já eliminou 4 das 5 colunas extras aqui.
Flávio
Bom dia, meu caro.
Eis o arquivo de exemplo: Contagem exclusiva de dias da semana
naquele ponto que dava erro faltou o sinal de diferente !
este símbolo (o maior junto com o menor) não sai nos comentários.
pelo menos pra mim, aqui, este símbolo não aparece! aí dava o erro.
Funcionou perfeitamente. No more extra columns.
valeu demais.
Eu havia esquecido este detalhe, meu caro.
Devi às tags do html o sinal de diferente realmente apresenta este problema.
O importante é que eliminamos as suas colunas extras.
[ ]s
Eu tenho uma planilha para controle de orçamentos, faço contagem de pedidos por mês e seus respectivos valores. Estou com um problema para a contagem do mês de Janeiro, minha planilha está sem dados e a célula do mês de janeiro está dando 908, mas deveria dar 0.
Abaixo as formular que eu estou usando:
Está errado – Janeiro: =SOMARPRODUTO((MÊS($C$35:$C$942)=1)*1)
Está certo – Fevereiro: =SOMARPRODUTO((MÊS($C$35:$C$942)=2)*1)
E quando eu coloco a data que chegou pedido ele conta, porem para janeiro não está acontecendo
Onde estou errando?
Boa tarde, observe que, se A1 estiver vazia, a fórmula MÊS(A1) retorna o valor 1.
Por isso a fórmula utilizada retorna a soma das células vazias…
Experimente fazer SOMARPRODUTO((MÊS($C$35:$C$942)=1)*($C$35:$C$942””)*1)