我这里是微软office的版本，其它版本大同小异：
1、数据校验入口

2、设置数据

3、sheet页右击查看代码

4、复制下面代码进去：

5、效果如下：

Option ExplicitSub Worksheet_Change(ByVal Target As Range)
'让数据有效性选择 可以多选,重复选
Dim rngDV As Range
Dim oldVal As String
Dim newVal As String
If Target.Count > 1 Then GoTo exitHandlerOn Error Resume Next
Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation)
On Error GoTo exitHandlerIf rngDV Is Nothing Then GoTo exitHandlerIf Intersect(Target, rngDV) Is Nothing Then
'do nothing
Else
Application.EnableEvents = False
newVal = Target.Value
Application.Undo
oldVal = Target.Value
Target.Value = newVal
If oldVal = "" Then
Else
If newVal = "" Then
Else
Target.Value = oldVal _
& ", " & newVal
End If
End If
End IfexitHandler:
Application.EnableEvents = True
End Sub

补充指定列，不可重复选代码片段

Option ExplicitSub Worksheet_Change(ByVal Target As Range)
'让数据有效性选择 可以多选,重复选
Dim rngDV As Range
Dim oldVal As String
Dim newVal As String
If Target.Count > 1 Then GoTo exitHandlerOn Error Resume Next
Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation)
On Error GoTo exitHandlerIf rngDV Is Nothing Then GoTo exitHandlerIf Intersect(Target, rngDV) Is Nothing Then'do nothing
ElseApplication.EnableEvents = FalsenewVal = Target.ValueApplication.UndooldVal = Target.ValueTarget.Value = newValIf Target.Column = 17 Then '这里规定好哪一列的数据有效性是多选的，A列是第1列，依次类推，如3就是C列，7就是G列If oldVal = "" Then'do nothingElseIf newVal = "" Then'do nothingElseIf 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)ElseTarget.Value = Replace(oldVal, newVal & ",", "") '不是最后一个选项重复的时候处理逗号End IfElse '不是重复选项就视同增加选项Target.Value = oldVal & "," & newVal
'      NOTE: you can use a line break,
'      instead of a comma
'      Target.Value = oldVal _
'        & Chr(10) & newValEnd IfEnd IfEnd IfEnd If
End IfexitHandler:
Application.EnableEvents = True
End Sub