Fr:Les Fonctions SOMME.SI et SOMMEPROD

From NeoWiki

La fonction SOMME.SI appartient à la catégorie des fonctions mathématiques et la fonction SOMMEPROD à celle des fonctions de matrice. Elles vous permettent de travailler avec des plages de cellules satisfaisant à certains critères. Avant de les décrire, il est bon de détailler la différence entre une fonction (ou formule) et une fonction de matrice (ou formule de matrice).

Formules et formules de matrice

Une formule (ou fonction) de matrice permet de faire des calculs impliquant les valeurs d'une matrice, i.e. d'une plage rectangulaire contenant au moins deux cellules adjacentes. Une telle formule peut traiter plusieurs valeurs simultanément. Elle peut renvoyer plusieurs valeurs, ce qui évite d'avoir à remplir une plage de cellules en glissant le pointeur. Le résultat obtenu à l'aide d'une formule de matrice est une autre matrice. Pour valider la saisie d'une formule de matrice, appuyez sur les trois touches Cmd-Maj-Retour. La formule saisie est alors mise entre accolades dans la barre de formules. Mais vous ne pouvez pas créer une formule de matrice en saisissant vous-même ces accolades dans la barre de formules.

Exemple

Supposons que la plage A1:A20 contienne des valeurs numériques. Pour afficher le produit de ces valeurs par 5 dans la plage B1:B20, nous avons deux possibilités :
1) Cliquer dans la cellule B1, entrer la formule :

=A1*5

et presser la touche Retour. Ensuite remplir la plage B1:B20 en faisant glisser le pointeur. Voir l'article Sélection et remplissage d'une plage de cellules.

2) Cliquer dans la cellule B1, entrer la formule :

=A1:A20*5

et presser les trois touches Cmd-Maj-Retour.
La plage B1:B20 est automatiquement remplie.

Fonction SOMME.SI

Cette fonction est une fonction mathématique, non une formule de matrice. Elle permet de faire la somme d'une plage de cellules constituant une matrice et remplissant un critère. Ce critère doit être soit un nombre, soit une chaine de texte. La syntaxe est :

=SOMME.SI(plage_à_tester;condition_à_remplir;plage_à_sommer)

Supposons que la plage A1:A20 contienne des valeurs numériques.

• Pour additionner les nombres impairs de cette plage, vous devez utiliser une colonne auxiliaire, par exemple B, indiquant la parité des nombres de la plage A1:A20.
  

• Entrez dans la cellule B1 la formule

=EST.IMPAIR(A1:A20)

et pressez les touches Cmd-Maj-Retour

• Entrez dans une cellule vide la formule :

=SOMME.SI(B1:B20;1;A1:A20)

ou bien

=SOMME.SI(B1:B20;"VRAI";A1:A20)

et pressez la touche Retour

• Pour additionner les nombres de la plage A1:A20 qui sont supérieurs à la moyenne de cette plage, vous n'avez pas besoin d'une colonne auxiliaire, vous pouvez entrer dans une cellule vide la formule :

=SOMME.SI(A1:A20;">"&MOYENNE(A1:A20);A1:A20)

Note – Vous pouvez remplacer la fonction SOMME.SI par la formule de matrice SOMME. La syntaxe de cette fonction est :

=SOMME((plage=critère)*(plage_à_sommer))

Par exemple, dans les cas ci-dessus, vous pouvez entrer dans une cellule vide :

=SOMME((B1:B20=1)*(A1:A20))

ou

=SOMME((A1:A120>MOYENNE(A1:A20))*(A1:A20))

et presser les touches Cmd-Maj-Retour.

Fonction SOMMEPROD

Nous supposons toujours que la plage A1:A20 contient des valeurs numériques.
La fonction SOMMEPROD est une formule de matrice. Comme elle renvoie un simple nombre, il suffit de presser la touche Retour pour valider son entrée. Elle peut être utilisée
1)Pour calculer le produit scalaire de deux vecteurs.

2) Pour compter le nombre de cellules d'une plage satisfaisant à une ou à plusieurs conditions. La syntaxe de la fonction SOMMEPROD est alors la suivante :

=SOMMEPROD(plage=critère)

• Comptons par exemple le nombre de cellules contenant des nombres impairs :

Entrez dans une cellule vide la formule :

=SOMMEPROD(EST.IMPAIR(A1:A20))

et pressez la touche Retour.

• Comptons le nombre de cellules contenant des nombres supérieurs à la moyenne de cette plage :

Entrez dans une cellule vide la formule :

=SOMMEPROD(A1:A20>MOYENNE(A1:A20))

• Comptons le nombre de cellules contenant des nombres impairs et supérieurs à la moyenne

Entrez la formule suivante :

=SOMMEPROD((A1:A20>MOYENNE(A1:A20))*(EST.IMPAIR(A1:A20)))

et pressez la touche Retour. 3) Pour additionner tous les nombres d'une plage qui vérifient certains critères. La syntaxe de la fonction SOMMEPROD est alors :

=SOMMEPROD(plage_à_sommer;critére)

ou bien

=SOMMEPROD((plage_à_sommer)*(critère))

Dans chaque cas, vous pouvez intervertir l'ordre des facteurs.

• Additionner tous les nombres impairs d'une plage :

  Entrez dans une cellule vide, l'une ou l'autre des formules suivantes :

=SOMMEPROD(A1:A20;EST.IMPAIR(A1:A20))

ou bien

=SOMMEPROD((A1:A20)*(EST.IMPAIR(A1:A20)))

et pressez la touche Retour.

• Additionner tous les nombres supérieurs à la moyenne dans une plage :

  Entrez la formule suivante :

=SOMMEPROD(A1:A20;A1:A20>MOYENNE(A1:A20))

et pressez la touche Retour.

• Additionner les nombres impairs et supérieurs à la moyenne.

  Entrez la formule suivante :

=SOMMEPROD((A1:A20>MOYENNE(A1:A20))*(EST.IMPAIR(A1:A20))*(A1:A20))

et pressez la touche Retour.

Note Il peut être commode de donner des noms aux plages de cellules.
Supposons que les cellules de la plage A1:A10 contiennent la valeur "rouge" ou la valeur "verte", et que celles de la plage B1:B10 contiennent la valeur "grand" ou la valeur "petit". Si nous voulons calculer le nombre d'occurrences simultanées des valeurs "rouge" et "petit", nous pouvons entrer dans la cellule C1 la formule :

=SOMMEPROD(A1:A10="rouge";B1:B10="petit")

ou bien

=SOMMEPROD((A1:A10="rouge")*(B1:B10="petit"))

mais nous pouvons aussi donner des noms aux plages A1:A10 et B1:B10. Pour cela :

• Sélectionnez la plage A1:A10.
• Allez dans le menu Données et choisissez Définir la plage….
• Dans la fenêtre Définition d'une plage de bases de données entrez un Nom, par exemple "couleur" (sans guillemets).
• Cliquez sur le bouton Ajouter puis sur OK.
• Faites de même avec la plage B1:B10 que vous pouvez nommer "taille"
• Vous pouvez maintenant utiliser la formule :

=SOMMEPROD(couleur="rouge";taille="petit")

ou bien

=SOMMEPROD((couleur="rouge")*(taille="petit")).