Формула завдання - побудуйте ключ відповіді для тестів - Головоломка

Зміст

Проблема

Існує один майстер-тест (тест А) та три варіанти (тест В, тест С та тест D). Усі 4 тести мають однакові 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 ви знайдете нижче. Залиште свою відповідь як коментар нижче.

Підказки

  1. Ця проблема є складною для встановлення. Заплутатися дуже легко. Пам'ятайте, цифри в C5: E23 лише підказують, де ви можете знайти задане питання. Ви все ще повинні знайти питання після цього :)

  2. Цю проблему можна вирішити за допомогою INDEX та MATCH, що пояснюється в цій статті. Частина рішення передбачає ретельне блокування посилань на комірки. Якщо у вас виникли проблеми з подібними посиланнями, потренуйтеся побудувати таблицю множення, показану тут. Ця проблема вимагає ретельно побудованих посилань на клітинки!

  3. Можливо, вам здається, що ви можете зробити це швидше вручну. Так, для невеликої кількості питань. Однак із більшою кількістю питань (уявіть собі 100, 500, 1000 питань) ручний підхід стає набагато складнішим. Хороша формула з радістю розгляне тисячі питань і не припуститься помилок :)

Відповідь (натисніть, щоб розгорнути)

Існує два шляхи інтерпретації цього виклику. Коли я встановлював проблему, я запозичував безпосередньо приклад, надісланий мені читачем. Це виявляється більш складним підходом (тлумачення №2 нижче), здебільшого тому, що так легко заплутатися, намагаючись зрозуміти таблицю. Нижче я пояснюю обидві інтерпретації разом із формулами, які можна використовувати з кожним.

Інтерпретація №1 (неправильна)

C5: E23 показує ті самі запитання з тесту A, просто переупорядковані. Так, наприклад, у тесті B …

Ви можете знайти запитання №1 із тесту А у позиції №13
Ви можете знайти питання №2
із тесту А у позиції №3

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

Відповідаючи на тест A у масиві H5: H23, INDEX просто отримує значення, використовуючи число зі стовпця C для номера рядка. Набагато простіше, ніж це. Це не правильна відповідь на цей виклик, але це все одно приємний приклад.

Інтерпретація №2 (правильна)

Друге тлумачення є більш складним. C5: E23 - це ключ, який повідомляє вам лише те, де ви можете знайти запитання з тесту А. Він не повідомляє номер запитання, він повідомляє сортовий індекс. Так, наприклад, у тесті B …

Ви можете знайти питання # 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 - рядки заблоковані, стовпці зміняться

Цікаві статті...