تابع RANDARRAY در اکسل | تولید اعداد تصادفی در اکسل

در این آموزش با استفاده از تابع RANDARRAY در اکسل یاد می­گیریم که چگونه اعداد تصادفی تولید کنیم، لیست را به صورت تصادفی مرتب کنیم، به صورت تصادفی انتخاب کنیم و داده ها را به صورت تصادفی به گروه ها تخصیص دهیم.

احتمالاً می­دانید که مایکروسافت اکسل از قبل دارای چند تابع تصادفی به نام های RAND، RANDBETWEEN بوده است. پس چه نیازی به یک تابع جدید بود؟ به طور خلاصه، زیرا بسیار قدرتمندتر از دو تابع قبلی و همچنین جایگزینی برای هر دو تابع قدیمی است. جدا از اینکه به شما اجازه تنظیم حداکثر و حداقل مقادیر را می­دهد، به شما امکان می دهد تعداد سطرها و ستون هایی را که قرار است پر شود را مشخص کنید و اعداد را تعیین کنید که به صورت اعشاری تولید شوند یا عدد صحیح.  می توان این تابع را با سایر توابع ترکیب کرد و حتی داده ها را تغییر داد و نمونه تصادفی انتخاب کرد.

تابع RANDARRAY در اکسل

تابع RANDARRAY در اکسل آرایه ای از اعداد تصادفی را بین هر دو عددی که تعیین کنید، برمیگرداند.

این تابع یکی از 6 تابع جدیدی است که در نسخه2021 اکسل معرفی شده است. نتیجه یک آرایه پویا است که داخل تعداد ردیف ها و ستون هایی که تعیین کرده اید، نشان داده می شود.

ساختار این تابع به شکل زیر است:

RANDARRAY([rows], [columns], [min], [max], [whole_number])

Rows: اختیاری- مشخص میکند که چند ردیف باید پر شود. اگر این آگومان حذف شود، به صورت پیش فرض یک ردیف در نظر گرفته میشود.

Columns: اختیاری- مشخص میکند که چند ستون باید پر شود. اگر این آگومان حذف شود، به صورت پیش فرض یک ستون در نظر گرفته میشود.

Min: اختیاری- کوچکترین عدد تصادفی برای تولید. اگر مشخص نشود، به صورت پیش فرض مقدار 0 در نظر گرفته می شود.

Max: اختیاری- بزرگترین عدد تصادفی برای تولید. اگر مشخص نشود، از مقدار پیش فرض 1 استفاده می­شود.

Whole_number: اختیاری- تعیین می­کند چه نوع مقادیری برگردانده شوند:

True: اعداد صحیح

False: پیش فرض- اعداد اعشاری

(در صورتی که این آرگومان حذف شود، به صورت پیش فرض حالت FALSE در نظر گرفته می­شود.)

چیزهایی که در مورد تابع RANDARRAY باید به یاد داشته باشید:

برای تولید مؤثر اعداد تصادفی در اکسل، باید به 6 نکته مهم توجه کنید:

• این تابع فقط در نسخه 2021 و 365 در دسترس است و در نسخه های قبلی قابل دسترس نیست.
• اگر آرایه ای که توسط تابع RANDARRAY برگردانده شده است نتیجه نهایی است (یعنی در تابع دیگری استفاده نمی­شود) اکسل به صورت خودکار یک محدوده ایجاد میکند و نتایج را در آنجا وارد می­کند. پس لطفا در نظر داشته باشید که به اندازه کافی سلول خالی در پایین و یا سمت راست سلولی که در آن فرمول را وارد کرده اید، وجود داشته باشد؛ در غیر اینصورت خطای #SPILL رخ می­دهد.
• اگر هیچ یک از آرگومان ها مشخص نشده باشند، تابع RANDARRAY() یک عدد اعشاری بین 0 و یک برمیگرداند.
• اگر آرگومان های rows یا columns با اعداد اعشاری نشان داده شوند، فقط قسمت صحیح در نظر گرفته میشود. (یعنی اگر به جای هر کدام از این آرگومان ها عدد 5.9 قرار داده شود، 5 در نظر گرفته می­شود.)
• اگر آرگومان min و max مشخص نشده باشند؛ randarray به صورت پیش فرض به ترتیب 0 و 1 را برای آنها در نظر میگیرد.
• مانند سایر توابع تصادفی، تابع RANDARRAY فرار است، به این معنی که هر بار شما در صفحه محاسباتی انجام دهید(یا حتی در سلولی کلیک کنید)، لیست جدیدی از مقادیر تصادفی ایجاد می­شوند. برای جلوگیری از این اتفاق از ویژگی Paste Special>Values استفاده کنید.

فرمول های مقدماتی RANDARRAY

فرض کنید که شما میخواهید یک محدوده شامل 5 ردیف و 3 ستون را از اعداد تصادفی پر کنید. برای انجام این کار دو آرگومان را تنظیم کنید:

Rows: این آرگومان را 5 قرار دهید.

Columns: این آرگومان را 3 قرار دهید.

در نهایت فرمول شما به شکل زیر خواهد بود:

=RANDARRAY(5, 3)

این فرمول را در بالاترین سلول سمت چپ محدوده (یعنی در A2) قرار دهید و سپس کلید enter را بزنید. نتیجه به شکل زیر خواهد بود:

همانطور که در تصویر می­بینید، اعداد تصادفی ایجاد شده بین 0 و 1 هستند. اگر مایل هستید که اعداد صحیح ایجاد شوند باید سه آگومان بعدی را نیز تنظیم کنید. در مثال های بعدی این موضوع را بررسی خواهیم کرد.

نحوه تصادفی انتخاب کردن در اکسل- مثال های فرمولی

در زیر به چند فرمول پیشرفته خواهید دید که سناریوهای تصادفی سازی معمول در اکسل را پوشش می­دهند.

ایجاد کردن اعداد تصادفی بین دو عدد

برای ایجاد یک لیست از اعداد تصادفی در یک محدوده خاص، کمترین مقدار مورد نظرتان را در آرگومان سوم و بیشترین مقدار را در آرگومان چهارم قرار دهید. اگر میخواهید اعداد تصادفی ایجاد شده، عدد صحیح باشند آگومان پنجم را روی true و اگر میخواهید اعشاری باشد، روی false تنظیم کنید.

به عنوان مثال فرض کنید که یک محدوده شامل 6 ردیف و 4 ستون را میخواهید از اعداد تصادفی بین 1 تا صد پر کنید. برای ان کار آرگومان ها را به صورت زیر تنظیم کنید:

Rows: 6 بگذارید زیرا 6 ردیف میخواهید داشته باشید.

Columns: 4  بگذارید زیرا 4 ستون میخواهید داشته باشید.

Min: 1

Max: 100

Whole_number: true

فرمول به شکل زیر خواهد بود:

=RANDARRAY(6, 4, 1, 100, TRUE)

ایجاد کردن تاریخ تصادفی بین دو تاریخ

برای ایجاد تاریخ تصادفی، کافی است که تاریخ قبل (تاریخ 1) و تاریخ بعدی (تاریخ 2) را در سلول های از پیش تعریف شده در اکسل وارد کنید و سپس آن سلول ها را در فرمول خود ارجاع دهید:

RANDARRAY(rows, columns, date1, date2, TRUE)

برای این مثال، لیستی از تاریخ های تصادفی بین تاریخ های D1 و D2 را با این فرمول ایجاد کرده ایم:

=RANDARRAY(10, 1, D1, D2, TRUE)

شما میتوانید برای تاریخ نیز حداقل و حداکثر تعریف کنید، اما مطمئن باشید که آنها را در قالبی وارد کنید که اکسل بتواند درک کند:

=RANDARRAY(10, 1, "1/1/2020", "12/31/2020", TRUE)

برای جلوگیری از اشتباهات، میتوانید از تابع DATE برای وارد کردن تاریخ استفاده کنید:

=RANDARRAY(10, 1, DATE(2020,1,1), DATE(2020,12,31), TRUE)

نکته: اکسل تاریخ ها را به شکل شماره سریال ذخیره میکند، در نتیجه احتمالاً نتایج فرمول به صورت اعداد نمایش داده میشوند. برای اینکه تاریخ ها به صورت صحیح نمایش داده شوند، محدوده ای که در آن قرار است نتایج نشان داده شوند را انتخاب کنید و فرمت تاریخ را برای همه سلول های آن اعمال کنید.

ایجاد کردن روزهای کاری تصادفی در اکسل

برای تولید روزهای کاری تصادفی، تابع Randarray را در اولین ارگومان تابع workday قرار دهید:

WORKDAY(RANDARRAY(rows, columns, date1, date2, TRUE), 1)

RANDARRAY یک آرایه تصادفی از تاریخ شروع ایجاد میکند، که تابع workday اطمینان حاصل میکند که تمام تاریخ های برگردانده شده روز های کاری هستند.

تاریخ 1 در سلول D1 و تاریخ 2 در سلول D2 قرار دارد، با استفاده از این دو تاریخ میخواهیم یک لیست شامل 10 روز کاری ایجاد کنیم. فرمول به شکل زیر خواهد بود:

=WORKDAY(RANDARRAY(10, 1, D1, D2, TRUE), 1)

مانند مثال قبل، لطفا به یاد داشته باشید که فرمت تاریخ را برای سلول هایی که قرار است نتیجه در آنها برگردانده شود، انتخاب کنید؛ تا نتیجه به درستی نشان داده شود.

ایجاد کردن اعداد تصادفی غیر تکراری

برای ساخت اعداد تصادفی منحصر به فرد خود در اکسل، باید چندین تابع را بایکدیگر مانند زیر ترکیب کنید:

عددهای صحیح تصادفی:

INDEX(UNIQUE(RANDARRAY(n*2, 1, min, max, TRUE)), SEQUENCE(n))

عددهای اعشاری تصادفی:

INDEX(UNIQUE(RANDARRAY(n*2, 1, min, max, FALSE)), SEQUENCE(n))

N: تعداد اعدادی که میخواهید تولید کنید.

Min: کمترین مقدار

Max: بیشترین مقدار

به عنوان مثال برای تولید 10 عدد صحیح تصادفی بدون تکرار، از این فرمول استفاده کنید:

=INDEX(UNIQUE(RANDARRAY(20, 1, 1, 100, TRUE)), SEQUENCE(10))

برای ایجاد لیست ده تایی از اعدادی اعشاری منحصر به فرد، آخرین آرگومان تابع randarray در اکسل را false قرار دهید یا آن را حذف کنید.

=INDEX(UNIQUE(RANDARRAY(20, 1, 1, 100, FALSE)), SEQUENCE(10))

مرتب سازی تصادفی در اکسل

برای جابه جایی داده ها در اکسل، به جای آرگومان by_array در تابع SORTBY از تابع RANDARRAY استفاده کنید. تابع ROWS تعداد ردیف ها را در داده های شما محاسبه میکند و تعداد اعداد تصادفی را برای تولید مشخص میکند:

SORTBY(data, RANDARRAY(ROWS(data)))

با استفاده از این روش، میتوانید در اکسل لیستی را به صورت تصادفی مرتب کنید. این لیست میتواند شامل عداد، تاریخ و یا متن باشد:

=SORTBY(A2:A13, RANDARRAY(ROWS(A2:A13)))

همچنین میتوانید داده های خود را بدون قاطی شدن (در مثال ما اسم وفامیل بدون قاطی شدن) به صورت تصادفی مرتب شده اند:

ایجاد یک نمونه تصادفی

برای ایجاد یک نمونه تصادفی از فرمول زیر استفاده کنید:

INDEX(data, RANDARRAY(n, 1, 1, ROWS(data), TRUE))

N تعداد موارد تصادفی است که میخواهید استخراج کنید.

به عنوان مثال، برای انتخاب تصادفی 3 نام از محدوده A2:A10، از این فرمول استفاده کنید:

=INDEX(A2:A10, RANDARRAY(3, 1, 1, ROWS(A2:A10), TRUE))

یا میتوانید اندازه نمونه ای که میخواهید را در یک سلول وارد کنید (مثلا در C2) و آن سلول را ارجاع دهید:

=INDEX(A2:A10, RANDARRAY(C2, 1, 1, ROWS(A2:A10), TRUE))

 

این فرمول چگونه کار میکند؟

در مرکز این فرمول تابع RANDARRAY یک آرایه تصادفی از اعداد صحیح ایجاد میکند و مقدار C2 تعداد مقادیر تولید شده را مشخص کرده است. حداقل تعداد اعداد تصادفی تولید شده (1) و حداکثر تعداد مربوط به تعداد ردیف های مجموعه شما است که با تابع ROWS برگردانده میشود.

آرایه اعداد تصادفی صحیح ایجاد شده، مستقیماً به جای آگومان row_num تابع INDEX قرار میگیرد و موقعیت مکانی موارد را برای برگرداندن مشخص میکند. برای نمونه در تصویر بالا، این است:

=INDEX(A2:A10, {8;7;4})

حالا از اولین سلول ستون Name شروع به شمارش میکند: شماره 8: Robert، شماره 7: Paul و شماره 4: David است. همانطور که میبینید در قسمت نتایج این سه مورد برگردانده شده اند.

انتخاب ردیف های تصادفی با تابع RANDARRAY در اکسل

اگر مجموعه داده های شما بیش از یک ستون دارد، مشخص کنید کدام ستون ها میخواهید در نمونه قرار بگیرند. برای این کار، یک آرایه ثابت برای آخرین آرگومان (Column_num) تابع index درنظر بگیرید. مانند:

=INDEX(A2:B10, RANDARRAY(D2, 1, 1, ROWS(A2:A10), TRUE), {1,2})

A2:A10 محدوده داده ها و D2 تعداد اعضای نمونه است.

همانطور که در تصویر زیر مشاهده میکنید، نمونه تصادفی ما شامل داده های هر دو ستون است:

اختصاص دادن اعداد تصادفی به متن با استفاده از تابع RANDARRAY در اکسل

برای اختصاص دادن اعداد تصادفی به متن در اکسل، از ترکیب توابع RANDBETWEEN و CHOOSE استفاده کنید:

CHOOSE(RANDARRAY(ROWS(data), 1, 1, n, TRUE), value1, value2,…)

Data: محدوده ای است که میخواهید مقادیر تصادفی را از آن انتخاب کنید.

N: تعداد کل اعدادی که میخواهید برگردانده شوند.

Value1، value2، value3 و…. مقادیری هستند که میخواهید به صورت تصادفی انتخاب شوند.

برای مثال، برای اختصاص دادن اعداد 1 تا 3 به مقادیر موجود در محدوده A2:A13، از فرمول زیر استفاده کنید:

=CHOOSE(RANDARRAY(ROWS(A2:A13), 1, 1, 3, TRUE), 1, 2, 3)

همچنین شما میتوانید مقادیر را در ستون های جدا از هم وارد کنید، مثال بگویید از D2 تا D4 و آنها را در فرمول خود ارجاع دهید (البته باید به صورت جدا جدا ارجاع دهید نه به صورت محدوده):

 

=CHOOSE(RANDARRAY(ROWS(A2:A13), 1, 1, 3, TRUE), D2, D3, D4)

 

به طور مشابه میتوانید به صورت تصادفی هر عدد، حرف، متن، تاریخ و زمانی را برای اختصاص دادن  در فرمول استفاده کنید:

نکته: تابع RANDARRAY با هر تغییری که در صفحه کاری ایجاد کنید، مقادیر تصادفی را به روز میکند و مجددا مقادیر جدیدی را نشان خواهد داد. برای ثابت کردن مقادیر، از ویژگی Paste /special>value استفاده کنید. به این ترتیب مقادیر محاسبه شده جایگزین فرمول ها میشوند.

این فرمول چگونه کار میکند:

ابتدا تابع RANDARRAY یک آرایه تصادفی بر اساس کمترین و بیشترین مقداری که شما انتخاب کردید (در اینجا 1 تا 3) تولید میکند. تابع ROWS به تابع RANDARRAY میگوید که چند عدد تصادفی تولید کند. این آرایه به جای آرگومان index_num تابع CHOOSE قرار میگیرد. برای مثال:

=CHOOSE({1;2;1;2;3;2;3;3;1;3;1;2}, D2, D3, D4)

آرگومان index_num موقعیت مقادیر بازگشتی را تعیین میکند و از آنجا که موقعیت ها تصادفی هستند، مقادیر موجود در D2:D4 به ترتیب تصادفی انتخاب میشوند. به همین سادگی 😊

اختصاص تصادفی داده ها به گروه ها

وقتی میخواهید شرکت کنندگان را به صورت تصادفی به گروه ها اختصاص دهید، ممکن است فرمول بالا مناسب نباشد، زیرا تعداد اعداد تصادفی ایجاد شده یکسان نیست (ممکن است عدد 1 دوبار و عدد 3 پنج بار تکرار شود.) به این ترتیب ممکن است به گروه A دو نفر اختصاص داده شود و به گروه C پنج نفر اختصاص یابد.

ابتدا با استفاده از فرمول زیر لیستی از اعداد تصادفی ایجاد کنید:

=RANDARRAY(ROWS(A2:A13))

داده های شما در محدوده A2:A13 قرار دارند.

و سپس با استفاده از این فرمول، گروه ها را اختصاص دهید:

INDEX(values_to_assign, ROUNDUP(RANK(first_random_number, random_numbers_range)/n, 0))

N تعداد اعضای گروه است.

برای مثال، برای اختصاص دادن افراد به گروه های E2:E5 (به طوری که هر گروه شامل 3 نفر باشد) از فرمول زیر استفاده میشود:

 

=INDEX($E$2:$E$5, ROUNDUP(RANK(B2,$B$2:$B$13)/3,0))

 

دقت کنید که این یک فرمول معمولی است (نه یک فرمول آرایه ای پویا!!!!)، بنابراین باید محدوده ها را در فرمول بالا ثابت کنید.

فرمول خود را در بالاترین سلول وارد کنید (در مثال ما سلول C2) سپس به هر اندازه سلولی که میخواهید به سمت پایین فرمول را گسترش دهید. نتیجه مشابه تصویر زیر خواهد شد:

به یاد داشته باشید که تابع RANDARRAY فرار است، به این معنی که هر بار شما در صفحه محاسباتی انجام دهید(یا حتی در سلولی کلیک کنید)، لیست جدیدی از مقادیر تصادفی ایجاد می­شوند. برای جلوگیری از این اتفاق از ویژگی Paste Special>Values استفاده کنید.

این فرمول چگونه کار میکند؟

خب تابع RANDARRAY دقیقا همونطوری که توی مثال قبلی توضیح داده شد کار میکند. در نتیجه در ادامه فرمولی که داخل ستون C نوشته شده است بررسی میشود:

=INDEX($E$2:$E$5, ROUNDUP(RANK(B2,$B$2:$B$13)/3,0))

تابع RAND محدوده B2:B12 را از یک تا عدد مربوط به کل شرکت کنندگان (در این مثال 12) رتبه بندی میکند. رتبه به اندازه گروه تقسیم میشود (در مثال ما 3 گروه است.) و تابع roundup آن را به نزدیکترین عدد صحیح گرد میکند. نتیجه این عملیات عددی بین 1 و تعداد کل گروه ها (در مثال ما4) است.

عدد صحیح به آرگومان row_num از تابع index منتقل میشود، و مقدار مربوطه را از محدوده E2:E5 برمیگرداند که این مقدار نشان دهنده گروه مربوطه است. و تمااام

دلایل کار نکردن تابع RANDARRAY در اکسل

اگر فرمول RANDARRAY یک خطا برگرداند، به احتمال زیاد یکی از موارد زیر است:

خطا #SPILL

مانند هر آرایه پویا دیگر، خطا #SPILL اغلب به این معناست که فضای کافی (تعداد سلول کافی) برای برگرداندن نتایج وجود ندارد. تمام سلول های موجود در محدوده نتیجه را پاک کنید و فرمول را مجددا محاسبه کنید. برای اطلاعات بیشتر به خطای #sPILL در اکسل مراجعه کنید.

خطا #vlaue

این خطا به دو دلیل ممکن است رخ داده باشد:

• اگر مقدار max کمتر از مقدار min باشد.
• اگر آرگومان ها غیرعددی باشند.

خطا #NAME

در بیشتر مواقع این خطا به یکی از دلایل زیر است:

• اسم تابع را اشتباه تایپ کرده باشید.
• تابعی که استفاده کرده اید در ورژن اکسل شما قابل دسترس نباشد.

خطا #CALC

این خطا زمانی رخ میدهد که آرگومان مربوط به ردیف ها یا ستون ها کمتر از 1 باشد یا به یک سلول خالی ارجاع داده شده باشند.

امیدوارم از این مقاله لذت برده باشید.

سوالی داشتید حتما زیر همین مطلب کامنت بذارید.

ممنون از همراهی شما. باز هم به سایت ما سر بزنید.