Somme des colonnes et des lignes correspondantes

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


Sujets que vous pourriez aussi aimer :


Consolidations conditionnelles

Avec cette nouvelle astuce Excel, nous allons apprendre à réaliser des consolidations conditionnelles particulièrement spécifiques et puissantes. Et pourtant nous le verrons, la syntaxe de la formule finale n'en demeurera pas moins évidente.



La capture ci-dessus illustre la solution finalisée à atteindre. L'objectif est de consolider les ventes effectuées par gammes d'articles. Ces gammes regroupent les codes articles commençant par la même lettre. Et ces consolidations doivent intervenir pour un jour précis de la semaine. C'est la raison pour laquelle la gamme tout comme le jour se définissent par le biais de deux listes déroulantes. C'est ainsi que les contraintes du calcul dynamique sont posées.

Classeur source
Pour élaborer ces travaux, nous avons premièrement besoin de récupérer ce classeur, dans sa version quelque peu défrichée.

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

Les jours de semaine au cours desquels les ventes ont été enregistrées sont énumérés en première colonne du tableau. Il s'agit de la colonne B. Les codes articles sont quant à eux listés en première ligne de ce tableau. Il s'agit de la cinquième ligne de la feuille. En cellule M6, l'utilisateur peut définir une lettre par le biais d'une liste déroulante. Chacune de ces lettres est la première de l'une des catégories pour lesquelles il s'agit de consolider les ventes. Toujours par le biais d'une liste déroulante, l'utilisateur doit définir un jour de semaine en cellule M9. Sur ces deux critères, la consolidation des chiffres doit être calculée juste en-dessous, plus précisément en cellule M12.

Somme sur des lignes et colonnes concordantes
C'est bien sûr un raisonnement matriciel qui permet de réaliser cette consolidation. L'astuce consiste à exploiter la fonction Excel SommeProd en la faisant agir sur des matrices conditionnelles. Une première condition doit être émise sur la matrice des jours, selon le choix de l'utilisateur. Une deuxième condition doit être émise sur la matrice des codes articles, selon la lettre choisie par l'utilisateur. Ces matrices conditionnelles doivent être recoupées pour ne conserver que les ventes du jour de semaine et de la gamme correspondante. Le résultat de ce recoupement doit être multiplié par la matrice des chiffres, soit toutes les données numériques situées au centre du tableau. Toutes celles qui ne concordent pas avec les critères recoupés par les deux premières matrices seront ignorées. Toutes les autres seront cumulées et donc consolidées.

Remarque importante : Les plages des matrices sont respectivement nommées Jours, Cat et Chiffres.

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

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

• Dans la zone Matrice1, ouvrir une parenthèse pour la première matrice conditionnelle,
• Désigner la matrice des jours de semaine par son nom, soit : Jours,
• Taper le symbole égal (=) pour annoncer le premier critère à honorer,
• Sélectionner le jour choisi par l'utilisateur en cliquant sur sa cellule M9,
• Puis, fermer la parenthèse de cette première matrice conditionnelle,
• Taper le symbole de l'étoile (*) pour annoncer la matrice conditionnelle à recouper,
• Ouvrir une nouvelle parenthèse pour accueillir cette dernière,
• Inscrire la fonction prélevant les premiers caractères, suivie d'une parenthèse, soit : Gauche(,

En effet, l'utilisateur mentionne une lettre. Cette première lettre commune aux codes articles désigne une gamme. Ce sont tous les articles de cette gamme qui sont concernés. Nous devons donc établir la correspondance par rapport à cette première lettre à extraire de chaque code article.

• Désigner la matrice des codes articles par son nom, soit : Cat,
• Taper un point-virgule (;) pour passer dans l'argument de la longueur pour la fonction Gauche,
• Puis, taper le chiffre 1,

De cette manière, en partant du début de la chaîne (Gauche), nous demandons à n'isoler que la première lettre.

• Fermer la parenthèse de la fonction Gauche,
• Taper le symbole égal (=) pour annoncer le deuxième critère à respecter,
• Désigner la lettre choisie par l'utilisateur en cliquant sur sa cellule M6,
• Fermer la parenthèse de cette deuxième matrice conditionnelle,

Aussitôt et comme vous pouvez le voir, des indicateurs de positions concordantes viennent se greffer sous forme de matrice, en regard de la zone Matrice1. Pour la gamme A et pour le Mardi, la première correspondance est en effet à observer en dixième position. Et si vous consultez le résultat de synthèse en bas de la boîte de dialogue, vous constatez que 12 concordances sont repérées. Ce résultat semble parfaitement cohérent. Il existe en effet trois codes articles dans cette gamme et quatre Mardi énoncés dans ce tableau.

Mais ce n'est pas fini. Sur ces concordances repérées, nous devons cumuler tous les chiffres du tableau. Pour cela, il suffit de multiplier ce premier résultat par la matrice des chiffres.

• Taper le symbole de l'étoile pour annoncer la dernière matrice conditionnelle à croiser,
• Désigner tous les chiffres du tableau par le nom de la matrice, soit : Chiffres,

Les indicateurs numériques s'actualisent aussitôt pour désigner tous les chiffres à considérer selon ces critères recoupés. Et à l'issue, ils sont effectivement additionnés par la fonction SommeProd comme le révèle le bilan en bas de la boîte de dialogue.

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

De retour sur la feuille, le résultat est livré et il est fidèle à celui annoncé en avant-première par l'assistant.



Bien sûr, si vous modifiez la lettre de la gamme et/ou le jour de la semaine, la consolidation s'actualise immédiatement.

Surligner les cellules consolidées
Il est bien sûr très simple de vérifier le résultat fourni par le calcul matriciel. Il suffit de regrouper les chiffres concernés dans une même sélection grâce à la touche Ctrl.



Dès lors la barre d'état, en bas de la fenêtre Excel fournit la somme de ces valeurs. Et comme vous pouvez le voir, elle corrobore parfaitement le bilan livré par la formule matricielle. Mais il est encore plus pertinent de faire ressortir dynamiquement ces cases en couleur. La correspondance deviendra ainsi évidente. Pour cela, nous devons bâtir une règle de mise en forme conditionnelle. Cette règle doit vérifier les correspondances sur la gamme et sur le jour de semaine.

• Sélectionner tous les chiffres du tableau, soit la plage de cellules C6:K33,
• 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,
• Dès lors, taper le symbole égal (=) pour initier la syntaxe de la règle de mise en forme,
• Inscrire la fonction pour recouper les conditions, suivie d'une parenthèse, soit : Et(,
• Inscrire la fonction pour prélever les premiers caractères avec une parenthèse, soit : Gauche(,
• Désigner le premier code article en cliquant sur sa cellule C5, ce qui donne : $C$5,

En effet, nous ne sommes plus dans un raisonnement matriciel. L'analyse d'une mise en forme conditionnelle est chronologique par rapport à la sélection. C'est la raison pour laquelle nous portons l'étude sur le premier code article. Quand la règle opèrera, ils seront tous passés en revue.

• Enfoncer la touche F4 du clavier pour libérer la colonne de la cellule, soit : C$5,

En effet, l'analyse pour ce premier critère doit toujours porter sur cette ligne 5. Elle ne doit donc pas bouger. En revanche, chaque code article doit être étudié. Donc la colonne doit être libérée.

• Taper un point-virgule suivi du chiffre 1 : ;1, pour ne prélever que la première lettre,
• Fermer la parenthèse de la fonction Gauche,
• Taper le symbole égal (=) pour annoncer le premier critère à honorer,
• Puis, cliquer sur la lettre choisie pour la gamme en cliquant sur sa cellule M6, soit : $M$6,

Nous cherchons ainsi à repérer chaque colonne pour laquelle le code article en cours d'analyse appartient bien à la gamme désignée par l'utilisateur.

• Taper un point-virgule (;) pour continuer l'énumération des critères de la fonction Et,

Cette seconde condition doit observer la concordance sur le jour de semaine.

• Désigner le premier jour de la semaine en cliquant sur sa cellule B6, soit : $B$6,
• Enfoncer deux fois la touche F4 du clavier pour la libérer en ligne, ce qui donne : $B6,

Contrairement au cas précédent, la correspondance doit toujours être observée dans cette colonne B. Elle ne doit donc pas bouger. Mais tous les jours de la semaine doivent être analysés. Donc la mise en forme conditionnelle doit pouvoir déplacer son analyse en ligne.

• Taper le symbole égal (=) pour annoncer le critère à respecter,
• Désigner le jour choisi par l'utilisateur en cliquant sur sa cellule M9, ce qui donne : $M$9,

Ce critère est dans une cellule de référence. Elle ne doit pas bouger.

• Fermer la parenthèse de la fonction Et,

Lorsque la correspondance est établie sur la gamme et sur le jour de semaine, les chiffres du tableau doivent être mis en lumière. Pour cela, nous devons définir des attributs de format.

• En bas de la boîte de dialogue, cliquer sur le bouton Format,
• Dans la boîte de dialogue qui suit, activer l'onglet Police,
• Avec la seconde liste déroulante, choisir un orange assez vif pour la couleur du texte,
• Valider ces attributs de format en cliquant sur le bouton Ok,
• De retour sur la première boîte de dialogue, valider la création de la règle par le bouton Ok,

De retour sur la feuille, vous voyez surgir les cellules impliquées dans la formule matricielle. Si vous modifiez les choix sur la gamme et sur le jour, le calcul s'actualise aussitôt et les couleurs se déplacent instantanément. Il devient donc évident d'interpréter les résultats mais aussi de les confirmer.