Consolider les points par formule matricielle Excel

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


Sujets que vous pourriez aussi aimer :


Additions conditionnelles sur des tableaux entiers

Cette nouvelle astuce Excel montre comment consolider des valeurs sur des critères additionnés.



Dans l'exemple illustré par la capture, nous travaillons sur un tableau présentant les résultats obtenus par différentes équipes au cours du premier trimestre. Ces équipes sont mentionnées en première colonne du tableau. Sur la droite, l'utilisateur peut choisir deux de ces équipes à l'aide de deux listes déroulantes respectives. Juste en dessous, nous devons être capables de livrer le cumul des points engrangés pour ces deux équipes, donc pour ces deux critères superposés.

Classeur source
Pour développer cette astuce, nous proposons d'appuyer nos travaux sur un classeur offrant déjà ces données et cette structure.

• Télécharger le classeur somme-criteres-multiples.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é,

L'utilisateur doit donc choisir deux équipes en cellules respectives G6 et G9. A ce titre, vous notez qu'elles sont toutes deux dotées d'une liste déroulante de choix.

Consolider les résultats sur des critères
Etant donné que la plage des critères et la plage de la somme ne sont pas homogènes, nous ne pouvons avoir recours aux fonctions conditionnelles d'addition usuelles (Somme.Si et Somme.Si.Ens). C'est donc une fois encore une technique matricielle, capable de raisonner sur l'ensemble des matrices engagées, qui va nous sortir de ce faux pas. Et pour cela, nous allons exploiter la fonction SommeProd. Elle doit accueillir des matrices soumises à condition. Ces matrices additionnées pour certaines et recoupées pour d'autres, livreront des résultats numériques qui seront sommés à l'issue par la fonction SommeProd.

• Sélectionner la cellule du résultat à trouver en cliquant sur G12,
• Taper le symbole égal (=) pour initier la syntaxe de la formule matricielle,
• Inscrire le nom de la fonction matricielle suivi d'une parenthèse, soit : SommeProd(,
• A gauche de la barre de formule, cliquer sur le petit bouton (fx) Insérer une fonction,

Nous appelons ainsi l'assistant Excel pour la fonction SommeProd. Il va nous aider à mieux appréhender la logique en livrant des résultats intermédiaires au fil de la construction du calcul.

• Dans la zone Matrice1, ouvrir deux nouvelles parenthèses,

Nous avons en effet deux matrices conditionnelles à additionner et une troisième à recouper.

• Sélectionner toutes les équipes, soit la plage de cellules B6:B12,
• Taper le symbole égal (=) pour annoncer la première condition à respecter,
• Désigner alors la première équipe choisie en cliquant sur sa cellule G6,
• Fermer la parenthèse de cette première matrice conditionnelle,
• Inscrire l'opérateur d'addition (+) pour annoncer la seconde matrice à ajouter,
• De fait, ouvrir une nouvelle parenthèse pour accueillir cette dernière,
• Sélectionner de nouveau toutes les équipes, soit la plage de cellules B6:B12,
• Taper le symbole égal (=) pour annoncer le second critère à honorer,
• Sélectionner la seconde équipe choisie en cliquant sur sa cellule G9,
• Fermer la parenthèse de cette seconde matrice conditionnelle,
• Puis, fermer la parenthèse de la factorisation,

Aussitôt des indications numériques apparaissent. Sur la droite de la zone Matrice1, une matrice présentée à l'horizontale énumère des chiffres. Les chiffres 1 représentent les positions dans l'énumération verticale des équipes concordantes trouvées. Grâce à l'opérateur plus (+) nous avons pu simuler l'emploi d'une fonction Ou dans un raisonnement matriciel. En effet, les conditions ne peuvent pas être recoupées. Sur une même ligne, il s'agit des résultats d'une équipe et non des deux à la fois. D'ailleurs, en bas de la boîte de dialogue, vous notez le résultat sommé par la fonction SommeProd à ce stade. Elle indique sans équivoque que deux équipes ont bien été trouvées.

Pour ces deux lignes décelées, nous devons maintenant additionner tous les points correspondants. Il suffit simplement de multiplier cette matrice verticale par celle des points. Tous les points des équipes concordantes seront multipliés par 1. Tous les autres seront annulés puisque multipliés par 0. Et comme vous le savez, à l'issue de cette multiplication, la fonction SommeProd cumulera tous les points ayant résisté à ce traitement. Nous obtiendrons bien la consolidation pour les deux équipes désignées par l'utilisateur.

• Taper le symbole de l'étoile (*) pour annoncer la matrice à recouper,
• Sélectionner tous les points soit la plage de cellules C6:E12,

Les informations de l'assistant se mettent aussitôt à jour. Vous notez tout d'abord les emplacements des points conservés par le croisement des matrices. Puis, vous notez le cumul consolidé par la fonction SommeProd en bas de la boîte de dialogue.

• Cliquer sur le bouton Ok de la boîte de dialogue pour valider la création de la formule,

De retour sur la feuille, si vous modifiez les équipes à l'aide des listes déroulantes, vous constatez l'actualisation immédiate du score cumulé en cellule G12. La syntaxe complète de la formule matricielle que nous avons bâtie est la suivante :

=SOMMEPROD(((B6:B12=G6) + (B6:B12=G9))*C6:E12)

Repérer les lignes des points cumulés
Il est certes très simple de valider la cohérence du résultat fourni par la formule matricielle. Grâce à la touche CTRL du clavier, vous pouvez sélectionner ensemble tous les points des deux équipes choisies.



Dès lors, en consultant l'information de synthèse sur la somme fournie dans la barre d'état, en bas de la fenêtre Excel, vous notez qu'elle recoupe parfaitement la sentence livrée par notre formule matricielle. Malgré cette simplicité de vérification, pour une application plus aboutie, il apparaît opportun de faire ressortir ces valeurs dans une couleur différente. Et pour cela, nous devons construire une règle de mise en forme conditionnelle.

• Sélectionner toutes les données numériques du tableau, soit la plage de cellules C6:E12,
• Dans la section Styles du ruban Accueil, cliquer sur le bouton Mise en forme conditionnelle,
• En bas de la liste des propositions, choisir l'option Nouvelle règle,
• Dans la boîte de dialogue qui suit, sélectionner le type : Utiliser une formule pour ...,
• Puis, cliquer dans la zone de saisie du dessous pour l'activer,
• Taper le symbole égal (=) pour initier la syntaxe de la règle de mise en forme conditionnelle,
• Inscrire la fonction non exclusive pour énumérer les critères, avec une parenthèse : Ou(,
• Désigner la première équipe en cliquant sur sa cellule B6, ce qui donne : $B$6,
• Enfoncer deux fois la touche F4 du clavier pour la libérer en ligne, soit : $B6,

Nous le répétons à chaque reprise, l'analyse d'une mise en forme conditionnelle est chronologique. Nous débutons donc l'étude à partir de la première équipe. C'est la raison pour laquelle, nous libérons la ligne de la cellule. Ainsi, toutes les équipes seront tour à tour passées en revue. En revanche, le premier critère à honorer est forcément porté dans une cellule de cette colonne B. C'est la raison pour laquelle nous conservons le dollar devant son indice.

• Taper le symbole égal (=) pour annoncer la première condition à satisfaire,
• Puis, cliquer sur la première équipe choisie en cliquant sur sa cellule G6, ce qui donne : $G$6,

Cette fois, nous la conservons intégralement figée. Malgré la progression de l'analyse, le critère doit toujours être honoré par rapport à cette cellule de référence. Elle ne doit donc pas bouger.

• Taper un point-virgule (;) pour poursuivre l'énumération des conditions,
• Sélectionner de nouveau la première équipe en cliquant sur sa cellule B6,
• Comme précédemment, enfoncer deux fois la touche F4 du clavier, soit : $B6,
• Taper le symbole égal (=) pour annoncer la condition additionnée à suivre,
• Puis, sélectionner la deuxième équipe choisie en cliquant sur sa cellule G9, soit : $G$9,
• Fermer la parenthèse de la fonction Ou pour terminer l'énumération,

Lorsque l'un ou l'autre critère est vérifié, nous devons faire ressortir explicitement l'intégralité de la ligne correspondante.

• Cliquer sur le bouton Format en bas de la boîte de dialogue,
• Dans la boîte de dialogue qui suit, activer l'onglet Police,
• Avec la deuxième liste déroulante, choisir un vert assez vif pour la couleur du texte,
• Valider ces attributs de format avec le bouton Ok,
• De retour sur la première boîte de dialogue, valider la règle de mise en forme par Ok,

Nous sommes de retour sur la feuille Excel. Les points des deux équipes choisies surgissent effectivement dans les attributs que nous avons définis. Bien sûr, si vous changez les équipes par le biais des listes déroulantes, le calcul des points s'actualise et dans le même temps, les couleurs de repérage se déplacent dynamiquement. La syntaxe de la règle de mise en forme conditionnelle que nous avons construite est la suivante :

=OU($B6=$G$6; $B6=$G$9)