Khi bạn cần chọn ngẫu nhiên để lấy ra một vài giá trị trong bảng dữ liệu có sẵn trong Excel để tiến hành các công việc như kiểm tra xác suất ngẫu nhiên, bạn sẽ làm thế nào? Đừng kéo lướt toàn bộ bảng dữ liệu và chọn nhé, bởi vì chúng ta có một cách đơn giản và tiết kiệm thời gian, công sức hơn rất nhiều. Đó chính là sử dụng hàm Excel.
Bạn đang xem: Hàm chọn mẫu ngẫu nhiên trong excel
Trong bài viết này, Gitiho sẽ hướng dẫn bạn cách sử dụng hàm trong Excel để chọn ngẫu nhiên giá trị cực nhanh. Cùng theo dõi nhé!
TOP KHÓA HỌC EXCEL HAY NHẤT
G-LEARNING
63034 học viên
4.7 (73 đánh giá)
199,000đ
499,000đ
G-LEARNING
36247 học viên
4.84 (122 đánh giá)
499,000đ
799,000đ
G-LEARNING
699 học viên
4.8 (5 đánh giá)
499,000đ
799,000đ
Bài toán ví dụ chọn ngẫu nhiên giá trị trong Excel
Trước khi bắt đầu vào cách chọn ngẫu nhiên giá trị trong Excel, chúng ta sẽ cùng nhau xét một ví dụ dưới đây:
Cho một bảng có 10 ô chứa dữ liệu (tên các học sinh) như sau:
Hãy chọn ngẫu nhiên tên 5 trong số 10 học sinh từ bảng dữ liệu có sẵn. Hay nói cách khác, đây chính là bài toán chọn ngẫu nhiên nhiều ô từ một cột dữ liệu có sẵn. Để có thể chọn ngẫu nhiên theo bài toán này trong Excel, chúng ta có logic thực hiện như sau:
Gắn tên của mỗi học sinh bằng một con số (chọn ngẫu nhiên 1 con số hoặc là sắp đặt trước)Sử dụng hàm trong Excel để lấy giá trị tên theo số (chọn ngẫu nhiên hoặc sắp đặt trước)Sử dụng chức năng Fill Handle để áp dụng công thức vừa tạo cho các giá trị chọn ngẫu nhiên còn lại.Dựa vào logic thực hiện, bạn có thể chọn 1 trong 2 cách dưới đây để thực hiện chọn ngẫu nhiên giá trị trong Excel nhé!
Cách chọn ngẫu nhiên giá trị bằng hàm Excel cực nhanh
Sử dụng kết hợp hàm VLOOKUP và hàm RANDBETWEEN để chọn ngẫu nhiên giá trị
Cách này đặc biệt phù hợp với những bảng dữ liệu đã có sẵn cột số thứ tự. Thay vì phải gắn mỗi giá trị với một số ngẫu nhiên nào đó, bạn có thể sử dụng chính số thứ tự làm số để chọn ngẫu nhiên luôn. Tuy nhiên, nếu bảng dữ liệu của bạn chưa có số thứ tự thì bạn cũng có thể chèn thêm một cột vào bên trái cột tên, sau đó điền số thứ tự nhé
Sau khi đã đánh số thứ tự cho bảng dữ liệu, chúng ta sẽ sử dụng kết hợp 2 hàm Excel là hàm VLOOKUP và hàm RANDBETWEEN để chọn ngẫu nhiên giá trị trong Excel nhé!
Cấu trúc hàm VLOOKUP và hàm RANDBETWEENĐể có thể hiểu rõ hơn cách sử dụng của công thức kết hợp hàm VLOOKUP và hàm RANDBETWEEN giúp chọn ngẫu nhiên giá trị trong Excel,
Cấu trúc hàm VLOOKUP
=VLOOKUP(lookup_value, table_array, col_index_num,
Trong đó:
lookup_value: Giá trị tìm kiếm ban đầutable_array: Bảng tham chiếucol_index_num: Cột lấy giá trị trả về trong bảng tham chiếu.range_lookup: Phương pháp dò tìmGiá trị “0”: Dò tìm tuyệt đối
Giá trị “1”: Dò tìm tương đối
Cấu trúc hàm RANDBETWEEN
=RANDBETWEEN(bottom;top)
Trong đó: Bottom; top là khoảng phạm vi bạn muốn thiết lập
Công thức kết hợp hàm VLOOKUP và hàm RANDBETWEEN để chọn ngẫu nhiên giá trịĐể chọn ngẫu nhiên giá trị như bài toán ví dụ trên, chúng ta sẽ làm theo các bước sau:
Chọn ô mà bạn muốn đặt giá trị được chọn ngẫu nhiênTại ô này, ta nhập công thức sau:=VLOOKUP(RANDBETWEEN(1,10)$A$2:$B$11,2,0)
Giải thích ý nghĩa công thức:
HàmRANDBETWEEN(1,10)sẽ tạo ngẫu nhiên giá trị là số tự nhiên trong khoảng từ 1 đến 10 (vì ở đây đánh số thứ tự từ 1 đến 10)Hàm
VLOOKUPtìm kiếm giá trị của kết quả hàm
RANDBETWEENtrong bảng A2:B11, đối chiếu sang cột thứ 2 và trả kết quả
Sử dụng chức năng Fill Handle cho 4 dòng còn lại để áp dụng công thức trên vàchọn ngẫu nhiên thêm 4 cái tên nữa.
Sử dụng kết hợp hàm RAND, INDEX và RANK
Nếu bảng dữ liệu ban đầu của bạn không có sẵn cột số thứ tự, và bạn cũng không muốn kẻ thêm và cố định cột số thứ tự ở đó thì bạn có thể sử dụng các chọn ngẫu nhiên này nhé. Đây sẽ là sự kết hợp giữa 3 hàm là hàm RAND, hàm INDEX và hàm RANK
Cú pháp hàm RAND, INDEX và hàm RANKTương tự như cách sử dụng kết hợp hàm VLOOKUP và RANDBETWEEN để chọn ngẫu nhiên giá trị, bạn cũng cần biết cú pháp của từng hàm được sử dụng trong công thức để hiểu rõ về logic, tránh những sai sót trong quá trình làm.
Hàm RAND
Hàm RAND trả kết quả là một số thực ngẫy nhiên, lớn hơn hoặc bằng 0 và nhỏ hơn 1. Vì vậy, trong cú pháp của hàm RAND không có một tham số nào. Cú pháp như sau:
=RAND()
Hàm INDEX
Hàm INDEX dạng mảng: =INDEX(Array,Row_num,
Column_num: Thứ tự cột cần tìm
Hàm INDEX dạng tham chiếu: =INDEX(Reference,Row_num,
Row_num: Thứ tự dòng cần tìm
Column_num: Thứ tự cột cần tìm
Area_num: tùy chọn Số của vùng ô sẽ trả về giá trị trong reference
Hàm RANK
Cú pháp của hàm RANK như sau:
=RANK(number,ref,
Trong đó:
Number: Số mà bạn muốn tìm thứ hạng của nó.Ref:Một mảng hoặc tham chiếu tới một danh sách các số. Các giá trị không phải là số trong tham chiếu sẽ được bỏ quaOrder: cách xếp hạng sốCông thức kết hợp hàm RAND, INDEX và RANK để chọn ngẫu nhiên trong Excel
Để chọn ngẫu nhiên giá trị trong Excel bằng cách kết hợp các hàm này, chúng ta sẽ thực hiện như sau:
Chèn thêm một cột mới cạnh cột chứa dữ liệu muốn chon ngẫu nhiênNhập hàm RAND: =RAND() tại ô ứng với cái tên đầu tiên của cột dự liệu tại cột mới chènDùng chức năng Fill Handle để áp dụng hàm RAND() cho 9 dòng còn lại để chọn ngẫu nhiên 9 số nữa
Nhập công thức:
=INDEX($B$2:$B$11,RANK(A2,$A$2:$A$11))
Giải thích công thức:HàmRANKsẽ đưa ra xếp hạng của ô A2 trong vùng A2:A11.Hàm
INDEXsẽ trả ra kết quả theo dòng và cột của mảng được nhập vào.Tiếp tục dùng Fill Handle để sao chép công thức cho các dòng còn lại. Vậy là chúng ta đã dễ dàng chọn ngẫu nhiên được ra 5 cái tên trong số 10 tên học sinh trong bảng dữ liệu cho sẵn rồi đó!
Tổng kết
Việc chọn ngẫu nhiên giá trị không phải việc thường xuyên, tuy nhiên là việc rất cần thiết, đặc biệt trong công tác kiểm tra, kiểm soát chất lượng. Vì vậy, bạn nên nắm chắc về 2 công thức kết hợp hàm Excel giúp chọn ngẫu nhiên mà Gitiho hướng dẫn bên trên.
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.
COUNTBLANK và các hàm khác để đếm các ô trống trong Excel
Cách tạo số ngẫu nhiên trong Excel mà không bị lặp
Cách hiển thị, ẩn và khôi phục ruy băng bị thiếu trong Excel
Cách tìm và loại bỏ các ô trùng lặp trong Excel
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.Xem thêm: Một Số Kiến Thức Excel Cơ Bản, Trung Cấp Đến Nâng Cao, Tổng Quan Về Các Công Thức Trong Excel
Đó 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-Blog
Spo
Đó 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!