☰

我想使用带有下拉列表的 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 Channelas命名为Channeltbl
  • Data接下来，从Tab --> Get & Transform Data--> Get Data--> From Other Sources-->打开一个空白查询Blank Query
  • 上面的命令可以Power Query打开窗口，现在从HomeTab --> Advanced Editor--> 删除您看到的任何内容并粘贴以下内容M-Code，然后按Done

• 对于输出：

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具有所需输出的，而后者将提示一个窗口询问您将结果放在何处。

相关内容