Vấn đề
Có một bài kiểm tra tổng thể (Bài kiểm tra A) và ba biến thể (Bài kiểm tra B, Bài kiểm tra C và Bài kiểm tra D). Cả 4 bài thi đều có 19 câu hỏi giống nhau, nhưng được sắp xếp theo thứ tự khác nhau.
Bảng đầu tiên trong màn hình dưới đây là "phím câu hỏi" và hiển thị cách các câu hỏi trong Bài kiểm tra A được sắp xếp như thế nào trong 3 bài thi còn lại. Bảng thứ hai là "khóa trả lời" hiển thị câu trả lời đúng cho tất cả 19 câu hỏi trong tất cả các bài kiểm tra.
Ở trên: Các câu trả lời đúng trong I5: K23, công thức bị che khuất
Ví dụ: câu trả lời cho câu hỏi số 1 trong Bài kiểm tra A là C. Câu hỏi này giống như câu hỏi số 4 trong Bài kiểm tra B, vì vậy câu trả lời cho câu hỏi số 4 trong Bài kiểm tra B cũng là C.
Câu hỏi đầu tiên trong Bài kiểm tra B giống với câu hỏi số 13 trong Bài kiểm tra A và câu trả lời cho cả hai đều là E.
Các thách thức
Công thức nào có thể được nhập vào I5 (đó là chữ i như trong "lều tuyết") và sao chép trên I5: K23 để tìm và hiển thị các câu trả lời chính xác cho Bài kiểm tra B, C và D?
Bạn sẽ tìm thấy tệp Excel bên dưới. Để lại câu trả lời của bạn dưới dạng bình luận bên dưới.
Gợi ý
-
Vấn đề này là một thách thức để thiết lập. Rất dễ bị nhầm lẫn. Hãy nhớ rằng, các số trong C5: E23 chỉ cho bạn biết bạn có thể tìm thấy một câu hỏi nhất định ở đâu. Bạn vẫn phải tìm câu hỏi sau đó :)
-
Vấn đề này có thể được giải quyết với INDEX và MATCH, được giải thích trong bài viết này. Một phần của giải pháp liên quan đến việc khóa cẩn thận các tham chiếu ô. Nếu bạn gặp khó khăn với những loại tham chiếu này, hãy thực hành xây dựng bảng cửu chương được hiển thị ở đây. Vấn đề này yêu cầu tham chiếu ô được xây dựng cẩn thận!
-
Bạn có thể thấy mình nghĩ rằng bạn có thể làm điều này nhanh hơn theo cách thủ công. Có, cho một số câu hỏi nhỏ. Tuy nhiên, với nhiều câu hỏi hơn (tưởng tượng 100, 500, 1000 câu hỏi), cách tiếp cận thủ công sẽ khó hơn nhiều. Một công thức tốt sẽ xử lý hàng nghìn câu hỏi một cách vui vẻ và nó sẽ không mắc lỗi :)
Có hai cách để giải thích thách thức này. Khi tôi đặt vấn đề, tôi đang mượn trực tiếp một ví dụ do một độc giả gửi cho tôi. Đây hóa ra là cách tiếp cận khó khăn hơn (Giải thích # 2 bên dưới), chủ yếu là vì bạn rất dễ nhầm lẫn khi cố gắng hiểu bảng. Dưới đây, tôi giải thích cả hai cách diễn giải cùng với các công thức có thể được sử dụng với mỗi cách.
Giải thích # 1 (không chính xác)
C5: E23 hiển thị các câu hỏi tương tự từ bài kiểm tra A, được sắp xếp lại đơn giản. Vì vậy, ví dụ, trong Bài kiểm tra B…
Bạn có thể tìm thấy câu hỏi số 1 từ Bài kiểm tra A ở vị trí số 13
Bạn có thể tìm câu hỏi số 2 từ Bài kiểm tra A ở vị trí số 3
Bạn có thể tìm câu hỏi số 3 từ Bài kiểm tra A ở vị trí số 7
=INDEX($H$5:$H$23,C5)
Với các câu trả lời cho Kiểm tra A trong mảng H5: H23, INDEX chỉ cần truy xuất một giá trị bằng cách sử dụng số từ cột C cho số hàng. Không đơn giản hơn thế này nhiều. Đây không phải là câu trả lời chính xác cho thử thách này, nhưng dù sao nó cũng là một ví dụ hay.
Giải thích # 2 (đúng)
Cách giải thích thứ hai phức tạp hơn. C5: E23 là một khóa chỉ cho bạn biết nơi bạn có thể tìm thấy câu hỏi từ bài kiểm tra A. Nó không báo cáo số câu hỏi, nó báo cáo một chỉ mục của các loại. Vì vậy, ví dụ, trong Bài kiểm tra B…
Bạn có thể tìm câu hỏi số 1 từ Bài kiểm tra A ở vị trí số 4
Bạn có thể tìm câu hỏi số 2 từ Bài kiểm tra A ở vị trí số 19
Bạn có thể tìm câu hỏi số 3 từ Bài kiểm tra A ở vị trí số 2
Đây là một vấn đề phức tạp hơn. Thay vì cho bạn biết câu hỏi từ Bài kiểm tra A ở vị trí nào, điều quan trọng là cho bạn biết bạn có thể tìm thấy câu hỏi mà bạn tìm kiếm ở đâu. Công thức dưới đây là một câu trả lời đúng cho vấn đề này, vì nó sẽ trả về các câu trả lời được hiển thị trong thử thách ban đầu.
=INDEX($H$5:$H$23,MATCH($G5,C$5:C$23,0))
Lưu ý các tham chiếu hỗn hợp bên trong MATCH đã được thiết lập cẩn thận để thay đổi khi cần thiết khi công thức được sao chép trên bảng.
$ G5 - cột bị khóa, hàng sẽ thay đổi
C $ 5: C $ 23 - hàng bị khóa, cột sẽ thay đổi
