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

Question

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

همانطور که اغلب در مورد اکسل اتفاق می افتد، چندین روش مختلف وجود دارد که می توانید در پیش بگیرید. هر رویکردی که در این نکته بررسی می‌شود، فرض می‌کند که نام‌هایی که باید از بین آنها انتخاب کنید در سلول‌های A1 تا A1000 فهرست شده‌اند. البته، دامنه نام‌های شما می‌تواند کوتاه‌تر یا طولانی‌تر باشد، اما نکته اینجاست که آنها در سلول‌های به هم پیوسته در ستون A هستند. مثال‌ها همچنین فرض می‌کنند که باید 15 نام را به‌طور تصادفی از لیست انتخاب کنید.

اولین رویکرد استفاده از تابع INDEX است. فرمول زیر را در سلول های B1:B15 وارد کنید:

=INDEX(A:A,INT((RAND()*1000)+1),1)

یک فرمول مشابه از تابع OFFSET استفاده می کند:

=OFFSET($A$1,ROUNDUP(RAND()*1000,0),0,1,1)

ممکن است، اما نه محتمل، که در لیست حاصل دو بار یک نام را دریافت کنید. (عدم احتمال به دلیل اندازه لیست اصلی است. هر چه لیست بزرگتر باشد، احتمال وجود موارد تکراری در لیست استخراج شده کمتر می شود.) اگر نام تکراری دریافت کردید، به سادگی با فشار دادن F9، کاربرگ خود را مجدداً محاسبه کنید . . هر بار که دوباره محاسبه می کنید، لیست نام های استخراج شده دوباره تولید می شود.

رویکرد بالقوه دیگر نیاز به استفاده از چندین ستون دارد. به سادگی این مراحل را دنبال کنید:

     =RANK(B1,$B$1:$B$1000)

شکل 1. کادر محاوره ای Paste Special.

شکل 2. کادر محاوره ای مرتب سازی.

1. =RAND() را در سلول B1 وارد کنید .
2. فرمول زیر را در سلول C1 وارد کنید:
3. محدوده B1:C1 را انتخاب کنید و تا ردیف 1000 پر کنید.
4. محدوده B1:C1000 را انتخاب کنید.
5. برای کپی کردن محدوده در کلیپ بورد، Ctrl+C را فشار دهید .
6. تب Home نوار را نمایش دهید.
7. روی فلش رو به پایین زیر ابزار Paste کلیک کنید و سپس Paste Special را انتخاب کنید. اکسل کادر محاوره ای Paste Special را نمایش می دهد. (شکل 1 را ببینید.)
8. مطمئن شوید که دکمه رادیویی Values ​​انتخاب شده است.
9. روی OK کلیک کنید. اکنون مقادیر ایستا در B1:C1000 دارید، به این معنی که هر بار که کاربرگ دوباره محاسبه می شود، تغییر نمی کند.
10. یک سلول در ستون C را انتخاب کنید.
11. تب Data روبان را نمایش دهید.
12. بر روی ابزار Sort کلیک کنید. اکسل کادر محاوره ای مرتب سازی را نمایش می دهد. (شکل 2 را ببینید.)
13. روی OK کلیک کنید. جدول (محدوده A1:C1000) بر اساس مقادیر ستون C مرتب شده است.

نتیجه این است که ستون C اکنون شامل یک رتبه بندی از تمام اعداد تصادفی در ستون B است. 15 ردیف اول شامل نام های تصادفی شما است.

در این روش شما همچنین می‌توانید ستون C را کاملاً کنار بگذارید و به سادگی فهرست خود را بر اساس مقادیر تصادفی ثابت در ستون B مرتب کنید. باز هم، 15 مورد برتر نام‌های تصادفی شما خواهند بود.

البته، تعدادی راه حل کلان وجود دارد که می توانید برای این مشکل استفاده کنید. کدگذاری هر ماکرو مشابهی خواهد بود و بر تابع RND VBA برای تولید اعداد تصادفی تکیه می کند. از بین همه راه حل های کلان ممکن، شاید راه حل زیر منحصر به فرد ترین باشد و مزایایی را ارائه می دهد که با راه حل های کتاب کار که تاکنون مورد بحث قرار گرفته اند، در دسترس نیستند:

Sub GetRandom()
    Dim TempDO As Variant
    Dim iRows As Integer
    Dim iCols As Integer
    Dim iBegRow As Integer
    Dim iBegCol As Integer
    Dim sCells As String
    Dim J As Integer
    Dim iWantRow As Integer

    Set TempDO = New DataObject

    iRows = Selection.Rows.Count
    iCols = Selection.Columns.Count
    iBegRow = Selection.Row
    iBegCol = Selection.Column

    If iRows < 16 Or iCols > 1 Then
        MsgBox "Too few rows or too many columns"
    Else
        Randomize Timer
        sCells = ""
        For J = 1 To 15
            iWantRow = Int(Rnd() * iRows) + iBegRow
            sCells = sCells & Cells(iWantRow, iBegCol) & vbCrLf
        Next J
        TempDO.SetText sCells
        TempDO.PutInClipboard
    End If
End Sub

باید توجه داشته باشید که این ماکرو درست پس از اعلان متغیرها یک DataObject جدید را تعریف می کند و آن را به متغیر TempDO اختصاص می دهد. اگر ماکرو در این خط کد پخش شود، به سادگی به این معنی است که باید به VBA بگویید به کتابخانه مناسب ارجاع دهد:

شکل 3. کادر محاوره ای References.

1. در ویرایشگر VBA، References را از منوی Tools انتخاب کنید. VBA کادر محاوره ای References را نمایش می دهد. (شکل 3 را ببینید.)
2. در فهرست مراجع پیمایش کنید تا زمانی که یکی به نام Microsoft Forms Object Library را مشاهده کنید. (ممکن است یک شماره نسخه در نام مرجع موجود باشد، مانند Microsoft Forms 2.0 Object Library.)
3. مطمئن شوید که چک باکس سمت چپ کتابخانه اشیا انتخاب شده است.
4. روی OK کلیک کنید.

به منظور استفاده از ماکرو، فقط نام هایی را که می خواهید 15 نام تصادفی از آنها انتخاب کنید، انتخاب کنید. در مثال‌های تا کنون، محدوده A1:A1000 را انتخاب می‌کنید. سپس ماکرو 15 نام را به‌طور تصادفی از سلول‌ها بیرون می‌کشد و آنها را در کلیپ بورد قرار می‌دهد. هنگامی که ماکرو را اجرا می کنید، می توانید محتویات کلیپ بورد را در هر کجا که می خواهید بچسبانید. هر بار که ماکرو اجرا می شود، یک گروه ۱۵ تایی متفاوت انتخاب می شود.