Excel 2021 : la fonction SOMME.SI.ENS s’enrichit avec les formules matricielles dynamiques

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.

SOMME.SI.ENS matricielle dynamique

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.

SOMME.SI.ENS matricielle dynamique DONNEES

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 :

SOMME.SI.ENS matricielle dynamique CRITERES

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.

SOMME.SI.ENS matricielle dynamique 1

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.

SOMME.SI.ENS matricielle dynamique 2

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

Share Button
EXCEL - SOMME SI ENS Formule Matricielle Dynamique
EXCEL - SOMME SI ENS Formule Matricielle Dynamique
EXCEL-SOMME.SI_.ENS-formule-matricielle-dynamique.xlsx
13 KiB
66 téléchargements
Détails...
The following two tabs change content below.
Après seize années passées en cabinet d’expertise-comptable et de commissariat aux comptes (où j’ai exercé comme expert-comptable et chef de mission audit), j’ai pris le poste de directeur comptable d’un groupe de distribution automobile en novembre 2014. Au cours de ma carrière, j’ai acquis une expérience significative en audit et en exploitation des systèmes d’information (analyse de données, automatisation des tâches, programmation informatique) au service de la production des comptes annuels et consolidés. C’est cette expérience personnelle et ma passion pour l’informatique que je partage sur ce blog. Mon CV / Réalisations personnelles et projets informatiques / Ma collection / Me contacter

Un commentaire

Laisser un commentaire

Votre adresse e-mail ne sera pas publiée. Les champs obligatoires sont indiqués avec *

Ce site utilise Akismet pour réduire les indésirables. En savoir plus sur comment les données de vos commentaires sont utilisées.