0111-Calculando valores num intervalo de semanas

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

Marcado com: , , , , , , , ,
Publicado em Nível: Intermediário
11 comentários em “0111-Calculando valores num intervalo de semanas
  1. flaviox27 disse:

    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

  2. flaviox27 disse:

    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

  3. flaviox27 disse:

    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.

  4. José Arnaldo disse:

    É uma pena que não esteja mais desponibilizando arquivos com exemplos.

  5. flaviox27 disse:

    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

  6. flaviox27 disse:

    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.

Deixe uma resposta

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s

Visitas dos usuários
  • 1,963,899

Clique para assinar este blog e receber notificações de novos artigos por email.

Junte-se a 1.476 outros seguidores

Aumente sua interação

Siga-nos do twitter (@usuariosdoexcel).

Acesse o nosso fórum e compartilhe as suas dúvidas.

Curta nossa página no Facebook.

Junte-se ao grupo do LinkedIn.

Visite-nos utilizando o seu smartphone ou tablet.

Recomende este Blog
Visualizar notícias
Site monitorado:
Real Time Web Analytics

Clicky

%d blogueiros gostam disto: