☰

我想使用帶有下拉列表的 sumifs 函數將資料從一張紙填充到另一張紙

tag-icon • tag-icon microsoft-excel dropdownlist
我想使用帶有下拉列表的 sumifs 函數將資料從一張紙填充到另一張紙

我在圖像一中有數據，我希望使用下拉列表在圖像二中可見，其中結果根據從下拉列表中選擇的商店而變化。我嘗試了 sumifs，但它只顯示商店 1 的數據，而不顯示商店 2 的數據。

在此輸入影像描述

有資料的圖像 1

在此輸入影像描述

圖 2 與公式

在此輸入影像描述

圖三shop2數據不可見

答案1

這是一種方法：

在此輸入影像描述

• 在儲存格中使用的公式I4

=VLOOKUP($H4,INDIRECT($I$1),MATCH(I$3,HEADERS,0),0)
  • 定義一些範圍以使公式相對於下拉清單相應地工作。
  • 範圍=$B$4:$F$7定義為SHOP1
  • 範圍=$B$11:$F$14定義為SHOP2
  • 因為，每個標題保持相同，然後為SHOPS其中一個定義SHOPSHEADERS=$B$3:$F$3
  • 接下來，使用VLOOKUP()如上圖所示的公式，向下填充，向右填充！

但是，如果您打算使用，SUMIFS()建議您使用SUMPRODUCT()或SUM()基於您的Excel Version

在此輸入影像描述

• 在儲存格中使用的公式I4

=SUMPRODUCT(INDIRECT($I$1)*(Criteria_Range=$H4)*(I$3=HEADERS))
  • 對定義的名稱進行一些更改。依照以下規定：

  1. 範圍=$C$4:$F$7定義為SHOP1

  2. 範圍=$C$11:$F$14定義為SHOP2

  3. 範圍=$C$3:$F$3定義為HEADERS

  4. 範圍=$B$4:$B$7定義為Criteria_Range

  • 現在，使用上面的公式，向下填充並向右填充！

也許使用SUMIFS()定義的命名範圍與使用第一種方法保持相同VLOOKUP()

在此輸入影像描述

=SUMIFS(INDEX(INDIRECT($I$1),,MATCH(I$3,HEADERS,0)),INDEX(INDIRECT($I$1),,1),$H4)

• 如果您有權訪問MS365，請使用以下公式，它是一個會溢出整個陣列的公式：

=LET(
     _Data, VSTACK(EXPAND(SHOP1,,6,"SHOP1"),EXPAND(SHOP2,,6,"SHOP2")),
     _Filtered, FILTER(_Data, TAKE(_Data,,-1)=I1),
     MAKEARRAY(ROWS(H22:H24), COLUMNS(I21:L21), LAMBDA(r,c,
     VLOOKUP(INDEX(H22:H24,r),_Filtered,XMATCH(INDEX(I21:L21,c),HEADERS),0))))

警告：使用INDIRECT()函數並不具有暗示性，因為它Volatile本質上會減慢工作功能的速度，Excel並且每當任何打開的工作簿發生更改時，它都會不斷重新計算單元格。您應該對資料結構進行一些更改，以使您的工作變得輕鬆，避免使用易失性函數。

這也可以使用Windows 和 MAC 上的POWER QUERY可用版本來實現。Excel 2010+

在此輸入影像描述

  • 首先將來源範圍轉換為表格並相應命名，在本例中我將其命名為 for SHOP1as SHOP_1、 for SHOP2asSHOP_2和 for ChannelasChanneltbl
  • Data接下來，從Tab --> Get & Transform Data--> Get Data--> From Other Sources-->開啟一個空白查詢Blank Query
  • 上面的內容讓Power Query視窗打開，現在從HomeTab --> --> 並透過刪除您看到的任何內容來Advanced Editor貼上以下內容，然後按M-CodeDone

• 對於輸出：

let
    Source = Excel.CurrentWorkbook(),
    #"Filtered Rows" = Table.SelectRows(Source, each Text.StartsWith([Name], "SHOP")),
    #"Expanded Content" = Table.ExpandTableColumn(#"Filtered Rows", "Content", {"Expense Type", "January", "February", "March", "April"}, {"Expense Type", "January", "February", "March", "April"}),
    #"Filtered Rows1" = Table.SelectRows(#"Expanded Content", each ([Name] = CHANNEL)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows1",{"Name"}),
    #"Filtered Rows2" = Table.SelectRows(#"Removed Columns", each [Expense Type] <> "Total Budget")
in
    #"Filtered Rows2"

• 對於頻道：

let
    Source = Excel.CurrentWorkbook(){[Name="Channeltbl"]}[Content],
    CHANNEL = Source{0}[CHANNEL]
in
    CHANNEL
  • 請記住，您需要將以上內容貼到兩個不同的空白查詢中。此外，CHANNEL查詢將是一個連接，而另一個需要在工作表中輸入。
  • 最後，要將其導入回Excel--> 單擊Close & Load或Close & Load To--> 單擊的第一個將創建一個New Sheet具有所需輸出的文件，而後者將提示一個窗口，詢問您將結果放在哪裡。

相關內容