Avec la fonction RECHERCHEX, vous pouvez extraire des données d’un tableau.
Or, on peut pousser encore plus loin en faisant des statistiques par le biais des calculs, notamment avec la fonction SOMME.
C’est parti !
Les opérateurs matriciels
Qu’est ce qui se cache sous ce nom ?
En fait, lorsqu’on utilise une fonction matricielle comme SOMME, comment sait elle ce qu’il faut additionner ?
Il existe 3 opérateurs dits matriciels à cet effet :
Les 2 points (:)
Indique de prendre tout ce qui va d’un point A à un point B. C’est une plage
Pour faire la somme d’un point A à un point B, on va utiliser les 2 points entre les références des deux cellules.
=SOMME(A1:A4)
Le point virgule (;)
Indique de prendre la somme de la valeur d’un point A et de la valeur à un point B. C’est une union.
Pour additionner deux cellules, j’aurais pu utiliser la formule classique :
=A1+A5
Mais je peux tout aussi bien utiliser la fonction SOMME avec un point virgule entre chaque valeur à additionner.
=SOMME(A1;A5)
L'espace ( )
Indique de faire la somme des cellules conjointes entre une plage A et une plage B. C’est une intersection.
J’ai deux plages :
- une jaune de A1 à C5
- une bleue de C1 à E5.
Je veux calculer les valeurs des cellules qui sont dans les deux zones à la fois (vertes).
Grâce à la formule, je n’ai qu’à saisir mes 2 plages séparées d’un espace.
=SOMME(A1:C5 C1:E5)
Calculer une somme des résultats de RECHERCHEX
1er exemple : Somme sur une période donnée
Prenons un exemple concret : J’ai un listing des livraisons de céréales et je veux calculer le montant dû sur une période donnée.
Dans mon exemple, je veux calculer mon chiffre d’affaires entre le 05/09/2021 et le 12/09/2021.
Pour cela, je dois créer deux fonctions RECHERCHEX : 1 pour la date de début et 1 pour la date de fin.
J’obtiens :
RECHERCHEX(I2;Tabl_vente[DateLivraison];Tabl_vente[Montant])
RECHERCHEX(J2;Tabl_vente[DateLivraison];Tabl_vente[Montant])
Maintenant que j’ai mon point de départ et mon point d’arrivée, je peux faire la somme entre ces 2 points. Comme c’est une plage, je veux utiliser les 2 points.
=SOMME(RECHERCHEX(I2;Tabl_vente[DateLivraison];Tabl_vente[Montant]):RECHERCHEX(J2;Tabl_vente[DateLivraison];Tabl_vente[Montant]))
On obtient 26 000 € ce qui correspond bien aux 10 000 + 9 000 + 7 000 entre le 05/09/2021 et le 12/09/2021.
Imaginons maintenant que je veux la période du 01/09/2021 au 30/09/2021.
J’ai un message #N/A. C’est normal puisqu’il n’existe aucune livraison le 01/09.
Pour contourner ce problème, on va modifier l’argument mode_correspondance des 2 fonctions RECHERCHEX :
- pour le point de départ, il faut que je prenne l’élément suivant la date mise en cellule I2
- pour la date de fin, il faut prendre l’élément précédent de notre tableau (qu’il soit antérieur à notre date en cellule J2).
=SOMME(RECHERCHEX(I2;Tabl_vente[DateLivraison];Tabl_vente[Montant];;1):RECHERCHEX(J2;Tabl_vente[DateLivraison];Tabl_vente[Montant];;-1))
On obtient bien la somme des livraisons entre le 1/09 et 30/09 (compris) soit 67 400 €.
2e exemple : Somme de plusieurs personnes
J’ai un listing des kilomètres parcourus par des salariés.
Je veux connaître la distance parcourue de Bernard et Megane.
Comme pour le premier exemple, je crée tout d’abord mes fonctions RECHERCHEX.
=RECHERCHEX($A$10;Tabl_kms[Associé];Tabl_kms[kms])
=RECHERCHEX($A$11;Tabl_kms[Associé];Tabl_kms[kms])
Mes valeurs en B10 et B11 correspondent bien à mon tableau. Maintenant je veux en faire la somme. Je mets un 2 points :
=SOMME(RECHERCHEX($A$10;Tabl_kms[Associé];Tabl_kms[kms]);RECHERCHEX($A$11;Tabl_kms[Associé];Tabl_kms[kms]))
On obtient bien la somme des deux collaborateurs.
Toutefois, cette méthode ne marche qu’à la condition qu’il y a des valeurs uniques. Dans notre exemple, il y a une ligne pour Bernard et une pour Mégane.
Maintenant, vous voyez où je veux en venir ? J’ai une solution de contournement.
3e exemple : Somme des déplacements kilométriques
J’ai un listing des déplacements des salariés cette fois-ci.
Je veux connaître la distance parcourue de Bernard et Sylvie.
Comme vous le voyez, chaque collaborateur a fois plusieurs déplacements.
Nous ne pouvons utiliser en l’état cette base afin de faire une SOMME des RECHERCHEX.
On va rajouter une colonne qui faire faire le cumul des déplacements. Pour cela on va utiliser la fonction SOMME.SI.ENS.
En cellule E2, je rentre la formule :
=SOMME.SI.ENS(E2;A2;A2)
Comme c’est la première ligne du tableau, la formule est simplifiée.
En cellule E3, je rentre cette fois-ci :
=SOMME.SI.ENS(E$2:E3;A$2:A3;A3)
Je recopie cette formule jusqu’au bout du tableau. Ainsi, pour chaque ligne, Excel va cumuler tous les déplacements de la personne du début du tableau jusqu’à la ligne où se trouve la formule.
Maintenant qu’on a le total des déplacements (dernière ligne de chaque collaborateur), je vais pouvoir en faire la somme.
J’utilise mes fonctions RECHERCHEX et je vais mettre -1 dans l’argument mode_recherche pour récupérer la dernière ligne de saisie pour le collaborateur.
Cela donne :
=RECHERCHEX(A11;Tabl_dplc[Associé];Tabl_dplc[Cumul];;;-1)
=RECHERCHEX(A10;Tabl_dplc[Associé];Tabl_dplc[Cumul];;;-1)
Avec cela, j’ai bien récupéré le dernier cumul des kms parcourus pour Sylvie et Bernard.
Il me reste à faire la somme :
=SOMME(RECHERCHEX(A10;Tabl_dplc[Associé];Tabl_dplc[Cumul];;;-1):RECHERCHEX(A11;Tabl_dplc[Associé];Tabl_dplc[Cumul];;;-1))
Et voici notre résultat final !
Ça nous a coûté qu’une colonne supplémentaire. Sympa, vous ne trouvez pas ?
