O problema
Há um teste mestre (Teste A) e três variantes (Teste B, Teste C e Teste D). Todos os 4 testes têm as mesmas 19 questões, mas organizadas em uma ordem diferente.
A primeira tabela na tela abaixo é uma "chave de pergunta" e mostra como as perguntas no Teste A são ordenadas nos outros 3 testes. A segunda tabela é uma "chave de respostas" que mostra as respostas corretas para todas as 19 questões em todos os testes.
Acima: respostas corretas em I5: K23, fórmula obscurecida
Por exemplo, a resposta à pergunta nº 1 no Teste A é C. Esta mesma pergunta aparece como pergunta nº 4 no Teste B, então a resposta à pergunta nº 4 no Teste B também é C.
A primeira pergunta do Teste B é igual à pergunta # 13 do Teste A e a resposta para ambas é E.
O desafio
Que fórmula pode ser inserida em I5 (que é um i como em "iglu") e copiada em I5: K23 para encontrar e exibir as respostas corretas para os testes B, C e D?
Você encontrará o arquivo Excel abaixo. Deixe sua resposta como um comentário abaixo.
Dicas
-
Este problema é difícil de configurar. É muito fácil ficar confuso. Lembre-se de que os números em C5: E23 apenas indicam onde você pode encontrar uma determinada pergunta. Você ainda precisa encontrar a pergunta depois disso :)
-
Este problema pode ser resolvido com INDEX e MATCH, que são explicados neste artigo. Parte da solução envolve o bloqueio cuidadoso das referências de células. Se você tiver problemas com este tipo de referências, pratique a construção da tabuada mostrada aqui. Este problema requer referências de células cuidadosamente construídas!
-
Você pode se pegar pensando que poderia fazer isso mais rápido manualmente. Sim, para um pequeno número de perguntas. No entanto, com mais perguntas (imagine 100, 500, 1000 perguntas), a abordagem manual fica muito mais difícil. Uma boa fórmula resolverá facilmente milhares de perguntas e não cometerá erros :)
Existem duas maneiras de interpretar esse desafio. Quando elaborei o problema, estava pegando emprestado diretamente de um exemplo enviado a mim por um leitor. Essa é a abordagem mais desafiadora (Interpretação nº 2 abaixo), principalmente porque é muito fácil se confundir ao tentar entender a tabela. Abaixo, explico ambas as interpretações junto com fórmulas que podem ser usadas com cada uma.
Interpretação # 1 (incorreta)
C5: E23 mostra as mesmas questões do teste A, simplesmente reordenadas. Então, por exemplo, no Teste B …
Você pode encontrar a pergunta # 1 do Teste A na posição # 13.
Você pode encontrar a pergunta # 2 do Teste A na posição # 3.
Você pode encontrar a pergunta # 3 do Teste A na posição # 7
=INDEX($H$5:$H$23,C5)
Com as respostas do Teste A na matriz H5: H23, INDEX simplesmente recupera um valor usando o número da coluna C para o número da linha. Não fica muito mais simples do que isso. Esta não é a resposta correta para este desafio, mas é um bom exemplo de qualquer maneira.
Interpretação # 2 (correta)
A segunda interpretação é mais complicada. C5: E23 é uma chave que informa apenas onde você pode encontrar uma pergunta do teste A. Não está relatando um número de pergunta, está relatando uma espécie de índice. Então, por exemplo, no Teste B …
Você pode encontrar a pergunta # 1 do Teste A na posição # 4.
Você pode encontrar a pergunta # 2 do Teste A na posição # 19.
Você pode encontrar a pergunta # 3 do Teste A na posição # 2
Este é um problema mais complicado. Em vez de dizer que pergunta do Teste A está em uma determinada posição, a chave é dizer onde você pode encontrar a pergunta que procura. A fórmula abaixo é uma resposta correta para esse problema, pois retornará as respostas mostradas no desafio original.
=INDEX($H$5:$H$23,MATCH($G5,C$5:C$23,0))
Observe as referências mistas dentro de MATCH que foram cuidadosamente configuradas para serem alteradas conforme necessário quando a fórmula é copiada na tabela.
$ G5 - a coluna está bloqueada, a linha mudará
C $ 5: C $ 23 - as linhas estão bloqueadas, as colunas mudarão
