分类：准确性篇

下图中的生产表中需要输入大量的姓名，而且每天都输入相同的一批姓名。但是经常会在输入姓名时产生手误，如将五笔编码FNU误按成FNY，“张志全”录成“张专全”，从而导致资料出错。有什么办法既能让输入速度加快又不出错，确保每天输入同一个人的姓名时都保持一致呢？

解题步骤

创建一个辅助工作表，然后将所有需要用到的姓名存放在辅助表中，最后对需要输入姓名的区域设置数据验证，通过数据验证调用辅助表中的所有姓名，显示在下拉列表中供用户使用，从而用选输录入的方式替代手工打字。具体步骤如下。

1.单击工作表标签右方的“新工作表”按钮（图标为），然后将新建的工作表移到最左端并命名为“职工表”。

2.将所有需要用到的姓名输入到职工表的A列中，效果如图3.39.所示。

图3.39 创建辅助表

3.选择A2：A20，然后单击功能区的“数据”→“数据验证”，弹出“数据验证”对话框。

4.从“允许”下拉列表中将默认值“任何值”修改为“序列”。

5.将“来源”设置为“=职工表！$A$1：$A$20”，操作界面如图3.40所示。

图3.40 设置数据验证的条件与数据来源

6.单击“确定”按钮保存设置，同时返回工作表界面。

7.单击A2：A20区域中的任意单元格，Excel会弹出如图3.41所示的列表供用户选择。

图3.41 从下拉列表中选择目标数据

知识扩展

1.从下拉列表中选择目标数据的方式输入字符串有可能比手工打字的速度更快，也有可能更慢，视打字速度而定。但是从准确性上讲，通过选择输入数据的方式一定占据上风。同一个姓名，一年输入300次很难确保300次都输入正确，但是通过选择的方式输入则会准确得多。

2.数据验证的数据源不能有重复值，否则下拉列表会变得很长，列表越长从列表中查找目标就越费时。

3.本例的数据源可以放在“1号生产表”中，但由于后面还有“2号生产表”、“3号生产表”都需要调用，因此统一放在辅助工作表中更利于维护、更新。

4.为了提升搜索速度，应该将辅助工作表中的姓名按拼音顺序排序。例如，“鲍文秀”应排在前面，而“赵文祥”则排在后面。在打开下拉列表之前就能够预知目标的大概位置，找到目标的速度就会快得多。

5.数据验证产生的下拉列表中的文字不允许修改字体大小。

6.设置了数据验证后，可以选择式输入也允许手工输入，但是输入错别字时将被 Excel 阻止，提示“输入值非法”。数据验证的存在价值正在于此。

7.下拉列表框的默认项目数量是8个，要查看其他项目只能拉滚动条查看。

8.选中单元格后可以按组合键＜Alt+下箭头键＞打开下拉列表，然后通过下箭头键选择目标。

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

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

图 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群下载文件和视频教材，从而方便练习，同时验证本案例中的技巧与操作结果。

在单元格中输入以0开头的数值后前置的0总会自动消失。例如，在单元格输入035后单元格只显示35，在单元格中输入00042后单元格只显示42。

如何才能输入以0开头的数字而不丢失前置的0呢？

解题步骤

让单元格显示前置的0值有两种方法，方法1的步骤如下。

1.选择A1：A10区域，然后按组合键＜Ctrl+1＞弹出“设置单元格格式”对话框。

2.单击“数字”选项卡中的“文本”，表示将单元格设置为文本格式，操作界面如图 3.31所示。

图3.31 设置文本格式

3.单击“确定”按钮保存设置，同时返回工作表界面。

4.在A2单元格；输入数字035，在A4单元格输入0028，如图3.32所示。可以发现A1：A10区域中输入的数值可以保存前置的0，而其他区域则不可以。

图3.32 文本格式的单元格中可以保存前置的0

方法2：不需要做任何设置，只需要输入数字前先输入一个半角状态的撇号即可。例如，原本要输入“026”，改成输入“＇026”即可，Excel自动将撇号隐藏起来，只在单元格中显示“026”。

知识扩展

1.本例中方法1的优点是需要大量输入前面带0的数字时效率更高，不需要多输入一个撇号。方法2的优点是不用设置单元格格式，直接输入数据就好。前者适用于大面积输入数字，后者适用于少量输入的情况。

2.要让数字在单元格中显示前置的 0，本例的两种方法都可以实现，不过不管哪种方法，都是将数值变成文本，只有文本才能显示前置的0，数值无法做到。

3.文本格式的数字不能直接参与求和、求平均等计算，需要转换成数值后再运算。因此对于不需要参与运算的、带有前置0的数字，可以直接使用文本格式，如工号、学号、订单号码等。

4.如果数字右边有文本，如汉字、标点符号或英文字母，那么前置的0可以保留下来，不需要设置单元格的格式，也不需要输入撇号。

5.以文本形式输入数字后，单元格左上角会显示一个绿色的倒三角符号，单击单元格后则再显示一个黄色的惊叹号。假设需要关闭绿色的倒三角符号，只需单击黄色的惊叹号，然后在弹出的菜单中单击“忽略错误”即可。

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

如下图所示的工作表中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群下载文件和视频教材，从而方便练习，同时验证本案例中的技巧与操作结果。

工作中会经常收到同事发来的包含不规范日期值的报表，从而导致后期运算出错。例如“2015.7.15”、“2015年7月15号”、“07.15.2015”、“20150715”等格式都属于不规范的日期值，无法使用日期函数对它们执行运算。是否可以在前期设计表格模板时禁止用户输入不规范的日期？

解题步骤

假设有图3.6所示的销量报表模板，要求对A2：A9区域加以限制，从而提升日期值的准确性，操作步骤如下。

图3.6 销量报表模板

1.选择A2：A9区域。

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

3.将对话框中“允许”下拉列表中的“任何值”修改为“日期”，表示只允许在 A2：A9 区域输入日期。

4.根据实际工作需求对“开始日期”和“结束日期”指定范围。例如，当前工作表只允许输入2015年和2016年的日期，那么就分别输入“2015-1-1”和“2016-12-31”。设置结果如图3.7所示。

5.打开“数据验证”对话框的“出错警告”选项卡，“样式”保持“停止”不变，将“标题”设置为“友情提示”，将“错误信息”设置为“请按标准的日期格式输入，有效范围在2015-1-1到2016-12-31之间”，设置结果如图3.8所示。

图3.7 指定日期范围

图3.8 设置出错警告

6.单击“数据验证”对话框中的“确定”按钮返回工作表界面，在 A2 单元格输入不规范的日期“2015.2.28”，当单击Enter键后将弹出图3.9所示的错误提示信息。

图3.9 输入格式不规范的日期时产生错误提示

7.单击“重试”按钮，继续输入日期“2014-5-7”，Excel会产生相同的提示信息，表示格式有误、范围有误时都禁止操作。

知识扩展

1.“2015.7.15”、“2015年7月15号”、“07.15.2015”、“20150715”等都不是规范的日期，所有日期函数都无法对它们执行日期运算。

例如，Month函数可以计算一个日期属于几月份，图3.10和图3.11两个图片分别展示了错误格式的日期和正确格式的日期的运算结果。

图3.10 错误的日期执行日期运算时出错

图3.11 正常计算出月份

2.正确的长日期有三种格式（排除英文格式），包含“2015-7-15”、“2015/7/15”和“2015年7月15日”。当在单元格中输入最后一种格式的日期时，Excel会自动将它转换成“2015/7/15”格式的日期。

3.日期其实就是数值，只是它呈现出的形态与数值不同而已，本质上是一样的。例如2015年8月8日等同于数值42224，只是显示为日期样式而已。因此日期必须是数值，不管其外观如何变化。假设按文本形式输入日期则无法参与数值运算，对工作带来负面影响。“2015.8.8”就是文本形式，所有日期函数都无法对该值执行日期运算。

4.为什么42224代表2015年8月8日呢？Excel以1900年1月1日作为初始日期，日期1900年1月1日被当作第一天，那么相对于1900年1月0日之后的第42224天则刚好是2015年8月8日。可以如此验证：在A1单元格录入数值42224，然后依次单击功能区的“开始”→“数字格式”→“长日期”，单元格中的42224马上会显示为2015年8月8日，效果如图3.12所示。

图3.12 通过设置格式改变数值的显示形态

在图3.1中，C2：D11区域需要录入数值（E2：E11、C12：E12是公式计算出来的，不需要输入值），由于担心该区域错误地输入了文本而导致后续运算出错，因此要求限制该区域不能输入文本。如何操作才能仅允许C2：D11区域输入数值呢？

图3.1 产量表

解题步骤

对区域进行数据类型限制一般通过设置数据验证来实现，具体步骤如下。

1.选择C2：D11区域。

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

3.在“允许”下拉列表框中将“任何值”改为“整数”，表示此区域禁止输入整数以外的值。

4.由于产量不能是负数，同时不会大于2000，因此将“最小值”设置为0，最大值设置为2000。设置界面如图3.3所示。

图3.2 默认状态的“数据验证”对话框

图3.3 为C2∶D11区域指定验证条件

5.打开“数据验证”对话框的“出错警告”选项卡，“样式”保持“停止”不变，将“标题”设置为“友情提示”，将“错误信息”设置为“不能录入文本，只能录入0到2000之间的数值”，设置结果如图3.4所示。

6.在“数据验证”对话框中单击“确定”按钮返回工作表界面，然后在C11单元格输入字符9oo（后面两位不是数值0而是字母o），当按下Enter键后将弹出图3.5所示的提示框。

图3.4 设置出错警告

图3.5 输入文本时自动产生警告

7单击“重试”按钮，然后重新输入数值900，当按下Enter键后Excel不再弹出任何提示，表示输入的数据符合要求。

知识扩展

1.数据验证在Excel 2010及更早的版本中称为数据有效性，它用于控制区域中允许录入的字符类型，可以限制区域中只能输入某个范围的整数、小数、日期值、时间值，也可以限制区域中只能输入某些具体的单词，其中最强大、灵活的是“自定义”，通过公式可以限制任何类型。

2.数据验证可以理解为单元格的一种属性、一种格式，它可以很轻易地被清除。例如，将一个未设置数据验证的单元格复制到已经设置了数据验证的单元格时，由于复制时默认包含单元格的一切格式信息，因此目标单元格的格式会被覆盖掉，导致单元格的数据验证设置丢失，从而不会报警。换言之，数据验证仅对手工输入数据有效，对复制、粘贴操作无效。

3.对于错误输入时产生提示，Excel提供了两套安全机制：其一是数据验证——位于“数据”选项卡；其二是使用条件格式，其菜单位于“开始”选项卡。两者的区别是数据验证有强制性，数据错误时会弹出提示对话框从而中断操作，而条件格式仅通过单元格颜色、边框或字体颜色来提醒用户，用户可以完全忽略这些提示而继续操作。因此通俗地讲，如果只想获得通知信息则采用条件格式；如果需要在接收到提示信息后必须做出响应才能消除该信息，则宜采用数据验证。

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