قرعه کشی در اکسل با استفاده از توابع اعداد تصادفی
در این پست میخوایم براتون یکی دیگه از کاربردهای خیلی جالب اکسل رو آموزش بدیم. شاید تو برنامه های تلویزیونی مثل برنامه نود دیده باشید که به طور تصادفی یک شماره موبایل از بین هزاران شماره به طور تصادفی برنده میشه. اما چطور این کار انجام میشه؟ یکی از راه های این کار استفاده از نرم افزار اکسل و توابع اعداد تصادفی اونه. برای آموزش قرعه کشی در اکسل به مثال زیر توجه کنید.
در یک مسابقه 15 نفر شرکت کردن که شماره تماس اون ها رو داریم. میخوایم یکی از این 15 نفر را به طور تصادفی انتخاب و به عنوان برنده مسابقه اعلام کنیم. برای این منظور یه جدول مثل جدول زیر در اکسل ایجاد می کنیم.
جدول شماره موبایل شرکت کننده ها در اکسل
همونطوری که میبینید 15 شماره موبایل در سلول های A1 تا A15 وارد شده. پس نیاز داریم تا یه عدد تصادفی بین 1 تا 15 ایجاد کنیم و هر عددی که نتیجه شد، شماره موبایل اون رو به عنوان برنده اعلام کنیم. این کار رو تابع RANDBETWEEN انجام میده. تابع RANDBETWEEN شامل دو آرگومان به صورت زیر هستش:
(RANDBETWEEN(bottom,top
bottom: حد پایین اعداد تصادفی
top: حد بالای اعداد تصادفی
ما در مثال میخوایم یک عدد تصادفی بین 1 تا 15 ایجاد کنیم پس در سلول C3 که مربوط به شماره ردیف برنده است، فرمول زیر را مینویسیم:
(RANDBETWEEN(1,15
اما موضوعی که وجود داره اینه که اگر تعداد شماره ها زیاد باشه دیگه شمارشش رو بهتره ما انجام ندیم و به خود اکسل بسپریم. مثلا توی این مثال اگر بخوایم به جای اینکه خودمون عدد 15 رو داخل آرگومان دوم بنویسیم، به اکسل بسپریم که تعداد شماره موبایل ها رو بشمره باید از تابع COUNTA استفاده کنیم. تابع COUNTA تعداد سلول های پر شده یک بازه ای که بهش میدیم رو میشماره. پس فرمول رو به صورت زیر بازنویسی می کنیم:
((RANDBETWEEN(1,COUNTA(A1:A15
تابع RANDBETWEEN برای قرعه کشی در اکسل
حالا هر بار که فرمول بالا را اجرا کنیم یه عدد تصادفی بین 1 تا 15 که مربوط به یکی از شماره موبایل ها هست رو بر میگردونه.
در مرحله بعدی ما میخوایم مثلا اگر عدد 7 از فرمول بالا نتیجه شده، 7 امین شماره در سلول برنده درج بشه. یعنی شماره موبایل برنده رو برامون توی سلول C3 نمایش بده.
خوب اینجا یکم نیاز هست راجع به تابع INDEX توضیح بدیم. تابع INDEX دو حالت داره که در زیر میبینید:
INDEX(array,row_num,[column_num])
INDEX(reference,row_num,[column_num],[area_num])
ما در این مثال با حالت اول تابع INDEX کار داریم و همون رو توضیح میدیم. این تابع زمانی به کار میاد که ما بخوایم از یک جدول یکی از سلول هاش رو استخراج کنیم. در آرگومان اول یعنی array، جدولی از داده ها رو میگیره و در آرگومان دوم شماره ردیف اون سلول مد نظر رو میگیره و آرگومان سومش هم که اختیاریه و مربوط به شماره ستون سلول مورد نظر از جدوله. به عنوان خروجی هم محتوای اون سلول مورد نظر رو برمیگردونه.
خوب! تا اینجای کار ما با استفاده از تابع RANDBETWEEN و COUNTA تونستیم شماره ردیف برنده رو به دست بیاریم. اما میخوایم شماره موبایلی که توی اون شماره ردیف هست رو رو برای ما نمایش بده. پس از تابع INDEX استفاده می کنیم. به این صورت که در آرگومان اولش A1:A15 رو میدیم و در آرگومان دوم، همون فرمول ((RANDBETWEEN(1,COUNTA(A1:A15 رو میدیم تا بره شماره موبایلی رو پیدا کنه که فرمول RANDBETWEEN گفته. مثل تصویر زیر:
ترکیب تابع INDEX و RANDBETWEEN برای نمایش شماره موبایل برنده در اکسل
میبینید که بعدش از زدن دکمه Enter شماره موبایل برنده در قرعه کشی نمایش داده میشه. اما اگر در برنامه های تلویزیونی هم دیده باشید، به دلایل محرمانه شماره موبایل برنده به صورت کامل نمایش داده نمیشه و قسمتی از شماره برنده به صورت ستاره دار نمایش داده میشه. ما هم میخوایم از شماره برنده فقط 4 رقم اول و 4 رقم آخر دیده بشه و هرچی وسطش هست ستاره نشون بده. پس باید سه رقم اول بعلاوه سه تا ستاره بعلاوه 4 رقم آخر بهم بچسبن. این کار رو تابع CONCATENATE انجام میده. تابع CONCANTENATE رشته های متنی رو به عنوان آرگومان هاش میگیره و اون ها رو بهم میچسبونه و به عنوان یک رشته متنی نشون میده. آرگومان اول این تابع میشه 4 رقم سمت چپ شماره برنده که با استفاده از تابع LEFT از شماره استخراج میشه. آرگومان دوم “***” و آرگومان سوم 4 رقم سمت راست شماره برنده است که با استفاده از تابع RIGHT استخراج میشه. پس در سلول شماره نمایش داده شده فرمول زیر را مشابه تصویر مینویسم:
ترکیب تابع CONCATENATE و LEFT و RIGHT برای ستاره دار کردن شماره تلفن در اکسل
پس از زدن دکمه Enter نتیجه مورد نظر ما به صورت تصویر زیر نمایش داده میشه و کار قرعه کشی در اکسل به پایان میرسه. حالا هر بار که فرمول داخل سلول C3 را اجرا کنیم یکی از 15 شماره موبایل به تصادف به عنوان برنده در سلول D3 به صورت محرمانه نمایش داده میشه.
نتیجه قرعه کشی در اکسل