Гарольд пытается разработать формулу, которая скажет ему последние пять лет, когда определенная дата (скажем, 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

Когда макрос находит пять лет, он отображает их в окне сообщения.

ExcelTips — ваш источник экономичного обучения Microsoft Excel.

Этот совет (13529) применим к Microsoft Excel 2007, 2010, 2013, 2016, 2019 и Excel в Office 365.