☰

使用兩個表中的多個條件計算總銷售額

tag-icon • tag-icon microsoft-excel worksheet-function
使用兩個表中的多個條件計算總銷售額

我目前正在與表格1並嘗試計算總銷售額[C 欄]具有相同的產品ID以及匹配的庫存單位和銷售單位表2。我嘗試使用 SUMIFS 公式，如下所示：

=SUMIFS(Table2!E:E, Table2!A:A, Table1!A1, Table2!C:C, Table2!D:D)

然而，它似乎沒有給我想要的結果。有人可以幫我解決這個問題嗎？我非常感謝你的幫助。謝謝你！

表 1（預期結果）：

表格1

表2：

表2

答案1

如果我理解正確，那麼這SUMIFS()不是適合所需輸出的正確函數，而是使用以下內容，如螢幕截圖所示：

在此輸入影像描述

• 儲存格中使用的公式C2並Sheet Named Table1填寫：

=LET(
     _extract, FILTER(Table2!C$2:E$19,(Table2!A$2:A$19=Table1!A2)*(Table1!B2=Table2!B$2:B$19),""),
     SUM(--(INDEX(_extract,,1)=INDEX(_extract,,2))*TAKE(_extract,,-1)))
  • 使用函數提取標題為、且基於和 的FILTER()資料範圍Stock UnitSales UnitSalesIDName
  • 接下來，使用SUM()函數進行布林運算來比較上面提取的Sales Unit和Stock Unit數組，以求對應數組的乘積總和。

另外，如果您正在使用，MS365那麼您可以使用一個公式來溢出整個輸出，因此無需在此處填寫：

=MAP(A2:A7,B2:B7,LAMBDA(x,y,
  LET(z, FILTER(Table2!C:E,(Table2!A:A=x)*(Table2!B:B=y),""), 
  SUM(--(INDEX(z,,1)=INDEX(z,,2))*TAKE(z,,-1)))))
  • 使用MAP()函數迭代每個單元格值以執行自訂LAMBDA()計算。
  • 在LAMBDA()使用 aLET()傳遞一個函數以根據和FILTER()提取三個所需的列IDName
  • 接下來，在這裡對對應數組的乘積進行求和，將Stock Unit-->INDEX(z,,1)與返回的Sales Unit-->進行比較，然後將其乘以求和後以獲得所需的輸出。INDEX(z,,2)TRUEFALSESales

一個簡單的方法可以使用SUMIFS()Column Sales Unittake asCriteria Range和Stock UnitsColumn asCriteria但是，這取決於您的偏好：

在此輸入影像描述

=SUMPRODUCT(
    SUMIFS(
        Table2!E:E,
        Table2!A:A, Table1!A2,
        Table2!B:B, Table1!B2,
        Table2!D:D, UNIQUE(
            Table2!C:C
        )
    )
)

還有一種替代方法，但建議這樣做，它會降低 Excel 效率。

=SUM(FILTER(Table2!E:E,(IFNA(XMATCH(Table2!D:D,UNIQUE(Table2!C:C)),0))*(A2=Table2!A:A)*(Table2!B:B=Table1!B2),0))

相關內容