Problemet
Der er en mastertest (Test A) og tre varianter (Test B, Test C og Test D). Alle 4 test har de samme 19 spørgsmål, men arrangeret i en anden rækkefølge.
Den første tabel på nedenstående skærm er en "spørgsmålstast" og viser, hvordan spørgsmål i test A er ordnet i de andre 3 test. Den anden tabel er en "svarnøgle", der viser de korrekte svar på alle 19 spørgsmål i alle tests.
Ovenfor: Korrekte svar i I5: K23, formel tilsløret
For eksempel er svaret på spørgsmål nr. 1 i test A C. Det samme spørgsmål vises som spørgsmål nr. 4 i test B, så svaret på spørgsmål nr. 4 i test B er også C.
Det første spørgsmål i test B er det samme som spørgsmål nr. 13 i test A, og svaret på begge er E.
Udfordringen
Hvilken formel kan indtastes i I5 (det er et i som i "igloo") og kopieres over I5: K23 for at finde og vise de korrekte svar til test B, C og D?
Du finder Excel-filen nedenfor. Efterlad dit svar som en kommentar nedenfor.
Tips
-
Dette problem er udfordrende at oprette. Det er meget let at blive forvirret. Husk, tallene i C5: E23 fortæller dig kun, hvor du kan finde et givet spørgsmål. Du skal stadig finde spørgsmålet efter det :)
-
Dette problem kan løses med INDEX og MATCH, som forklares i denne artikel. En del af løsningen involverer omhyggelig låsning af cellereferencer. Hvis du har problemer med denne type referencer, kan du øve dig med at opbygge multiplikationstabellen vist her. Dette problem kræver omhyggeligt konstruerede cellereferencer!
-
Du kan måske finde dig selv i at tænke, at du kunne gøre dette hurtigere manuelt. Ja, for et lille antal spørgsmål. Men med flere spørgsmål (forestil dig 100, 500, 1000 spørgsmål) bliver den manuelle tilgang meget sværere. En god formel vil med glæde håndtere tusindvis af spørgsmål, og den laver ikke fejl :)
Der er to måder at fortolke denne udfordring på. Da jeg oprettede problemet, lånte jeg direkte fra et eksempel, der blev sendt til mig af en læser. Dette viser sig at være den mere udfordrende tilgang (fortolkning nr. 2 nedenfor), hovedsagelig fordi det er så let at blive forvirret, når man prøver at forstå tabellen. Nedenfor forklarer jeg begge fortolkninger sammen med formler, der kan bruges med hver.
Fortolkning nr. 1 (forkert)
C5: E23 viser de samme spørgsmål fra test A, simpelthen genbestilt. Så for eksempel i test B …
Du kan finde spørgsmål nr. 1 fra test A på position # 13
Du kan finde spørgsmål nr. 2 fra test A på position # 3
Du kan finde spørgsmål nr. 3 fra test A på position # 7
=INDEX($H$5:$H$23,C5)
Med svarene på test A i matrixen H5: H23 henter INDEX simpelthen en værdi ved hjælp af tallet fra kolonne C for række nummer. Bliver ikke meget enklere end dette. Dette er ikke det rigtige svar på denne udfordring, men det er alligevel et godt eksempel.
Fortolkning nr. 2 (korrekt)
Den anden fortolkning er mere kompliceret. C5: E23 er en nøgle, der kun fortæller dig, hvor du kan finde et spørgsmål fra test A. Det rapporterer ikke et spørgsmålsnummer, det rapporterer et slags indeks. Så for eksempel i test B …
Du kan finde spørgsmål nr. 1 fra test A på position nr. 4
Du kan finde spørgsmål nr. 2 fra test A på position nr. 19
Du kan finde spørgsmål nr. 3 fra test A på position nr. 2
Dette er et vanskeligere problem. I stedet for at fortælle dig, hvilket spørgsmål fra test A er i en given position, fortæller nøglen dig, hvor du kan finde det spørgsmål, du søger. Formlen nedenfor er et korrekt svar på dette problem, da det returnerer svarene vist i den oprindelige udfordring.
=INDEX($H$5:$H$23,MATCH($G5,C$5:C$23,0))
Bemærk de blandede referencer inde i MATCH, som er omhyggeligt indstillet til at ændre efter behov, når formlen kopieres over bordet.
$ G5 - kolonne er låst, række ændres
C $ 5: C $ 23 - rækker er låst, kolonner ændres
