Masalah
Terdapat satu ujian utama (Ujian A), dan tiga varian (Ujian B, Uji C, dan Uji D). Semua 4 ujian mempunyai 19 soalan yang sama, tetapi disusun mengikut urutan yang berbeza.
Jadual pertama di skrin di bawah adalah "kunci soalan" dan menunjukkan bagaimana soalan dalam Ujian A disusun dalam 3 ujian yang lain. Jadual kedua adalah "kunci jawapan" yang menunjukkan jawapan yang betul untuk semua 19 soalan dalam semua ujian.
Di atas: Jawapan yang betul dalam I5: K23, formula dikaburkan
Sebagai contoh, jawapan untuk soalan # 1 dalam Ujian A adalah C. Soalan yang sama muncul seperti soalan # 4 dalam Ujian B, jadi jawapan untuk soalan # 4 dalam Ujian B juga C.
Soalan pertama dalam Ujian B adalah sama dengan soalan # 13 dalam Ujian A, dan jawapan untuk kedua-duanya adalah E.
Cabaran
Formula apa yang dapat dimasukkan di I5 (itu i seperti di "igloo") dan disalin di I5: K23 untuk mencari dan memaparkan jawapan yang betul untuk Ujian B, C, dan D?
Anda akan menemui fail Excel di bawah. Tinggalkan jawapan anda sebagai komen di bawah.
Petunjuk
-
Masalah ini sukar untuk diselesaikan. Sangat mudah untuk keliru. Ingat, nombor di C5: E23 hanya memberitahu anda di mana anda dapat mencari soalan yang diberikan. Anda masih perlu mencari soalan selepas itu :)
-
Masalah ini dapat diselesaikan dengan INDEX dan MATCH, yang dijelaskan dalam artikel ini. Sebahagian daripada penyelesaian melibatkan mengunci rujukan sel dengan berhati-hati. Sekiranya anda menghadapi masalah dengan rujukan seperti ini, praktikkan membina jadual pendaraban yang ditunjukkan di sini. Masalah ini memerlukan rujukan sel yang dibina dengan teliti!
-
Anda mungkin berfikir bahawa anda boleh melakukan ini dengan lebih pantas secara manual. Ya, untuk sebilangan kecil soalan. Walau bagaimanapun, dengan lebih banyak soalan (bayangkan 100, 500, 1000 soalan) pendekatan manual menjadi lebih sukar. Formula yang baik dengan senang hati akan menangani ribuan soalan, dan tidak akan membuat kesilapan :)
Terdapat dua cara untuk mentafsirkan cabaran ini. Semasa saya menyelesaikan masalah, saya meminjam terus dari contoh yang dihantar oleh pembaca kepada saya. Ini ternyata merupakan pendekatan yang lebih mencabar (Tafsiran # 2 di bawah), terutamanya kerana sangat mudah untuk keliru ketika cuba memahami jadual. Di bawah ini, saya menerangkan kedua-dua tafsiran bersama dengan formula yang boleh digunakan untuk masing-masing.
Tafsiran # 1 (tidak betul)
C5: E23 menunjukkan soalan yang sama dari ujian A, hanya disusun semula. Jadi, sebagai contoh, dalam Ujian B…
Anda boleh cari soalan # 1 dari Test A di kedudukan # 13
anda boleh cari soalan # 2 dari Test A di kedudukan # 3
Anda boleh cari soalan # 3 dari Test A di kedudukan # 7
=INDEX($H$5:$H$23,C5)
Dengan jawapan untuk Uji A dalam larik H5: H23, INDEX hanya memperoleh nilai menggunakan nombor dari lajur C untuk nombor baris. Tidak menjadi lebih mudah daripada ini. Ini bukan jawapan yang tepat untuk cabaran ini, tetapi tetap merupakan contoh yang bagus.
Tafsiran # 2 (betul)
Tafsiran kedua lebih rumit. C5: E23 adalah kunci yang memberitahu anda hanya di mana anda dapat mencari soalan dari ujian A. Ia tidak melaporkan nombor soalan, melapor jenis indeks. Jadi, sebagai contoh, dalam Ujian B…
Anda dapat mencari soalan # 1 dari Uji A di kedudukan # 4
Anda dapat mencari soalan # 2 dari Uji A di posisi # 19
Anda dapat mencari soalan # 3 dari Uji A di posisi # 2
Ini adalah masalah yang lebih sukar. Daripada memberitahu anda apa soalan dari Ujian A dalam kedudukan tertentu, kuncinya adalah memberitahu anda di mana anda dapat mencari soalan yang anda cari. Rumus di bawah adalah satu jawapan yang tepat untuk masalah ini, kerana ia akan mengembalikan jawapan yang ditunjukkan dalam cabaran asal.
=INDEX($H$5:$H$23,MATCH($G5,C$5:C$23,0))
Perhatikan rujukan campuran di dalam MATCH yang telah disusun dengan teliti untuk berubah sesuai keperluan ketika formula disalin di seluruh meja.
$ G5 - lajur dikunci, baris akan berubah
C $ 5: C $ 23 - baris dikunci, lajur akan berubah
