如何从文本格式的单元格中获取小时分钟的总和？

Question

我有一列数据如下：

2 Hour, 23 Minute
1 Hour, 10 Minute

我需要以小时分钟格式计算此列的总和。可以是 3 小时 43 分钟。或者 3:43 也可以。

到目前为止，我已成功将文本转换为这种格式->2:23, 1:10

Answer 1

请执行下列操作：

怎么运行的：

• 在单元格 E22 和 E23 中输入 02:33 和 01:10。

• 在两个单元格上应用此格式：

  [h] "hours ," mm "minutes"

• 在单元格 E25 和 E26 中输入此公式：

  =E22+E23

• 应用的格式是 [h] "hours ," mm "minutes"。

• 单元格 E26 的格式为h:mm。

根据需要调整公式中的单元格引用。

Answer 2

数据在A1通过A5， 在B1进入：

=TIMEVALUE(SUBSTITUTE(SUBSTITUTE(A1," Hour, ",":")," Minute",":00"))

并向下复制。然后将适当的格式应用于列乙：

最后，在另一个单元格中输入：

=SUM(B:B)

Answer 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结果如下。

Answer 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（一天的分钟数）
• 将结果格式化为显示小时：分钟