Het probleem
Er is één mastertest (Test A) en drie varianten (Test B, Test C en Test D). Alle 4 tests hebben dezelfde 19 vragen, maar in een andere volgorde gerangschikt.
De eerste tabel in het onderstaande scherm is een "vraagtoets" en laat zien hoe de vragen in Test A gerangschikt zijn in de andere 3 tests. De tweede tabel is een "antwoordsleutel" die de juiste antwoorden toont voor alle 19 vragen in alle tests.
Boven: Juiste antwoorden in I5: K23, formule verduisterd
Het antwoord op vraag 1 in toets A is bijvoorbeeld C. Deze zelfde vraag verschijnt als vraag 4 in toets B, dus het antwoord op vraag 4 in toets B is ook C.
De eerste vraag in Test B is hetzelfde als vraag # 13 in Test A, en het antwoord op beide is E.
De uitdaging
Welke formule kan worden ingevoerd in I5 (dat is een i zoals in "iglo") en gekopieerd over I5: K23 om de juiste antwoorden voor tests B, C en D te vinden en weer te geven?
U vindt het Excel-bestand hieronder. Laat hieronder uw antwoord achter als opmerking.
Tips
-
Dit probleem is een uitdaging om op te zetten. Het is heel gemakkelijk om in de war te raken. Onthoud dat de cijfers in C5: E23 u alleen vertellen waar u een bepaalde vraag kunt vinden. Daarna moet je de vraag nog vinden :)
-
Dit probleem kan worden opgelost met INDEX en MATCH, wat in dit artikel wordt uitgelegd. Een deel van de oplossing omvat het zorgvuldig vergrendelen van celverwijzingen. Als je problemen hebt met dit soort referenties, oefen dan met het bouwen van de tafel van vermenigvuldiging die hier wordt weergegeven. Dit probleem vereist zorgvuldig opgebouwde celverwijzingen!
-
Je zou kunnen denken dat je dit sneller handmatig zou kunnen doen. Ja, voor een klein aantal vragen. Bij meer vragen (stel je voor 100, 500, 1000 vragen) wordt de handmatige aanpak echter veel moeilijker. Een goede formule kan duizenden vragen met plezier beantwoorden en zal geen fouten maken :)
Er zijn twee manieren om deze uitdaging te interpreteren. Toen ik het probleem opstelde, leende ik rechtstreeks van een voorbeeld dat mij door een lezer was gestuurd. Dit blijkt de meer uitdagende benadering te zijn (interpretatie 2 hieronder), vooral omdat het zo gemakkelijk is om in de war te raken als je de tabel probeert te begrijpen. Hieronder leg ik beide interpretaties uit, samen met formules die bij elk kunnen worden gebruikt.
Interpretatie # 1 (onjuist)
C5: E23 toont dezelfde vragen uit test A, gewoon opnieuw gerangschikt. Dus bijvoorbeeld in Test B …
Je kunt vraag # 1 van Test A vinden op positie # 13
Je vindt vraag # 2 van Test A op positie # 3
Je vindt vraag # 3 van Test A op positie # 7
=INDEX($H$5:$H$23,C5)
Met de antwoorden op Test A in de array H5: H23 haalt INDEX eenvoudig een waarde op met behulp van het nummer uit kolom C voor het rijnummer. Het wordt niet veel eenvoudiger dan dit. Dit is niet het juiste antwoord voor deze uitdaging, maar het is sowieso een mooi voorbeeld.
Interpretatie # 2 (correct)
De tweede interpretatie is ingewikkelder. C5: E23 is een sleutel die u alleen vertelt waar u een vraag uit toets A kunt vinden. Het rapporteert geen vraagnummer, het rapporteert een soort index. Dus bijvoorbeeld in Test B …
Je vindt vraag # 1 van Test A op positie # 4
Je vindt vraag # 2 van Test A op positie # 19
Je vindt vraag # 3 van Test A op positie # 2
Dit is een lastiger probleem. In plaats van u te vertellen welke vraag uit Test A zich in een bepaalde positie bevindt, vertelt de sleutel u waar u de vraag kunt vinden die u zoekt. De onderstaande formule is een correct antwoord op dit probleem, aangezien het de antwoorden zal retourneren die in de oorspronkelijke uitdaging werden getoond.
=INDEX($H$5:$H$23,MATCH($G5,C$5:C$23,0))
Let op de gemengde verwijzingen in MATCH die zorgvuldig zijn ingesteld om naar behoefte te veranderen wanneer de formule over de tabel wordt gekopieerd.
$ G5 - kolom is vergrendeld, rij zal veranderen
C $ 5: C $ 23 - rijen zijn vergrendeld, kolommen zullen veranderen
