segunda-feira, 5 de março de 2012

EXCEL 2007 - SOMAR VALORES COM CRITÉRIOS MÚLTIPLOS (SUMIF WITH MULTIPLE CRITERIA)


Na mensagem anterior apresentei um modelo de filtragem de dados com recurso a uma função UDF. Nesse mesmo modelo utilizei uma função para somar os valores constantes da tabela de acordo com vários critérios. Para executar este procedimento, existem várias alternativas utilizando as funções SOMARPRODUTO ou SOMA.SE.S:
  1. A célula com fundo rosa tem a seguinte fórmula: "=SOMARPRODUTO((BaseDados!$A$2:$A$5000=C$1)*(BaseDados!$C$2:$C$5000=$A4)*(BaseDados!$E$2:$E$5000))"
  2. A célula com fundo verde tem a seguinte fórmula: "=SOMARPRODUTO(--(BaseDados!$A$2:$A$5000=D$1);--(BaseDados!$C$2:$C$5000=$A5);--(BaseDados!$E$2:$E$5000))"
  3. Por fim, a célula com fundo laranja tem a fórmula: "=SOMA.SE.S(BaseDados!$E$2:$E$5000;BaseDados!$A$2:$A$5000;E$1;BaseDados!$C$2:$C$5000;$A6)".
Todas estas possibilidades permitem chegar ao mesmo resultado. A diferença está apenas na rapidez de cálculo que o Excel executa. Ora, se em pequenas tabelas essa diferença é praticamente inexistente, já quando se trata de um grande número de dados (por exemplo, uma tabela com milhares de linhas), a diferença de rapidez é consideravelmente notória.

Até à versão Excel 2003, só era possível executar somas baseadas em dois ou mais critérios recorrendo à função SOMARPRODUTO (ver as opções 1 e 2). O Excel 2007 introduziu a função SOMA.SE.S (que é um desenvolvimento da anterior SOMA.SE, permitindo mais do que um critério). Com esta possibilidade, há um ganho considerável na rapidez de cálculo.


In previous post I 've shown a template of data filtering using a UDF function. In the same template I used a function to sum ​​the values ​​listed in the table according to multiple criteria. To perform this procedure, there are several alternatives using the SUMPRODUCT function or SUMIFS:
  1.  The cell with pink background has the following formula: "=SUMPRODUCT ((BaseDados!$A$2:$A$5000=C$1)*(BaseDados!$C$2:$C$5000=$A4)*(BaseDados!$E$2:$E$5000))"
  2.  The cell with a green background has the following formula: "=SUMPRODUCT(--(BaseDados!$A$2:$A$5000=D$1),--(BaseDados!$C$2:$C$5000=$A5), --(BaseDados!$E$2:$E$5000))"
  3.  Finally, the cell with orange background has the formula: "=SUMIFS(BaseDados!$E$2:$E$5000,BaseDados!$A$2:$A$5000,E$1,BaseDados!$C$2:$C$ 5000,$A6)"
All these possibilities allow to reach the same result. The only difference is the speed of calculation that Excel runs. However, in small tables like the example we can't see no difference is the speed calculation. But in the case of a large number of data (eg, a table with thousands of rows), a faster proccedure is very important.

In Excel 2003 version, we could only run sums based on two or more criteria using the SUMPRODUCT function (see options 1 and 2). Excel 2007 introduced SUMIFS function (which is a development of the previous SUMIF, allowing more than one criteria). With this possibility, there is a considerable gain in speed of calculation.

3 comentários:

  1. Preciso fazer uma soma em que essa soma como valores de determinada coluna seja >0 e <10. como faço?

    ResponderEliminar
    Respostas
    1. Olá
      Se bem percebi a sua dúvida:
      Vamos supor que tem o intervalo A1:A50. Para somar os valores >0 e >10, introduza a seguinte fórmula:
      =SOMA.SE.S (A1:A50;A1:A50;">0";A1:A50;"<10")
      Nota: Está fórmula só é possivel a partir da versão 2007 do Excel.
      Se o seu office for o 2003, terá de usar a seguinte fórmula:
      =SOMARPRODUTO((A1:A50>0)*(A1:A50<10)*A1:A50)
      Espero ter ajudado
      João

      Eliminar
  2. Muito obrigada João!! Eu estava fazendo a formula do 2007 no 2003. Você é nota 10. :)

    ResponderEliminar