برجسته سازی بیشتر از میانگین مدت زمان خشک در اکسل

Question

Heather یک کاربرگ دارد که در ستون A، یک سری تاریخ دارد. اینها تاریخ هایی را در 25 سال گذشته نشان می دهد که در آن ایستگاه هواشناسی حداقل 0.01 اینچ بارندگی را ثبت کرده است. بنابراین، مدت زمان بین دو تاریخ متوالی یک "مدت خشک" است. هدر می‌خواهد تاریخ‌هایی را در فهرست برجسته کند که مدت زمان خشکی بیشتر از میانگین مدت خشکی برای همه تاریخ‌های فهرست باشد.

ساده ترین راه برای انجام این کار این است که یک ستون دیگر به داده های خود اضافه کنید. این یکی برای ذخیره مدت زمان خشک استفاده می شود. با فرض اینکه تاریخ های شما مرتب شده و در ستون A (با سلول A2 شروع می شود)، فرمول زیر را در سلول B3 قرار دهید:

=A3-A2

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

1. تمام تاریخ های خود را انتخاب کنید، که با سلول A3 شروع می شود. (فرض می کنم که A3:A4750 را انتخاب می کنید.)
2. مطمئن شوید که تب Home نوار نمایش داده می شود.
3. روی ابزار Conditional Formatting در گروه Styles کلیک کنید. اکسل پالتی از گزینه های مربوط به قالب بندی شرطی را نمایش می دهد.
4. روی New Rule کلیک کنید. اکسل کادر محاوره ای New Formatting Rule را نمایش می دهد.
5. در ناحیه Select a Rule Type در بالای کادر محاوره ای، Use a Formula to Determin Your Cells Format را انتخاب کنید.
6. در کادر Format Values ​​Where This Formula Is True، موارد زیر را وارد کنید: =B3>AVERAGE(B3:B4750) . اگر سلول کنار تاریخ حاوی مقداری بزرگتر از میانگین مدت زمان خشک شدن باشد، این فرمول True را برمی گرداند.
7. روی Format کلیک کنید تا کادر محاوره ای Format Cells نمایش داده شود.
8. هر گونه تغییری در فونت و/یا سلول ها ایجاد کنید تا مقادیر True را به دلخواه برجسته کنید.
9. برای بستن کادر محاوره ای Format Cells روی OK کلیک کنید.
10. روی OK کلیک کنید تا کادر محاوره ای New Formatting Rule بسته شود. اکسل قالب شرطی را برای سلول های انتخاب شده در ستون A اعمال می کند.

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

Sub DryPeriod()
    Dim RowNum As Long
    Dim MaxRow As Long
    Dim AvDuration As Long

    Application.ScreenUpdating = False
    MaxRow = ActiveSheet.UsedRange.Rows.Count
    With Range("A2:A" & MaxRow).Font
        .ColorIndex = xlAutomatic
        .Bold = False
    End With

    Range("B1").EntireColumn.Insert
    For RowNum = 3 To MaxRow
        If Cells(RowNum, 1) > "" Then
            Cells(RowNum, 2) = Cells(RowNum, 1) - Cells(RowNum - 1, 1)
        End If
    Next RowNum

    AvDuration = Application.WorksheetFunction.Average(Range("B3:B" & MaxRow))
    For RowNum = 3 To MaxRow
        If Cells(RowNum, 2) > AvDuration Then
            Cells(RowNum, 1).Font.Color = vbRed
            Cells(RowNum, 1).Font.Bold = True
        End If
    Next RowNum

    Range("B1").EntireColumn.Delete
    Application.ScreenUpdating = True
End Sub

شما می توانید ماکرو را هر چند بار که بخواهید اجرا کنید، به همین دلیل است که ابتدا قالب بندی در ستون A را به صورت پیش فرض در هر بار اجرا تنظیم می کند.