قرعه کشی در اکسل

سلام دوستان عزیز وقتتون بخیر؛ آرزوی اوقات خوشی رو براتون داریم 🙂 در این جلسه از سری آموزش های پویش پایدار در خدمتتون هستیم برای آموزش قرعه کشی در اکسل با مثال.

قرعه کشی در اکسل با استفاده از توابع اعداد تصادفی

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

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

 

 

کاربرد تابع Randbetween اکسل در این مثال

همونطوری که میبینید 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

 

 

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

کاربرد تایع Index اکسل در قرعه کشی

در مرحله بعدی ما میخوایم مثلا اگر عدد 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 گفته. مثل تصویر زیر:

 

 

کاربرد تابع Concatenate اکسل در این مثال

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

تابع CONCANTENATE رشته های متنی رو به عنوان آرگومان هاش میگیره و اون ها رو بهم میچسبونه و به عنوان یک رشته متنی نشون میده. آرگومان اول این تابع میشه 4 رقم سمت چپ شماره برنده که با استفاده از تابع LEFT از شماره استخراج میشه. آرگومان دوم “***” و آرگومان سوم 4 رقم سمت راست شماره برنده است که با استفاده از تابع RIGHT استخراج میشه. پس در سلول شماره نمایش داده شده فرمول زیر را مشابه تصویر مینویسم:

 

 

پس از زدن دکمه Enter نتیجه مورد نظر ما به صورت تصویر زیر نمایش داده میشه و کار قرعه کشی در اکسل به پایان میرسه. حالا هر بار که فرمول داخل سلول C3 را اجرا کنیم یکی از 15 شماره موبایل به تصادف به عنوان برنده در سلول D3 به صورت محرمانه نمایش داده میشه.

 

 

ممنون از توجه شما 🙂

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