Problemet
Det finns ett mastertest (Test A) och tre varianter (Test B, Test C och Test D). Alla fyra testerna har samma 19 frågor, men ordnade i en annan ordning.
Den första tabellen på skärmen nedan är en "frågetangent" och visar hur frågor i test A ordnas i de andra tre testerna. Den andra tabellen är en "svarstangent" som visar rätt svar för alla 19 frågorna i alla tester.
Ovan: Rätt svar i I5: K23, formel dold
Till exempel är svaret på fråga nr 1 i test A C. Samma fråga visas som fråga nr 4 i test B, så svaret på fråga nr 4 i test B är också C.
Den första frågan i test B är densamma som fråga nr 13 i test A, och svaret på båda är E.
Utmaningen
Vilken formel kan skrivas in i I5 (det är ett i som i "igloo") och kopieras över I5: K23 för att hitta och visa rätt svar för test B, C och D?
Du hittar Excel-filen nedan. Lämna ditt svar som en kommentar nedan.
Tips
-
Detta problem är utmanande att ställa in. Det är väldigt lätt att bli förvirrad. Kom ihåg att siffrorna i C5: E23 bara berättar var du kan hitta en viss fråga. Du måste fortfarande hitta frågan efter det :)
-
Detta problem kan lösas med INDEX och MATCH, vilket förklaras i den här artikeln. En del av lösningen innebär att cellreferenser noggrant låses. Om du har problem med den här typen av referenser kan du öva på att skapa multiplikationstabellen som visas här. Detta problem kräver noggrant konstruerade cellreferenser!
-
Du kanske tänker att du skulle kunna göra detta snabbare manuellt. Ja, för ett litet antal frågor. Men med fler frågor (föreställ dig 100, 500, 1000 frågor) blir det manuella tillvägagångssättet mycket svårare. En bra formel hanterar gärna tusentals frågor, och det kommer inte att göra misstag :)
Det finns två sätt att tolka denna utmaning. När jag ställde upp problemet lånade jag direkt från ett exempel som skickades till mig av en läsare. Detta visar sig vara det mer utmanande tillvägagångssättet (tolkning 2 nedan), främst för att det är så lätt att bli förvirrad när man försöker förstå tabellen. Nedan förklarar jag båda tolkningarna tillsammans med formler som kan användas med var och en.
Tolkning nr 1 (felaktig)
C5: E23 visar samma frågor från test A, helt enkelt ombeställt. Så till exempel i test B …
Du kan hitta fråga nr 1 från Test A vid position # 13
Du kan hitta fråga nr 2 från Test A vid position # 3
Du kan hitta fråga nr 3 från Test A vid position # 7
=INDEX($H$5:$H$23,C5)
Med svaren på test A i matrisen H5: H23 hämtar INDEX helt enkelt ett värde med hjälp av numret från kolumn C för radnummer. Blir inte mycket enklare än så här. Det här är inte det rätta svaret för den här utmaningen, men det är ändå ett bra exempel.
Tolkning # 2 (rätt)
Den andra tolkningen är mer komplicerad. C5: E23 är en nyckel som bara berättar var du kan hitta en fråga från test A. Det rapporterar inte ett frågenummer, det rapporterar ett slags index. Så till exempel i test B …
Du kan hitta fråga nr 1 från test A vid position # 4
Du kan hitta fråga nr 2 från test A vid position # 19
Du kan hitta fråga nr 3 från test A vid position # 2
Detta är ett knepigare problem. Istället för att berätta vilken fråga från test A som är i en given position, är nyckeln att berätta var du kan hitta den fråga du söker. Formeln nedan är ett rätt svar på detta problem, eftersom det kommer att returnera svaren som visas i den ursprungliga utmaningen.
=INDEX($H$5:$H$23,MATCH($G5,C$5:C$23,0))
Observera de blandade referenserna i MATCH som noggrant har ställts in för att ändra efter behov när formeln kopieras över bordet.
$ G5 - kolumn är låst, rad ändras
C $ 5: C $ 23 - rader är låsta, kolumner ändras
