Problém
Existuje jeden hlavný test (Test A) a tri varianty (Test B, Test C a Test D). Všetky 4 testy majú rovnakých 19 otázok, ale sú zoradené v inom poradí.
Prvá tabuľka na obrazovke nižšie je „kľúčom otázky“ a ukazuje, ako sú otázky v teste A zoradené v ďalších 3 testoch. Druhá tabuľka predstavuje „kľúč odpovede“, ktorý zobrazuje správne odpovede na všetkých 19 otázok vo všetkých testoch.
Hore: Správne odpovede v I5: K23, zakrytý vzorec
Napríklad odpoveď na otázku č. 1 v teste A je C. Táto rovnaká otázka sa javí ako otázka č. 4 v teste B, takže odpoveď na otázku č. 4 v teste B je tiež C.
Prvá otázka v teste B je rovnaká ako otázka č. 13 v teste A a odpoveď na obe otázky je E.
Výzva
Aký vzorec je možné zadať v I5 (to je i ako v „iglu“) a skopírovať ich v I5: K23 na nájdenie a zobrazenie správnych odpovedí na testy B, C a D?
Súbor Excel nájdete nižšie. Nižšie nechajte svoju odpoveď ako komentár.
Rady
-
Tento problém je náročné postaviť. Je veľmi ľahké sa nechať zmiasť. Pamätajte, že čísla v C5: E23 vám povedia iba to, kde nájdete danú otázku. Po tomto si ešte musíte nájsť otázku :)
-
Tento problém je možné vyriešiť pomocou indexov INDEX a MATCH, ktoré sú vysvetlené v tomto článku. Súčasťou riešenia je starostlivé uzamknutie odkazov na bunky. Ak máte problémy s týmto druhom odkazov, vyskúšajte si zostavenie tabuľky znásobenia, ktorá je zobrazená tu. Tento problém si vyžaduje starostlivo zostavené odkazy na bunky!
-
Možno zistíte, že si myslíte, že by ste to mohli urobiť rýchlejšie manuálne. Áno, pre malý počet otázok. S ďalšími otázkami (predstavte si 100, 500, 1 000 otázok) je však manuálny prístup oveľa ťažší. Dobrý vzorec s radosťou zvládne tisíce otázok a nebude robiť chyby :)
Existujú dva spôsoby, ako interpretovať túto výzvu. Keď som nastavoval problém, požičiaval som si priamo z príkladu, ktorý mi poslal čitateľ. Ukázalo sa, že to je náročnejší prístup (Výklad č. 2 uvedený nižšie), hlavne preto, že pri pokuse o porozumenie tabuľky je ľahké sa nechať zmiasť. Ďalej vysvetľujem obidve interpretácie a vzorce, ktoré je možné použiť pri každej z nich.
Výklad č. 1 (nesprávny)
C5: E23 zobrazuje rovnaké otázky z testu A, jednoducho usporiadané. Napríklad v teste B…
Nájdete otázka # 1 z testu A na pozícii # 13
nájdete otázku # 2 z testu v pozícii # 3
môžete nájsť otázku # 3 z testu v pozícii # 7
=INDEX($H$5:$H$23,C5)
S odpoveďami na test A v poli H5: H23, INDEX jednoducho načíta hodnotu pomocou čísla zo stĺpca C pre číslo riadku. Už to nie je oveľa jednoduchšie. Toto nie je správna odpoveď na túto výzvu, ale aj tak je to pekný príklad.
Výklad č. 2 (správny)
Druhá interpretácia je komplikovanejšia. C5: E23 je kľúč, ktorý vám povie iba tam, kde nájdete otázku z testu A. Neuvádza číslo otázky, ale hlási index druhov. Napríklad v teste B…
Otázku # 1 z Testu A nájdete na pozícii č. 4
Otázku # 2 nájdete z Testu A na pozícii č. 19.
Otázku # 3 nájdete z Testu A na pozícii č. 2.
Toto je zložitejší problém. Namiesto toho, aby sme vám povedali, ktorá otázka z testu A je v danej pozícii, kľúčom je, aby ste povedali, kde nájdete otázku, ktorú hľadáte. Vzorec uvedený nižšie je jednou správnou odpoveďou na tento problém, pretože vráti odpovede uvedené v pôvodnej výzve.
=INDEX($H$5:$H$23,MATCH($G5,C$5:C$23,0))
Všimnite si zmiešané odkazy v MATCH, ktoré boli starostlivo nastavené tak, aby sa podľa potreby zmenili pri kopírovaní vzorca cez tabuľku.
$ G5 - stĺpec je uzamknutý, riadok sa zmení
C $ 5: C $ 23 - riadky sú uzamknuté, stĺpce sa zmenia
