قرعه کشی در اکسل می تواند بسیار جذاب باشد به خصوص که آن را به روشهای مختلف و پیشرفته انجام دهید که در این آموزش رایگان به مبحث قرعه کشی و اعداد تصادفی خواهیم پرداخت. در ادامه روش دیگری را معرفی کردیم که در آن امکان تقلب در قرعه کشی هم وجود دارد.
فهرست محتوا
ویدیو آپدیت قرعه کشی در اکسل
تابع Randbetween و استفاده برای قرعه کشی در اکسل
تابع Randbetween همونطور که از اسمش هم مشخصه انتخاب اعداد تصادفی بین بازه دو تا عدد هست یعنی چی یعنی اینکه یک عدد تصادفی بین کمترین و بیشترین عددی که ما بهش میدیم انتخاب میکنه ، توی مثال زیر نتیجه تابع یه عددی خواهد بود به صورت تصادفی بین 1 تا 10 که هر بار شیت رفرش بشه عدد تغییر خواهد کرد.
=RANDBETWEEN(1,10)
استفاده از تابع بسیار ساده هست و همونطوری که توی شکل زیر هم میبینید دو تا آرگومان بیشتر نداره که هر دو ضروری هستند و باید وارد بشن یکی کمترین مقدار و اون یکی بیشترین مقدار که نتیجه عددی تصادفی بین این دو تا عدد خواهد بود .
نکته : اگر بخواید از تابع Randbetween در چند سط استفاده کنید و نخواید که داده تکراری بهتون برگردونه باید از تابع Rand هم کمک بگیرید که تو ادامه میگم چطوری این کار رو انجام بدین.
کاربرد تابع Rand در اکسل چیست؟
تابع Rand در اکسل هم مخفف کلمه Random هست و هیچ آرگومانی هم نداره و به شکل Rand() استفاده میشه یعنی تابع رو مینویسید و پرانز رو باز میکنید و می بندید . حالا نتیجه چی هست ؟
یک عدد تصادفی بین 0 و 1 که احتمال تکرار خیلی خیلی کمی داره و خیلی جاها میشه ازش به عنوان یه تابع کمکی مثل همین آموزشی که ما داریم استفاده کرد .
پس تابع Rand در اکسل یک کار ساده بیشتر انجام نمیده و اون هم نمایش یک عدد تصادفی بین 0 و 1 هست از این تابع هم می توان برای قرعه کشی در اکسل بهره برد.
از تابع Rank در اکسل چه استفاده ای میشه ؟
دلیل اینکه این سه تا تابع رو با هم دارم آموزش میدم این هست که توی انجام قرعه کشی در اکسل از این سه تابع به همراه تابع Vlookup استفاده شده و تابع Rank در اکسل هم وظیفش اینه که مشخص کنه توی یه محدوده عددی ، عدد انتخاب شده چه رتبه ای داره .
مثال میتونه دانش آموزان کلاس باشن که اگه معدل همه دانش آموزان رو داشته باشیم و بخوایم بدونیم که مثلا معدل 18 چه رتبه ای در کلاس داشته میتونیم از تابع Rank در اکسل استفاده کنیم، و اما تابع Rank همونطور که در تصویری زیر میبینید دارای 3 تا آرگومان هست که دوتای اولی ضروری و آخری هم اختیاری هست که به صورت پیشفرض 0 یا False در نظر گرفته میشه که در ادامه توضیخ میدم .
آرگومان اول تابع Rank در اکسل که Number هست :
توی این قسمت باید عددی رو وارد کنیم یا سلول عددی رو انتخاب کنیم که قراره رتبش مشخص بشه .
آرکومان Ref :
اینجا باید محدوده عددی مورد نظر که رتبه عدد انتخابی شما بر اساس اون محدوده سنجیده میشه رو باید انتخاب کنید . که حتما هم باید Lock یا مطلق بشه که با زدن کلید F4 این اتفاق میوفته یعنی آیکون $ اضافه میشه و آدرس در اصطلاح مطلق میشه .
آرگومان Order که انتخابش ضروری نیست و در صورت عدم انتخاب به صورت پیشفرض 0 در نظر گرفته میشه :
این آرگومان دو تا گزینه بیشتر قبول نمیکنه که یا باید 0 باشه یا 1 که اگر 0 باشه رتبه عدد انتخابی شما رو از بالا به پائین مرتب میکنه ، یعنی چی ؟ یعنی اینکه اگه همون مثال کلاس رو در نظر بگیرم و 10 تا معدل داشته باشیم اگر مقدار Order رو تو آرگومان مربوطه 0 قرار بدیم بالاترین معدل رتبه یک رو داره و به همون ترتیب رتبه های بعدی مشخص میشن ولی اگه مقدار تابع رو برابر 1 قرار بدیم پائینترین معدل ، بالاترین رتبه رو میگیره و میشه رتبه یک .
حالا با ترکیب توابع بالا که توضیح مختصری در موردشون دادم یه کار جالب توی اکسل انجام دادیم که توسط اون میتونید قرعه کشی بدون نمایش عدد تکراری انجام بدین که توی فیلم آموزشی زیر کاملا مشخصه و از هر سه تابع Randbetween در اکسل و تابع Rand در اکسل و تابع Rank در اکسل به همراه تابع Vlookup و چند تا ترفند استفاده شده .
با استفاده از تابع Randbetween و تابع Vlookup و قابلیت قالبدهی شرطی می توانیم قرعه کشی در اکسل راه اندازی کنیم که آموزش آن را در ادامه به صورت رایگان به همراه فایل نمونه خواهید دید.
روشهای مختلف ایجاد عدد تصادفی در اکسل
1- استفاده از تابع Rand
- تابع Rand هیچ آرگومانی ندارد و فقط با پرانتز باز و بسته استفاده می شودو نتیجه آن هم یک عدد تصادفی اعشاری بین ۰ و ۱ هست که احتمال تکرار خیلی خیالی کمی داره .
۲- استفاده از تابع int اکسل به همراه تابع Rand
- فرض می کنیم نتیجه تابع Rand عدد ۰٫۴۳۲۸۲۴ شده است از این عدد چه استفاده می توانیم بکنیم ؟
- حالا اگر بخواهیم قرعه کشی در اکسل بین اعداد ۱ تا ۵۰ انجام شود کافیست تابع INT را به همراه تابع Rand به شکل زیر به کار ببریم .
=INT(RAND()*50)+1
۳- استفاده از تابع Randbetween در اکسل
- استفاده از این تابع نیز بسیار ساده می باشد و دو آرگومان دارد ، آرگومان اول عدد کوچکتر و آرگومان دوم عدد بزرگتر ، سپس این تابع یک عدد تصادفی بین دو عدد را بر می گرداند . و می تواند برای قرعه کشی در اکسل استفاده شود
=RANDBETWEEN(0,50)
پیشنهاد می شود برای درک بهتر ، مطلب مربوط به تابع INT و تابع Rand را مطالعه بفرمائید
ایجاد عدد تصادفی در اکسل به کمک VBA
آیا می توان از تابع Randbetween به همان شکلی که در اکسل استفاده می شود در VBA بهره برد؟
با کمی تغییرات بله و باید مراحل زیر دنبال شود :
۱- در فایل اکسل خود یک Shape یا عکس یا دکمه قرار دهید.
۲- روی آن کلیک کرده و گزینه Assign Macro را انتخاب کنید .
۳- در پنجره باز شده گزینه New را انتخاب کنید .
۴- حالا در محیط کد نویسی قرار دارید و باید کد زیر را بنویسید :
Range("d2").Value = WorksheetFunction.RandBetween(1, 5)حالا هربار که روی کنترل مد نظر که در فایل قرار داده اید کلیک کنید ، یک عدد تصادفی بین ۱ و ۵ در سلول D2 قرار داده می شود .
۵- اگر بخواهید بازه اعداد متغیر باشد کد بالا را به شکل زیر استفاده کنید .
Range("d2").Value = WorksheetFunction.RandBetween(Range("A2"), Range("A3"))با هر بار کلیک روی کنترل یک عدد تصادفی بین دو عدد وارد شده در A2 و A3 در سلول D2 قرار می گیرد.
حلقه تکرار برای جذاب شدن قرعه کشی در اکسل
برای اینکه مانند تصویر زیر قرعه کشی چندین بار پشت سر هم بدون وقغه اننجام شود مراحل نوشته شده را انجام دهید .
۱- ابتدا با استفاده از دستورهای زیر دو متغیر از نوع عددی تعریف کنید
Dim i As Integer
For i = i To 1000
Range("d2").Value = WorksheetFunction.RandBetween(1, 5)
Next i۲- با استفاده از دستور For حلقه تکرار بین متغیر و یک عدد دلخواه انتخاب نمائید .
۳- دستوری که باعث انتخاب عدد تصادفی میشد و در مرحله قبلی توضیح داده بودیم را داخل حلقه قرار دهید .
این کار باعث میشود که انتخاب عدد تصادفی به تعدادی که شما مشخص کرده اید با سرعت بالا انجام شود . در مثال بالا ۱۰۰۰ با قرعه کشی می شود .
استفاده از کاندیشنال فرمتینگ برای رنگی کردن ردیفهای برنده
حالا اگر بخواهیم مانند تصویر زیر اعداد برنده به صورتی رنگی تغییر کنند باید از کاندیشنال فرمتینگ استفاده کنیم .
1- فرض می کنیم عدد انتخاب شده در سلول E2 قرار می گیرد و اعداد ما در رنج A2:A6 قرار دارند .
۲- محدوده A2:A6 را انتخاب میکنیم و از تب Home روی گزینه Conditional Formatting کلیک کرده و گزینه Mange Rules را انتخاب میکنیم .
۳- روی گزینه New Rule کلیک میکنیم .
۴- گزینه Use a Formula to Determine which Cell in Format را انتخاب کرده و مانند تصویر زیر عبارت =A2=$E$2 را در باکس قرار داده و رنگ دلخواه را انتخاب کرده و Ok می کنیم .
مرتب سازی یک لیست به صورت تصادفی
برای اینکه بتوانیم مبحث قرعه کشی در اکسل را تکمیل کنیم ، فرض می کنیم که در محدوده A2 تا A11 اسامی افراد وارد شده است و می خواهیم در محدوده B2 تا B11 همان افراد به صورت تصادفی لیست شوند ، برای این منظور کافیست مراحل ذیل را انجام دهیم.
- به سلول B2 رفته و فرمول زیر را در آن وارد میکنیم .
- محدوده B2:B11 را انتخاب می کنیم و کپی می کنیم .
- در همان محدوده کلیک راست کرده و از گزینه Paste گزینه Paste Values را انتخاب می کنیم.
=SORTBY(A5:A14,RANDARRAY(ROWS(A5:A14)))
در فرمول بالا در ترکیب سه تابع SORTBY و تابع RANDARRAY و تابع ROWS استفاده شده است و به دلیل دو تابع اول باید از آفیس 2021 استفاده کنیم و اگر به هر دلیلی امکان نصب آفیس 2021 را ندارید ، می توانید از روش زیر استفاده نمائید.
- در سلول B2 تابع Rand را بنویسید.
- نتیجه به دست آمده را تا سلول B11 امتداد دهید .
- ستون B را به صورت نزولی یا صعودی مرتب سازی کنید.
- حالا می توانید ستون B را که به صورت کمکی از آن استفاده کرده بودید پاک کنید.
تقلب در قرعه کشی اکسل
آیا میشود در روشهای بالا تقلب کرد ؟ اگر از روش استفاده از تابع RandBetween استفاده کنید و کدنویسی نداشته باشید ، احتمال تقلب وجود ندارد .
ولی اگر بخواهید با کدنویسی این کار را انجام دهید و قرعه کشی کنید باید بگویم که امکان تقلب وجود دارد
تصویر بالا را ببینید ، اگر کدهای زیر را برای دکمه قرعه کشی قرار دهیم ، هر بار که روی آن کلیک کنیم لیست اسامی شروع به رنگی شدن تصادفی به مدت زمان تصادفی می کنند ولی در نهایت هربار ردیف 7 داده ها یعنی نادر بحری 😎 انتخاب می شود .
Do
Range("a1") = WorksheetFunction.RandBetween(1, 11)
Range("a2") = WorksheetFunction.RandBetween(1, 300)
Loop Until Range("a2") = 250 And Range("a1") = 7خط آخر کدها ، فارغ از اینکه چه اتفاقی در خطهای قبلی رخ می دهد نهایتا سلول A1 را برابر 7 قرار می دهد که شماره ردیف استفاده شده در تابع INDEX است . مشاهده آموزش ویدیو کامل این روش .
در نهایت اگر مشا ایده یا نظری برای انجام بهتر قرعه کشی در اکسل دارید ، می توانید در قسمت نظرات عنوان نمائید.
