Arbre de décision Excel

Cylfo,

Oui tout à fait, CCP, PRP ou Prpo en fonction des réponses. Chaque ligne correspond à une étape de production. Pour chaque étape (ou presque), je me poserais les mêmes questions (Q1 à Q4) et donc pour chaque étape de production je dois avoir un résultat final CCP, PRP ou Prpo.

Merci de ton aide

Bonjour Cali,

La version ci-dessous intègre la formule (simple) qui permet d'afficher le résultat lorsque les réponses correspondent à un des chemins de l'arbre de décision.

J'ai capitaliser sur les différentes versions faites au fur et à mesure que le besoin a évolué et s'est précisé, ce qui permettrait de définir d'autres arbres de décision.

L'onglet "Arbre de décision" comporte 2 éléments indispensables au fonctionnement de la feuille 3 :

1 - Le tableau de définition de l'arbre de décision (tableau dynamique, colonnes A à F). En cas de création/modification de l'arbre de décision, c'est la 1ère étape à faire. La 2ème étape serait de valider visuellement l'arbre de décision via l'onglet "Représentation" qui fait un pseudo logigramme de l'arbre de décision.

2 - Le tableau des chemins de l'arbre de décision (colonnes H et I). Etape obligatoire en cas de modification / création d'un arbre de décision. Ce tableau peut être complété :

a - Soit manuellement en renseignant les différents chemins (colonne H) aboutissant à un résultat (colonne I). Un chemin est composé des réponses Oui/Non séparé par des ;

b - Soit automatiquement en utilisant la macro associée au bouton "Chemins possibles de l'arbre de décision". Si tu veux prendre l'option a manuelle, tu peux supprimer le bouton et en réenregistrer le fichier en XLSX (la macro ne sera plus présente).

L'onglet "Représentation" n'est pas indispensable mais voir point 1 ci-dessus.

L'onglet "3", le label de fin est affiché lorsque les réponses (Oui / Non) correspondent à un des chemins recensés dans l'onglet "Arbre de décision" en colonnes H et I. Sinon le chemin n'existe pas, label de fin n'est pas affiché. A noter qu'il n'y a pas de distinction entre un chemin incomplet (Q1[Oui] implique une réponse à Q2) et un chemin erroné (par exemple : Q1[Non] et Q2[Oui ou Non] puisque Q1[Non] => PRP). Si tu veux ajouter des questions, démasques la ligne 1 et les connes R à T et consultes le commentaire en T7. Si tu ajoutes des étapes, il suffit de dupliquer la dernière ligne (actuellement ligne 20).

Je n'arrive pas à joindre le fichier (message "Service non disponible"), je réessaie dans quelques instants et te l'envoie dès que cela refonctionne.

Bonjour Cylfo,

Je suis épatée, j'ai fait un essai et cela fonctionne comme je le souhaite. C'est vraiment très complet.

Je vais maintenant essayer de compléter mon tableau pour vérifier que tout fonctionne ms je n'en doute pas.

Juste une demande (c'est un détail et si ce n'est pas possible ce n'est vraiment pas grave), à tout hasard est-ce que le Logigramme peut etre intégré à l'onglet "Arbre de décision" pour supprimer l'onglet "Représentation" ? Comme je vais ajouter d'autres onglet à ce fichier, j'essaie d'en avoir le moins possible.

Merci encore c'est top

Bonjour Cali,

Avec le logigramme intégré à l'onglet "Arbre de décision".

C'est super ! Merci beaucoup Cylfo

Je vais maintenant le compléter petit à petit pour vérifier qu'il ne me manque rien et je te redirais.

Très contente, merci encore Cylfo

Bonjour à tous,

Je me permets de vous recontacter pour mettre à jour le fichier excel avec arbre de décision qui est vraiment super !

Je dois ajouter une question à mon arbre, je l'ai simplement ajoutée au tableau "T_ARBRE_DECIS" dans l'onglet "Arbre de décision" mais ça ne se met pas à jour en colonnes K à V.

Dans l'onglet "Analyse de risque", j'ai également ajouté la question 5 en R24. L'arbre de décision dit que si la réponse à la question 2 est non, je dois répondre à la question 3, si la réponse à la question 2 est oui, je dois aller directement à la question 5 (sans passer par les questions 3 et 4). Y aurait-il un moyen de mettre un verrou pour éviter de compléter par erreur les questions 3 et 4 en cas de réponse oui à la question 2 ?

Dans l'exemple en PJ, on à Q1= oui, Q2=oui et possibilité de compléter par oui ou non en Q3 et Q4 ce qui peut fausser les résultats.

Merci d'avance

Bonjour Cali,

Je vois que dans le tableau la question 2 renvoi vers 2 autres questions. Jusqu'à présent les questions ne comportaient comme choix possibles qu'une question et un label de fin ou 2 labels de fin mais pas 2 questions. Du coup il y a quelques impacts ... je regarde et te reviens.

Cdlt,

Je vois aussi que tu as modifié la plage de données du questionnaire en tableau () et je pense que c'est ce qui explique la présence de la ligne 9. En transformant la plage en tableau, les formules qui formaient le titre des questions ont été converties en valeur texte.

Dans la version provisoire jointe :

• J'ai scindé le tableau initial en 2 tableaux, le 1er (T_ETT_QUEST) pour gérer les titres des colonnes et éviter les 2 lignes (+ qq complications au niveau du code), le 2ème (T_QUEST) uniquement dédié aux questions. Dans ces 2 tableaux, j'ai masqué les lignes de titres (ne pas décocher "Ligne d'en-tête" dans le menu "Outils de tableau", ça met le bazar car les 2 tableaux se suivent). Il y a une conséquence : les filtres ne sont plus disponibles ... Si c'est gênant il faudra revenir au tableau initial avec une pseudo ligne de titre (pas de souci pour le retour arrière).
• La nouveauté introduite pas les questions avec les choix Oui et Non conduisant à 2 autres questions a aussi des conséquences.
  • Le logigramme ne peut plus (du moins je ne sais pas faire) être "dessiné" directement avec des formules Excel. Je dois pouvoir le faire avec des formes (shape) Excel. Je t'ai mis un exemple (fait manuellement) de ce que cela pourrait donner.
  • Il peut y avoir des sauts dans les questions, exemple la 2 renvoi vers la 5 si "Oui" et vers la 3 si "Non". La réponse a une question te positionne sur la question suivante, exemple : si tu réponds Oui à la 2, tu vas directement à la 5 et 3 et 4 sont grisées. A la dernière réponse, tu passes à la ligne suivante, ce n'était pas voulu mais c'est peut-être bien. Pour pouvoir gérer le "saut", il faut que la feuille soit protégée et l'option "Sélectionner les cellules verrouillées" décochée. En dehors de la plage réservée à la 1ère question, toutes les cellules sont verrouillées. Il faudra dans la version définitive que tu déverrouilles les cellules ouvertes à la saisie.
  • J'ai ajouté un bouton "Effacer le questionnaire".

Je te joins une version provisoire pour que tu puisses tester la partie questionnaire mais ne modifies pas la feuille, il faudra que je te donne un peu plus d'infos car il faut modifier les 2 tableaux de manière similaire.

Par rapport à cette version, dis moi si :

1. L'absence de filtre est rédhibitoire et d'ailleurs je n'ai pas d'idée sur le nombre de lignes que pourrait comporter le questionnaire ?
2. La saisie des réponses aux question et la gestion des "sauts" te conviennent y compris le passage à la ligne suivante ?
3. Le mode de représentation du logigramme (l'exemple peut comporter des erreurs), ok ?
4. Et autres remarques

1. L'absence de filtre est rédhibitoire et d'ailleurs je n'ai pas d'idée sur le nombre de lignes que pourrait comporter le questionnaire ? Disons que l'absence de filtre est assez handicapant et malheureusement je ne sais pas combien de lignes peut comporter le questionnaire car il va me servir pour plusieurs études différentes. Il n'est pas possible d'insérer des segments à partir de la ligne 10 (même masquée) ? Ces filtres seraient je pense suffisants.
2. La saisie des réponses aux question et la gestion des "sauts" te conviennent y compris le passage à la ligne suivante ? C'est parfait ! j'aime également le changement de couleur de la cellule en ligne 8 pour la question en cours, c'est vraiment bien. Le retour à la ligne également. Le fait que les cellules soient protégées évitent le risque d'erreur, vraiment rien à dire c'est parfait !
3. Le mode de représentation du logigramme (l'exemple peut comporter des erreurs), ok ? ça me va tout à fait ! Est-il possible de changer les formes en rectangle ou cercle par exemple ? Si ce n'est pas possible, ce n'est pas grave du tout, c'est très bien comme ça. C'est juste une question pour savoir si c'est facilement modifiable si je souhaite le faire plus tard. Il ne me semble pas avoir détecté d'erreur dans ce logigramme.
4. Et autres remarques : Je n'ai pas de remarque, ça répond à toutes mes attentes même beaucoup plus que ce que j'imaginais. Il faudra par contre que je fasse un mémo de tout ce qu'il ne faut pas toucher/modifier pour ne pas faire d'erreur.

Merci beaucoup Cylfo

Réponses :

1 - Je regarde.

3 - Je l'ai fait à la main. Je vais intégrer la construction au recensement des chemins. Pour les formes pas de pbm.

4 - Je vais intégrer un onglet "Explications"

Bonjour Cali,

Tu trouveras ci-joint la nouvelle version du questionnaire. Plusieurs changements dans les onglets :

• [Arbre de décision]
  • Le tableau [T_ARBRE_DECIS] s'est enrichi d'une colonne "Contrôles" qui vérifient la cohérence des infos saisies au niveau des "Si oui, ..." et "Si non, ..." et de l'utilisation des questions de la colonne "Question". Si un message est émis, les cellules pouvant être concernées sont sur fond rouge. Un autre contrôle est inclus dans le code, il concerne l'existence d'une boucle sans fin dans le questionnaire.
  • Le bouton "Vérifier et générer le questionnaire" a changé de nom mais surtout il effectue plus d'actions : Recenser les chemins, dessiner le logigramme et ... et actualiser les colonnes du questionnaire. Donc plus besoin de modifier directement les colonnes du questionnaire, c'est fait à ce niveau et en cohérence avec le tableau [T_ARBRE_DECIS]. Attention en cliquant sur ce bouton, cela provoquera l'effacement des réponses présentes dans le questionnaire.
  • Représentation du logigramme. Les formes et les couleurs sont paramétrables dans le nouvel onglet [Paramètres].
• [Analyse risques]
  • Il y avait un espace à la fin du nom de l'onglet, je l'ai ôté. Je pense qu'il n'était pas voulu mais si c'est le cas, il faudra modifier une constante dans le code.
  • Il n'y a plus qu'un seul tableau avec une seule ligne de titre et des filtres actifs et Il n'y a plus non plus de lignes masquées. Il reste uniquement la colonne "COLONNE BORNE" qui elle reste masquée et qu'il ne faut surtout pas supprimer. Les colonnes de la partie questionnaire ne doivent pas être modifiées directement.
  • Si des colonnes doivent être ajoutées dans les autres parties du tableau [T_QUEST], il faut au préalable passer en mode maintenance (voir onglet [Paramètres]) et de préférence regénérer le questionnaire sauf s'il ne doit pas être effacé.
  • Il ne faut pas modifier les protections de la plage de cellules de la partie questionnaire. Les autres cellules sont pour l'instant verrouillées mais tu peux les déprotéger si c'est nécessaire.
• [Paramètres]
  • Les cellules sur fond blanc et jaune pâle sont modifiables, elles permettent le paramétrage du logigramme. Je ne détaille pas les tableaux, les libellés (je l'espère) me semblent assez significatifs et j'ai ajouté quelques commentaires.
  • Il y a une liste avec les labels de fin et les couleurs (fond et police) utilisée pour la représentation graphique de l'arbre de décision. Cette liste est modifiable mais je n'ai pas inclus la mise à jour des mises en forme conditionnelle de la feuille "Analyse risques" (j'y ai pensé en écrivant ...), il faudra le faire manuellement.
  • Le bouton [Mode maintenance] : il active ou désactive la prise en compte des événements (Application.EnableEvents) déclenchées pour le classeur et ses feuilles. Si tu dois modifier la feuille "Analyse risques", il faut que tu passes en mode maintenance, sinon certaines modifications que tu tenteras reverrouilleront la feuille avant même que tu ne puisses les effectuer. Ne pas oublier de réactiver la gestion des événements sinon rien de se passera en répondant au questionnaire.

Conseil superflu mais ... si tu veux apporter des modifications, fais une sauvegarde préalable du classeur.

Dans le fichier joint, j'ai modifié les questions, j'en ai rajouté et j'ai aussi testé le renvoi de plusieurs questions vers une même question (exemple : 5 & 7 -> 8).

Je te laisse tester ...

Bonjour Cylfo,

L'évolution est impressionnante ! Il y a beaucoup d'informations et il va me falloir un petit peu de temps pour tout découvrir.

J'ai une 1ère question (peut-être idiote mais tant pis). J'ai essayé de tester le logigramme à partir des 5 premières questions (l'ajout les questions supplémentaires est très bien, on ne sait jamais) mais ça ne fonctionne pas. Comment faire pour ne prendre en compte de les questions nécessaires ?

Si j'ai bien compris ta question : Dans la colonne "QUESTION", tu sélectionnes les questions de 6 à 11, clic droit dans la sélection, Supprime / Lignes de tableaux.

Avant de tester le logigramme, il faut que tu vérifies / rectifies les données car au cours de mes tests, je les ai modifiées et pas forcément remis à l'état initial.

et ensuite, il faut que tu cliques sur le bouton "Vérifier et générer le questionnaire" car le dessin du logigramme est inclus dans les traitements déclenchés par ce bouton.

Le logigramme fonctionne mais j'ai le message suivant qui apparait : "

"Une erreur est survenue lors de l’actualisation du questionnaire (Feuille[Arbre de décision]), il pourrait être nécessaire de l’actualiser manuellement avant de pouvoir l’utiliser.

Erreur 9 : L’indice n’appartient pas à la sélection"

Je dois louper quelque chose

Avec cette version, cela devrait beaucoup mieux fonctionner.

ça fonctionne

J'ai remarqué qu'en choisissant par exemple "cercle" dans "question" et constante 9, le chemin négatif (flèche rouge) passe par-dessus les formes (c'est un détail, il y a tellement de possibilités).

Sinon apparemment en changeant la constante uniquement, cela modifie la forme ?

Je vais continuer le tester demain au travail en le complétant avec mes données. Je reviens vers toi pour te tenir au courant.

Ce fichier est vraiment excellent, merci beaucoup.

Pour les formes, je ferai une modification car selon les formes il y a plus ou moins de points de connexions.

Pour la constante, oui tu pourrais ne modifier que la constante mais perso je visualise ce qu'est un cercle par contre de tête je ne suis pas sûr de pouvoir te dire ce que représente 9 comme forme ... et ce n'est pas l'objectif car il faut déprotéger la feuille alors qu'une fois les formes ajoutées, il suffit de les sélectionner.

Je grouperai les modifs en fonction de tes retours.

Pas de problème, je regarde demain et je te redis.

Bonne soirée