Ngẫu nhiên bằng Excel

Không phải người dùng nào cũng có nhu cầu về số ngẫu nhiên trong Excel. Hầu hết mọi người làm việc với các số và công thức cố định và có thể không cần các số ngẫu nhiên xuất hiện trong báo cáo của họ

Tuy nhiên, một trình tạo số ngẫu nhiên có công dụng rất lớn khi làm việc với các tình huống khác nhau trên một tập hợp dữ liệu hoặc khi thực hiện các phân tích thống kê khác nhau

Một mô hình tài chính có thể sử dụng mô phỏng ngẫu nhiên phụ thuộc vào xác suất. Mô hình có thể cần được chạy hàng nghìn lần, nhưng với trình tạo số ngẫu nhiên cung cấp các tham số của mỗi mô phỏng

Bất kể nhu cầu của bạn về số ngẫu nhiên là gì, Excel có một số cách để tạo chúng

Trong bài đăng này, tôi sẽ chỉ cho bạn tất cả các phương pháp bạn có thể sử dụng để chèn các số ngẫu nhiên vào sổ làm việc của mình

Tạo số ngẫu nhiên bằng hàm RAND

Cách đầu tiên tôi sẽ chỉ cho bạn là cách dễ nhất để tạo các giá trị ngẫu nhiên trong Excel

Có một hàm RAND rất đơn giản không yêu cầu tham số và sẽ tạo một số ngẫu nhiên trong khoảng từ 0 đến 1

Cú pháp hàm RAND

= RAND ( )

Hàm này không có đối số bắt buộc hoặc tùy chọn. Hàm luôn được nhập với một bộ dấu ngoặc đơn trống

Hàm này sẽ tạo ra một số thập phân ngẫu nhiên trong khoảng từ 0 đến 1, nhưng không bao gồm 0 hoặc 1

Các giá trị lặp lại có thể xảy ra nhưng không chắc vì hàm RAND tạo ra các số từ một dãy số liên tục

Các giá trị được trả về sẽ tuân theo phân phối đồng đều. Điều này có nghĩa là bất kỳ số nào trong khoảng từ 0 đến 1 đều có khả năng được trả về như nhau

Tạo số ngẫu nhiên giữa hai số bất kỳ

Số thập phân từ 0 đến 1 có thể không quá hữu ích nếu bạn cần các số từ 1 đến 10

Nhưng bạn có thể sử dụng một công thức đơn giản liên quan đến hàm RAND để tạo các số ngẫu nhiên giữa hai số bất kỳ

= RAND ( ) * ( Y - X ) + X

Nói chung, bạn có thể tạo một số ngẫu nhiên giữa X và Y bằng cách sử dụng công thức trên

= RAND ( ) * 9 + 1

Ví dụ: để tạo các số từ 1 đến 10, bạn có thể sử dụng công thức trên

Điều này nhân số ngẫu nhiên được tạo bởi 9 và sau đó thêm 1 vào số đó. Điều này sẽ tạo ra các số thập phân từ 1 đến 10

Tạo số nguyên ngẫu nhiên giữa hai số bất kỳ

Một nhu cầu khác mà bạn có thể gặp phải là tạo các số nguyên ngẫu nhiên giữa hai số đã cho. Điều này cũng có thể được thực hiện bằng cách sử dụng một công thức đơn giản

= ROUND ( RAND ( ) * ( Y - X ) + X, 0 )

Nói chung, bạn có thể sử dụng công thức trên để tạo số nguyên ngẫu nhiên giữa hai giá trị X và Y

= ROUND ( RAND ( ) * 9 + 1, 0 )

Ví dụ: công thức trên sẽ tạo ra các số nguyên ngẫu nhiên trong khoảng từ 1 đến 10

Đây là công thức giống như trước, nhưng sử dụng hàm ROUND để làm tròn đến 0 chữ số thập phân

Bạn có thể sao chép công thức này xuống cột trên bảng tính và nếu bạn tiếp tục nhấn F9 để tính lại, bạn sẽ thấy các tổ hợp số khác nhau từ 1 đến 10

Vì tập hợp các số có thể là rời rạc, nên các số ngẫu nhiên được tạo có thể được nhân đôi trong danh sách, tùy thuộc vào mức tối thiểu và tối đa của phạm vi

= ROUND ( RAND ( ) * ( 4 - -3 ) + -3, 0 )

Điều này cũng hoạt động để tạo ra số âm. Giả sử bạn cần tạo các số nguyên ngẫu nhiên trong khoảng từ -3 đến 4, thì công thức trên sẽ là thứ bạn cần

Nhân hàm RAND với 7 sẽ tạo ra các số ngẫu nhiên trong khoảng từ 0 đến 7. Thêm -3 vào kết quả và làm tròn đến 0 chữ số thập phân, điều này sẽ cho dãy số ngẫu nhiên từ -3 đến 4

Tạo số ngẫu nhiên bằng Hàm RANDBETWEEN

Excel có một chức năng hữu ích để tạo các số ngẫu nhiên trong phạm vi số trên và số dưới

Điều này dễ sử dụng hơn so với sử dụng hàm RAND vì nó bao gồm các toán tử bổ sung để đi đến phạm vi cụ thể của bạn

Cú pháp hàm RANDBETWEEN

= RANDBETWEEN ( bottom, top )

• dưới cùng là phạm vi thấp hơn cho các giá trị trả về
• top là phạm vi trên cho các giá trị trả về

Cả hai đối số này đều bắt buộc

Hàm này sẽ tạo ra các số nguyên ngẫu nhiên giữa các giá trị dưới cùng và trên cùng. Hàm này cũng sẽ trả về các giới hạn trên và dưới ở mức có thể vì nó không hoàn toàn nằm trong hàm này

Ví dụ với Hàm RANDBETWEEN

= RANDBETWEEN ( -3, 4 )

Ví dụ: nếu bạn muốn các số ngẫu nhiên trong khoảng từ -3 đến 4, như trong ví dụ trước, bạn có thể sử dụng công thức trên

Lưu ý rằng hàm RANDBETWEEN chỉ có thể tạo ra các số nguyên. Không có cách nào để làm cho hàm tạo số thập phân. Tuy nhiên, nó ít phức tạp hơn nhiều so với việc sử dụng hàm RAND với các toán tử để đạt được kết quả tương tự

Tạo số ngẫu nhiên bằng Hàm RANDARRAY

Thông thường, đó là trường hợp bạn không muốn chỉ một giá trị ngẫu nhiên mà là toàn bộ tập hợp các giá trị ngẫu nhiên

Hàm RANDARRAY là giải pháp hoàn hảo cho việc này

Nó sẽ điền vào một dải ô có một dãy số ngẫu nhiên, có thể rất mạnh

Hàm này chỉ có trên phiên bản Microsoft 365 của Excel

Cú pháp của hàm RANDARRAY

________số 8_______

• Rows là số hàng cần trả về
• Cột là số cột cần trả về
• Min là giá trị nhỏ nhất cho các số ngẫu nhiên
• Max là giá trị tối đa cho các số ngẫu nhiên
• Whole_Number là TRUE để trả về số nguyên và FALSE để trả về số thập phân

Tất cả các đối số là tùy chọn cho chức năng này

Nếu không có tham số nào được đưa vào, bạn sẽ nhận được một số ngẫu nhiên có chữ số thập phân, giống như hàm RAND

Ví dụ với hàm RANDARRAY

= RANDARRAY ( 4, 3, 6, 14, TRUE )

Để tạo một mảng gồm 4 hàng và 3 cột gồm các số nguyên ngẫu nhiên trong khoảng từ 6 đến 14, bạn có thể sử dụng công thức trên

Điều này sẽ tạo ra một mảng các giá trị. Chú ý đường viền màu xanh xung quanh các con số?

Lưu ý rằng góc trên cùng bên trái của mảng luôn được neo vào ô chứa công thức. Nhấn F9 để tính toán lại bảng tính sẽ thay đổi tất cả các số trong mảng

Nếu bạn không đặt giá trị tối thiểu hoặc tối đa, giá trị mặc định từ 0 đến 1 sẽ được sử dụng

Giá trị nhỏ nhất phải nhỏ hơn giá trị lớn nhất nếu không sẽ có lỗi #VALUE. lỗi

Mảng sẽ tự động thay đổi kích thước nếu bạn thay đổi tham số hàng hoặc cột trong công thức RANDARRAY. Đây là lý do tại sao chúng được gọi là mảng động

Cảnh báo. Nếu đã có dữ liệu trong một trong các ô trong phạm vi đầu ra mà bạn đã nhập, bạn sẽ nhận được lỗi #SPILL. lỗi. Không có dữ liệu sẽ được ghi đè

Tạo số ngẫu nhiên bằng phần bổ trợ công cụ phân tích

Có một phương pháp khác có thể được sử dụng để chèn số ngẫu nhiên mà không cần sử dụng công thức

Bạn có thể sử dụng add-in để tạo số ngẫu nhiên. Excel đi kèm với phần bổ trợ Công cụ phân tích Pak, nhưng bạn sẽ cần phải cài đặt phần bổ trợ này trước khi có thể sử dụng

Cài đặt Toolpak phân tích

Dưới đây là các bước để cài đặt bổ trợ Công cụ phân tích Pak

1. Bấm vào tab Tệp trong ruy-băng

1. Trong ngăn bên trái phía dưới của cửa sổ, cuộn xuống và nhấp vào Tùy chọn. Bạn cũng có thể sử dụng phím tắt Alt, F, T từ cửa sổ bảng tính để mở cửa sổ Tùy chọn

1. Trong ngăn bên trái của cửa sổ bật lên, nhấp vào Add-Ins
2. Ở dưới cùng của cửa sổ chính được hiển thị, chọn Phần bổ trợ Excel từ danh sách thả xuống và nhấp vào nút Bắt đầu

1. Thao tác này sẽ hiển thị cửa sổ bật lên chứa tất cả các phần bổ trợ có sẵn cho Excel. Chọn hộp cho ToolPak Phân tích và sau đó nhấp vào OK

1. Trên ruy-băng Excel, trên tab Dữ liệu, hiện có một nhóm bổ sung có tên là Phân tích với một nút có tên là Phân tích dữ liệu

Tạo số ngẫu nhiên bằng Toolpak phân tích

Nhấp vào nút Phân tích dữ liệu trong nhóm Phân tích

Điều này sẽ hiển thị một cửa sổ bật lên. Cuộn xuống và chọn tùy chọn Tạo số ngẫu nhiên rồi nhấp vào OK

Một cửa sổ bật lên mới sẽ xuất hiện nơi bạn có thể nhập các tham số của mình để tạo các số ngẫu nhiên

Có một số cài đặt có thể được tùy chỉnh

• Số biến Đây là số cột của các số ngẫu nhiên mà bạn muốn trong bảng đầu ra của mình. Nếu để trống, thì tất cả các cột sẽ được điền vào phạm vi đầu ra mà bạn chỉ định
• Số lượng số ngẫu nhiên Đây là số hàng số ngẫu nhiên mà bạn muốn tạo. Nếu để trống, phạm vi đầu ra mà bạn chỉ định sẽ được lấp đầy
• Phân phối Bạn có thể chọn một số phương thức phân phối từ trình đơn thả xuống, chẳng hạn như phân phối đồng đều hoặc bình thường. Các tùy chọn khác nhau sẽ có sẵn trong phần Tham số tùy thuộc vào lựa chọn của bạn tại đây
• Tham số Nhập các giá trị để mô tả phân phối đã chọn
• Hạt giống ngẫu nhiên Đây là tùy chọn và sẽ là điểm bắt đầu để thuật toán tạo ra các số ngẫu nhiên. Nếu bạn sử dụng lại cùng một hạt giống, nó sẽ tạo ra các số ngẫu nhiên giống nhau. Nếu để trống, nó sẽ lấy giá trị hạt giống từ sự kiện hẹn giờ
• Phạm vi đầu ra Nhập ô phía trên bên trái của vị trí bảng sẽ được tạo trong bảng tính. Nếu bạn để trống tham số Biến, thì bạn sẽ cần chỉ định toàn bộ phạm vi. Lưu ý rằng dữ liệu hiện có trong phạm vi đó sẽ bị ghi đè
• New Worksheet Ply Tùy chọn này sẽ chèn một trang tính mới trong sổ làm việc và sẽ dán kết quả vào Ô A1. Nhập tên trang tính vào hộp bên cạnh, nếu không, tên mặc định sẽ được sử dụng
• Sổ làm việc mới Thao tác này sẽ tạo một sổ làm việc mới và dán kết quả vào ô A1 trong trang tính đầu tiên

Nhấn nút OK và Excel sẽ chèn số ngẫu nhiên theo các tùy chọn đã chọn

Lưu ý rằng không giống như các phương pháp công thức được hiển thị trước đây, những con số này được mã hóa cứng và sẽ không thay đổi khi bạn làm mới các phép tính trong sổ làm việc

Tạo số ngẫu nhiên với VBA

VBA (Visual Basic for Applications) là ngôn ngữ lập trình nằm phía sau giao diện người dùng của Excel và ngôn ngữ này cũng có thể được sử dụng để tạo số ngẫu nhiên

Tuy nhiên, nó phức tạp hơn việc chỉ nhập công thức vào một ô trong Excel và bạn cần có một số kiến ​​thức lập trình để sử dụng nó

Để mở trình soạn thảo VBA, hãy sử dụng phím tắt Alt + F11

Trong ngăn bên trái của cửa sổ (Project Explorer), bạn sẽ thấy các sổ làm việc đang mở (bao gồm cả phần bổ trợ) và các trang tính có sẵn

Trên menu ở đầu cửa sổ, nhấp vào Chèn rồi nhấp vào Mô-đun. Điều này sẽ thêm một cửa sổ mô-đun vào bảng tính hiện tại. Dán hoặc thêm đoạn mã sau vào mô-đun

= RAND ( ) * ( Y - X ) + X

0

Nhấn F5 để chạy cái này và một thông báo bật lên sẽ xuất hiện trong Excel với một số ngẫu nhiên được hiển thị. Nhấn OK và bạn sẽ quay lại cửa sổ mã

Chạy lại mã và một số ngẫu nhiên khác sẽ được hiển thị. Số ngẫu nhiên sẽ nằm trong khoảng từ 0 đến 1, nhưng sẽ không bao gồm các giá trị 0 hoặc 1

Bạn cũng có thể cung cấp cho hàm Rnd một tham số, là hạt giống cho điểm bắt đầu của thuật toán để tạo ra các số ngẫu nhiên

Nếu giá trị hạt giống được đặt thành số âm hoặc số không, thì cùng một số ngẫu nhiên sẽ được hiển thị mỗi lần

Sử dụng các hàm VBA, bạn có thể mô phỏng tất cả các chức năng của các phương pháp giao diện người dùng đã được đề cập trong bài viết này

= RAND ( ) * ( Y - X ) + X

1

Ví dụ: nếu bạn muốn tạo toàn bộ số ngẫu nhiên từ 3 đến 10, thì bạn sẽ sử dụng đoạn mã trên

Mã này nhân số ngẫu nhiên với 7, sau đó cộng 3 vào số đó, sau đó làm tròn đến 0 chữ số thập phân

Giả sử rằng sau đó bạn muốn hiển thị các số ngẫu nhiên của mình trong lưới. Bạn có thể làm điều này với đoạn mã sau

= RAND ( ) * ( Y - X ) + X

2

Mã này sử dụng vòng lặp For Next để lặp lại 5 lần thông qua phép tính số ngẫu nhiên và nhập kết quả vào một cột gồm các ô bắt đầu từ ô A1.

Hãy nhớ rằng mọi dữ liệu đã có sẽ bị ghi đè và không có tính năng cảnh báo hoặc hoàn tác nào khả dụng. Lưu trước mọi công việc trước đó

= RAND ( ) * ( Y - X ) + X

3

Ngoài ra còn có một chức năng VBA được gọi là Randomize. Bạn có thể sử dụng giá trị này trước hàm Rnd để đặt lại giá trị gốc cho sự kiện hẹn giờ hoặc cho bất kỳ tham số nào đã cho

Tạo số ngẫu nhiên mà không trùng lặp hoặc lặp lại

Bạn có thể gặp trường hợp muốn tạo một dãy số ngẫu nhiên, nhưng bạn không muốn thấy bất kỳ giá trị trùng lặp nào xuất hiện

Bạn có thể muốn chọn 3 số ngẫu nhiên giữa các số từ 1 đến 10, nhưng mỗi số trong 3 số được chọn là duy nhất

Bạn có thể tạo các số ngẫu nhiên bằng hàm RANDBETWEEN, sau đó sử dụng hàm Excel Loại bỏ các số trùng lặp khỏi dải băng, nhưng điều này vẫn có thể không cung cấp cho bạn tất cả các số cần thiết

Có một số giải pháp khả thi

Giải pháp với RANK. Chức năng EQ và COUNTIF

Nếu bạn không có quyền truy cập vào hàm RANDARRAY trong Excel thì bạn có thể sử dụng kết hợp RANK. EQ và COUNTIF để nhận các số ngẫu nhiên duy nhất

Bạn có thể tạo các số ngẫu nhiên của mình bằng cách sử dụng RANDBETWEEN và sau đó sử dụng công thức trong cột tiếp theo để xếp hạng chúng, do đó cung cấp cho bạn một chuỗi được sắp xếp ngẫu nhiên từ 1 đến 10

= RAND ( ) * ( Y - X ) + X

4

Tại ô B2, nhập công thức trên. Sao chép công thức này xuống để có 10 hàng số ngẫu nhiên đi xuống ô B11

Bạn sẽ nhận thấy rằng một số số có thể bị trùng lặp và một số hoàn toàn không được hiển thị

Sau đó, bạn có thể sử dụng RANK. Chức năng EQ để xếp hạng chúng để tạo ra một chuỗi từ 1 đến 10 nhưng được sắp xếp ngẫu nhiên

= RAND ( ) * ( Y - X ) + X

5

Trong ô C2, nhập công thức trên

Lưu ý rằng có các tham chiếu tuyệt đối được sử dụng (dấu $) để các tham chiếu công thức được cố định khi bạn sao chép công thức xuống

Sao chép công thức này xuống ô C11 và điều này sẽ hiển thị tất cả các số từ 1 đến 10, nhưng theo thứ tự ngẫu nhiên

Để giải thích sâu hơn về công thức này, nó sử dụng hai hàm RANK. EQ và COUNTIF

= RAND ( ) * ( Y - X ) + X

6

• Number là số mà ta muốn tìm thứ hạng của trong mảng
• Tham chiếu là mảng mà chúng tôi muốn tìm kiếm số
• Thứ tự là tùy chọn và cho phép bạn tìm thứ hạng theo thứ tự tăng dần hoặc giảm dần. Nếu nó bị bỏ qua thì thứ tự tăng dần được sử dụng

Cấp bậc. Hàm EQ trả về thứ hạng của một số trong một mảng số

= RAND ( ) * ( Y - X ) + X

7

• Phạm vi là phạm vi đang được tìm kiếm cho các trường hợp của tiêu chí
• Tiêu chí là giá trị để khớp trong phạm vi

Hàm COUNTIF đếm số ô dựa trên tiêu chí cho trước. Trong trường hợp này, nó đang đếm số lần một số ngẫu nhiên đã cho xuất hiện trong danh sách

Đối với mỗi số ngẫu nhiên, RANK. Chức năng EQ sẽ xác định vị trí xếp hạng của nó so với các số ngẫu nhiên khác. Nhưng nếu các số ngẫu nhiên chứa các số trùng lặp, thì chúng sẽ tạo ra một thứ hạng gắn liền

Hàm COUNTIF sẽ bù trừ cho bất kỳ ràng buộc nào trong xếp hạng và sẽ thêm một vào xếp hạng cho mỗi lần số ngẫu nhiên xuất hiện trước đó

Điều này tạo ra một thứ hạng duy nhất trong đó các mối quan hệ không có cùng thứ hạng

Vì thứ hạng này dựa trên một tập hợp các số ngẫu nhiên nên kết quả cũng giống như xếp hạng ngẫu nhiên một danh sách các số từ 1 đến 10

Bây giờ, nếu bạn chỉ muốn 5 số không lặp lại, bạn chỉ cần lấy 5 số đầu tiên từ danh sách xếp hạng

Giải pháp với VBA

Bạn cũng có thể sử dụng VBA để tạo một chuỗi số ngẫu nhiên từ 1 đến 10 mà không trùng lặp

= RAND ( ) * ( Y - X ) + X

8

Mã này lặp qua các giá trị từ 1 đến 5, tạo ra một số ngẫu nhiên trong khoảng từ 1 đến 10 mỗi lần

Nó kiểm tra số ngẫu nhiên để kiểm tra xem nó đã được tạo chưa. Điều này được thực hiện bằng cách nối các số thành công thành một chuỗi và sau đó tìm kiếm chuỗi đó để xem số đó đã được sử dụng chưa

Nếu nó đã được tìm thấy, thì nó sử dụng nhãn Lặp lại để quay lại và tạo lại một số mới. Điều này một lần nữa được kiểm tra rằng nó chưa được sử dụng. Nếu đó là một số mới, thì nó sẽ được thêm vào trang tính

Giải pháp với mảng động

Nếu bạn có mảng động trong Excel, thì có một phương pháp công thức duy nhất để tránh lặp lại các giá trị

Giả sử bạn muốn trả về 5 số từ dãy 1 đến 10. Bạn muốn mỗi số được chọn là duy nhất

Điều này có thể được thực hiện bằng cách sử dụng kết hợp các hàm SEQUENCE, SORTBY, RANDARRAY và INDEX

= RAND ( ) * ( Y - X ) + X

9

Công thức trên tạo dãy số từ 1 đến 10

Sau đó, nó sắp xếp chúng theo một thứ tự ngẫu nhiên bằng cách sử dụng hàm SORTBY và sắp xếp trên một cột gồm các số ngẫu nhiên do hàm RANDARRAY tạo ra. Tác dụng là sắp xếp chuỗi theo thứ tự ngẫu nhiên

Bây giờ nếu bạn muốn lấy 5 số ngẫu nhiên và duy nhất, bạn chỉ cần lấy 5 số đầu tiên trong dãy được sắp xếp ngẫu nhiên

Đây chính xác là những gì chức năng INDEX làm. Phần này của công thức sẽ trả về 5 số đầu tiên từ chuỗi được sắp xếp ngẫu nhiên

Phần kết luận

Có một số cách để tạo số ngẫu nhiên trong Excel

Cho dù bạn cần số nguyên, số thập phân hay dãy số ngẫu nhiên có giới hạn trên và dưới, tiện ích này đều có sẵn. Excel cực kỳ linh hoạt về chủ đề này

Tuy nhiên, hãy nhớ rằng những số này là số giả ngẫu nhiên do thuật toán tạo ra

Mặc dù trình tạo số ngẫu nhiên vượt qua tất cả các bài kiểm tra về tính ngẫu nhiên, nhưng chúng không phải là số ngẫu nhiên thực sự

Để trở thành một số thực sự ngẫu nhiên, nó phải được điều khiển bởi một sự kiện ngẫu nhiên xảy ra bên ngoài môi trường máy tính

Đối với hầu hết các mục đích xây dựng mô phỏng chung và phân tích thống kê, trình tạo số ngẫu nhiên trong Excel được coi là phù hợp với mục đích này

Bạn đã sử dụng bất kỳ phương pháp nào trong số này để tạo số ngẫu nhiên trong Excel chưa?