excel - 如何创建基于多个条件的过滤下拉列表

Question

在下面日程 图片我正在尝试创建 下拉列表 在“腺体(A)”栏中。我希望根据“核心/对”、“尺寸 mm”和“电缆类型”中的数据过滤该列表，而不是仅仅创建所有可用“腺体”的列表。对于此示例，我们将使用“3c 16 BS5467，XLPE/SWA/PVC”。
日程

要确定列表的过滤器，需要考虑所选电缆的“ID Ø (mm)”和“OD Ø (mm)”，请参阅 电缆 下图。如您所见，我们使用的电缆的“ID”为 。 15.5 和 的“OD” 20.35 .
电缆

终于在下面看到了腺体 如图，上面的“ID”需要在“INNER MIN/MAX”之内，“OD”需要在“OUTER MIN/MAX”之内。
腺体

所以回到第 4 行的“GLAND (A)”列中的第一个图像，下拉列表应该被过滤并且只显示连接值:

151/RAC/B/M25

501/453/UNIV/B/M25

ICG/653/UNIV/B/M25

在两个单独的公式中，我设法 查找 只是基于电缆类型的“OD”:

=VLOOKUP(B4&C4&E4,'Cables'!A$2:H$169,8,FALSE)

然后根据检索到的值 查找 “OUTER MIN/MAX”中的“GLAND SIZE”:

=LOOKUP(2,1/((F4>='Glands'!E$3:E$9 + 1)*(F4<='Glands'!F$3:F$9 - 1)),'Glands'!B$3:B$9)

问题是我不知道如何检查“ID”以及检索连接的单元格(“GLAND TYPE”和“GLAND SIZE”)，然后将它们设为 。数据验证下拉列表 .
对此的任何帮助将不胜感激。
谢谢

Answer 1

好的，这将很难解释。我将尽我所能。也许如果我们把它包在一张专用的纸上，我们就不会出错。
阶段 1:创建一个新工作表。
创建一个新工作表并将其命名为“计算”。我们会把大部分东西放在这里。首先，我们在单元格 A1 中键​​入“Schedule 中的选定行”。
阶段 2:确定选择的电缆编号。
由于我们在计划表中有多个电缆条目，我们将需要多个可能的空地列表。为每个车道创建一个专用列表或限制用户自由是不切实际的。因此，我们需要知道用户在 Schedule 表中选择了哪一行。我们必须使用 VBA。右键单击计划表名称标签，然后单击“查看代码”。将此代码复制粘贴到出现的窗口中:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    'Filling the cell A2 in the sheet Calculation with the row number of the selected _
    cell in the scheet Schedule.
    Sheets("Calculations").Range("A2").Value = ActiveCell.Row
    
    'Preventing multiple selection in the F column of the sheet Schedule.
    If Not Application.Intersect(Target, Range("F:F")) Is Nothing Then
        Target.Resize(1, 1).Select
    End If
    
End Sub

此代码将在工作表计算的单元格 A2 中报告在工作表计划中实际选择的行号。每次更改选择时，值都会更改。它还可以防止在计划表中选择 F 列的多行(将放置 Glades 下拉列表的列)。您可以通过更改计划表中的选择并查看计算表中的结果来测试代码。
阶段 3:确定选择的电缆类型及其 ID/OD。
在计算表中，在范围 B1 中键入“选定电缆”。在范围 B2 中键入以下公式:

=INDEX(Schedule!$A:$F,Calculations!$A$2,2)&INDEX(Schedule!$A:$F,Calculations!$A$2,3)&INDEX(Schedule!$A:$F,Calculations!$A$2,5)

这个公式重构了我们将在 Cables 表的 LOOKUP 列中搜索的名称。它是一系列 INDEX 函数，并不复杂。
现在我们知道要查找什么，我们可以提取其 ID/OD。在单元格 C1 中键入“ID”，在单元格 D1 中键入“OD”。在单元格 C2 中键入以下公式:

=VLOOKUP($B$2,Cables!$A:$H,7,FALSE)

在单元格 D2 中键入以下公式:

=VLOOKUP($B$2,Cables!$A:$H,8,FALSE)

这些公式将在电缆表中搜索电缆列表并提取给定电缆的 ID/OD。
阶段 4:创建过滤列表。
您的腺体列表的第一个腺体位于第三行。因此，为了更容易交叉检查数据，我们将相应地放置我们的公式。在工作表计算中，在单元格 E2 中键入“列出阶段 1”。在单元格 E3 中键入以下公式:

=IF(AND(C$2>=Glands!C3,C$2<=Glands!D3,D$2>=Glands!E3,D$2<=Glands!F3),ROW(),"")

将它一直向下拖动，直到它覆盖腺体表中腺体列表的相同行数。这个公式将“突出显示”我们正在寻找的腺体(如果有的话)。此时列表很长，未排序，并且可能有很多空白单元格。我们需要对其进行排序。在单元格 F2 中键入“列表阶段 2”。在单元格 F3 中键入以下公式:

=IF.ERROR(SMALL(E:E,ROW()-ROW(F$2)),"")

像上一个一样向下拖动这个。现在我们有了一个紧凑的数字列表。我们需要把它们翻译成林间空地的名字。在单元格 G2 中键入“过滤的腺体列表”。在单元格 G3 中键入以下公式:

=IF.ERRORE(INDEX(Glands!A:B,F3,1)&"/"&INDEX(Glands!A:B,F3,2),"")

像以前一样再次将其向下拖动。我们有我们的 list 。
阶段 5:命名列表。
我们需要创建一个对列表的动态引用来删除所有的空白单元格。定义一个名为 Gland_Filtered_List 的新名称，引用此公式:

=INDIRECT("Calculations!$G$3:G" & ROWS(Calculations!$G$3:$G$1048576) -COUNT.BLANK(Calculations!$G$3:$G$1048576)+2)

阶段 6:插入数据验证。
在计划表中，使用列表模式和 Gland_Filtered_List 作为来源为腺列创建数据验证。
这应该够了吧。现在我要快点上类，所以我无法检查解释。一切都应该井井有条。试试这个，问任何问题。我稍后会回答。