Masalah
Ada satu tes master (Tes A), dan tiga varian (Tes B, Tes C, dan Tes D). Keempat tes memiliki 19 pertanyaan yang sama, tetapi disusun dalam urutan yang berbeda.
Tabel pertama pada layar di bawah ini adalah "kunci pertanyaan" dan menunjukkan bagaimana pertanyaan dalam Tes A diurutkan dalam 3 tes lainnya. Tabel kedua adalah "kunci jawaban" yang menunjukkan jawaban yang benar untuk semua 19 pertanyaan di semua tes.
Atas: Jawaban yang benar di I5: K23, rumus dikaburkan
Misalnya, jawaban soal # 1 di Tes A adalah C. Pertanyaan yang sama ini muncul sebagai pertanyaan # 4 di Tes B, jadi jawaban soal # 4 di Tes B juga C.
Pertanyaan pertama di Tes B sama dengan pertanyaan # 13 di Tes A, dan jawaban keduanya adalah E.
Tantangan
Rumus apa yang dapat dimasukkan di I5 (itu adalah i seperti di "igloo") dan disalin di I5: K23 untuk menemukan dan menampilkan jawaban yang benar untuk Tes B, C, dan D?
Anda akan menemukan file Excel di bawah ini. Tinggalkan jawaban Anda sebagai komentar di bawah.
Petunjuk
-
Masalah ini sulit diatur. Sangat mudah untuk menjadi bingung. Ingat, angka di C5: E23 hanya memberi tahu Anda di mana Anda dapat menemukan pertanyaan yang diberikan. Anda masih harus menemukan pertanyaannya setelah itu :)
-
Masalah ini dapat diselesaikan dengan INDEX dan MATCH, yang dijelaskan di artikel ini. Bagian dari solusi melibatkan penguncian referensi sel dengan hati-hati. Jika Anda mengalami masalah dengan referensi semacam ini, berlatihlah membuat tabel perkalian yang ditunjukkan di sini. Masalah ini membutuhkan referensi sel yang dibangun dengan hati-hati!
-
Anda mungkin berpikir Anda bisa melakukan ini lebih cepat secara manual. Ya, untuk sejumlah kecil pertanyaan. Namun, dengan lebih banyak pertanyaan (bayangkan 100, 500, 1000 pertanyaan) pendekatan manual menjadi jauh lebih sulit. Formula yang baik akan dengan senang hati menangani ribuan pertanyaan, dan tidak akan membuat kesalahan :)
Ada dua cara untuk menafsirkan tantangan ini. Ketika saya membuat masalah, saya meminjam langsung dari contoh yang dikirimkan kepada saya oleh seorang pembaca. Ini ternyata merupakan pendekatan yang lebih menantang (Interpretasi # 2 di bawah), sebagian besar karena sangat mudah untuk menjadi bingung ketika mencoba untuk memahami tabel. Di bawah ini, saya menjelaskan kedua interpretasi beserta rumus yang dapat digunakan dengan masing-masing.
Interpretasi # 1 (salah)
C5: E23 menunjukkan pertanyaan yang sama dari tes A, hanya disusun ulang. Jadi, misalnya, di Tes B…
Anda bisa menemukan pertanyaan # 1 dari Tes A di posisi # 13
Anda bisa menemukan pertanyaan # 2 dari Tes A di posisi # 3
Anda bisa menemukan pertanyaan # 3 dari Tes A di posisi # 7
=INDEX($H$5:$H$23,C5)
Dengan jawaban untuk Tes A dalam larik H5: H23, INDEX hanya mengambil nilai menggunakan nomor dari kolom C untuk nomor baris. Tidak jauh lebih sederhana dari ini. Ini bukan jawaban yang benar untuk tantangan ini, tetapi ini adalah contoh yang bagus.
Interpretation # 2 (benar)
Penafsiran kedua lebih rumit. C5: E23 adalah kunci yang memberi tahu Anda hanya di mana Anda dapat menemukan pertanyaan dari tes A. Ini tidak melaporkan nomor pertanyaan, itu melaporkan semacam indeks. Jadi, misalnya, di Tes B…
Anda dapat menemukan pertanyaan # 1 dari Tes A di posisi # 4
Anda dapat menemukan pertanyaan # 2 dari Tes A di posisi # 19
Anda dapat menemukan pertanyaan # 3 dari Tes A di posisi # 2
Ini adalah masalah yang lebih rumit. Alih-alih memberi tahu Anda pertanyaan apa dari Tes A yang ada di posisi tertentu, kuncinya adalah memberi tahu Anda di mana Anda dapat menemukan pertanyaan yang Anda cari. Rumus di bawah ini adalah salah satu jawaban yang benar untuk soal ini, karena ini akan mengembalikan jawaban yang ditunjukkan pada tantangan awal.
=INDEX($H$5:$H$23,MATCH($G5,C$5:C$23,0))
Perhatikan referensi campuran di dalam MATCH yang telah diatur dengan hati-hati untuk berubah sesuai kebutuhan saat rumus disalin ke seberang tabel.
$ G5 - kolom dikunci, baris akan berubah
C $ 5: C $ 23 - baris dikunci, kolom akan berubah
