قالب های شرطی برای ستون های زوج و فرد در اکسل

Question

زار نیاز دارد که یک قالب شرطی را برای مقادیر ستون A یک کاربرگ اعمال کند، اما او نمی تواند بفهمد که قوانین ممکن است چه باشند. علاوه بر ستون A، او همچنین داده هایی دارد که از ستون B شروع می شوند و به طور دوره ای ستون های جدیدی از داده ها را اضافه می کند. اگر در تمام ستون‌های فرد که با B شروع می‌شود، داده‌ای وجود داشته باشد، زار می‌خواهد یک قالب در ستون A اعمال شود. C، Zar می‌خواهد فرمت متفاوتی در ستون A اعمال شود. اگر داده‌هایی در تمام ستون‌های داده‌ای که با B شروع می‌شوند وجود داشته باشد - هر چند تعدادشان زیاد باشد - پس او می‌خواهد قالب سومی اعمال شود.

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

=COUNTA(B1:C1)=2
=COUNTA(B1)=1
=COUNTA(C1)=1

اولین فرمول اگر اطلاعاتی در هر دو B و C وجود داشته باشد True را برمی‌گرداند، اگر اطلاعاتی در B و C وجود داشته باشد، دومی اگر اطلاعاتی در C وجود داشته باشد، و سومی اگر اطلاعاتی در C وجود داشته باشد. تا زمانی که برای هر قانون/فرمول "Stop If True" را انتخاب کنید، سپس فرمت شما به خوبی کار خواهد کرد.

ایجاد یک فرمول برای چندین ستون فراتر از B و C فقط کمی دشوارتر است. همان سه نوع فرمول به ترتیب به صورت زیر خواهد بود:

=COUNTA(B1:G1)=6)
=COUNTA(B1,D1,F1)=3
=COUNTA(C1,E1,G1)=3

در صورت نیاز می توانید به راحتی مراجع سلولی اضافی را به فرمول ها اضافه کنید. چنین رویکردی فقط در سه حالت True را برمی‌گرداند: اگر همه سلول‌های محدوده B1:G1 چیزی در خود داشته باشند، اگر همه سلول‌های فرد (B1، D1، F1) چیزی در خود داشته باشند، و اگر همه سلول‌های زوج (C1، E1، G1) چیزی در آنها وجود دارد. اگر فقط برخی از سلول‌های محدوده دارای مقادیر باشند، مقدار True را بر نمی‌گرداند. به عنوان مثال، مقادیری در سلول های B1، C1 و E1 وجود دارد، سپس True برنمی گردد و هیچ یک از معیارهای قالب بندی برآورده نمی شود.

در حالی که همه اینها با محدودیت ذکر شده به خوبی کار می کنند، دقیقاً همان چیزی نیستند که زار به دنبال آن است - او فرمولی می خواهد که تشخیص دهد چند ستون در هفته به هفته استفاده می شود، همانطور که او به اضافه کردن داده ها به ستون ها ادامه می دهد و فرمول را مطابق با آن تنظیم می کند. بدون نیاز به ویرایش دستی فرمول برای در نظر گرفتن داده های اضافه شده. به عبارت دیگر، اگر او داده‌ها را به ستون H اضافه کند، می‌خواهد فرمول‌ها به طور خودکار تنظیم شوند تا ستون اضافه شده را در نظر بگیرد:

=COUNTA(B1:H1)=7)
=COUNTA(B1,D1,F1,H1)=4
=COUNTA(C1,E1,G1)=3

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

Function CellChk(crng As Range) As String
    Dim iNumOdds As Integer
    Dim iNumEvens As Integer
    Dim iOdds As Integer
    Dim iEvens As Integer
    Dim iTots As Integer
    Dim iTotCells As Integer
    Dim rWork As Range
    Dim rCell As Range
    Dim iLastCol As Integer
    Dim sTemp As String

    iOdds = 0
    iEvens = 0
    iTots = 0

     Figure out the real last column in the worksheet and set range
    iLastCol = ActiveSheet.Cells.Find(What:="*", _
        SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, _
        LookIn:=xlFormulas).Column
    Set rWork = Range(Cells(crng.Row, 2), Cells(crng.Row, iLastCol))

    iTotCells = rWork.Count
    iNumOdds = (iTotCells + 1)  2     Number of odd columns
    iNumEvens = iTotCells - iNumOdds   Number of even columns

    For Each rCell In rWork
        If rCell <> "" Then
            If ((rCell.Column - 1) Mod 2) = 1 Then
                iOdds = iOdds + 1
            Else
                iEvens = iEvens + 1
            End If
            iTots = iTots + 1
        End If
    Next rCell

    sTemp = ""
    If iTots = iTotCells Then
        sTemp = "t"
    ElseIf iOdds = iNumOdds Then
        sTemp = "o"
    ElseIf iEvens = iNumEvens Then
        sTemp = "e"
    End If
    CellChk = sTemp
End Function

ماکرو را با ارسال آدرسی در ردیفی که می‌خواهید بررسی کنید، استفاده می‌کنید. بنابراین، برای مثال، اگر قانون قالب‌بندی شرطی را در سلول A3 اعمال می‌کردید، یک آدرس B3 یا C3 را به ماکرو منتقل می‌کردید - هر چیزی به جز A3، زیرا باعث ایجاد یک مرجع دایره‌ای می‌شود. ماکرو آخرین سلول مورد استفاده در آن ردیف را جستجو می کند و سپس تعیین می کند که چند سلول زوج و فرد چیزی در آنها وجود دارد. ماکرو هر یک از چهار مقدار را برمی گرداند. اگر معیار اول برآورده شود (همه سلول های ردیفی که با ستون B شروع می شوند چیزی در آنها وجود دارد) یک "t" برگردانده می شود. اگر همه ستون‌های فرد (که B اولین ستون فرد است) چیزی در آنها وجود داشته باشد، «o» برگردانده می‌شود. اگر تمام ستون های زوج (با C اولین ستون زوج است) چیزی در آنها وجود داشته باشد، "e" برگردانده می شود. اگر هیچ یک از این سه معیار برآورده نشد،

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

=CellChk(B1)="t")
=CellChk(B1)="o")
=CellChk(B1)="e")

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