好吧，有一個公式問題。我需要列出某個買家的所有餘數大於零的採購訂單行。
我需要使用買家儀表板資料中的資料填充買家的儀表板。注意：每個採購訂單可能有一行或多行需要依序列出，並且兩張表都在一個工作簿中。
買家儀表板：
買家儀表板數據：
以下是我嘗試過的方法：
=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()as ，這將列出您使用參數指定的任何列的適當值。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。 ，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() 中使用它，並將整個內容包裝在 an 中，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$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，並填入。
我希望這個幫助能祝你好運。