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

debugcn 发表于 Dev

尼古翁

这个问题与Excel有关。如果我有一个类型为text的单元格，我将在其中插入多个日期，每个日期都带有小时，其格式如下：

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

当然，所有内容都将视为文本。但是，如果我想把所有的时间加起来，是否有办法做到？

罗恩·罗森菲尔德

如果您拥有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结果是只有一个空格
用标记替换空格，并在开头和结尾添加标记，以创建格式正确的 XML
返回紧随包含的节点之后的节点内容的数组taken。
在美国，我必须用点替换逗号以正确解释数值。您可能需要，也可能不需要在您的语言环境中执行此操作。
应用双精度一元将数字的文本表示形式转换为实数
使用DOLLARDE和DOLLARFR函数可以将1.23表示形式的数字更改为1 hr 23 min可以加的小数，然后再转换为小数表示形式h.mm ，也可以将其更改为“实际” excel时间并进行格式化，但是您尚未弄清要求
SUM 结果数组。