☰

Excel - 基于公式的斜线分隔时间字符串的单个持续时间和总持续时间计算

tag-icon • tag-icon microsoft-excel worksheet-function microsoft-excel-2007
Excel - 基于公式的斜线分隔时间字符串的单个持续时间和总持续时间计算

我正在尝试在 Excel 中计算（仅使用基于工作表公式的解决方案）两个单元格中以斜线“/”分隔的时间文本中的单个时间持续时间和总时间持续时间（第一个单元格的每个值都是开始时间，第二个单元格中的对应值是结束时间）（仍然仅使用 Excel 2007）

输入单元格（B1 包含起始时间数据，而 B2 包含相应的终止时间数据）

Cell-B1:  "00:20 / 00:58 / 09:25 / 12:05 / 17:26 / 21:58"
Cell-B2:  "00:45 / 07:57 / 11:41 / 15:57 / 21:27 / 23:48"

预期输出（B6 显示单个时间持续时间，单元格 B7 显示所有持续时间的总和）

Cell-B6:  "00:25 / 06:59 / 02:16 / 03:52 / 04:01 / 01:50"
Cell-B7:  19:23:00

数据组织和预期输出结果如下面的快照图所示。

快照图像：查看我的输入数据单元和预期输出数据单元

我理解，VBA 可以轻松完成此操作，而且我确实具有基本的 VBA 知识，但是是否有可能基于单个公式（可能是也可能不是数组公式）的解决方案来显示单个时间持续时间和所有计算的时间持续时间的总和？我只是想避免使用 VBA 宏，也不想手动处理文本到列（使用分隔符“/”），然后进行单个时间持续时间计算，最后以连接形式显示所有持续时间，并显示所有单个时间持续时间的总计。

为了得出结果，只能使用一些基本的工作表函数，例如 MID、FIND、SUM、SEARCH、TRIM、SUBSTITUTE、INDEX、ROW 等。

答案1

适应你的公式求和差值

=TEXT(SUM(
   VALUE(TRIM(MID(SUBSTITUTE(B2,"/",REPT(" ",LEN(B2))),
      (ROW(INDIRECT("1:"&LEN(B2)-LEN(SUBSTITUTE(B2,"/",""))+1))-1)*
      LEN(B2)+1,LEN(B2))))-
   VALUE(TRIM(MID(SUBSTITUTE(B1,"/",REPT(" ",LEN(B1))),
      (ROW(INDIRECT("1:"&LEN(B1)-LEN(SUBSTITUTE(B1,"/",""))+1))-1)*
      LEN(B1)+1,LEN(B1))))), "hh:mm:ss")

答案2

公式

抱歉，看到了排除任何使这变得容易的功能的要求;-) 也许将它留给使用当前版本的 Excel 并偶然发现它的人。

=LET(
   durations, MAP(TEXTSPLIT(B1," / "),TEXTSPLIT(B2," / "),
      LAMBDA(from,to,TIMEVALUE(to)-TIMEVALUE(from))),
   VSTACK(
      TEXTJOIN(" / ",FALSE,BYCOL(durations,LAMBDA(dur,TEXT(dur,"hh:mm")))),
      TEXT(SUM(durations), "hh:mm")))
  1. 这让函数用于命名/存储中间计算以供日后使用
  2. 文本分割 “从”和“到”串上" \ "和地图值拉姆达功能from和to变量
  3. LAMBDA 的公式用途时间值转换映射的字符串，并将每个映射对的差异作为名为的单行数组中的一列返回durations
  4. 堆栈用于返回 2 行（1 列）数组：
    • 第 1 行：  文本连接连接durations'值由" / "首次应用后文本格式"hh:mm"通过另一个拉姆达功能
    • 第 2 行：  文本格式"hh:mm"应用于和的durations'值。

相关内容