情况：
假设我有数百万个数据点，其中包含随时间变化的不同信息（即国家、产品等），我想根据某些用户输入对这些数据点进行过滤。示例数据存储在范围内A1:I21，用户输入收集在范围内L1:M5，国家在内L1，产品在内L2，单位在内L3，市场在内L4，时间范围在L5（开始）和M5（结束），例如基于数据验证列表。
目的：
最终结果应该是一个数组，该数组捕获所有产品随时间变化的值。重要的是，它还捕获了特定国家/地区不存在的产品。鉴于数据点数量庞大，它应该是一种非常高效的方法（理想情况下是非 VBA），假设用户输入频繁更改。下面描述的方法有效，但 Excel 变得非常慢，计算需要相当长的时间。有没有关于更优化方法的建议？
可能的方法：
一种很诱人的方法是使用该FILTER函数，但这并不奏效，因为国家 B、产品 3 的输出不会用 #N/A 捕获。
对于以下两种方法（即XLOOKUP和INDEX），我在单元格中使用以下公式O2来捕获所需的日期范围，并将唯一的产品（1、2、3）P1:R1分别放在范围内。
=TRANSPOSE(FILTER($E$1:$I$1,($E$1:$I$1>=$L$5)*($E$1:$I$1<=$M$5)))
XLOOKUP 方法：
一种可能的方法是使用嵌套XLOOKUP（范围以下的公式P2:R2，因为公式会溢出）。首先，我寻找正确的时间段，然后过滤其他用户输入。
=XLOOKUP($O$2#,$A$1:$I$1,
XLOOKUP(1,($B$1:$B$21=P$1)*($A$1:$A$21=$L$1)*($C$1:$C$21=$L$3)*($D$1:$D$21=$L$4),$A$1:$I$21,
NA(),0,1))
INDEX/MATCH 方法：
另一种可能的方法是使用INDEX/MATCH（范围以下的公式P2:R4，因为公式不会溢出）。该过程与 XLOOKUP 方法非常相似。
=INDEX(INDEX($A$1:$I$21,,MATCH($O2,$A$1:$I$1,0)),
MATCH(1, ($B$1:$B$21=P$1)*($A$1:$A$21=$L$1)*($C$1:$C$21=$L$3)*($D$1:$D$21=$L$4), 0))
数据：
country product unit market 2000 2005 2010 2015 2020
A 1 EUR local 137 982 557 574 423
A 1 EUR global 866 133 179 139 939
A 1 % local 0.5 0.14 0.36 0.41 0.5
A 1 % global 0.9 0.91 0.6 0.98 0.03
A 2 EUR local 722 797 115 599 658
A 2 EUR global 869 620 165 724 171
A 2 % local 0.68 0.2 0.09 0.07 0.78
A 2 % global 0.08 0.83 0.39 0.05 0.55
A 3 EUR local 235 979 593 273 510
A 3 EUR global 302 244 741 850 569
A 3 % local 0.03 0.31 0.44 0.54 0.57
A 3 % global 0.97 0.17 0.04 0.2 0.62
B 1 EUR local 526 864 822 330 668
B 1 EUR global 846 791 214 583 780
B 1 % local 0.56 0.86 0.32 0.78 0.18
B 1 % global 0.54 0.21 0.18 0.42 0.16
B 2 EUR local 222 353 831 595 161
B 2 EUR global 289 431 192 392 571
B 2 % local 0.14 0.77 0.1 0 0.44
B 2 % global 0.65 0.36 0.81 0.37 0.89
用户输入：
Country B
product 1
unit EUR
market global
time 2005 2015
期望输出：
1 2 3
2005 791 431 #N/A
2010 214 192 #N/A
2015 583 392 #N/A
Excel 工作表：
答案1
我必须做和你类似的事情，但数据量少得多，这很困难。我必须让客户以用户友好的方式选择一些过滤条件，然后会为他们过滤 3 个不同的工作表（我们假设客户自己不知道如何使用自动过滤器进行过滤）。
我现在找到的解决方案包括使用数据验证创建的几个列表、用于计算和其他内容的隐藏工作表以及包含相交方法的 VBA（如果您在工作表中选择“更改”事件，该方法将起作用）。在 VBA 内部：
'Event code, Target variable contains the cell or range that has been changed
Private Sub Worksheet_Change(ByVal Target As Range)
Dim xWs As Worksheet
On Error Resume Next
'Interesect method returns a range object of a rectangular intersection of two or more cell ranges
If Not Intersect(Target, Range("B2")) Is Nothing Then
'Select Case statment performs one out of several groups of statements based on a condition
Select Case Range("B2")
'Different texts in the cell ("Cases" apply different filters through macros
Case "-":
Set xWs = Worksheets("Sheet2")
xWs.Range("H1").AutoFilter Field:=8
Set xWs = Worksheets("Sheet3")
xWs.Range("H1").AutoFilter Field:=8
Set xWs = Worksheets("Sheet4")
xWs.Range("H1").AutoFilter Field:=8
Set xWa = Worksheets("Sheet5")
xWa.Range("H1").AutoFilter Field:=8
'If text in cell H1 is equal to "Yes" a macro is run
Case "1 circuito": macroname
'Stops Select Case statement
End Select
End If
以及一个过滤的宏示例（位于 VBA 中的模块文件夹中）：
Sub Filter_Example()
'Filters Sheet1 by X
Dim xWs As Worksheet
Application.Volatile
On Error Resume Next
Set xWs = Worksheets("Sheet1")
xWs.Range("H1").AutoFilter 8, "=1", Operator:=xlOr,
Criteria1:="=1"
End Sub
还有更多代码可以显示在特定时刻哪个过滤器处于活动状态，还有一个 force.calc 解决方案可以解决用户定义函数的问题（即使我们将它们声明为易失性函数）。我拥有的 force_recalc 解决方案可能不是您庞大数据集的好解决方案，但这不是我能决定的。无论如何，如果您需要这些代码片段，请索取，我会提供。我希望这会有所帮助，或者如果其他人建议更好的解决方案，我会很高兴听到。
答案2
我决定重新阅读这个问题，如果国家/产品/单位/市场都是唯一的（可以用作复合索引），那么您只需使用数据透视表即可。
如果国家/产品/单位/市场不唯一，您可以添加切片器或添加唯一索引以用作附加行参数
答案3
您可以使用以下公式：
=LET(ftr,FILTER(A:I,(A:A=L1)*(C:C=L3)*(D:D=L4)),vert,VSTACK(A1:I1,ftr),HSTACK(CHOOSECOLS(vert,2),FILTER(vert,(A1:I1>=L5)*(A1:I1<=M5))))
您还可以包含 TRANSPOSE 来改变结果的方向。