自动填充行数据从多个工作表使用单一功能在excel

wkyowqbh  于 2023-01-14  发布在  其他
关注(0)|答案(1)|浏览(125)

我有一个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))
zqdjd7g9

zqdjd7g91#

您可以在单元格H2中尝试以下方法:

=LET(incProj, FILTER(A2:A6, E2:E6="No"), prjProf, G2:G3, datesProf, H1:O1,
  prj, A10:A11, dates, B9:K9, profit, B10:K11, DROP(REDUCE("", prjProf, 
  LAMBDA(ac,p, VSTACK(ac, N(ISNUMBER(XMATCH(p, incProj))) *
      XLOOKUP(datesProf,dates, FILTER(profit, prj=p),0)))),1))

下面是输出:

行和列都是一次生成的,请根据实际问题进行调整,并确认结果,不考虑项目的起止日期,只考虑该表中的Exclude列,通过LET定义了一些名称,便于阅读公式。

    • 注意**:由于我们使用的是XLOOKUP函数,因此datesprofit需要具有相同的列数。

相关问题