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

Question

مارلین یک معلم است و دانش‌آموزانی دارد که عاشق جستجوی کلمات هستند. به نظر او ساختن آنها کاملاً زمان بر است، اما به نظر می رسد که دانش آموزان وقتی از آنها استفاده می کند، مطالب درسی را خیلی بهتر به خاطر می آورند. مارلین به این فکر کرد که آیا راهی برای تکمیل خودکار محدوده ای از سلول ها با حروف الفبا، A تا Z وجود دارد. به این ترتیب او می تواند از این ویژگی برای پر کردن مربع های جستجوی کلمه با حروف استفاده کند، قبل از اینکه برخی از آن حروف را با حروف جایگزین کند. کلمات واقعی که باید جستجو شوند

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

شکل 1. کادر محاوره ای فهرست های سفارشی.

1. کادر محاوره ای Excel Options را نمایش دهید. (در اکسل 2007 روی دکمه آفیس کلیک کنید و سپس روی Excel Options کلیک کنید. در اکسل 2010 و اکسل 2013 تب File روبان را نمایش دهید و سپس روی گزینه ها کلیک کنید.)
2. اگر از Excel 2007 استفاده می کنید، مطمئن شوید که Popular در سمت چپ کادر محاوره ای انتخاب شده است. اگر از اکسل 2010 یا اکسل 2013 استفاده می کنید، روی Advanced در سمت چپ صفحه کلیک کنید و به قسمت عمومی بروید.
3. روی Edit Custom Lists کلیک کنید. اکسل کادر محاوره ای لیست های سفارشی را نمایش می دهد و کادر گفتگوی گزینه های اکسل را پنهان می کند. (شکل 1 را ببینید.)
4. گزینه NEW LIST را از لیست Custom Lists در سمت چپ کادر محاوره ای انتخاب کنید.
5. در کادر List Entries، هر حرف از حروف الفبا را در هر خط یک حرف وارد کنید. (بعد از هر حرفی که تایپ می کنید Enter را فشار دهید.)
6. وقتی کارتان تمام شد، روی دکمه افزودن کلیک کنید.
7. برای اتمام روی OK کلیک کنید.

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

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

1. سلول های حاوی حروف الفبا را انتخاب کنید.
2. کادر محاوره ای Excel Options را نمایش دهید. (در اکسل 2007 روی دکمه آفیس کلیک کنید و سپس روی Excel Options کلیک کنید. در اکسل 2010 و اکسل 2013 تب File روبان را نمایش دهید و سپس روی گزینه ها کلیک کنید.)
3. اگر از Excel 2007 استفاده می کنید، مطمئن شوید که Popular در سمت چپ کادر محاوره ای انتخاب شده است. اگر از اکسل 2010 یا اکسل 2013 استفاده می کنید، روی Advanced در سمت چپ صفحه کلیک کنید و به قسمت عمومی بروید.
4. روی Edit Custom Lists کلیک کنید. اکسل کادر محاوره ای لیست های سفارشی را نمایش می دهد و کادر گفتگوی گزینه های اکسل را پنهان می کند. همچنین باید توجه داشته باشید که محدوده سلول هایی که در مرحله 1 انتخاب کرده اید، درست در سمت چپ دکمه Import نشان داده شده است.
5. روی دکمه Import کلیک کنید. اکسل محتویات سلول های انتخاب شده را به عنوان یک لیست سفارشی اضافه می کند.
6. روی OK کلیک کنید.

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

یک راه آسان برای بدست آوردن حروف تصادفی استفاده از فرمول زیر است:

=CHAR(RANDBETWEEN(65,90))

این فرمول کار می کند زیرا تابع RANDBETWEEN یک مقدار عددی تصادفی را بین دو مقدار مرزی ارائه شده برمی گرداند. در این صورت مقداری بین 65 تا 90 را برمی گرداند که به ترتیب مقادیر ASCII حروف A و Z هستند. سپس تابع CHAR برای تبدیل این مقدار عددی تصادفی به یک حرف واقعی استفاده می شود.

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

Sub AlphaFill()
    Dim Cell, CellChars
    Dim Default, Prompt, Title
    Dim rangeSelected As Range
    Dim UpperCase As Boolean

    Title = "AlphaFill Cell Selection"
    Default = Selection.Address
    Prompt = vbCrLf _
      & "Use mouse in conjunction with " _
      & "SHIFT and CTRL keys to" & vbCrLf _
      & "click and drag or type in name(s) " _
      & "of cell(s) to AlphaFill" & vbCrLf & vbCrLf _
      & "Currently selected cell(s): "
      & Selection.Address

    On Error Resume Next
    Set rangeSelected = InputBox(Prompt, Title, _
      Default, Type:=8)
    If rangeSelected Is Nothing Then Exit Sub

    UpperCase = True
    Randomize
    For Each Cell In rangeSelected
        CellChars = Chr(64 + Int((Rnd * 26) + 1))
        If Not UpperCase Then CellChars = LCase(CellChars)
        Cell.Value = CellChars
    Next
End Sub

کد ماکرو همانطور که نوشته شده است، حروف بزرگ را در هر محدوده ای که شما مشخص می کنید وارد می کند. اگر می خواهید به جای آن از حروف کوچک استفاده کنید، تنها کاری که باید انجام دهید این است که متغیر UpperCase را به جای True روی False قرار دهید.