Có thể bạn chưa biết Excel hỗ trợ một số hàm tạo ngày tháng, mật khẩu, chuỗi văn bản ngẫu nhiên. Đôi khi chúng ta cần sử dụng các hàm ngẫu nhiên này thế nhưng ko phải ai cũng biết dùng chúng. Trong bài viết dưới đây thủ thuật vn sẽ giải thích các đặc trưng của thuật toán tạo số ngẫu nhiên của Excel và chỉ cho các bạn cách sử dụng các hàm RAND và RANDBETWEEN trong Excel để tạo ra các số, ngày tháng, mật khẩu và các chuỗi văn bản ngẫu nhiên khác.
Dữ liệu ngẫu nhiên là gì?
Trước khi chúng ta nghiên cứu các kỹ thuật tạo ra các số ngẫu nhiên khác nhau trong Excel, chúng ta hãy xác định chúng là gì. Nói một cách đơn giản, dữ liệu ngẫu nhiên là một chuỗi các con số, chữ cái hoặc các ký hiệu không có khuôn mẫu.
Hàm RAND - Hàm tạo số ngẫu nhiên
Hàm RAND trong Excel là 1 trong hai hàm được thiết kế đặc biệt để tạo ra các số ngẫu nhiên. Nó trả về một số thập phân ngẫu nhiên (số thực) giữa 0 và 1.
RAND () là một hàm bất định, có nghĩa là một số ngẫu nhiên mới được tạo ra mỗi khi bảng tính được tính toán. Và điều này xảy ra mỗi khi bạn thực hiện bất kỳ hành động nào trên bảng tính, ví dụ cập nhật công thức (không nhất thiết là công thức RAND, chỉ cần bất kỳ công thức nào khác trên một trang tính), chỉnh sửa một ô hoặc nhập dữ liệu mới.
Hàm RAND có sẵn trong tất cả các phiên bản Excel 2016, Excel 2013, Excel 2010, 2007, 2003, XP và 2000.
Vì hàm RAND không có đối số, bạn chỉ cần nhập =RAND() vào ô và sao chép công thức vào nhiều ô nếu bạn muốn:
Và bây giờ, chúng ta hãy đi thêm một bước nữa và viết một vài công thức RAND để tạo các số ngẫu nhiên theo các điều kiện của bạn.
Xác định giới hạn trên của dải số ngẫu nhiên khi dùng RAND
Để tạo các số ngẫu nhiên giữa số không và bất kỳ giá trị N nào, bạn có hàm RAND nhân N:
= RAND () * N
Ví dụ, để tạo một dãy số ngẫu nhiên lớn hơn hoặc bằng 0 nhưng dưới 100, hãy sử dụng công thức sau:
=RAND() * 100
Chú ý. Giá trị ràng buộc phía trên không xuất hiện trong chuỗi ngẫu nhiên trả về. Ví dụ, nếu bạn muốn nhận được số ngẫu nhiên từ 0 đến 10, bao gồm 10, công thức đúng là =RAND() * 11.
Tạo số ngẫu nhiên giữa 2 số dùng RAND
Để tạo một số ngẫu nhiên giữa hai số nào mà bạn chỉ định, hãy sử dụng công thức RAND sau:
= RAND () * ( B – A ) + A
Trong đó A là giá trị giới hạn dưới (số nhỏ nhất) và B là giá trị giới hạn trên (số lớn nhất).
Ví dụ: để tạo các số ngẫu nhiên giữa 10 và 50, bạn có thể sử dụng công thức sau:
=RAND() * (50-10) + 10
Chú ý. Công thức ngẫu nhiên này sẽ không trả lại một số bằng số lớn nhất của dãy được chỉ định ( giá trị B ).
Tạo số nguyên ngẫu nhiên trong Excel
Để làm cho hàm RAND tạo ra các số nguyên ngẫu nhiên, lấy một trong hai công thức đã đề cập ở trên và nhúng nó trong hàm INT.
Để tạo số nguyên ngẫu nhiên giữa 0 và 50:
=INT(RAND()*50)
Để tạo các số nguyên ngẫu nhiên từ 10 đến 50:
=INT(RAND()*(50-10)+10)
HÀM RANDBETWEEN- Tạo số ngẫu nhiên trong phạm vi quy định
RANDBETWEEN là một hàm khác được Excel tạo ra để tạo các số ngẫu nhiên. Nó trả về số nguyên ngẫu nhiên trong dãy mà bạn chỉ định:
RANDBETWEEN (bottom , top)
- bottom là số thấp nhất
- top là số cao nhất trong phạm vi số ngẫu nhiên bạn muốn nhận được.
Giống RAND, RANDBETWEEN của là một hàm không bất định và nó trả về một số nguyên ngẫu nhiên mới mỗi khi bảng tính của bạn được tính toán lại.
Ví dụ, để tạo các số nguyên ngẫu nhiên giữa 100 và 500 (kể cả 100 và 500), sử dụng công thức RANDBETWEEN sau:
=RANDBETWEEN(100, 500)
Hàm RANDBETWEEN trong Excel có thể tạo ra cả hai số dương và âm. Ví dụ, để có được một danh sách các số nguyên ngẫu nhiên từ -10 đến 10, hãy nhập công thức sau trong bảng tính của bạn:
=RANDBETWEEN(-10, 10)
Hàm RANDBETWEEN hiện có trong các phiên bản Excel 2016, Excel 2013, Excel 2010 và Excel 2007. Trong các phiên bản cũ, bạn có thể sử dụng công thức RAND thể hiện trong ví dụ 3 ở trên.
Cách sử dụng hàm RANDBETWEEN để tạo các giá trị ngẫu nhiên khác với số nguyên.
Mặc dù hàm RANDBEETWEEN trong Excel được thiết kế để trả về các số nguyên ngẫu nhiên, bạn có thể bắt buộc nó phải trả lại các số thập phân ngẫu nhiên với nhiều độ dài thập phân như bạn muốn.
Ví dụ, để có được một danh sách các số với một vị trí thập phân, bạn nhân các giá trị dưới và đầu trang cho 10, và sau đó chia giá trị trả về cho 10:
= RANDBETWEEN ( bottom value * 10, top value * 10) / 10
Công thức RANDBETWEEN sau đây trả về số thập phân ngẫu nhiên giữa 1 và 50:
=RANDBETWEEN(1*10, 50*10) / 10
Theo cách tương tự, để tạo các số ngẫu nhiên giữa 1 và 50 với 2 số thập phân, bạn nhân các đối số của hàm RANDBETWEEN cho 100, và sau đó chia kết quả cho 100:
=RANDBETWEEN(1*100, 50*100) / 100
Tạo ngày ngẫu nhiên trong Excel
Để trả về một danh sách các ngày ngẫu nhiên giữa hai ngày, sử dụng hàm RANDBETWEEN kết hợp với DATEVALUE :
= RANDBETWEEN (DATEVALUE ( ngày bắt đầu ), DATEVALUE ( ngày kết thúc ))
Ví dụ: để có được danh sách các ngày giữa ngày 1 tháng 6 năm 2020 và ngày 30 tháng 6 năm 2020, hãy nhập công thức sau trong bảng tính của bạn:
=RANDBETWEEN(DATEVALUE(“1/Jun/2020”), DATEVALUE(“30/Jun/2020”))
Ngoài ra, bạn có thể sử dùng hàm DATE thay vì DATEVALUE:
=RANDBETWEEN(DATE(2020,6,1),DATE(2020,6,30))
Hãy nhớ áp dụng định dạng ngày cho (các) ô và bạn sẽ nhận được một danh sách các ngày ngẫu nhiên tương tự như sau:
Chèn thời gian ngẫu nhiên trong Excel
Nhớ rằng trong khoảng thời gian của Excel được lưu dưới dạng thập phân, bạn có thể sử dụng hàm RAND chuẩn để chèn các số thực ngẫu nhiên và sau đó chỉ cần áp dụng định dạng thời gian vào các ô:
Để trả lại thời gian ngẫu nhiên theo tiêu chí của bạn, cần các công thức ngẫu nhiên cụ thể hơn, như được trình bày dưới đây.
Tạo thời gian ngẫu nhiên trong phạm vi chỉ định
Để chèn ngẫu nhiên thời gian giữa bất kỳ hai thời gian mà bạn chỉ định, sử dụng hàm TIME hoặc TIMEVALUE kết hợp với RAND:
= TIME ( thời gian bắt đầu ) + RAND () * (TIME ( thời gian bắt đầu ) – TIME ( thời gian kết thúc ))
= TIMEVALUE ( thời gian bắt đầu ) + RAND () * (TIMEVALUE ( thời gian bắt đầu ) – TIMEVALUE ( thời gian kết thúc ))
Ví dụ: để chèn thời gian ngẫu nhiên giữa 6 giờ sáng đến 5 giờ 30 tối, bạn có thể sử dụng một trong hai công thức sau:
=TIME(6,0,0) + RAND() * (TIME(17,30,0) – TIME(6,0,0))
=TIMEVALUE(“6:00 AM”) + RAND() * (TIMEVALUE(“5:30 PM”) – TIMEVALUE(“6:00 AM”))
Tạo ngày và thời gian ngẫu nhiên
Để tạo một danh sách các ngày tháng và thời gian ngẫu nhiên , hãy sử dụng kết hợp các hàm RANDBETWEEN và DATEVALUE:
= RANDBETWEEN (DATEVALUE ( ngày bắt đầu) , DATEVALUE ( ngày kết thúc )) + RANDBETWEEN (TIMEVALUE ( thời gian bắt đầu ) * 10000, TIMEVALUE ( thời gian kết thúc ) * 10000) / 10000
Giả sử bạn muốn chèn các ngày ngẫu nhiên từ ngày 1 tháng 6 năm 2015 đến ngày 30 tháng 6 năm 2015 với thời gian từ 7 giờ 30 sáng đến 6 giờ chiều, ta có công thức sau:
=RANDBETWEEN(DATEVALUE(“1-Jun-2015”), DATEVALUE(“30-Jun-2015”)) + RANDBETWEEN(TIMEVALUE(“7:30 AM”) * 10000, TIMEVALUE(“6:00 PM”) * 10000) / 10000
Bạn cũng có thể thay thời gian và ngày sử dụng với hàm DATE và TIME, tương ứng:
=RANDBETWEEN(DATE(2015,6,1), DATE(2015,6,30)) + RANDBETWEEN(TIME(7,30,0) * 10000, TIME(18,0,0) * 10000) / 10000
Tạo các ký tự ngẫu nhiên trong Excel
Để trả về một chữ cái ngẫu nhiên, cầu một sự kết hợp của ba hàm khác nhau:
=CHAR(RANDBETWEEN(CODE("A"),CODE("Z")))
- Với A là ký tự đầu tiên
- Z là ký tự cuối cùng trong dãy chữ cái bạn muốn (theo thứ tự chữ cái).
Trong công thức trên:
- CODE trả về các mã ANSI số cho các chữ cái được chỉ định.
- RANDBETWEEN lấy các số được trả về bởi các hàm CODE dưới dạng các giá trị trên và dưới cùng của dãy.
- CHAR chuyển đổi các mã ANSI ngẫu nhiên được RANDBETWEEN trả lại cho các chữ cái tương ứng.
Chú ý. Vì các mã ANSI phân biệt chữ hoa và chữ thường, công thức này là chữ hoa .
Nếu bạn nhớ mã ký tự ANSI trong đầu, thì bạn có thể ghi trực tiếp trong hàm RANDBETWEEN.
Ví dụ: để tạo chữ Hoa ngẫu nhiên giữa A (ANSI code 65) và Z (ANSI code 90), bạn viết:
=CHAR(RANDBETWEEN(65, 90))
Để tạo các chữ thường từ a (ANSI code 97) đến z (mã ANSI 122), bạn sử dụng công thức sau:
=CHAR(RANDBETWEEN(97, 122))
Để chèn một ký tự đặc biệt ngẫu nhiên, chẳng hạn như ! “# $% & ‘() * +, -. /, Sử dụng hàm RANDBETWEEN với tham số dưới cùng là 33 (mã ANSI cho”! “) Và tham số đầu đặt là 47 (mã ANSI cho” / “).
=CHAR(RANDBETWEEN(33,47))
Tạo chuỗi văn bản và mật khẩu ngẫu nhiên trong Excel
Để tạo một chuỗi văn bản ngẫu nhiên trong Excel, bạn chỉ cần nối một vài hàm CHAR / RANDBEETWEEN.
Ví dụ: để tạo danh sách mật khẩu gồm 4 ký tự, bạn có thể sử dụng công thức tương tự như sau:
=RANDBETWEEN(0,9) & CHAR(RANDBETWEEN(65,90)) & CHAR(RANDBETWEEN(97, 122)) & CHAR(RANDBETWEEN(33,47))
Để làm cho công thức thêm gọn nhẹ, tôi cung cấp các mã ANSI trực tiếp trong công thức. Bốn hàm trả về các giá trị ngẫu nhiên sau:
- RANDBETWEEN(0,9) – trả về số ngẫu nhiên từ 0 đến 9.
- CHAR(RANDBETWEEN(65,90))– trả về chữ hoa ngẫu nhiên giữa A và Z .
- CHAR(RANDBETWEEN(97, 122))– Trả về các ký tự chữ thường ngẫu nhiên giữa a và z .
- CHAR(RANDBETWEEN(33,47)) – Trả về các ký tự đặc biệt ngẫu nhiên.
Các chuỗi văn bản được tạo ra bằng công thức trên sẽ là một cái gì đó như dưới đây
| 6Jo+ | 6Pf) | 3Xz+ | 0Sb( | 1Nk" | 5Pb, | 2Kp& | 3Lj' |
| 6Ek& | 1Jd, | 7Xb& | 5Gu/ | 8Mh- | 7Ru* | 6Hv" | 5Ly% |
| 5Av, | 5Sf! | 7Th# | 2Vo" | 6Zo! | 1Bf) | 1Lf) | 8Sn+ |
| 6Fh! | 3Kg% | 7Zi, | 9Zy% | 0Cs" | 2Tl. | 8Rn, | 1Cf. |
| 8Sh) | 0Qj# | 8Dt! | 8Hh+ | 1Ul- | 0Tc" | 1Tj# | 9Jv$ |
| 6Cw( | 7Bu" | 0Jb# | 3Yq. | 2Gl% | 4Bg% | 6Zh# | 0Yy( |
| 7Zb+ | 0Xz, | 9Dx$ | 2Zx+ | 7Dw! | 9Cz. | 5Qt& | 3Jp' |
| 5Wc' | 5Ye% | 6Rf( | 9Mc) | 4Dv' | 7Mz) | 7Nv+ | 0Kx, |
| 2El$ | 6Ze- | 0Et$ | 6Ex% | 3Zk! | 9Bk& | 9Zn( | 2Uw' |
| 4In* | 4Cg% | 4Zl( | 2Uy, | 5Sp& | 4Ip' | 6Mt% | 0Bv+ |
| 7Qf+ | 7Av% | 8Aa( | 0Ve! | 8Sc# | 9Pt) | 0Eq) | 0Iq* |
| 6Pq) | 6Nn/ | 4Wn$ | 4Ut. | 1Up! | 1Oy* | 5Qo! | 2Hq' |
| 5Hc) | 9Hd- | 6Er* | 2Df% | 2Cd' | 3Yv+ | 6Vc) | 7Rx% |
| 9Zt" | 4Ry, | 5Qb" | 9Ux) | 5Gb* | 0Tt* | 8Pl& | 0Oj( |
| 5Ct- | 7Kz$ | 5Vk% | 9Sk/ | 3Ld' | 1Xq! | 7Ci' | 1On% |
| 1Tb) | 8Mn- | 1Yp+ | 7Zw* | 0Gl/ | 7Qb" | 3Qn' | 1Vn" |
| 6Jn' | 8Tp, | 4Ag/ | 0Dk- | 0Hf. | 3Dj" | 0Aj, | 5Ra# |
Cách ngăn RAND và RANDBETWEEN tính toán lại
Nếu bạn muốn có được một tập hợp các số ngẫu nhiên, ngày hoặc chuỗi văn bản cố định không thay đổi mỗi khi bảng tính được tính lại, hãy sử dụng một trong các kỹ thuật sau:
- Để hàm RAND hoặc RANDBETWEEN dừng tính toán lại trong một ô, hãy chọn ô đó, chuyển sang thanh công thức và nhấn F9 để thay thế công thức bằng giá trị của nó.
- Để ngăn chặn một hàm ngẫu nhiên của Excel tự động cập nhật các giá trị trong một số ô, hãy sử dụng paste Special Chọn tất cả các ô với công thức ngẫu nhiên, nhấn Ctrl + C để sao chép chúng, sau đó nhấp chuột phải vào dãy đã chọn và nhấn Paste Special > Values . Ngoài ra, bạn có thể nhấn Shift + F10 và sau đó V , là phím tắt cho tùy chọn ” Paste special >Values .
Cách tạo các số ngẫu nhiên độc đáo trong Excel
Không những hàm ngẫu nhiên của Excel có thể tạo ra các giá trị ngẫu nhiên độc đáo. Mà nếu ai đó đang tìm kiếm một bộ phát số ngẫu nhiên duy nhất trong Excel, bạn có thể sử dụng cách t sau đây.
- Sử dụng hàm RAND hoặc RANDBETWEEN để tạo ra một danh sách các giá trị ngẫu nhiên. Tạo ra nhiều giá trị hơn bạn thực sự cần vì một số bản sao sé được xóa sau.
- Chuyển đổi công thức thành các giá trị như được giải thích trong Làm thế nào để ngăn hàm ngẫu nhiên của Excel tính lại .
- Loại bỏ các giá trị được sao chép bằng cách sử dụng Duplicate Remover tích hợp trong Excel ( Tab Data > Remove Duplicates).
Trên đây là cách sử dụng các hàm tạo dữ liệu ngẫu nhiên bằng hàm RAND và RANDBETWEEN toàn tập. Hi vọng với bài hướng dẫn này các bạn đã biết cách sử dụng các hàm tạo ngẫu nhiên này. Chúc các bạn thành công!
