Dénombrement matriciel Excel sur critères additionnés

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


Sujets que vous pourriez aussi aimer :


Synthèses matricielles combinées

Nous avons précédemment appris à livrer des statistiques fines, grâce aux calculs matriciels, sur la base de conditions recoupées. Mais pour des analyses souples, il est parfois nécessaire d'émettre plusieurs choix, donc de superposer les critères qui ne peuvent être croisés.



Le tableau de la capture ci-dessus illustre l'une des solutions abouties à ce sujet. Un tableau de bord permet de spécifier deux codes produit. Les formules matricielles dressent un bilan comptable de tous les articles répondant à ces catégories de références.

Source et présentation de la problématique
Pour mener à bien cette étude, nous proposons de réceptionner un classeur offrant deux sources de données différentes.

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

Nous débouchons sur un classeur muni de deux feuilles nommées respectivement Syntheses et Catalogue. Le tableau de la feuille Syntheses est une base de données des salariés d'une entreprise. Un tableau de bord sur sa droite permet d'isoler deux services par le biais de listes déroulantes.



Des résultats statistiques sont attendus par rapport à ces choix à combiner.

Le tableau de la feuille Catalogue est une base de données des produits vendus par l'entreprise. Elle aussi offre un tableau de bord à partir duquel il est possible de définir deux catégories de codes article. Des calculs matriciels sur des critères superposés doivent permettre d'offrir un rendu synthétisé des données les concernant.

Synthèse matricielle
Jusqu'alors, nous avons appris que certains calculs complexes pouvaient aussi être entrepris avec des techniques classiques. Dans le cas de figure que nous abordons en revanche, les formules matricielles apportent la solution qu'il serait bien difficile d'aboutir autrement.

• En bas de la fenêtre Excel, cliquer sur l'onglet Syntheses pour revenir sur sa feuille,
• Avec la liste déroulante en I5, choisir le service Comptabilité,
• Avec la liste déroulante en I6, choisir le service Etude,
• En cellule I7, inscrire le salaire plafond de 3000 Euros,

Selon ces choix émis, une synthèse numérique doit être livrée dans les cellules I10 et I11 du dessous. La première attend le nombre d'employés correspondant aux deux services réunis pour un salaire seuil de 3000 Euros à ne pas dépasser. Selon ces mêmes contraintes, la deuxième cellule doit recevoir la somme des salaires correspondant à ces employés regroupés.

Contrairement aux démonstrations de la formation précédente, les conditions à poser sur le service doivent être empilées et non recoupées. Un salarié travaille dans un seul service. Cette information à scruter dans le champ correspondant de la base de données, ne peut être les deux à la fois. Elle peut être soit l'une, soit l'autre. Mais c'est la synthèse cumulée des deux que nous souhaitons délivrer. Dans le raisonnement matriciel, nous ne devons plus exprimer le recoupement semblable à la fonction Et mais la superposition semblable à la fonction Ou. Pour une meilleure compréhension, nous proposons d'exploiter l'assistant fonction d'Excel.

• Sélectionner la cellule du nombre à calculer, soit I10,
• Taper le symbole égal (=) pour initier le calcul,
• Saisir la fonction de somme matricielle suivie d'une parenthèse, soit : SommeProd(,
• A gauche de la barre de formule, cliquer sur le bouton Insérer une fonction,

La boîte de dialogue de l'assistant fonction SommeProd apparaît. Comme pour la formation précédente, nous allons seulement implémenter l'argument Matrice1.

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

En effet, nous devons additionner deux matrices conditionnelles pour matérialiser l'opérateur Ou. Ce résultat devra alors être recoupé avec la condition sur la matrice des salaires.

• Sélectionner tous les services, soit la plage de cellules D4:D21,
• Taper le symbole égal (=) pour le critère à vérifier sur cette rangée,
• Sélectionner la première variable en cliquant sur sa cellule I5,

De cette manière, nous cherchons à connaître tous les salariés appartenant au premier service mentionné.

• Fermer la parenthèse de cette première matrice conditionnelle,
• Taper le symbole plus (+) pour matérialiser l'opérateur Ou,

Ainsi, les critères matriciels ne seront pas recoupés comme avec l'étoile (*) mais additionnés.

• Ouvrir une nouvelle parenthèse pour accueillir la deuxième matrice conditionnelle,
• Désigner de nouveau la colonne des services, soit la plage de cellules D4:D21,
• Taper le symbole égal (=) pour la nouvelle condition à respecter,
• Sélectionner la seconde variable en cliquant sur sa cellule I6,

De fait, nous cherchons tous les salariés appartenant au second service indiqué à additionner avec les premiers trouvés.

• Fermer la parenthèse de cette deuxième matrice conditionnelle,
• Puis, fermer la parenthèse englobant les deux matrices conditionnelles ajoutées,

Aussitôt, des indications de repérages apparaissent sous forme de chiffres. Le chiffre 1 traduit la valeur booléenne Oui. Selon sa position, il indique à quel endroit est trouvé l'enregistrement concordant. Et comme vous le constatez, plusieurs correspondent a priori aux deux services réunis.

Notre calcul matriciel n'est pas terminé. Il doit se soustraire à la condition imposée par le salaire seuil et ce, quel que soit le service. Cette dernière condition matricielle doit donc être recoupée et non additionnée.

• A la suite de la syntaxe, taper le symbole de l'étoile (*) pour matérialiser l'opérateur Et,
• Ouvrir une parenthèse pour accueillir cette dernière condition matricielle,
• Sélectionner tous les salaires, soit la plage de cellules F4:F21,
• Taper l'inégalité : <=, pour annoncer la condition à respecter,
• Sélectionner le salaire seuil en cliquant sur sa cellule I7,
• Fermer la parenthèse de cette dernière condition matricielle,

Les indicateurs chiffrés réapparaissent. Ils renseignent sur la position des enregistrements pour lesquels le service est soit le premier mentionné, soit le second et dans le même temps pour lesquels le salaire ne dépasse pas le plafond indiqué.

• Valider la syntaxe de la fonction SommeProd en cliquant sur le bouton Ok,

Le résultat tombe aussitôt. Et si vous choisissez de scruter le tableau à la recherche des correspondances, vous constatez que ce dénombrement est parfaitement correct.



Il est bien entendu dynamique. Pour le noter, il suffit de changer l'une ou l'autre contrainte depuis le tableau de bord.

La syntaxe complète de la formule matricielle que nous avons bâtie est la suivante :

=SOMMEPROD(((D4:D21 = I5)+(D4:D21 = I6))*(F4:F21 < =I7))
Le calcul qui suit est trivial désormais. Il se décline du précédent. Il s'agit de sommer tous les salaires des enregistrements concordants. Nous devons donc recouper une matrice pour la multiplication. La fonction SommeProd se chargera de la consolidation finale pour la somme.

• En cellule I11, adapter la syntaxe du calcul matriciel précédent comme suit :

=SOMMEPROD(((D4:D21 = I5)+(D4:D21 = I6))*(F4:F21 <= I7)*(F4:F21))

Nous multiplions le précédent dénombrement matriciel par l'ensemble des salaires désignés sous forme de matrice. Dès qu'un enregistrement concorde, il vaut 1. Son salaire est donc ajouté aux autres. Le cas échant, il vaut 0 et le salaire est ignoré.

Le résultat est aussitôt livré. Après quelques vérifications d'usage, vous pouvez confirmer sa parfaite véracité. Et bien entendu, en modifiant les contraintes du tableau de bord, par exemple en baissant le plafond du salaire, vous notez l'actualisation instantanée du dénombrement matriciel ainsi que de la somme.



Ces techniques de calculs sont donc définitivement précieuses pour livrer des informations statistiques fines et complexes.

Additionner les conditions
Pour bien comprendre le raisonnement et les calculs entrepris, nous proposons de réaliser la même démarche sur une autre base de données. Cette fois, nous n'allons pas nous appuyer sur la fonction SommeProd. Il s'agit d'impliquer des matrices conditionnelles dans des fonctions classiques. Nous transformerons le tout en formule matricielle pour que les opérations soient effectuées ligne à ligne.

• En bas de la fenêtre Excel, cliquer sur l'onglet Catalogue pour activer sa feuille,

Nous y trouvons donc une base de données des articles de la société. Chacun est identifié par une référence en première colonne. Les sept premiers caractères de ces références désignent une catégorie. Lorsqu'ils sont communs, les articles sont donc du même type.



C'est pourquoi, ces codes de catégories sont proposés sous forme de liste déroulante en cellules K5 et K6. L'opérateur peut donc désigner deux catégories à ajouter pour obtenir les statistiques. La correspondance doit être établie sur ces premiers caractères. Donc la fonction Excel Gauche doit être utilisée : =Gauche(Texte; Nombre_caractères). Elle permet de prélever une partie d'une information sur un nombre de caractères à déterminer en second argument.

• En haut à gauche de la feuille Excel, cliquer sur la flèche de la zone Nom,

Comme vous pouvez le voir, des noms de plages ont été créés automatiquement en fonction des titres de colonne. Nous exploiterons le champ produit_ref pour simplifier la syntaxe et la construction de la formule matricielle.

Pour réaliser le dénombrement des articles correspondant aux deux catégories, nous devons additionner les critères matriciels. Donc, nous devons englober ces matrices conditionnelles dans la fonction Somme.

• Avec la liste déroulante en cellule K5, choisir le code catégorie 3062PCN,
• Avec la liste déroulante en cellule K6, choisir le code catégorie 3062VTN,
• Conserver le stock minimum réglé à zéro (0) en cellule K7,
• Sélectionner la cellule K10 et taper le symbole égal (=) pour initier le calcul,
• Saisir la fonction pour l'addition suivie d'une parenthèse, soit : Somme(,
• Ouvrir deux nouvelles parenthèses,

En effet, nous devons englober l'addition des matrices conditionnelles pour pouvoir ensuite multiplier leurs résultats avec la contrainte imposée sur la matrice des stocks.

• Saisir la fonction pour collecter un bout de chaîne suivie d'une parenthèse, soit : Gauche(,
• Désigner alors la matrice des références par son nom, soit : produit_ref,
• Taper un point-virgule (;) pour passer dans l'argument de la longueur à prélever,
• Saisir le chiffre 7 pour indiquer une longueur de sept caractères,
• Fermer la parenthèse de la fonction Gauche,
• Taper le symbole égal (=) pour annoncer le premier critère matriciel,
• Sélectionner la première référence du tableau de bord, soit la cellule K5,
• Fermer la parenthèse de cette première matrice conditionnelle,
• Taper le symbole plus (+) pour permettre l'empilement des conditions,
• Ouvrir une nouvelle parenthèse pour accueillir la deuxième matrice conditionnelle,
• Saisir la fonction pour prélever un bout d'information suivie d'une parenthèse, soit : Gauche(,
• Désigner de nouveau la colonne des références par son nom, soit : produit_ref,
• Taper un point-virgule (;) pour passer dans le paramètre de la longueur à prélever,
• Comme précédemment, saisir le chiffre 7,
• Fermer la parenthèse de la fonction Gauche,
• Taper le symbole égal (=) pour la deuxième égalité à honorer,
• Sélectionner la seconde référence du tableau de bord, soit la cellule K6,
• Fermer la parenthèse de cette deuxième matrice conditionnelle,
• Fermer la parenthèse englobant ces deux conditions matricielles additionnées,
• Taper le symbole de l'étoile (*) pour engager le recoupement conditionnel,
• Ouvrir une parenthèse pour accueillir la dernière matrice conditionnelle,
• Désigner tous les stocks par le nom de la plage, soit : produit_stock,
• Inscrire le symbole supérieur suivi du symbole égal, soit : >=, pour la condition à construire,
• Sélectionner la contrainte sur les stocks dans le tableau de bord, soit la cellule K7,
• Fermer la parenthèse de cette dernière matrice conditionnelle,
• Fermer la parenthèse de la fonction Somme,
• Puis, valider le calcul par le raccourci clavier CTRL + MAJ + Entrée,

Nous le transformons ainsi en formule matricielle, comme en atteste l'apparition des accolades dans la barre de formule :

{ =SOMME(((GAUCHE(produit_ref; 7) = K5) + (GAUCHE(produit_ref; 7) = K6))*(produit_stock >= K7))}

La touche Maj est aussi connue sous sa désignation anglaise Shift. Quoiqu'il en soit, nous obtenons le dénombrement matriciel escompté. Une fois encore, nous pouvez réaliser les vérifications d'usage dans la base de données. Pour en avoir le coeur net, vous pouvez modifier l'une des contraintes du tableau de bord, comme le stock minimum.



Le dénombrement conditionnel s'actualise aussitôt. Tous les enregistrements ne satisfont plus aux conditions ajoutées et recoupées. Sur la base de fonctions usuelles, nous avons bâti un raisonnement que nous avons transformé en formule matricielle grâce à ce fameux raccourci clavier. Les critères sont étudiés sur les lignes respectives des matrices mises en regard.

Comme précédemment, le résultat suivant intitulé Somme valeurs se déduit naturellement du précédent calcul. Il consiste à multiplier les produits concordants par leur stock afin de connaître la valeur du fond de commerce. Si les précédentes conditions imposées fonctionnent, elles renvoient le chiffre 1. De fait, nous pouvons multiplier les montants qui seront additionnés par la fonction Somme. Le cas échéant, le chiffre 0 les neutralise.

• En cellule K11, adapter la précédente syntaxe matricielle comme suit :

{=SOMME(((GAUCHE(produit_ref; 7) = K5)+(GAUCHE(produit_ref; 7) = K6))*(produit_stock >= K7)*(produit_stock)*(produit_prix))}

• Puis, la valider nécessairement par le raccourci clavier CTRL + MAJ + Entrée,

Le fond de commerce pour les catégories indiquées et le stock minimum spécifié est désormais calculé dynamiquement sur ces multiples conditions.



La somme des valeurs se met parfaitement à jour et elle est à chaque fois tout à fait cohérente.

Repérage dynamique des enregistrements
Pour parfaire la solution, il est opportun de surligner dynamiquement les enregistrements en fonction des trois conditions émises depuis le tableau de bord. Il s'agit de bâtir une règle de mise en forme conditionnelle. La condition sur le stock doit être recoupée avec les critères à additionner sur les catégories de référence. Nous devons donc imbriquer une fonction Excel Ou à l'intérieur d'une fonction Et.

• Sélectionner toutes les données du tableau, soit la plage de cellules B4:H247,
• 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 juste en dessous pour l'activer,
• Dès lors, inscrire la syntaxe suivante pour la règle :

=ET($G4>=$K$7; OU(GAUCHE($B4;7)=$K$5; GAUCHE($B4;7)=$K$6))

Nous exploitons donc la fonction Et pour énumérer les conditions. Chaque cellule du tableau est figée en colonne et libérée en ligne. Ainsi, la comparaison est réalisée dans la bonne rangée et effectuée pour tous les enregistrements. Fort naturellement, les contraintes du tableau de bord demeurent complètement figées. La fonction OU est imbriquée pour permettre l'addition des critères sur les catégories de références. Lorsqu'un enregistrement concorde, il doit surgir dans une mise en forme dynamique explicitement différente.

• En bas de la boîte de dialogue, cliquer sur le bouton Format,
• Dans la boîte de dialogue qui suit, activer l'onglet Remplissage,
• Dans la palette de couleurs, choisir un orange clair,
• Activer alors l'onglet Police de la boîte de dialogue,
• Avec la liste déroulante, choisir un rouge foncé pour le texte,
• Puis, valider ces attributs dynamiques de format par le bouton Ok,

Nous sommes de retour sur la première boîte de dialogue. Elle indique explicitement la mise en valeur qui sera appliquée dynamiquement aux enregistrements correspondant aux catégories indiquées et dont le stock est supérieur à la valeur mentionnée.

• Valider la création de cette règle de mise en forme conditionnelle par le bouton Ok,

En adaptant les contraintes du panneau de contrôle, vous remarquez que ces repérages visuels dynamiques viennent parfaitement corroborer les statistiques matricielles multicritères.