熟悉excel多级联动下拉菜单的都知道，通过设置数据有效性时嵌入公式和用VBA编写代码都可以实现菜单的联动。那这两者方法进行比较，到底哪种更好呢？我们来一起看看。
1、公式法：
一般情况下，设置菜单过程很简单，在数据有效性序列来源处输入公式=INDIRECT($I2)即可，其中“$I2”为上级菜单所处单元格。
但是当需要剔除菜单里的空值和重复项时，用公式法就需要设置较复杂的公式甚至需要设置辅助数据区；比如下面用公式方法设置三级菜单中的第一级菜单时，剔除空值和重复值就用到了辅助区：
一级菜单数据有效性来源公式：=OFFSET($T$2,,,COUNTIF(T:T,"><"),1)
二级菜单数据有效性来源公式：=OFFSET(INDIRECT($I3),0,0,1,MATCH(CHAR(1),INDIRECT($I3),-1))
三级菜单数据有效性来源公式：=OFFSET(INDIRECT($J3),0,0,MATCH(CHAR(1),INDIRECT($J3),-1),1)
其中二、三级菜单未考虑剔除重复值。
2、VBA代码法：一次性设置多级菜单
Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Count > 1 Or Target.Row < 3 Then Exit Sub If Target.Column = 9 Then Call 一级菜单(Target) If Target.Column = 10 Then If Target.Offset(0, -1) <> "" Then Call 二级菜单(Target, Target.Offset(0, -1)) End If If Target.Column = 11 Then If Target.Offset(0, -1) <> "" Then Call 三级菜单(Target, Target.Offset(0, -1), Target.Offset(0, -2)) End If End Sub Sub 一级菜单(rg As Range) Set D = CreateObject("Scripting.Dictionary") For i = 1 To [a30000].End(3).Row If Cells(i, 1) <> "" Then D(Cells(i, 1).Value) = "" End If Next s = Join(D.keys, ",") With rg.Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:=s End With Set s = Nothing End Sub Sub 二级菜单(rg As Range, fn As String) Set D = CreateObject("Scripting.Dictionary") For i = 1 To [a30000].End(3).Row If Cells(i, 1) = fn Then rowh = i Exit For End If Next For i = 2 To 7 If Cells(rowh, i) <> "" Then D(Cells(rowh, i).Value) = "" End If Next s = Join(D.keys, ",") arr = Split(s, ",") With rg.Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:=s End With For i = 1 To UBound(arr) If rg = arr(i) Then Exit Sub Next rg = arr(0) Set s = Nothing Set arr = Nothing End Sub Sub 三级菜单(rg As Range, fn1 As String, fn As String) Set D = CreateObject("Scripting.Dictionary") For i = 1 To [a30000].End(3).Row If Cells(i, 1) = fn Then rowh = i Exit For End If Next For j = 2 To 7 If Cells(rowh, j) = fn1 Then coll = j Exit For End If Next For k = rowh + 1 To [a30000].End(3).Row If Cells(k, 1) <> "" Then endrow = k - 1 Exit For End If Next If endrow = "" Then endrow = 200 For i = rowh + 1 To endrow If Cells(i, coll) <> "" Then D(Cells(i, coll).Value) = "" End If Next s = Join(D.keys, ",") arr = Split(s, ",") With rg.Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:=s End With For i = 1 To UBound(arr) If rg = arr(i) Then Exit Sub Next rg = arr(0) Set s = Nothing Set arr = Nothing End Sub
3、PK
第一种公式法设置的菜单中，在已经选择了3级菜单的情况下，修改2级菜单和1级菜单，会得到了下图中“电器区”和“瓜果”及“面点”不是联动匹配的情况。
这个时候需要继续设置2、3级菜单才可以正确显示，当有时候中途完成一半忘了时，就容易出现不合适的情况。
而第二种VBA代码方法通过增加下面的菜单区单元格修改事件，让上级菜单修改后，下级菜单根据修改情况自动变为当级菜单的第一个选项，规避不匹配的情况。
Private Sub Worksheet_Change(ByVal Target As Range) If Target.Count > 1 Or Target.Row < 3 Or Target.Value = "" Then Exit Sub If Target.Column = 9 Then Call 二级菜单(Target.Offset(0, 1), Target.Value) If Target.Column = 10 Then Call 三级菜单(Target.Offset(0, 1), Target.Value, Target.Offset(0, -1).Value) End Sub
也可通过修改代码实现当上级菜单修改后，下级菜单单元格清空的功能，看自己的需要进行设置即可。
效果如下：
