主页 / coding / 问题 / 79598783
Accepted
nightfire36
Asked: 2025-04-30 00:02:48 +0800 CST

有没有办法计算多个公式中特定数字的实例？

  • 772

我一直在会计部门帮忙，他们会给加班的人发放额外工时。如果你在某个班次连续工作了5天或7天，除了你实际工作的工时外，还会额外获得一些额外工时。所以，你可能会得到5*10的额外工时，再加上4小时，总共54小时，而不是50小时。

问题在于，所使用的电子表格会跟踪每个月的班次，并将天数加在一起。例如，7月份，一个人工作了5天，又工作了7天，所以他7月份的单元格会被标记为“=5+7”。我们需要知道该班次的天数，以及全年应奖励的奖励小时数。

我知道有更好的方法来计算这个问题，但我们还是用老旧的电子表格，因为会计部门就是这么想的。有没有办法统计单元格包含 7 或 5 的次数，然后把它们加到新的一列？

对于一名员工来说，给定的行可能看起来像这样：

七月 八月 九月
=5+7 5 = 7+7

因此，对于这名员工，我需要三列来输出总天数（31）、连续 5 天轮班的数量（2）和连续 7 天轮班的数量（3）。

他们目前是手动操作的，但对于100多名员工来说，手动操作太繁琐，而且容易出错。他们也明确表示希望在每个单元格中都这样计算，所以我没办法解决这个问题。我试过用单元格函数，但不知道它能否同时处理多个单元格。我想我可以做一个countif，然后用单元格函数来查看所有12个月的数据，但这似乎不太优雅。

excel

3 个回答

  1. Best Answer

    假设这些是真正的公式而不是看起来像公式的文本：

    =LET(
    y,A2:C2,
    ar,--TEXTSPLIT(TEXTJOIN("+",,IF(ISFORMULA(y),SUBSTITUTE(FORMULATEXT(y),"=",""),y)),"+"),
    HSTACK(SUM(ar),SUM(--(ar=5)),SUM(--(ar=7))))

    在此处输入图片描述

    • 2

  2. 另一个解决方案：

    =LET(
   data, A2:C2,
   shifts, TOCOL(--REGEXEXTRACT(CONCAT(IFNA(FORMULATEXT(data), data)), "\d", 1)),
   VSTACK(
     GROUPBY(shifts, shifts, COUNT, , 0),
     HSTACK("Total", SUM(shifts))
   )
 )

    在此处输入图片描述

    工作原理

    首先，我们利用和A2:C2的组合，从数据中获取连续 n 天的轮班FORMULATEXTREGEXEXTRACT

    在此处输入图片描述

    shifts ← TOCOL(--REGEXEXTRACT(CONCAT(IFNA(FORMULATEXT(data), data)), "\d", 1))

    然后我们GROUPBY根据这些值的数量对其进行分组。

    GROUPBY(shifts, shifts, COUNT, , 0)

    最后总数堆叠在下面使用VSTACK

    VSTACK(
  GROUPBY(shifts, shifts, COUNT, , 0),
  HSTACK("Total", SUM(shifts))
)

    如果希望结果水平显示，可以使用TRANSPOSE

    =LET(
   data, A2:C2,
   shifts, TOCOL(--REGEXEXTRACT(CONCAT(IFNA(FORMULATEXT(data), data)), "\d", 1)),
   TRANSPOSE(VSTACK(
     GROUPBY(shifts, shifts, COUNT, , 0),
     HSTACK("Total", SUM(shifts))
   ))
 )

    在此处输入图片描述

    • 2

  3. 对于每个员工行，您需要三个公式：

    1. 总天数（假设一月至十二月的数据在 B 至 M 列中）：

      =SUMPRODUCT(B2:M2)

    2. 5天轮班次数：

      =SUMPRODUCT(LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(FORMULATEXT(B2:M2), "=", ""), "+", " "), " ", " "))-LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(FORMULATEXT(B2:M2), "=", ""), "+", " "), " ", " 5 ")))/LEN("5")

    3. 7 天轮班次数：

      =SUMPRODUCT(LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(FORMULATEXT(B2:M2), "=", ""), "+", " "), " ", " "))-LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(FORMULATEXT(B2:M2), "=", ""), "+", " "), " ", " 7 ")))/LEN("7")
    • 0

相关问题