Problème de formule dansExcell

[Noirdesir]

J'utilise un fichier Excell pour corriger les QCM de l'unif dans laquelle je travaille. Jusqu'il y a 15 jours, on était sous Excell 2003 et tout se passait très bien. Mais maintenant qu'on a migrer massivement sous Seven (Bye XP) et Office 2010, j'ai un petit problème dans mes fichiers.

En gros, mon fichier contient plusieurs pages:

- Une pour les données brutes, en provenance du programme de lecture optique
- Une pour la repentance
- Une pour les points

Comme je suis une brèle dans Excell, j'utilise des formules à base de IF genre:

=IF('Données Brutes'!F2=""; 'Données Brutes'!E2;'Données Brutes'!F2)

Bon, ce n'est certainement pas très beau mais sous Excell 2003, ça marchait très bien et c'est tout ce que je lui demandais. Sauf que maintenant, si je supprime une entrée de ma page Données Brutes, au lieu de me mettre une cellule vide (comme c'est le cas pour les cellules vides à la base), il me met un joli 0... Du coup, pour les pages suivantes, il interprète ça comme une réponse fausse et retire des points aux étudiants (C'est ballot).
Comment-puis-je faire pour qu'il me mette une cellule vide quand la cellule d'origine est effacée?

[Daedaal]

Ton premier test c'est pour savoir si la cellule est vide ?
Si c'est le cas, essaye en remplaçant 'Données Brutes'!F2="" par ISBLANK('Données Brutes'!F2).

[Noirdesir]

En fait, comme il y a de la repentance, je vérifie si l'élève à répondu quelque chose sur la deuxième ligne (ici F2), si cette ligne est vide, je prends la valeurs dans E2, sinon, je prends la valeur dans F2.
Je viens de tester avec cette formule:

=IF(ISBLANK('Données Brutes'!H2); 'Données Brutes'!G2;'Données Brutes'!H2)

Mais ça ne me prends que la repentance, ça ne tient pas compte du reste... Si H2 est vide, il ne me met rien du tout dans ma cellule. Par contre, ça résout effectivement le problème des 0.

[Daedaal]

Je n'ai pas changé ta formule fondamentalement, hein... ISBLANK() c'est à peu près l'équivalent de ="".

Et je dois avouer que je ne pige pas tout à fait ce que tu cherches à faire (déjà, "repentance" est un belgicisme universitaire qui m'est inconnu ) et j'ai l'impression que tu mélanges un peu lignes et colonnes.

Si je reprend tes messages, ce que je comprend c'est que tu as 2 colonnes (E et F) qui contiennent des données. Dans une troisième colonne, tu cherches à mettre la valeur de F si il y a quelque chose dans la cellule, sinon tu prend la valeur de E.


C'est exactement ce que fais la formule que tu donnes en exemple.

Donc comme j'ai bien l'impression que ce que tu veux faire est plus complexe, ça serait pas mal que tu fasses un effort pour clarifier un peu ce qui existe et ce que que tu souhaites obtenir en sortie (quitte à mettre un petit extrait de tes données).

EDITH :
Si en fait tu veux prendre valeur de la repentance uniquement si il n'y a rien dans la première colonne, tu peux aussi imbriquer les IF :

[Noirdesir]

Bon, je vais tenter d'être plus clair, désolé. Pour la repentance, c'est très simple, dans un QCM, si l'élève se trompe, plutôt que d'effacer sa réponse, il y a une deuxième ligne de réponse à leur disposition (plus pratique pour la lecture optique).
Voici un petit fichier pour l'exemple:


Dans cet exemple (ce sont les données brutes), je dois tenir compte de la deuxième colonne (1b, 2b, etc.) si elle est remplie.
Ma formule dans ce cas-ci est:

=IF('Données Brutes'!F2=""; 'Données Brutes'!E2;'Données Brutes'!F2)

Le problème vient des 3. Comme on n'en tient pas compte, je les effaces. Avant effacement ça donne ça:


Après effacement ça donne ça:


Les 3 ont été remplacés par des 0 alors que la cellule est vide...

J'espère que c'est plus clair?

[Daedaal]

C'est un peu plus clair effectivement avec les données.
J'ai quand même encore une question : Qu'est-ce que tu fais avec les non-réponses ou les valeurs invalides (le 3 ici) ?

Enfin déjà avec ces infos complémentaires on devrait pouvoir avancer :

Déjà, pour info il affiche 0 quand il n'y a pas de valeur dans ta cellule source parce qu'il s'agit du résultat d'une formule. On va donc s'arranger pour qu'il n'affiche rien.
Avec cette formule, tu renvoie la réponse si il n'y a pas de valeur pour la repentance, la repentance si il y en a une et rien si il n'y a ni l'un ni l'autre.


Maintenant, je ne comprend pas pourquoi tu t'emmerde à supprimer les 3 "à la main" alors qu'il suffit de les traiter directement dans la formule.
Ça donnerait ça si seule la réponse peut prendre la valeur 3 :


Le OR(test;test;...) renvoie TRUE si l'un des tests est positif (ici A2 est vide ou A2=3).

Ça répond à ta question ?

[Noirdesir]

Bon, je comprends mieux maintenant, en gros, je dois spécifier dans ma formule qu'il ne doit rien afficher si la cellule est vide ou si j'ai un 3. Ce qui est étrange c'est pourquoi la même formule renvoit deux résultats différents dans deux cas similaire?

Si la cellule est vide (et l'a toujours été), la formule me donne une case vide mais si j'efface le contenu de la cellule, la formule me renvoit 0... Heureusement que j'ai remarqué que c'est en effaçant les 3 que je me retrouvais avec des 0 partout sinon j'airais pu chercher encore longtemps d'où ils venait.
Dans ton exemple, si on efface le 3 en case A5, ma formule me renvoit un 0 alors que la même formule en C6 me renvoit une case vide. Du coup, dans le même fichier, j'ai des cases vides et des 0 qui ne sont pas traités de la même manière alors qu'ils le devraient. Je peux évidement modifier ma formule pour les points pour inclure les 0 mais ça ne fait que masquer mon problème.

[Daedaal]

Si tu utilises la formule que je t'ai donné et qui gère les 3, pas besoin de les effacer de tes données source donc une manip en moins à faire). C'est d'ailleurs en général souhaitable de manipuler au minimum la source de données brutes, il vaut mieux effectuer les traitements dans d'autres feuilles, ça simplifie l'importation.

Je ne comprend pas ton problème avec les 0 non plus. Que la cellule soit vide ou à 0, quelle différence dans l'absolu ? Est-ce qu'il n'y a pas moyen de traiter ça de manière plus efficace ?
Par exemple, si tu as un tableau des résultats avec le barème, il doit être assez simple d'afficher directement la note à partir de la formule qu'on a détaillé dans les précédents posts (genre aller chercher le couple n° de question+réponse et retourner les points correspondants au barème).

[Noirdesir]

Ben fondamentalement, avec ta formule, je peux tout à fait gérer ça plus loin mais j'aurais bien voulu comprendre pourquoi parfois Excel décide de remplir une case et parfois non. Mais merci beaucoup pour ton aide en tout cas. Je testerai tout ça demain sur mon fichier de données mais je ne vois pas pourquoi ça ne marcherait pas. Le seul "problème" vient de la formule beaucoup plus complexe à adapter dans chaque nouveau fichier.

[Daedaal]

D’où l'intérêt de ne pas toucher aux données sources : Si le layout de la source et la mécanique de traitement sont identiques, avec un tout petit peu de taf, tu peux adapter tes formules pour prendre en compte les différents cas de figure.
Une feuille avec les données sources, une avec un tableau qui reprend le barème question par question (comme ça tu peux gérer des devoirs différents) et une feuille qui mélange tout ça pour t'afficher les résultats élève par élève.

A vue de nez ça se fait en quelques minutes.

[Noirdesir]

Bon, je viens de comprendre un truc bien pénible... Je ne sais pas pourquoi mais Excel ne considère pas que mes cellules vide sont vides
J'en était venu à utilise cette formule là:

=IF(ISBLANK('Données Brutes'!G2);0;IF(ISBLANK('Données Brutes'!H2);'Données Brutes'!G2;'Données Brutes'!H2))

qui fonctionnait très bien chez moi sur un fichier test mais visiblement, les données en provenance de la lecture optique ne sont pas interprétés de la même manière sous Excel 2010 que sous Excel 2003.

Au moins ça explique mon premier problème... Excel n'affichait pas un 0 parce qu'il affichait une cellule "vide mais en fait non".

A la base, le programme de lecture optique me sort une feuille Excel mais dont les chiffres sont au format texte, en copiant tout cela dans mon fichier de correction, je sélectionne toutes les réponses et reformate les cellules en nombre. Visiblement durant cette transformation, les cellules vides ne sont pas affectées et ne sont pas considérées comme étant des cellules vides...
Je commence à m'arracher les cheveux sur ce truc!

J'ai joint le fichier de données anonymisé pour que ce soit plus clair.
Correction tests.zip

[Daedaal]

Je vais regarder ça.

Effectivement, j'ai aussi été confronté à cette connerie de vide mais pas blank, je ne sais plus comment je m'en était sorti.
Tu as l'air de faire beaucoup de manipulations à la mano sur ton fichier source alors que certaines fonctions pourraient les faire pour toi directement (genre convertir un chiffre format texte en valeur chiffrée).

[Genezis]

Salut,

Je viens de regarder ton fichier, une chose m'étonne : tu dis formater tes cellules en nombres, or sur la 1ère feuille ce sont toujours du texte, j'ai raté un truc ?

Sinon, si j'ai bien compris, essaie cette formule :

=SI(NON(ESTNUM(CNUM('Données Brutes'!I2)));0;SI(NON(ESTNUM(CNUM('Données Brutes'!J2)));'Données Brutes'!I2;'Données Brutes'!J2))

[Noirdesir]

Dans le fichier partagé, pour être sur de ne rien rater, j'ai laissé les données telles qu'elles me sont fournie par la lecture optique. Mais dans mon fichier, je transforme bien ces cellules en nombre.

Pour palier au problème de cellule vide mais pas blank, j'ai trouvé une formule qui fonctionne pas trop mal:

=IF(LEN('Données Brutes'!G2)<=0;0;IF(LEN('Données Brutes'!H2)<=0;'Données Brutes'!G2;'Données Brutes'!H2))

LEN(cellule) test si la valeur dans cette cellule est (dans mon cas) inférieur ou égale à 0. si je teste juste égal à 0, ça ne marche pas par contre, je suppose qu'Excel considère une cellule numérique vide comme ayant une valeur différente de 0.

Pour calculer les points, j'utilise maintenant cette formule:

=IF(Résultats!L2=3; 0; IF(Résultats!L2=0; 0; IF(Résultats!L2=Correction!F$2; 1; -0,5)))

J'ai tenté de la simplifier avec un OR vu que si j'ai 3 ou 0 je compte zéro point avec:

=IF(Résultats!L2=OR(0;3);0;IF(Résultats!L2=Corre ction!F$2;1;-0,5))

mais étrangement, ça ne fonctionne pas, j'ai toujours un -0,5 comme résultat. Pourtant j'ai testé le OR tout seul et il me sort bien un TRUE pour 0 ou 3 mais pas pour le reste. Peut-être que l'on ne peut pas imbriquer le OR comme je n'ai fait...

@Daedaal: Pour les manipulation à la main, avec ces nouvelles fonctions, je ne fait presque plus rien, je transforme juste les données en chiffres (15 secondes...), tout le reste est automatisé.

@Genezis: Ma version d'Excel étant en anglais, il ne reconnait pas les formules en français...

[Daedaal]

Un petit test sur ton fichier et je crois qu'il y a moyen de faire assez facilement.
Jusqu'ici, on testait pour savoir si repentance était vide (ISBLANK), au lieu de ça on va tester si la cellule contient un chiffre avec ISNUMBER().

Comme tes données source sont avec des chiffres en format texte et que je suis une sale feignasse, on va même les convertir à la volée en utilisant la fonction VALUE() qui renvoie le nombre en format nombre si c'est un nombre en format texte.

Donc notre test pour voir si la cellule repentance contient un nombre ou non devient ISNUMBER(VALUE(H2)).

Reste ensuite à inverser les résultats du IF et voilà...

---------- Post added at 13h08 ---------- Previous post was at 13h03 ----------

EDITH : Effectivement, ça marche aussi avec LEN, mais si ta cellule contient un caractère non numérique tu es baisé (c'est de la mauvaise foi de ma part )...

Pour ton essai avec OR(), c'est juste une question de syntaxe. Chaque test logique doit être en entier dans le OR, lui ne renvoie que TRUE ou FALSE.
Il faudrait mettre OR(Résultats!L2=0;Résultats!L2=3).

[Madri]

Attention, la fonction OR te renvoie en réponse la valeur TRUE ou FALSE.

Forcément, dans ta dernière formule tu lui demande si Résultats!L2=OR(0;3) ce qui revient à lui demander si une valeur chiffrée est égale à TRUE ou FALSE. Tu compares 2 types de valeurs non comparables

La bonne formule (rapidement hein, y a p'têtre mieux) serait =IF(OR(Résultats!L2=0;Résultats!L2=3);0;IF(Résu ltats!L2=Correction!F$2;1;-0,5))

Edit : pas assez rapide

[Noirdesir]

Ok, je commence à comprendre la logique derrière les formules Excel... C'est pas toujours super intuitif mais bon, c'est assez logique quand même.
Je viens de tester avec ISNUMBER et cela semble fonctionner sans problème. Je pourrais même l'utiliser comme je le faisais avant mais la nouvelle formule prend plus de cas en considération.

N'empêche, grâce à vous, j'apprends pleins de choses