해롤드는 지난 5 년 동안 특정 날짜 (예 : 5 월 10 일)가 특정 요일 (예 : 목요일)에 발생한 지난 5 년을 알려주는 공식을 고안하려고합니다.

이 정보를 결정하는 방법에는 여러 가지가 있습니다. 한 가지 간단한 방법은 새 워크 시트에서 테스트 할 날짜 (예 : 5/10/18)를 A1 셀에 입력하는 것입니다. 그 바로 아래에있는 A2 셀에 1 년 전의 날짜를 입력합니다 : 5/10/17. 이 두 셀을 선택하고 원하는 셀 수에 대해 채우기 핸들을 아래쪽으로 드래그하면 내림차순으로 5 월 10 일 날짜가 포함 된 열이 생성됩니다.

그런 다음 B1 셀에 다음 수식을 입력합니다.

=WEEKDAY(A1)

A 열에 날짜가있는만큼 많은 행에 대해이 수식을 복사합니다.

그러면 A 열의 날짜에 대한 요일을 나타내는 1부터 7까지의 값이 반환됩니다. 1 = 일요일, 2 = 월요일 등이므로 5의 값은 목요일을 나타냅니다. 따라서이 수식을 셀 C1에 넣고 아래로 복사 할 수 있습니다.

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

이것은 A 열의 날짜가 목요일 인 연도를 “부른다”.

원하는 경우 C 열을 추가하는 대신 필터링을 사용하여 B 열의 값이 5 (목요일) 인 날짜 만 표시 할 수 있습니다.

배열 수식을 사용하여 원하는 정보를 찾을 수도 있습니다.

먼저 원하는 열에서 5 개의 셀을 선택합니다. 그런 다음 수식 입력 줄에 다음을 입력합니다.

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

Ctrl + Shift + Enter를 눌러 수식을 입력합니다. 결과적으로 5 개의 셀에는 5 월 10 일이 목요일이었던 지난 5 년이 포함됩니다. 다음과 같은 방식으로 키 요소를 명명 된 범위로 대체하여 수식을보다 다양하게 만들 수 있습니다.

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

이제 MO (월), DA (일) 및 DW (요일) 범위의 명명 된 범위를 생성하기 만하면됩니다. 이러한 명명 된 범위의 값을 변경하면 배열 수식이 원하는 연도를 반영하도록 5 개의 셀을 업데이트합니다.

정보를 찾는 세 번째 방법은 시작 날짜와 요일을 묻는 매크로를 만드는 것입니다. 그런 다음 매크로는 기준을 충족하는 5 년을 확보 할 때까지 한 번에 1 년씩 뒤로 이동할 수 있습니다.

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

매크로가 5 년을 찾으면 메시지 상자에 표시합니다.

_ExcelTips_는 비용 효율적인 Microsoft Excel 교육을위한 소스입니다.

이 팁 (13529)은 Office 365의 Microsoft Excel 2007, 2010, 2013, 2016, 2019 및 Excel에 적용됩니다.