☰

根据起始字符串过滤列，从过滤后的单元格中读取数字并执行算术运算以生成 Excel 中两行的摘要

tag-icon • tag-icon microsoft-excel macos worksheet-function microsoft-excel-365
根据起始字符串过滤列，从过滤后的单元格中读取数字并执行算术运算以生成 Excel 中两行的摘要

我正在制作一份项目和资源分配报告。我需要帮助来生成公式以在报告的右侧部分显示摘要。

以下是我的报告的规格：

  • A 列第 2 行至第 15 行代表为每个项目分配的资源（人员）
  • B列、C列....代表每个项目每月分配的资源百分比
  • A 列的每个资源合并到 2 个单元格，因为每个资源每月可以参与 1 个或 2 个项目
  • 一个月内从事单个项目的资源将合并两个单元格。从事 2 个项目的资源将有 2 行代表该项目
  • 每个项目都有一个数字，代表分配给该项目的资源百分比

以下是我的报告的截图：

项目资源分配表截图

在这里，在摘要中，我表示每个人将在每个项目上花费的总周数。为了简化计算，我假设每个月相当于正好 4 周。所以，

  • 如果人员 A 在 2 月份全力投入项目 B。那么项目 B 的总结中需要考虑的总结数字应该是 4
  • 同样，如果 A 在 1 月份分别在 2 个项目上工作，那么要考虑的总计时间应该是每个项目 2-2 周

一般来说，总结中要考虑的数字可以用数学表示为：

4 *[Percentage from column] / 100

以下是我想要实现的目标：

  1. 从单元格读取字符串（F1 - J1）
  2. 在两行（B2，C2 ...）和（B3，C3 ...）的人员列中进行查找以获取以项目字符串开头的单元格
  3. 从上述单元格中筛选数字（来自步骤 2）对数字求和，然后除以 100 并乘以 4（得到周数）

这是我针对类似问题创建的答案，作为参考这里：

=IFERROR((IFERROR(SUM(--((TRIM(RIGHT(SUBSTITUTE(FILTER($B2:$C2,ISNUMBER(SEARCH(J$1,$B2:$C2)))," ",REPT(" ",99)),99))))), 0) + IFERROR(SUM(--((TRIM(RIGHT(SUBSTITUTE(FILTER($B3:$C3,ISNUMBER(SEARCH(J$1,$B3:$C3)))," ",REPT(" ",99)),99))))), 0))*4, "")

这给了我项目 B 的 6。有没有办法简化这个？我觉得我在这里做了很多多余的操作。此外，如果未找到项目，它会返回 0。我更喜欢用空字符串代替它

如果有更好的方法来执行此计算，请随时提出建议。

笔记：

  1. 对于合并两个单元格的列，该人仅应考虑一次计算。
  2. 我的摘要计算基于两行数据
  3. 这里我使用人员名称（“人员 A”、“人员 B”等）和项目名称（“项目 A”、“项目 B”等）字符串作为占位符文本。在实际报告中，这些将变为带有随机字母的实际值。

PS：这与我之前的问题类似，我在单列上生成摘要，其中列内的两个单元格可以合并：Excel - 根据起始字符串过滤行，从过滤后的行中读取数字并执行算术运算以生成列摘要。但是，这里我计算了两行来得到汇总。合并行单元格只需要考虑一次即可计算。

答案1

也许，尝试一下这个更短的公式建议。

1] 在 中F2，横向和纵向复制公式：

=SUMPRODUCT(4*TEXT(SUBSTITUTE($B2:$C3,F$1,""),"0%;;;\0"))

2]你之前的问题也可以用这个公式来解决，我把它放在了A19:C23中。

在 中B19，横向和纵向复制公式：

=SUMPRODUCT(0+TEXT(SUBSTITUTE(B$2:B$15,$A19,""),"0%;;;\0"))

和，

编辑

删除 0

1]使用单元格格式：

所有公式单元格格式在>>“会计”>>“符号”，选择“无”>>“小数位”：点击“1”

2] 使用 IFERROR(1(/1.......),"") 添加到 F2 公式，变成 >>

=IFERROR(1/(1/SUMPRODUCT(4*TEXT(SUBSTITUTE($B2:$C3,F$1,""),"0%;;;\0"))),"")

在此处输入图片描述

答案2

解决方案更加复杂，因为要处理的范围是多维的。（在您之前的问题中，它只是一个维度）。

一种处理方法是将其转换为一维数组，可以使用TEXTJOIN和 来完成FILTERXML。有一个限制，即TEXTJOIN函数生成的字符串不能超过32,767字符。如果这会成为问题，那么 VBA 或 Power Query 解决方案可能会更好。

此外，FILTERXML在 Mac 版本或 Excel Online 上不可用。

如果不适用这些限制，那么

F2: =IFERROR(4*SUM(FILTERXML("<t><s>" & SUBSTITUTE(FILTERXML("<t><s>" &TEXTJOIN("</s><s>",TRUE,$B2:$C3) & "</s></t>","//s[starts-with(.,'" & F$1 & "')]")," ","</s><s>") & "</s></t>","//s[last()]")),"")

  • 向右和向下填充

  • 我们首先创建一个XML（使用TEXTJOIN）为每个单元创建一个单独的节点

    • 我们的参数仅返回第 1 行中的项目名称xPath的节点。start-with
    • 然后我们使用SUBSTITUTE创建另一个XML基于空格分隔的节点并xPath返回最后一个节点。
    • 进行数学运算。

在此处输入图片描述

编辑：

如果你不是有FILTERXML函数，试试这个公式（O365 Mac 或 Windows 和 Excel Online）它使用不同的方法将二维数组转换为一维数组进行处理：

=IFERROR(SUM(4*TRIM(RIGHT(
   SUBSTITUTE(LET(
        seq,IF(SEQUENCE(99)=1,1,(SEQUENCE(99)-1)*99),
        x,TRIM(MID(TEXTJOIN(REPT(" ",99),TRUE,$B2:$C3),seq,99)),
        y, FILTER(x,LEFT(x,LEN(F$1))=F$1),y),
   " ",REPT(" ",99)),99))),"")

此公式仍受 32,767 个字符的限制。您是否会遇到这种情况主要取决于项目名称中有多少个空格。计算公式大致如下：

  • a = 单元格中的平均字符数

  • b = 单元格中的空格数

  • c = 细胞数量

     (a+b*99) * c

例如，如果您有15字符、5空格和24单元格，那么将会得到大约12,000字符。

相关内容