Le problème
Il existe un test principal (test A) et trois variantes (test B, test C et test D). Les 4 tests ont les mêmes 19 questions, mais disposées dans un ordre différent.
Le premier tableau de l'écran ci-dessous est une «clé de question» et montre comment les questions du test A sont classées dans les 3 autres tests. Le deuxième tableau est une «clé de réponse» qui montre les bonnes réponses pour les 19 questions de tous les tests.
Ci-dessus: réponses correctes en I5: K23, formule masquée
Par exemple, la réponse à la question n ° 1 du test A est C. Cette même question apparaît en tant que question n ° 4 du test B, donc la réponse à la question n ° 4 du test B est également C.
La première question du test B est la même que la question n ° 13 du test A, et la réponse aux deux est E.
Le défi
Quelle formule peut être entrée dans I5 (c'est un i comme dans «igloo») et copiée sur I5: K23 pour trouver et afficher les bonnes réponses aux tests B, C et D?
Vous trouverez le fichier Excel ci-dessous. Laissez votre réponse sous forme de commentaire ci-dessous.
Astuces
-
Ce problème est difficile à mettre en place. Il est très facile de se confondre. N'oubliez pas que les nombres en C5: E23 vous indiquent seulement où trouver une question donnée. Vous devez encore trouver la question après ça :)
-
Ce problème peut être résolu avec INDEX et MATCH, qui est expliqué dans cet article. Une partie de la solution consiste à verrouiller soigneusement les références de cellule. Si vous rencontrez des problèmes avec ce type de références, entraînez-vous à construire la table de multiplication présentée ici. Ce problème nécessite des références de cellules soigneusement construites!
-
Vous pourriez penser que vous pourriez le faire plus rapidement manuellement. Oui, pour un petit nombre de questions. Cependant, avec plus de questions (imaginez 100, 500, 1000 questions), l'approche manuelle devient beaucoup plus difficile. Une bonne formule répondra volontiers à des milliers de questions et ne fera pas d'erreur :)
Il y a deux manières d'interpréter ce défi. Lorsque j'ai posé le problème, j'empruntais directement à un exemple qui m'a été envoyé par un lecteur. Cela s'avère être l'approche la plus difficile (interprétation n ° 2 ci-dessous), principalement parce qu'il est si facile de se confondre en essayant de comprendre le tableau. Ci-dessous, j'explique les deux interprétations ainsi que les formules qui peuvent être utilisées avec chacune.
Interprétation n ° 1 (incorrecte)
C5: E23 montre les mêmes questions du test A, simplement réorganisées. Ainsi, par exemple, dans le test B…
Vous pouvez trouver la question n ° 1 du test A à la position n ° 13
Vous pouvez trouver la question n ° 2 du test A à la position n ° 3
Vous pouvez trouver la question n ° 3 du test A à la position n ° 7
=INDEX($H$5:$H$23,C5)
Avec les réponses au test A dans le tableau H5: H23, INDEX récupère simplement une valeur en utilisant le numéro de la colonne C pour le numéro de ligne. Cela ne devient pas beaucoup plus simple. Ce n'est pas la bonne réponse à ce défi, mais c'est quand même un bel exemple.
Interprétation # 2 (correcte)
La deuxième interprétation est plus compliquée. C5: E23 est une clé qui vous indique uniquement où vous pouvez trouver une question du test A. Elle ne rapporte pas un numéro de question, elle rapporte une sorte d'index. Ainsi, par exemple, dans le test B…
Vous pouvez trouver la question # 1 du test A à la position # 4
Vous pouvez trouver la question # 2 du test A à la position # 19
Vous pouvez trouver la question # 3 du test A à la position # 2
C'est un problème plus délicat. Au lieu de vous dire quelle question du test A se trouve dans une position donnée, la clé est de vous dire où vous pouvez trouver la question que vous cherchez. La formule ci-dessous est une réponse correcte à ce problème, car elle renverra les réponses indiquées dans le défi original.
=INDEX($H$5:$H$23,MATCH($G5,C$5:C$23,0))
Notez les références mixtes à l'intérieur de MATCH qui ont été soigneusement configurées pour changer selon les besoins lorsque la formule est copiée dans le tableau.
$ G5 - la colonne est verrouillée, la ligne changera
5 $ C: 23 $ C - les lignes sont verrouillées, les colonnes changeront
