☰

如何根据前一个单元格中的值显示 Excel 表（数据验证列表）-多个表

tag-icon • tag-icon microsoft-excel data-validation list
如何根据前一个单元格中的值显示 Excel 表（数据验证列表）-多个表

我在 Excel 中拥有多个包含各种相互关联的数据的表格（例如：Excel 中的表格）

我已经命名了所有表格（例如：表的名称）并且我已经从这些表中创建了列表，以便在需要时更轻松地在数据验证中调用它们（例如：表格列表）。

我想要实现的是一张表单，用户可以根据之前的选择继续选择选项。就像在这个特定案例中，他们需要先选择国家/地区（例如：国家选择），然后根据所选的国家，选择属于这个国家的食物（例如：特定国家的食物选择），最后根据所选的食物，选择更具体的先前所选的食物类型（例如：食物细节选择）。

我遇到的问题与最后一个选择有关。我可以用“ifs”循环它，但是当我有更多表格时（示例只有几个，实际场景中有更多表格），数据验证字段仅允许一定长度的文本，而所有“ifs”都超过了该长度，因此我无法在那里得到公式。

有没有办法创建一个适合数据验证的简短公式：

  1. 检查先前单元格中选择的内容
  2. 检查它属于哪个表
  3. 下一个表格应该显示为下拉列表吗？

非常感谢！N

答案1

我知道您说过您不能把所有东西都放在一张表中，但我已经做了这个，我不会把它扔掉。这实际上不是对您问题的回答，而是对您问题的不同解决方法，而且确实有效。

在一个名为“数据”的工作表上创建一个名为“数据”的表格，如下所示：

食物 食物类型 国家
鳕鱼 鱼 英国
低音 鱼 英国
卷曲 芯片 英国
厚切 芯片 英国
楔子 芯片 英国
巧克力 布丁 英国
面包 布丁 英国
香蕉 布丁 英国
芝士汉堡 汉堡 我们
菲斯堡 汉堡 我们
牛肉 汉堡 我们
牛扒 薯条 我们
皱褶 薯条 我们
鞋带 薯条 我们
芝麻 贝果 我们
甜洋葱 贝果 我们
清楚的 贝果 我们
俄罗斯 波兰饺子 波兰
灰树花 波兰饺子 波兰
血清素 波兰饺子 波兰
特拉迪西尼 比戈斯 波兰
姆洛达·卡普斯塔 比戈斯 波兰
沙博维 科特莱特 波兰
米耶洛尼 科特莱特 波兰
雷布尼 科特莱特 波兰

插入新的 VBA 模块并将此代码粘贴到其中：

Function uniqueValuesFromColumn(ByVal rng As Range, Optional ByVal colNum As Integer = 1, Optional ByVal visibleOnly As Boolean = False) As Collection
    Set uniqueValuesFromColumn = New Collection
    Dim r As Range
    For Each r In rng.Rows
        Dim val As String
        val = r.Cells(1, colNum)
        If (Not r.EntireRow.Hidden And visibleOnly) Or Not visibleOnly Then
            On Error Resume Next
                uniqueValuesFromColumn.Add val, val
            On Error GoTo 0
        End If
    Next r
End Function

Function stringFromCollection(ByVal col As Collection, ByVal separator As String) As String
    Dim val As Variant
    For Each val In col
        stringFromCollection = stringFromCollection & val & separator
    Next val
    stringFromCollection = Left(stringFromCollection, Len(stringFromCollection) - 1)
End Function

Function updateValidation()
    Worksheets("data").Cells.Validation.Delete
    Dim countryValidation As String, foodTypeValidation As String, foodValidation As String
    
    countryValidation = stringFromCollection(uniqueValuesFromColumn(Worksheets("data").Range("data"), 3, True), ",")
    Worksheets("data").Range("I1").Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
    Formula1:=countryValidation
    
    foodTypeValidation = stringFromCollection(uniqueValuesFromColumn(Worksheets("data").Range("data"), 2, True), ",")
    Worksheets("data").Range("K1").Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
    Formula1:=foodTypeValidation
    
    foodValidation = stringFromCollection(uniqueValuesFromColumn(Worksheets("data").Range("data"), 1, True), ",")
    Worksheets("data").Range("M1").Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
    Formula1:=foodValidation
End Function

然后在“数据”表模块中插入以下代码：

数据表帮助

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error GoTo cleanExit
    Application.EnableEvents = False
    With Worksheets("data")
        Select Case Target
            Case .Range("I1")
                If .Range("I1") = vbNullString Then
                    .ListObjects("data").Range.AutoFilter Field:=3
                    .ListObjects("data").Range.AutoFilter Field:=2
                Else
                    .ListObjects("data").Range.AutoFilter Field:=3, Criteria1:=.Range("I1")
                End If
                .Range("K1").Clear
                .Range("M1").Clear
            Case .Range("K1")
                If .Range("K1") = vbNullString Then
                    .ListObjects("data").Range.AutoFilter Field:=2
                Else
                    .ListObjects("data").Range.AutoFilter Field:=2, Criteria1:=.Range("K1")
                End If
                .Range("M1").Clear
        End Select
    End With
cleanExit:
    Application.EnableEvents = True
    updateValidation
End Sub

最后将此代码插入“ThisWorkbook”模块：

工作簿模块帮助

Private Sub Workbook_Open()
    updateValidation
End Sub

现在只需将工作簿保存为 .xlsm，关闭并重新打开。带有验证的单元格是“数据”工作表上的 I1、K1 和 M1。

相关内容