Sommes conditionnelles sur plusieurs lignes et colonnes

Pour partager cette vidéo sur les réseaux sociaux ou sur un site, voici son url :


Sujets que vous pourriez aussi aimer :


Sommes conditionnelles - Plusieurs lignes et colonnes

Nous savons déjà réaliser des sommes conditionnelles avec la fonction Somme.Si et des additions conditionnelles multicritères avec la fonction Somme.Si.Ens. Mais l'une et l'autre permettent d'effectuer ces opérations sur une seule rangée, soit une seule colonne. Or ici, il est question de livrer un bilan multiple et dynamique à l'aide d'une formule unique et évolutive, capable de réaliser les additions contraintes, à la fois sur plusieurs lignes et plusieurs colonnes.



Sur l'exemple illustré par la capture, un tableau relate les ventes réalisées par gammes de produits sur quatre différents sites de l'entreprise. Ces gammes peuvent être répétées à plusieurs reprises en raison de la consolidation des données au coup par coup. Sur la droite de ce tableau, l'utilisateur désigne l'une de ces gammes à l'aide d'une liste déroulante. Aussitôt, la somme des ventes réalisées pour cette gamme sur les quatre sites, donc en ligne comme en colonne, est livrée juste en-dessous avec deux formules. La seconde est un dérivé de la première pour comprendre que cette syntaxe peut s'adapter à l'augmentation impromptue du nombre des sites, donc du nombre des colonnes. Et comme vous le voyez, une mise en forme conditionnelle vient repérer en vert toutes les données additionnées, pour renforcer l'efficacité de la synthèse.

Classeur Excel à télécharger
Pour la découverte de cette nouvelle astuce Excel, nous suggérons d'appuyer l'étude sur un classeur offrant ce tableau des chiffres à analyser.

• Télécharger le classeur sommes-conditions-colonnes.xlsx en cliquant sur ce lien,
• Double cliquer sur le fichier réceptionné pour l'ouvrir dans Excel,
• Puis, cliquer sur le bouton Activer la modification du bandeau de sécurité,

Nous retrouvons bien le tableau des ventes par gamme et par site. Et si vous choisissez l'une de ces gammes à l'aide de la liste déroulante en cellule H4, vous constatez que les couleurs se déplacent pour mettre en évidence tous les résultats à additionner sur les lignes et les colonnes concernées.

Si la syntaxe de cette règle vous intéresse :

• Cliquer sur la case de l'un des résultats du tableau, par exemple en cellule C4,
• Dans la section Styles du ruban Accueil, cliquer sur le bouton Mise en forme conditionnelle,
• En bas des propositions, choisir l'option Gérer les règles,
• Dans la boîte de dialogue qui suit, cliquer sur le bouton Modifier la règle,

C'est ainsi que la syntaxe de la règle apparaît.



Notez l'emploi judicieux des références absolues dans ce raisonnement chronologique de la règle, en partant de la première gamme figée dans sa colonne pour progresser en ligne et être inévitablement comparée à la référence choisie en H4, complètement figée ($H$4), pour ne pas évoluer avec l'analyse. C'est ainsi que les lignes complètes et concernées sont mises en valeur dans la couleur définie.

• Cliquer sur le bouton Ok de la boîte de dialogue puis sur le bouton Ok de celle qui suit.

Additions multiples
Pour que la somme puisse être réalisée sur les lignes et les colonnes concernées par le choix de l'utilisateur, l'astuce consiste à imbriquer la fonction Somme.Si dans la fonction SommeProd, mais ce n'est pas tout. Vous le savez, la fonction SommeProd est une fonction matricielle. Elle multiplie les lignes respectives des matrices confrontées pour additionner leurs résultats à l'issue. Donc en guise de plage pour la somme de la fonction Somme.Si, nous devons être en mesure de parcourir l'ensemble des colonnes de ce tableau dans ce raisonnement matriciel. C'est la raison pour laquelle nous allons faire varier cette colonne grâce à la fonction Excel Decaler et une matrice virtuelle.

• Cliquer sur la case de la somme à trouver, soit sur la cellule H7,
• Taper le symbole égal (=) pour initier la syntaxe de la formule matricielle,
• Inscrire le nom de la fonction matricielle suivie d'une parenthèse, soit : SommeProd(,
• Inscrire la fonction d'addition conditionnelle suivie d'une parenthèse, soit : Somme.si(,
• Désigner la plage dans laquelle le critère doit être vérifié, soit : B4:B14,
• Taper un point-virgule (;) pour passer dans l'argument du critère à vérifier,
• Désigner le choix utilisateur en cliquant sur la cellule H4 de la liste déroulante,
• Taper un point-virgule (;) pour atteindre l'argument de la plage correspondante de la somme,

C'est précisément là que l'astuce réside. Il ne s'agit pas de considérer une seule colonne mais toutes celles qui honorent le critère. Et les calculs conventionnels ne sont pas capables de résoudre le cas. En revanche, un raisonnement matriciel est capable de confronter ces colonnes au critère tour à tour, comme le ferait un traitement récursif en VBA Excel. Et pour passer ces colonnes en revue les unes après les autres, nous allons exploiter la fonction Excel Decaler avec une matrice virtuelle de chiffres dans l'argument du décalage en colonne.

• Inscrire la fonction de déplacement suivie d'une parenthèse, soit : Decaler(,
• Désigner la première plage de la somme en la sélectionnant, soit : C4:C14,
• Taper deux points-virgules successifs pour ignorer l'argument du décalage en ligne, soit : ;;,
• Dès lors, construire la matrice virtuelle suivante : {0;1;2;3},

De cette manière et dans ce raisonnement matriciel, nous indiquons de recouper le critère de la fonction somme.si, dans un premier temps pour sommer sur la plage d'origine (décalage zéro en colonne) puis sur la suivante (1) et les deux restantes (2 et 3).

• Fermer la parenthèse de la fonction Décaler,
• Fermer la parenthèse de la fonction Somme.si,
• Puis, fermer la parenthèse de la fonction SommeProd,
• Enfin, valider la formule avec la touche Entrée du clavier,

Un montant consolidé apparaît. Il concerne la gamme sélectionnée. Et vous pouvez facilement vérifier la cohérence de cette somme conditionnelle sur plusieurs colonnes en sélectionnant toutes les cellules de couleur et en consultant l'information de synthèse fournie par Excel dans la barre d'état, an bas à droite. Elle recoupe parfaitement le résultat de la formule matricielle que nous avons bâtie.

Et bien sûr, si vous changez de gamme avec la liste déroulante, vous obtenez instantanément la synthèse conditionnelle multiligne et multicolonne.

La syntaxe complète de la fonction conditionnelle d'addition que nous avons construite est la suivante :

=SOMMEPROD(SOMME.SI(B4:B14; H4; DECALER(C4:C14; ; {0;1;2;3})))

Pour une formule plus adaptative, nous pourrions ajuster le dernier argument de la fonction décaler :

=SOMMEPROD(SOMME.SI(B4:B14; H4; DECALER(C4:C14; ; LIGNE(INDIRECT("$1:" & NBVAL(11:11)))-2)))

Dans la fonction ligne, nous exploitons la fonction Indirect pour interpréter une matrice virtuelle reconstruite par calcul. Nous partons de l'indice 1 jusqu'à l'indice correspondant au nombre de colonnes du tableau. Celui-ci peut évoluer en effet. Nous nous basons sur la ligne 11 qui ne propose aucune cellule de synthèse sur la droite. Nous retranchons deux unités, premièrement pour partir d'un décalage de zéro unité pour la première plage d'addition et d'une autre pour déduire la colonne B de titre du décompte.