第7章 控制选区的不重复值
在日常工作中，某些数据是不允许重复的，否则就表示数据输入有误，如订单号码、员工工号、身份证号码、CPU序列号等。本章将通过9个实例讲解本列重复值、双列重复值及多表重复值之相关操作。
● 实例52统计字符在选区中的重复次数
● 实例53报告重复数据的地址
● 实例54检查重复数据的重复次数
● 实例55统计选区中不重复数据个数
● 实例56提取单列数据之不重复值
● 实例57提取多列数据之不重复值
● 实例58单列中不允许输入重复值
● 实例59双列不允许输入重复值
● 实例60跨工作表控制不重复值
实例52 统计字符在选区中的重复次数
【技巧说明】 统计字符在选区中的重复次数。
【案例介绍】 产品检测与否一般用“是”与“否”表示，现需统计已检验之产品数，即“是”在选区中重复出现的次数。
【案例实现】 参见以下步骤：
[1] 使用快捷键Alt+F11进入VBE（Visual Basic Editor）环境。
[2] 单击菜单【插入】\【模块】，打开模块代码窗口。
[3] 在右边代码窗口输入以下代码：
Sub选区字符重复次数统计()
Dim str As String, strr As String, num As Long, i As Integer, rng As Range
str=Application.InputBox("请输入要查询的文字：", "重复值", "", 10, 10, , , 3)
For Each rng In Selection
For i=1 To Len(rng)
strr=Mid(rng.Value, i, 1)
If strr=str Then
num=num+1
End If
Next
Next
MsgBox "选区中" & str & "字重复出现了" & num & "次", vbInformation, "重
复次数"
End Sub
[4] 关闭VBE窗口返回到工作表。
[5] 选择待检测区域，用快捷键Alt+F8调出运行宏窗口，然后单击“执行”按钮，将弹出输入要查询的文字之对话框，如图2.47所示。输入文字并确定后程序返回结果，如图2.48所示。
图2.47 输入要查询的文字
图2.48 返回重复次数
提示
本实例参见光盘样本：..\第2部分\实例52.xlsm。
实例53 报告重复数据的地址
【技巧说明】 报告重复数据的地址。
【案例介绍】 输入学号时不允许有重复值，若有重复值需检测已存在该值的单元格地址。
【案例实现】 参见以下步骤：
[1] 使用快捷键Alt+F11进入VBE（Visual Basic Editor）环境。
[2] 使用快捷键Ctrl+R，显示工程资源管理器。
[3] 双击左边列表中的“学号表”，打开工作表代码窗口。
[4] 在右边代码窗口输入以下代码；
Private Sub Worksheet_Change(ByVal Target As Range)
Dim msg, rng As Range, temp As String
If Target.Count=1 And Target.Column=3 Then
'如果一次仅在单个单元格输入数据且当前列为第3列
If Application.WorksheetFunction.CountIf(Target.EntireColumn,
Target.Value) > 1 Then '如果存在重复
For Each rng In Range("c1", Target.Address) '在C列检测
If rng=Target Then '如果某单元格等于当前单元格
temp=temp & rng.Address & "、" '记录其地址
End If
Next '检测下一个
temp=Left(temp, Len(temp)-1) '去掉顿号
MsgBox "出现重复数据,地址在:" & Chr(10) & temp, vbOKOnly, "提示" '返回结果
End If
End If
End Sub
[5] 关闭VBE窗口返回到工作表。
[6] 在C列输入一个已存在的学号“025”，将弹出重复值地址，如图2.49所示。
图2.49 返回重复值地址
提示
本实例参见光盘样本：..\第2部分\实例53.xlsm。
【相关知识说明】
（1）Left：返回Variant (String)，其中包含字符串中从左边算起指定数量的字符。
（2）Len：返回Long，其中包含字符串内字符的数目，或者是存储一个变量所需的字节数。
实例54 检查重复数据的重复次数
【技巧说明】 检查当前重复数据的重复次数。
【案例介绍】 本例是实例52的延伸，实例52是统计单字，假设单元格数据为“是是”，则将计算两次；本例以单元格数据为基准对区域进行检测，检测对象是单元格数据非单字。
【案例实现】 参见以下步骤：
[1] 使用快捷键Alt+F11进入VBE（Visual Basic Editor）环境。
[2] 使用快捷键Ctrl+R，显示工程资源管理器。
[3] 双击左边列表中的“学号表”，打开工作表代码窗口。
[4] 在右边代码窗口输入以下代码：
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count=1 And Target.Column=3 Then
'如果一次仅在单个单元格输入且当前列为3
If Application.WorksheetFunction.CountIf(Target.EntireColumn,
Target.Value) > 1 Then
msg=MsgBox("出现重复数据：" & Target.Value & Chr(10) &
"在本列此数据已重复出现" & _
WorksheetFunction.CountIf(Target.EntireColumn,Target.Value)–1
& "次" _
& Chr(10) & "想保留请点Y，否则点N", vbYesNo+vbOKOnly, "提示")
If msg=vbNo Then '选择不保留则该单元格赋空值
Target.ClearContents
Else: If msg=vbYes Then Exit Sub
End If
End If
End If
End Sub
[5] 关闭VBE窗口返回到工作表。
[6] 在C列输入一个已存在的学号“025”，将弹出与当前单元格数据重复次数提示框，并提示是否保存当前输入数据。选择“是”则保存，“否”则清除已输入数据，如图2.50所示。
图2.50 返回重复数据重复次数
提示
本实例参见光盘样本：..\第2部分\实例54.xlsm。
实例55 统计选区中不重复数据个数
【技巧说明】 统计选区中不重复数据个数。
【案例介绍】 如图2.51所示，投票人列出了各自的投票对象。现在需要统计被投票的才女已有多少名，即计算区域中的不重复值个数。
【案例实现】 参见以下步骤：
[1] 使用快捷键Alt+F11进入VBE（Visual Basic Editor）环境。
[2] 单击菜单【插入】\【模块】，打开模块代码窗口。
[3] 在右边代码窗口输入以下代码：
Sub不重复数据个数()
Dim a As String, b As Integer, rng As Range
a=","
For Each rng In Selection '遍历整个区域
If InStr(a, "," & rng & ",")=0 Then '如果在变量a中未找到单元格的
值加前缀后缀
b=b+1: a=a & rng & "," '那么累加计数器，同时重定义变量a
End If '下一个
Next
MsgB
[4] 关闭VBE窗口返回到工作表。
[5] 选择才女名字所有区域B3:B13及D3:D13，用快捷键Alt+F8调出运行宏窗口，然后单击执行按钮，将弹出选区中不重复值个数之对话框，如图2.51所示。
图2.51 返回不重复值的个数
提示
本实例参见光盘样本：..\第2部分\实例55.xlsm。
【相关知识说明】
（1）InStr：返回Variant (Long)，指定一个字符串在另一个字符串中最先出现的位置。
（2）计算不重复数据个数有很多方法，本例方法比较有效率。为了开拓思维，再给出一个同样可求不重复值的个数之程序：
Sub不重复值个数()
On Error Resume Next
Dim Cell As Range, only As New Collection
For Each Cell In Selection
If Cell <> "" Then only.Add Cell.Value, CStr(Cell.Value)
Next Cell
MsgBox "选区中不重复值有" & only.Count & "个！"
End Sub
实例56 提取单列数据之不重复值
【技巧说明】 提取单列数据之不重复值。
【案例介绍】 以实例55数据为基准，仅提取单列数据。现需在D列返回被投票之才女姓名（不存在重复名），数据如图2.52所示。
【案例实现】 参见以下步骤：
[1] 使用快捷键Alt+F11进入VBE（Visual Basic Editor）环境。
[2] 单击菜单【插入】\【模块】，打开模块代码窗口。
[3] 在右边代码窗口输入以下代码：
Sub返回不重复值()
Range("b2:b13").Copy Range("D2") '复制数据
Application.CutCopyMode=False '关闭粘贴模式
ActiveSheet.Range("D2:D13").RemoveDuplicates Columns:=1,Header:=xlNo
'返回不重复值
End Sub
[4] 关闭VBE窗口返回到工作表。
[5] 用快捷键Alt+F8调出运行宏窗口，然后单击“执行”按钮，在D列将列出B列数据的不重复值，如图2.53所示。
图2.52 投票数据表
图2.53 在D列出现B列数据的不重复值
提示
本实例参见光盘样本：..\第2部分\实例56.xlsm。
【相关知识说明】
（1）CutCopyMode：返回或设置剪切或复制模式的状态，可为True、False。本例中粘贴数据后使复制模式等于假，即可消除单元格四周之虚框。
（2）RemoveDuplicates：从值区域中删除重复的值。语法如下：
代码体表达式.RemoveDuplicates(Columns, Header)
第一个参数为单元格区域，第二个参数表示是否包含标题。
（3）提取区域不重复值仍然存在多种方法。本例中的方法是Excel 2007新增的方法，在更低的版本中代码不可用。为了提供更多的编程思维，同前例一样，再提供一种方法供读者学习，此法可通用于Excel 2000、Excel 2002、Excel 2003、Excel 2007：
Sub返回不重复值二()
On Error Resume Next
Dim Cell As Range, only As New Collection, i As Integer
For Each Cell In Range("b2:b" & [b1048576].End(xlUp).Row)
If Cell <> "" Then only.Add Cell.Value, CStr(Cell.Value)
Next Cell
For i=1 To only.Count
Cells(1+i, 4)=only(i)
Next i
Cells(i, 4).CurrentRegion.Borders.LineStyle=xlContinuous
End Sub
实例57 提取多列数据之不重复值
【技巧说明】 提取多列数据之不重复值。
【案例介绍】 高考学生每人可以填写两个志愿学校，每个学校名称都在表中出现多次。现需列出学生所报考的学校有哪些，重复出现仅统计一次。
【案例实现】 参见以下步骤：
[1] 使用快捷键Alt+F11进入VBE（Visual Basic Editor）环境。
[2] 单击菜单【插入】\【模块】，打开模块代码窗口。
[3] 在右边代码窗口输入以下代码：
Sub不重复值()
On Error Resume Next '错误时运行下一句
Dim cell As Range, only As New Collection, i As Byte, Results As
VbMsgBoxResult
Dim Cn As Byte, Rn As Byte, msg As String, item
Results=MsgBox("先列后行点击“是（Y）”" & Chr(10) & "先行后列点击“否（N）”",
vbYesNo, "取值顺序") '询问取值顺序
If Results=vbYes Then '如果选择是
Rn=Selection.Rows.Count '统计选区行数
Cn=Selection.Columns.Count '统计选区列数
For i=1 To Cn
For j=0 To Rn-1
'下一句用If跳过空值
If Selection.item(i+j * Cn) <> "" Then only.Add Selection.item
(i+j * Cn).Value, CStr(Selection.item(i+j * Cn))
Next j
Next i
Else '如果选择否
For Each cell In Selection '遍历选区
only.Add cell.Value, CStr(cell.Value) '提取不重复值
Next cell
End If
For Each item In only '遍历不重复值
msg=msg & Chr(10) & item '将不重复值串联
Next item
MsgBox msg, 64, "不重复数据" '以消息形式返回不重复值，也可以将之返回到单元格
End Sub
[4] 关闭VBE窗口返回到工作表。
[5] 选中B3∶C14区域，用快捷键Alt+F8调出运行宏窗口，然后单击“执行”按钮，程序弹出“取值顺序”对话框，单击“是”按钮，程序自动列出大学学校的名称，每个学校名仅取一次，如图2.54所示。
图2.54 提取多列不重复数据
提示
本实例参见光盘样本：..\第2部分\实例57.xlsm。
【相关知识说明】
（1）Selection.Rows.Count：选择区的行数；Selection.Columns.Count则是列数。
（2）Collection：项目所组成的有序集合，可以把这个集合作为单元来引用。本例中使用此对象来储存选区数据的不重复值，然后再将不重值逐个取出，以MsgBox消息框形式返回。
（3）本例中以消息方式返回结果，也可以将结果返回到指定的区域，代码如下：
Sub不重复值二()
On Error Resume Next
Dim cell As Range, only As New Collection, i As Byte, Results As
VbMsgBoxResult
Dim Cn As Byte, Rn As Byte, msg As String, item
Results=MsgBox("先列后行点击“是（Y）”" & Chr(10) & "先行后列点击“否（N）”",
vbYesNo, "取值顺序")
If Results=vbYes Then
Rn=Selection.Rows.Count
Cn=Selection.Columns.Count
For i=1 To Cn
For j=0 To Rn-1
If Selection.item(i+j * Cn) <> "" Then only.Add Selection.item
(i+j * Cn).Value, CStr(Selection.item(i+j * Cn))
Next j
Next i
Else
For Each cell In Selection
only.Add cell.Value, CStr(cell.Value)
Next cell
End If
i=0
Set cell=Application.InputBox("请选择用于存放结果的区域（可以选单个单元格）",
"结果存放区域", "d2", , , , , 8)
For Each item In only
cell.Offset(i, 0)=item
i=i+1
Next item
End Sub
实例58 单列中不允许输入重复值
【技巧说明】 单列中不允许输入已存在的数据。
【案例介绍】 以实例53数据为例，同班学生学号不可以重复，如果输入了重复学号，则进行提示，同时删除输入的数据。
【案例实现】 参见以下步骤：
[1] 使用快捷键Alt+F11进入VBE（Visual Basic Editor）环境。
[2] 使用快捷键Ctrl+R，显示工程资源管理器。
[3] 双击左边列表中的“学号表”，打开工作表代码窗口。
[4] 在右边代码窗口输入以下代码：
Private Sub Worksheet_Change(ByVal Target As Range)
With Target
If .Count=1 And .Column=3 Then '如果一次仅在单个单元格输入且当前列为3
If Application.WorksheetFunction.CountIf(.EntireColumn,.Value)>1 Then
MsgBox "数据重复，请检查后再输入！", 64, "提示"
.ClearContents
End If
End If
End With
End Sub
[5] 关闭VBE窗口返回到工作表。
[6] 在单元格C6输入已存在的学号025，系统立即提示数据重复，同时清除输入的数据，如图2.55所示。
图2.55 提示数据重复
提示
本实例参见光盘样本：..\第2部分\实例58.xlsm。
实例59 双列不允许输入重复值
【技巧说明】 双列不允许输入重复值。
【案例介绍】 以实例53数据为例，将数据改为多列，同班学生学号不可以重复，如果输入了重复学号则进行提示，同时删除输入的数据。
【案例实现】 参见以下步骤：
[1] 使用快捷键Alt+F11进入VBE（Visual Basic Editor）环境。
[2] 使用快捷键Ctrl+R，显示工程资源管理器。
[3] 双击左边列表中的“学号表”，打开工作表代码窗口。
[4] 在右边代码窗口输入以下代码：
Private Sub Worksheet_Change(ByVal Target As Range)
With Target
If .Count=1 And (.Column=3 Or .Column=6) Then
'如果一次仅在单个单元格输入且当前列为3或者6
If WorksheetFunction.CountIf(Columns(3), .Value)+
WorksheetFunction.CountIf(Columns(6), .Value) > 1 Then
MsgBox "数据重复，请检查后再输入！", 64, "提示"
.ClearContents
End If
End If
End With
End Sub
[5] 关闭VBE窗口返回到工作表。
[6] 在单元格F6输入已存在的学号025，系统立即提示数据重复，同时清除输入的数据，如图2.56所示。
提示
本实例参见光盘样本：..\第2部分\实例59.xlsm。
图2.56 提示数据重复
【相关知识说明】
（1）And：两个表达式的逻辑运算符，表示逻辑连接，相当于中文的“而且”，本例表示需要同时满足两个条件才执行程序。
（2）Or：两个表达式的逻辑运算符，相当于中文的“或者”。
（3）CountIf：工作表函数，按指定条件计数。
实例60 跨工作表控制不重复值
【技巧说明】 在不同工作表间控制不重复值。
【案例介绍】 以实例59数据为例，将后三列数据置于第二个工作表，仍然要求输入重复学号时则进行提示，同时删除输入的数据。
【案例实现】 参见以下步骤：
[1] 使用快捷键Alt+F11进入VBE（Visual Basic Editor）环境。
[2] 使用快捷键Ctrl+R，显示工程资源管理器。
[3] 双击左边列表中的“一班学号”，打开工作表代码窗口。
[4] 在右边代码窗口输入以下代码：
Private Sub Worksheet_Change(ByVal Target As Range)
With Target
If .Count=1 And (.Column=3) Then '如果一次仅在单个单元格输入且当前列为3
If WorksheetFunction.CountIf(Sheet1.Columns(3), .Value)+
WorksheetFunction.CountIf(Sheet2.Columns(3), .Value) > 1 Then
MsgBox "数据重复，请检查后再输入！", 64, "提示"
.ClearContents
End If
End If
End With
End Sub
[5] 关闭VBE窗口返回到工作表。
[6] 双击工程资源管理器中的“二班学号”工作表，将代码粘贴进去。
[7] 在两个工作表中的第三列输入一个已存在的学号“025”，系统立即提示数据重复，同时清除输入的数据，如图2.57所示。
图2.57 阻止输入重复值
提示
本实例参见光盘样本：..\第2部分\实例60.xlsm。