在Excel中计算项目员工的部分月份薪金成本[已关闭]

- 已关闭 * * 。此问题需要更多 focused 。当前不接受答案。
- 想要改进此问题吗？ * * 更新问题，使其仅关注 editing this post 的一个问题。

13 天前关闭。
这篇文章是 9 天前编辑并提交审查的。
Improve this question 格式
我有以下输入数据，其中包含 * * Employee * * 和项目信息（日期格式为 MM/DD/YYYY ）：
| 员工|角色|专业|开始日期|结束日期|
| - -| - -| - -| - -| - -|
| 鲍勃|高级程序员|程序设计|2020 年 12 月 1 日|2021 年 5 月 3 日|
| 戴夫|中级程控仪|程序设计|2020 年 1 月 2 日|2020 年 5 月 30 日|
| 彼得|高级程序员|程序设计|2020 年 1 月 2 日|2020 年 1 月 30 日|
| 杰克|初级程序员|程序设计|2020 年 1 月 2 日|2020 年 6 月 30 日|
| 理查德德|资深艺术家|艺术|2020 年 1 月 3 日|2020 年 4 月 30 日|
| 罗德尼|QA 负责人|质量保证部|2020 年 1 月 3 日|2020 年 6 月 30 日|
| 项目 1 - 聘用 1|高级制片人|生产部|2020 年 1 月 2 日|2020 年 5 月 30 日|
| 罗杰|质量保证部|质量保证部|2020 年 1 月 1 日|2020 年 4 月 30 日|
| 韦斯利|中级程控仪|程序设计|2020 年 1 月 2 日|2020 年 5 月 31 日|
| 拉结|资深艺术家|艺术|2020 年 1 月 1 日|2020 年 6 月 30 日|
| 项目 1 - 聘用 2|首席程序员|程序设计|2020 年 1 月 1 日|2020 年 7 月 31 日|
然后，我有一个花名册表，其中包含每个雇员的工资信息：
| 员工|薪资起始日期|薪资终止日期|薪资|月薪|日薪|
| - -| - -| - -| - -| - -| - -|
| 鲍勃|2020 年 1 月 1 日|二零二一年三月三十一日|五万两千美元|四千三百三十三元|217 美元|
| 鲍勃|2021 年 4 月 1 日|2022 年 3 月 31 日|五万五千美元|四千五百八十三元|229 美元|
| 鲍勃|2022 年 4 月 1 日||五万八千美元|四千八百三十三元|242 美元|
| 戴夫|2020 年 1 月 1 日|二零二一年三月三十一日|三万八千美元|三千一百六十七元|一百五十八美元|
| 戴夫|2021 年 4 月 1 日||四万两千美元|三千五百元|一百七十五美元|
| 韦斯利|2020 年 1 月 1 日||四万五千美元|三千七百五十元|一百八十八元|
| 杰克|2020 年 1 月 1 日||两万五|2,083 美元|一百零四美元|
| 理查德德|2020 年 1 月 1 日||四万五千美元|三千七百五十元|一百八十八元|
| 罗德尼|2020 年 1 月 1 日||五万两千美元|四千三百三十三元|217 美元|
| 项目 1 - 聘用 1| 2020 年 1 月 1 日||四万一千五百元|三千四百五十八元|173 美元|
| 罗杰|2020 年 1 月 1 日||两万美元|一千六百六十七元|83 美元|
| 史提|2020 年 1 月 1 日||两万七千美元|2,250 美元|一百一十三美元|
| 拉结|2020 年 1 月 1 日||4 万美元|三千三百三十三元|167 美元|
| 彼得|2020 年 1 月 1 日||三万四千美元|两千八百三十三元|142 美元|
| 萨拉|2020 年 1 月 1 日||两万两千美元|一千八百三十三元|92 美元|
| 克洛伊|2020 年 1 月 1 日||三万三千美元|两千七百五十美元|一百三十八元|
| 马修|2020 年 1 月 1 日|二零二一年三月三十一日|两万三千美元|一千九百一十七元|96 美元|
| 马修|2021 年 4 月 1 日||两万八千美元|2,333 美元|一百一十七美元|
| 项目 1 - 聘用 2| 2020 年 1 月 1 日||三万六千美元|三千美元|150 美元|
以下条件适用于上表：
1.一个员工可以有多个薪金，但相应的时间间隔不能重叠（ * * Start * / * End Dates * * 列） - 用于年度加薪。
1.如果 " * * 结束日期 * * " 值为空，则员工当前使用的是设置的薪金。
我发布了一个相关的问题：* Sequence a 'sumif' with exclusions in Excel * ，计算月薪成本，仅考虑整个月的分配，即只有在整个月都为员工分配资源时才分配资源。
现在我想考虑部分分配，即根据总工作日数计算成本（星期一到星期五）考虑到上表中 * * Daily Salary * * 列的值，为员工分配了由 * * Start Date * * 和 * * End Date * * 定义的部分月份。我试图根据 DavidLeal 提供的答案修改公式，但我找不到一个方法来适应它。
下面是用于解决上一个非部分分配问题的公式。

=LET(namePrj, TB_Prj[Employee], startPrj, TB_Prj[Start Date], endPrj,
 TB_Prj[End Date],name, TB_Roster[Employee],start, TB_Roster[Salary Start Date],
 end, TB_Roster[Salary End Date],salary, TB_Roster[Salary Monthly],
 SOMs, H1:S1, EOMs, EOMONTH(SOM,0),
 BYCOL(SOMs, LAMBDA(SOM, LET(EOM, EOMONTH(SOM,0),
 namesActive, FILTER(namePrj, (startPrj <= SOM) * (endPrj >= EOM)),
  cost, FILTER(salary, (start <= SOM) * (IF(end > 0, end, EOM) >= EOM) *
    (ISNUMBER(XMATCH(name,namesActive))),0), sum(cost)
  )))
 )

中的每一个
我正在寻找一个 Excel 公式，该公式将返回序列的第一个月的每月薪金成本：

=EDATE(DATE(2020,1,1), SEQUENCE(1, 12, 0))

格式
然后将其扩展到周期的末尾或者仅返回整个周期 1/1/2020-12/31/2020 的 1x12 数组。

在单元格I2中，输入以下公式（有关简化版本，请参阅UPDATE部分）：

=LET(setPrj, A2:E12, setRoster, A15:F33, SOMs, I1:T1,
 namesPrj, INDEX(setPrj,,1), startsPrj, INDEX(setPrj,,4), endsPrj,
 INDEX(setPrj,,5),names, INDEX(setRoster,,1),starts, INDEX(setRoster,,2),
 ends, INDEX(setRoster,,3),
 salaries,INDEX(setRoster,,6), empty, ",,",
 SPLIT, LAMBDA(x,case, LET(y, TEXTSPLIT(TEXTJOIN(";",,x),",",";"), z,
   FILTER(y, INDEX(y,,1)<>"", NA()),
     IFS(case=0, z,case=1, HSTACK(INDEX(z,,1), 1*CHOOSECOLS(z,2,3)), case=2, 1*z))),
 BYCOL(SOMs, LAMBDA(SOM, LET(EOM, EOMONTH(SOM,0),endsAdj, IF(ends > 0, ends, EOM),
  overlapsPrj, MAP(namesPrj, startsPrj, endsPrj, LAMBDA(namePrj,startPrj,endPrj,
   IF(AND(startPrj <= EOM, endPrj >= SOM),
   TEXTJOIN(",",,namePrj, MAX(startPrj, SOM), MIN(endPrj, EOM)), empty))),
   setPrjAdj, SPLIT(overlapsPrj,1),
   overlapsRoster, IF(ISNA(ROWS(setPrjAdj)), NA(),
    MAP(INDEX(setPrjAdj,,1), INDEX(setPrjAdj,,2), INDEX(setPrjAdj,,3),
      LAMBDA(name,start,end,
      LET(found, FILTER(HSTACK(starts, endsAdj, salaries), (names=name) * (starts <= end)
        * (endsAdj >= start), NA()), IF(AND(ROWS(found)=1, NOT(ISNA(found))),
        TEXTJOIN(",",, MAX(INDEX(found,,1), start),
          MIN(INDEX(found,,2), end), INDEX(found,,3)), empty)
    )))
  ),setRosterAdj, SPLIT(overlapsRoster,2),
  IF(ISNA(ROWS(setRosterAdj)), 0,
    LET(startEffDates, INDEX(setRosterAdj,,1), endEffDates, INDEX(setRosterAdj,,2),
      effSalaries, INDEX(setRosterAdj,,3),days, NETWORKDAYS(startEffDates, endEffDates),
      SUMPRODUCT(days, effSalaries)
    ))
  )))
)

这是整个周期内单个数组1x12的输出。

注：在单元格I15中，我使用了问题答案中建议的想法：sequence a sumif with exclusions in Excel，用于检查花名册数据集的间隔不重叠。

更新

公式可以简化如下，不需要定义用户LAMBDA函数：SPLIT。我们可以让MAP函数只返回索引位置的重叠部分，然后使用此信息查找其余信息。我们还使用DROP/REDUCE/VSTACK/HSTACK组合返回多个值（我们受到MAP的限制，只能返回单个列）

=LET(setPrj, A2:E12, setRoster, A15:F33, SOMs, I1:T1,namesPrj, INDEX(setPrj,,1),
 startsPrj, INDEX(setPrj,,4), endsPrj,INDEX(setPrj,,5),names, INDEX(setRoster,,1),
 starts, INDEX(setRoster,,2), ends, INDEX(setRoster,,3), salaries,INDEX(setRoster,,6),
 BYCOL(SOMs, LAMBDA(SOM, LET(EOM, EOMONTH(SOM, 0), endsAdj, IF(ends > 0, ends, EOM),
  overlapsPrj, MAP(SEQUENCE(ROWS(setPrj)), startsPrj, endsPrj,
    LAMBDA(idx, startPrj, endPrj,IF(AND(startPrj <= EOM, endPrj >= SOM),
    idx, 0))), pIdx, FILTER(overlapsPrj, overlapsPrj > 0, NA()),
  overlapsRoster, IF(ISNA(ROWS(pIdx)), NA(), DROP(REDUCE(0,pIdx,
    LAMBDA(acc, idx, VSTACK(acc, LET(name, INDEX(namesPrj,idx),
    start, MAX(INDEX(startsPrj,idx), SOM), end, MIN(INDEX(endsPrj,idx), EOM),
    found, FILTER(HSTACK(starts, endsAdj, salaries), (names=name) * (starts <= end)
      * (endsAdj >= start), NA()),
    IF(AND(NOT(ISNA(found)), ROWS(found)=1), HSTACK(MAX(start, INDEX(found,1,1)),
      MIN(end, INDEX(found,1,2)), INDEX(found,1,3)),{0,0,0})
    )))),1)), rEff, FILTER(overlapsRoster, INDEX(overlapsRoster,,3) > 0, NA()),
  IF(ISNA(ROWS(rEff)),0, LET(effStarts, INDEX(rEff,,1), effEnds,
    INDEX(rEff,,2), effSalaries, INDEX(rEff,,3),days, NETWORKDAYS(effStarts, effEnds),
    SUMPRODUCT(days, effSalaries)
  )))))
 )

说明

总体思路

对于计算，我们使用两个主要思想：
1.检查两个间隔（A，B）是否重叠的条件：
(StartA <= EndB) AND (EndA >= StartB) -> TRUE
1.如果两个区间A、B重叠，则两个区间的交集为：
[MAX(StartA, StartB), MIN(EndA, EndB)]
我们将遍历每个月。首先查看项目数据集，以查找在给定月份重叠的间隔，并查找与当前月份重叠的每个间隔的交集。
一旦我们有了资源清单及其Map的交集。我们需要寻找符合间隔的Map工资。我们再次重复在项目数据集中找到的交集，但现在在工资数据集中寻找第二个交集。
公式
我们使用LET函数来定义输入和中间结果。我们开始定义两个数据集setPrj，用于表示项目信息的数据，以及setRoster，用于输入集所需的名册信息和相关名称。
我们将使用MAP函数（用于查找每个重叠）这对于进行转换非常方便，它可以接受多个相同大小的输入数组，但然后返回单个数组。为了避免这种情况，MAP的输出将是nx1数组，并且在每行上，我们在LET中定义了一个用户自定义的LAMBDA函数，用于将结果从CSV格式转换回2D数组。
此用户函数名为SPLIT（不要将其与TEXTSPLIT标准Excel函数混淆）。在LET函数内定义此函数，作用域将限于该函数，无需为其创建命名区域。

SPLIT, LAMBDA(x,case, LET(y, TEXTSPLIT(TEXTJOIN(";",,x),",",";"),
  z, FILTER(y, INDEX(y,,1)<>"", NA()),
  IFS(case=0, z,case=1, HSTACK(INDEX(z,,1),
    1*CHOOSECOLS(z,2,3)), case=2, 1*z)))

因为输入参数x是一个由逗号分隔的值组成的nx1数组，所以一旦我们将其转换为二维数组，就需要将一些列转换回它们的原始数据类型。我们使用第二个输入参数case来考虑主公式中使用的所有转换方案。

case=1，将列2与3转换为数字
case=2，将所有列转换为数字
注意：case=0不用于主公式，仅用于测试目的。MAP的输出将在两个调用中是一个三列数组。

最后，输出将是一个已删除空行的nxm数组（",,"。有一个为它定义的名称：empty）。如果所有行都为空，FILTER将返回错误（Excel中不存在空集合），以防止我们使用此函数的第三个输入参数返回NA()（我们在主公式的其他部分使用相同的想法）
现在我们使用BYCOL来迭代月份（我们使用此函数是因为月份是列格式）。对于每个月份（表示为该月的第一天）我们使用SOM（月初）和EOM（月末）名称来查找重叠和交叉。第一个MAP调用执行此操作，结果命名为intersecPrj：

MAP(namesPrj, startsPrj, endsPrj, LAMBDA(namePrj,startPrj,endPrj,
  IF(AND(startPrj <= EOM, endPrj >= SOM),
  TEXTJOIN(",",,namePrj, MAX(startPrj, SOM), MIN(endPrj, EOM)), empty)))

注：这里我们可以使用FILTER代替MAP，但使用后者我们可以同时找到重叠和交叉。结果以CSV格式按行存储，包含以下信息：name, startDate, endDate，其中日期表示交叉点日期。

现在，我们通过SPLIT函数将信息转换回2D数组：SPLIT(intersecPrj,1)，因为我们希望将name保留为文本，所以我们使用case=1并将其命名为：setPrjAdj它是一个数组nx3，其中n表示找到的交集的数目。
现在，我们需要在setPrjAdj中查找姓名对应的薪金。这里，我们需要考虑未找到交集的方案，即对于给定月份，没有与资源关联的项目。计算intersecRoster的条件防止出现以下情况：

IF(ISNA(ROWS(setRosterAdj)), 0,…)

我们可以检查NA()，因为SPLIT函数在没有交集的情况下返回这个值，所以如果条件是TRUE，我们返回NA()。如果ISNA的输入是一个数组，它返回一个数组。因此我们使用ROWS将测试缩减到单个单元格。如果数组的任何元素具有#N/A值，则ROWS输出将是#N/A。
如果找到项目交集，我们需要找到工资以及setPrjAdj中的日期信息与花名册数据集中的日期之间的相应新交集。
这通过以下MAP调用完成，并将结果命名为intersecRoster

MAP(INDEX(setPrjAdj,,1), INDEX(setPrjAdj,,2), INDEX(setPrjAdj,,3),
 LAMBDA(name, start, end,
  LET(found, FILTER(HSTACK(starts, endsAdj, salaries), (names=name) * (starts <= end)
        * (endsAdj >= start), NA()), IF(AND(ROWS(found)=1, NOT(ISNA(found))),
        TEXTJOIN(",",, MAX(INDEX(found,,1), start),
        MIN(INDEX(found,,2), end), INDEX(found,,3)), empty)
    )))

对于每个name、start和end（来自setPrjAdj），HSTACK(starts, endsAdj, salaries)由name过滤并查找重叠。
它使用endsAdj、而不是ends原始输入数据，因为我们需要处理空白结束日期。结果保存在found名称中。现在我们需要检查FILTER的空集。筛选器的not found条件（空集）由以下输出NA()表示。可能是找不到名称（拼写错误或名称丢失）。
如果它返回多行（不应发生此情况，因为花名册集的间隔不应重叠，即员工不能同时有两个薪金）。我们分配了empty行。由于无法确定薪金，因此此资源不会计入薪金每月成本。否则，我们使用相应的信息通过TEXTJOIN构建字符串：起始日期、终止日期和相应的薪金。其中起始日期和终止日期表示start和end日期（来自setPrjAdj）与花名册数据集的起始日期和终止日期（来自FILTER输出）之间的交叉点。
现在，intersecRoster具有CSV格式的以下信息：start, end, salary。我们现在通过SPLIT将字符串信息转换为二维数组，并将结果命名为setRosterAdj。我们使用case=2，因为所有信息都是数字。

SPLIT(intersecRoster,2)

这里我们需要防止名字在花名册表中找不到，以避免出现任何意外的结果。如果没有找到资源，那么我们通过下面的条件返回0：

IF(ISNA(ROWS(setRosterAdj)), 0,…)

现在我们有了所有要查找的信息。为了计算工作日期，我们使用NETWORKDAYS(startEffDates, endEffDates)，其中日期是setRosterAdj中的相应列，它被命名为days。
最后道：

SUMPRODUCT(days, effSalaries)

提供我们要寻找的结果。我们使用Eff（有效）作为Map数据行的名称，来命名setRosterAdj中的所有数据行。

非部分分配月薪

前一种方法是根据工作日和日薪来计算成本。如果您要考虑每月成本，而不是资源整个月都分派给您的月份，以及部分分派给您的月份的日薪，则调整后的公式如下：

=LET(setPrj, A2:E12, setRoster, A15:F33, SOMs, I1:T1, namesPrj, INDEX(setPrj,,1),
 startsPrj, INDEX(setPrj,,4), endsPrj,INDEX(setPrj,,5),names, INDEX(setRoster,,1),
 starts, INDEX(setRoster,,2), ends, INDEX(setRoster,,3),
 monthlySalaries,INDEX(setRoster,,5), dalySalaries,INDEX(setRoster,,6), empty, ",,",
 SPLIT, LAMBDA(x,case, LET(y, TEXTSPLIT(TEXTJOIN(";",,x),",",";"),
  z, FILTER(y, INDEX(y,,1)<>"", NA()),IFS(case=0, z,case=1, HSTACK(INDEX(z,,1),
  1*CHOOSECOLS(z,2,3)), case=2, 1*z))),
 BYCOL(SOMs, LAMBDA(SOM, LET(EOM, EOMONTH(SOM,0),endsAdj, IF(ends > 0, ends, EOM),
  overlapsPrj, MAP(namesPrj, startsPrj, endsPrj, LAMBDA(namePrj,startPrj,endPrj,
   IF(AND(startPrj <= EOM, endPrj >= SOM), TEXTJOIN(",",,namePrj, MAX(startPrj, SOM),
    MIN(endPrj, EOM)), empty))),
   setPrjAdj, SPLIT(overlapsPrj,1),
   overlapsRoster, IF(ISNA(ROWS(setPrjAdj)), NA(),
    MAP(INDEX(setPrjAdj,,1), INDEX(setPrjAdj,,2), INDEX(setPrjAdj,,3),
      LAMBDA(name,start,end,
      LET(found, FILTER(HSTACK(starts, endsAdj, dalySalaries, monthlySalaries),
       (names=name) * (starts <= end)  * (endsAdj >= start), NA()),
       IF(AND(ROWS(found)=1, NOT(ISNA(found))),
        TEXTJOIN(",",, MAX(INDEX(found,,1), start), MIN(INDEX(found,,2), end),
          CHOOSECOLS(found,3,4)), empty)
    )))
  ),setRosterAdj, SPLIT(overlapsRoster,2),
  IF(ISNA(ROWS(setRosterAdj)), 0,
    LET(startEffDates, INDEX(setRosterAdj,,1), endEffDates, INDEX(setRosterAdj,,2),
      effDailySalaries, INDEX(setRosterAdj,,3), effMonthlySalaries, INDEX(setRosterAdj,,4),
      days, NETWORKDAYS(startEffDates, endEffDates), monthWorkDays, NETWORKDAYS(SOM, EOM),
      actualSalaries, IF(days = monthWorkDays, effMonthlySalaries, effDailySalaries),
      actualDays, IF(days = monthWorkDays, 1, days),
      SUMPRODUCT(actualDays, actualSalaries)
    ))
  )))
)

提示

由于这是一个大型公式，而Excel没有提供正确调试某些数组函数的方法，因此，有一种调试某些部分结果的方法是很有帮助的。由于BYCOL每列返回一个单元格，因此，在LET中定义另一个用户的LAMBDA函数以实现此目的是很方便的。例如，下面的函数，并将其命名为DEBUG。

LAMBDA(x, TEXTJOIN(" & ",,x)),

则它可用于返回DEBUG的输出，而不是用于测试目的的最终结果。

在Excel中计算项目员工的部分月份薪金成本[已关闭]

1条答案

更新

说明

总体思路

非部分分配月薪

提示

相关问题

热门标签

最新问答