excel - 使用 NetworkDays.Intl 作为 Excel VBA 中的复杂公式排除非工作时间？

Question

我最近写了一些 VBA 代码，但尽管有一些编码背景，但我认为我在其中的经验仍然是新的/新鲜的。在问我自己的问题之前，我已经广泛搜索以查看类似的主题并在那里实现解决方案，但是经过 2 天的搜索/工作后，要么我不擅长搜索，要么就是找不到与我自己的问题类似的解决方案来实现.

我使用的是 Excel 2019。

我有一种RAW DATA，我WEEKLY/MONTHLY 得到，这个RAW DATA 包含从几千到几万的任何地方行，我的 VBA 代码通过仅获取需要的内容来对这些 RAW DATA 进行排序。现在我还想自动化的是从 2 个日期中排除非工作时间。在追求这一点时，我想到了一个复杂的公式，当应用于带有变量的单元格时，它本身就可以工作，但我也想将它包含在我的 VBA 代码中。

我尝试了宏记录器(就像我对很多事情所做的那样，以获得关于如何实现这些东西的提示)，但我有点卡在这个上面，因此需要你的专业知识和知识

有问题的公式是:

=(NETWORKDAYS.INTL([@[DC_CREATION_DATE]],[@[ACTUAL_END_DATE]],""0000000"")-1)*(upper-lower)+IF(NETWORKDAYS.INTL([@[ACTUAL_END_DATE]],[@[ACTUAL_END_DATE]],""0000000""),MEDIAN(MOD([@[ACTUAL_END_DATE]],1),upper,lower),upper)-MEDIAN(NETWORKDAYS.INTL([@[DC_CREATION_DATE]],[@[DC_CREATION_DATE]],""0000000"")*MOD([@[DC_CREATION_DATE]],1),upper,lower)"

我的目标是完全没有周末(因此使用 NetworkDays.Intl 使用“0000000”自定义设置所有工作日)，并且只设置工作时间(从 0800 到 2300)(8:00AM 到 11 :00PM)，并且晚上 11:01 到早上 7:59 之间的任何时间都将从总数中排除。

这是我实现上述公式的方法的 VBA 代码:

    Sub RAWDATA_SORT()
    
    Dim Main As Worksheet, Processed As Worksheet
    Dim LastRow As Long, col As Long, k As Integer
    Dim colName As String, maincolName As String
    Dim i As Range
    Dim Headers As Range, SearchHeaders As Range
    Dim upper As Date, lower As Date, StartDate As Date, EndDate As Date
    
    On Error Resume Next
    Set Main = ActiveSheet
    Main.Name = "RAW DATA"
    Sheets.Add(After:=Sheets("RAW DATA")).Name = "Processed Data"
    Set Processed = Sheets("Processed Data")
    Main.Activate
    Main.ShowAllData
    Set Headers = Main.Range("1:1")
    LastRow = 0
    lower = Format(TimeValue("08:00 AM"), "hh:mm AMPM")
    upper = Format(TimeValue("11:00 PM"), "hh:mm AMPM")
    Debug.Print (lower)
    Debug.Print (upper)
    
    ' More Code Here
    
    With Processed
    Processed.Activate
    Processed.AutoFilterMode = False
    Processed.ShowAllData
    
    ' More Code Here

    LastRow = Main.AutoFilter.Range.Columns(1).SpecialCells(xlCellTypeVisible).Cells.Count
    k = 2
    For Each i In Range("N2:N" & LastRow)
        StartDate = Range("N" & k).Value
        EndDate = Range("R" & k).Value
        Debug.Print (StartDate)
        Debug.Print (EndDate)
        Range("U" & k).Value = DateDiff("s", Range("N" & k).Value, Range("R" & k).Value)
        Range("V" & k).Value = "=(NETWORKDAYS.INTL([" & StartDate & "],[" & EndDate & "],""0000000"")-1)*([" & upper & "]- [" & lower & "])" _
                                    & "+IF(NETWORKDAYS.INTL([" & EndDate & "],[" & EndDate & "],""0000000""),MEDIAN(MOD([" & EndDate & "],1),[" & upper & "],[" & lower & "]),[" & upper & "])" _
                                    & "-MEDIAN(NETWORKDAYS.INTL([" & StartDate & "],[" & StartDate & "],""0000000"")*MOD([" & StartDate & "],1),[" & upper & "],[" & lower & "])"
        k = k + 1
    Next i
    Range("U:U").NumberFormat = "General"
End With

    ' Proceeding to End

这是宏记录器给出的:

ActiveCell.FormulaR1C1 = _
    "=(NETWORKDAYS.INTL([@[DC_CREATION_DATE]],[@[ACTUAL_END_DATE]],""0000000"")-1)*(upper-lower)" & Chr(10) & "+IF(NETWORKDAYS.INTL([@[ACTUAL_END_DATE]],[@[ACTUAL_END_DATE]],""0000000""),MEDIAN(MOD([@[ACTUAL_END_DATE]],1),upper,lower),upper)" & Chr(10) & "-MEDIAN(NETWORKDAYS.INTL([@[DC_CREATION_DATE]],[@[DC_CREATION_DATE]],""0000000"")*MOD([@[DC_CREATION_DATE]],1),upper,lower)"

我尝试过的:

• 将 Range("V"& k).Value 替换为:Formula、FormulaR1C1、Formula2、Formula2R1C1
• 用单元格替换范围
• 尝试使用 Application.WorksheetFunction.NetworkDays_Intl，但我的经验不足以将整个公式正确转换为代码。

结果是……什么都没有，当代码运行时，它没有给出任何错误，但是“V”列完全是空的，没有任何值/结果。

我确信我遗漏了一些东西，例如使用带有变量的公式或将公式本身设置为单元格/范围的正确语法，但我已经绞尽脑汁寻求帮助并学习过程。

或者，如果有人有更好的解决方案来排除工作时间而不使用 NetworkDays.Intl(因为没有周末)，我也会很感激。

如果这样的问题已经得到解答，我深表歉意，非常感谢您完整阅读我的帖子。

编辑:按照 Tim Williams 的建议注释掉“On Error Resume Next”后，我遇到了运行时错误:1004，应用程序定义或对象定义的错误，在我所在的行放置公式。

Answer 1

由于发布的公式准确地返回了 DC_CREATION_DATE 之间的工作时间和 ACTUAL_END_DATE ，问题似乎是关于如何使用 VBA 输入 Excel 公式。

Op 的公式:

= ( NETWORKDAYS.INTL( [@[DC_CREATION_DATE]], [@[ACTUAL_END_DATE]], "0000000" ) -1 ) * ( Upper - Lower )
 + IF( NETWORKDAYS.INTL( [@[ACTUAL_END_DATE]], [@[ACTUAL_END_DATE]], "0000000" ),
 MEDIAN( MOD( [@[ACTUAL_END_DATE]], 1 ), Upper, Lower ), Upper )
 - MEDIAN( NETWORKDAYS.INTL( [@[DC_CREATION_DATE]], [@[DC_CREATION_DATE]], "0000000" )
 * MOD( [@[DC_CREATION_DATE]], 1 ), Upper, Lower )

上面的公式似乎是从 Excel 表(即 ListObject)中获得的，如以下参数所示:[@[DC_CREATION_DATE]]和 [@[ACTUAL_END_DATE]] , 而 Upper和 Lower似乎对应于Defined Names

使用标准单元格作为参数的相同公式如下所示:

= ( NETWORKDAYS.INTL( B7, C7, "0000000" ) -1 ) * ( Upper - Lower )
 + IF( NETWORKDAYS.INTL( C7, C7, "0000000" ),
 MEDIAN( MOD( C7, 1 ), Upper, Lower ), Upper )
 - MEDIAN( NETWORKDAYS.INTL( B7, B7, "0000000" )
 * MOD( B7, 1 ), Upper, Lower )

注意参数:[@[DC_CREATION_DATE]]和 [@[ACTUAL_END_DATE]]替换为单元格 B7和 C7分别

这就是 Op 代码的问题:

• 它不会替换整个参数

  • 仅替换 @[DC_CREATION_DATE]而不是 [@[DC_CREATION_DATE]]
  • 仅替换 @[ACTUAL_END_DATE]而不是 [@[ACTUAL_END_DATE]]

• 此外，它还用 [ 包裹了 Upper 和 Lower和 ]

用 VBA 处理 Excel 公式:

我建议添加对 DC_CREATION_DATE 的验证和 ACTUAL_END_DATE在公式的开头如下:

= IF( [@[ACTUAL_END_DATE]] < [@[DC_CREATION_DATE]], 0,
 ( NETWORKDAYS.INTL( [@[DC_CREATION_DATE]], [@[ACTUAL_END_DATE]], "0000000" ) -1 ) * ( Upper - Lower )
 + IF( NETWORKDAYS.INTL( [@[ACTUAL_END_DATE]], [@[ACTUAL_END_DATE]], "0000000" ),
 MEDIAN( MOD( [@[ACTUAL_END_DATE]], 1 ), Upper, Lower ), Upper )
 - MEDIAN( NETWORKDAYS.INTL( [@[DC_CREATION_DATE]], [@[DC_CREATION_DATE]], "0000000" )
 * MOD( [@[DC_CREATION_DATE]], 1 ), Upper, Lower ) )

我建议使用以下方法使用 VBA 处理 excel 公式:

1. 将公式中的参数替换为将由 R1C1 替换的关键字运行程序时实际值的引用:

…

= IF( #END < #INI, 0," & vbLf & _
 ( NETWORKDAYS.INTL( #INI, #END, "0000000" ) -1 ) * ( #UPR - #LWR )" & vbLf & _
 + IF( NETWORKDAYS.INTL( #END, #END, "0000000" )," & vbLf & _
 MEDIAN( MOD( #END, 1 ), #UPR, #LWR ), #UPR )" & vbLf & _
 - MEDIAN( NETWORKDAYS.INTL( #INI, #INI, "0000000" )" & vbLf & _
 * MOD( #INI, 1 ), #UPR, #LWR ) )"

地点:
#INI = [@[DC_CREATION_DATE]]
#END = [@[ACTUAL_END_DATE]]
#LWR = Lower
#UPR = Upper

By using the R1C1 reference of the cells we can update the formulas for the entire range at once instead of looping over each cell.

1. 定义一个常量来保存公式模板:

…

Const kFmlHours As String = "= IF( #END < #INI, 0," & vbLf & _
    " ( NETWORKDAYS.INTL( #INI, #END, ""0000000"" ) -1 ) * ( #UPR - #LWR )" & vbLf & _
    " + IF( NETWORKDAYS.INTL( #END, #END, ""0000000"" )," & vbLf & _
    " MEDIAN( MOD( #END, 1 ), #UPR, #LWR ), #UPR )" & vbLf & _
    " - MEDIAN( NETWORKDAYS.INTL( #INI, #INI, ""0000000"" )" & vbLf & _
    " * MOD( #INI, 1 ), #UPR, #LWR ) )"

1. 根据需要为参数定义变量:

…

Dim sFmlHours As String
Dim TimeLwr As Double, TimeUpr As Double
Dim sDateIni As String, sDateEnd As String

1. 用相应的值或R1C1引用替换公式模板中的关键字:

…

        With .Range("V2")
            sDateIni = Range("N2").Address(0, 1, xlR1C1, False, .Cells)
            sDateEnd = Range("R2").Address(0, 1, xlR1C1, False, .Cells)
            sFmlHours = kFmlHours
            sFmlHours = Replace(sFmlHours, "#INI", sDateIni)
            sFmlHours = Replace(sFmlHours, "#END", sDateEnd)
            sFmlHours = Replace(sFmlHours, "#LWR", TimeLwr)
            sFmlHours = Replace(sFmlHours, "#UPR", TimeUpr)
        End With

1. 输入整个范围的公式，(您也可以用结果值替换公式):

…

        With .Range("V2:V" & lRow)
            .FormulaR1C1 = sFmlHours    'Enter formula
            .Value = .Value             'Replace Formula with Value
        End With

程序:

此程序仅包括工作时间的计算:

Sub Formula_Working_Hours()
   
Const kFmlHours As String = "= IF( #END < #INI, 0," & vbLf & _
    " ( NETWORKDAYS.INTL( #INI, #END, ""0000000"" ) -1 ) * ( #UPR - #LWR )" & vbLf & _
    " + IF( NETWORKDAYS.INTL( #END, #END, ""0000000"" )," & vbLf & _
    " MEDIAN( MOD( #END, 1 ), #UPR, #LWR ), #UPR )" & vbLf & _
    " - MEDIAN( NETWORKDAYS.INTL( #INI, #INI, ""0000000"" )" & vbLf & _
    " * MOD( #INI, 1 ), #UPR, #LWR ) )"

Dim wsMain As Worksheet, wsPrcs As Worksheet
Dim sFmlHours As String
Dim TimeLwr As Double, TimeUpr As Double
Dim sDateIni As String, sDateEnd As String
Dim lRow As Long
    
    Rem Set Lower & Upper Time
    TimeLwr = TimeSerial(8, 0, 0)
    TimeUpr = TimeSerial(23, 0, 0)
    
    With ThisWorkbook
        Set wsMain = .Sheets("RAW DATA")
        Set wsPrcs = .Sheets("Processed Data")
    End With
    
    lRow = wsMain.AutoFilter.Range.Columns(1).SpecialCells(xlCellTypeVisible).Cells.Count
    
    With wsPrcs
                
        .Activate
        If Not (.AutoFilter Is Nothing) Then .AutoFilter.Range.AutoFilter
            
        Rem Set Formula
        With .Range("V2")
            sDateIni = Range("N2").Address(0, 1, xlR1C1, False, .Cells)
            sDateEnd = Range("R2").Address(0, 1, xlR1C1, False, .Cells)
            sFmlHours = kFmlHours
            sFmlHours = Replace(sFmlHours, "#INI", sDateIni)
            sFmlHours = Replace(sFmlHours, "#END", sDateEnd)
            sFmlHours = Replace(sFmlHours, "#LWR", TimeLwr)
            sFmlHours = Replace(sFmlHours, "#UPR", TimeUpr)
        End With
        
        Rem Enter Formula
        With .Range("V2:V" & lRow)
            .FormulaR1C1 = sFmlHours    'Enter formula
            .Value = .Value             'Replace Formula with Value
        End With
        
    End With

End Sub