01 de 02
Suma de cel·les que es troben entre dos valors
Lifewire
La funció SUMPRODUCT a Excel és una funció molt versàtil que donarà resultats diferents segons com s'introdueixin els arguments de la funció.
Normalment, com el seu nom indica, SUMPRODUCT multiplica els elements d'una o més matrius per obtenir el seu producte i després suma o suma els productes junts.
Ajustant la sintaxi de la funció, però, es pot utilitzar per sumar només les dades de les cel·les que compleixen uns criteris específics.
Des d'Excel 2007, el programa inclou dues funcions - SUMIF i SUMIFS - que sumen les dades en cel·les que compleixin un o més criteris establerts.
De vegades, però, és més fàcil treballar amb SUMPRODUCT quan es tracta de trobar diverses condicions relacionades amb el mateix interval, tal com es mostra a la imatge de d alt.
SUMPRODUCT Sintaxi de la funció per sumar cel·les
La sintaxi utilitzada per aconseguir que SUMPRODUCT sumi les dades de les cel·les que compleixen condicions específiques és:
=SUMPRODUCTE([condició1][condició2][matriu])
condition1, condition2: les condicions que s'han de complir abans que la funció trobi el producte de la matriu.
matriu: un rang contigu de cel·les
Exemple: suma de dades en cel·les que compleixen diverses condicions
L'exemple de la imatge de d alt afegeix les dades a les cel·les de l'interval D1 a E6 que estan entre 25 i 75.
Introducció a la funció SUMPRODUCT
Com que aquest exemple utilitza una forma irregular de la funció SUMAPRODUCTE, el quadre de diàleg de la funció no es pot utilitzar per introduir la funció i els seus arguments. En comptes d'això, la funció s'ha d'escriure manualment en una cel·la del full de treball.
- Feu clic a la cel·la B7 del full de treball per convertir-la en la cel·la activa;
- Introduïu la fórmula següent a la cel·la B7: =SUMAPRODUCTE(($A$2:$B$6>25)($A$2:$B$6<75)(A2:B6))
- La resposta 250 hauria d'aparèixer a la cel·la B7
- La resposta es va arribar afegint els cinc nombres de l'interval (40, 45, 50, 55 i 60) que estan entre 25 i 75. El total dels quals és 250
Desglossament de la fórmula SUMPRODUCT
Quan s'utilitzen condicions per als seus arguments, SUMPRODUCT avalua cada element de la matriu amb la condició i retorna un valor booleà (VERTADER o FALS).
A efectes de càlculs, Excel assigna un valor de 1 per als elements de la matriu que són VERDADERS (compleixin la condició) i un valor de 0per als elements de matriu que són FALS (no compleixen la condició).
Per exemple, el número 40:
- és TRUE per a la primera condició, de manera que s'assigna un valor de 1 a la primera matriu;
- és TRUE per a la segona condició, de manera que s'assigna un valor de 1 a la segona matriu.
El número 15:
- és FAL per a la primera condició, de manera que s'assigna un valor de 0 a la primera matriu;
- és TRUE per a la segona condició, de manera que s'assigna un valor de 1 a la segona matriu.
Els corresponents uns i zeros de cada matriu es multipliquen junts:
- Per al número 40: tenim 1 x 1 que retorna un valor d'1;
- Per al número 15: tenim 0 x 1 que retorna un valor de 0.
Multiplicar els uns i els zeros pel rang
Aquests uns i zeros es multipliquen després pels nombres de l'interval A2: B6
Això es fa per donar-nos els números que es sumaran per la funció.
Això funciona perquè:
- 1 vegades qualsevol número és igual al número original
- 0 vegades qualsevol nombre és igual a 0
Així que acabem amb:
-
140=40
015=0
022=0
145=45
150=50
155=55
025=0
075=0
160=600100=0
Resum dels resultats
A continuació, SUMPRODUCT resumeix els resultats anteriors per trobar la resposta.
40 + 0 + 0 + 45 + 50 + 55 + 0 + 0 + 60 + 0=250
