Эта проблема
Существует один основной тест (тест A) и три варианта (тест B, тест C и тест D). Все 4 теста содержат одинаковые 19 вопросов, но расположены в разном порядке.
Первая таблица на экране ниже представляет собой «ключ вопроса» и показывает, как вопросы в тесте A упорядочены в других трех тестах. Вторая таблица - это «ключ ответа», который показывает правильные ответы на все 19 вопросов во всех тестах.
Вверху: Правильные ответы в I5: K23, формула скрыта.
Например, ответ на вопрос № 1 в тесте A - C. Этот же вопрос появляется как вопрос № 4 в тесте B, поэтому ответ на вопрос № 4 в тесте B также является C.
Первый вопрос в тесте B такой же, как вопрос № 13 в тесте A, и ответ на оба вопроса - E.
Соревнование
Какую формулу можно ввести в I5 (это i, как в «иглу») и скопировать в I5: K23, чтобы найти и отобразить правильные ответы для тестов B, C и D?
Вы найдете файл Excel ниже. Оставьте свой ответ в комментариях ниже.
Подсказки
-
Эту проблему сложно решить. Запутаться очень легко. Помните, что числа в C5: E23 говорят вам только, где вы можете найти заданный вопрос. После этого вам еще предстоит найти вопрос :)
-
Эту проблему можно решить с помощью ИНДЕКСА и ПОИСКПОЗ, которые описаны в этой статье. Часть решения включает в себя тщательную блокировку ссылок на ячейки. Если у вас возникли проблемы с подобными ссылками, попрактикуйтесь в построении таблицы умножения, показанной здесь. Эта проблема требует тщательно сконструированных ссылок на ячейки!
-
Вы можете подумать, что можете сделать это быстрее вручную. Да, по небольшому количеству вопросов. Однако с большим количеством вопросов (представьте, 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 - это ключ, который сообщает вам только, где вы можете найти вопрос из теста A. Он не сообщает номер вопроса, он сообщает своего рода указатель. Так, например, в Тесте Б…
Вы можете найти вопрос №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 - строки заблокированы, столбцы изменятся
