Transformat. de valeurs Excel par comparaison Fermé

Question

Bonjour à tous!

Je souhaite automatiser dans un classeur Excel la restitution des résultats  à un test de psychométrie : L'inventaire d'intérêts Professionnels (RIASEC). Le principe d'automatisation est le suivant : les testés saisissent des réponses quantitatives dans les cellules. Les résultats sont réstitués sous forme graphique dans une autre feuille.

Pour l'instant, j'ai réussi à automatiser une partie du processus de restitution par macro. Cependant, c'est toujours au testé de "traduire" les valeurs brutes de ses réponses en valeurs standards à partir d'une table d'étalonnage à deux entrées qui se trouve dans une autre feuille (numéro d'item, valeur de la réponse).

Est-il possible d'automatiser cette "transformation" des valeurs brutes en valeurs standards ?
Quelle est la manière la plus simple d'y arriver : 
- faire passer chaque valeur dans une fonction if... else...
- utiliser une fonction dédiée d'Excel
- une troisième solution?

Comme vous l'avez compris, je suis débutant sous Excel. Mais je suis assez motivé pour apprendre! J'ai besoin d'une piste de résolution.

Le classeur que j'ai créé se trouve ici :
www.roselecowboy.net/Inventaire_d_Interets_Professionnels.xls

Merci de votre aide.

blux · Answer

Salut,

j'ai pas tout compris, mais tu peux parfaitement rajouter une cellule après chaque réponse, dont le contenu sera fonction de la cellule 'réponse' et amendé avec une autre cellule, grâce à la fonction si :

Exemple :

Dans feuill1, réponse donnée en A1, formule à mettre dans B1 : = SI(A1="12";feuill2!B5;feuill3!C9)
Le contenu de B1 sera la valeur de la cellule B5 de feuill2 (du même classeur) si A1 est égal à 12, sinon ce sera la valeur de C9 de feuill3

Et on peut imbriquer les si (jusqu'à 8 niveaux) : =si(A1=12;A2;si(a1=13;A3;A4))

castelem · Answer

Merci pour le principe.
comment défintit-on une condition avec un intervalle plutôt qu'avec une variable discréte.
Si A1 appartient à [0, 10] alors...
Merci.

blux · Answer

On utilise le ET :

=si(et(A1>=0;A1<11);"vrai";"faux")

Bien entendu, c'est imbricable... reste à être clair dans sa pensée... :-)

castelem · Answer

Merci beaucoup... Je suis ennuyé car j'ai besoin de définir une condition sur 9 intervalles. En effet, toute valeur brute comprise entre 9 et 1000 doit renvoyer une notre standard comprise de 1 à 9 dans mon test psychométrique... Si cette expression "écourtée" fonctionne (7 imbrications) =SI(ET(A53>=9;A53<=30);9;SI(ET(A53>=31;A53<=42);8;SI(ET(A53>=43;A53<=49);7;SI(ET(A53>=50;A53<=54);6;SI(ET(A53>=55;A53<=61);5;SI(ET(A53>=62;A53<=67);4;SI(ET(A53>=68;A53<=74);3;1))))))) Celle-ci ne fonctionne pas (9 imbrications) : une erreur est relévée aprés la 7ème imbrication... =SI(ET(A53>=9;A53<=30);9;SI(ET(A53>=31;A53<=42);8;SI(ET(A53>=43;A53<=49);7;SI(ET(A53>=50;A53<=54);6;SI(ET(A53>=55;A53<=61);5;SI(ET(A53>=62;A53<=67);4;SI(ET(A53>=68;A53<=74);3;SI(ET(A53>=75;A53<=80);2;SI(ET(A53>=80;A53<=1000);1;0))))))))) Tu m'as effectivement expliqué qu'il y avait un nombre d'imbrication limité..comment résoudre ce soucis... Un grand merci pour ta patience....

blux · Answer

Il peut être possible de créer des pseudo-cellules pour régler le problème en deux parties :

une cellule B53 qui aurait les 4 premiers tests et qui renverrait la note de 9 à 6 si on est bon et qui laisserait le résultat brut sinon.
puis une autre cellule C53 qui regarderait si la valeur de B53 est >9 et ne toucherait à rien tandis qu'une valeur supérieure à 53 lancerait la série des 4 tests suivants ?

c'est clair ? ;-)

castelem · Answer

Merci, le principe est assez clair. Concernant sa mise en pratique, voilà ce que j'ai fait : Cellule 1 (notation de 9 à 3) : =SI(ET(A53>=9;A53<=30);9;SI(ET(A53>=31;A53<=42);8;SI(ET(A53>=43;A53<=49);7;SI(ET(A53>=50;A53<=54);6;SI(ET(A53>=55;A53<=61);5;SI(ET(A53>=62;A53<=67);4;SI(ET(A53>=68;A53<=74);3;C53))))))) Cellule 2 (notation de 2 à 1) : =SI(ET(A53>=9;A53<=54);B53;SI(ET(A53>=75;A53<=80);2;SI(ET(A53>=80;A53<=1000);1;0))) Ca marche effectivement mais ça fait apparaître des cellules de calcul intermédiaire. Ai-je bien interprété ton conseil? Je peux me débrouiller pour "cacher" les cellules 2 par ergonomie. En tout cas, un grand merci!!!

blux · Answer

J'aurais pas fait mieux :-)

castelem · Answer

Merci Bluxx pour tes encouragements.

Voilà le pépin qui me ramène ici :

A l'issu du calcul des formules évoquées plus haut, je me retrouve avec un tableau de valeurs qu'Excel "refuse" de trier par ordre croissant.
Les valeurs sont correctes par rapport à l'étalonnage mais il y a tout de même un petit triangle vert en face de chaque cellule qui laisse soupçonner qu'Excel a un souci avec elles...

Il est plus commode de télécharger la feuille (pas de macro) pour regarder ça concrétement : c'est la tableau IV de la feuille "formulaire" qui ne peut être trié en focntion de la colonne B :

www.roselecowboy.net/Inventaire_d_Interets_Professionnels3.xls 


Un grand merci pour votre aide.

eriiic · Answer

Bonsoir,

Tout d'abord je me suis penché sur ta dernière question et je n'ai pas la réponse actuellement.
Je trouve tes formules un peu impressionnantes et sans doute difficiles à mettre en oeuvre sans erreur et necessitant un contrôle laborieux.
Si j'ai bien compris le principe je te propose un autre système (sans doute trop tard mais bon...)

Inscrire différement ta table d'étalonnage. Ex sur la 1ère ligne du tableau: l'inscrire verticalement et seulement la valeur max :

        A
1   Plein air
2    150
3      79
4      75
5      67
6      60
7      52
8      45
9      38
10    30

Pour +80 j'ai inscris 150 ça devrait suffire... et pour inférieur à 9 ça te ramene toujours 9 mais tu peux ajouter une ligne qui te ramenera 10, là je ne sais pas si ça peut arriver ni comment tu le traites.

Supposons en B1 la valeur à 'étalonner'
=EQUIV(B1;$A$2:$A$10;-1)  te ramène l'index de la plus petite valeur qui est supérieure ou égale à B1, ce qui correspond directement la valeur qui t'interesse de 1 à 9, sans aucun test.
30 => 9
31 => 8
38 => 8
39 => 7 etc

Si en plus tu nommes tes zones, par exemple 'H_PeinAir'=$A$10 ta formule devient plus parlante et plus facile à écrire : =EQUIV(B1;H_PeinAir;-1) 

Si tu n'as pas fini toutes tes formules et que je ne suis pas à coté de la plaque ça t'interessera peut-être
eric

Raymond PENTIER · Answer

C'est à mon tour d'apporter mon grain de sel, qui ne résoudra d'ailleurs pas le problème de notre psychotechnicien castelem ! Concernant les posts 4 & 6, on peut alléger l'écriture des formules. En effet, si j'ai bien compris, la valeur de A53 est un nombre entier qui ne peut jamais être inférieur à 9. Alors : =SI(ET(A53>=9;A53<=30);9;SI(ET(A53>=31;A53<=42);8;SI(ET(A53>=43;A53<=49);7;SI(ET(A53>=50;A53<=54);6;SI(ET(A53>=55;A53<=61);5;SI(ET(A53>=62;A53<=67);4;SI(ET(A53>=68;A53<=74);3;1))))))) peut s'écrire : =SI(A53<31;9;SI(A53<43;8;SI(A53<50;7;SI(A53<55;6;SI(A53<62;5;SI(A53<68;4;SI(A53<75;3;1))))))) C'est quand même plus simple ? Avec 7 imbrications on obtient 8 valeurs possibles. Il nous en faut une neuvième ? Nous pouvons "trafiquer" le dernier test pour lui faire afficher 2 ou 1, selon que A53 sera plus petit ou plus grand que 80 ; pour y parvenir, remplaçons le "1" en fin de formule par ENT(4-A53/40). Concernant le post 8, c'est malheureusement évident que ton tri ne marchera jamais, puisqu'il n'opère pas sur des valeurs, résultats d'opérations diverses, mais sur des formules. Ainsi ton tri sur la colonne B se fait bien, comme tu peux le constater sur la colonne A qui est modifiée (lignes 177 à 188). Mais quel est le contenu de B177 ? "=L141". Cette formule n'est pas modifiable par le tri, et le tableau se trouve complètement faussé. S'il me vient une idée (avant les copains) je la posterai. Bon travail.

eriiic · Answer

Pour completer mon post 9 en fait tu peux le faire en laissant ton tableau de valeurs en horizontal ce qui en simplifie la saisie puisu'il n'y a plus qu'à incrire la valeur max en conservant sa forme.

eric

castelem · Answer

Bonjour, 


Merci à Eric et à Raymond !
Je suis très impressionné par vos astuces (index matriciel, référence à la valeur entière pour passer de 8 valeurs à 9).
Pour comprendre vos suggestions, il m'a fallu lire la description des fonctions plusieurs fois dans l'aide d'Excel (surtout pour l'index matriciel et cette satanéee valeur de l'argument type! )
C'est assurément ces astuces que j'utiliserai dorénavant pour l'étalonnage des tests que je dois automatiser sous Excel.
Cela écourte considérablement le temps de saisi et cela rend le contrôle des formules bien plus facile .

Pour l'heure, j'attends avec impatience vos astuces pour pouvoir trier les valeurs étalonnées. Il parait étonnant qu'Excel ne permettent pas de trier les valeurs qui sont le résultat d'une formule.

Je cherche de mon côté. A bientôt j'espère!

Raymond PENTIER · Answer

Désolé !

Comme promis en post 11, j'ai cherché mais sans rien trouver.
Il faudrait qu'un spécialiste de macros t'en propose une pour faire un Copier / Collage spécial / Valeurs des lignes 177 à 188, macro à activer après toutes tes saisies, et collage sur lequel tu pourrais effectuer ton tri.

Salut à tous.

eriiic · Answer

Re,

La meilleure solution serait, comme dit raymond, de faire une fonction spécialisée

Voici une solution avec formule mais faut ruser... 
Ce n'est pas un tri mais tu retrouves tes valeurs dans l'ordre souhaité.
C'est un peu dur (et long surtout) à expliquer le principe mais j'essaie...

La difficulté vient de ce que plusieurs items ont le même poids. Par exemple 4 fois la valeur 4, 3 fois la valeur 5 etc
La 'ruse' intervient ici, au lieu de calculer le rang (qui met les ex-equo au même rang) je calcule le rang additionné de la ligne relative dans la matrice divisée par 100 (tu suis ? ;-) ). Ainsi plus d'ex-equos, au lieu d'avoir 4 fois le rang 8 (dans la colonne Q que j'ai ajouté) pour la valeur 4 (en colonne L) j'obtiens 8.01 8.02 8.03 8.04. Ensuite il n'y a plus qu'à utiliser la fonction rang normalement en colonne R (ajoutée) ce qui ramene le classement de 1 à 12.
Après avec une combinaison de Index() et Equiv() + une colonne supplémentaire où tu indiques le rang à ramener (c'est dans un soucis de clarté et avoir la possibilité de recopier la formule mais tu peux t'en passer en inscrivant le rang désiré en dur dans la formule)  il est facile de ramener le libellé de l'item et la valeur 'étalonnées' correspondante.

En résumé 
ajout de rang1 avec formules en Q141:Q152  =RANG(L141;$L$141:$L$152)+(LIGNE()-140)/100 '  rang avec décimales
                              Attention à la valeur 140, elle dépend du numéro de la ligne où est inscrite la formule
ajout de rang2 avec formules en R141:R152  =RANG(Q141;$Q$141:$Q$152)   ' rang de 1 à 12

en A177:A188 :    =INDEX($A$141:$R$152;EQUIV($C177;$R$141:$R$152;0);1)   ' ramene le texte de la zone $R$141:$R$152, colonne A, ligne du rang indiqué en $C177
en B177:B188 :    =INDEX($A$141:$R$152;EQUIV($C177;$R$141:$R$152;0);12)  ' ramene le total étalonné de la colonne L (12èmecol)
en C177:C188 : les valeurs de rang désiré de 1 à 12

Le plus simple est de regarder l'exemple :
http://www.cijoint.fr/cij7098540429326.xls
J'ai supposé qu'il fallait ramener en 1er le total étalonné le plus élevé...

eric

castelem · Answer

Merci pour ta formule optimisée Eric, je la copie-colle dans un coin avec ton fichier exemple. Je m'y pencherai pour ma prochaine automatisation de test. Cela va me demander un petit effort, mais l'enjeu est motivant. Car le problème du tri est résolu "automatiquement".
Je reviens pour vous soumettre mes dernières investigations : j'ai pris au mot Raymond et j'ai enregistré une macro qui trie les valeurs récupérées à partir de collage spécial/valeurs. J'obtiens automatiquement mes graphiques!
Le seul hic, c'est que :
- la macro bogue ligne 438 (ce qui n'est jamais très ergonomqiue pour l'utilisateur, même si les graphiques sont générés)
- que la macro se redéclenche à chaque fois qu'on clique dans une cellule ...

Est-il possible de déboguer le ligne 438, et de forcer la macro à ne se lancer qu'une seule fois?

Voilà le lien vers mon dernier fichier :
http://roselecowboy.net/Inventaire_d_Interets_Professionnels_macro.xls
attention : il faut saisir la cellulle L158 pour déclencher la macro qui trie les valeurs étalonnées et génére un graphique

J'espère que vous ne m'en voudrez pas de mettre tout de suite en application les dernières formules d'Eric,  mais je dois faire passer ce test à des stagiaires demain. J'aurai plus de temps pour me pencher sur ces formules après.

En attendant impatiemment de vous lire.

Raymond PENTIER · Answer

Alors là, je passe la main : Je ne suis pas assez compétent, question macro.
Mais je voulais féliciter castelem de sa confiance et de son enthousiasme, et je suis persuadé qu'il aboutira à un résultat exemplaire. 

Bonne continuation.

castelem · Answer

Merci Raymond pour votre précieuse aide : la macro marche, il faut simplement la "nettoyer" un peu. 

Je suis sûr que quelqu'un aura une idée : la question qu'il reste à résoudre a été posée initialement ici :
lancement conditionnelle d une macro#0

J'ai réorienté la résolution du problème dans ce fil de discussion, je clôturerai les deux fils une fois le problème résolu.

En attendant, cela me rendra tout de même de grands services d'avoir ce classeur Excel en l'état.

Merci à vous.

castelem · Answer

Bonjour Eric,

Effectivement, grâce au post 3 de l'autre discussion, j'ai réussi à automatiser la macro de tri : j'explique là où ça coince dans le post 19 de cette discussion.

J'ai placé un lien de téléchargement vers la dernière version du classeur qui remplit son office mais qu'il  faut "deboguer" un peu (ligne 439):
http://roselecowboy.net/Inventaire_d_Interets_Professionnels_macro.xls 

Mathieu.

eriiic · Answer

Re Castelem,

Je viens de voir ta macro:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
 Dim Fin As Boolean
 Fin = True
    For Each cel In Range("L147:L158")
     If cel.Value = "" Then Fin = False
   Next
 If Fin Then Call TrierMaZone ' sous-entendu if fin = true
End Sub
1) si tu utilises l'evenement selection_change c'est normal qu'elle se déclenche à chaque nouvelle selection de cellule dans ta feuille.
2) au vu de ta demande de l'autre post "automatiser le déclenchement d'une macro juste après que toutes les informations demandées à l'utilisateur ont été saisies dans les cellules prévues à cet effet"  j'aurais plutôt utilisé l'evenement change. En général sur les evenements change et selectionchange on contrôle si la zone modifiée faire partie de la zone 'surveillée' pour ne lancer le traitement que si ça nous interesse. Ce qui aurait donné :
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim Fin As Boolean
    Fin = True
    If Not Intersect(Target, [L147:L158]) Is Nothing Then
        For Each cel In Range("L147:L158")
            If cel.Value = "" Then Fin = False: Exit For
        Next
        If Fin Then Call TrierMaZone ' sous-entendu if fin = true
    End If
End Sub
C'était une parenthese informative car ......

Si je regarde ta feuille, sur ces cellules ce n'est pas du tout une saisie utilisateur mais le resultat d'une formule ! Ce qui n'a rien à voir puisque l'evenement change n'a lieu QUE sur saisie.

Là il faut que tu utilises l'evenement calculate et ta macro devient :
Private Sub Worksheet_Calculate()
 Dim Fin As Boolean
 Fin = True
    For Each cel In Range("L147:L158")
     If cel.Value = "" Then Fin = False
   Next
 If Fin Then Call TrierMaZone ' sous-entendu if fin = true
End Sub

N'oublie pas de supprimer Sub Worksheet_SelectionChange

eric

castelem · Answer

Franchement merci pour ta patience et pour tes efforts pédagogoqies Eric, c'est très stimulant.
Je m'attelle à la tâche cette nuit.
Bonne soirée.

castelem · Answer

Bonjour, 

Un grand merci à tous pour votre patience et la précisison de vos explications!
Merci à Blux pour le pied à l'étrier et la première formule
Merci à Eric pour les macros (j'ai pu optimiser l'ergonomie du lancement conditionnel)
Merci à Raymond pour le collage spécial
Je place le lien vers le classeur pendant 3 jours, ça peut dépanner dans votre entourage pour amorcer une réflexion personnelle sur ses centres d'intérêts professionnels. Le test RIASEC a une bonne validité externe concernant la prévision du niveau de satisfaction dans l'emploi occupé.
 
http://roselecowboy.net/Inventaire_d_Interets_Professionnels_RIASEC.xls

A bientôt.

eriiic · Answer

Bravo à toi
Si je peux me permettre une suggestion: changer la couleur de police des champs remplis (et pourquoi pas ceux des libellés correspondants) pour mieux faire apparaitre ceux restant à saisir.
Sinon pour le fond orange pétard c'est super, tu fais un test de vue en même temps !!! ;-)
eric

castelem · Answer

C'est une super idée: il faut plutôt faire une macro où c'est une fonction qu'on colle dans chaque cellule?

eriiic · Answer

Il faut utiliser 'format  / mise en forme conditionnelle'
Dans condition 1 tu saisis par exemple  =$B10 <> "?"
Tu peux traiter une sélection d'un coup mais évite les sélections sur plusieurs colonnes. Et si tu fais une sélection multiple, dans la formule il faut bien désigner la cellulle sur la même ligne que la cellule active.
eric

castelem · Answer

Et hop, c'est fait. Merci, c'est super cette fonction !
Mathieu.