分类：Excel 2016实用技巧大全

下图中的语文最高成绩为120分，其他所有成绩最高分为100分。为了提示用户正确的有效成绩范围，要求用户选中单元格时自动弹出提示信息。如何才能实现此需求呢？

解题步骤

使用批注可以实现用户将鼠标指针指向单元格时产生提示信息，而使用数据验证可以让用户单击单元格时产生提示信息。具体操作步骤如下。

1.选择B2：B11区域，单击功能区的“数据”→“数据验证”，弹出“数据验证”对话框。

2.在“设置”选项卡中将“允许”下拉列表的默认值“任何值”修改为“整数”，然后将整数的最小值和最大值分别设置为“0”和“120”，操作界面如图3.50所示。

图3.50 设置数据验证的条件和取值范围

3.打开“输入信息”选项卡，按图3.51的方式输入提示信息的标题和内容。

图3.51 指定提示信息

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

5.选中B2：B11区域的任意单元格，Excel会产生如图3.52所示的提示信息。

6.重复步骤 1、2、3、4，选择 C2：G11 区域，并将数据验证的数值范围限制在 0～100，同时相应地修改提示信息，当选中C2：G11区域的单元格后可以产生如图3.53所示的提示信息。

图3.52 选中B2∶B11区域的单元格时产生的提示

图3.53 选中B2∶B11区域的单元格时产生的提示

知识扩展

1.数据验证可以单独设置提示信息和数据范围/数据来源，不过两者同时设置更人性化。例如，只提示范围却不限制范围，那么仍然有可能输入错误。

2.数据验证设置也属于单元格的一种格式信息，可以复制。如果对 A1 设置了数据验证，那么将A1复制到C5后，C5也拥有了和A1单元格一样的数据验证。

3.如果想知道活动工作表中哪些单元格设置了数据验证，那么可以按组合键＜Ctrl+G＞，弹出定位对话框，然后单击“定位条件”，并按图 3.54 所示的方式设置选项，当单击“确定”按钮后就可以选中所有设置了数据验证的单元格。本例中B2：G11区域中设置了数据验证。

图3.54 定位所有设置了数据验证的单元格

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

在百度搜索网站中输入搜索关键词时，百度会逐步弹出相关的词条列表。例如，输入“三”会弹出包含“三国杀”、“三维度”之类的词条列表，而输入“三星”时则弹出包含“三星S6”、“三星手机官网”之类的词条列表。

Excel的单元格是否也可以在输入字符时逐步提示呢？以图3.42所示的订单表为例，在“客户名称”工作表的A1：A23中有23个客户的详细名称，其中多数客户名称的前几个字符都是相同的，现要求在订单表的A列输入客户名称时可以产生带联想功能的下拉菜单。

图3.42 订单表

解题步骤

在疑难39中，使用数据验证让单元格产生了下拉列表，若修改一下验证条件和数据来源，则可以让下拉列表匹配当前已输入的字符，从而实现“联想”功能，具体步骤如下。

1.选择订单表的A2：A1000区域，然后单击功能区的“数据”→“数据验证”，弹出“数据验证”对话框。

2.将“允许”下拉列表中的默认值“任何值”修改为“序列”，然后在“来源”文本框中输入以下公式：

图3.43为数据验证的设置界面。

3.打开“出错警告”选项卡，取消选择“输入无效数据时显示出错警告”复选框，操作界面如图3.44所示。

图3.43 设置数据验证的条件和来源

图3.44 取消出错警告

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

5.在A2单元格录入字符“东”，然后单击单元格右方的倒三角符号，Excel会弹出“东”字开头的所有客户名称，效果见图3.45。

6.继续输入“东莞万”，然后再次单击倒三角符号，Excel会弹出以“东莞万”3字开头的所有客户名称，效果如图3.46所示。

图3.45 显示“东”开头的词条列表

图3.46 显示“万莞万”开头的词条列表

7.在列表中选择第3项，单元格会自动产生“东莞万江税务局”。

知识扩展

1.在实现本例的功能前，客户名称工作表中的所有名称必须预先排序。不排序的前提下无法显示完整的词条列表。

2.OFFSET 函数的功能是以指定的单元格为参照系，通过上、下、左或右执行一定量的偏移后产生新的区域引用，区域的大小可以通过第3参数和第4参数控制。其中第3参数和第4参数的默认值是1，忽略参数的值则表示1行、1列。

=OFFSET（B2，2，3，3，1）——此公式表示相对于B2单元格向下偏移2行、向右偏移3列从而形成的3行1列的区域，即E4：E6区域。

MATCH函数的功能则是搜索一个字符串在一组字符串或一行、一列中的出现顺序，要搜索的字符串允许包含通配符“*”和“？”，其中“*”代表任何长度的任意字符，“？”代表长度为1的任意字符。例如公式：

=MATCH（＂成都＂，{＂重庆＂，＂广州＂，＂北京＂，＂成都＂，＂昆明＂}，FALSE）——计算结果是4，因为成都二字排列在“{＂重庆＂，＂广州＂，＂北京＂，＂成都＂，＂昆明＂}”这个数组中的第4位。

=MATCH（＂长*＂，{＂重庆市＂，＂广州市＂，＂长沙市＂，＂成都市＂，＂长葛市＂}，FALSE）——计算结果是3，参数“长*”表示以“长”开头的任意长度、任意字符结尾的字符串，数组“{＂重庆市＂，＂广州市＂，＂长沙市＂，＂成都市＂，＂长葛市＂}”的长沙市和长葛市都符合条件，但是此时MATCH函数只以第一个目标为准，即长沙市所在的位置。

COUNTIF 函数用于计算一个区域中有多少个符合条件的单元格。例如，在图 3.47 中COUNTIF函数用于计算A1：A5区域包含多少个以“长”字开头、任意长度的字符结尾的单元格数量，计算结果为2，其中A3和A5单元格符合条件。

图3.47 COUNTIF函数功能示意图

本例公式的含义是：以 A1 为参照点，以活动单元格的值（A2 属于相对引用，代表活动单元格，而非仅指代 A2 单元格本身，在哪个单元格中输入，A2 就代表哪一个单元格）在客户名称工作表的A1：A23区域中的位置减1作为行偏移量，以0作为列偏移量，以“客户名称”工作表的A1：A23区域中包含活动单元格的值的数量作为行数从而形成的区域。

以上解释颇为复杂，通过图片可以更快捷地了解本例公式。

为了方便查看，特将公式写在客户名称工作表中，原来公式中的A2修改为C2。完整公式如下：

=OFFSET（客户名称！$A$1，MATCH（C2&＂*＂，客户名称！$A$1：$A$23，0）-1，0，COUNTIF（客户名称！$A$1：$A$23，C2&＂*＂））

图3.48中包含OFFSET函数的参数解释，以及OFFSET的引用结果。

图3.48 本例公式详解

3.设置了数据验证后，默认状态下输入的值不符合需求时会弹出错误警告，但本例需要在输入部分字符后调出下拉列表，因此需要禁用错误警告。将“输入无效数据时显示出错警告”复选框即表示禁用错误警告。

4.本例公式中采用“A2&*”作为MATCH函数的搜索条件和COUNTIF函数的计数条件，因此OFFSET引用的对象是以 A2的值开头、其他任意字符结尾的单元格。不能将本例公式理解为包含引用A2的值的所有单元格，“*&A2&*”才是包含关系。

5.数据验证无法做到输入过程中自动弹出下拉列表，只在单击倒三角箭头时才会弹出。

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

下图中的生产表中需要输入大量的姓名，而且每天都输入相同的一批姓名。但是经常会在输入姓名时产生手误，如将五笔编码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 通过设置格式改变数值的显示形态