عدد تصادفی در اکسل — راهنمای کاربردی

۱۷۰۹۴ بازدید
آخرین به‌روزرسانی: ۱۶ خرداد ۱۴۰۲
زمان مطالعه: ۱۰ دقیقه
عدد تصادفی در اکسل — راهنمای کاربردی

اعداد تصادفی در کنار فعالیت‌های روزانه بشر، گاهگاهی به کار می‌روند. انجام قرعه کشی، شبیه‌سازی فعالیت‌های واقعی، پیش‌بینی آب و هوا و شرایط جوی، همگی از اعداد تصادفی تولید شده استفاده می‌کنند. همچنین ابزارهای مختلفی نظیر ماشین‌های مکانیکی تولید اعداد تصادفی تا ابزارهای الکترونیکی برای تولید «اعداد تصادفی» (Random Number) یا به شکل دقیق‌تر «شبه تصادفی» (Pseudo Random Number) تولید شده‌اند. در این نوشتار به نحوه تولید عدد تصادفی در اکسل خواهیم پرداخت و شیوه‌های مختلف ایجاد چنین اعدادی را در اکسل مرور خواهیم کرد.

997696

برای آشنایی بیشتر در مورد اصطلاحات به کار رفته در اعداد تصادفی و همچنین نحوه تولید آن‌ها، مطالب اعداد تصادفی (Random Numbers) — تاریخچه و کاربردها و توزیع یکنواخت گسسته و پیوسته — مفاهیم و کاربردها را مطالعه کنید. همچنین خواندن نوشتارهای آزمایش تصادفی، پیشامد و تابع احتمال و متغیر تصادفی و توزیع دو جمله‌ای — به زبان ساده نیز خالی از لطف نیست.

عدد تصادفی در اکسل

بازی‌های شانسی وابسته به اعداد تصادفی هستند. پرتاب یک تاس، نمونه‌ای از یک بازی تصادفی یا شانسی است. قبل از آنکه یک تاس را پرتاب کنیم، از نتیجه آن آگاه نخواهیم بود ولی می‌دانیم یکی از اعداد ۱ تا ۶ به عنوان حاصل پرتاب تاس خواهد بود. به این ترتیب احتمال مشاهده مثلا عدد ۵ را برابر با ۱/۶ در نظر می‌گیریم. همین احتمال یا هم‌شانسی را می‌توان مبنایی برای تولید اعداد تصادفی در نظر گرفت. البته واضح است که در این حالت، احتمال مشاهده هر یک از اعداد، یکسان بوده و با احتمال برابر رخ خواهند داد.

اکسل به عنوان یک ابزار تحلیل داده، امکانات مختلفی برای تولید و به کارگیری اعداد تصادفی در اختیار کاربر قرار می‌دهد. برای مثال تولید اعداد تصادفی یکنواخت (با احتمال رخداد یکسان) هم به کمک توابع اکسل و هم به وسیله ابزارهای آن قابل انجام است.

در این نوشتار ابتدا توزیع‌های آماری و نحوه تولید اعداد تصادفی براساس آن‌ها را توضیح داده، سپس نحوه تولید اعداد تصادفی با تابع‌های اکسل (از توزیع یکنواخت و توزیع دلخواه) را بازگو خواهیم کرد. در انتها نیز با استفاده از افزونه Analysis ToolPak، تولید اعداد تصادفی را به کمک توزیع‌های مختلف آماری در اکسل اجرا می‌کنیم.

عدد تصادفی و توزیع احتمال

عدد تصادفی، مقداری است که طبق قوانین شانس ظاهر می‌شود. همانطور که می‌دانید، در «نظریه احتمال» (Probability Theory) که یکی از شاخه‌های آمار محسوب می‌شود، قوانین شانس مورد مطالعه و استخراج قرار می‌گیرد. در نتیجه تولید عدد تصادفی کاملا وابسته به توزیع احتمال و متغیرهای تصادفی است. در نوشتار توزیع های آماری — مجموعه مقالات جامع وبلاگ فرادرس می‌توانید با این گونه توزیع‌ها و متغیرهای وابسته، آشنا شده و کاربرد هر یک را بخوانید.

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

یکی از توابع برای تولید عدد تصادفی در اکسل که از «توزیع یکنواخت پیوسته» (Continuous Uniform Distribution) استفاده می‌کند، تابع RAND است. واضح است که چنین عددی در بازه ۰ تا ۱ تولید خواهد شد. شکل دستوری این تابع به صورت زیر است.

RAND ( )

همانطور که مشاهده می‌کنید، این تابع به هیچ پارامتری احتیاج ندارد و به طور خودکار یک عدد تصادفی در بازه [0,1)[0,1) تولید می‌کند. برای آنکه مثلا ۱۰ عدد تصادفی با توزیع یکنواخت پیوسته تولید کنید، کافی است فرمول نوشته شده در یک سلول را در ۱۰ سلول دیگر کپی کنید. برای مثال اگر تابع RAND را در سلول A1‌ وارد کرده باشیم، با کپی کردن آن در سلول‌های پایینی، ۱۰ مقدار تصادفی خواهیم داشت.

نکته: می‌دانید که هر فرمول یا تابع در اکسل، به محض تغییر هر یک از سلول‌ها، مجدداً محاسبه می‌شود. در نتیجه هر بار مقدار سلول‌هایی که با تابع RAND مقدار دهی شده‌اند، تغییر کرده و یک عدد تصادفی جدید درون سلول ثبت خواهد شد.

در نسخه‌های بعد از ۲۰۱۰ اکسل، الگوریتم به کار رفته برای تولید اعداد تصادفی، «الگوریتم مرسن پیچشی» (Mersenne Twister algorithm) یا به اختصار (MT19937) استفاده می‌کند. این الگوریتم یکی از بهترین روش‌های تولید اعداد تصادفی بوده که در بیشتر زبان‌های برنامه‌نویسی از جمله R, Python و C، پیاده‌سازی شده است.

اگر می‌خواهید به کمک این تابع در بازه aa تا bb، یک عدد تصادفی ایجاد کنید، کافی است محاسبات را به صورت زیر انجام دهید.

= RAND() * ( b - a ) + a

به این ترتیب اگر a=10a = 10 و b=20b = 20‌ باشد، با توجه به نتیجه عدد تصادفی مثلا ۰٫۳۵۴، خواهیم داشت.

=0.354(2010)+10=3.54+10=13.54 = 0.354 * ( 20 - 10 ) + 10 = 3.54 + 10 = 13. 54

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

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

تابع دیگری که برای ایجاد اعداد تصادفی گسسته یا با مقادیر صحیح به کار می‌رود، تابع RANDBETWEEN است. شکل دستوری این تابع به صورت زیر است.

RANDBETWEEN(bottom, top)

که پارامتر bottom کران پایین و top نیز کران بالا برای اعداد تصادفی تولید شده را نشان می‌دهند. البته توجه داشته باشید که هر دو این پارامترها باید مقادیر صحیح (مثبت با منفی) باشند. برای مثال اگر بخواهیم اعداد صحیح در بازه ۱۰- تا ۱۰+ ایجاد کنیم تابع را به صورت زیر وارد می‌کنیم. با کپی کردن این فرمول روی سلول‌های دیگر نیز به تعداد دلخواه عدد تصادفی در این بازه تولید خواهد شد.

= RANDBETWEEN(-10 , 10)

باز هم توجه داشته باشید که با تغییر در هر سلول دیگر در کاربرگ یا کارپوشه، مقادیر حاصل از تابع RANDBETWEEN تغییر کرده و یک عدد تصادفی جدید تولید خواهد شد. به این ترتیب تمامی سلول‌هایی که وابسته به سلول‌هایی با مقادیر تصادفی باشند، دچار تغییر گشته و مقدار جدید را تولید خواهند کرد.

نکته: اگر می‌خواهید اعداد تصادفی تولید شده، تغییری نداشته باشند، کافی است آن‌ها را کپی کرده و هنگام اجرای دستور Paste Special، گزینه Value را انتخاب کنید. به این ترتیب فرمول‌ها تبدیل به مقادیر شده و دیگر تغییر نخواهند کرد.

تولید عدد تصادفی در اکسل با تابع معکوس توزیع‌های آماری

در این قسمت می‌خواهیم براساس توابع آماری و بخصوص توابع مرتبط با معکوس توزیع‌های آماری، عدد تصادفی در اکسل تولید کنیم. در اکسل بسیاری از توابع مربوط به محاسبات آماری، مرتبط به توزیع‌های آماری هستند. برای مثال تابع NORM.DIST، مقدار تابع چگالی یا مقدار توزیع احتمال (توزیع تجمعی) را در یک نقطه، محاسبه می‌کند. واضح است که این مقدار در بازه ۰ تا ۱ خواهد بود. حال اگر از تابع عکس توزیع یعنی NORM.DIST.INV استفاده کنیم، با مشخص کردن مقدار احتمال (p) و پارامترهای توزیع نرمال، مقدار صدک pام را بدست خواهیم آورد.

در نتیجه اکثر توابعی که در اکسل با پسوند INV مشخص شده‌اند، مربوط به معکوس توابع احتمال بوده و برای تولید عدد تصادفی از آن توزیع به کار می‌روند. جدول زیر به معرفی توابع معکوس احتمال برای توزیع‌های مختلف آماری پرداخته است که در اکسل قابل استفاده هستند.

جدول ۱: معکوس توابع احتمال برای تولید عدد تصادفی در اکسل

توزیعتابعپارامترهای توزیعپارامتر احتمال یا p
توزیع بتاBETA.INValpha , beta, A , Bprobability
توزیع دو جمله‌ایBINOM.INVtrials , probability_salpha
توزیع کای ۲CHISQ.INVdeg_freedomprobability
توزیع کای ۲ (دم سمت راست)CHISQ.INV.RTdeg_freedomprobability
توزیع FF.INVdeg_freedom1 , deg_freedom2probability
توزیع F (دم سمت راست)F.INV.RTdeg_freedom1 , deg_freedom2probability
توزیع گاماGAMMA.INValpha , betaprobability
توزیع لوگ نرمالLOGNORM.INVmean, standard_devprobability
توزیع نرمالNORM.INVmean, standard_devprobability
توزیع نرمال استانداردNORM.S.INV-probability
توزیع تیT.INVdeg_freedomprobability
توزیع تی (دو طرفه)T.INV.2Tdeg_freedomprobability

برای مثال فرض کنید می‌خواهیم مقداری تصادفی از توزیع گاما تولید کنید. اگر توزیع گامای مورد نظر با پارامترهای ۱ و ۲ باشد، فرمول زیر می‌تواند یک عدد تصادفی از این توزیع در نقطه 0٫75 ایجاد کند.

= GAMMA.INV ( 0.75 , 1 , 2 )

حال در نظر بگیرید که مقدار احتمال (0٫75) توسط یک تابع تصادفی مانند ()RAND تولید شده باشد. به این ترتیب فرمولی که در ادامه مشاهده خواهید کرد. با هر بار تغییر در کاربرگ، یک عدد تصادفی از توزیع گاما خواهد ساخت.

= GAMMA.INV ( RAND( ) , 1 , 2 )

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

= NORM.S.IN ( RAND( ) )

تولید عدد تصادفی با ابزار Random Number Generation

یکی از ابزارهای بسیار مفید در اکسل که به صورت یک افزونه (Add-ins) قابل نصب است، Analysis ToolPak است که وظیفه تحلیل داده‌ها را با استفاده از تکنیک‌های آماری به عهده دارد. در این قسمت با استفاده از گزینه Random Number Generation، عدد تصادفی در اکسل ایجاد خواهیم کرد.

نکته: برای آگاهی از نحوه نصب و راه‌اندازی این افزونه بهتر است به نوشتار جدول فراوانی در اکسل --- راهنمای کاربردی مراجعه کنید.

data analysis add ins
تصویر ۱: افزونه تحلیل داده و دستور تولید عدد تصادفی در اکسل (Random Number Generation)

پس از اجرای این دستور پنجره‌ای به صورت زیر ظاهر شده که پارامترهای مربوط به تولید عدد تصادفی در اکسل را درخواست می‌کند. این پارامترها توسط جدول زیر معرفی شده‌اند.

جدول ۲: گزینه‌ها و تنظیمات پنجره تولید عدد تصادفی در افزونه Data Analysis

گزینهعملکردتوضیحات
Number of Variablesتعداد متغیرهاتعداد ستون‌هایی که باید عدد تصادفی در آن‌ها تولید شود.
Number of Random Numbersتعداد اعداد تصادفیتعداد سطرهایی که باید برای تولید عدد تصادفی به کار رود.
Distributionتوزیع آماریتوزیع‌های پر کاربرد آماری در این بخش دیده می‌شود.
Parametersپارامترهای توزیع مورد نظربا انتخاب هر نوع توزیع، پارامترهای آن در قسمت پایین درخواست می‌شود.
Random Seedدانه تصادفیمقداری است که براساس آن الگوریتم تولید اعداد شبه تصادفی به کار می‌افتد. با تعیین آن هر بار اجرا دستور، اعداد تصادفی یکسانی تولید می‌کند.
Output Rangeناحیه خروجیانتخاب ابتدای ناحیه‌ای از کاربرگ جاری برای تولید اعداد تصادفی
New Worksheet Plyکاربرگ جدید برای خروجی
New Workbookکارپوشه جدید برای خروجی

توجه داشته باشید که در رایانه‌ها، با استفاده از الگوریتم‌هایی، اعداد تصادفی تولید می‌شوند. در نتیجه واقعا این اعداد تصادفی نیستند. گزینه Random Seed، اجازه می‌دهد که مقدار اولیه برای این الگوریتم‌ها مشخص شده تا با هر بار تکرار الگوریتم، نتیجه یکسانی حاصل شود. این کار در زمانی که می‌خواهیم عملیاتی را که وابسته به اعداد تصادفی هستند، بررسی کنیم، لازم است. اگر Random Seed وارد نشود، سیستم هر بار یک عدد تصادفی وابسته به ویژگی‌های سخت‌افزاری و ساعت سیستم تولید کرده که تکراری نخواهد بود.

random number generation dialog box
تصویر ۲: عدد تصادفی در اکسل و افزونه Data Analysis ToolPak

توزیع‌هایی که در قسمت Distribution، قابل انتخاب هستند در جدول زیر معرفی شده‌اند. البته یک ستون از این جدول به معرفی پارامترهای این توزیع‌ها اختصاص دارد.

جدول ۳: توزیع‌های مختلف برای تولید عدد تصادفی در اکسل با افزونه Data AnalysisToolPak

توزیعشرحپارامترهای توزیع
Uniformتوزیع یکنواختکران پایین در بخش Between و کران بالا بعد از and معرفی می‌شوند. این توزیع گسسته است.
Normalتوزیع نرمالمیانگین (Mean) و انحراف استاندارد (Standard Deviation) پارامترهای این توزیع هستند. انحراف استاندارد باید مقداری مثبت باشد.
Bernoulliتوزیع برنولیپارامتر این توزیع p Value احتمال موفقیت است. در نتیجه باید در بازه ۰ تا ۱ مقدار دهی شود.
Binomialتوزیع دو جمله‌ایپارامترهای این توزیع p Value یا احتمال موفقیت و تعداد آزمایش‌ها (Number of trials) است. هر دو پارامتر مثبت بوده ولی p Value در بازه ۰ تا ۱ تغییر می‌کند.
Poissonتوزیع پواسنپارامتر این توزیع مقدار Lambda است که مقداری مثبت است. این پارامتر نشان دهنده متوسط تعداد خطاها یا معدل متغیر تصادفی توزیع پواسن را نشان می‌دهد.
Patternedدنباله الگو داربا این روش دنباله‌ای از اعداد طبق پارامترهای مختلف تولید می‌شود.
Discreteتوزیع گسستهپارامتر این توزیع، «مقادیر و لیستی از مقادیر احتمال» (Value and Probability Input Range) است. به این ترتیب هر مقدار با احتمال مشخصی، شانس ظاهر شدن در لیست عدد تصادفی را دارد.

نکته: انتخاب توزیع‌های گسسته مانند «توزیع یکنواخت» (Uniform) یا «گسسته» (Discrete) باعث تولید اعداد تصادفی صحیح یا طبیعی خواهد شد. ولی توزیع نرمال اعداد اعشاری تولید خواهد کرد.

مثال

فرض کنید که می‌خواهیم برای دو ستون از کاربرگ اکسل و به تعداد ۱۰0۰ سطر، اعداد تصادفی از توزیع نرمال تولید کنیم. به این منظور تنظیمات این پنجره را مطابق با تصویر بالا انجام می‌دهیم. البته توجه داشته باشید که توزیع نرمال در اینجا همان توزیع نرمال استاندارد بوده و میانگین و انحراف استاندارد آن به ترتیب ۰ و ۱ است. نتیجه به صورت دو ستون و هزار سطر خواهد بود که شامل مقادیری در حدود تقریبی بازه ۳- تا ۳+ است.

برای نمایش دادن نرمال بودن توزیع این متغیرها، می‌توان از رسم نمودار فراوانی (Histogram) استفاده کرد. به این منظور، دستور Histogram را از پنجره Data Analysis انتخاب کرده و تنظیمات را به صورت زیر انجام می‌دهیم.

Histogram parameters
تصویر ۳: پنجره تنظیمات رسم نمودار فراوانی (Histogram) در اکسل

از آنجایی که با انتخاب گزینه New Worksheet Ply، کاربرگ جدیدی برای نتیجه ایجاد خواهد شد، خروجی را به طور جداگانه خواهیم دید. نمودار ترسیم شده نمایانگر آن است که توزیع داده‌های تصادفی بسیار به توزیع نرمال یا «نمودار زنگی شکل» (Bell Curve) شبیه هستند. از طرفی صفر بودن میانگین نیز در این نمودار به وضوح به چشم می‌خورد.

Histogram plot
تصویر ۴: نمودار فراوانی و مشابهت توزیع داده های تصادفی با توزیع نرمال

ساخت الگوی عددی در اکسل با افزونه Data Analysis

از آنجایی که گزینه Pattern یک توزیع آماری نیست، احتیاج به توضیحات بیشتری دارد که در ادامه مورد بحث قرار می‌گیرد. این روش در حقیقت اعداد تصادفی تولید نمی‌کند بلکه با توجه به پارامترهای داده شده، دنباله‌ای از اعداد را تولید و در کاربرگ ثبت می‌کند. پارامترهای مربوط به این گزینه در جدول زیر آمده‌اند.

جدول ۴: پارامترهای دستور Pattern در تولید عدد تصادفی

گزینهعملکردتوضیحات
Fromکران پایین یا اولین مقدار الگوممکن است با توجه به این پارامترها، به آخرین مقدار الگو نرسیم به همین جهت نام پارامتر دوم را کران بالا قرار دادیم.
toکران بالا یا آخرین مقدار الگو
in steps ofمیزان افزایش
repeating each numberتعداد تکرارهای هر یک از اعداد الگو
repeating the sequenceتعداد تکرارهای دنباله عددی تولید شده

مثال

همانطور که می‌دانید هر سال شامل ۴ فصل است که می‌توان آن‌ها را به ترتیب از ۱ تا ۴ شماره‌گذاری کرد. همچنین می‌خواهیم این فصل‌ها را برای ۱۰ سال مشخص کنیم. در نتیجه باید دنباله ۱ تا ۴، هر دفعه ۱۰ بار تکرار شوند. واضح است که تکرار هر عدد، در هر سال، یکبار اتفاق می‌افتد پس گزینه repeating each number را ۱ انتخاب کرده و repeating the sequence را ۱۰ انتخاب می‌کنیم. به این ترتیب ۴۰ عدد تولید خواهد شد. تصویر زیر، تنظیمات و همینطور دنباله تولید شده را نشان می‌دهد.

pattern generation
تصویر ۵: ایجاد الگوی فصل‌های ۱۰ سال با مقادیر عددی در اکسل با گزینه Patterned

توجه داشته باشید در این جا برای تعداد اعداد (Number of Random Numbers)، مقداری مشخص نشده است. در نتیجه هنگامی که دنباله به پایان برسد، کار تولید عددها نیز خاتمه خواهد یافت.

خلاصه و جمع‌بندی

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

بر اساس رای ۱۰ نفر
آیا این مطلب برای شما مفید بود؟
اگر بازخوردی درباره این مطلب دارید یا پرسشی دارید که بدون پاسخ مانده است، آن را از طریق بخش نظرات مطرح کنید.
منابع:
مجله فرادرس
۱ دیدگاه برای «عدد تصادفی در اکسل — راهنمای کاربردی»

سلام،ممنونم از زحماتتون،
اگه ما ۱۰۰۰تا دیتا داشته باشیم، که در ۲ ستون و ۱۰۰۰ سطر باشه،بخواهیم ۳۰درصد از این دیتاهارا بصورت رندوم انتخاب کنم چیکار باید کرد؟

نظر شما چیست؟

نشانی ایمیل شما منتشر نخواهد شد. بخش‌های موردنیاز علامت‌گذاری شده‌اند *