ایجاد کدهای دسترسی تصادفی درب

Question

Ray وظیفه تعیین و ردیابی کدهای دسترسی درب را برای شرکت خود دارد. اینها کدهای پنج رقمی تصادفی هستند که با 2 یا 3 شروع می شوند. ری تعجب می کند که آیا راهی در اکسل برای تولید چنین کدهای تصادفی برای یک فرد وجود دارد، بدون اینکه کد هر بار که کاربرگ دوباره محاسبه می شود و بدون تکرار کدی که قبلاً استفاده شده است، دوباره تولید شود. .

در واقع چند راه مختلف وجود دارد که می توانید به این مشکل نزدیک شوید. تولید کدها با استفاده از تابع RANDBETWEEN آسان است. به عنوان مثال، می توانید از موارد زیر استفاده کنید:

=RANDBETWEEN(20000,39999)

این یک کد تصادفی پنج رقمی ایجاد می کند که با 2 یا 3 شروع می شود، همانطور که Ray نیاز دارد. با این حال دو مشکل وجود دارد. اول، تابع مقادیر منحصر به فرد را بر نمی گرداند، به این معنی که می تواند همان مقدار را در فراخوانی های مختلف RANDBETWEEN برگرداند. مشکل دوم این است که شما نمی توانید آنچه را که برگردانده شده است را بر اساس لیستی از "کدهای استفاده شده" که ممکن است داشته باشید بررسی کنید (و رد صلاحیت کنید). غلبه بر این مسائل نیاز به کمی کار بیشتر برای کشف کردن دارد و راه حل ها مستلزم استفاده از ماکروها هستند.

ابتدا، اجازه دهید به یک راه حل بسیار آسان مبتنی بر کلان نگاه کنیم. در این رویکرد، فرض می کنم که شما چهار ستون اطلاعات در یک کاربرگ دارید. در ستون A، کدهای احتمالی درب، به ترتیب از 20000 تا 39999 را دارید. (بله، این بدان معناست که شما از 20000 ردیف برای همه این مقادیر استفاده می کنید—20001 ردیف اگر عنوان ستونی در ردیف 1 داشته باشید.) در ستون B قرار می دهید. نام افراد، در ستون C تاریخ تخصیص کد و در ستون D تاریخ خارج شدن کد از خدمت.

کلید این رویکرد این است که می توانید از یک ماکرو برای انتخاب یکی از کدهای درب (در ستون A) به صورت تصادفی استفاده کنید و سپس می توانید بررسی کنید که آیا چیزی در ستون های B:D برای آن کد وجود دارد یا خیر. اگر وجود داشته باشد، کد دیگری را به صورت تصادفی انتخاب می کند و این روند را تا زمانی که یک کد استفاده نشده پیدا کند ادامه می دهد. وقتی این کار را کرد، تاریخ امروز را به ستون C می چسباند و سلول ستون B را انتخاب می کند تا بتوانید نامی را وارد کنید که کد برای چه کسی است. این ماکرو است:

Sub PickCode()
    Dim bFlag As Boolean
    Dim iRow As Integer
    Dim sRange As String
    Dim c As Range

    bFlag = False     Assume we have not found a match

    Randomize
    While Not bFlag
        iRow = Int(Rnd * 20000)
         If you have a header row, then add 2
         If you have no header row, only add 1
        iRow = iRow + 2
        sRange = "B" & iRow & ":D" & iRow
        
        bFlag = True     Assume this row will be empty
        For Each c In Range(sRange)
            If Not IsEmpty(c) Then bFlag = False     Not empty; keep looking
        Next c
    Wend

     We only exit the While loop when weve found an empty code row
    Cells(iRow, 3) = Now()
     Remove the following two lines if you already
     formatted columns C and D as dates
    Cells(iRow, 3).NumberFormat = "mm/dd/yyyy"
    Cells(iRow, 4).NumberFormat = "mm/dd/yyyy"
    Cells(iRow, 2).Select
End Sub

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

اگر ترجیح می دهید مستقیماً یک کد درب ایجاد کنید (بنابراین لیست شما بسیار کوتاهتر از 20000 ردیفی است که رویکرد قبلی استفاده می شد)، پس بدیهی است که یک ماکرو متفاوت مورد نیاز است. در این رویکرد، دوباره فرض می کنم که از همان چهار ستون استفاده می شود.

Sub AddCode()
    Dim bFlag As Boolean
    Dim iCode As Integer
    Dim iTopRow As Integer
    Dim iLastRow As Integer
    Dim sRange As String
    Dim c As Range

     Find bounds of existing door codes
     If there is a header row, then start with row 2
    iTopRow = 2
    iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
    sRange = "A" & iTopRow & ":A" & iLastRow
    
    bFlag = False     Assume we have not found a valid code
    
    Randomize
    While Not bFlag
        iCode = Int(Rnd * 20000) + 20000
        
        bFlag = True     Assume this code will be good
        For Each c In Range(sRange)
            If c.Value = iCode Then bFlag = False     Code already in use
        Next c
    Wend

     We only exit the While loop when weve found an unused code
     Increment the last row
    iLastRow = iLastRow + 1
    Cells(iLastRow, 1) = iCode
    Cells(iLastRow, 3) = Now()
     Remove the following two lines if you already
     formatted columns C and D as dates
    Cells(iLastRow, 3).NumberFormat = "mm/dd/yyyy"
    Cells(iLastRow, 4).NumberFormat = "mm/dd/yyyy"
    Cells(iLastRow, 2).Select
End Sub

ماکرو تعیین می کند که کدام ردیف ها، بر اساس ستون A، حاوی کدهای درب از قبل تعیین شده باشند. سطرهای شروع و پایان به ترتیب در متغیرهای iTopRow و iLastRow قرار می گیرند. یک کد درب تصادفی احتمالی تولید و در متغیر iCode ذخیره می شود، سپس هر یک از کدهای درب موجود بررسی می شود تا ببینیم آیا با مقدار موجود در iCode مطابقت دارد یا خیر. اگر تطابق وجود داشته باشد، حلقه تکرار می شود تا زمانی که یک کد استفاده نشده تولید شود.

در آن مرحله، کد در اولین سلول خالی ستون A، تاریخ امروز در ستون C قرار می گیرد و سلول در ستون B انتخاب می شود تا بتوانید نام افراد را وارد کنید.

اگر به دلایلی نمی توانید از ماکروها در ورک بوک خود استفاده کنید، می توانید از یک رویکرد فرمولی استفاده کنید، اما نیاز به استفاده از دو سلول اضافی دارد. در توضیح این موضوع، من فرض می کنم که همان چهار ستون مورد استفاده قرار می گیرند که قبلاً توضیح داده شد، و ستون A حاوی فهرست کدهای درب اختصاص داده شده است. دو سلول اضافی من F2 و F3 خواهند بود. در F2، فقط یک کد درب تصادفی در محدوده مورد نظر خود ایجاد کنید:

=RANDBETWEEN(20000,39999)

در سلول F3 فرمول خود را برای تعیین اینکه آیا این کد درب تصادفی قبلاً استفاده شده است قرار می دهید:

=IF(ISNA(MATCH(F2,A:A,0)),"Unused Code", "CODE IN USE; PRESS F9")

این از تابع MATCH برای تعیین اینکه آیا کد تصادفی (F2) در هر نقطه ای از ستون A ظاهر می شود یا خیر استفاده می کند یا خیر. این بدان معنی است که متن "Uused Code" به عنوان نتیجه فرمول ظاهر می شود.

اگر کد در ستون A ظاهر شود، MATCH ردیفی را که تطابق در آن رخ می دهد برمی گرداند، بنابراین متن "CODE IN USE; PRESS F9" نمایش داده می شود. هنگامی که F9 فشار داده می شود، یک کد درب تصادفی جدید ایجاد می شود و پیام مناسب نمایش داده می شود. وقتی کد در را پیدا کردید که می توانید استفاده کنید، فقط آن را به صورت دستی به ستون A اضافه کنید و ستون های دیگر را به دلخواه پر کنید.