Khi bạn phải chọn bỗng dưng để mang ra một vài quý hiếm trong bảng dữ liệu có sẵn vào Excel để tiến hành các công việc như kiểm tra phần trăm ngẫu nhiên, các bạn sẽ làm ráng nào? Đừng kéo lướt tổng thể bảng dữ liệu và lựa chọn nhé, bởi vì chúng ta bao gồm một cách đơn giản và dễ dàng và tiết kiệm ngân sách thời gian, công sức của con người hơn cực kỳ nhiều. Đó đó 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 đang hướng dẫn bạn cách sử dụng hàm trong Excel để chọn tự nhiên giá trị rất nhanh. Thuộc theo dõi nhé!
TOP KHÓA HỌC EXCEL giỏi NHẤT
G-LEARNING
63034 học viên
4.7 (73 tiến công 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 tập viên
4.8 (5 đánh giá)
499,000đ
799,000đ
Bài toán lấy ví dụ chọn bỗng nhiên giá trị trong Excel
Trước khi bắt đầu vào cách chọn ngẫu nhiên quý hiếm trong Excel, chúng ta sẽ với mọi người trong nhà 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 lựa chọn ngẫu nhiên thương hiệu 5 trong số 10 học sinh từ bảng tài liệu có sẵn. Hay nói phương pháp khác, đây chính là bài toán chọn hốt 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 vào 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 số lượng (chọn ngẫu nhiên 1 con số hoặc là sắp xếp trước)Sử dụng hàm vào Excel để mang giá trị tên theo số (chọn bỗng nhiên hoặc sắp xếp trước)Sử dụng công dụng Fill Handle để áp dụng công thức vừa làm cho các giá trị chọn thiên nhiên còn lại.Dựa vào xúc tích thực hiện, chúng ta có thể chọn 1 trong các 2 cách sau đây để thực hiện chọn đột nhiên giá trị trong Excel nhé!
Cách chọn thốt nhiên giá trị bằng hàm Excel cực nhanh
Sử dụng phối hợp hàm VLOOKUP cùng hàm RANDBETWEEN nhằm chọn tình cờ giá trị
Cách này đặc biệt phù hợp với rất nhiều bảng tài liệu đã bao gồm sẵn cột số thiết bị tự. Ráng vì yêu cầu gắn mỗi quý hiếm với một trong những ngẫu nhiên nào đó, bạn có thể sử dụng thiết yếu số trang bị tự làm số để lựa chọn ngẫu nhiên luôn. Mặc dù nhiên, nếu bảng dữ liệu của bạn chưa tồn tại số trang bị tự thì bạn cũng có thể chèn thêm 1 cột vào phía trái cột tên, kế tiếp điền số lắp thêm tự nhé
Sau khi đã đánh số thứ tự mang đến bảng dữ liệu, chúng ta sẽ sử dụng phối kết hợp 2 hàm Excel là hàm VLOOKUP cùng hàm RANDBETWEEN để chọn đột nhiên giá trị trong Excel nhé!
Cấu trúc hàm VLOOKUP với hàm RANDBETWEENĐể rất có thể hiểu rõ hơn cách áp dụng của công thức kết hợp hàm VLOOKUP với hàm RANDBETWEEN giúp chọn ngẫu nhiên quý hiếm 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 quý giá trả về vào bảng tham chiếu.range_lookup: cách thức dò tìmGiá trị “0”: Dò tìm hay đối
Giá trị “1”: Dò search tương đối
Cấu trúc hàm RANDBETWEEN
=RANDBETWEEN(bottom;top)
Trong đó: Bottom; vị trí cao nhất là khoảng chừng phạm vi bạn có nhu cầu thiết lập
Công thức phối hợp hàm VLOOKUP và hàm RANDBETWEEN để chọn tự nhiên giá trịĐể chọn thiên nhiên giá trị như câu hỏi ví dụ trên, chúng ta sẽ làm theo công việc sau:
Chọn ô mà bạn có nhu cầu đặt quý hiếm được chọn ngẫu nhiênTại ô này, ta nhập bí quyết sau:=VLOOKUP(RANDBETWEEN(1,10)$A$2:$B$11,2,0)
Giải thích chân thành và ý nghĩa công thức:
HàmRANDBETWEEN(1,10)sẽ tạo ngẫu nhiên giá trị là số tự nhiên và thoải mái trong khoảng từ là 1 đến 10 (vì tại chỗ này đánh số thiết bị tự từ một đến 10)Hàm
VLOOKUPtìm kiếm quý hiếm của tác dụng hàm
RANDBETWEENtrong bảng A2:B11, đối chiếu sang cột thứ 2 và trả kết quả
Sử dụng tác dụng Fill Handle mang đến 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 thương hiệu nữa.
Sử dụng kết hợp hàm RAND, INDEX cùng RANK
Nếu bảng dữ liệu ban sơ của bạn không có sẵn cột số lắp thêm tự, và các bạn cũng không muốn kẻ thêm và thắt chặt và cố định cột số lắp thêm tự ở kia thì bạn cũng có thể sử dụng các chọn ngẫu nhiên này nhé. Đây đang là sự kết hợp giữa 3 hàm là hàm RAND, hàm INDEX cùng hàm RANK
Cú pháp hàm RAND, INDEX với hàm RANKTương tự như biện pháp sử dụng kết hợp hàm VLOOKUP và RANDBETWEEN để chọn ngẫu nhiên giá chỉ trị, chúng ta cũng nên biết cú pháp của từng hàm được sử dụng trong bí quyết để làm rõ về logic, tránh phần đông sai sót trong quy trình làm.
Hàm RAND
Hàm RAND trả công dụng là một số trong những thực ngẫy nhiên, to hơn hoặc bởi 0 và nhỏ dại hơn 1. Bởi vậy, trong cú pháp của hàm RAND không tồn tại 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: đồ vật tự cột bắt buộc tìm
Hàm INDEX dạng tham chiếu: =INDEX(Reference,Row_num,
Row_num: thứ tự dòng đề xuất tìm
Column_num: lắp thêm tự cột đề xuất tìm
Area_num: tùy lựa chọn Số của vùng ô vẫn trả về cực hiếm 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ị ko phải là số trong tham chiếu sẽ được bỏ quaOrder: bí quyết xếp hạng sốCông thức kết hợp hàm RAND, INDEX với RANK để chọn tự dưng trong Excel
Để chọn thốt nhiên giá trị vào Excel bằng phương pháp kết hợp những hàm này, chúng ta sẽ thực hiện như sau:
Chèn thêm một cột new cạnh cột chứa tài liệu muốn chon ngẫu nhiênNhập hàm RAND: =RAND() tại ô ứng với dòng tên đầu tiên của cột dự liệu trên cột new chènDùng chức năng Fill Handle để áp dụng hàm RAND() mang lại 9 dòng sót lại để chọn hốt 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 ham mê công thức:HàmRANKsẽ đưa ra xếp hạng của ô A2 vào 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 cần sử dụng Fill Handle để coppy công thức cho những dòng còn lại. Vậy là họ đã tiện lợi chọn bỗng nhiên được ra 5 mẫu tên trong số 10 tên học viên trong bảng tài liệu cho sẵn rồi đó!
Tổng kết
Việc chọn ngẫu nhiên giá chỉ trị không hẳn việc thường xuyên, tuy vậy là bài toán rất phải thiết, đặc biệt trong công tác kiểm tra, điều hành và 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 phối hợp hàm Excel góp chọn bỗng dưng mà Gitiho hướng dẫn mặt trên.
Hướng dẫn triệu tập vào cách thực hiện lấy mẫu đột nhiên trong Excel mà lại không lặp lại. Các bạn sẽ tìm thấy phương án cho tất cả các phiên bạn dạng Excel 365, Excel 2019, Excel 2016, Excel 2013 về bên trước.Cách đây không lâu, chúng tôi đã mô tả một trong những cách khác nhau để chọn tình cờ trong Excel. Số đông các giải pháp đó đều phụ thuộc vào các hàm RAND cùng RANDBETWEEN, hoàn toàn có thể tạo ra những số trùng lặp. Bởi vì đó, mẫu bất chợt của chúng ta có thể chứa các giá trị lặp lại. Nếu khách hàng cần một lựa chọn đột nhiên không trùng lặp, thì nên sử dụng các cách thức được diễn tả trong hướng dẫn này.
Lựa chọn thốt nhiên trong Excel từ danh sách không có bản sao
Chỉ chuyển động trong các phiên bạn dạng mới duy 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 áp dụng công thức bình thường 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 mỏi muốn.
Ví dụ: để đưa 5 tên đột nhiên duy nhất từ danh sách vào 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, chúng ta có thể nhập form size mẫu vào một trong những ô xác định trước, ví dụ như C2 và hỗ trợ tham chiếu ô đến hàm SEQUENCE:
=INDEX(SORTBY(A2:A10, RANDARRAY(ROWS(A2:A10))), SEQUENCE(C2))
Công thức này vận động như cố gắng nào:
Đây là lời giải thích cấp cao về súc tích của công thức: RANDARRAY hàm chế tạo ra một mảng các số ngẫu nhiên, SORTBY sắp đến xếp những giá trị ban sơ theo những con số đó với MỤC LỤC truy xuất những giá trị như được chỉ định bởi vì SEQUENCE.
Dưới đấy là bảng phân tích đưa ra tiết:
Hàm ROWS đếm số hàng nhưng mà tập dữ liệu của người tiêu dùng chứa và chuyển số lượng cho hàm RANDARRAY, do vậy hàm này hoàn toàn có thể tạo ra cùng một trong những lượng thập phân ngẫu nhiên:
RANDARRAY(ROWS(A2:C10))
Mảng những số thập phân thốt nhiên này được thực hiện làm mảng “sắp xếp theo” vì chưng hàm SORTBY. Vì chưng đó, dữ liệu gốc của công ty bị đảo lộn ngẫu nhiên.
Từ tài liệu được bố trí ngẫu nhiên, bạn trích xuất một chủng loại có kích cỡ cụ thể. Đối với điều này, bạn cung ứng mảng xáo trộn mang lại hàm INDEX với yêu cầu truy xuất mảng đầu tiên N với việc trợ giúp của hàm SEQUENCE, hàm này tạo ra một chuỗi các số từ là 1 đến N. Cũng chính vì dữ liệu ban đầu đã được sắp xếp theo sản phẩm công nghệ tự ngẫu nhiên, cửa hàng chúng tôi không thực sự suy xét vị trí nào để truy xuất, chỉ quan trọng đặc biệt số lượng.
Chọn những hàng ngẫu nhiên trong Excel ko trùng lặp
Chỉ hoạt động trong những phiên bạn dạng mới độc nhất vô nhị của Excel 365 cung ứng mảng động.
Để chọn những hàng ngẫu nhiên không có lặp lại, hãy tạo ra công thức theo phong cách sau:
INDEX (SORTBY (dữ liệu, RANDARRAY (ROWS (dữ liệu))), SỰ NỐI TIẾP(n), 1,2,…)
Ở đâu n là form size mẫu và 1,2,… là số cột bắt buộc trích xuất.
Ví dụ: hãy lựa chọn các hàng tự nhiên từ A2: C10 mà không tồn tại mục nhập trùng lặp, dựa trên kích thước mẫu trong F1. Vì tài liệu của chúng tôi có 3 cột, công ty 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à dìm được tác dụng sau:
Công thức này chuyển động như vậy nào:
Công thức chuyển động với cùng một súc tích như công thức trước đó. Một cố kỉnh đổi nhỏ tuổi tạo ra sự khác biệt lớn là các bạn chỉ định cả nhì row_num cùng column_num đối số mang đến hàm INDEX: row_num được hỗ trợ bởi SEQUENCE với column_num do hằng số mảng.
COUNTBLANK và các hàm khác nhằm đếm các ô trống vào Excel
Cách tạo số ngẫu nhiên trong Excel mà không trở nên lặp
Cách hiển thị, ẩn và phục hồi ruy băng bị thiếu thốn trong Excel
Cách tìm và loại bỏ các ô đụng hàng trong Excel
Cách tiến hành lấy mẫu bỗng nhiên trong Excel 2010-2019
Vì chỉ đk Microsoft 365 mới hỗ trợ mảng động, những hàm mảng cồn được sử dụng trong số ví dụ trước chỉ vận động trong Excel 365. Đối với những phiên bạn dạng Excel khác, bạn sẽ phải tìm kiếm ra giải pháp khác.
Giả sử bạn muốn một lựa chọn thiên nhiên từ list trong A2: A10. Điều này hoàn toàn có thể được triển khai với 2 cách làm riêng biệt:
Tạo số bất chợt với bí quyết Rand. Trong trường vừa lòng của chúng tôi, cửa hàng chúng tôi nhập nó vào B2, và sau đó sao chép xuống B10:= RAND ()Trích xuất quý giá ngẫu nhiên trước tiên với bí quyết dưới đây, mà chúng ta 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 phương pháp trên vào từng nào ô với tương đối nhiều giá trị ngẫu nhiên bạn có nhu cầu chọn. Trong ví dụ như này, cửa hàng chúng tôi muốn bao gồm 4 tên, vị vậy công ty chúng tôi sao chép công thức từ E2 mang đến E5.Làm xong! Mẫu bỗng nhiên không có bản sao của cửa hàng chúng tôi trông như sau:
Công thức này hoạt động như vắt nào:
Giống như trong ví dụ đầu tiên, bạn thực hiện hàm INDEX để mang các quý giá từ cột A dựa trên số sản phẩm ngẫu nhiên. Sự khác biệt là sống cách các bạn nhận được những số lượng đó:
Các RAND hàm tủ đầy phạm vi B2: B10 với những số thập phân ngẫu nhiên.
Các RANK.EQ hàm thống kê giám sát thứ hạng của một số trong những ngẫu nhiên trong một hàng tuyệt nhất định. Ví dụ: vào E2, RANK.EQ (B2, $ B $ 2: $ B $ 10) xếp hạng số trong B2 so với toàn bộ các số vào B2: B10. Lúc được xào luộc sang E3, tìm hiểu thêm tương đối B2 chuyển thành B3 và trả về hình dạ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 số ô trên. Ví dụ: vào E2, COUNTIF ($ B $ 2: B2, B2) chỉ kiểm tra một ô – bao gồm B2 với trả về 1. Trong E5, công thức thay đổi thành COUNTIF ($ B $ 2: B5, B5) cùng trả về 2, cũng chính vì B5 cất cùng cực hiếm với B2 (xin lưu lại ý, điều này chỉ để phân tích và lý giải tốt hơn xúc tích của công thức; bên trên một tập dữ liệu nhỏ, thời cơ nhận được các số bỗng dưng trùng lặp gần bằng 0).
Kết trái là, đối với cả các lần xuất hiện đầu tiên, COUNTIF trả về 1, tự đó chúng ta trừ đi 1 để không thay đổi xếp hạng ban đầu. Đối cùng với lần lộ diện thứ hai, COUNTIF trả về 2. Bằng cách trừ đi 1, chúng ta tăng xếp thứ hạng lên 1, vì thế ngăn xếp thứ hạng trùng lặp.
Ví dụ: so với B2, RANK.EQ trả về 1. Vì đó là lần xuất hiện thêm đầu tiên, COUNTIF cũng trả về 1. RANK.EQ + COUNTIF trả về 2. Và – 1 phục sinh xếp hạng 1.
Bây giờ, hãy coi điều gì sẽ xảy ra trong trường hợp 2nd tần suất xảy ra. Đối cùng với B5, RANK.EQ cũng trả về 1 trong các khi COUNTIF trả về 2. Cộng các giá trị này lên sẽ đến 3, từ bỏ đó bạn trừ đi 1. Kết quả cuối cùng, các bạn nhận được 2, thay mặt đạ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ới nó lựa chọn giá trị từ bỏ hàng tương ứng ( column_num đối số bị bỏ qua, vày vậy nó được mặc định là 1). Đây là vì sao tại sao nó rất quan trọng đặc biệt để tránh xếp hạng trùng lặp. Nếu không hẳn cho hàm COUNTIF, RANK.EQ sẽ đưa về giá trị 1 cho tất cả B2 với B5, khiến cho INDEX trả về quý giá từ hàng đầu tiên (Andrew) hai lần.
Cách ngăn mẫu thốt nhiên trong Excel thay đổi
Vì tất cả các hàm tự dưng hóa trong Excel như RAND, RANDBETWEEN với RANDARRAY các dễ cất cánh hơi, bọn chúng sẽ giám sát và đo lường lại với mọi thay đổi trên trang tính. Vày đó, mẫu bất chợt của bạn sẽ liên tục rứa đổi. Để ngăn điều đó xảy ra, hãy áp dụng Dán Đặc biệt> quý giá tính năng sửa chữa thay thế các phương pháp bằng những giá trị tĩnh. Đối cùng với điều này, hãy thực hiện các bước sau:
Chọn tất cả các ô gồm công thức của công ty (bất kỳ bí quyết nào cất hàm RAND, RANDBETWEEN hoặc RANDARRAY) cùng nhấn Ctrl + C để sao chép chúng.Nhấp chuột buộc phải vào phạm vi đã lựa chọn và nhấp vào Dán sệt biệt > Giá trị. Hoặc, nhận Shift + F10 và sau đó V, là phím tắt cho tác dụng nêu trên.Để biết các bước chi tiết, sung sướng xem Cách biến đổi công thức thành quý hiếm trong Excel.
Lựa chọn tự dưng trong Excel: hàng, cột hoặc ô
Hoạt động trên toàn bộ các phiên bản Excel 365, Excel 2019, Excel 2016, Excel 2013 và Excel 2010.
Nếu bạn có của shop chúng tôi Suite Ultimate được thiết lập trong Excel của bạn, sau đó chúng ta có thể lấy chủng loại ngẫu nhiên bởi một cú bấm vào thay vị công thức. Đây là phương pháp thực hiện:
Trên Công cầm 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 chống của xẻ trợ, hãy làm như sau:Chọn xem bạn có nhu cầu chọn hàng, cột hoặc ô ngẫu nhiên.Xác định form size mẫu: hoàn toàn có thể là tỷ lệ hoặc số.Nhấn vào Lựa chọn dòng 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 hình ảnh bên dưới, một mẫu thiên nhiên được lựa chọn trực tiếp trong tập tài liệu của bạn. Nếu bạn có nhu cầu sao chép nó chỗ nào đó, chỉ việc nhấn một phím tắt xào luộc thông thường (Ctrl + C).
Nếu các bạn quan tâm, hãy thử kỹ năng này cùng hơn 300 tính năng hoàn hảo nhất khác được bao hàm trong Suite Ultimate, bạn đều được mừng đón tải xuống phiên bản dùng thử 14 ngày. Nếu bạn thích các phương tiện và đưa ra quyết định lấy giấy phép, đừng bỏ qua chương trình giảm ngay 15% dành cho độc mang blog của bọn 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 thiên nhiên trong Excel cơ mà không có bạn dạng sao. Tôi cảm ơn các bạn đã gọi và mong muốn sẽ chạm chán bạn bên trên blog của shop chúng tôi vào tuần tới!