برجسته کردن نقض الگوی در اکسل

Question

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

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

=OR(ISNUMBER(FIND("-",SUBSTITUTE(A1," - ",""))),
ISNUMBER(FIND(",",SUBSTITUTE(A1,", ",""))),
ISNUMBER(FIND(" ,",A1)))

در اینجا فرمول را به سه خط تقسیم کردم، اما باید آن را یک فرمول کامل در نظر گرفت. این فرمول الگوهای صحیح (فاصله، خط تیره، فاصله و کاما، فاصله) را از نام فایل حذف می کند و سپس بررسی می کند که آیا خط تیره یا کاما در نام فایل باقی مانده است یا خیر. اگر یکی باقی بماند، فرمول True را برمی‌گرداند.

می توانید یک قانون قالب بندی شرطی برای استفاده از فرمول به این صورت تنظیم کنید:

شکل 1. کادر محاوره ای New Formatting Rule.

1. سلول هایی را انتخاب کنید که شامل همه نام فایل هایی است که می خواهید بررسی شوند.
2. در حالی که تب صفحه اصلی نوار نمایش داده می شود، روی گزینه Conditional Formatting در گروه Styles کلیک کنید. اکسل پالتی از گزینه های مربوط به قالب بندی شرطی را نمایش می دهد.
3. Highlight Cells Rules را انتخاب کنید و سپس بیشتر قوانین را از منوی فرعی انتخاب کنید. اکسل کادر محاوره ای New Formatting Rule را نمایش می دهد. (شکل 1 را ببینید.)
4. در ناحیه Select a Rule Type در بالای کادر محاوره ای، Use a Formula to Determin Your Cells Format را انتخاب کنید.
5. در کادر Format Values ​​Where This Formula Is True، فرمول طولانی که قبلاً بحث شده را وارد کنید.
6. روی Format کلیک کنید تا کادر محاوره ای Format Cells نمایش داده شود.
7. با استفاده از کنترل‌های موجود در کادر محاوره‌ای، قالبی را که می‌خواهید برای برجسته کردن سلول‌هایی که الگوی شما را نقض می‌کنند، مشخص کنید.
8. برای حذف کادر محاوره ای Format Cells روی OK کلیک کنید. قالب بندی که در مرحله 7 مشخص کرده اید اکنون باید در قسمت پیش نمایش قانون ظاهر شود.
9. روی OK کلیک کنید.

اگر سلول‌هایی که در مرحله 1 انتخاب کرده‌اید با سلول A1 شروع نشده‌اند، باید فرمول مورد استفاده در مرحله 5 را تغییر دهید تا سلول ابتدایی شما را منعکس کند. (هر سه نمونه A1 در فرمول باید برای ارجاع به سلول اولیه شما تغییر کنند.)

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

راه حل این امر می تواند یک فرمول بسیار طولانی تر یا دور زدن مسیر قالب بندی شرطی به طور کلی و شروع به استفاده از ستون های کمکی باشد. این دقیقاً به دومین "گوچا" وارد می شود، و یک مورد بزرگ است: اگر قالب بندی شرطی (یا ستون های کمکی حاوی فرمول ها را اضافه کنید) به ده هزار ردیف، متوجه افزایش قابل توجهی در مدت زمان محاسبه مجدد کاربرگ خود خواهید شد. . وقتی شروع به اضافه کردن فرمول های زیادی به کاربرگ می کنید، راهی برای حل این مشکل وجود ندارد.

به همین دلیل، ممکن است ایجاد یک ماکرو که سلول ها را برجسته می کند، مناسب تر باشد. زمانی که می‌خواهید الگوها را بررسی کنید، ماکرو می‌تواند به صورت دستی اجرا شود، که به این معنی است که محاسبه مجدد کاربرگ عادی شما کند نمی‌شود.

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

Sub CheckFilenames1()
    Dim bBad As Boolean
    Dim c As Range
    Dim sTemp1 As String
    Dim sTemp2 As String

    For Each c In Selection
        bBad = False
        sTemp1 = c.Text

        If Instr(sTemp1, "  -") > 0 Then bBad = True
        If Instr(sTemp1, "-  ") > 0 Then bBad = True
        If Instr(sTemp1, " ,") > 0 Then bBad = True
        If Instr(sTemp1, ",  ") > 0 Then bBad = True
        sTemp2 = Replace(sTemp1, " - ", "")
        If Instr(sTemp2, "-") > 0 Then bBad = True
        sTemp2 = Replace(sTemp1, ", ", "")
        If Instr(sTemp2, ",") > 0 Then bBad = True

        If bBad Then
            c.Interior.Color = vbYellow
        Else
            c.Interior.Color = xlColorIndexNone
        End If
    Next c
End Sub

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

Sub CheckFilenames2()
    Dim bBad As Boolean
    Dim c As Range
    Dim sTemp1 As String
    Dim sTemp2 As String

    For Each c In Selection
        bBad = False
        sTemp1 = c.Text

        If InStr(sTemp1, "  -") > 0 Then bBad = True
        If InStr(sTemp1, "-  ") > 0 Then bBad = True
        If InStr(sTemp1, " ,") > 0 Then bBad = True
        If InStr(sTemp1, ",  ") > 0 Then bBad = True
        sTemp2 = Replace(sTemp1, " - ", "")
        If InStr(sTemp2, "-") > 0 Then bBad = True
        sTemp2 = Replace(sTemp1, ", ", "")
        If InStr(sTemp2, ",") > 0 Then bBad = True

        If bBad Then c.Offset(0, 1) = "BAD"
    Next c
End Sub

هنگام اجرا، این تغییر ماکرو متن "BAD" را در سلول سمت راست نام فایل های الگوی نامناسب وارد می کند. سپس می توانید از قابلیت های فیلتر اکسل برای نمایش تنها ردیف هایی که حاوی متن هستند استفاده کنید.

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

Sub FixFilenames()
    Dim myArry() As String
    Dim sTemp As String
    Dim c As Range
    Dim s As Variant

    For Each c In Selection
        myArry = Split(c, "-")
        sTemp = ""
        For Each s In myArry
            If sTemp > "" Then
                sTemp = sTemp & " - " & Trim(s)
            Else
                sTemp = Trim(s)
            End If
        Next s
        myArry = Split(sTemp, ",")
        sTemp = ""
        For Each s In myArry
            If sTemp > "" Then
                sTemp = sTemp & ", " & Trim(s)
            Else
                sTemp = Trim(s)
            End If
        Next s
        c = sTemp
    Next c
End Sub