Statistiques matricielles sur des critères recoupés
Accueil > Bureautique > Excel > Excel Avancé > Statistiques matricielles sur des critères recoupés
Pour partager cette vidéo sur les réseaux sociaux ou sur un site, voici son url :
Statistiques matricielles sur critères recoupés
Les formules matricielles sont d'une aide précieuse lorsqu'il s'agit de dresser des synthèses sur des bases de données, à partir de contraintes dynamiques recoupées.
L'exemple illustré par la capture ci-dessus fournit des informations statistiques croisées sur une base de données des salariés. Des listes déroulantes permettent de définir les critères à recouper, notamment sur le service et la qualification. Instantanément, les résultats de synthèse sont délivrés par calculs matriciels.
Source et présentation de la problématique
Pour réaliser cette étude, nous proposons tout d'abord de réceptionner une base de données.
- Télécharger le classeur syntheses-matricielles-et-criteres.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é,
Un petit tableau de bord est présent sur la droite entre les colonnes H et I. Il permet d'actionner plusieurs leviers grâce à des listes déroulantes. Le principe consiste à définir des critères dynamiques croisés. Ainsi, en fonction du service, de la qualification et éventuellement d'un salaire plafond, une synthèse doit être livrée dans les cellules du dessous, entre les lignes 10 et 13.
Cette méthode doit offrir une vue synthétisée pour une bonne connaissance et interprétation du fonctionnement de l'entreprise.
Nous parachèverons la solution en surlignant tous les enregistrements concordants.
Analyse multicritère
A l'occasion d'une formation, nous avions démontré qu'il était tout à fait possible de produire une analyse multicritère, sur la base de techniques classiques de calcul. Et nous proposons de débuter par cette méthode afin de bien comprendre ensuite l'intérêt des formules matricielles.
Il s'agit d'exploiter les fonctions conditionnelles Nb.Si.Ens et Somme.Si.Ens.
= Nb.Si.Ens(Plage1; Critère1; Plage2; Critère2; ...; PlageN; CritèreN)
= Somme.Si.Ens(Plage_Somme; Plage1; Critère1; Plage2; Critère2; ...; PlageN; CritèreN)
Toutes deux raisonnent sur un ensemble de plages de cellules. Sur chacune, un critère doit être recoupé avec les précédentes conditions. Dans notre cas, il s'agit de poser les contraintes sur les colonnes du service et de la qualification, voire même du salaire. Ces contraintes sont régies par les choix de l'utilisateur dans le tableau de bord. La première fonction réalise un dénombrement multicritère. La seconde, en fonction d'une plage mentionnée en premier argument, réalise la somme correspondant aux conditions croisées.
- En cellule I5, choisir le service Comptabilité avec la liste déroulante,
- En cellule I6, choisir la qualification Q1 avec la liste déroulante,
- En cellule I7, saisir le salaire seuil de 3000,
- En cellule I10, taper la formule suivante :
Sur la première plage de cellules, nous recherchons la présence du service mentionné par l'utilisateur. Nous recoupons cette condition avec la recherche de la qualification sur la deuxième plage de cellules désignée. Le troisième critère vient se greffer sur la troisième plage à la recherche des salaires inférieurs au montant indiqué en I7.
Lorsqu'un enregistrement réunit toutes ces conditions, il est comptabilisé. Le résultat de synthèse dévoile que deux salariés sont des comptables de qualification Q1 avec un salaire inférieur à 3000 Euros. En scrutant le tableau à la recherche des comptables, vous constatez qu'ils ne sont que trois en effet. Et l'un d'entre eux est de qualification Q3.
- En cellule I7, remplacer la valeur du salaire seuil par 1400,
Instantanément, le résultat de synthèse multicritère s'actualise. En effet, sur les trois comptables, seuls deux sont de qualification Q1. Et sur les deux restants, seul l'un d'entre eux possède un salaire ne dépassant pas les 1400 Euros.
- En cellule I11, construire la formule suivante :
L'énumération des plages et des critères à y recouper est strictement identique. Mais la fonction change. Elle doit se charger de réaliser la somme des salaires lorsque toutes les conditions sont recoupées pour un même salarié. C'est la raison pour laquelle nous désignons cette plage en premier argument.
Le résultat conduit à un total de 1400 Euros. En effet, seul l'un des trois comptables résiste à ces conditions croisées. Et il s'agit bien de son propre salaire.
- En cellule I7, taper le salaire seuil de 2000 Euros,
La synthèse multicritère s'actualise aussitôt. Deux comptables répondent aux conditions croisées. Et la somme de leurs salaires conduit bien à un résultat de 2900 Euros.
Pour l'instant, nous laissons de côté les deux derniers calculs de synthèse proposés en cellules I12 et I13. Ils consistent à réaliser les mêmes dénombrements et sommes tout en considérant que la qualification peut être définie ou non. Cette condition supplémentaire s'intègre facilement dans un raisonnement matriciel à l'inverse d'un calcul classique. Ces résultats permettront de livrer des statistiques intéressantes pour l'ensemble d'un service, toutes qualifications confondues.
Statistiques matricielles multicritères
Dans la précédente formation, nous avons découvert la logique des calculs matriciels. Et à ce titre, nous avons présenté la fonction Excel SommeProd. Elle réalise l'addition de toutes les valeurs multipliées entre deux matrices. Dans notre cas, ces matrices étaient matérialisées par des tableaux d'une seule colonne. On parle aussi de vecteurs.
Ici, l'idée consiste à exploiter cette fonction SommeProd pour réaliser la multiplication des critères sur des plages de cellules, soit des matrices. Lorsque des conditions sont recoupées, il en résulte le chiffre 1 et le chiffre 0 dans le cas contraire. Ils traduisent des résultats booléens (1 pour Oui et 0 pour Non). La somme des chiffres conduira alors au dénombrement multicritère attendu.
Comme cette construction n'est pas coutumière, nous proposons d'exploiter l'assistant fonction. Il révèlera des indicateurs simplifiant la compréhension au fil de l'implémentation.
- Supprimer les précédents résultats en cellules I10 et I11,
- Puis, sélectionner la cellule I10,
- Taper le symbole égal (=) pour initier le calcul,
- Saisir la fonction matricielle suivie d'une parenthèse, soit : SommeProd(,
- A gauche de la barre de formule, cliquer sur le bouton Insérer une fonction,
De fait, la boîte de dialogue de l'assistant pour la fonction SommeProd apparaît.
- Dans la zone Matrice1, ouvrir la parenthèse pour accueillir la première matrice conditionnelle,
- Sélectionner alors la plage de cellules des services, soit D4:D21,
- Taper le symbole égal (=) pour la condition à satisfaire sur cette matrice,
- Cliquer sur la cellule I5 pour spécifier le service désigné par l'utilisateur,
- Fermer la parenthèse de cette première matrice conditionnelle,
Aussitôt, des indications surgissent sur la droite de la zone Matrice1. Des valeurs booléennes (VRAI) situées aux emplacements des comptables repérés dans la chronologie, viennent confirmer que des enregistrements concordent sur cette première condition pas encore recoupée.
- Taper le symbole de l'étoile (*) pour recouper cette contrainte avec la suivante,
- Ouvrir la parenthèse pour accueillir la nouvelle matrice conditionnelle,
- Désigner toutes les qualifications, soit la plage de cellules E4:E21,
- Taper le symbole (=) pour la deuxième condition à honorer,
- Puis, cliquer sur la cellule de la qualification choisie, soit I6,
- Fermer la parenthèse de cette deuxième condition matricielle,
Instantanément, les indications réapparaissent sur la droite de la zone Matrice1. Mais cette fois, du fait du recoupement traduit par le symbole de l'étoile, les valeurs booléennes multipliées conduisent à des chiffres : 1 pour Vrai et 0 pour Faux. Ils se positionnent aux emplacements concordants repérés. Ce sont ces chiffres cumulés et additionnés par la fonction SommeProd qui vont livrer le dénombrement multicritère attendu.
- Taper de nouveau le symbole de l'étoile pour le dernier recoupement,
- Ouvrir une parenthèse pour accueillir la nouvelle matrice conditionnelle,
- Sélectionner tous les salaires, soit la plage de cellules F4:F21,
- Taper le symbole inférieur suivi du symbole égal, soit : <=, pour l'inégalité à respecter,
- Sélectionner alors le salaire seuil stipulé par l'utilisateur, soit la cellule I7,
- Puis, fermer la parenthèse de cette dernière matrice conditionnelle,
Nous l'avions déjà constaté en effet. Seuls deux comptables de qualification Q1 gagnant moins de 2000 Euros sont recensés dans cette base de données.
- Valider la formule matricielle en cliquant sur le bouton Ok de l'assistant fonction,
- Avec la liste déroulante en I5, choisir le service Fabrication,
Aussitôt, l'information calculée s'actualise. Plusieurs personnes travaillent dans le service Fabrication. Mais une seule d'entre elles répond à ces trois conditions recoupées.
La formule matricielle complète que nous avons construite est donc la suivante :
=SOMMEPROD((D4:D21 = I5)*(E4:E21 = I6)*(F4:F21 <= I7))
Le résultat suivant peut directement se calquer sur ce précédent calcul matriciel. Comme vous le savez, il consiste à sommer tous les salaires des employés répondant aux trois conditions énoncées dynamiquement. Comme cette formule retourne le chiffre 1 pour chaque enregistrement concordant, il suffit de multiplier le tout par la matrice des salaires. De fait, chaque salaire en regard sera multiplié par le chiffre 1 et le tout sera sommé naturellement par la fonction SommeProd.
- Il s'agit de prélever la syntaxe et de l'adapter comme suit en cellule I11 :
En modifiant les contraintes du tableau de bord, vous remarquez que les calculs de dénombrement et de somme conditionnelle s'opèrent parfaitement.
Adaptation du calcul matriciel
Après ces illustrations sur la puissance fournie par les formules matricielles, nous abordons une phase particulièrement intéressante. Elle concerne les deux derniers calculs en cellules respectives I12 et I13. Il s'agit bien d'un dénombrement suivi d'une somme multicritère. Mais le contexte doit s'adapter. Si la qualification n'est pas stipulée par l'opérateur en cellule I6, le calcul doit quand même se faire en considérant l'intégralité du service tout en incluant la contrainte sur le salaire. Si elle est mentionnée, elle doit être intégrée dans le recoupement. Un calcul matriciel permet d'intégrer une analyse conditionnelle au beau milieu de sa syntaxe.
- En I12, adapter la formule matricielle de dénombrement comme suit :
- Puis, la valider nécessairement par le raccourci clavier CTRL + MAJ + Entrée, ce qui donne :
Très simplement, nous remplaçons la deuxième matrice conditionnelle sur la qualification, par une analyse de contexte à l'aide de la fonction Excel Si. Si la qualification n'est pas stipulée (I6=''), nous inscrivons le chiffre 1. Dans le cas contraire, nous engageons de nouveau la matrice conditionnelle sur la qualification. Comme le chiffre 1 n'est pas une matrice, nous le transformons en tant que telle, pour correspondre avec toutes les lignes des autres vecteurs, grâce au raccourci clavier.
Dès lors, chaque condition recoupée pour un enregistrement est multipliée par le chiffre 1. Il en résulte une donnée statistique capable d'ignorer la contrainte sur la qualification, lorsque cette dernière n'est pas mentionnée.
Pour l'instant, le résultat du dénombrement multicritère est identique au précédent.
- En cellule I6, enfoncer la touche Suppr pour éliminer la qualification,
Alors que les deux premières formules matricielles ne produisent plus aucun résultat, notre dernier calcul livre le nombre d'employés du service pour le salaire plafond indiqué. Nous commençons donc à entrevoir la souplesse et la puissance délivrées par ces raisonnements matriciels.
Le principe est identique pour adapter la somme matricielle multicritère au contexte.
- En cellule I13, adapter la syntaxe de la somme multicritère comme suit :
- Puis, valider la formule par le raccourci clavier CTRL + MAJ + Entrée, ce qui donne :
Lorsque la qualification n'est pas demandée, la somme des salaires est effectuée pour l'ensemble du service indiqué. Dans le cas contraire, l'addition est réalisée selon les trois contraintes matricielles.
Repérer dynamiquement les enregistrements
Pour parfaire la solution, nous souhaitons faire réagir dynamiquement les enregistrements concordant avec les conditions émises. Il sera ainsi plus facile de les repérer et ils viendront renforcer les résultats de synthèse. Nous devons donc construire une règle de mise en forme conditionnelle. Pour recouper les multiples conditions, la fonction Excel ET est nécessaire. Mais comme deux contextes se présentent, avec ou sans qualification, il peut paraître judicieux de bâtir deux règles.
- Sélectionner toutes les données du tableau, soit la plage de cellules B4:F21,
- Dans la section Styles du ruban Accueil, cliquer sur le bouton Mise en forme conditionnelle,
- En bas de la liste des propositions, cliquer sur 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,
- Taper alors la syntaxe suivante : =ET($D4=$I$5;$F4<=$I$7),
- Cliquer sur le bouton Format en bas de la boîte de dialogue,
- Dans la boîte de dialogue qui suit, activer l'onglet Remplissage,
- Dans la palette de couleurs, choisir un vert clair,
- Activer alors l'onglet Police de la boîte de dialogue,
- Avec la liste déroulante, choisir le violet pour la couleur du texte,
- Puis, valider ces attributs de format par le bouton Ok,
- Valider ensuite la création de la règle en cliquant de nouveau sur le bouton Ok,
Il ne reste plus qu'à créer la seconde règle, celle incluant toutes les conditions croisées.
- Sélectionner toutes les données du tableau, soit la plage de cellules B4:F21,
- Dans la section Styles du ruban Accueil, cliquer sur le bouton Mise en forme conditionnelle,
- En bas de la liste des propositions, cliquer sur 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,
- Taper alors la syntaxe suivante : =ET($D4=$I$5; $E4=$I$6; $F4<=$I$7),
- Cliquer sur le bouton Format en bas de la boîte de dialogue,
- Dans la boîte de dialogue qui suit, activer l'onglet Remplissage,
- Dans la palette de couleurs, choisir un bleu clair,
- Activer alors l'onglet Police de la boîte de dialogue,
- Avec la liste déroulante, choisir un bleu foncé pour la couleur du texte,
- Puis, valider ces attributs de format par le bouton Ok,
- Valider ensuite la création de la règle en cliquant de nouveau sur le bouton Ok,
Avec quelques ajustements des contraintes depuis le tableau de bord, vous voyez surgir les enregistrements concordants dans des jeux de couleur différents. Nous obtenons ainsi un rendu visuel venant corroborer les résultats de synthèse offerts par les calculs matriciels multicritères.
Sur Facebook
Sur Youtube
Les livres
Contact
Mentions légales
