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

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

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

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

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

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

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

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

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

فیلم آموزش ساخت داده‌ های تصادفی در اکسل + آزمایش فرمول‌ها (رایگان) در فرادرس

کلیک کنید

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

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

RAND ( )

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

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

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

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

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

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

$$= 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 مشخص شده‌اند، مربوط به معکوس توابع احتمال بوده و برای تولید عدد تصادفی از آن توزیع به کار می‌روند. جدول زیر به معرفی توابع معکوس احتمال برای توزیع‌های مختلف آماری پرداخته است که در اکسل قابل استفاده هستند.

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

برای مثال فرض کنید می‌خواهیم مقداری تصادفی از توزیع گاما تولید کنید. اگر توزیع گامای مورد نظر با پارامترهای ۱ و ۲ باشد، فرمول زیر می‌تواند یک عدد تصادفی از این توزیع در نقطه 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

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

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

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

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

مثال

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

فیلم آموزش رسم نمودار در اکسل – طراحی انواع نمودار و تنظیمات در فرادرس

کلیک کنید

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

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

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

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

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

مثال

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

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

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

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