Il problema
C'è un test principale (Test A) e tre varianti (Test B, Test C e Test D). Tutti e 4 i test hanno le stesse 19 domande, ma disposte in un ordine diverso.
La prima tabella nella schermata seguente è una "chiave di domanda" e mostra come le domande nel test A sono ordinate negli altri 3 test. La seconda tabella è una "chiave di risposta" che mostra le risposte corrette per tutte le 19 domande in tutti i test.
Sopra: risposte corrette in I5: K23, formula oscurata
Ad esempio, la risposta alla domanda n. 1 nel test A è C. Questa stessa domanda appare come domanda n. 4 nel test B, quindi anche la risposta alla domanda n. 4 nel test B è C.
La prima domanda nel test B è la stessa della domanda n. 13 nel test A e la risposta a entrambe è E.
La sfida
Quale formula può essere inserita in I5 (che è una i come in "igloo") e copiata in I5: K23 per trovare e visualizzare le risposte corrette per i test B, C e D?
Di seguito troverai il file Excel. Lascia la tua risposta come commento qui sotto.
Suggerimenti
-
Questo problema è difficile da configurare. È molto facile confondersi. Ricorda, i numeri in C5: E23 ti dicono solo dove puoi trovare una determinata domanda. Dopo devi ancora trovare la domanda :)
-
Questo problema può essere risolto con INDICE e CONFRONTA, spiegati in questo articolo. Parte della soluzione prevede il blocco accurato dei riferimenti di cella. Se hai problemi con questo tipo di riferimenti, esercitati a costruire la tabella di moltiplicazione mostrata qui. Questo problema richiede riferimenti di cella costruiti con cura!
-
Potresti trovarti a pensare di poterlo fare manualmente più velocemente. Sì, per un numero limitato di domande. Tuttavia, con più domande (immagina 100, 500, 1000 domande) l'approccio manuale diventa molto più difficile. Una buona formula gestirà felicemente migliaia di domande e non commetterà errori :)
Ci sono due modi per interpretare questa sfida. Quando ho impostato il problema, stavo prendendo in prestito direttamente da un esempio inviato da un lettore. Questo risulta essere l'approccio più impegnativo (Interpretazione # 2 sotto), soprattutto perché è così facile confondersi quando si cerca di capire la tabella. Di seguito, spiego entrambe le interpretazioni insieme alle formule che possono essere utilizzate con ciascuna.
Interpretazione # 1 (errata)
C5: E23 mostra le stesse domande del test A, semplicemente riordinate. Quindi, ad esempio, nel test B …
Puoi trovare la domanda # 1 dal Test A alla posizione # 13
Puoi trovare la domanda # 2 dal Test A alla posizione # 3
Puoi trovare la domanda # 3 dal Test A alla posizione # 7
=INDEX($H$5:$H$23,C5)
Con le risposte al test A nell'array H5: H23, INDEX recupera semplicemente un valore utilizzando il numero dalla colonna C per il numero di riga. Non è molto più semplice di così. Questa non è la risposta corretta per questa sfida, ma è comunque un bell'esempio.
Interpretazione # 2 (corretta)
La seconda interpretazione è più complicata. C5: E23 è una chiave che ti dice solo dove puoi trovare una domanda dal test A. Non sta riportando un numero di domanda, sta riportando una sorta di indice. Quindi, ad esempio, nel test B …
Puoi trovare la domanda # 1 dal Test A alla posizione # 4
Puoi trovare la domanda # 2 dal Test A alla posizione # 19
Puoi trovare la domanda # 3 dal Test A alla posizione # 2
Questo è un problema più complicato. Invece di dirti quale domanda del test A si trova in una data posizione, la chiave è dirti dove puoi trovare la domanda che stai cercando. La formula seguente è una risposta corretta a questo problema, poiché restituirà le risposte mostrate nella sfida originale.
=INDEX($H$5:$H$23,MATCH($G5,C$5:C$23,0))
Nota i riferimenti misti all'interno di MATCH che sono stati accuratamente impostati per cambiare secondo necessità quando la formula viene copiata nella tabella.
$ G5 - la colonna è bloccata, la riga cambierà
C $ 5: C $ 23 - le righe sono bloccate, le colonne cambieranno
