Mark S.'s questions

我有一张格式松散的表格，其中人员在顶部，任务在行中，还有一列表示开始日期，一列表示结束日期。用户将在给定的子任务中输入预期的小时数，然后它将汇总到任务本身中。我能够得到按行求和的结果，除以查找的月份数，但无法将值正确分布在相关月份中。

这就是数据的结构。

要求制作一个表格，列出任务和子任务，并计算相关月份内完成给定任务所花费时间的直线平均值。（总计/月份数 -> 开始日期和结束日期之间的月份数）。

例如，在这里您可以看到我从总任务中过滤掉子任务，并将它们各自的总小时数分散到预计发生任务的月份中。

我现在想集中讨论下表，因为上表是从那里总结出来的，所以我想我可以相应地调整其余部分。

现在，我从标题中该任务/日期的每个任务/月总数中取出 sumifs，只是为了让它溢出到列中。但是，这会将相同的值分布到所有月份的任务中，而不仅仅是相关月份。所以我尝试了一个 If 来检查日期是否在开始日期和结束日期之间，但它不会溢出。如果我单独这样做，它不能正确验证逻辑。

我还尝试使用 LET 语句来定义开始日期和结束日期，然后检查月份标题溢出范围以查看该值是否介于这些日期之间，如果是，则将该值乘以 1 或 0 以尝试将不相关的值归零。

当我尝试根据行的 xlookup 结果检查列并将其应用于交点时，这就是当前发生的情况。

下面是一个标记表，可以帮助您了解基础知识。

我的任务过滤公式：=FILTER(A5:A16,MOD(A5:A16,1)<>0)

我的日期标题公式：=DATE(YEAR(MIN(B5:B16)),SEQUENCE(1,DATEDIF(MIN(B5:B16),MAX(C5:C16),"M")+1,MONTH(MIN(B5:B16)),1),1)

我有一个数组，其中包含几列溢出范围。我需要使用每一列执行多条件 xlookup（或类似的公式/结果），以提供结果的溢出范围。我使用 sumifs 来获取结果，但当单元格为空时，我希望它保持为空，而当单元格为空时，sumifs 结果给出 0%。

这个的实际应用将放入一个 LET 公式中，并且 xlookup 中的每个溢出都是一个 choosecols 输出。

=XLOOKUP(1,($C$3#=$C$12:$C$15)*($D$3#=D12:D15),$E$12:$E$15,"",0)

=SUMIFS($E$12:$E$15,$C$12:$C$15,C3#,$D$12:$D$15,D3#)

图中边框表示溢出阵列。预期结果是手动输入的。

总之，我的目标是获得溢出公式的摘要输出，该公式生成前 x 行，但最后一行对所有其他行进行分组，以便总数将与数据源相关联。（第一张图显示了它的工作原理，第二张图显示了我想要的效果。包含用于测试的 Markdown 表。）如果有更好的方式来表达标题，请告诉我，我会进行调整。

源表是通过针对数据透视表的溢出公式生成的。我这样做是为了在开始组合字段之前获得我想要关联的总数，而数据透视表没有一个干净的解决方案（要对前 x 个字段进行分组，您需要手动选择它们，但我需要它是动态的）。

在图像中，第一个表（左侧）是所有溢出公式。生成顶部的部门编号和侧面下方的 ID，以及值。

第二个表试图取前 5 名，然后将前 5 名之后的所有内容汇总到“其他”中；然而，正如您所看到的，这是获取 ID 6 的数据，而不是大于 6 的所有数据。我尝试了几种方法，但无法让它在执行此操作的同时立即对所有列求和。一种解决方案要求我复制附加列的公式，或者在数量较少时将其删除，因为这不是静态的。

约束：第一个表是根据溢出公式生成的，行数和列数不是静态的，因此第二个表需要考虑到这一点。对于示例，我使用了Sequence()标题和行，以及值的随机公式以获得一些数据。

该公式适用于除“其他”行之外的所有行：

=MAKEARRAY( ROWS(I2#), COLUMNS(K1#), LAMBDA(r,c,SUM(C2#*--(A2#=INDEX(I2#,r))*--(C1#=INDEX(K1#,1,c)) )))

我尝试将 K2 中的公式修改为下面的结果，但结果是#N/A：

=MAKEARRAY( ROWS(I2#), COLUMNS(K1#), LAMBDA(r,c,SUM(C2#*--IF(I2#>5,(A2#>=INDEX(I2#,r)),(A2#=INDEX(I2#,r)))*--(C1#=INDEX(K1#,1,c)) )))

这是所需的输出：

（Markdown 用于测试）

这是帮助我创建第一个公式的问题

我有一张工作表，其中部门位于顶部 (H43)，并且我使用排序、唯一和筛选嵌套公式从命名表中提取 3 列。过滤器查看该部门并提供所有 ID、员工姓名和头衔。我需要的附加列也在该表中，但不在相邻列“状态”中。我需要的列的列号依次为 7、8、9、5。

我想尽量减少拖动公式的维护，所以我希望有两件事之一：我可以使用溢出公式来拉动额外的非相邻列，或者进行多条件查找（因为这里有重复的 ID）引入就业状态并使其在泄漏时自动填充。

_Pop 标头是，目前我正在拉最后 3 个，但想要员工状态。最后 3 列正在从第一组列中提取数据，但状态很好，所以我不想移动它，或不必要地重复它。

我尝试过的公式：

溢出：

此公式可以很好地提取最后 3 列，但不能提取附加状态列

=SORT( UNIQUE( FILTER( _Pop[[EmployeeID]:[Title]], _Pop[department]='Annualized Hours (2)'!$H$43)),2,1)

这个公式给出了奇怪的行为

=SORT( UNIQUE( FILTER( _Pop, _Pop[department]='Annualized Hours (2)'!$H$43),{7,8,9,5}),2,1)

这次尝试给出了#Value！错误。

=SORT(UNIQUE(FILTER(INDEX(_Pop, , {7,8,9,5}), _Pop[department]='Annualized Hours (2)'!$H$43)),2,1) =SORT(UNIQUE(FILTER(INDEX(_Pop[#Data], , {7,8,9,5}), _Pop[department]='Annualized Hours (2)'!$H$43)),2,1)

这给出了列，但不是按照我需要的顺序：

=SORT(UNIQUE(FILTER(FILTER(_Pop, _Pop[department]='Annualized Hours (2)'!$H$43), (COLUMN(_Pop)=COLUMN(INDEX(_Pop,,7)))+(COLUMN(_Pop)=COLUMN(INDEX(_Pop,,8)))+(COLUMN(_Pop)=COLUMN(INDEX(_Pop,,9)))+(COLUMN(_Pop)=COLUMN(INDEX(_Pop,,5)))),FALSE,FALSE),3,1,FALSE)

在 A1:D1 中创建助手，以防它不喜欢这些常量。没有运气

=INDEX(_Pop,MATCH('Annualized Hours (2)'!$H$43,_Pop[department],0),MATCH(COLUMN(A1:D1),COLUMN(INDEX(_Pop,,{7,8,9,5})),0))

查找：

=XLOOKUP(INDEX(I44#,,1), MATCH(TRUE,($H$43=_Pop[department])*(INDEX(I44#,,1)=_Pop[EmployeeID]),0),_Pop[Employee Status],0,0)