Formula challenge - zgradite ključ za odgovore za teste - Uganka

Kazalo

Težava

Obstaja en glavni test (test A) in tri različice (test B, test C in test D). Vsi 4 testi imajo enakih 19 vprašanj, vendar razporejenih v drugačnem vrstnem redu.

Prva tabela na spodnjem zaslonu je "ključ vprašanja" in prikazuje, kako so vprašanja v preizkusu A razporejena v ostalih 3 preizkusih. Druga tabela je "ključ za odgovor", ki prikazuje pravilne odgovore na vseh 19 vprašanj v vseh testih.

Zgoraj: Pravilni odgovori v I5: K23, formula zakrita

Na primer, odgovor na vprašanje št. 1 v preizkusu A je C. To isto vprašanje se pojavlja kot vprašanje št. 4 v preizkusu B, zato je odgovor na vprašanje št. 4 v preizkusu B tudi C.

Prvo vprašanje v preizkusu B je enako vprašanju št. 13 v preizkusu A, odgovor na oba pa je E.

Izziv

Katero formulo lahko vnesete v I5 (to je i kot pri "iglu") in kopirate v I5: K23, da poiščete in prikažete pravilne odgovore za teste B, C in D?

Datoteko Excel najdete spodaj. Odgovor pustite kot komentar spodaj.

Namigi

  1. Ta problem je težko izvesti. Zelo enostavno se zmedete. Ne pozabite, da številke v C5: E23 povedo le, kje lahko najdete določeno vprašanje. Po tem morate še najti vprašanje :)

  2. To težavo je mogoče rešiti z INDEX in MATCH, kar je razloženo v tem članku. Del rešitve vključuje skrbno zaklepanje referenc celic. Če imate težave s tovrstnimi referencami, vadite izdelavo tabele množenja, prikazano tukaj. Ta težava zahteva skrbno izdelane sklice na celice!

  3. Morda se vam zdi, da bi to lahko storili hitreje ročno. Da, za majhno število vprašanj. Vendar pa je z več vprašanji (predstavljajte si 100, 500, 1000 vprašanj) ročni pristop veliko težji. Dobra formula bo z veseljem obravnavala tisoče vprašanj in ne bo naredila napak :)

Odgovor (kliknite za razširitev)

Ta izziv lahko razložimo na dva načina. Ko sem nastavil težavo, sem si izposojal neposredno iz primera, ki mi ga je poslal bralec. Izkazalo se je, da je to bolj zahteven pristop (razlaga št. 2 spodaj), predvsem zato, ker se tako enostavno zmede, ko poskušate razumeti tabelo. Spodaj pojasnjujem obe interpretaciji skupaj s formulami, ki jih je mogoče uporabiti z vsako.

Razlaga št. 1 (napačna)

C5: E23 prikazuje ista vprašanja iz testa A, preprosto preurejena. Tako na primer v preizkusu B …

Vprašanje št. 1 iz testa A najdete na položaju št. 13
Vprašanje št. 2 iz testa A
najdete na položaju št. 3 vprašanje št. 3 iz testa A najdete na položaju št. 7

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

Z odgovori na preizkus A v polju H5: H23 INDEX preprosto pridobi vrednost z uporabo številke iz stolpca C za številko vrstice. Ne postane veliko preprostejše od tega. To ni pravi odgovor za ta izziv, je pa vseeno lep primer.

Razlaga št. 2 (pravilno)

Druga razlaga je bolj zapletena. C5: E23 je ključ, ki vam pove samo, kje lahko najdete vprašanje iz testa A. Ne sporoča številke vprašanja, ampak poroča o indeksu vrst. Tako na primer v preizkusu B …

Vprašanje št. 1 iz testa A najdete na položaju št. 4
Vprašanje št. 2 iz testa A
najdete na položaju št. 19 Vprašanje št. 3 iz testa A najdete na položaju št. 2

To je bolj zapleten problem. Namesto da bi vam povedal, kakšno vprašanje iz testa A je v določenem položaju, vam ključ pove, kje lahko najdete vprašanje, ki ga iščete. Spodnja formula je en pravilen odgovor na to težavo, saj bo vrnila odgovore, prikazane v prvotnem izzivu.

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

Upoštevajte mešane reference znotraj MATCH, ki so bile skrbno nastavljene tako, da se po potrebi spremenijo, ko se formula kopira po tabeli.

$ G5 - stolpec je zaklenjen, vrstica se spremeni
C $ 5: C $ 23 - vrstice so zaklenjene, stolpci se bodo spremenili

Zanimive Članki...