我有一个All Projects Net Profit工作表,其中包含项目信息数据集,该数据集将随着新条目的增加而不断增大,如下所示:
对于每个项目,我都有一个基于项目名称的单独工作表,用于计算成本和总体净利润(请参见项目1工作表底部的样本数据)。
在All Projects Net Profit工作表上的上述数据集右侧,我有一个公式,它复制每个项目的每月净利润。
此功能还允许在特定日期终止项目,具体日期由E列中的相关行确定,或者如果F列中的同一行设置为"是",则完全排除项目。如果项目在设定日期取消或只是未按预期启动,此功能将用作快速查看财务状况的方法,而不会影响源数据。
我在K2中使用的公式如下:
=LET(StartOfMonths, $K$1:INDIRECT(ADDRESS(ROW($K$1),COLUMN($K$1)+ StudioProjectedOperatingMonths -1)),
ProjectNetProfitData, INDIRECT("'"&A2&"'!$I$1:$XX$13"),
TerminateProjectEarly, NOT(ISBLANK($E2)),
ExcludeProject, EXACT($F2, "Yes"),
NetProfitRowIndex, 13,
SetRowToZeros, SEQUENCE(1,StudioProjectedOperatingMonths,0,0),
IFERROR(IF(ExcludeProject,SetRowToZeros,IF(TerminateProjectEarly,HLOOKUP(IF(StartOfMonths<=$E2,StartOfMonths, 0), ProjectNetProfitData,NetProfitRowIndex,FALSE),HLOOKUP(IF(StartOfMonths,StartOfMonths, 0), ProjectNetProfitData,NetProfitRowIndex,FALSE))),0))
其中:
- 开始月份是从K1(2017年8月)到工作室计划运营月份**(定义为401)的日期范围
- ProjectNetProfitData**是项目工作表上的数据集(工作表名称使用A2)
- 提前终止项目**仅包括此日期之前的净利润
- ExcludeProject**不包括项目的任何净利润(如果已设置)
- 净利润行索引13是要从项目净利润数据**中引用的行
为了使它对所有行都有效,我将函数复制到K3、K4、K5和K6中。虽然这一切都按预期工作,但理想情况下,我希望在K2中有一个函数,它将根据A2以下的条目自动填充各行,这样最终用户就不需要在K列中复制函数。
我相信像BYCOL这样的东西可以帮助我实现这一点,但我有点不知所措如何在这种情况下应用它。
有什么想法,我可以修改上述功能,以这种方式工作?
- 项目1**工作表"净利润"示例数据(不包括不必要的行-仅第13行,所有其他项目都采用相同的布局,但日期范围不同):
| | 九月十七日|十月十七日|十一月十七日|十二月十七日|一月十八日|二月十八日|三月十八日|四月十八日|五月十八日|六月十八日|
| - ------|- ------|- ------|- ------|- ------|- ------|- ------|- ------|- ------|- ------|- ------|
| 月度净利润|81,604英镑|小行星81604|81 604英镑|小行星81604|小行星81604|小行星81604|小行星81604|小行星81604|小行星81604|小行星81604|
- 所有项目净利润**工作表数据集:
| 项目名称|项目代码名称|开始日期|结束日期|项目终止日期|从损益表中排除|
| - ------|- ------|- ------|- ------|- ------|- ------|
| 项目1|项目1代码名称|2017年9月1日|二〇二三年三月三十一日||没有|
| 项目2|项目2代码名称|2020年1月2日|二〇二二年十二月三十一日||没有|
| 项目3|项目3代码名称|2020年3月1日|二零二一年十二月三十一日||没有|
| 项目4|项目4代码名称|2020年4月1日|2021年6月30日||没有|
| 项目五|项目5代码名称|2023年1月1日|二〇二四年十二月三十一日||没有|
- 问题的最终解决方案**
在David Leal的帮助下,最终解决方案支持项目终止日期和单独表格中的净利润数据:
=LET(dataset, FILTER(A2:F999,(A2:A999<>"")),
prjName, FILTER(dataset,{1,0,0,0,0,0}),
exclude, FILTER(dataset,{0,0,0,0,0,1}),
incProj, FILTER(prjName, exclude="No"),
terminationDate, FILTER(dataset,{1,0,0,0,1,0}),
datesProf, I1:BD1,
GET_TERMINATION_DATE, LAMBDA(proj, VLOOKUP(proj, terminationDate,2,FALSE)),
GET_DATES, LAMBDA(proj, INDIRECT("'"&proj&"'!B1#")),
GET_PROFIT,LAMBDA(proj, LET(dates, INDIRECT("'"&proj&"'!B1#"),
INDIRECT("'"&proj&"'!B3:"&ADDRESS(3,MAX(COLUMN(dates)))))),
DROP(REDUCE("", prjName,
LAMBDA(ac,p, VSTACK(ac, N(ISNUMBER(XMATCH(p, incProj))) *
XLOOKUP(datesProf,GET_DATES(p)*(IF(NOT(ISBLANK(GET_TERMINATION_DATE(p))), GET_DATES(p)<= GET_TERMINATION_DATE(p),1)), GET_PROFIT(p),0)))),1))
1条答案
按热度按时间zqdjd7g91#
您可以在单元格
H2
中尝试以下方法:下面是输出:
行和列都是一次生成的,请根据实际问题进行调整,并确认结果,不考虑项目的起止日期,只考虑该表中的Exclude列,通过
LET
定义了一些名称,便于阅读公式。XLOOKUP
函数,因此dates
和profit
需要具有相同的列数。