☰

如何将 Excel 计算转换为溢出动态数组

tag-icon • tag-icon microsoft-excel worksheet-function
如何将 Excel 计算转换为溢出动态数组

如果不描述完整的问题，我甚至不确定如何询问我在寻找什么。

如果我有这张表格（如果有人想要的话，底部有 CSV 表格）：

在此处输入图片描述 F2：=SORT(UNIQUE(FILTER(C:C,ISNUMBER(C:C))))

G2级：=LET(x,F2#,SUMIFS(B:B,C:C,x,D:D,1))

I2：=LET(x,F2#,SUMIFS(B:B,C:C,x,D:D,2))

一个完整的薪资期为 4 小时；一天最多可以有两个薪资期。但是，如果只使用了几个小时，则可以将它们逐步合并到另一个任意天，并在薪资期总计为 4 小时时付款。

我可以让 H2 按照以下方式工作：

=IF(--G2=0,"",LET(x,FILTER(E:E,(C:C=F2)*(D:D=1)),IF(AND(EXACT(x,@x)),@x,"inconsistent")))

本质上，它确保给定支付日期/支付期内的所有资金都来自同一个资金池，即基本工资池或退休金池。

我可以很好地复制/粘贴它。但是，有没有办法重新制定 H2 以产生与 F2# 和 G2# 长度相同的动态溢出范围？

(如果有人好奇，这就是美国军事预备役薪酬的运作方式，这是我用来确保我所声称的内容是诚实的追踪器。)

上表的CSV格式：

Date Performed,Hours,Pay Date,Pay Period,Pay Type,Verification,Period 1,Pay Type,Period 2,Pay Type
1-Oct-21,1.5,24-Sep-22,1,base pay,1-May-22,4,inconsistent,4,base pay
29-Oct-21,1.5,24-Sep-22,1,retirement,2-May-22,2,base pay,,
29-Nov-21,1.5,24-Sep-22,2,retirement,19-May-22,4,base pay,4,base pay
23-Dec-21,1,24-Sep-22,1,retirement,22-Sep-22,,,2.5,retirement
23-Dec-21,2,24-Sep-22,2,retirement,23-Sep-22,4,retirement,2,retirement
27-Jan-22,0.5,24-Sep-22,2,retirement,24-Sep-22,4,inconsistent,4,retirement
2-Feb-22,0.5,23-Sep-22,2,retirement,25-Sep-22,,,3.5,retirement
22-Feb-22,2,23-Sep-22,,retirement,,,,,
27-Feb-22,2.5,23-Sep-22,1,retirement,,,,,
28-Feb-22,0.5,23-Sep-22,2,retirement,,,,,
2-Mar-22,0.5,23-Sep-22,1,retirement,,,,,
3-Mar-22,1,23-Sep-22,1,retirement,,,,,
4-Mar-22,0.5,,2,retirement,,,,,
6-Mar-22,1,23-Sep-22,2,retirement,,,,,
6-Mar-22,2,1-May-22,1,,,,,,
7-Mar-22,2,1-May-22,1,base pay,,,,,
8-Mar-22,,2-May-22,1,base pay,,,,,
8-Mar-22,1.5,25-Sep-22,2,retirement,,,,,
9-Mar-22,1,22-Sep-22,2,retirement,,,,,
10-Mar-22,1,1-May-22,2,base pay,,,,,
13-Mar-22,1.5,1-May-22,2,base pay,,,,,
14-Mar-22,1.5,1-May-22,2,base pay,,,,,
15-Mar-22,1,2-May-22,1,base pay,,,,,
17-Mar-22,1,2-May-22,1,base pay,,,,,
17-Mar-22,2,25-Sep-22,2,retirement,,,,,
18-Mar-22,1,22-Sep-22,2,retirement,,,,,
24-May-22,0.5,22-Sep-22,2,retirement,,,,,
19-May-22,4,19-May-22,1,base pay,,,,,
,,,,,,,,,
19-May-22,4,19-May-22,2,base pay,,,,,

编辑：最初创建时在顶部有一个手动内联表；一旦我有足够的声誉，我就用屏幕截图替换它。后来根据@Jos Woolley的建议，更新为包含空白行

答案1

我不确定为什么还有这么多人继续误解哪些函数可以传递任意大小的范围（例如整个列），而不会损害计算性能。以下构造的一个实例

FILTER(E:E,(C:C=F2)*(D:D=1))

例如，被迫迭代超过两百万个细胞，因为该函数不采用对这些范围内最后使用的单元格的隐式检测。

尝试在第一阶段进行以下设置：

=LET(ζ,MMULT(GESTEP(COUNTIFS(C2:C30,F2#,D2:D30,1,E2:E30,TRANSPOSE(UNIQUE(E2:E30))),1),SEQUENCE(2,,,0)),IF(ζ=0,"",IF(ζ=1,INDEX(E2:E30,MATCH(F2#&"|1",C2:C30&"|"&D2:D30,0)),"Inconsistent")))

保持传递范围在合理的长度。

那个部分

"|1"

可以替换为

"|2"

对于等效的第 2 期设置，如果需要，可在上述公式中添加错误子句（例如，对于 5 月 2 日，没有第 2 期工资单条目）。

答案2

如果您不需要使用函数，您也可以使用数据透视表。

转到插入 - 数据透视表 - 确保选中“添加到数据模型”，然后单击确定。

在 Power Pivot 选项卡 - 度量 - 插入度量并使用以下公式创建一个：
=if (DISTINCTCOUNT([Pay Type])=1,DISTINCT(Table1[Pay Type]),"inconsistent")

在此处输入图片描述

答案3

我想我已经知道了。

如果你只想要最终答案，这里是：

=IF(G2#=0,"",LET(payPeriod,1,payTypes,TRANSPOSE(UNIQUE(FILTER(E:E,(E:E>0)*(E:E<>E1)))),occurencesPerDate,COUNTIFS(C:C,F2#,D:D,payPeriod),resultIfConsistent,INDEX(E:E,MATCH(F2#,(C:C)*(D:D=payPeriod),0)),LET(payMatrix,COUNTIFS(C:C,F2#,D:D,payPeriod,E:E,payTypes),LET(matrixRowCount,MMULT(payMatrix,SEQUENCE(COUNTA(payTypes),1,1,0)),nonBlanksPerMatrixRow,MMULT(--(payMatrix<>0),SEQUENCE(COUNTA(payTypes),1,1,0)),LET(isConsistent,(nonBlanksPerMatrixRow=1)*(occurencesPerDate=matrixRowCount),IF(isConsistent,resultIfConsistent,"inconsistent"))))))

首先，我认为@MátéJuhász 的观点是正确的，使用数据透视表可能是最直接的方法。我不得不做一些研究，因为我以前也为此苦苦挣扎，但据我所知，我认为只有当源数据集的所有列和行都有一些数据时，数据透视表才有效。我需要支持完全空白的行（在他提出这个建议之前我没有提到这一点），否则我可能会放弃我正在做的事情并尝试弄清楚数据透视表。

其次，对于纯公式化解决方案，@JosWoolley 已经非常接近了，它并不完全符合我所考虑的所有边缘情况，但他指出我最初并没有指定边缘情况。

我已经更新了问题以（希望）展示这些。我从@JosWoolley 那里获得了很大的启发，并使用了一些技巧，尤其是MMULT()使它工作的方法和LET()使它更快、更易读的方法。

整个方程很复杂，但可以分解成多个可管理的部分。该LET()函数极大地帮助了使各部分更易读（呃，无论如何“易于理解”）。

首先，需要确保我们只使用有结果的结果。对于 H 列，这是工资期 1（J 列将 G2# 更改为 I2#，将 payPeriod 更改为 2，否则之后的所有内容在工资期评估之间都是相同的）

=IF(G2#=0,"",LET(payPeriod,1

然后我们需要计算工作表上使用的薪酬类型。后面的使用都需要将其放在水平行中，下一个计算步骤将其用作研发期间的列标题：

,payTypes,TRANSPOSE(UNIQUE(FILTER(E:E,(E:E>0)*(E:E<>E1))))

在此处输入图片描述

然后我们计算每个付款日发生的次数：

,occurencesPerDate,COUNTIFS(C:C,F2#,D:D,payPeriod)

在此处输入图片描述

然后我们计算薪酬类型，假设一切都完全一致。请注意，原则上，错误和不准确的结果是可以接受的，我们实际使用的部分应该是正确的。

,resultIfConsistent,INDEX(E:E,MATCH(F2#,(C:C)*(D:D=payPeriod),0))

在此处输入图片描述

接下来，我们生成一个矩阵，计算每个薪资类型在给定薪资日期的使用次数。出于研发目的，将计算的这一部分直接放在 payTypes 下方很有用；它将根据需要水平展开，并在视觉上对齐，以将 payTypes 用作列标题。

,LET(payMatrix,COUNTIFS(C:C,F2#,D:D,payPeriod,E:E,payTypes)

在此处输入图片描述

下一步很有趣。我们将 payMatrix 折叠成一个单列数组，其中每一行是 payMatrix 中每行所有条目的总和。这是通过将 payMatrix 数组与另一个全 1 数组进行矩阵乘法来实现的，该数组的宽度为 1 列，行数与 PayTypes 中的列数相同。

,LET(matrixRowCount,MMULT(payMatrix,SEQUENCE(COLUMNS(payTypes),1,1,0))

在此处输入图片描述

然后，计算每行非空白单元格的数量（稍后我们将测试以确保每行只有一个非空白单元格）：

,nonBlanksPerMatrixRow,MMULT(--(payMatrix<>0),SEQUENCE(COUNTA(payTypes),1,1,0))

在此处输入图片描述

对于最后的计算，我们生成一个 1 和 0 的表作为一致性测试：

,LET(isConsistent,(nonBlanksPerMatrixRow=1)*(occurencesPerDate=matrixRowCount)

在此处输入图片描述

最后一步总体来说相当简单。如果没有 payDate+payPeriod，则返回“”（注意，这个测试是在一开始就进行的）如果 payDate+payPeriod 没有完全相同的 payType，则返回“不一致”如果两个测试都通过，则返回 payDate+payPeriod 使用的实际 payType

,IF(isConsistent,resultIfConsistent,"inconsistent"))))))

@JosWoolley 指出，这非常低效，因为我测试的是整个百万行列（C:C 等），而不是我需要评估的部分。我机器上的性能分析显示它在几百行上运行得足够快。我怀疑，只需执行一次，许多列就可以在许多情况下限制行数，这可能是第一步。FILTER(C:E,(C:C<>"")*(C:C<>C1)*((D:D=1)+D:D=2)))如果我发现我需要进一步优化，那很可能是我的第一步，但目前这个公式已经太复杂了。

此外，我非常确信整个公式的架构过于复杂。如果能去掉不必要的部分，甚至采用完全不同的方法，效果会更好。

答案4

我找到了一种更简单的方法来解决这个问题，这表明我最初的方法过于复杂，并且还结合了@JosWolley 的建议，以尽量减少每次重新计算评估数百万行。

对于工资期 1 的答案是：

=LET(table,FILTER(B:E,(B:B>0)*(C:C>0)*(D:D=1)),BYROW(F2#,LAMBDA(row,LET(payTypes,INDEX(FILTER(table,(INDEX(table,,2)=row)),,4),consistency,AND(EXACT(payTypes,INDEX(payTypes,1))),IF(ISERR(consistency),"",IF(consistency,INDEX(payTypes,1),"inconsistent"))))))

第二个工资期只需更新(D:D=1)至(D:D=2)，其他方面相同。

以下是其结构再现：

=LET(table,FILTER(B:E,(B:B>0)*(C:C>0)*(D:D=1)),
    BYROW(F2#,LAMBDA(row,
        LET(payTypes,INDEX(FILTER(table,(INDEX(table,,2)=row)),,4),
            consistency,AND(EXACT(payTypes,INDEX(payTypes,1))),
                IF(ISERR(consistency),
                    "",
                    IF(consistency,
                        INDEX(payTypes,1),
                        "inconsistent"
                    )
                )
            )
        )
    ))
)

并以图形方式再现：

在此处输入图片描述

相关内容