重复 – 第2页

Excel 如何计算不重复值的个数？

图 7.99 中，C 列是生产车间今日正在生产的所有型体名称，部分型体在多台机上生产，因此型体名称存在重复值。现要求计算今日同时在生产的型体名称有多少个。

图7.99 生产表

解题步骤

计算一列中的数据个数（忽略重复值）有很多方法，可以使用删除重复项工具去除重复值，或使用高级筛选提取唯一值，然后计算去重后的唯一值数据个数，也可以使用公式计算一列中的不重复数据个数。使用公式达成需求的优点在于数据源变化时计算结果也相应地变化，其他方法则必须在数据源更新后重新操作一遍。

计算唯一值数据个数的公式如下，效果见图7.100。

图7.100 使用公式计算唯一值数量

知识扩展

1.COUNTIF函数的功能是计算符合条件的单元格数量，第一参数是计算目标，第二参数是条件。当第二参数包含多个条件时函数的计算结果也会有多个。

在本例中，COUNTIF 函数的第二参数是 C2：C13，包含 12 个值，因此表达式“COUNTIF（C2：C13，C2：C13）”会产生12个计算结果，对应12个字符串的出现次数。

图 7.101 中，A1：C12 包含 12 个产品的名称，选择 B1：B12 后输入公式“=COUNTIF（A1：A12，A1：A12）”并按组合键＜Ctrl+Shift+Enter＞结束，公式结果包含2、1、2、1、2、1、2、1、1、2、1、2等12个结果，其中1代表此单元格的值仅在A1：A12区域中出现一次，2则代表出现过两次，如A1和A7都有压线钳，因此B1和B7的值为2。

图7.101 统计每个单元格的值的出现次数

2.“COUNTIF（C2：C13，C2：C13））”用于计算每个单元格值的出现次数，而“=SUMPRODUCT（1/COUNTIF（C2：C13，C2：C13））”则用于计算C2：C13区域的唯一值数量。某个数据的出现次数是几，“COUNTIF（C2：C13，C2：C13））”的计算结果中就会有几个几。例如，某单元格的值出现了3次，那么“COUNTIF（C2：C13，C2：C13））”的计算结果中就会有3个3，再如，某单元格的值出现了5次，那么“COUNTIF（C2：C13，C2：C13））”的计算结果中就会有5个5……基于此规律，将它的倒数求和，得到的结果就刚好是不重复值的数量。

举一个简单的例子，A列有1个1、2个2、4个4，在B1中输入公式“=1/a1”从而计算其倒数（见图7.102），将公式向下填充后可以得到1个一分之一、2个二分之一和4个四分之一，使用SUM函数将它们求和后的结果为3，而A列的不重复数据个数也刚好等于3，这印证了求倒数后再汇总可以得到不重复数据个数的思路是正确的。

图7.102 对倒数求和

3.SUMPRODUCT 函数在本例中用于替换 SUM 函数，本例使用 SUM 和 SUMPRODUCT函数求和都能得到正确结果，但是使用 SUM 时必须按组合键＜Ctrl+Shift+Enter＞，而使用SUMPRODUCT不需要按此三键，相比而言更方便。

Excel 可否逐一罗列两列数据的相同值？

是否可以逐一罗列出两列数据的相同值？当数据源中的值变化时，提取出来的相同值也相应地更新。

以图7.80为例，A列和B列分别是去年和今年的优秀员工姓名，要求在D列罗列出两年都是优秀员工的姓名。

图7.80 去年与今年的优秀员工

解题步骤

判断两列是否存在相同项，使用COUNTIF函数计数即可，然后配合IF函数排除不符合条件的值，具体操作步骤如下。

1.在D1中输入标题“相同项”（见图7.81）。

2.选择D2：D11区域，然后输入以下数组公式：

输入公式后必须按组合键＜Ctrl+Shift+Enter＞结束，只按Enter键无法得到正确结果。

图7.81 提取两列的相同项

知识扩展

1.COUNTIF函数用于计算满足条件的单元格数量，第一参数是区域，第二参数是条件，条件可以是单个的也可以包含多个，当使用单个条件时统计结果也是单个的；当使用多个条件时，统计结果也包含多个，不过必须使用＜Ctrl+Shift+Enter＞组合键输入公式，否则只能取得单个结果。

2.本例中表达式“COUNTIF（B2：B10，A2：A11）”的含义是逐一统计A2：C11区域中每个单元格的值在B2：B10中的出现次数，如果未出现在B2：B10中则返回值为0，否则返回出现次数。表达式的计算结果包含10个值，使用数组公式才能一次性取得这10个值。

3.公式“=IF（COUNTIF（B2：B10，A2：A11）＞0，A2：A11，＂＂）”的含义是如果A2：A11区域中某个单元格的值出现在B2：B10区域，那么就返回该值本身，否则返回空文本。

IF函数在本例中的作用是排除不符合条件的值，使其返回空文本。可以通过以下方式理解公式的运算过程。

首先用COUNTIF函数统计A2：C11区域中每个单元格的值在B2：B10中的出现次数，效果如图7.82所示。

图7.82 统计A2∶C11的值在B2∶B10中的出现次数

图 7.82 中公式的返回值大于 0 时表示该姓名同时出现在去年和今年的区域中，此时用“COUNTIF（B2：B10，A2：A11）＞0”作为IF函数的条件执行判断就可以提取符合条件的姓名，排除其他姓名。

4.本例公式提取出来的姓名未放置在相邻的单元格中，查看时不太方便。改用以下数组公式可以让结果放置在相邻的单元格。

以上公式的使用方法是：在D2单元格输入公式，按组合键＜Ctrl+Shift+Enter＞结束，然后按下填充柄将公式向下填充到D11单元格中（见图7.83）。

图7.83 将两列的相同值放在相邻的单元格

此公式的含义是：如果A2：A11区域中某个单元格的值出现在B2：B10区域中，那么返回其行号，否则返回行号9999。接着使用SMALL函数对这些行号从小到大排序，最后使用INDEX函数从A列提取目标姓名，姓名的行号由SMALL函数生成的升序行号决定。

由于符合条件的姓名都是生成该姓名所在行的行号（本例为2、4、10），不符合条件时则生成9999，因此SMALL函数产生的升序行号其实是2、4、10、9999、9999、9999、9999、9999、9999、9999，如图7.84所示。

图7.84 模拟SMALL函数生成的行号

此时使用INDEX函数从以上行号提取姓名即可，由于A9999没有姓名，因此INDEX会返回数值0，为了将0转换成空白，在INDEX函数的后面添加了表达式“&＂＂”。

以上公式比较复杂，对于未系统学习数组公式的用户而言理解比较困难。不过这属于本案例的补充知识，读者可以忽略此公式，或者加入本书的售后服务群向图书作者提问。

Excel 如何标示两列数据的相同项？

图6.53是包含去年与今年成绩前10名的名单，现要求将两列名单中的相同值用红色标示出来。

图6.53 今年和去年的前十名名单

解题步骤

将目标单元格标示颜色的最佳工具是条件格式，具体操作步骤如下。

1.选择A2：C11区域。

2.单击功能区的“开始”→“条件格式”→“新建规则”，弹出“新建格式规则”对话框。

3.选择“使用公式确定要设置格式的单元格”，然后在下方的文本框中输入公式“=AND（ISTEXT（A2），COUNTIF（$A$2：$C$11，A2）＞1）”，设置界面如图6.54所示。

4.单击“格式”按钮，弹出“设置单元格格式”对话框，然后打开“填充”选项卡，将背景色设置为红色，然后单击“确定”按钮保存设置，图6.55为设置界面。

图6.54 设置条件

图6.55 设置格式

5.单击“确定”按钮返回工作表界面，此时工作表中A列与C列的相同值所在单元格都会显示为红色背景，而成绩相同时却不会标示出来，效果如图6.56所示。

图6.56 条件格式的执行效果

知识扩展

1.本例中选择A2：C11后再设置条件格式是因为去年与今年的学生姓名都刚好容纳于此区域中，尽管选择A2：D11区域再设置条件格式也能实现同样的功能，但是设置条件格式时应该遵循一个规则：由于条件格式本身比较耗内存，因此应该对尽可能小的区域设置条件格式，非必要的区域应该忽略。

2.公式“=AND（ISTEXT（A2），COUNTIF（$A$2：$C$11，A2）＞1）”表示如果A2：C11区域中某个单元格是文本，而且在这个区域中出现的次数大于1次，那么它就满足条件。

表达式“ISTEXT（A2）”用于判断单元格中的值是否为文本，“COUNTIF（$A$2：$C$11，A2）＞1”用于判断单元格的值出现的次数，最后用AND函数来判断是否两个条件同时满足，只有同时满足两个条件才添加背景色，从而忽略重复的成绩。

3.本例还有另一种操作方法：分两次设置条件格式，一次对 A2：A11 区域设置条件格式，一次对 C2：C11 区域设置条件格式。对 A2：C11 区域设置条件格式时，用“=COUNTIF（$C$2：$C$11，A2）＞0” 作为条件，对 C2：C11 区域设置条件时，用“=COUNTIF（$A$2：$A$11，C2）＞0”作为条件。

Excel 如何快捷删除多列的重复值？

工作表中存在重复值，是否可以删除重复值只保留唯一值呢？

例如，同一项数据出现了多次，只保留第一次出现的数据。

解题步骤

Excel 从 2007 版开始提供了删除重复项工具，它可以将单列重复或多列重复的数据删除，Excel 2003及以前的版本不带此功能。

以图 6.46 为例，要求姓名和学号同时重复时删除重复项，其中一项重复时不删除，操作步骤如下。

图6.46 成绩表

1.选择A1：C11区域。

2.单击功能区的“数据”→“删除重复项”，弹出“删除重复项”对话框。

3.在图6.47所示的对话框中，取消选择“成绩”复选框，表示只判断姓名和学号，成绩允许重复。

图6.47 设置重复项的判断条件

4.单击“确定”按钮后，Excel会弹出如图6.48所示的对话框，通知用户删除了多少个重复值。关闭对话框后可以看到图6.49所示的结果。

图6.48 提示已删除的重复值数量

图6.49 删除重复项的结果

知识扩展

1.删除重复项和通过高级筛选工具提取唯一值有所不同。删除重复项的缺点是无法把结果显示在其他区域，优点是允许自由选择判断重复的条件，可以以单列的值为判断条件，也可以以多列的值为判断条件。

2.如果要求将重复值隐藏起来，便于以后在必要时恢复数据，那么应该采用辅助区域搭配筛选工具来完成。例如，在D1输入“辅助区域”，再在D2输入公式“=A2&B2”并向下填充，效果如图6.50所示。接着选择D1：A11区域，并单击功能区的“数据”→“高级”，弹出“高级筛选”对话框，在对话框中按图6.51所示的方式设置选项，当单击“确定”按钮后将得到图6.52所示的筛选结果。

图6.50 在辅助区域中填充公式

图6.51 设置筛选条件

图6.52 筛选结果

图6.52中重复值已经处于隐藏状态，如果此时清除筛选可以还原所有数据。

Excel 可否突出显示区域中的重复值？

在图 6.32 中，部分人员获得多次冠军，要求通过背景色将多次夺冠者所在行标示出来，如何才能实现？

图6.32 冠军榜

解题步骤

用背景颜色标示目标值只能使用条件格式，不过由于要求目标所在行都要标示，而非只标示目标单元格，因此需要对整个区域添加条件格式，而且要通过公式来确认单元格是否重复，具体操作步骤如下。

1.选择A2：C13区域，然后单击功能区的“开始”→“条件格式”→“新建规则”。

2.从规则类型列表中选择“使用公式确定要设置格式的单元格”，然后在下方的文本框中输入公式“=COUNTIF（$B$2：$B$13，$B2）＞1”，操作界面如图6.33所示。

3.单击“格式”按钮，弹出“设置单元格格式”对话框，打开“填充”选项卡，然后选择红色并单击“确定”按钮，图6.34即为格式设置界面。

图6.33 设置条件

图6.34 设置格式

4.设置好条件与格式后，关闭对话框，返回工作表界面，此时选区会自动应用条件格式，最终效果如图6.35所示。

图6.35 标示结果

知识扩展

1.公式“=COUNTIF（$B$2：$B$13，$B2）＞1”的含义是B2：B13区域中任意值的出现次数大于1，就符合条件格式的条件，然后自动应用指定的格式。其中“$B$2：$B$13”必须用绝对引用，而“$B2”则只能锁定列标、不能锁定行号。

2.条件格式中自带一个“重复值”子菜单，单击即可标示完成，不需要使用公式。它的缺点是只能标示一列，不能达成本例的效果——B2符合条件时，将A2：C2都标示颜色。

3.条件格式自带的标示重复值使用步骤为：选择B2：B13，然后单击功能区的“开始”→“条件格式”→“突出显示单元格规则”→“重复值”，在弹出的“重复值”对话框中不做任何修改，直接单击“确定”按钮应用条件格式即可操作完成。图6.36是条件与格式设置界面，图6.37则是条件与格式的应用效果。

图6.36 设置条件与格式

图6.37 应用条件与格式效果

Excel 可以标示区域中的重复值吗？

当数据量大时，要在工作表中找出重复的值是相当困难的。例如在数千行中找到出现过两次或三次的姓名，仅凭肉眼是无法准确找到目标的。

图 3.33 中部分参赛队员的姓名重复，有没有办法瞬间标示出重复的姓名？省和姓名两个项目同时重复才算重复。

图3.33 参赛人员资料表

解题步骤

Excel 2016的条件格式可以标示符合条件的目标单元格，具体步骤如下。

1.选择A2：C11区域。

2.单击功能区的“开始”→“条件格式”→“新建规则”，弹出“新建格式规则”对话框。

3.在“选择规则类型”列表中单击“使用公式确定要设置格式的单元格”。

4.在下方的公式文本框中输入以下公式：

此公式表示A列的省名加上B列的姓名在所有省名与姓名中的出现次数大于1。操作界面如图3.34所示。

5.单击“新建格式规则”对话框右下角的“格式”按钮，弹出“设置单元格格式”对话框，打开“填充”选项卡，将颜色设置为红色，操作界面如图3.35所示。

图3.34 添加条件格式

图3.35 添加格式

6.单击“确定”按钮保存设置，同时返回工作表界面，A2：C11区域应用条件格式后将显示图3.36所示的结果。

图3.36 应用条件格式结果

图 3.36 中湖南的张扬扬和陕西的朱玲都被标示为红色，说明它们都有重复出现。事实上，由于案例中数据较少，通过肉眼查看也能判断湖南的张扬扬和陕西的朱玲确实存在重复，表明条件格式设置正确。

知识扩展

1.条件格式用于将符合条件的单元格按指定的格式显示。通俗地讲，就是突出显示某些单元格。Excel 2003中允许设置1～3个条件，Excel 2007开始所有高版本都支持1～64个条件。

2.Excel提供了数十个内置的条件，其中包含标示重复值，不过它只对单列或单行的重复值生效。本例要求两列同时重复才算重复，因此只能借用公式来自定义条件。

3.在本例中，公式“=SUM（N（$A2&$B2=$A$2：$A$11&$B$2：$B$11））＞1”的含义是：拿A2连接B2后产生的字符串去与A2：A11连结B2：B11产生的10个字符串逐一比较，然后计算字符串一致的数量是否大于1次。当大于1次时，公式结果为True，条件成立，立即应用指定的格式。本例中指定的格式是红色背景，因此省名与姓名重复时单元格会显示为红色。

4.如果要标示只参加了一个体育项目的运动员姓名，那么将公式中的“＞1”修改为“=1”即可。

5.公式中的绝对值符号$不能删除，否则无法正确标示目标单元格。

6.如果只需要标示A列与B列，那么选择A2：B11区域后再设置条件格式即可，公式与本例一致。

7.件格式可以用单元格背景为条件，也可以用字体加粗、倾斜、字体颜色或添加单元格边框作为条件，只不过添加背景色最为醒目，因此工作中用得最多的是背景色。

8.如果需要删除条件格式，那么选择设置了条件格式的区域，并单击功能区的“开始”→“条件格式”→“管理规则”，然后在“条件格式规则管理器”中选择要删除的条件，最后单击“删除规则”按钮，操作界面如图3.37所示。

图3.37 删除条件格式

请在封面中缝指定的QQ群下载文件和视频教材，从而方便练习，同时验证本案例中的技巧与操作结果。

Excel 如何禁止输入重复值

如下图所示的工作表中A列用于存放职工工号。由于姓名允许重复，但工号不允许重复，因此要求在A输入数据时，Excel自动检查是否重复，如果重复则弹出提示信息，要求用户重新输入。

解题步骤

限制某区域不能输入重复值宜用数据验证，具体步骤如下。

1.选择A2：A10区域。

2.单击功能区的“数据”→“数据验证”，弹出“数据验证”对话框。

3.将对话框中“允许”下拉列表中的“任何值”修改为“自定义”。

4.在公式框中输入以下公式，操作界面如图3.14所示。

5.打开“数据验证”对话框的“出错警告”选项卡，“样式”保持“停止”不变，将“标题”设置为“友情提示”，将“错误信息”设置为“当前值在A2：A10区域已经存在，请重新录入”，设置界面如图3.15所示。

图3.14 使用公式限制A2∶A10只能输入唯一值

图3.15 设置出错警告

6.单击“数据验证”对话框中的“确定”按钮返回工作表界面，在A2：A3单元格输入工号34和35，然后在A4单元格再次输入工号34，当单击Enter键后，Excel会弹出图3.16所示的提示框，必须单击“重试”按钮，然后输入其他不重复的值，否则无法输入成功。

图3.16 输入重复值时的错误提示

知识扩展

1.COUNTIF函数属于Excel的统计函数，用于计算区域中符合某个条件的数据个数。例如，等于100的单元格数量、大于999的单元格数量、包含“合格”二字的单元格数量，或者计算不等于某个单元格的单元格数量。本例采用COUNTIF函数计算A2：A10区域中值等于当前单元格的值的单元格数量。

2.COUNTIF函数的语法如下：

COUNTIF(range,criteria)

它包含两个必选参数，第一参数代表要对其进行计算的区域，第二参数代表要计算的条件。假设要计算A1：A10区域中大于500的单元格数量，那么宜采用以下公式：

=COUNTIF(A1:A10,＂＞500＂)

假设要计算A1：A10区域中等于500的单元格数量，那么宜采用以下公式：

=COUNTIF(A1:A10,500)

当第二参数是数字时可以不用等号也不加双引号。

假设要计算A1：A10区域中包含“螺丝”的单元格数量，那么宜采用以下公式：

=COUNTIF（A1：A10，＂*螺丝*＂）

第二参数“*螺丝*”表示包含“螺丝”，前后的“*”代表任意长度的任意字符，即以任意字符开始、任意字符结尾、中间包含“螺丝”二字的字符串。公式的运算效果如图3.17所示。

图3.17 计算A1∶A10区域中包含“螺丝”的单元格数量

3.本例中公式“=COUNTIF（$A$2：$A$10，A2）=1”代表A2：A10区域中任意单元格的值只允许出现一次，如果重复则会弹出提示对话框警告用户。

4.数据验证仅对设置后手工输入数据有效，如果先输入字符后设置数据验证，那么 Excel不会提示不符合规则的数据。

5.数据验证对公式也没有限制，限制A1单元格只能输入1～10之间的数值，当在A1单元格手工输入11时会禁止输入，但是A1单元格通过公式引用A2单元格的值，在A2单元格中输入11时，A1单元格也会产生11，而且不产生任何提示。

请在封面中缝指定的QQ群下载文件和视频教材，从而方便练习，同时验证本案例中的技巧与操作结果。

Excel 2019利用高级筛选删除重复数据

Excel有一个小小的缺陷，那就是无法自动识别重复的记录。虽说Excel中并没有提供清除重复记录这样的功能，但是可以利用它的高级筛选功能来达到相同的目的。

打开“成绩总计表.xlsx”工作簿，以该工作簿中的数据为例，筛选出“总分在450分以上且语文成绩在75分以上”的记录，具体操作步骤如下。

STEP01：在A18:B20单元格区域输入要进行筛选的条件，输入结果如图6-57所示。

图6-57　输入筛选条件

STEP02：如图6-58所示，在“方式”列表框中选择“将筛选结果复制到其他位置”单选按钮，设置引用的“列表区域”位置为“Sheet1!$A$1:$J$16”，引用的“条件区域”为“Sheet1! $A$19:$B$20”，并选择将筛选的结果复制到“Sheet1! $A$22”单元格处，然后勾选“选择不重复的记录”复选框，最后单击“确定”按钮完成高级筛选设置。最终结果如图6-59所示，筛选结果中重复的数据只显示唯一的一条记录。

图6-58　设置筛选区域

图6-59　删除重复记录结果

Excel 2019利用条件格式快速比较不同区域的数值

在实际工作中，用户需要对比的数据可能不是位置一一相对应的。如果工作表中两列编号的排列顺序并不相同，要把两列不匹配的编号一一标记出来，条件格式的设置要相对复杂一些。

通过以下操作步骤，可以快速比较不同区域的数值。

STEP01：打开“匹配数据.xlsx”工作簿，选择A2:B20单元格区域，切换至“开始”选项卡，在“样式”组中单击“条件格式”下三角按钮，在展开的下拉列表中选择“新建规则”选项打开如图5-72所示的“新建格式规则”对话框。在“选择规则类型”列表框中选择“使用公式确定要设置格式的单元格”选项，在“为符合此公式的值设置格式”文本框中输入公式，这里输入“=OR(EXACT(A2,$B$2:$B$20))=FALSE”，然后单击“格式”按钮打开“设置单元格格式”对话框，如图5-72所示。

STEP02：切换至“填充”选项卡，单击“图案颜色”选择框右侧的下拉按钮，在展开的下拉列表中选择“蓝色”；单击“图案样式”选择框右侧的下拉按钮，在展开的下拉列表中选择“75%灰色”选项，然后单击“确定”按钮完成单元格格式的设置，如图5-73所示。

图5-72　输入公式

图5-73　设置填充格式

STEP03：在“新建格式规则”对话框中单击“确定”按钮返回工作表页面，此时的工作表如图5-74所示，编号一中所有与编号二不匹配的内容都会被标记出来。

图5-74　查询不匹配的内容

在上述公式中，EXACT函数用于比较两个文本字符串是否完全相同，如完全相同则返回TRUE，否则返回FALSE。另外，如果在公式中输入“=NOT(OR(A2=$B$2:$B$20))”，也可以实现相同的操作。

Excel 2019利用条件格式突出显示重复数据

例如，在高校招生时，会有同名的学生。现在工作人员需要把同名学生的成绩突出显示出来，如图5-63所示。此时可以使用“条件格式”功能来实现突出显示重复数据的操作。

其具体操作步骤如下。

STEP01：选择A2:G17单元格区域，切换至“开始”选项卡，在“样式”组中单击“条件格式”下三角按钮，在展开的下拉列表中选择“新建规则”选项，打开“新建格式规则”对话框。在“选择规则类型”列表框中选择“使用公式确定要设置格式的单元格”选项，在“为符合此公式的值设置格式”文本框中输入公式，例如这里输入“=COUNTIF($A$2:$A$17,$A2)>1”，然后单击“格式”按钮，如图5-64所示。