☰

在 Excel 的文本类型单元格中求和

tag-icon • tag-icon microsoft-excel worksheet-function spreadsheet
在 Excel 的文本类型单元格中求和

这个问题涉及 Excel。如果我有一个文本类型的单元格，我将在其中插入更多日期，每个日期带有小时数，格式如下：

06/05/2019 Hours taken: 1,23

06/09/2019 Hours taken: 0,12

06/10/2019 Hours taken: 0,45

06/11/2019 Hours taken: 1,59

当然，所有内容都会被视为文本。但如果我想计算所有小时数的总和，有什么办法吗？

答案1

如果您有 Excel 2013+，并且具有该FILTERXML函数，则可以使用以下数组公式：

编辑：公式改变以解释代表的时间h,mm

=DOLLARFR(SUM(DOLLARDE(--SUBSTITUTE(FILTERXML("<t><s>"&SUBSTITUTE(TRIM(SUBSTITUTE(A1,CHAR(10)," "))," ","</s><s>") &"</s></t>","//s[contains(text(),'taken')]/following-sibling::s[1]"),",","."),60)),60)

由于这是一个数组公式，因此您需要按住ctrl+shift并点击 来“确认” enter。如果您正确执行此操作，Excel 将{...}在公式栏中看到括号。

如果小数分隔符是逗号，那么您应该使用：

=DOLLARFR(SUM(DOLLARDE(--FILTERXML("<t><s>"&SUBSTITUTE(TRIM(SUBSTITUTE(A1,CHAR(10)," "))," ","</s><s>") &"</s></t>","//s[contains(text(),'taken')]/following-sibling::s[1]"),60)),60)

算法

  • 将换行符替换为空格，TRIM结果只有一个空格
  • 用 token 替换空格，并在开头和结尾添加 token，以创建格式良好的XML
  • 返回包含 的节点后紧接着的节点的内容数组taken。
  • 在美国，我必须用点代替逗号才能正确解释数值。在您的语言环境中，您可能需要或不需要这样做。
  • 应用双重一元运算符将数字的文本表示形式转换为实数
  • 使用DOLLARDE和DOLLARFR函数可以将1.23表示的数字转换1 hr 23 min为可以相加的分数，然后再转换为表示的小数h.mm 它也可以更改为“真实”的 excel 时间并格式化，但你还没有明确你的要求
  • SUM结果数组。

在此处输入图片描述

答案2

假设美国地区和时间格式为唔，列中有数据A， 在B1进入：

=TIMEVALUE(SUBSTITUTE(RIGHT(A1,4),",",":") & ":00")

并将格式设置为时间格式。然后向下复制。在另一个单元格中输入：

=SUM(B:B)

在此处输入图片描述

相关内容