Analyse décisionnelle multicritère Excel

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


Sujets que vous pourriez aussi aimer :


Analyse décisionnelle multicritères

Dans cette mise en pratique Excel, nous proposons de résoudre une problématique d'entreprise multicritères. Il s'agit d'une société réalisant des Audits, référencés pour chaque mois de l'année, dans un tableau de bord. Des notes sont attribuées à chacun d'entre eux avec un ratio de satisfaction, ramené à la note maximale.



Ce tableau modèle qui doit être complètement dynamique et automatisé, est exploité pour le suivi et la gestion des Audits réalisés chaque année.

Analyse du classeur source
Pour concentrer nos efforts sur la réflexion à bâtir en fonction des objectifs que nous allons énoncer, nous démarrons les travaux à partir d'un classeur Excel existant.

• Télécharger le classeur Excel analyse-decisionnelle-audit.xlsx en cliquant sur son lien,
• L'ouvrir dans Excel et cliquer sur le bouton Activer les modifications si nécessaire,

Trois lignes de saisie d'Audits sont proposées pour chacun des mois de l'année. Il s'agit d'un modèle. Donc les formules que nous bâtirons pour trois lignes seront vraies pour une société réalisant plus de volume et donc, nécessitant plus de lignes de référencement.

Entre les colonnes Q et T de cette feuille Excel figure la zone de critères. Tous les calculs devront s'y référer afin que le modèle soit parfaitement dynamique. Tout d'abord la société considère que les Audits du mois sont un succès lorsque plus de 80% (cf. critères) des Audits ont une note supérieure à 90%. Mais en même temps, pour que la période soit considérée comme validée, le mois doit être terminé et donc la date passée.

Et à l'issue, nous devrons être en mesure de fournir un résultat synthétique multicritères en F5. L'objectif est de considérer que les Audits sur l'ensemble des périodes sont un succès, si plus de 80% des audits réalisés sont satisfaisants pour les mois de l'année passée. Les décideurs n'auront qu'à considérer cette valeur afin d'interpréter la qualité du travail fourni. Si d'aventure la valeur retournée était négative, ce tableau automatique permettrait d'indiquer que certaines données sont à analyser plus en détail afin d'en tirer les conclusions nécessaires.

Pour mettre en oeuvre le critère à recouper sur la date, nous avons besoin d'une petite astuce. En effet, les noms des mois indiqués en entête de chaque petit tableau, sont des informations textuelles. Si bien que dans leur format brut, ils ne peuvent pas être comparés à une date afin de savoir si le mois concerné est passé.



C'est la raison pour laquelle figure un petit tableau, sur la droite de la feuille, référençant pour chaque mois de l'année, sa correspondance en nombre. Ce tableau est situé entre les colonnes T et U pour les lignes 21 à 32. Cette correspondance permettra de réaliser une comparaison avec le mois de la date en cours, afin de déterminer si la période analysée et bien terminée.

Extraire le numéro du mois
Nous proposons d'afficher un résultat intermédiaire, à côté de chaque petit tableau, en colonnes G et O. Nous masquerons ces colonnes à l'issue. Ce résultat consistera à indiquer si le mois désigné en entête du petit tableau est terminé. Il s'agit pour cela de comparer sa valeur numérique, extraite du tableau en colonnes T et U, avec la valeur numérique du mois de la date en cours (aujourdhui).

Comme nous l'avons appris, les fonctions Index Et Equiv imbriquées, sont tout à fait intéressantes pour extraire dynamiquement de l'information à partir de tableaux Excel. Mais lorsque la configuration s'y prête, nous pouvons aussi exploiter la fonction Excel RechercheV. La syntaxe de la fonction RechercheV est la suivante :

=RechercheV(valeur_cherchee ; tableau_de_recherche ; numero_colonne ; Faux)

La valeur cherchée est le nom du mois désigné en entête de chaque petit tableau. Ce mois doit être cherché dans le tableau situé entre les colonnes T et U et les lignes 21 et 32. Lorsqu'il est trouvé, nous souhaitons retourner le nombre qui lui correspond. Ce nombre se situe dans la deuxième colonne de ce tableau, soit 2 pour numero_colonne. Enfin, comme nous souhaitons réaliser une recherche selon une correspondance exacte, nous terminerons par le dernier paramètre booléen Faux. Nous pourrons alors comparer cette valeur extraite avec le nombre du mois pour la date en cours.

• Sélectionner la cellule G12 où doit être réalisée la recherche du premier mois,
• Taper le symbole = pour débuter le calcul,
• Saisir le nom de la fonction conditionnelle suivi d'une parenthèse ouvrante, soit SI(,

La fonction Excel Si permet d'envisager deux cas selon que le critère, à indiquer en premier paramètre, est vérifié ou non. Sa syntaxe est la suivante :

=Si(critere_a_verifier ; action_alors ; action_sinon)

Ici le critère consiste à savoir si la valeur numérique du mois, est inférieure à celle du mois en cours, signifiant que la période définie par le tableau est terminée. Nous devons commencer par extraire cette valeur pour la comparer :

• Saisir le nom de la fonction d'extraction suivi d'une parenthèse ouvrante, soit RechercheV(,
• Sélectionner le premier mois à rechercher, soit la cellule A7,
• Taper un point-virgule pour passer à l'argument du tableau de recherche,
• Sélectionner alors la plage de cellules T21:U32,
• Enfoncer la touche F4 du clavier afin de figer les références de cette plage ,

En effet, comme nous souhaitons répliquer ce calcul pour tous les autres petits tableaux, nous indiquons à Excel que la plage dans laquelle la recherche doit être effectuée, est toujours la même. Sans les dollars ajoutés grâce à la touche F4, les bornes de ce tableau se déplaceraient en même temps que nous déplaçons le calcul.

• Taper un point-virgule pour passer à l'argument de l'indice de colonne de retour,
• Saisir le chiffre 2,
• Puis taper un point-virgule suivi du texte Faux pour demander une comparaison stricte,
• Fermer la parenthèse de la fonction RechercheV,
• Taper le symbole inférieur (<) pour la comparaison de la zone de critère,
• Saisir le nom de la fonction pour extraire le mois d'une date, suivi d'une parenthèse ouvrante, soit Mois(,
• Saisir le nom de la fonction retournant la date du jour dynamique, soit aujourdhui(),
• Fermer la parenthèse de la fonction Mois,
• Puis, taper un point-virgule pour passer dans la branche Alors de la fonction Si,

La syntaxe de la fonction Si à ce stade, est la suivante :

=SI(RECHERCHEV(A7; $T$21:$U$32; 2; FAUX) < MOIS(AUJOURDHUI());

Nous comparons la valeur du mois du tableau à celle du mois de la date du jour, renvoyée par la fonction aujourdhui(). Cette fonction s'écrit sans apostrophes et est accompagnée d'une parenthèse ouvrante et d'une parenthèse fermante. La fonction Mois présentée par la formation des opérations sur les dates et les heures, retourne la valeur numérique du mois, par exemple 5 pour Mai.

• Saisir le texte Ok entre guillemets, soit 'ok',
• Taper un point-virgule pour passer dans la branche Sinon de la fonction Si,
• Taper deux guillemets sans texte pour laisser la cellule vide le cas échéant,
• Fermer la parenthèse de la fonction Si et valider le calcul par Entrée,

Au moment où nous réalisons ce support, nous sommes au mois de Mai. La formule que nous avons saisie retourne donc fort logiquement l'inscription Ok, signifiant que le mois du tableau concerné est passé.

Comme nous avons judicieusement figé les cellules qui ne devaient pas se déplacer dans ce calcul, nous allons pouvoir le répercuter sur l'ensemble des autres cellules concernées.

• Sélectionner la cellule G12 du résultat,
• La copier à l'aide du raccourci clavier CTRL + C ,
• Sélectionner la cellule G19 pour le tableau du mois de Février,
• Coller (CTRL + V) les informations précédemment copiées,

Le calcul s'adapte dans la mesure où la recherche est désormais réalisée sur la cellule du mois de Février, soit A14, car nous ne l'avions pas figée. Mais cette valeur est bel et bien recherchée dans la plage fixe T21:U32.

• De la même manière, répliquer ce calcul sur les cellules G26, G33, G40, G47, O12, O19, O26, O33, O40 et O47.

Tous les indicateurs apparaissent jusqu'au mois d'Avril inclus dans notre cas, confirmant que notre formule d'extraction et de comparaison fonctionne parfaitement.



Dénombrement multicritères
Désormais, pour chacun des mois, il s'agit d'afficher l'information synthétique qui permettra de considérer que la qualité des Audits réalisés est au rendez-vous (Validé) ou non (Non validé). Et pour cela, nous allons exploiter le résultat du calcul précédent. Le mois est considéré comme validé si plusieurs conditions sont remplies :

• Le mois doit être terminé,
• Plus de 80% des Audits réalisés doivent avoir obtenu une note supérieure à 90.

Nous avions déjà exploité la fonction Nb.si.ens() dans la formation Excel sur le dénombrement statistique multi-critères. Cette fonction permet de vérifier ensemble plusieurs critères sur des plages de cellules à désigner. Mais ici, l'information sur la date, se situe dans une cellule isolée. Donc nous pouvons tout aussi bien exploiter la fonction Nb.si() et recouper les critères avec la fonction Excel ET(). La contrainte sur le pourcentage d'Audits satisfaisants fixée à 80% est inscrite en cellule T2. De cette manière, il suffit de faire varier les données du problème pour que les calculs liés, livrent des conclusions tout à fait différentes selon le contexte. Mais le critère sur la note de chaque Audit, fixé à 90 n'est inscrit nulle part. Nous allons pallier ce problème dans le souci de bâtir un modèle complètement automatisé et dynamique.

• Saisir la valeur 90 en cellule U2,

Ainsi, tous nos calculs pourront être bâtis sur des variables.



Nous allons construire le premier calcul multicritères en cellule E12, soit pour le rapport d'audit du mois de Janvier. Il s'agit de savoir si plus de 80% des audits réalisés ont obtenu une note supérieure à 90 pour le mois dont la date est passée. La fonction NB.SI() d'Excel permet de compter le nombre de cellules répondant à un critère (>90) sur une plage. Sa syntaxe est la suivante :

=Nb.si(plage_de_cellules ; critere)

Si la valeur qu'elle retourne est supérieure à 80% des audits réalisés, alors le mois est considéré comme validé. Ce critère doit donc être posé à l'aide de la fonction Excel conditionnelle Si. Et pour la comparaison, c'est la fonction Excel NbVal() qui permet de compter les cellules non vides d'une plage, soit les Audits réalisés.

• Sélectionner la première cellule du calcul, soit E12,
• Taper le symbole = pour initier la formule,
• Saisir le nom de la fonction conditionnelle suivi d'une parenthèse ouvrante, soit SI(,
• Taper le nom permettant d'énumérer les critères suivi d'une parenthèse, soit ET(,
• Saisir la fonction de dénombrement conditionnel suivi d'une parenthèse, soit NB.SI(,
• Sélectionner la plage de cellules sur laquelle le critère de la note doit être vérifié, soit E9:E11,
• Taper un point-virgule pour passer dans l'argument du critère de la fonction Nb.si,
• Taper le symbole supérieur entre guillemets, soit '>' pour l'opérateur de comparaison,
• Saisir le caractère de concaténation (&), pour l'associer à une valeur numérique,
• Sélectionner la contrainte sur la note, soit la cellule U2,
• Enfoncer la touche F4 du clavier pour la figer,
• Fermer la parenthèse de la fonction Nb.si,
• Taper le symbole supérieur (>) pour comparer son résultat aux 80% souhaités,
• Sélectionner la contrainte sur la qualité des Audits en nombre, soit la cellule T2,
• Enfoncer la touche F4 du clavier pour la figer dans le calcul,
• Taper l'étoile de la multiplication (*) pour multiplier ce pourcentage avec le nombre d'audits,
• Saisir la fonction comptant les cellules non vides suivi d'une parenthèse, soit NBVAL(,
• Sélectionner la plage de cellules des notes, soit E9:E11,
• Fermer la parenthèse de la fonction NbVal,
• Taper un point-virgule pour énumérer un nouveau critère dans la fonction ET,
• Sélectionner la cellule du résultat sur la comparaison de dates, soit G12,
• Taper le symbole = pour initier la comparaison du second critère,
• Saisir le texte Ok entre guillemets, soit 'ok',
• Fermer la parenthèse de la fonction Et,
• Taper un point-virgule pour passer dans la branche Alors de la fonction Si,

Si les deux critères sont vérifiés (+ de 80% des audits ont une note supérieure à 90 pour lesquels le mois est passé), alors nous devons inscrire l'information Validé et Non validé le cas échéant. Un texte s'écrit toujours entre guillemets.

• Saisir le texte Validé entre guillemets, soit 'Validé',
• Taper un point-virgule pour passer dans la branche Sinon de la fonction Si,
• Saisir le texte Non validé entre guillemets, soit 'Non Validé',
• Fermer la parenthèse de la fonction Si et valider la formule,

Le résultat livré par notre calcul conditionnel multicritères est l'information Validé. En effet, sur les trois Audits effectués par l'entreprise au mois de Janvier, tous ont obtenu une note supérieure à 90. La syntaxe complète de la formule que nous avons construite est la suivante :

=SI(ET(NB.SI(E9:E11; '>'& $U$2) > $T$2*NBVAL(E9:E11); G12='ok'); 'Validé'; 'Non Validé')

Comme nous avons judicieusement figé les cellules des contraintes, issues du tableau de référence, nous pouvons répercuter ce calcul pour les autres mois, comme nous l'avons fait précédemment, par Copier-Coller. Les plages de critères et la cellule du résultat de la comparaison sur la date n'étant quant à elles pas figées, les analyses se déplaceront bien sur les nouvelles cellules concernées pour les autres tableaux.

• Sélectionner le résultat précédent, soit la cellule E12,
• La copier à l'aide du raccourci clavier CTRL + C,
• Puis la coller (CTRL + V) sur les cellules E19, E26, E33, E40, E47, M12, M19, M26, M33, M40 et M47,

Le mois d'Avril, passé au moment de la conception de ce support, n'est pas validé. En effet, seuls 2 audits sur 3 ont dépassé la note de 90. C'est seulement 66% au lieu des 80% demandés par le premier critère de la fonction Si (> $T$2*NBVAL(E9:E11)). En revanche, tous les Audits réalisés au mois de Mai ont obtenu sans exception une note supérieure à 90. Il s'agit donc d'un résultat de 100% nettement supérieur aux 80% demandés par le critère. Et pourtant le mois est considéré comme Non validé. En effet, au moment de la formule, nous sommes le 29 Mai. Le mois n'étant pas terminé, le deuxième critère de la fonction Si (G12='ok'), énuméré par la fonction Et n'est donc pas vérifié. Un nouvel Audit peut être renseigné avant la fin du mois. Donc ce résultat est tout à fait logique et notre formule multicritère fonctionne à merveille.

En cellule E31, si vous modifiez la note de 88,6 en 90,1 par exemple, le résultat livré pour le mois d'Avril bascule aussitôt. Au lieu de modifier E31, si vous abaissez la contrainte en cellule T2 à 60%, la conclusion du mois d'Avril devient aussitôt favorable. Tout cela prouve que nos résultats d'analyses sont parfaitement dynamiques. L'application que nous sommes en train de concevoir est donc portable afin que toute société puisse adapter les critères de son analyse à ses propres contraintes.



Comme vous l'avez remarqué, des indicateurs visuels dynamiques se sont automatiquement déclenchés grâce à une mise en forme conditionnelle prédéfinie. Ainsi une couleur verte, synonyme de compte rendu positif fait ressortir un mois considéré comme validé. Dans le même temps, une couleur orangée avec une police rouge, synonyme de compte rendu négatif, met en évidence les mois insatisfaisants.



Pour le constater, vous pouvez consulter les règles de mises en valeur dynamiques en vigueur sur des cellules ou plages de cellules.

• Sélectionner par exemple la cellule E12,
• Dans le ruban Accueil, cliquer sur la flèche du bouton Mise en forme conditionnelle,
• En bas de la liste, choisir Gérer les règles,

Comme l'illustre la capture ci-dessus, une boîte de dialogue apparaît. Elle résume toutes les règles de format dynamique actives sur la ou les cellules sélectionnées. Vous pouvez à tout moment les modifier ou les supprimer. Ces couleurs dynamiques, directement liées aux résultats des calculs, facilitent la lecture et l'interprétation des données de tableaux Excel.

• Cliquer sur le bouton Fermer pour masquer cette boîte de dialogue,

Toujours dans l'esprit de bâtir un modèle exploitable et livrable à toute société, nous proposons de masquer deux colonnes. Il s'agit des colonnes G et O dans lesquelles figurent les résultats des calculs intermédiaires sur les extractions et comparaisons de dates. Ces résultats ne sont pas utiles pour l'utilisateur mais essentiels pour les calculs de la feuille. C'est pourquoi, nous proposons de les masquer sans les supprimer.

• Cliquer avec le bouton droit de la souris sur l'étiquette de colonne G en haut de la feuille,
• Dans le menu contextuel, choisir Masquer,
• Réitérer la même opération sur l'étiquette de colonne O,

En revanche, la plage d'extraction de dates se situe sur des lignes et colonnes communes des tableaux de la feuille. A défaut de pouvoir le masquer, nous pouvons lui attribuer une couleur de police blanche et verrouiller ses cellules, ou encore le déplacer par Couper-Coller.

Analyse décisionnelle multicritère
Il s'agit maintenant de livrer le résultat de l'interprétation finale, selon une analyse de plusieurs critères intermédiaires recoupés. En cellule F5, il s'agit d'inscrire un indicateur textuel permettant de confirmer si l'ensemble des Audits réalisés par l'entreprise sont satisfaisants (Validé) ou insatisfaisants (Non validé). Comme l'indique l'inscription de la cellule fusionnée sur sa gauche, le rapport doit juger les résultats comme validés si 90% des Audits passés sont satisfaisants. Il faut donc compter les audits validés sur l'ensemble de l'année. Et il faut ramener ce résultat du décompte au nombre d'audits réellement effectués, donc correspondant aux mois dont la date est passée.

Ce dénombrement multicritère se réalise de nouveau à l'aide de la fonction Nb.Si(). Et ces calculs doivent être intégrés dans une fonction Si, pour une interprétation conditionnelle des résultats. Mais plutôt que de considérer les 90% de la cellule fusionnée, nous prendrons en référence la contrainte du panneau de contrôle en R2, pour l'instant initialisée à 80%. Ainsi nous continuons de bâtir un modèle parfaitement dynamique et automatisé. Il sera alors très simple de modifier cette contrainte, pour la ramener à 90% par exemple, afin que l'analyse décisionnelle multicritère se mette instantanément à jour.

Nous pourrions réaliser cette étude en une seule fois en intégrant le calcul dans la branche du critère d'une fonction Excel Si. Mais nous pourrions aussi le décomposer, comme nous proposent de le faire les cellules M5 et N5 du tableau. Et c'est que nous allons faire. En M5, nous devons comptabiliser les audits validés. Il suffit donc de compter le nombre de fois que l'information Validé est dénombrée sur l'ensemble des tableaux.

• Cliquer dans la cellule M5 pour la sélectionner,
• Taper le symbole = pour commencer la formule,
• Saisir le nom de la fonction de dénombrement conditionnel suivi d'une parenthèse, soit Nb.Si(,
• Sélectionner tous les tableaux, soit la plage de cellules A7:N47,
• Taper un point-virgule pour passer dans l'argument du critère à compter,
• Saisir le texte Validé entre guillemets, soit 'Validé',
• Fermer la parenthèse de la fonction Nb.Si et valider le calcul avec la touche Entrée.

Nous obtenons un résultat de 3 audits validés, concordant avec ce que retourne les informations visuelles dynamiques de ces tableaux.



En N5 nous souhaitons calculer le ratio des audits validés. Il s'agit donc de diviser le nombre d'audits validés, par le nombre d'Audits réellement effectués au cours de l'année, soit pour les mois dont la date est passée. Souvenez-vous, cette information peut se compter dans les colonnes que nous avons récemment masquées. Lorsque le résultat du calcul retourne l'information Ok, cela signifie que le mois doit être intégré dans l'analyse et le décompte. Nous devons donc diviser le résultat du calcul précédent par le nombre de fois où l'information Ok est trouvée, dans la même plage de cellules que précédemment.

• Cliquer sur la cellule N5 pour la sélectionner et taper le symbole = pour initier le calcul,
• Sélectionner la cellule du nombre d'audits validés, soit M5,
• Taper le slash (/) du pavé numérique pour la division,
• Saisir le nom de la fonction de dénombrement suivi d'une parenthèse, soit NB.SI(,
• Sélectionner l'ensemble des tableaux, soit la plage de cellules A7:N47,
• Taper un point-virgule pour passer dans l'argument du critère de la fonction Nb.Si,
• Saisir le texte ok entre guillemets, soit 'ok',
• Fermer la parenthèse de la fonction Nb.Si et valider le calcul par Entrée,

Nous obtenons le résultat surprenant de 0,8, soit 80%, ce qui correspond à 4 audits validés sur 5. Or dans notre cas, pour le 29 Mai, comme l'illustre parfaitement le format dynamique, seuls 3 audits sur 4 sont validés, ce qui correspond à 75%. En réalité le nombre est simplement tronqué à l'affichage par Excel.

• Cliquer sur le bouton Style de pourcentage de la section Nombre du ruban Accueil,

Le résultat est désormais plus précis puisque la cellule affiche 75%, soit une valeur cohérente avec nos audits. Dès lors, l'analyse multicritère finale en F5 n'est plus qu'une formalité. Ce résultat dépend de tous les calculs intermédiaires réalisés et imbriqués. Il s'agit de comparer le résultat du ratio avec la contrainte en pourcentage de la zone de critères, pour définir si les objectifs sur l'année en cours, sont validés ou non. C'est encore une fois la fonction Excel SI qui permet de poser ce raisonnement.

• Cliquer sur la cellule F5 pour la sélectionner,
• Taper le symbole = pour débuter le calcul,
• Saisir le nom de la fonction conditionnelle suivi d'une parenthèse ouvrante, soit SI(,
• Cliquer le ratio de synthèse précédemment calculé, soit la cellule N5,
• Taper le symbole supérieur (>) pour la comparaison,
• Cliquer la contrainte de la zone de critères, soit la cellule R2,
• Taper un point-virgule pour passer dans la branche Alors de la fonction Si,
• Saisir le texte Validé entre guillemets, soit 'Validé',
• Taper un point-virgule pour passer dans la branche Sinon de la fonction Si,
• Saisir l'information Non validé entre guillemets, soit 'Non Validé',
• Fermer la parenthèse de la fonction Si et valider le calcul avec la touche Entrée,

La sentence tombe. Les résultats ne sont pas à la hauteur des espérances. Mais seuls quatre mois pour l'instant ont pu être comptabiliser. Les dirigeants savent que le tir doit être corrigé avec les audits qui seront prochainement réalisés.



En revanche, comme nous avons bâti un modèle d'analyse parfaitement dynamique, si vous modifiez la contrainte en R2 à 70%, l'analyse qualité change instantanément, la couleur avec. Mais plutôt que de changer cette contrainte, si vous modifiez la note la plus faible du mois d'Avril pour la passer au-delà des 90, la conclusion se met à jour de la même façon. Tous nos calculs sont imbriqués et dynamiques, livrant un modèle portable et ajustable pour toutes les sociétés qui souhaitent faire un contrôle de ses audits.