如果如果则索引此或索引匹配和匹配?

如果如果则索引此或索引匹配和匹配?

好的,我遇到了一个公式问题。我需要列出某个买家所有余数大于零的采购订单行。

我需要使用来自买方仪表板数据的数据填充买方仪表板。注意:每个采购订单可能有一行或多行需要按顺序列出,并且两张表都在一个工作簿中。

买家仪表板:
买家仪表板

买家的仪表板数据:
买家仪表板数据

以下是我尝试过的方法: =IFERROR(INDEX(Download!$B$2:$B$50000,MATCH(TRUE,$B$3,Download!$C$2:$C$50000>0,0)),"")

=IFERROR(INDEX(Download!$B$2:$B$50000,MATCH($B$3&>0,Download!C2:C50000&DownloadG2:G50000,0)),"")

=IF(ISERROR(SMALL(IF(IF(ISERROR(SEARCH($B$3,$C$2:$C$50000)),FALSE,TRUE),ROW($C$2:$C$50000)),ROW($B$2:$B$50000))),"",INDEX($A$2:$K$50000,SMALL(IF(IF(ISERROR(SEARCH($B$3,$C$2:$C$50000)),FALSE,TRUE),ROW($$C$2:$C$50000)),ROW($B$2:B50000)),3))

=IF(ISERROR(INDEX(Download!A2:K50000,SMALL(IF(Download!C2:C50000=$B$3,ROW(Download!B2:B50000)),ROW(1:1))-1,3)),"",INDEX(Download!A2:K50000,SMALL(IF(Download!C2:C50000=$B$3,ROW(Download!B2:B50000)),ROW(1:1))-1,3))

有任何想法吗????

答案1

这是在 Excel 中制作动态列表的一种方法。

假设您有一个包含符合条件的行号的数组TRUE(即买方代码 = JDH 且剩余 > 0)。然后,您可以将该数组提供给一个SMALL()函数,该函数会在填充时列出行号。最后,您可以在函数中将SMALL()用作,这将列出您使用参数指定的任何列的适当值。row_numsINDEX()column_num

因此,让我们首先获取行号数组。为简单起见,我假设您的数据表只有 25 行,并且仪表板与数据位于同一张表上。此外,我使用的是买方代码 ELN,它位于单元格 B30 中。

TRUE/FALSE首先,我们将计算符合您的标准的值数组:

($C$1:$C$25=$B$30)*($G$1:$G$25>0)

第一个表达式给出了一个值数组TRUE/FALSE,其中买方代码(C 列)= ELN,第二个表达式给出了一个TRUE/FALSE值数组,其中剩余(G 列)> 0。在涉及逻辑值的算术运算中,TRUEFALSE被转换为 1 和 0,因此这个乘法给出了一个由 1 和 0 组成的数组,其中 1 位于您的标准均为 的位置TRUE

现在我们需要将此数组转换为符合条件的行号列表TRUE。如果我们将一个连续数字数组除以这个由 1 和 0 组成的数组,我们将得到一个行号数组,其中有 1 的地方就是行号,而有#DIV/0!0 的地方就是错误。

ROW($1:$25)/(($C$1:$C$25=$B$30)*($G$1:$G$25>0))

接下来,SMALL()我们将使用AGGREGATE(),而不是 ,它的功能与 (function=15) 相同SMALL(),只是它有一个忽略错误的选项 (option=6)。还AGGREGATE()可以处理数组,而不必使用 进行输入CTRLShiftEnter

因此,在下面的公式中,AGGREGATE()忽略#DIV/0!错误,并给出一个仅包含符合您的条件的行号的数组:

AGGREGATE(15,6,ROW($1:$25)/(($C$1:$C$25=$B$30)*($G$1:$G$25>0)),ROW()-33)

我在第 34 行有这个公式,因此“-33”使函数从 1 开始并列出向下填充的行号。更改此参数以适合您的情况。

现在我们可以在 INDEX() 中使用它,并将整个内容包装在一个中,IFERROR()以便在INDEX()用完时插入空白row_nums,因为它已填满。此公式从 B34 向下填充:

=IFERROR(INDEX($A$1:G$25,AGGREGATE(15,6,ROW($1:$25)/(($C$1:$C$25=$B$30)*($G$1:$G$25>0)),ROW()-33),MATCH(B$32,$A$1:$G$1,0)),"")

给出如下所示的结果。

在此处输入图片描述

对于买方 ELN,仅第 5 行和第 11 行剩余为 0,公式列出了除这些行之外的所有行。

请注意,我引用了仪表板标题,通过使用作为MATCH()给出。当公式填充时,这适用于采购订单编号和说明,但仪表板中的其他标题与数据表中的标题不同。column_num'sB$32lookup_value

因此,对于其他列,我用B$32带引号的文本替换了单元格引用:“LINE_NBR”代表采购单行列,“QUANTITY”代表订购数量列,“REMAINING”代表收到数量列。最后一列涉及计算,因此公式实际上是:

=E34-IFERROR(INDEX($A$1:G$25,AGGREGATE(15,6,ROW($1:$25)/(($C$1:$C$25=$B$30)*($G$1:$G$25>0)),ROW()-33),MATCH("REMAINING",$A$1:$G$1,0)),"")

最后一列“所需”的计算方法如下:

=E34-F34,并向下填充。

我希望这个帮助能祝你好运。

相关内容