如何在 Excel 中实现下拉多选、反选

发表 分类

操作步骤（视频）：

操作步骤（文字）：

  1. 选择一列；
  2. 依次点击：数据 > 数据验证
  3. 类型选择「列表」，然后输入待选择的下拉项：PHP,Java,Python
  4. 在 「sheet」上右击，选择「查看代码」，添加如下处理多选逻辑的宏代码：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
Private Sub Worksheet_Change(ByVal Target As Range)
' Developed by Contextures Inc.
' www.contextures.com
Dim rngDV As Range
Dim oldVal As String
Dim newVal As String
If Target.Count > 1 Then GoTo exitHandler

On Error Resume Next
Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation)
On Error GoTo exitHandler

If rngDV Is Nothing Then GoTo exitHandler

If Intersect(Target, rngDV) Is Nothing Then
   'do nothing
Else
  Application.EnableEvents = False
  newVal = Target.Value
  Application.Undo
  oldVal = Target.Value
  Target.Value = newVal
  If Target.Column = 1 Then
    If oldVal = "" Then
      'do nothing
      Else
      If newVal = "" Then
      'do nothing
      Else
        If InStr(1, oldVal, newVal) <> 0 Then
          If InStr(1, oldVal, newVal) + Len(newVal) - 1 = Len(oldVal) Then
            Target.Value = Left(oldVal, Len(oldVal) - Len(newVal) - 1)
          Else
            Target.Value = Replace(oldVal, newVal & ",", "")
          End If
        Else
        Target.Value = oldVal & "," & newVal
'      NOTE: you can use a line break,
'      instead of a comma
'      Target.Value = oldVal _
'        & Chr(10) & newVal
        End If
      End If
    End If
  End If
End If

exitHandler:
  Application.EnableEvents = True
End Sub
  1. 修改代码 Target.Column = 1 为对应的列，多个列使用 Or 连接

⚠️ 注意：该操作是借助 Excel 宏功能实现，所以之后重新打开时要允许宏，否则不生效。