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

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

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

以下是我的报告的规格:

  • Column ARow 2Row 15代表为每个项目分配的资源(人员)

  • Column B, Column C.... 表示每个项目每月分配的资源百分比

  • 每个资源column A合并到 2 个单元,因为每个资源每月可以参与 1 个或 2 个项目

  • 一个月内从事单个项目的资源将合并两个单元格。从事 2 个项目的资源将有 2 行代表该项目

  • 每个项目都有一个数字,代表分配给该项目的资源百分比

以下是我的报告的截图:

项目及资源分配报告截图

在底部报告中,我有一个摘要部分,它表示每个项目分配的资源数量。目前这些计数由我手动添加。我需要帮助来生成这些摘要的公式。以下是我想要实现的目标:

  1. 从单元格 (A20 - A24) 读取字符串
  2. 在月份列(B2 - B15)中进行查找以获取以项目字符串开头的单元格
  3. 从上述单元格中筛选数字(来自步骤 2)
  4. 将数字相加并除以100得到资源数量

到目前为止,我能够生成这个公式,从传递给它的行中读取数字:

=TEXTJOIN("",TRUE,IFERROR((MID(B2,ROW(INDIRECT("1:"&LEN(B2))),1)*1),""))

它给出了包含文本的50单元格中的数字。B2"Project A 50%"

答案1

如果您有O365,您可以使用该FILTER功能:

  • 按 A20:Ann 的内容过滤列表

  • 仅返回每个字符串中最后一个空格分隔的值(百分比)

  • 总结结果

  • IFERROR在项目不存在的情况下使用

      =IFERROR(SUM(--((TRIM(RIGHT(SUBSTITUTE(FILTER(B$2:B$15,ISNUMBER(SEARCH($A20,B$2:B$15)))," ",REPT(" ",99)),99))))),"")
    

或者(取决于您是否想返回零或""不存在的条目)

    =IFERROR(SUM(--((TRIM(RIGHT(SUBSTITUTE(FILTER(B$2:B$15,ISNUMBER(SEARCH($A20,B$2:B$15)))," ",REPT(" ",99)),99))))),"")

编辑:在评论中,OP 提到项目可以有类似的名称,但前面要加一个字符,这些应该分开处理。这需要不同的过滤标准:

=IFERROR(SUM(--((TRIM(RIGHT(SUBSTITUTE(FILTER(B$2:B$15,LEFT(B$2:B$15,LEN($A20))=$A20)," ",REPT(" ",99)),99))))),"")

在此处输入图片描述

答案2

2-15 行的数据表示很混乱,因为你将两部分信息合并在一个单元格中。这基本上会禁用所有数字单元格函数的可用性。将项目标识符和资源量分成两列。

鉴于 B 列中的项目标识符和 C 列中的资源,您只需将单元格 B20 设置为“=sumif(b$2:b$15;"A";c$2:c$15)”。对于项目 B,您可以将该公式修改为“B”。

为了更快地输入而不修改公式,创建一个包含 ABCD ...的隐藏列,并让上述公式中的固定字符串指向隐藏值 ABCD ...。

答案3

你可能不会喜欢这个答案,但是请保持开放的心态

想必这不会是你最后一次需要分析这个表。如果你现在进行重组,以后你的生活就会更轻松。

  1. 合并单元格当时感觉是个好主意,但以后只会给你带来麻烦
  2. 将变量组合到单个单元格“项目 A 50%”中总是会使分析更加困难,因为你现在必须分离这些变量才能理解它们
  3. 交叉制表输入数据可能稍微简单一些,但需要付出代价。如果您现在需要制作一份报告,询问您分配给项目 A 的年度季度资源,该怎么办?此外,当您进入下一年时会发生什么?创建一个新表?然后你如何比较它们?

我并不是想在这里装糊涂,但是在使用 Excel 和其他系统处理数据 25 年后,我想鼓励人们思考他们将来可能需要做什么。

像这样的事情对你最有帮助:

在此处输入图片描述

请注意,使用 Ctrl+T 或功能区“主页”选项卡上的“格式化为表格”,可以将其格式化为表格。

这种结构有许多好处:

  1. 现在,只需在新行中键入内容即可轻松输入数据
  2. 可以使用数据透视表对任何维度(人员、月份、项目)进行分析 - 对于本应简单的问题,无需复杂的公式
  3. 制图现在变得非常简单
  4. 您可以将新信息作为列添加到每行。也许您想将此人的直线经理或团队名称作为 XLOOKUP 从另一个表添加到行中,或者您可能想为该人添加有关该月的绩效或休假的注释
  5. 如果你需要将其复制到另一个电子表格中,或者将其作为 csv 发送给某人,或者将其加载到数据库或数据框中的表格中进行分析,你无需花时间取消合并并填充空单元格即可完成这些操作

编辑:

要用这个修改后的数据结构来回答您的问题,您可以简单地创建一个数据透视表:

在此处输入图片描述

相关内容