При разработке рабочего листа для отслеживания деловой информации у вас может возникнуть необходимость определить все пятницы в диапазоне дат. Лучший способ сделать это зависит от данных на вашем листе и от того, как вы хотите отображать результаты.

Если у вас есть список дат в столбце, вы можете использовать несколько различных функций рабочего листа, чтобы определить, являются ли эти даты пятницей или нет.

Функция WEEKDAY возвращает число от 1 до 7, в зависимости от дня недели даты, используемой в качестве аргумента:

=WEEKDAY(A2)

Это использование возвращает число 6, если дата в A2 — пятница. Если эта формула скопирована рядом со столбцом дат, вы можете использовать функцию автофильтра Excel, чтобы отображать только те даты, для которых день недели равен 6 (пятница).

Вы также можете использовать функцию условного форматирования Excel, чтобы просто выделить все пятницы в списке дат. Выполните следующие действия:

  1. Выберите список дат.

  2. Убедитесь, что на ленте отображается вкладка «Главная».

  3. Щелкните инструмент «Условное форматирование» в группе «Стили». Excel отображает серию вариантов.

  4. Щелкните Новое правило. Excel отображает диалоговое окно «Новое правило форматирования».

(См. Рис. 1.)

  1. В области «Выбор типа правила» в верхней части диалогового окна выберите «Использовать формулу для определения ячеек для форматирования». (См. Рис. 2.)

  2. В области формул введите следующую формулу, заменив A2 адресом активной ячейки, выбранной на шаге 1: = WEEKDAY (A2) = 6. Щелкните Форматировать, чтобы открыть диалоговое окно Формат ячеек.

  3. Установите параметры форматирования, чтобы выделить пятницу по своему желанию.

  4. Нажмите кнопку ОК, чтобы закрыть диалоговое окно Формат ячеек.

  5. Щелкните ОК.

Если вы хотите определить серию пятниц на основе даты начала и окончания, вы можете настроить серию формул, чтобы вычислить их.

Предполагая, что дата начала находится в A2, а дата окончания — в A3, вы можете использовать следующую формулу для определения даты первой пятницы:

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

Если поместить эту формулу в ячейку 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 <= iMaxDays Then         PullFridays3 = dFirstday + (iIndex - 1) * 7     End If End Function

Вы используете эту функцию в ячейке вашего рабочего листа следующим образом:

=PULLFRIDAYS3(A2,A3,1)

Первый аргумент функции — это дата начала, а второй — дата окончания. Третий аргумент указывает, какую пятницу вы хотите вернуть из указанного диапазона. Если вы используете 1, вы получите первую пятницу, 2 — вторую пятницу и т. Д. Если вы используете 0 в качестве третьего аргумента, функция вернет количество пятниц в указанном диапазоне. Если указанная дата начала больше даты окончания, функция возвращает ошибку # ЧИСЛО.

_Примечание: _

Если вы хотите узнать, как использовать макросы, описанные на этой странице (или на любой другой странице на сайтах ExcelTips), я подготовил специальную страницу, содержащую полезную информацию.

link: / excelribbon-ExcelTipsMacros [Щелкните здесь, чтобы открыть эту специальную страницу в новой вкладке браузера].

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

Этот совет (8147) применим к Microsoft Excel 2007, 2010, 2013, 2016, 2019 и Excel в Office 365. Вы можете найти версию этого совета для старого интерфейса меню Excel здесь:

link: / excel-Pulling_All_Fridays [Получение всех пятниц].