将日期标识为星期四，并对过去 6 天求和

Question

我有一个包含多个操作代码及其小时的表，我需要在每个星期四对每个代码花费的时间求和。

尽管能够弄清楚 IF 公式可以完成我卡在范围总和上的工作，但我可以通过 VBA 让它工作，但我无法在公式上应用相同的解决方案：

WorksheetFunction.Sum(Range("E" & cCell.Row &"：E" & cCell.Row - 6))

Dim Counter As Integer
Dim cCell As Range
Dim intToday As Integer
Dim CountDate As Integer
Dim strWsName As String
strWsName = ActiveSheet.Name
Dim xWs As Worksheet
Set xWs = Worksheets(strWsName)
'Clause 101
For Counter = 4 To 34
Set cCell = xWs.Cells(Counter, 4)
If WorksheetFunction.WeekDay(cCell.Value) = 5 Then
If cCell.Row = 4 Then
xWs.Range("Q" & cCell.Row) = WorksheetFunction.Sum(Range("E" & cCell.Row & ":E" & cCell.Row))
Else
If cCell.Row >= 34 Then
xWs.Range("Q" & cCell.Row) = WorksheetFunction.Sum(Range("E" & cCell.Row & ":E" & cCell.Row))
Else
If cCell.Row - 6 <= 0 Then
xWs.Range("Q" & cCell.Row) = WorksheetFunction.Sum(Range("E" & cCell.Row & ":E4"))
Else
xWs.Range("Q" & cCell.Row) = WorksheetFunction.Sum(Range("E" & cCell.Row & ":E" & cCell.Row - 6))
End If
End If
End If
End If
Next Counter
End Sub

我想知道如何将该段代码转换为Excel上的公式。

Answer 1

在对公式和变量范围进行研究后，我设法设置了这个公式，它就像一个魅力：

=IF(工作日($D 9)=5;IF(行(E9)<=6;总和(偏移量(E9;;;-ROW()));总和(偏移量(E9;;;-7)));")

注意：我的区域设置在公式上使用";"而不是"，"，因此请记住，您必须将其更改为区域设置。

解释：

"=IF(WEEKDAY($D 9)=5"公式将评估日期是否为星期四，如果不是，它将在列上删除";

IF(ROW(E9)<=6 子句将用于识别电子表格顶部的限制，根据其值，它将对不同的范围求和：

• 如果日期的行小于 6，它将运行以下命令： 总和(偏移量(E9;;;-ROW()))。偏移量将与其中的行相同 日期是。
• 现在，如果该行大于 6，那么我将能够设置一个固定的 7 行的偏移量：总和(偏移量(E9;;;-7))

如果需要将计算更改为星期三，您所要做的就是更改工作日的"=IF(WEEKDAY($D 9)=5"数字上的数字"5"，以下是列表：

• 1至周日
• 2至周一
• 3 至 周二
• 4至周三
• 5至周四
• 6至周五
• 7 至周六

感谢在Excel Jet和Extend Office上找到的文章，我设法构建了这个解决方案！

希望更多人能使用这个解决方案！

Answer 2

解决这个问题的最好方法是创建一个UDF，我在ExcelForum上得到了WideBoyDixon的帮助：

Public Function SumWeek(sumRange As Range, dateRange As Range, endDate)
Application.Volatile
Dim prevSheetName As String
Dim prevSheet As Worksheet
SumWeek = Application.WorksheetFunction.SumIfs(sumRange, dateRange, "<=" & CStr(CLng(endDate)), dateRange, ">" & CStr(CLng(endDate - 7)))
prevSheetName = Mid("DecJanFebMarAprMayJunJulAugSepOctNov", Month(endDate) * 3 - 2, 3) & CStr(Year(endDate) - IIf(Month(endDate) = 1, 1, 0))
On Error Resume Next
Set prevSheet = Worksheets(prevSheetName)  
If Not (prevSheet Is Nothing) Then
SumWeek = SumWeek + Application.WorksheetFunction.SumIfs(prevSheet.Range(sumRange.Address), prevSheet.Range(dateRange.Address), "<=" & CStr(CLng(endDate)), prevSheet.Range(dateRange.Address), ">" & CStr(CLng(endDate - 7)))
End If
End Function