Hướng dẫn tập trung vào cách thực hiện lấy mẫu ngẫu nhiên trong Excel mà không lặp lại. Bạn sẽ tìm thấy giải pháp cho tất cả các phiên bản Excel 365, Excel 2019, Excel 2016, Excel 2013 trở về trước.
Cách đây không lâu, chúng tôi đã mô tả một số cách khác nhau để chọn ngẫu nhiên trong Excel. Hầu hết các giải pháp đó đều dựa vào các hàm RAND và RANDBETWEEN, có thể tạo ra các số trùng lặp. Do đó, mẫu ngẫu nhiên của bạn có thể chứa các giá trị lặp lại. Nếu bạn cần một lựa chọn ngẫu nhiên không trùng lặp, thì hãy sử dụng các phương pháp được mô tả trong hướng dẫn này.
Lựa chọn ngẫu nhiên trong Excel từ danh sách không có bản sao
Chỉ hoạt động trong các phiên bản mới nhất của Excel 365 hỗ trợ mảng động.
Để thực hiện lựa chọn ngẫu nhiên từ một danh sách không có lặp lại, hãy sử dụng công thức chung sau:
INDEX (SORTBY (dữ liệu, RANDARRAY (ROWS (dữ liệu))), SỰ NỐI TIẾP(n))
Ở đâu n là kích thước lựa chọn mong muốn.
Ví dụ: để lấy 5 tên ngẫu nhiên duy nhất từ danh sách trong A2: A10, đây là công thức để sử dụng:
=INDEX(SORTBY(A2:A10, RANDARRAY(ROWS(A2:A10))), SEQUENCE(5))
Để thuận tiện, bạn có thể nhập kích thước mẫu vào một ô xác định trước, chẳng hạn như C2 và cung cấp tham chiếu ô cho hàm SEQUENCE:
=INDEX(SORTBY(A2:A10, RANDARRAY(ROWS(A2:A10))), SEQUENCE(C2))
Công thức này hoạt động như thế nào:
Đây là lời giải thích cấp cao về logic của công thức: RANDARRAY hàm tạo một mảng các số ngẫu nhiên, SORTBY sắp xếp các giá trị ban đầu theo những con số đó và MỤC LỤC truy xuất nhiều giá trị như được chỉ định bởi SEQUENCE.
Dưới đây là bảng phân tích chi tiết:
Hàm ROWS đếm số hàng mà tập dữ liệu của bạn chứa và chuyển số lượng cho hàm RANDARRAY, vì vậy hàm này có thể tạo ra cùng một số lượng thập phân ngẫu nhiên:
RANDARRAY(ROWS(A2:C10))
Mảng các số thập phân ngẫu nhiên này được sử dụng làm mảng “sắp xếp theo” bởi hàm SORTBY. Do đó, dữ liệu gốc của bạn bị xáo trộn ngẫu nhiên.
Từ dữ liệu được sắp xếp ngẫu nhiên, bạn trích xuất một mẫu có kích thước cụ thể. Đối với điều này, bạn cung cấp mảng xáo trộn cho hàm INDEX và yêu cầu truy xuất mảng đầu tiên N với sự trợ giúp của hàm SEQUENCE, hàm này tạo ra một chuỗi các số từ 1 đến N. Bởi vì dữ liệu ban đầu đã được sắp xếp theo thứ tự ngẫu nhiên, chúng tôi không thực sự quan tâm đến vị trí nào để truy xuất, chỉ quan trọng số lượng.
Chọn các hàng ngẫu nhiên trong Excel không trùng lặp
Chỉ hoạt động trong các phiên bản mới nhất của Excel 365 hỗ trợ mảng động.
Để chọn các hàng ngẫu nhiên không có lặp lại, hãy xây dựng công thức theo cách sau:
INDEX (SORTBY (dữ liệu, RANDARRAY (ROWS (dữ liệu))), SỰ NỐI TIẾP(n), {1,2,…})
Ở đâu n là kích thước mẫu và {1,2,…} là số cột cần trích xuất.
Ví dụ: hãy chọn các hàng ngẫu nhiên từ A2: C10 mà không có mục nhập trùng lặp, dựa trên kích thước mẫu trong F1. Vì dữ liệu của chúng tôi có 3 cột, chúng tôi cung cấp hằng số mảng này cho công thức: {1,2,3}
=INDEX(SORTBY(A2:C10, RANDARRAY(ROWS(A2:C10))), SEQUENCE(F1), {1,2,3})
Và nhận được kết quả sau:
Công thức này hoạt động như thế nào:
Công thức hoạt động với cùng một logic như công thức trước đó. Một thay đổi nhỏ tạo ra sự khác biệt lớn là bạn chỉ định cả hai row_num và column_num đối số cho hàm INDEX: row_num được cung cấp bởi SEQUENCE và column_num bởi hằng số mảng.
Cách thực hiện lấy mẫu ngẫu nhiên trong Excel 2010-2019
Vì chỉ đăng ký Microsoft 365 mới hỗ trợ mảng động, các hàm mảng động được sử dụng trong các ví dụ trước chỉ hoạt động trong Excel 365. Đối với các phiên bản Excel khác, bạn sẽ phải tìm ra giải pháp khác.
Giả sử bạn muốn một lựa chọn ngẫu nhiên từ danh sách trong A2: A10. Điều này có thể được thực hiện với 2 công thức riêng biệt:
- Tạo số ngẫu nhiên với công thức Rand. Trong trường hợp của chúng tôi, chúng tôi nhập nó vào B2, và sau đó sao chép xuống B10:
= RAND () - Trích xuất giá trị ngẫu nhiên đầu tiên với công thức dưới đây, mà bạn nhập vào E2:
= INDEX ($ A $ 2: $ A $ 10, RANK.EQ (B2, $ B $ 2: $ B $ 10) + COUNTIF ($ B $ 2: B2, B2) – 1) - Sao chép công thức trên vào bao nhiêu ô với nhiều giá trị ngẫu nhiên bạn muốn chọn. Trong ví dụ này, chúng tôi muốn có 4 tên, vì vậy chúng tôi sao chép công thức từ E2 đến E5.
Làm xong! Mẫu ngẫu nhiên không có bản sao của chúng tôi trông như sau:
Công thức này hoạt động như thế nào:
Giống như trong ví dụ đầu tiên, bạn sử dụng hàm INDEX để lấy các giá trị từ cột A dựa trên số hàng ngẫu nhiên. Sự khác biệt là ở cách bạn nhận được những con số đó:
Các RAND hàm lấp đầy phạm vi B2: B10 với các số thập phân ngẫu nhiên.
Các RANK.EQ hàm tính toán thứ hạng của một số ngẫu nhiên trong một hàng nhất định. Ví dụ: trong E2, RANK.EQ (B2, $ B $ 2: $ B $ 10) xếp hạng số trong B2 so với tất cả các số trong B2: B10. Khi được sao chép sang E3, tham khảo tương đối B2 chuyển thành B3 và trả về thứ hạng của số trong B3, v.v.
Các COUNTIF hàm tìm bao nhiêu lần xuất hiện của một số nhất định trong các ô trên. Ví dụ: trong E2, COUNTIF ($ B $ 2: B2, B2) chỉ kiểm tra một ô – chính B2 và trả về 1. Trong E5, công thức thay đổi thành COUNTIF ($ B $ 2: B5, B5) và trả về 2, bởi vì B5 chứa cùng giá trị với B2 (xin lưu ý, điều này chỉ để giải thích tốt hơn logic của công thức; trên một tập dữ liệu nhỏ, cơ hội nhận được các số ngẫu nhiên trùng lặp gần bằng 0).
Kết quả là, đối với tất cả các lần xuất hiện đầu tiên, COUNTIF trả về 1, từ đó bạn trừ đi 1 để giữ nguyên xếp hạng ban đầu. Đối với lần xuất hiện thứ hai, COUNTIF trả về 2. Bằng cách trừ đi 1, bạn tăng xếp hạng lên 1, do đó ngăn xếp hạng trùng lặp.
Ví dụ: đối với B2, RANK.EQ trả về 1. Vì đây là lần xuất hiện đầu tiên, COUNTIF cũng trả về 1. RANK.EQ + COUNTIF trả về 2. Và – 1 khôi phục xếp hạng 1.
Bây giờ, hãy xem điều gì sẽ xảy ra trong trường hợp 2nd tần suất xảy ra. Đối với B5, RANK.EQ cũng trả về 1 trong khi COUNTIF trả về 2. Cộng các giá trị này lên sẽ cho 3, từ đó bạn trừ đi 1. Kết quả cuối cùng, bạn nhận được 2, đại diện cho thứ hạng của số trong B5.
Xếp hạng đi đến row_num đối số của hàm INDEX và nó chọn giá trị từ hàng tương ứng ( column_num đối số bị bỏ qua, vì vậy nó được mặc định là 1). Đây là lý do tại sao nó rất quan trọng để tránh xếp hạng trùng lặp. Nếu không phải cho hàm COUNTIF, RANK.EQ sẽ mang lại giá trị 1 cho cả B2 và B5, khiến INDEX trả về giá trị từ hàng đầu tiên (Andrew) hai lần.
Cách ngăn mẫu ngẫu nhiên trong Excel thay đổi
Vì tất cả các hàm ngẫu nhiên hóa trong Excel như RAND, RANDBETWEEN và RANDARRAY đều dễ bay hơi, chúng sẽ tính toán lại với mọi thay đổi trên trang tính. Do đó, mẫu ngẫu nhiên của bạn sẽ liên tục thay đổi. Để ngăn điều này xảy ra, hãy sử dụng Dán Đặc biệt> Giá trị tính năng thay thế các công thức bằng các giá trị tĩnh. Đối với điều này, hãy thực hiện các bước sau:
- Chọn tất cả các ô có công thức của bạn (bất kỳ công thức nào chứa hàm RAND, RANDBETWEEN hoặc RANDARRAY) và nhấn Ctrl + C để sao chép chúng.
- Nhấp chuột phải vào phạm vi đã chọn và nhấp vào Dán đặc biệt > Giá trị. Hoặc, nhấn Shift + F10 và sau đó V, là phím tắt cho tính năng nêu trên.
Để biết các bước chi tiết, vui lòng xem Cách chuyển đổi công thức thành giá trị trong Excel.
Lựa chọn ngẫu nhiên trong Excel: hàng, cột hoặc ô
Hoạt động trên tất cả các phiên bản Excel 365, Excel 2019, Excel 2016, Excel 2013 và Excel 2010.
Nếu bạn có của chúng tôi Suite Ultimate được cài đặt trong Excel của bạn, sau đó bạn có thể lấy mẫu ngẫu nhiên bằng một cú nhấp chuột thay vì công thức. Đây là cách thực hiện:
- Trên Công cụ Ablebits tab, nhấp vào Ngẫu nhiên hóa > Chọn ngẫu nhiên.
- Chọn phạm vi mà bạn muốn chọn mẫu.
- Trên ngăn của bổ trợ, hãy làm như sau:
- Chọn xem bạn muốn chọn hàng, cột hoặc ô ngẫu nhiên.
- Xác định kích thước mẫu: có thể là phần trăm hoặc số.
- Nhấn vào Lựa chọn cái nút.
Đó là nó! Như thể hiện trong hình ảnh bên dưới, một mẫu ngẫu nhiên được chọn trực tiếp trong tập dữ liệu của bạn. Nếu bạn muốn sao chép nó ở đâu đó, chỉ cần nhấn một phím tắt sao chép thông thường (Ctrl + C).
Nếu bạn quan tâm, hãy thử tính năng này và hơn 300 tính năng tuyệt vời khác được bao gồm trong Suite Ultimate, bạn đều được chào đón tải xuống phiên bản dùng thử 14 ngày. Nếu bạn thích các công cụ và quyết định lấy giấy phép, đừng bỏ lỡ chương trình giảm giá 15% dành riêng cho độc giả blog của chúng tôi! Mã phiếu giảm giá của bạn: AB14-BlogSpo
Đó là cách chọn một mẫu ngẫu nhiên trong Excel mà không có bản sao. Tôi cảm ơn bạn đã đọc và hy vọng sẽ gặp bạn trên blog của chúng tôi vào tuần tới!
The post Cách lấy mẫu ngẫu nhiên trong Excel mà không có bản sao appeared first on Kế Toán MVB | Dịch Vụ kế toán tại Hà Nội | Uy tín - Chất lượng.
source https://ketoanmvb.com/cach-lay-mau-ngau-nhien-trong-excel-ma-khong-co-ban-sao.html
Không có nhận xét nào:
Đăng nhận xét