Das Problem
Es gibt einen Master-Test (Test A) und drei Varianten (Test B, Test C und Test D). Alle 4 Tests haben die gleichen 19 Fragen, sind jedoch in einer anderen Reihenfolge angeordnet.
Die erste Tabelle auf dem Bildschirm unten ist ein "Fragenschlüssel" und zeigt, wie Fragen in Test A in den anderen 3 Tests angeordnet sind. Die zweite Tabelle ist ein "Antwortschlüssel", der die richtigen Antworten für alle 19 Fragen in allen Tests anzeigt.
Oben: Richtige Antworten in I5: K23, Formel verdeckt
Zum Beispiel lautet die Antwort auf Frage 1 in Test A C. Dieselbe Frage erscheint wie Frage 4 in Test B, daher lautet die Antwort auf Frage 4 in Test B auch C.
Die erste Frage in Test B ist dieselbe wie die Frage Nr. 13 in Test A, und die Antwort auf beide lautet E.
Die Herausforderung
Welche Formel kann in I5 eingegeben werden (das ist ein i wie in "Iglu") und über I5: K23 kopiert werden, um die richtigen Antworten für die Tests B, C und D zu finden und anzuzeigen?
Sie finden die Excel-Datei unten. Hinterlassen Sie Ihre Antwort als Kommentar unten.
Hinweise
-
Dieses Problem ist schwierig einzurichten. Es ist sehr leicht, verwirrt zu werden. Denken Sie daran, die Zahlen in C5: E23 sagen Ihnen nur, wo Sie eine bestimmte Frage finden können. Danach muss man noch die Frage finden :)
-
Dieses Problem kann mit INDEX und MATCH gelöst werden, die in diesem Artikel erläutert werden. Ein Teil der Lösung besteht darin, Zellreferenzen sorgfältig zu sperren. Wenn Sie Probleme mit solchen Referenzen haben, üben Sie das Erstellen der hier gezeigten Multiplikationstabelle. Dieses Problem erfordert sorgfältig konstruierte Zellreferenzen!
-
Möglicherweise denken Sie, Sie könnten dies manuell schneller tun. Ja, für eine kleine Anzahl von Fragen. Mit mehr Fragen (stellen Sie sich 100, 500, 1000 Fragen vor) wird der manuelle Ansatz jedoch viel schwieriger. Eine gute Formel wird gerne Tausende von Fragen beantworten und keine Fehler machen :)
Es gibt zwei Möglichkeiten, diese Herausforderung zu interpretieren. Als ich das Problem einrichtete, borgte ich direkt von einem Beispiel, das mir ein Leser geschickt hatte. Dies stellt sich als herausfordernder heraus (Interpretation Nr. 2 unten), hauptsächlich weil es so leicht ist, verwirrt zu werden, wenn man versucht, die Tabelle zu verstehen. Im Folgenden erkläre ich beide Interpretationen zusammen mit Formeln, die mit jeder verwendet werden können.
Interpretation # 1 (falsch)
C5: E23 zeigt die gleichen Fragen aus Test A, einfach neu angeordnet. So zum Beispiel in Test B…
Sie finden Frage Nr. 1 aus Test A an Position Nr. 13.
Sie finden Frage Nr. 2 aus Test A an Position Nr. 3.
Sie finden Frage Nr. 3 aus Test A an Position Nr. 7
=INDEX($H$5:$H$23,C5)
Mit den Antworten auf Test A im Array H5: H23 ruft INDEX einfach einen Wert unter Verwendung der Nummer aus Spalte C für die Zeilennummer ab. Einfacher geht es nicht. Dies ist nicht die richtige Antwort für diese Herausforderung, aber es ist trotzdem ein schönes Beispiel.
Interpretation # 2 (richtig)
Die zweite Interpretation ist komplizierter. C5: E23 ist ein Schlüssel, der Ihnen nur sagt, wo Sie eine Frage aus Test A finden können. Er meldet keine Fragennummer, sondern eine Art Index. So zum Beispiel in Test B…
Sie finden Frage Nr. 1 aus Test A an Position Nr. 4.
Sie finden Frage Nr. 2 aus Test A an Position Nr. 19.
Sie finden Frage Nr. 3 aus Test A an Position Nr. 2
Dies ist ein schwierigeres Problem. Anstatt Ihnen zu sagen, welche Frage aus Test A sich an einer bestimmten Position befindet, sagt Ihnen der Schlüssel, wo Sie die gesuchte Frage finden können. Die folgende Formel ist eine korrekte Antwort auf dieses Problem, da sie die in der ursprünglichen Herausforderung gezeigten Antworten zurückgibt.
=INDEX($H$5:$H$23,MATCH($G5,C$5:C$23,0))
Beachten Sie die gemischten Referenzen in MATCH, die sorgfältig eingerichtet wurden, um sie nach Bedarf zu ändern, wenn die Formel über die Tabelle kopiert wird.
$ G5 - Spalte ist gesperrt, Zeile ändert sich
C $ 5: C $ 23 - Zeilen sind gesperrt, Spalten ändern sich
