Formelutfordring - bygg svarnøkkel for tester Puslespill

Innholdsfortegnelse

Problemet

Det er en mastertest (Test A) og tre varianter (Test B, Test C og Test D). Alle de 4 testene har de samme 19 spørsmålene, men ordnet i en annen rekkefølge.

Den første tabellen på skjermen nedenfor er en "spørsmålstast" og viser hvordan spørsmål i test A er bestilt i de andre 3 testene. Den andre tabellen er en "svarnøkkel" som viser de riktige svarene for alle 19 spørsmålene i alle testene.

Over: Riktige svar i I5: K23, formel skjult

For eksempel er svaret på spørsmål nr. 1 i test A C. Det samme spørsmålet vises som spørsmål nr. 4 i test B, så svaret på spørsmål nr. 4 i test B er også C.

Det første spørsmålet i test B er det samme som spørsmål nr. 13 i test A, og svaret på begge er E.

Utfordringen

Hvilken formel kan legges inn i I5 (det er et i som i "igloo") og kopieres over I5: K23 for å finne og vise de riktige svarene for test B, C og D?

Du finner Excel-filen nedenfor. Legg igjen svaret ditt som en kommentar nedenfor.

Tips

  1. Dette problemet er utfordrende å sette opp. Det er veldig lett å bli forvirret. Husk at tallene i C5: E23 bare forteller deg hvor du kan finne et gitt spørsmål. Du må fremdeles finne spørsmålet etter det :)

  2. Dette problemet kan løses med INDEX og MATCH, som er forklart i denne artikkelen. En del av løsningen innebærer nøye å låse cellereferanser. Hvis du har problemer med denne typen referanser, kan du øve på å bygge multiplikasjonstabellen vist her. Dette problemet krever nøye konstruerte cellereferanser!

  3. Du kan tenke at du kan gjøre dette raskere manuelt. Ja, for et lite antall spørsmål. Men med flere spørsmål (forestill deg 100, 500, 1000 spørsmål) blir den manuelle tilnærmingen mye vanskeligere. En god formel vil gjerne håndtere tusenvis av spørsmål, og det vil ikke gjøre feil :)

Svar (klikk for å utvide)

Det er to måter å tolke denne utfordringen på. Da jeg la opp problemet, lånte jeg direkte fra et eksempel som ble sendt til meg av en leser. Dette viser seg å være den mer utfordrende tilnærmingen (Tolkning nr. 2 nedenfor), hovedsakelig fordi det er så lett å bli forvirret når du prøver å forstå tabellen. Nedenfor forklarer jeg begge tolkningene sammen med formler som kan brukes med hver.

Tolkning nr. 1 (feil)

C5: E23 viser de samme spørsmålene fra test A, bare omorganisert. Så for eksempel i test B …

Du kan finne spørsmål nr. 1 fra test A på posisjon # 13
Du kan finne spørsmål nr. 2 fra test A på posisjon nr. 3
Du kan finne spørsmål nr. 3 fra test A på posisjon nr. 7

=INDEX($H$5:$H$23,C5)

Med svarene på test A i matrisen H5: H23, henter INDEX ganske enkelt en verdi ved hjelp av tallet fra kolonne C for radnummer. Blir ikke mye enklere enn dette. Dette er ikke det riktige svaret for denne utfordringen, men det er uansett et fint eksempel.

Tolkning nr. 2 (riktig)

Den andre tolkningen er mer komplisert. C5: E23 er en nøkkel som bare forteller deg hvor du kan finne et spørsmål fra test A. Det rapporterer ikke et spørsmålsnummer, det rapporterer en slags indeks. Så for eksempel i test B …

Du kan finne spørsmål nr. 1 fra test A på posisjon nr. 4
Du kan finne spørsmål nr. 2 fra test A på posisjon nr. 19
Du kan finne spørsmål nr. 3 fra test A på posisjon nr. 2

Dette er et vanskeligere problem. I stedet for å fortelle deg hvilket spørsmål fra test A er i en gitt posisjon, er nøkkelen å fortelle deg hvor du kan finne spørsmålet du søker. Formelen nedenfor er ett riktig svar på dette problemet, siden det vil returnere svarene vist i den opprinnelige utfordringen.

=INDEX($H$5:$H$23,MATCH($G5,C$5:C$23,0))

Legg merke til de blandede referansene i MATCH som er nøye konfigurert for å endre etter behov når formelen kopieres over bordet.

$ G5 - kolonne er låst, rad vil endre
C $ 5: C $ 23 - rad er låst, kolonner vil endres

Interessante artikler...