Công thức chung
=INDEX(characters,RANDARRAY(n,1,1,count,TRUE))
Tóm lược
Để tạo danh sách các chuỗi văn bản ngẫu nhiên, bạn có thể sử dụng công thức dựa trên INDEX, RANDARRAY và TEXTJOIN. Trong ví dụ được hiển thị, công thức trong D5 là:
=TEXTJOIN("",1,INDEX(chars,RANDARRAY(6,1,1,26,TRUE)))
trong đó ký tự là dải ô được đặt tên B5: B30 chứa các chữ cái AZ. Khi công thức được sao chép xuống cột, nó tạo ra một chuỗi văn bản 6 ký tự mới trên mỗi dòng.
Giải trình
Công thức mảng động mới trong Excel 365 giúp giải quyết một số vấn đề phức tạp nhất định với công thức dễ dàng hơn nhiều.
Trong ví dụ này, mục tiêu là tạo một danh sách các mã gồm 6 ký tự ngẫu nhiên. Tính ngẫu nhiên được xử lý bởi hàm RANDARRAY, một hàm mới trong Excel 365. Hàm RANDARRAY trả về 6 số ngẫu nhiên cho INDEX, sau đó lấy 6 giá trị ngẫu nhiên từ các ký tự phạm vi được đặt tên. Các kết quả từ INDEX sau đó được nối với nhau bằng hàm TEXTJOIN.
Trong ví dụ được hiển thị, công thức trong D5 là:
=TEXTJOIN("",1,INDEX(chars,RANDARRAY(6,1,1,26,TRUE)))
Làm việc từ trong ra ngoài, hàm RANDARRAY được sử dụng để tạo một mảng chứa sáu số ngẫu nhiên trong khoảng từ 1-26:
RANDARRAY(6,1,1,26,TRUE) // return array like (14;5;21;7;25;3)
Lưu ý rằng mảng được trả về sẽ thay đổi theo từng trường hợp của hàm RANDARRAY. Ngoài ra, vì RANDARRAY là một hàm dễ bay hơi, nó sẽ tính toán lại với mỗi lần thay đổi trang tính.
Mảng số ngẫu nhiên này được trả về trực tiếp cho hàm INDEX dưới dạng đối số hàng:
INDEX(chars,(14;5;21;7;25;3))
Bởi vì chúng tôi đang yêu cầu INDEX cho 6 hàng, chúng tôi nhận được 6 kết quả trong một mảng như sau:
("N","E","U","G","Y","C")
Mảng này được trả về hàm TEXTJOIN dưới dạng đối số text1:
=TEXTJOIN("",1,("N","E","U","G","Y","C")) // returns "NEUGYC"
TEXTJOIN được thiết lập để sử dụng một chuỗi trống làm dấu phân cách và bỏ qua các giá trị trống. Với cấu hình này, TEXJOIN chỉ cần nối tất cả các giá trị với nhau và trả về một chuỗi văn bản gồm 6 ký tự như "NEUGYC".
Đếm ký tự theo chương trình
Thay vì mã hóa cứng kích thước của các ký tự trực tiếp vào hàm RANDARRAY, bạn có thể sử dụng hàm COUNTA để đếm các phần tử trong mảng và trả lại số đó cho RANDARRAY:
RANDARRAY(6,1,1,COUNTA(chars),TRUE)
Điều này giả định rằng các ký tự không chứa bất kỳ ô trống nào.
Tạo ký tự theo chương trình
Vì các chữ cái AZ có các giá trị mã số bên dưới, nên có thể tạo mảng ký tự được sử dụng để tập hợp các chuỗi văn bản theo chương trình, thay vì sử dụng một dải ô. Điều này có thể được thực hiện với hàm CHAR và hàm SEQUENCE.
Để tạo một mảng có tất cả các chữ cái viết hoa AZ, ánh xạ tới ASCII 65-90:
=CHAR(SEQUENCE(26,1,65,1)) // returns ("A","B","C",… )
Để tạo các chữ cái thường az, tương ứng với ASCII 97-122:
=CHAR(SEQUENCE(26,1,97,1)) // returns ("a","b","c",… )
Mã này có thể được đưa vào công thức ban đầu để thay thế các "ký tự" như sau:
=TEXTJOIN("",1,INDEX(CHAR(SEQUENCE(26,1,65,1)),RANDARRAY(6,1,1,26,TRUE)))
Các nhân vật khác
Các ký tự trong các ký tự phạm vi được đặt tên có thể là bất kỳ ký tự nào bạn thích. Nếu bạn thêm nhiều hơn 26 ký tự (hoặc ít hơn), hãy điều chỉnh số 26 cho phù hợp hoặc sử dụng COUNTA như đã giải thích ở trên.
Không có Excel 365
It is possible to generate random text strings without Excel 365, but the formula is more tedious and redundant. Since we don't have a good way to get 6 random numbers all at once, we use the RANDBETWEEN function to get one random value at a time:
=INDEX(chars,RANDBETWEEN(1,26))& INDEX(chars,RANDBETWEEN(1,26))& INDEX(chars,RANDBETWEEN(1,26))& INDEX(chars,RANDBETWEEN(1,26))& INDEX(chars,RANDBETWEEN(1,26))& INDEX(chars,RANDBETWEEN(1,26))
This formula uses the INDEX function to retrieve one random value at a time from the named range chars, and the 6 results are concatenated together into a single text string. Line breaks added for readability.
It is also possible to generate values A-Z directly with CHAR and RANDBETWEEN like this:
=CHAR(RANDBETWEEN(65,90))& CHAR(RANDBETWEEN(65,90))& CHAR(RANDBETWEEN(65,90))& CHAR(RANDBETWEEN(65,90))& CHAR(RANDBETWEEN(65,90))& CHAR(RANDBETWEEN(65,90))
Trong phiên bản này, RANDBETWEEN đang trả về giá trị từ 65 đến 90 (bao gồm) tương ứng với giá trị ASCII cho các chữ cái AZ (viết hoa). Hàm CHAR chuyển giá trị số thành một chữ cái. Như trên, tất cả các kết quả được nối với nhau trong một chuỗi văn bản duy nhất.
