Výzva vzorce - sestavte klíč odpovědi pro testy Hádanka

Problém

K dispozici je jeden hlavní test (Test A) a tři varianty (Test B, Test C a Test D). Všechny 4 testy mají stejných 19 otázek, ale jsou seřazeny v jiném pořadí.

První tabulka na obrazovce níže je „klíč otázky“ a ukazuje, jak jsou otázky v testu A seřazeny v ostatních 3 testech. Druhá tabulka je „klíč odpovědi“, který zobrazuje správné odpovědi na všech 19 otázek ve všech testech.

Nahoře: Správné odpovědi v I5: K23, zakrytý vzorec

Například odpověď na otázku # 1 v Testu A je C. Tato stejná otázka se objeví jako otázka # 4 v Testu B, takže odpověď na otázku # 4 v Testu B je také C.

První otázka v testu B je stejná jako otázka č. 13 v testu A a odpověď na obě otázky je E.

Výzva

Jaký vzorec lze zadat v I5 (to je i jako v „iglú“) a zkopírovat přes I5: K23 k nalezení a zobrazení správných odpovědí pro testy B, C a D?

Níže naleznete soubor Excel. Níže zanechejte svou odpověď jako komentář.

Rady

  1. Tento problém je náročné nastavit. Je velmi snadné se zmást. Nezapomeňte, že čísla v C5: E23 vám řeknou pouze to, kde najdete danou otázku. Poté budete muset najít otázku :)

  2. Tento problém lze vyřešit pomocí INDEX a MATCH, které jsou vysvětleny v tomto článku. Součástí řešení je pečlivé zamykání odkazů na buňky. Pokud máte potíže s tímto druhem odkazů, procvičte si sestavení tabulky znásobení zobrazené zde. Tento problém vyžaduje pečlivě vytvořené odkazy na buňky!

  3. Možná zjistíte, že si myslíte, že byste to mohli udělat rychleji ručně. Ano, pro malý počet otázek. S více otázkami (představte si 100, 500, 1000 otázek) je však manuální přístup mnohem těžší. Dobrý vzorec šťastně zvládne tisíce otázek a nedělá chyby :)

Odpověď (kliknutím rozbalíte)

Tuto výzvu lze interpretovat dvěma způsoby. Když jsem nastavil problém, půjčil jsem si přímo z příkladu, který mi poslal čtenář. Ukázalo se, že jde o náročnější přístup (Výklad č. 2 níže), hlavně proto, že při pokusu porozumět tabulce je tak snadné se zmást. Níže vysvětluji obě interpretace spolu s formulemi, které lze u každé použít.

Výklad č. 1 (nesprávný)

C5: E23 ukazuje stejné otázky z testu A, jednoduše přeuspořádané. Například v testu B…

Otázku č. 1 z Testu A najdete na pozici č. 13
Otázku č. 2 najdete z Testu A na pozici č. 3
Otázku č. 3 najdete z Testu A na pozici č. 7

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

S odpověďmi na Test A v poli H5: H23 INDEX jednoduše načte hodnotu pomocí čísla ze sloupce C pro číslo řádku. Už to není o moc jednodušší. To není správná odpověď na tuto výzvu, ale stejně je to pěkný příklad.

Výklad č. 2 (správný)

Druhá interpretace je složitější. C5: E23 je klíč, který vám řekne pouze to, kde najdete otázku z testu A. Nehlásí číslo otázky, hlásí index druhů. Například v testu B…

Otázku č. 1 z Testu A najdete na pozici č. 4
Otázku č. 2 najdete z Testu A na pozici č. 19.
Otázku č. 3 najdete z Testu A na pozici č. 2.

To je složitější problém. Místo toho, abychom vám řekli, která otázka z testu A je v dané pozici, klíčem je, abyste řekli, kde můžete najít otázku, kterou hledáte. Níže uvedený vzorec je jednou správnou odpovědí na tento problém, protože vrátí odpovědi uvedené v původní výzvě.

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

Všimněte si smíšených odkazů uvnitř MATCH, které byly pečlivě nastaveny tak, aby se podle potřeby změnily při kopírování vzorce přes tabulku.

$ G5 - sloupec je uzamčen, řádek se změní
C $ 5: C $ 23 - řádky jsou uzamčeny, sloupce se změní

Zajímavé články...