Problēma
Ir viens pamatpārbaudījums (A tests) un trīs varianti (B tests, C tests un D tests). Visos 4 testos ir vienādi 19 jautājumi, bet tie sakārtoti citā secībā.
Pirmā tabula zemāk redzamajā ekrānā ir "jautājuma atslēga" un parāda, kā jautājumi A testā tiek kārtoti pārējos 3 testos. Otrā tabula ir "atbilžu atslēga", kas parāda pareizās atbildes uz visiem 19 jautājumiem visos testos.
Virs: Pareizās atbildes I5: K23, formula ir aizklāta
Piemēram, atbilde uz A testa 1. jautājumu ir C. Šis pats jautājums B testā parādās kā 4. jautājums, tāpēc atbilde uz B testa 4. jautājumu ir arī C.
B testa pirmais jautājums ir tāds pats kā A testa 13. jautājums, un atbilde uz abiem ir E.
Izaicinājums
Kādu formulu var ievadīt I5 (tas ir i tāpat kā "iglu") un nokopēt I5: K23, lai atrastu un parādītu pareizās atbildes uz B, C un D testiem?
Excel failu atradīsit zemāk. Atstājiet savu atbildi kā komentāru zemāk.
Padomi
-
Šīs problēmas uzstādīšana ir sarežģīta. Apjukt ir ļoti viegli. Atcerieties, ka skaitļi C5: E23 norāda tikai to, kur jūs varat atrast doto jautājumu. Pēc tam jums joprojām ir jāatrod jautājums :)
-
Šo problēmu var atrisināt ar INDEX un MATCH, kas ir izskaidrots šajā rakstā. Daļa no risinājuma ietver rūpīgi bloķējošas šūnu atsauces. Ja jums ir problēmas ar šāda veida atsaucēm, praktizējiet šeit redzamās reizināšanas tabulas izveidi. Šī problēma prasa rūpīgi izveidotas atsauces uz šūnām!
-
Jūs varētu domāt, ka to varētu izdarīt ātrāk manuāli. Jā, par nelielu jautājumu skaitu. Tomēr, ja rodas vairāk jautājumu (iedomājieties 100, 500, 1000 jautājumus), manuālā pieeja kļūst daudz grūtāka. Laba formula ar prieku izskatīs tūkstošiem jautājumu, un tas nekļūdīsies :)
Ir divi veidi, kā interpretēt šo izaicinājumu. Uzstādot problēmu, es aizņēmos tieši no lasītāja man iesūtītā piemēra. Izrādās, ka tā ir sarežģītāka pieeja (interpretācija Nr. 2 zemāk), galvenokārt tāpēc, ka mēģināt saprast tabulu ir tik viegli sajaukt. Zemāk es izskaidroju abas interpretācijas kopā ar formulām, kuras var izmantot ar katru.
1. interpretācija (nepareiza)
C5: E23 parāda tos pašus jautājumus no A testa, vienkārši pārkārtojot tos. Piemēram, B testā…
Jūs varat atrast jautājums # 1 no Test A pozīcijā # 13
Jūs varat atrast jautājumu # 2 no Test A pozīcijā # 3
Jūs varat atrast jautājumu # 3 no Test A pozīcijā # 7
=INDEX($H$5:$H$23,C5)
Ar atbildēm uz testu A masīvā H5: H23, INDEX vienkārši iegūst vērtību, izmantojot rindas numuram C slejas numuru. Nekļūst daudz vienkāršāks par šo. Šī nav pareizā atbilde uz šo izaicinājumu, taču tik un tā tas ir jauks piemērs.
Interpretācija Nr. 2 (pareiza)
Otrā interpretācija ir sarežģītāka. C5: E23 ir atslēga, kas norāda tikai to, kur jūs varat atrast jautājumu no testa A. Tas nenorāda jautājuma numuru, bet gan par sava veida indeksu. Piemēram, B testā…
Jūs varat atrast jautājums # 1 no Test A pozīcijā # 4
Jūs varat atrast jautājumu # 2 no Test A pozīcijā # 19
Jūs varat atrast jautājumu # 3 no Test A pozīcijā # 2
Tā ir sarežģītāka problēma. Tā vietā, lai pastāstītu, kāds jautājums no A testa ir noteiktā pozīcijā, galvenais ir pateikt, kur jūs varat atrast meklēto jautājumu. Tālāk sniegtā formula ir viena pareiza atbilde uz šo problēmu, jo tā atgriezīs sākotnējā izaicinājumā norādītās atbildes.
=INDEX($H$5:$H$23,MATCH($G5,C$5:C$23,0))
Ievērojiet jauktās atsauces MATCH iekšpusē, kuras ir rūpīgi iestatītas, lai pēc vajadzības mainītos, kad formula tiek kopēta tabulā.
$ G5 - kolonna ir bloķēta, rinda mainīsies
C $ 5: C $ 23 - rindas ir bloķētas, kolonnas mainīsies
