主页 / user-11820046

Mark S.'s questions

Martin Hope
Mark S.
Asked: 2024-08-03 05:06:29 +0800 CST
  • 5

我有一个数组，其中包含几列溢出范围。我需要使用每一列执行多条件 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#)

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

图像

excel
Martin Hope
Mark S.
Asked: 2024-05-14 23:28:09 +0800 CST
  • 6

总之，我的目标是获得溢出公式的摘要输出，该公式生成前 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 用于测试）

编号/部门 姓名 1 2 3 4 5
1 汤姆 2.00 3.25 4.50 5.75 7点
2 乔 2.75 4.00 5.25 6.50 7.75
3 克里斯汀 3.50 4.75 6.00 7.25 8.50
4 标记 4.25 5.50 6.75 8.00 9.25
5 本 5.00 6.25 7.50 8.75 10:00
6 简 5.75 7点 8.25 9.50 10.75
7 凯里 6.50 7.75 9.00 10.25 11.50
8 杰克 7.25 8.50 9.75 11点 12.25
9 玫瑰 8.00 9.25 10.50 11.75 13:00
10 米歇尔 8.75 10:00 11.25 12.50 13.75
11 迈克尔 9.50 10.75 12:00 13.25 14.50
12 保罗 10.25 11.50 12.75 14:00 15.25
13 何塞 11点 12.25 13.50 14.75 16:00
14 珊达 11.75 13:00 14.25 15.50 16.75
15 埃里克 12.50 13.75 15:00 16.25 17.50

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

excel
Martin Hope
Mark S.
Asked: 2024-01-27 00:14:59 +0800 CST
  • 5

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

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

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

部门 员工 身份证号(姓名) 办公室 员工状况 全职工作时间 员工ID 姓名 标题

我尝试过的公式：

溢出：

此公式可以很好地提取最后 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)

excel