سالهایی که در آن تاریخ در یک روز خاص رخ داده است

Question

هارولد در تلاش است تا فرمولی ابداع کند که به او بگوید پنج سال گذشته که در آن تاریخ خاصی (مثلاً 10 می) در یک روز خاص از هفته (مثلاً پنجشنبه) رخ داده است.

راه های مختلفی وجود دارد که می توانید برای تعیین این اطلاعات استفاده کنید. یک راه ساده این است که در یک کاربرگ جدید، تاریخ مورد نظر برای آزمایش (مانند 5/10/18) را در سلول A1 وارد کنید. درست در زیر آن، در سلول A2، تاریخ یک سال قبل را وارد کنید: 5/10/17. این دو سلول را انتخاب کنید و دسته پر را برای تعداد سلول های دلخواه به سمت پایین بکشید و در نهایت ستونی حاوی تاریخ 10 می در سال های نزولی خواهید داشت. سپس در سلول B1 فرمول زیر را وارد کنید:

=WEEKDAY(A1)

این فرمول را برای سطرهایی که در ستون A تاریخ دارید کپی کنید. این یک مقدار از 1 تا 7 را برمی گرداند که نشان دهنده روز هفته برای تاریخ های ستون A است. از 1 = یکشنبه، 2 = دوشنبه و غیره، این بدان معنی است که هر مقدار 5 نشان دهنده یک پنجشنبه است. بنابراین، می توانید این فرمول را در سلول C1 قرار داده و آن را کپی کنید:

=IF(B1=5,YEAR(A1),"")

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

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

=LARGE((WEEKDAY(DATE(ROW(1918:2018),5,10))=5)*ROW(1918:2018),ROW(1:5))

با فشار دادن Ctrl+Shift+Enter فرمول را وارد کنید . نتیجه این است که این پنج سلول شامل پنج سال گذشته خواهد بود که در آن 10 می روز پنجشنبه بود. می توانید با جایگزین کردن عناصر کلیدی با محدوده های نام گذاری شده، فرمول را متنوع تر کنید، به این ترتیب:

=LARGE((WEEKDAY(DATE(ROW(1918:2018),MO,DA))=DW)*ROW(1918:2018),ROW(1:5))

اکنون تنها کاری که باید انجام دهید این است که محدوده های نامگذاری شده MO (ماه)، DA (روز) و DW (روز هفته) را ایجاد کنید. با تغییر مقادیر در این محدوده های نامگذاری شده، فرمول آرایه پنج سلول را به روز می کند تا سال های مورد نظر را منعکس کند.

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

Sub CalcDates()
    Dim sTemp As String
    Dim dBegin As Date
    Dim dWork As Date
    Dim J As Integer
    Dim iDoW As Integer
    Dim iYears(5) As Integer

    sTemp = InputBox("Beginning Date?")
    dBegin = CDate(sTemp)
    If dBegin > 0 Then
        sTemp = InputBox("Day of Week?")
        sTemp = LCase(Trim(sTemp))
        iDoW = 0
        For J = 1 To 7
            If sTemp = LCase(WeekdayName(J)) Then iDoW = J
        Next J
        If iDoW > 0 Then
            dWork = dBegin
            J = 0
            While J < 5
                If Weekday(dWork) = iDoW Then
                    J = J + 1
                    iYears(J) = Year(dWork)
                End If
                dWork = DateAdd("yyyy", -1, dWork)
            Wend

            sTemp = "These are the previous five years in which "
            sTemp = sTemp & MonthName(Month(dBegin)) & " " & Day(dBegin)
            sTemp = sTemp & " falls on a " & WeekdayName(iDoW) & ":"
            sTemp = sTemp & vbCrLf & vbCrLf
            For J = 5 To 1 Step -1
                sTemp = sTemp & iYears(J) & vbCrLf
            Next J
            MsgBox sTemp
        End If
    End If
End Sub

هنگامی که ماکرو پنج سال را پیدا می کند، آنها را در یک جعبه پیام نمایش می دهد.