Проблемът
Има един главен тест (тест A) и три варианта (тест B, тест C и тест D). И четирите теста имат едни и същи 19 въпроса, но подредени в различен ред.
Първата таблица на екрана по-долу е "ключ за въпроси" и показва как са подредени въпросите в тест А в останалите 3 теста. Втората таблица е "ключ за отговор", който показва правилните отговори за всички 19 въпроса във всички тестове.
Горе: Точни отговори в I5: K23, формулата затъмнена
Например отговорът на въпрос № 1 в тест A е C. Същият въпрос се появява като въпрос № 4 в тест B, така че отговорът на въпрос № 4 в тест B също е C.
Първият въпрос в тест B е същият като въпрос № 13 в тест A, а отговорът и на двата е E.
Предизвикателството
Каква формула може да се въведе в I5 (това е i като в „igloo“) и да се копира в I5: K23, за да се намерят и покажат правилните отговори за тестове B, C и D?
Ще намерите файла на Excel по-долу. Оставете отговора си като коментар по-долу.
Съвети
-
Този проблем е труден за настройване. Много е лесно да се объркате. Не забравяйте, че цифрите в C5: E23 ви казват само къде можете да намерите даден въпрос. Все още трябва да намерите въпроса след това :)
-
Този проблем може да бъде решен с INDEX и MATCH, което е обяснено в тази статия. Част от решението включва внимателно заключване на препратки към клетки. Ако имате проблеми с този вид препратки, практикувайте изграждането на таблицата за умножение, показана тук. Този проблем изисква внимателно изградени препратки към клетки!
-
Може да се замислите, че можете да направите това по-бързо ръчно. Да, за малък брой въпроси. Но с повече въпроси (представете си 100, 500, 1000 въпроса) ръчният подход става много по-труден. Добрата формула с радост ще се справи с хиляди въпроси и няма да допусне грешки :)
Има два начина за тълкуване на това предизвикателство. Когато настройвах проблема, взех назаем директно от пример, изпратен ми от читател. Това се оказва по-предизвикателният подход (тълкуване №2 по-долу), най-вече защото е толкова лесно да се объркате, когато се опитвате да разберете таблицата. По-долу обяснявам двете тълкувания заедно с формули, които могат да се използват с всяка.
Тълкуване № 1 (неправилно)
C5: E23 показва същите въпроси от тест A, просто пренаредени. Така например, в тест Б …
Можете да намерите въпрос № 1 от тест A на позиция № 13
Можете да намерите въпрос № 2 от тест A на позиция № 3
Можете да намерите въпрос № 3 от тест A на позиция № 7
=INDEX($H$5:$H$23,C5)
С отговорите на тест A в масива H5: H23, INDEX просто извлича стойност, като използва числото от колона C за номер на реда. Не става много по-просто от това. Това не е правилният отговор за това предизвикателство, но все пак е хубав пример.
Тълкуване # 2 (правилно)
Втората интерпретация е по-сложна. C5: E23 е ключ, който ви казва само къде можете да намерите въпрос от тест А. Той не отчита номер на въпрос, а отчита сортиращ индекс. Така например, в тест Б …
Можете да намерите въпрос № 1 от тест A на позиция № 4
Можете да намерите въпрос № 2 от тест A на позиция № 19
Можете да намерите въпрос № 3 от тест A на позиция № 2
Това е по-сложен проблем. Вместо да ви каже какъв въпрос от тест А е в дадена позиция, ключът ви казва къде можете да намерите въпроса, който търсите. Формулата по-долу е един правилен отговор на този проблем, тъй като ще върне отговорите, показани в първоначалното предизвикателство.
=INDEX($H$5:$H$23,MATCH($G5,C$5:C$23,0))
Обърнете внимание на смесените препратки вътре в MATCH, които са внимателно настроени да се променят при необходимост, когато формулата се копира в таблицата.
$ G5 - колоната е заключена, редът ще се промени
C $ 5: C $ 23 - редовете са заключени, колоните ще се променят
