如果某个单元格等于 0,我就会尝试自动重置总数。
A1 9
B1 9
C1 0 <-- Reset total
D1 9
E1 9
F1 9
G1 9
H1 36 (Should = 36, resetting at any cell showing 0 and not show 54)
字段A1:G1
保存名册上的每日工作时长,而H1
包含该周的总工作时长。(但是,如果休息日为 0 小时,我想重置此总数)
是否有公式可以做到这一点?
答案1
答案2
使用 BVA / 宏
Public Function sumreset(dataRange As Range)
totalSum = 0
totalLength = -1
indicator = "none"
totalLengthRows = dataRange.Rows.Count
totalLengthCols = dataRange.Columns.Count
If (totalLengthRows = 1) And (totalLengthCols > 1) Then
totalLength = totalLengthCols
indicator = "cols"
ElseIf (totalLengthRows > 1) And (totalLengthCols = 1) Then
totalLength = totalLengthRows
indicator = "rows"
End If
For i = totalLength To 1 Step -1
Select Case indicator
Case "rows"
nextData = dataRange(i, 1)
Case "cols"
nextData = dataRange(1, i)
End Select
If nextData <> 0 Then
totalSum = totalSum + nextData
Else
i = 1
End If
Next i
sumreset = totalSum
End Function
使用 ALT+F11 打开 VBA / 宏,在本工作簿插入一个模块并将此代码粘贴到右侧。
在单元格 H1 中输入=sumreset($A$1:$G$1)
这些,就完成了。
答案3
这是一个简单的宏,可以完全满足您的要求。它适用于水平或垂直范围
Function SumAfterZero(rng As Range) As Double
Dim r As Range
Dim output As Double
For Each r In rng
If r = 0 Then
output = 0
Else
output = output + r
End If
Next r
SumAfterZero = output
End Function
与上述一样,只需输入=SumAfterZero(A1:G1)
单元格 H1 中即可。