Excel
Somas condicionais avançadas
A forma usual de realizar somas condicionais em Excel implica o uso da função SUMIF. Por
exemplo, para, na tabela abaixo, se obter o total de horas trabalhadas pelo funcionário Manuel, a
fórmula a usar seria
=SUMIF(E4:E14;“Manuel”;F4:F14)
sendo que, o primeiro parâmetro é a gama de células em que o critério será aplicado, o 2º será o
critério propriamente dito e o 3º será a gama de células em que a soma condicional será
efectuada.
Mas, caso pretendamos efectuar uma soma condicional com um critério mais complexo,
envolvendo duas ou mais subcondições, a função SUMIF é já inadequada. Nesses casos, a
função SUMPRODUCT, usada normalmente com outra finalidade, torna-se muito útil. Referindonos, ainda, à tabela acima, vejamos como obter o total de horas trabalhado pela mesma pessoa
(Manuel) especificamente para o projecto X1. Usando a função SUMPRODUCT, a fórmula seria
=SUMPRODUCT((E4:E14=“Manuel”)*(D4:D14=“X1”)*(F4:F14))
Analizemos em detalhe a fórmula:
O 1º parâmetro especifica a 1ª condição, ou seja, que as células contidas na gama E4:E14
contenham “Manuel”. O 2º parâmetro especifica a 2ª condição, ou seja, que as células contidas
na gama E4:E14 contenham “X1”. O 3º parâmetro especifica qual a gama de células em que a
soma condicional se vai fazer. Nessa gama, sómente as células situadas em linhas que
obedeçam simultaneamente às duas condições especificadas serão somadas.
Outro exemplo de soma condicional com critério múltiplo seria o de calcular o total de horas
trabalhado por Manuel até ao dia "10-6-2003". Nessa caso, a fórmula seria
=SUMPRODUCT((E4:E14=E4)*(C4:C14<DATEVALUE("10-6-2003"))*(F4:F14))
Foi preciso nesta fórmula converter a data para o formato numérico com que o Excel representa
internamente datas. Para tal, foi usada a função DATEVALUE.
Repare-se ainda que a função SUMPRODUCT permite a utilização de um número muito longo de
sub-condições pelo que é possível construir critérios arbitrariamente complexos.
Com esta função é ainda possível efectuar contagens condicionais com critérios múltiplos. Se por
exemplo se eliminar da fórmula acima o 3º parâmetro (que especifica a gama onde a soma
condicional se fará), o resultado obtido é o do nº de dias trabalhados por aquele fucnionário até à
data em causa, ou seja, estamos perante uma contagem condicional que não seria possível com
um vulgar COUNTIF:
=SUMPRODUCT((E4:E14=E4)*(C4:C14<DATEVALUE("10-6-2003")))
Finalmente, apresenta-se um exemplo de uma aplicação da função SUMPRODUCT a uma caso
típico de soma de produtos. Considere-se a folha de cálculo abaixo:
Pretende-se com ela calcular as notas obtidas por cada aluno num dado teste, sabendo-se que
as classificações das várias alíneas estão dadas em percentagem e as respectivas cotações se
encontram nas células C20 a K20. Assim, cada nota será calculada por uma soma de productos,
representando cada produto a cotação obtida numa dada alínea (por exemplo, a cotação obtida
pelo aluno com o nº 205004 na alínea 2 será 3*20%). A forma mais expedita e flexível de
construir a fórmula de cálculo da nota será usar a função SUMPRODUCT, desta vez usada
apenas para o cálculo de soma de produtos:
=SUMPRODUCT((C11:K11)*($C$20:$K$20))
(1)
(1) – Estas fórmulas foram testadas na versão Excel 2000. A última fórmula não funciona no
Excel 2003. Neste caso há que substituir o asterisco (*) por um ponto e vírgula.
[ Versão corrigida a 16/5/2005 ]
António Silva, Junho de 2004
ISEP, Departamento de Informática