我正在制作一份项目和资源分配报告。我需要帮助来生成公式以在报告底部显示摘要。
以下是我的报告的规格:
Column A
从Row 2
到Row 15
代表为每个项目分配的资源(人员)Column B
,Column C
.... 表示每个项目每月分配的资源百分比每个资源
column A
合并到 2 个单元,因为每个资源每月可以参与 1 个或 2 个项目一个月内从事单个项目的资源将合并两个单元格。从事 2 个项目的资源将有 2 行代表该项目
每个项目都有一个数字,代表分配给该项目的资源百分比
以下是我的报告的截图:
在底部报告中,我有一个摘要部分,它表示每个项目分配的资源数量。目前这些计数由我手动添加。我需要帮助来生成这些摘要的公式。以下是我想要实现的目标:
- 从单元格 (A20 - A24) 读取字符串
- 在月份列(B2 - B15)中进行查找以获取以项目字符串开头的单元格
- 从上述单元格中筛选数字(来自步骤 2)
- 将数字相加并除以
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
你可能不会喜欢这个答案,但是请保持开放的心态
想必这不会是你最后一次需要分析这个表。如果你现在进行重组,以后你的生活就会更轻松。
- 合并单元格当时感觉是个好主意,但以后只会给你带来麻烦
- 将变量组合到单个单元格“项目 A 50%”中总是会使分析更加困难,因为你现在必须分离这些变量才能理解它们
- 交叉制表输入数据可能稍微简单一些,但需要付出代价。如果您现在需要制作一份报告,询问您分配给项目 A 的年度季度资源,该怎么办?此外,当您进入下一年时会发生什么?创建一个新表?然后你如何比较它们?
我并不是想在这里装糊涂,但是在使用 Excel 和其他系统处理数据 25 年后,我想鼓励人们思考他们将来可能需要做什么。
像这样的事情对你最有帮助:
请注意,使用 Ctrl+T 或功能区“主页”选项卡上的“格式化为表格”,可以将其格式化为表格。
这种结构有许多好处:
- 现在,只需在新行中键入内容即可轻松输入数据
- 可以使用数据透视表对任何维度(人员、月份、项目)进行分析 - 对于本应简单的问题,无需复杂的公式
- 制图现在变得非常简单
- 您可以将新信息作为列添加到每行。也许您想将此人的直线经理或团队名称作为 XLOOKUP 从另一个表添加到行中,或者您可能想为该人添加有关该月的绩效或休假的注释
- 如果你需要将其复制到另一个电子表格中,或者将其作为 csv 发送给某人,或者将其加载到数据库或数据框中的表格中进行分析,你无需花时间取消合并并填充空单元格即可完成这些操作
编辑:
要用这个修改后的数据结构来回答您的问题,您可以简单地创建一个数据透视表: