Công thức chung
=INDEX(data,MATCH(ROWS(exp_range),sort_values,0))
Tóm lược
Để sắp xếp ngẫu nhiên các giá trị hiện có bằng một công thức, bạn có thể sử dụng một công thức INDEX và MATCH cùng với các cột trợ giúp như trong ảnh chụp màn hình. Trong ví dụ được hiển thị, công thức trong E5 là:
=INDEX(names,MATCH(ROWS($D$5:$D5),sort,0))
trong đó "tên" là phạm vi được đặt tên B5: B11, "rand" là phạm vi được đặt tên C5: C11 và "sắp xếp" là phạm vi được đặt tên D5: D11.
Giải trình
Công thức này phụ thuộc vào hai cột trợ giúp. Cột trình trợ giúp đầu tiên chứa các giá trị ngẫu nhiên được tạo bằng hàm RAND (). Công thức trong C5, được sao chép xuống là:
=RAND()
Hàm RAND tạo ra một giá trị ngẫu nhiên tại mỗi hàng.
Lưu ý: RAND là một hàm dễ thay đổi và sẽ tạo ra các giá trị mới với mỗi lần thay đổi trang tính.
Cột trợ giúp thứ hai chứa các số được sử dụng để sắp xếp dữ liệu, được tạo bằng công thức. Công thức trong D5 là:
=RANK(C5,rand)+COUNTIF($C$5:C5,C5)-1
Xem trang này để biết giải thích về công thức này.
Công thức trong E5 là:
=INDEX(names,MATCH(ROWS($D$5:$D5),sort,0))
Ở đây, hàm INDEX được sử dụng để truy xuất các giá trị trong phạm vi được đặt tên là "tên", sử dụng các giá trị sắp xếp trong phạm vi được đặt tên "sắp xếp". Công việc thực tế của việc tìm ra giá trị nào cần truy xuất đã được thực hiện xong hàm MATCH trong đoạn mã này:
MATCH(ROWS($D$5:$D5),sort,0)
Bên trong MATCH, hàm ROWS được cung cấp một phạm vi mở rộng làm giá trị tra cứu, bắt đầu dưới dạng một ô và mở rộng khi công thức được sao chép xuống cột. Điều này làm tăng giá trị tra cứu, bắt đầu từ 1 và tiếp tục đến 7. MATCH sau đó trả về vị trí của giá trị tra cứu trong danh sách.
Vị trí được đưa đến INDEX làm số hàng và INDEX truy xuất tên tại vị trí đó.
