کشیدن همه جمعه ها در اکسل

Question

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

اگر فهرستی از تاریخ ها در یک ستون دارید، می توانید از چندین تابع مختلف کاربرگ برای تعیین اینکه آیا آن تاریخ ها جمعه هستند یا خیر استفاده کنید. تابع WEEKDAY بسته به روز هفته از تاریخ استفاده شده به عنوان آرگومان، عددی از 1 تا 7 را برمی گرداند:

=WEEKDAY(A2)

اگر تاریخ در A2 جمعه باشد، این کاربرد عدد 6 را برمی‌گرداند. اگر این فرمول در کنار ستونی از تاریخ‌ها کپی شده است، می‌توانید از ویژگی AutoFilter Excel استفاده کنید تا فقط تاریخ‌هایی را نشان دهید که در آن روز هفته 6 (جمعه) است.

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

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

شکل 2. از یک فرمول برای تعیین اینکه کدام سلول ها را قالب بندی کنید استفاده کنید.

1. لیست تاریخ ها را انتخاب کنید.
2. مطمئن شوید که تب Home نوار نمایش داده می شود.
3. روی ابزار Conditional Formatting در گروه Styles کلیک کنید. اکسل مجموعه ای از انتخاب ها را نمایش می دهد.
4. روی New Rule کلیک کنید. اکسل کادر محاوره ای New Formatting Rule را نمایش می دهد. (شکل 1 را ببینید.)
5. در ناحیه Select a Rule Type در بالای کادر محاوره ای، Use a Formula to Determin Your Cells Format را انتخاب کنید. (شکل 2 را ببینید.)
6. در ناحیه فرمول فرمول زیر را وارد کنید و آدرس سلول فعال انتخاب شده در مرحله 1 را جایگزین A2 کنید: =WEEKDAY(A2)=6
7. روی Format کلیک کنید تا کادر محاوره ای Format Cells نمایش داده شود.
8. گزینه های قالب بندی را تنظیم کنید تا جمعه ها را به صورت دلخواه برجسته کنید.
9. برای حذف کادر محاوره ای Format Cells روی OK کلیک کنید.
10. روی OK کلیک کنید.

اگر می خواهید مجموعه ای از جمعه ها را بر اساس تاریخ شروع و پایان تعیین کنید، می توانید یک سری فرمول برای تعیین آنها تنظیم کنید. با فرض اینکه تاریخ شروع در A2 و تاریخ پایان در A3 باشد، می توانید از فرمول زیر برای تعیین تاریخ اولین جمعه استفاده کنید:

=IF(A2+IF(WEEKDAY(A2)<=6,6-WEEKDAY(A2),6)>A3,
"",A2+IF(WEEKDAY(A2)

اگر این فرمول را در سلول C2 قرار دهید و سپس آن را به عنوان تاریخ قالب بندی کنید، می توانید از فرمول زیر برای تعیین جمعه آینده در محدوده استفاده کنید:

=IF(C2="","",IF(C2+7>$A$3,"",C2+7))

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

اگر واقعاً می‌خواهید جمعه‌ها را در یک محدوده تاریخی خاص "کشش" کنید، باید از یک ماکرو استفاده کنید. راه های مختلفی وجود دارد که می توانید در این مورد استفاده کنید. این ماکرو ساده تمام تاریخ‌ها را در محدوده A2:A24 بررسی می‌کند. اگر جمعه ها هستند، تاریخ در ستون C کپی می شود که از C2 شروع می شود. البته نتیجه این است که فهرستی که از C2 شروع می‌شود، فقط شامل تاریخ‌هایی است که جمعه‌ها هستند.

Sub PullFridays1()
    Dim dat As Range
    Dim c As Range
    Dim rw As Integer

    Set dat = ActiveSheet.Range("A2:A24")
    rw = 2
    For Each c In dat
        If Weekday(c) = vbFriday Then
            Cells(rw, 3).Value = Format(c)
            rw = rw + 1
        End If
    Next
End Sub

در صورت تمایل، می توانید محدوده بررسی شده توسط ماکرو را به سادگی با تغییر مرجع A2:A24 تغییر دهید، و می توانید محل نوشته شدن تاریخ ها را با تغییر مقدار rw (ردیف) و مقدار 3 (ستون) در قسمت تغییر دهید. عملکرد سلول ها

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

Sub PullFridays2()
    Dim dStart As Date
    Dim dEnd As Date
    Dim rw As Integer

    dStart = Range("A2").Value
    dEnd = Range("A3").Value

    rw = 2
    While dStart < dEnd
        If Weekday(dStart) = vbFriday Then
            Cells(rw, 3).Value = dStart
            Cells(rw, 3).NumberFormat = "m/d/yyyy"
            rw = rw + 1
        End If
        dStart = dStart + 1
    Wend
End Sub

ماکرو هنوز جمعه ها را از محدوده خارج می کند و آنها را در لیستی قرار می دهد که از C2 شروع می شود.

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

Function PullFridays3(dStartDate As Date, _
                      dEndDate As Date, _
                      iIndex As Integer)
    Dim iMaxDays As Integer
    Dim dFirstday As Date

    Application.Volatile
    If dStartDate > dEndDate Then
        PullFridays3 = CVErr(xlErrNum)
        Exit Function
    End If

    dFirstday = vbFriday - Weekday(dStartDate) + dStartDate
    If dFirstday < dStartDate Then dFirstday = dFirstday + 7
    iMaxDays = Int((dEndDate - dFirstday) / 7) + 1

    PullFridays3 = ""
    If iIndex = 0 Then
        PullFridays3 = iMaxDays
    ElseIf iIndex 

You use this function in a cell in your worksheet in the following manner:

=PULLFRIDAYS3(A2,A3,1)

The first argument for the function is the starting date and the second is the ending date. The third argument indicates which Friday you want returned from within the specified range. If you use 1, you get the first Friday, 2 returns the second Friday, etc. If you use a 0 for the third argument, then the function returns the number of Fridays in the specified range. If the specified beginning date is greater than the ending date, then the function returns a #NUM error.