Hướng dẫn how to distribute numbers randomly in excel - cách chia số ngẫu nhiên trong excel
|
Có lẽ tôi đang thiếu một cái gì đó rõ ràng, mặc dù điều này dường như không quá đơn giản khi chỉ sử dụng các công thức bảng tính. Show
Nếu danh sách các giá trị của bạn trong A1: A10, thì, trong B1:
và trong B2, công thức mảng **:array formula**:
Sao chép các phần trên xuống B10. Sau đó, bạn có thể sao chép các công thức trong B1: B10 ở bên phải như mong muốn. Trân trọng ** Các công thức mảng không được nhập theo cùng một cách như các công thức 'tiêu chuẩn'. Thay vì nhấn chỉ cần nhập, trước tiên bạn giữ Ctrl và thay đổi, và chỉ sau đó nhấn Enter. Nếu bạn đã thực hiện chính xác, bạn sẽ nhận thấy Excel đặt dấu ngoặc xoăn {} xung quanh công thức (mặc dù không cố gắng tự chèn chúng theo cách thủ công). Hướng dẫn chỉ ra cách tạo các số ngẫu nhiên, sắp xếp ngẫu nhiên danh sách, lựa chọn ngẫu nhiên và gán ngẫu nhiên dữ liệu cho các nhóm. Tất cả đều có chức năng mảng động mới - Randarray. Như bạn có thể biết, Microsoft Excel đã có một vài chức năng ngẫu nhiên - Rand và Randbete giữa. Ý thức trong việc giới thiệu một người khác là gì? Tóm lại, bởi vì nó mạnh hơn nhiều và có thể thay thế cả hai chức năng cũ hơn. Ngoài việc thiết lập các giá trị tối đa và tối thiểu của riêng bạn, nó cho phép bạn chỉ định số lượng hàng và cột để điền và có nên tạo ra số thập phân hoặc số nguyên ngẫu nhiên hay không. Được sử dụng cùng với các chức năng khác, Randarray thậm chí có thể xáo trộn dữ liệu và chọn một mẫu ngẫu nhiên. Chức năng Randarray ExcelHàm Randarray trong Excel trả về một mảng các số ngẫu nhiên giữa bất kỳ hai số nào bạn chỉ định. Đây là một trong sáu hàm mảng động mới được giới thiệu trong Microsoft Excel 365. Kết quả là một mảng động tràn vào số lượng hàng và cột được chỉ định tự động. Hàm có cú pháp sau. Xin lưu ý rằng tất cả các đối số là tùy chọn: Randarray ([hàng], [cột], [min], [max], [toàn bộ_number]) Where: Hàng (tùy chọn) - Xác định số lượng hàng để điền. Nếu bị bỏ qua, mặc định là 1 hàng. (optional) - defines how many rows to fill. If omitted, defaults to 1 row. Các cột (Tùy chọn) - Xác định số lượng cột để điền. Nếu bị bỏ qua, mặc định là 1 cột. (optional) - defines how many columns to fill. If omitted, defaults to 1 column. Min (tùy chọn) - Số ngẫu nhiên nhỏ nhất để sản xuất. Nếu không được chỉ định, giá trị 0 mặc định được sử dụng. (optional) - the smallest random number to produce. If not specified, the default 0 value is used. Tối đa (Tùy chọn) - Số ngẫu nhiên lớn nhất để tạo. Nếu không được chỉ định, giá trị 1 mặc định được sử dụng. (optional) - the largest random number to create. If not specified, the default 1 value is used. Whole_number (Tùy chọn) - Xác định loại giá trị nào để trả về: (optional) - determines what kind of values to return:
Chức năng Randarray - Những điều cần nhớĐể tạo hiệu quả các số ngẫu nhiên trong bảng tính Excel của bạn, có 6 điểm quan trọng cần chú ý:
Công thức Randarray Excel cơ bảnVà bây giờ, hãy để tôi chỉ cho bạn một công thức excel ngẫu nhiên ở dạng đơn giản nhất của nó. Giả sử bạn muốn lấp đầy một phạm vi bao gồm 5 hàng và 3 cột với bất kỳ số ngẫu nhiên nào. Để hoàn thành nó, hãy thiết lập hai đối số đầu tiên theo cách này:
Tất cả các đối số khác mà chúng tôi để lại các giá trị mặc định của chúng và nhận công thức sau:
Nhập nó vào ô trên cùng bên trái của phạm vi đích (A2 trong trường hợp của chúng tôi), nhấn phím ENTER và bạn sẽ có kết quả bị đổ trên số lượng hàng và cột được chỉ định.Enter key, and you will have the results spilt over the specified number of rows and columns. Như bạn có thể thấy trong ảnh chụp màn hình ở trên, công thức Randarray cơ bản này lấp đầy phạm vi với các số thập phân ngẫu nhiên từ 0 đến 1. Nếu bạn muốn nhận toàn bộ số trong một phạm vi cụ thể, sau đó định cấu hình ba đối số cuối cùng như được trình bày trong các ví dụ khác. Cách ngẫu nhiên trong Excel - Randarray Formula Ví dụDưới đây bạn sẽ tìm thấy một vài công thức nâng cao bao gồm các kịch bản ngẫu nhiên điển hình trong Excel. Tạo số ngẫu nhiên giữa hai sốĐể tạo danh sách các số ngẫu nhiên trong một phạm vi cụ thể, hãy cung cấp giá trị tối thiểu trong đối số thứ 3 và số tối đa trong đối số thứ 4. Tùy thuộc vào việc bạn cần số nguyên hay số thập phân, hãy đặt đối số thứ 5 thành đúng hay sai, tương ứng. Ví dụ, chúng ta hãy điền vào phạm vi 6 hàng và 4 cột với các số nguyên ngẫu nhiên từ 1 đến 100. Đối với điều này, chúng tôi đã thiết lập các đối số sau của hàm Randarray:
Đặt các đối số lại với nhau, chúng tôi có được công thức này:
Và nó tạo ra kết quả sau: Tạo ngày ngẫu nhiên giữa hai ngàyTìm kiếm một trình tạo ngày ngẫu nhiên trong Excel? Chức năng Randarray là một giải pháp dễ dàng! Tất cả những gì bạn phải làm là nhập vào ngày trước (ngày 1) và ngày sau (ngày 2) trong các ô được xác định trước, sau đó tham chiếu các ô đó trong công thức của bạn: Randarray (hàng, cột, ngày1, ngày2, true) Trong ví dụ này, chúng tôi đã tạo một danh sách các ngày ngẫu nhiên giữa các ngày trong D1 và D2 với công thức này:
 Tất nhiên, không có gì ngăn cản bạn cung cấp tối thiểu và tối đa trực tiếp trong công thức nếu bạn muốn. Chỉ cần chắc chắn rằng bạn nhập chúng theo định dạng mà Excel có thể hiểu:
Để ngăn ngừa sai lầm, bạn có thể sử dụng chức năng ngày để nhập ngày:
Ghi chú. Các cửa hàng Excel bên trong ngày là số sê -ri, vì vậy kết quả công thức rất có thể sẽ được hiển thị dưới dạng số. Để hiển thị chính xác kết quả, hãy áp dụng định dạng ngày cho tất cả các ô trong phạm vi tràn. Internally Excel stores dates as serial numbers, so the formula results will most likely be displayed as numbers. To display the results correctly, apply the Date format to all the cells in the spill range. Tạo các ngày làm việc ngẫu nhiên trong ExcelĐể tạo ra những ngày làm việc ngẫu nhiên, hãy nhúng chức năng Randarray vào đối số đầu tiên của ngày làm việc như sau: Ngày làm việc (randarray (hàng, cột, ngày1, ngày2, đúng), 1) Randarray sẽ tạo ra một loạt các ngày bắt đầu ngẫu nhiên, trong đó chức năng ngày làm việc sẽ thêm 1 ngày làm việc và đảm bảo rằng tất cả các ngày được trả lại là ngày làm việc. Với ngày 1 trong D1 và ngày 2 trong D2, đây là công thức để tạo ra danh sách 10 ngày trong tuần:
Như với ví dụ trước, xin vui lòng nhớ định dạng phạm vi tràn như ngày để có kết quả được hiển thị chính xác. Cách tạo số ngẫu nhiên mà không cần sao chépMặc dù Modern Excel cung cấp 6 chức năng mảng động mới, thật không may, vẫn không có chức năng sẵn có để trả về các số ngẫu nhiên mà không có bản sao. Để xây dựng trình tạo số ngẫu nhiên độc đáo của riêng bạn trong Excel, bạn sẽ cần chuỗi một số chức năng với nhau như được hiển thị bên dưới.unique random number generator in Excel, you will need to chain several functions together like shown below. Số nguyên ngẫu nhiên:: Chỉ mục (duy nhất (randarray (n*2, 1, min, tối đa, true)), trình tự (n)) Số thập phân ngẫu nhiên:: Chỉ mục (duy nhất (randarray (n*2, 1, min, tối đa, sai)), trình tự (n)) Where:
Ví dụ: để tạo ra 10 số toàn bộ ngẫu nhiên không có bản sao, hãy sử dụng công thức này:
Để tạo danh sách 10 số thập phân ngẫu nhiên duy nhất, hãy thay đổi đúng thành sai trong đối số cuối cùng của hàm randarray hoặc đơn giản là bỏ qua đối số này:random decimal numbers, change TRUE to FALSE in the last argument of the RANDARRAY function or simply omit this argument:
Mẹo và ghi chú:
Cách sắp xếp ngẫu nhiên trong ExcelĐể xáo trộn dữ liệu trong Excel, hãy sử dụng Randarray cho mảng "Sắp xếp theo" (đối số BY_Array) của hàm sắp xếp. Hàm các hàng sẽ đếm số lượng hàng trong tập dữ liệu của bạn, cho biết có bao nhiêu số ngẫu nhiên để tạo: SortBy (dữ liệu, randarray (hàng (dữ liệu))) Với cách tiếp cận này, bạn có thể sắp xếp ngẫu nhiên một danh sách trong Excel, cho dù nó chứa số, ngày hoặc các mục văn bản:randomly sort a list in Excel, whether it contains numbers, dates or text entries:
Ngoài ra, bạn cũng có thể xáo trộn các hàng mà không cần trộn dữ liệu của mình:shuffle rows without mixing your data:
Làm thế nào để có được một lựa chọn ngẫu nhiên trong ExcelĐể trích xuất một mẫu ngẫu nhiên từ danh sách, đây là một công thức chung để sử dụng: Chỉ mục (dữ liệu, randarray (n, 1, 1, hàng (dữ liệu), true)) Trong đó n là số lượng mục ngẫu nhiên bạn muốn trích xuất. Ví dụ: để chọn ngẫu nhiên 3 tên từ danh sách trong A2: A10, hãy sử dụng công thức này:
Hoặc nhập cỡ mẫu mong muốn trong một số ô, nói C2 và tham chiếu tế bào đó:
Công thức này hoạt động như thế nào: Cốt lõi của công thức này là hàm randarray tạo ra một mảng số nguyên ngẫu nhiên, với giá trị trong C2 xác định số lượng giá trị cần tạo. Số tối thiểu được mã hóa cứng (1) và số tối đa tương ứng với số lượng hàng trong bộ dữ liệu của bạn, được trả về bởi hàm hàng. Mảng các số nguyên ngẫu nhiên đi trực tiếp đến đối số ROW_NUM của hàm chỉ mục, chỉ định các vị trí của các mục để trả về. Đối với mẫu trong ảnh chụp màn hình ở trên, đó là:
Mẹo. Khi chọn một mẫu lớn từ một bộ dữ liệu nhỏ, rất có thể lựa chọn ngẫu nhiên của bạn sẽ chứa nhiều hơn một lần xuất hiện của cùng một mục, bởi vì không có gì đảm bảo rằng Randarray sẽ chỉ tạo ra các số duy nhất. Để ngăn chặn điều này xảy ra, hãy sử dụng phiên bản không trùng lặp của công thức này. When picking a big sample from a small data set, chances are that your random selection will contain more than one occurrence of the same entry, because there is no guarantee that RANDARRAY will produce only unique numbers. To prevent this from happening, use a duplicate-free version of this formula. Cách chọn các hàng ngẫu nhiên trong ExcelNếu bộ dữ liệu của bạn chứa nhiều hơn một cột, thì chỉ định các cột nào sẽ bao gồm trong mẫu. Đối với điều này, cung cấp một hằng số mảng cho đối số cuối cùng (cột_num) của hàm chỉ mục, như thế này:
Trong đó A2: B10 là dữ liệu nguồn và D2 là cỡ mẫu. Kết quả là, lựa chọn ngẫu nhiên của chúng tôi sẽ chứa hai cột dữ liệu: Mẹo. Như trường hợp với ví dụ trước, công thức này có thể trả về các bản ghi trùng lặp. Để đảm bảo rằng mẫu của bạn không lặp lại, hãy sử dụng một cách tiếp cận hơi khác được mô tả trong cách chọn các hàng ngẫu nhiên mà không cần trùng lặp. As is the case with the previous example, this formula may return duplicate records. To ensure that your sample has no repeats, use a slightly different approach described in How to select random rows without duplicates. Cách gán ngẫu nhiên số và văn bản trong excelĐể thực hiện phân công ngẫu nhiên trong Excel, hãy sử dụng Randbet giữa cùng với chức năng chọn theo cách này: Chọn (randarray (hàng (dữ liệu), 1, 1, n, true), value1, value2, triệt)) Where:
Ví dụ: để gán số từ 1 đến 3 cho người tham gia A2: A13, hãy sử dụng công thức này:
Để thuận tiện, bạn có thể nhập các giá trị để gán trong các ô riêng biệt, nói từ D2 đến D4 và tham chiếu các ô đó trong công thức của bạn (riêng lẻ, không phải là một phạm vi):
Kết quả là, bạn sẽ có thể gán ngẫu nhiên bất kỳ số, chữ cái, văn bản, ngày và thời gian nào có cùng một công thức: Ghi chú. Hàm Randarray sẽ tiếp tục tạo các giá trị ngẫu nhiên mới với mọi thay đổi trong bảng tính, vì kết quả các giá trị mới sẽ được gán mỗi lần. Để "Khắc phục" các giá trị được gán, hãy sử dụng các tính năng giá trị đặc biệt của Dán để thay thế các công thức bằng các giá trị được tính toán của chúng. The RANDARRAY function will keep generating new random values with every change in the worksheet, as the result new values will be assigned every time. To "fix" the assigned values, use the Paste Special > Values features to replace formulas with their calculated values. Cách thức hoạt động của công thức này Trọng tâm của giải pháp này một lần nữa là hàm randarray tạo ra một loạt các số nguyên ngẫu nhiên dựa trên các số tối đa và tối đa mà bạn chỉ định (từ 1 đến 3 trong trường hợp của chúng tôi). Chức năng hàng cho RandArray có bao nhiêu số ngẫu nhiên để tạo. Mảng này đi đến đối số index_num của chức năng chọn. Ví dụ:
Index_num là đối số xác định vị trí của các giá trị để trả về. Và bởi vì các vị trí là ngẫu nhiên, các giá trị trong D2: D4 được chọn theo thứ tự ngẫu nhiên. Đúng, thật đơn giản :) Cách gán ngẫu nhiên dữ liệu cho các nhómKhi nhiệm vụ của bạn là gán ngẫu nhiên người tham gia cho các nhóm, công thức trên có thể không phù hợp vì nó không kiểm soát được số lần một nhóm nhất định được chọn. Ví dụ: 5 người có thể được chỉ định cho nhóm A chỉ có 2 người để nhóm C. thực hiện việc gán ngẫu nhiên một cách đồng đều, để mỗi nhóm có cùng số người tham gia, bạn cần một giải pháp khác nhau.evenly, so that each group has the same number of participants, you need a different solution. Đầu tiên, bạn tạo một danh sách các số ngẫu nhiên bằng cách sử dụng công thức này:
Trong đó A2: A13 là dữ liệu nguồn của bạn. Và sau đó, bạn gán các nhóm (hoặc bất cứ điều gì khác) bằng cách sử dụng công thức chung này: Index (value_to_assign, roundup (xếp hạng (first_random_number, Random_numbers_range)/n, 0)) Trong đó n là kích thước nhóm, tức là số lần mỗi giá trị nên được gán. Ví dụ: để gán ngẫu nhiên mọi người vào các nhóm được liệt kê trong E2: E5, để mỗi nhóm có 3 người tham gia, hãy sử dụng công thức này:
Xin lưu ý rằng đó là một công thức thông thường (không phải là công thức mảng động!), Vì vậy, bạn cần khóa các phạm vi với các tham chiếu tuyệt đối như trong công thức trên. Nhập công thức của bạn vào ô trên cùng (C2 trong trường hợp của chúng tôi) và sau đó kéo nó xuống bao nhiêu ô khi cần. Kết quả sẽ trông tương tự như thế này: Xin nhớ rằng chức năng Randarray không ổn định. Để ngăn chặn việc tạo các giá trị ngẫu nhiên mới mỗi khi bạn thay đổi một cái gì đó trong bảng tính, hãy thay thế các công thức bằng các giá trị của chúng bằng cách sử dụng tính năng đặc biệt dán. Công thức này hoạt động như thế nào: Công thức Randarray trong cột trợ giúp rất đơn giản và hầu như không yêu cầu giải thích, vì vậy chúng ta hãy tập trung vào công thức trong cột C.
Hàm xếp hạng xếp hạng giá trị trong B2 so với mảng các số ngẫu nhiên trong B2: B13. Kết quả là một số từ 1 đến tổng số người tham gia (12 trong trường hợp của chúng tôi). Thứ hạng được chia cho kích thước nhóm, (3 trong ví dụ của chúng tôi) và hàm Roundup làm tròn nó lên đến số nguyên gần nhất. Kết quả của hoạt động này là một số từ 1 và tổng số nhóm (4 trong ví dụ này). Số nguyên đi đến đối số ROW_NUM của hàm chỉ mục, buộc nó phải trả về một giá trị từ hàng tương ứng trong phạm vi E2: E5, đại diện cho nhóm được gán. Chức năng excel randarray không hoạt độngKhi công thức randarray của bạn trả về một lỗi, đây là những lý do rõ ràng nhất để kiểm tra: Lỗi #SpillNhư với bất kỳ chức năng mảng động khác, #Spill! Lỗi thường xuyên nhất có nghĩa là không có đủ không gian trong phạm vi tràn dự định để hiển thị tất cả các kết quả. Chỉ cần xóa tất cả các ô trong phạm vi này và công thức của bạn sẽ tự động tính toán lại. Để biết thêm thông tin, vui lòng xem Excel #Spill ERROR - Nguyên nhân và Sửa lỗi. #Value lỗiMỘT GIÁ TRỊ! Lỗi có thể xảy ra trong những trường hợp này:
Lỗi #NAMETrong hầu hết các trường hợp, một #Name! Lỗi chỉ ra một trong những điều sau:
#Calc! lỗiMột #CALC! Lỗi xảy ra nếu đối số hàng hoặc cột nhỏ hơn 1 hoặc đề cập đến một ô trống. Đó là cách xây dựng một trình tạo số ngẫu nhiên trong Excel với chức năng Randarray mới. 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! Thực hành sổ làm việc để tải xuốngVí dụ về công thức randarray (tệp .xlsx) Bạn cũng có thể quan tâm |
