我有一列数据如下:
2 Hour, 23 Minute
1 Hour, 10 Minute
我需要以小时分钟格式计算此列的总和。可以是 3 小时 43 分钟。或者 3:43 也可以。
到目前为止,我已成功将文本转换为这种格式->2:23, 1:10
答案1
答案2
答案3
使用 VBA 函数(设置变量:a
带列名、j
带起始行、k
带结束行:
Function Calc()
Dim c As String
Dim j As Integer
Dim k As Integer
Dim n As Integer
Dim res As Integer
c = "A"
res = 0
j = 1
k = 8
Dim varCell As Variant
Dim testSplit,testWord
For i = j To k
varCell = Range(c & i).Value
testSplit = Split(varCell,",")
For Each o In testSplit
testWord = Split(o," ")
Select Case UBound(testWord)
Case "Second"
n = testWord(0)/60
res = res + n
Case "Minute"
n = testWord(0)
res = res + n
Case "Hour"
n = testWord(0)*60
res = res + n
End Select
Next
Next
res = res/60
End Function
res
结果如下。
答案4
如果您的格式如您所显示的那样:
<hours><space><text><minutes><space><text>`
如果您拥有 Windows Excel 2010+,则可以使用以下公式:
someCell: =(SUM(FILTERXML("<t><s>" & SUBSTITUTE(TRIM(A1:A6)," ","</s><s>") & "</s></t>","//s[1]"))*60+
SUM(FILTERXML("<t><s>" & SUBSTITUTE(TRIM(A1:A6)," ","</s><s>") & "</s></t>","//s[3]")))/1440
自定义格式 someCell 为[h]:mm
- 提取小时数并乘以 60
- 添加分钟
- 将上述总和除以 1440(一天的分钟数)
- 将结果格式化为显示小时:分钟