Avec la nouvelle version d’Excel, la fonction SOMME.SI.ENS prend un sérieux coup de jeune. En effet, cette fonction déjà fort pratique bénéficie maintenant des capacités de calcul matriciel dynamique au même titre que les fonctions FILTRE, TRIER ou TRANSPOSE déjà présentées sur ce blog.
Les formules matricielles dynamiques permettent à des fonctions d’interagir avec les cellules adjacentes pour y loger le résultat de calculs alors que traditionnellement une formule de calcul ne produit un résultat que dans la cellule où elle est logée. Le nombre de cellules concernées dépend de la taille du résultat ; d’où la notion de formules matricielles dynamiques.
Afin de rendre plus concret les propos qui suivent, ils seront illustrés par un exemple.
Le classeur Excel d’exemple comprenant les données et les formules de calcul est téléchargeable en bas de page. L’objectif est d’effectuer des calculs multicritères à partir de comptes d’une balance générale et de démontrer la puissance des formules matricielles dynamiques par rapport aux formules plus classiques.
Pour rappel, la fonction SOMME.SI.ENS additionnent les montants d’une plage de cellules sous réserve de la réalisation de critères.
Syntaxe :
SOMME.SI.ENS(plage;plagecondition1;condition1;plagecondition2; condition2;…)
Expression des critères :
La condition ne fait traditionnellement référence qu’à une seule valeur (ou cellule).
Par exemple, en cellule B9, la formule de calcul est la suivante :
=SOMME.SI.ENS($E$29:$E$60;$B$29:$B$60;$B5;$C$29:$C$60;$C5;$D$29:$D$60;$D5;$E$29:$E$60;”>=”&$E5)
Cette formule additionne tous les soldes de la balance (colonne E) pour lesquels les critères suivants (situés en ligne 5) sont réalisés :
- CodeEntité = ENTITE1 ;
- CompteNum commence (*) par 6062 ; l’astérisque (*) est un caractère générique ;
- CompteLib peut être n’importe quel compte (d’où *) ;
- Solde >= (supérieur ou égal à) 200 € (cf opérateurs de comparaison).
La formule en cellule B10 effectue un calcul similaire avec les critères listés en ligne 6.
Pour additionner le résultat de ces deux calculs, il n’y a d’autre solution que de passer par :
=SOMME.SI.ENS(…)+SOMME.SI.ENS(…)
Enfin, cela c’était avant Excel 2021 et l’apparition des formules matricielle dynamique…
Depuis Excel 2021, l’expression des critères peut faire référence à une plage cellules (et non plus à une seule cellule). Grâce à cette possibilité, le résultat du calcul renverra plusieurs montants, qui du fait de la propagation des données, sera restitué dans plusieurs cellules.
Par exemple, la formule en B15 reprend la même rédaction et les mêmes critères que celles figurant en B9 et B10, sauf que les conditions ne font plus référence à une seule valeur ($B$5 par exemple) mais à une plage de cellules ($B$5:$B$6…) :
=SOMME.SI.ENS($E$29:$E$60;$B$29:$B$60;$B$5:$B$6;$C$29:$C$60;$C$5:$C$6;$D$29:$D$60;$D$5:$D$6;$E$29:$E$60;”>=”&$E$5:$E$6)
Il s’ensuit que le résultat tient sur deux cellules (B15 et B16) alors qu’une seule formule aura été entrée dans Excel. A noter au passage que bien évidemment les résultats calculés sont les mêmes que ceux obtenus précédemment.
Encore mieux, il est possible d’obtenir la sommation de l’ensemble des calculs conditionnels en une seule formule avec la fonction SOMME associée à la fonction SOMME.SI.ENS :
=SOMME(SOMME.SI.ENS(…))
Exemple (cellule B18) :
=SOMME(SOMME.SI.ENS($E$29:$E$60;$B$29:$B$60;$B$5:$B$6;$C$29:$C$60;$C$5:$C$6;$D$29:$D$60;$D$5:$D$6;$E$29:$E$60;”>=”&$E$5:$E$6))
Un peu plus simple à rédiger en somme…
Pour faire le parallèle avec le langage SQL, ce calcul revient à rédiger la requête suivante :
SELECT Sum(Solde) As SommeDeSolde FROM _BG WHERE (CodeEntité="ENTITE1" And CompteNum LIKE "6062*" And Solde>=200) Or (CodeEntité="ENTITE3" And CompteNum LIKE "6061*" And Solde>=30000);
___
Approfondir le sujet : Formation Analyse de données et automatisation avec Excel et Access (gratuite) / Tout savoir sur Excel / Découvrir la série d’articles Maîtriser Excel
EXCEL-SOMME.SI_.ENS-formule-matricielle-dynamique.xlsx
Derniers articles parBenoît RIVIERE (voir tous)
- Nouveautés de l’interpréteur de formules de calcul (v1.1) - dimanche 3 novembre 2024
- Dématérialisation de la facturation : nouvelles mentions obligatoires - lundi 28 octobre 2024
- Interpréteur de formules de calcul en Python - dimanche 13 octobre 2024
- Les données de la facturation électronique - mercredi 9 octobre 2024
- VBA/SQL vs Power Query : deux solutions complémentaires - mercredi 2 octobre 2024
merci