好的,我遇到了一个公式问题。我需要列出某个买家所有余数大于零的采购订单行。
我需要使用来自买方仪表板数据的数据填充买方仪表板。注意:每个采购订单可能有一行或多行需要按顺序列出,并且两张表都在一个工作簿中。
以下是我尝试过的方法:
=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_nums
INDEX()
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。在涉及逻辑值的算术运算中,TRUE
和FALSE
被转换为 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's
B$32
lookup_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
,并向下填充。
我希望这个帮助能祝你好运。