نحوه انتخاب نمونه تصادفی (قرعه‌کشی در اکسل)

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

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

• نمونه‌ی تصادفی چیست؟
• نحوه‌ی انتخاب تصادفی در اکسل با فرمول
  • انتخاب یک عدد یا مقدار متنی تصادفی از یک لیست
  • انتخاب تصادفی بدون تکراری
  • انتخاب تصادفی سطرها در اکسل

نمونه‌ی تصادفی چیست؟

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

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

به عنوان مثال، شما می‌خواهید یک بررسی کمی در بین مشتریان خود انجام دهید. بدیهی است، عاقلانه نیست که یک پرسشنامه برای هر فرد در بانک اطلاعاتی چند هزار نفری خود ارسال کنید؛ بنابراین، نظرسنجی شما با چه کسانی است؟ آیا این 100 مشتری جدید خواهد بود، یا 100 مشتری اول که به ترتیب حروف الفبا ذکر شده‌اند، یا 100 نفر با کوتاه‌ترین نام؟ هیچکدام از این رویکردها متناسب با نیازهای شما نیستند زیرا آن‌ها ذاتاً مغرضانه هستند. برای به دست آوردن یک نمونه‌ی بی‌طرف در جایی که هر کس فرصت انتخاب برابر خود را دارد، با استفاده از یکی از روش‌های گفته شده در زیر، یک انتخاب تصادفی انجام دهید..

انتخاب تصادفی در اکسل با فرمول

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

نحوه‌ی انتخاب یک مقدار تصادفی از یک لیست

به فرض شما لیستی از نام‌ها در سلول‌های A2:A10 دارید و می‌خواهید به طور تصادفی یک نام را از لیست انتخاب کنید. این کار با استفاده از یکی از فرمول‌های زیر قابل انجام است:

=INDEX($A$2:$A$10,RANDBETWEEN(1,COUNTA($A$2:$A$10)),1)

یا

=INDEX($A$2:$A$10,RANDBETWEEN(1,ROWS($A$2:$A$10)),1)

تمام! انتخابگر نام تصادفی شما برای اکسل کاملاً تنظیم و آماده‌ی خدمت است:

توجه: لطفاً توجه داشته باشید که RANDBETWEEN یک تابع متغیر است، به این معنی که با هر تغییری که در صفحه‌ی کار ایجاد می‌کنید، دوباره محاسبه می‌شود. در نتیجه، انتخاب تصادفی شما نیز تغییر خواهد کرد. برای جلوگیری از این اتفاق، می‌توانید نام استخراج شده را کپی کرده و آن را به صورت مقدار (Value) در سلول دیگری بچسبانید (Paste Special > Values).

طبیعتاً، این فرمول‌ها نه تنها می‌توانند اسامی تصادفی را انتخاب کنند، بلکه اعداد تصادفی، تاریخ یا هر سلول تصادفی دیگری را نیز انتخاب می‌کنند.

نحوه‌ی کار این فرمول‌ها

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

به طور خاص، تابع RANDBETWEEN یک عدد صحیح تصادفی بین دو مقداری که شما تعیین می‌کنید ایجاد می‌کند. برای مقدار کمتر، عدد 1 را ارائه می‌کنید. برای مقدار بالاتر، از COUNTA یا ROWS استفاده می‌کنید تا تعداد ردیف کل را بدست آورید. در نتیجه، RANDBETWEEN یک عدد تصادفی را بین 1 و تعداد کل ردیف‌های موجود در مجموعه داده‌ی شما برمی‌گرداند. این عدد به آرگومان row_num از تابع INDEX می‌رود و می‌گوید کدام سطر انتخاب شود. برای argument_num، از 1 استفاده می‌کنیم زیرا می‌خواهیم مقداری را از ستون اول استخراج کنیم.

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

نحوه‌ی انتخاب به طور تصادفی در اکسل بدون تکراری

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

با لیستی از نام‌ها در سلول‌های A2:A16، لطفاً این مراحل را برای استخراج چند نام تصادفی دنبال کنید:

1. فرمول Rand را در B2 وارد کرده و آن را تا آخر ستون کپی کنید:
   =RAND()
2. فرمول زیر را در C2 قرار دهید تا مقداری تصادفی از ستون A استخراج شود:
   =INDEX($Aز2:$A$16, RANK(B2,$B$2:$B$16), 1)
3. فرمول فوق را به همان تعداد سلول‌هایی که می‌خواهید انتخاب کنید، کپی کنید. در مثال ما، فرمول را در چهار سلول دیگر کپی می‌کنیم (C2:C6).

تمام! پنج نام تصادفی بدون تکراری استخراج شده است.

نحوه‌ی کار این فرمول‌ها

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

• فرمول RAND ستون B را با اعداد تصادفی جمع می‌کند.
• تابع RANK رتبه‌ی عدد تصادفی را در همان ردیف برمی‌گرداند. به عنوان مثال، RANK(B2,$B$2:$B$16) در سلول C2 رتبه‌ی عدد موجود در سلول B2 را می‌گیرد. هنگام کپی کردن در C3، ارجاع نسبی B2 به B3 تغییر می‌کند و رتبه‌ی عدد موجود در سلول B3 را برمی‌گرداند و غیره.

عدد برگشتی توسط RANK به آرگومان row_num از تابع INDEX داده می‌شود. در آرگومان argument_num، ستون 1 را داده‌اید زیرا می‌خواهید مقدار را از ستون اول استخراج کنید.

احتیاط! همانطور که در تصویر بالا نشان داده شده‌است، انتخاب تصادفی اکسل ما فقط دارای مقادیر منحصر به فرد است. اما از لحاظ تئوری، احتمال بسیار کمی برای بودن نسخه‌های تکراری در نمونه‌ی شما وجود دارد. به این دلیل است: در یک مجموعه داده‌ی بسیار بزرگ، RAND ممکن است اعداد تصادفی تکراری تولید کند، و RANK همان رتبه را برای آن عدد باز می‌گرداند. به شخصه، هرگز در طول آزمون‌های خود با مقدار تکراری برخورد نکرده‌ایم، اما در تئوری، چنین احتمالی وجود دارد.

اگر به دنبال یک فرمول همه فن حریف برای انتخاب تصادفی فقط با مقادیر منحصر به فرد هستید، به جای RANK از RANK.EQ + COUNTIF استفاده کنید. فرمول کامل کمی بدقلق است، اما 100٪ عاری از تکراری است.:

=INDEX($A$2:$A$16, RANK.EQ(B2, $B$2:$B$16) + COUNTIF($B$2:B2, B2) – 1, 1)

نحوه‌ی انتخاب ردیف‌های تصادفی در اکسل

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

1. ستون جدیدی را در سمت راست یا سمت چپ جدول خود قرار دهیددرج کنید (ستون D در این مثال).
2. در سلول اول ستون درج‌شده، به استثنای عنوان ستون‌ها، فرمول RAND را وارد کنید: =RAND()
3. روی دسته‌ی پر کردن دوبار کلیک کنید تا فرمول تا پایین ستون کپی شود. در نتیجه، شما یک عدد تصادفی به هر سطر اختصاص خواهید داد.
4. اعداد تصادفی را از بزرگ به کوچک (largest to smallest) مرتب کنید (مرتب سازی بر اساس ترتیب صعودی می‌تواند عنوان ستون‌ها را در انتهای جدول منتقل کند، بنابراین حتماً به صورت نزولی مرتب کنید). برای این کار، به تب Data > گروه Sort & Filter بروید و بر روی دکمه‌ی ZA کلیک کنید. اکسل به طور خودکار انتخاب را گسترش می‌دهد و کل ردیف‌ها را به ترتیب تصادفی مرتب می‌کند. اگر از نحوه‌ی انتخاب تصادفی جدول خود کاملاً راضی نیستید، دوباره دکمه‌ی مرتب‌سازی را بزنید تا به آن دوباره مرتب شود.
   
5. در آخر، تعداد مورد نیازی از سطرها را برای نمونه‌ی خود انتخاب کنید، آن‌ها را کپی کرده و در هر کجا که دوست دارید پیست کنید.
   

برای مشاهده‌ی دقیق‌تر فرمول‌های مورد بحث در این آموزش، می‌توانید ورک‌بوک نمونه‌ی ما را با عنوان Excel Random Selection دانلود کنید.

دانلود