|
使用Excel 的高级功能
条件格式、数据有效性、工作表分级显示和合并计算、链接和超链接、使用语音引擎等，这些功能极大地加强了Excel处理电子表格数据的能力，使用户能够更轻松地应对自己的工作。同时，这些功能非常易于使用，读者无需花很多时间就能够快速掌握它们。
３２．１ 条件格式的设置
要对某个单元格或单元格区域设置条件格式，可按照下面的步骤进行操作：
步骤１．选定目标单元格或单元格区域。
步骤２．单击菜单“格式”——“条件格式”，弹出“条件格式”对话框。
步骤３．在“条件格式”对话框的“条件下（１）”下拉列表框中选择一种条件类型（单元格数值或公式）。
步骤４．设置具体的条件。步骤３选择不同，条件设置也会有所不同，条件可以是常量、单元格引用或公式。
步骤5.单击“格式”按钮，Excel会弹出应用于条件格式的“单元格格式”对话框，在对话框可设置条件为真时要应用的格式。
如果要添加条件格式，在“条件格式”对话框中，单击“添加”按钮，重复步骤３～步骤５。
步骤５．单击“确定”按钮。
当用户添加了多个条件格式时，Excel会按顺序进行判断，如果所有条件都不满足，则不应用任何格式。
如果有一个以上的条件同时被满足时，则只应用顺序在先的条件所对应的格式。如一个单元格设置了３个条件格式，条件１为大于１００，条件２为大于８０，条件３为大于６０；则输入数值大于１１０时，３个条件都被满足，则只应用条件１设置的格式。因此，设置多条件的条件格式时，要充分考虑各条件之间的设置顺序。
３２．２ 设置单元格数值条件
当条件格式的条件类型为单元格数值时，只能以单元格的数据为基准与其他数据（可以是常量、单元格引用甚至是公式结果）对比。功能上有一定的局限性，但使用比较简单。设置单元格数值条件时有以下几个判断选项：
１．介于
２．未介于
３．等于
４．不等于
５．大于
６．小于
７．大于或等于
８．小于或等于
如果条件中需要在文字之间进行对比，或在文字与数字之间对比，实际是按照它们的系统编码值来进行对比。其顺序由小到大分别为数字、英文字母、汉字。当汉字之间进行对比时，系统默认为按照它们的拼音字母大小来对比。
３２．２．１ 介于与未介于
介于与未介于用来判断单元格的数值是否在某一区间内，如果条件成立，即单元格的数值介于或未介于某一区间内，则应用条件格式中设置的格式。
示例３２．１ 标识成绩表格中所有６０～７９分的分数
把学生成绩在６０～７９分的单元格用灰色－２５％的单元格底纹标识出来。
步骤１．选定单元格区域B2：D11，单击菜单“格式”——条件格式。
步骤２．在“条件格式”对话框的“条件１（１）”下拉列表框中选择“单元格数值”，在其右侧的下拉列表框中选择“介于”，在两个文本框中分别输入数字６０和７９。
步骤３．单击“格式”按钮，以弹出的“单元格格式”对话框中选择“图案”选项卡，选择颜色为灰色－２５％。
步骤４．单击“确定”按钮关闭“单元格格式”对话框，再单击“条件格式”对话框的“确定”按钮关闭“条件格式”对话框。
示例３２．２ 标识ａ或ｂ开头的英语单词
把英语单词表中以 ａ或ｂ开头的单词用粉红色底纹标识出来。
步骤１．选定单元格区域，单击菜单栏的“格式”——条件格式。
步骤２．在“条件格式”对话框的“条件１（１）”下拉列表框中选择“单元格数值”，在右侧的下拉列表框中选择“介于”，然后分别在两个文本框中输入“ａ”和“ｂ中”。
步骤３．单击“格式”按钮，在弹出的“单元格格式”对话框中选择“图案”选项卡，选择颜色为粉红色。
步骤４．单击“确定”按钮关闭“单元格格式”对话框，再单击“条件格式”对话框的“确定”按钮关闭“条件格式”对话框。
“未介于”与“介于”条件正好相反（除非单元格为错误值），如例１的学生成绩表，因开家长会的需要，要把成绩小于60，与成绩大于等于80的学生标识出来，则可设置为未介于60到79，在此不再赘述。
注意：本例用介于“ａ”到“ｂ中”来判断开头字母是否为ａ或ｂ，是因为任何以ｂ开头的单词都小于字符串“ｂ中”。如“bzz”与“ｂ中”对比，第1个字符同为“b”，则对比第2个字符“z”和“中”，因为汉字大于英文字母，所以“中”大于“z”，因此“ｂ中”大于“bzz”。
３２．２．２ 等于与不等于
等于与不等于用来判断单元格的数值等于或不等于目标值，如果条件为真，则应用条件格式中设置的格式。
示例３２．３ 标识指定的行政区单元格
Ａ列单元格是中国省份名称表，在单元格E1输入省份名称，要求标识出A列中与E1相同的单元格，并将单元格填充灰色-25%。
步骤１．选定Ａ列，单击菜单栏的“格式”——条件格式。
步骤２．在“条件格式”对话框的“条件1（1）”下拉列表框中选择“单元格数个”，在右边的下拉列表框中选择“等于”，单击文本框，再用鼠标单击E1单元格，文本框中自动填上“=$E$1“）。
步骤３．单击“格式”按钮，在弹出的“单元格格式”对话框中选择“图案”选项卡，选择颜色为灰色－２５％。
步骤４．单击“确定”按钮关闭“单元格格式”对话框，再单击“条件格式”对话框的“确定”按钮关闭“条件格式”对话框。
“不等于”与“等于”条件相反，当“等于”条件为真时，“不等于”条件就为假；“等于”条件为假时，“不等于”条件就为真，只有一种情况例外，就是单元格数据为错误值，本书不再举例说明。
３２．２．３ 大于与小于
大于与小于用来判断单元格的数据大于或小于某个数据，如果条件为真，则应用条件格式中设置的格式。
示例３２．４ 标识出销售业绩大于１００的记录
步骤１．选定单元格区域，单击菜单栏的“格式”——“条件格式”。
步骤２．在“条件格式”对话框的“条件1（1）”下拉列表框中选择“单元格数值”，右侧的下拉列表框中选择“大于”，文本框中输入“100”。
步骤３．单击“格式”按钮，在弹出的“单元格格式”对话框中选择“图案”选项卡，选择颜色为灰色－２５％。
步骤４．单击‘确定’按钮关闭“单元格格式”对话框，再单击“条件格式”对话框的“确定”按钮关闭“条件格式”对话框。
大于与小于条件相反，当大于条件为真时，小于条件就为假；大于条件为假时，小于条件就为真。只有一种情况例外，即单元格数据为错误值。
３２．２．４ 大于或等于
大于或等于用来判断单元格的数据大于或等于某个数据。
示例３２．５ 分别用两种颜色标识成绩表格中60~79分和80分以上的分数
步骤１．选定单元格区域，单击菜单栏的格式——条件格式。
步骤２．单击“条件格式”对话框中的“添加”按钮，在“条件２（２）”下拉列表框中选择“单元格数值”，在右侧的下拉列表框中选择“大于或等于”，文本框中输入80。
步骤３．单击“格式”按钮，在弹出的“单元格格式”对话框中选择“图案”选项卡，选择颜色为黄色。
步骤４．单击“确定”按钮关闭“单元格格式”对话框，再单击“条件格式”对话框的“确定”按钮关闭“条件格式”对话框。
３２．２．５ 小于或等于
小于或等于用来判断单元格的数据小于或等于某个数据，如果条件为真，则应用于该格式。该用法与大于或等于相同，本书不再赘述。
３２．３ 设置公式条件
当条件格式的条件类型为公式时，可以处理比较复杂的条件，而且条件既可以与本单元格数值有关，也可以与本单元格数值无关，下面举例说明。
示例３２．６ 在成绩青史标识出总分低于８０的行
步骤１．选定单元格区域，单击菜单栏的“格式”——“条件格式”。
步骤２．在“条件格式”对话框匠“条件１（１）”下拉列表框中选择“公式”，在文本框中输入“＝$G3<80"。
步骤３．单击“格式”按钮，在弹出的“单元格格式”对话框中选择“图案”选项卡，选择颜色为茶色。
步骤４．单击“确定”按钮关闭“单元格格式”对话框，再单击“条件格式”对话框的“确定”按钮关闭“条件格式”对话框。
在这个例子中，除了Ｇ列单元格的条件与单元格本身数值有关，其他单元格都与本单元格的数值无关，如姓名列，无论姓甚名谁，只要考核成绩小于８０分，就要应用条件格式中所设置的格式。
注意：公式“＝$G3<80"中使用了行相对列绝对的混合引用，当公式复制到其他单元格时，列的位置不改变，都绝对引用Ｇ列单元格，行位置随单元格位置的改变而相对改变，比如A3的条件格式公式“＝$G3<80"复制到E5，则公式变为“＝$G5<80"。
示例３２．７ 自动为表格中新增的记录添加边框线
是一个还未设置边框的人才信息采集表，内容随时会增加，为了打印出来的表格能够清晰，必须为数据区域添加边框，而非数据区域不要边框。为了能在增加记录的同时自动为新记录加上边框，可以使用条件格式来解决，操作步骤如下。
步骤１．选定单元格区域A2：F100（范围大小可根据用户必须使用的行数多少而调整），保持活动单元格A2，单击菜单栏的“格式”——“条件格式”。
步骤２．在“条件格式”对话框中的“条件下（1）”下拉列表框中选择“公式”，在右侧的文本框中输入“$A2<>”“”。
步骤３．单击“格式”按钮，在弹出的“单元格格式”对话框中选择“边框”选项卡，单击“预置”下的“外边框”。
步骤４．单击“确定”按钮关闭“单元格格式”对话框，再单击“条件格式”对话框的“确定”按钮关闭“条件格式”对话框。
现在，当用户在Ａ列输入姓名后，Excel会自动为该行表格添加边框，如果清除A列某一单元格的姓名，Excel也会自动把该行的边框线清除。
示例３２．８ 标识数据列中重复的数据
是一份仓库新产品进出库结存表，为了使产品编号不重复输入，可以设置条件格式，使重复输入的编号单元格以粉红色底纹填充。
步骤１．选定Ｂ列，单击菜单栏的“格式”——“条件格式”。
步骤２．在“条件格式”对话框的“条件１（１）”下拉列表框中选择“公式”，在文本框中输入“＝COUNTIF
（B：B，B1）>1”。
步骤３．单击“格式”按钮，在弹出的“单元格格式”对话框中选择“图案”选项卡，选择颜色为粉红。
步骤４．单击“确定”按钮关闭“单元格格式”对话框，再单击“条件格式”对话框的“确定”按钮关闭“条件格式”对话框。
３２．４定位、复制和删除条件格式
条件格式可以被复制并粘贴到别的单元格区域，也可以被用户删除。
３２．４．１复制条件格式
包含条件格式的单元格被复制时，条件格式会被一同复制。如果只需要复制单元格的条件格式以应用到其他单元格区域而不需要单元格抽内容，可利用选择性粘贴的相关特性或格式刷来实现。
示例３２．９复制条件格式
如图所示的产品销售表，不同仓库的产品编号不能重复，成都百盛的产品编号已设置了条件格式来标识重复的编号，现在要把Ｂ列的条件格式复制到北京当代仓库的产品编号Ｆ列，有以下两种方法可以实现。
选择性粘贴方法 步骤１．复制Ｂ列。
步骤２．选定Ｆ列，单击菜单栏的“编辑”——“选择性粘贴”，Excel会自动弹出“选择性粘贴”对话框。
步骤３．在“选择性粘贴”对话框中的“粘贴”选项里选择“格式”选项按钮，再单击“确定”按钮。
格式刷方法
步骤１．选定Ｂ列。
步骤２．单击“格式”工具栏上的“格式刷”按钮，然后选择要设置条件格式的单元格区域Ｆ列，结果与选择性粘贴方法一样。
注意：使用以上两种方法，不但复制了条件格式，也复制了所有的单元格格式。
３２．４．２ 删除条件格式
如果用户不再需要使用单元格中的条件格式，可以对其进行删除。
示例３２．１０
仍以示例３２．９的图所示为例，清除A1：B6的条件格式的步骤如下。
步骤１．选定Ａ1：B6。
步骤２．单击菜单“格式”——“条件格式”。
步骤３．单击“条件格式”对话框的“删除”按钮，弹出“删除条件格式”对话框。
步骤４．在“删除条件格式”对话框中勾选要删除的条件复选框。被勾选的条件格式将疲取消，其余的条件如果已存在，将继续保留。
步骤５．单击“确定”按钮关闭“删除条件格式”对话框，再单击“条件格式”对话框的“确定”按钮，关闭“条件格式”对话框。
注意：按<Delete>键来清除单元格内容时，与利用菜单栏执行“编辑”——“清除”——“内容”结果一样，都只删除了单元格的内容，其格式仍被保留。如果要清除单元格的所有格式（包括边框、字体、底纹等），可选定单元格，然后在菜单栏执行“编辑”——‘清除’——“格式”命令。
３２．４．３ 定位到包含条件格式的单元格
若要选定工作表中含有条件格式的单元格，可以用定位的方法来选定。
定位包含条件格式的单元格有两种结果，一是定位到与活动单元格具有相同条件的条件格式单元格；二是定位包含任何条件的条件格式单元格。
若要定位包含任何条件的条件格式单元格，请按以下步骤执行。
步骤１．单击菜单栏的“编辑”——“定位”（也可以按<Ctrl+G>组合键或功能键<F5>），Excel会自动弹出“定位”对话框。
步骤２．单击“定位”对话框的“定位条件”按钮，在弹出的“定位条件”对话框单击“条件格式”单选按钮。
步骤３．单击“确定”按钮。
若要定位到与活动单元格具有相同条件的条件格式单元格，请在步骤２中的“定位条件”对话框中单击“条件格式”和“相同’单选按钮。
３２．５条件格式高级应用示例
充分利用条件格式中的公式，可以设置各种通常情况下难以实现的效果，以满足用户的更多要求。本节将详细说明公式对本工作表的引用和公式对其他工作表的引用两种情况。
３２．５．１公式对本工作表的引用
示例３２．１１标识最近５天内没有完成的任务项目
如图任务完成情况表，要求Ａ列日期在今天（假设今天为２００７年４月２日）之前的５天内，并且Ｃ列为“Ｎ”的行，用灰色－２５％字体显示出来。
步骤１．选定单元格区域A2：C10，单击菜单栏的“格式”——“条件格式”。
步骤２．在“条件格式”对话框的“条件１（１）”下拉列表框中选择“公式”，在右侧的文本框中输入“＝AND（TODAY()$A2,TOD
AY()-$A2<=5,$C2="N")"。
步骤３．在弹出的“单元格格式”对话框中选择“字体”选项卡，在颜色下拉框选择灰色－２５％。
步骤４．单击“确定”按钮关闭“单元格格式”对话框，再单击“条件格式”对话框的“确定”按钮关闭“条件格式”对话框。
示例３２．１２使用不同填充颜色区别表格中不同部门的记录
如图所示的员工名单，部门列中，只要相邻的部门不同，就给该行填充不同颜色的底纹。
步骤１．选定单元格区域A2：G20，单击菜单栏的“格式”——“条件格式”。
步骤２．在“条件格式”对话框的“条件１（１）”下拉列表框中选择“公式”，在右边的文本框中输入“＝MOD(SUM(N($B$1:$b1<>
$b$2:$b2)),2)"。
步骤３．单击“格式”按钮，在弹出的“单元格格式”对话框中选择“图案”选项卡，选择颜色为茶色为，再单击“确定”按钮。
步骤４．单击“添加”按钮，重复步骤２和步骤３，文本框公式改为“＝MOD(SUM(N($B1:$B1<>$B$2:$B2)),2)=0”，颜色改用浅绿色。
步骤５．单击“确定”按钮。
３２．５．２ 公式对其他工作表的引用
设置条件格式，如果条件公式要引用其他工作表的数据，可以采用两种方法来解决，即使用名称引用和使用函数引用。
１．使用名称引用
使用名称引用是指把要引用的单元格区域定义为名称后，在条件格式公式中，以名称代替引用的单元格地址。
３２．１３ 标识会计账簿输入错误的会计科目名称或编号
如图是标准的会计科目名称与编号表
是会计账簿中的期末余额，为检测账簿的科目名称与编号是否正确并且是一一对应的，可以按以下步骤设置条件格式。
步骤１．单击菜单栏的“插入”——名称——定义（或按Ctrl+F3组合键），显示“定义名称”对话框。
步骤２．在“定义名称”对话框中“在当前工作簿中的名称”下的文本框中输入一个名称，如“科目”，单击“引用位置”下的文本框，鼠标单击会计科目表的工作表标签，以激活会计科目表，选定B4：B26，输入"&"，再选定C4：C26，Excel会自动在文本框中输入“=会计科目表！$B$4:$B$26&会计科目表！C$4:$C$26"，单击“确定”按钮，完成名称的定义。
步骤３．选定会计账簿所在工作表的单元格区域A2：C15，单击菜单栏的“格式”——“条件格式”。
步骤４．在“条件格式”对话框的“条件１（１）”下拉列表框中选择“公式”，在右边的文本框中输入“＝ISNA（MATCH（$A2&$B2,科目，0））“。
步骤５．单击“格式”按钮，在弹出的“单元格格式”对话框中选择“图案”选项卡，选择颜色为茶色。］
步骤６．单击“确定”按钮关闭“单元格格式”对话框，再单击“条件格式”对话框的“确定”按钮关闭“条件格式”对话框。
从图中可以看出Ａ２的编号错误，会计科目表中并没有编号为1234的科目；B7把编号为1141的“坏账准备”写成了“坏账准备”；B12把编号为1009的“其他货币资金”写成了“其他货币资产”，因此，这3行被填充了茶色底纹。
２．使用函数引用
使用函数引用，可以用INDRECT函数，把以文本串表示的单元格地址转换为单元格引用。就是在工作表名后加上一个英文感叹号，再加上单元格地址，如引用Sheet1表的A列，写成INDIRECT（"Sheet1!A:A");A引用Sheet2表的B3：E10，写成INDIRECT（“Sheet2！B3：E10”）；引用Sheet3表的第3行到第5行，写成INDIRECT（“Sheet3！3：5”）。
示例３２．１４ 标识会计账簿中输入错误的会计科目名称或编号
示例３２．１３的图的例子为例，来说明相同条件下如何使用INDIRECT函数来达到与定义名称相同的效果。
步骤１．选定会计账簿所在工作表的单元格区域Ａ２：Ｃ１５，单击菜单栏的“格式”——“条件格式”。
步骤２．在“条件格式”对话框的“条件１（１）”下拉列表框中选择“公式”，在右边的文本框中输入“＝ISNA（MATCH（$A2&$B2
,INDIRECT("会计科目表！B4：B26)&INDIRECT("会计科目表！C4：C26"),0))"。
步骤３．单击“格式”按钮，在弹出的“单元格格式”对话框中选择“图案”选项卡，选择颜色为茶色。
步骤４．单击“确定”按钮关闭“单元格格式”对话框，再单击“条件格式”对话框的“确定”按钮关闭“条件格式”对话框。
３２．６条件格式与单元格格式的优先顺序
假如一个单元格里既设置了条件格式，又设置了自定义数字格式，还设置了字体格式，则它们的优先显示顺序依次为条件格式——自定义数字格式——单元格格式，下面举例说明。
示例３２．１５ 条件格式与自定义数字格式同时使用
在Ａ１：Ａ４分别输入50、70、90、文本，把单元格字体设置为灰色-50%。]
选定Ａ１：Ａ４单元格，单击菜单栏的“格式”——“单元格”（或按Ctrl+1组合键），在“单元格格式”对话框中单击“数字”选项卡，在“分类”框中选择“自定义”，“类型”框中输入“[蓝色][>60]0:G/通用格式；@”（不含外面双引号，意思是大于60的数字字体颜色为蓝色），单击“确定”按钮。
结果大于６０的数字７０、９０字体颜色为蓝色，其他字体颜色为灰色－５０％，说明自定义数字格式优先于单元格格式。
再选定Ａ１：Ａ４单元格，单击菜单栏的“格式”——“条件格式”。在“条件格式”对话框的“条件１（１）”下拉列表框中选择“单元格数值”，在右边的下拉列表框中选择“大于”，在右边文本框中输入值８０，单击“格式”按钮。在弹出的“单元格格式”对话框中选择“字体”选项卡，在“颜色”下拉框中选择灰色－２５％，单击“确定”按钮关闭条件格式对话框。再单击“确定”按钮关闭“条件格式’对话框。
数字９０既符合自定义的数字格式，又符合条件格式的条件，但因条件格式优先于自定义数字格式，所以显示为灰色－２５％。另外，文本显示为灰色－２５％是因为它符合条件格式的条件，文本大于任何数字。
３２．７条件格式的限制
条件格式与其他功能一样，都有一定的限制，如果不了解具体有哪些限制，会使用户陷入迷途。
３２．７．１ 对引用的限制
Excel条件格式中的引用，只能是对本工作表单元格区域的引用，不能直接引用其他工作簿或工作表单元格区域。如果一定要引用本工作表以外的区域，必须用定义名称或使用INDIRECT函数来引用。
条件格式也不能使用联合、交叉引用或数组常量，如Ｂ列是学生成绩，要把成绩在６０～８０分（不含８０分）之间的成绩用蓝色字体显示，条件格式公式不能写成“＝SUM(B1>={60;80})*{1;-1}0"，因为这里使用了数组常量（{60；80}、{1；-1}），可以用AND来判断，公式为
“=AND(B1>=60;B1<80)”。
３２．７．２ 对条件个数的限制
Excel的条件格式中，可用的条件最多只能有3条。
如果要应用超过了３条的条件格式，可以借助ＶＢＡ编写相应的程序来解决。
３２．７．３ 对格式的限制
在条件格式中能够设置的单元格格式，并非Excel单元格格式的全部。
在条件格式中的“单元格格式”对话框的“字体”选项卡中“字体”、“字号”、“上标”、“下标”为灰色，说明这些选项是不适用于条件格式的。
如果读者单击“边框”选项卡，还可以看到，在“边框”选项卡里，可应用的“线条样式”也只有６种。
此外，条件格式中的“单元格格式”对话框中包含一个“清除”按钮，可供用户快速删除现有的格式以便重新设置。
３２．７．４ 其他限制
使用条件格式功能所得到的单元格格式，无法利用Excel的查找与替换功能来定位，这个特性与普通单元格格式是完全不同的。
第三章 数据有效性
使用Excel的数据有效性功能，可以对输入单元格的数据进行必要的限制，并根据用户的设置，禁止数据输入或让用户选择是否继续输入数据。
例如，可限制一份名单表格中的性别列中只能输入“男”或“女”，而身份证号码列中只能输入１５位或１８位数字或字母等。
３３．１ 数据有效性的设置
要对某个单元格或单元格区域设置数据有效性，可按照下面的步骤进行操作。
步骤１．选定目标单元格或单元格区域。
步骤２．单击菜单“数据”——“有效性”，Excel会自动弹出“数据有效性”对话框。
步骤３．单击“设置”选项卡，在“允许”下拉列表框中，选择一个选项，如果要输入公式，请选择“自定义”选项。
步骤４．在“数据”下拉列表框选择设定条件，根据步骤３的选择不同，用户可以访问的控件也会有所不同。
步骤５．单击“确定”按钮。
通过上述步骤后，选定的单元格区域就设置了用户指定的有效性条件。
数据有效性其他选项的功能如下。
“输入信息”选项卡
用户选定单元格时出现信息框，类似批注的功能，前提是勾选“选定单元格时显示输入信息”单选按钮（默认为勾选），但如果“标题”和“输入信息”不输入内容，则不出现消息框（默认为空），可作为另类批注使用。
例如，选定Ｂ２单元格，单击菜单“数据”——“有效性”，单击“输入信息”选项卡，输入信息。当用户选定Ｂ２单元格时，无论在“设置”选项卡里设置什么条件，Excel都会出现一个提示信息框。
“出错警告”选项卡
当用户输入不符合数据有效性条件的数据（无效数据）时，Excel显示的对话框内容以及用户是否可以继续输入数据的设置，前提是勾选“输入无效数据时显示出错警告”单选按钮（默认为勾选），样式默认为“停止”，“标题”和“错误信息”默认为空。
用户设置了“出错警告”样式为“警告”，标题为“警告”，错误信息为“性别只能输入“男”或“女”。
当用户输入不符合数据有效性条件的数据时，Excel会自动显示对话框。
“输入法模式”选项卡
该选项卡“模式”下拉列表框中有３个选项，分别为‘随意’、‘打开’和“关闭（英文模式）”。默认为‘随意’，当用户把输入法模式设置为“打开”时，选定该单元格，系统自动打开中文输入法，任务栏中的“语言栏”将显示“中文（中国）”图标；设置为“关闭（英文模式）”时，系统自动关闭中文输入法，任务栏中的“语言栏”将显示“英语（美国）”图标。
选定Ａ列，单击菜单“数据”——“有效性”，单击“输入法模式”选项卡，在“模式”下拉列表框选择“打开”，单击“确定”按钮，用同样的方法，把Ｂ列的输入法模式设置为“关闭”（英文模式）“。
如果用户打开了某种中文输入法，则选定Ａ列的单元格将激活该输入法；选定Ｂ列的单元格将屏蔽输入法，显示“英语（美国）”图标。当用户需要限制某单元格区域不能输入中文时，可以使用此方法，避免重复切换中英文输入法。
３３．２ 数据有效性允许的条件
数据有效性允许设置的条件有以下几种：
１）任何值 ２）整数 ３）小数 ４）序列 ５）日期 ６）时间 ７）文本长度 ８）自定义
３３．２．１ 任何值
选择这个选项，用户可以在单元格中输入任何数据而不受影响，但其他选项卡的设置依旧保留，如果要把所有选项卡的设置都清除，请单击“全部清除”按钮。
３３．２．２ 整数
限制单元格只能输入整数。
示例３３．１限制单元格只能输入月份数
步骤１．选定要设置数据有效性的单元格区域，单击菜单“数据”——“有效性”。
步骤２．单击“设置”选项卡，在“允许”下拉列表框选择“整数”，在“数据”下拉列表框选择“介于”，在“最小值”折叠框中输入１，在“最大值”折叠框中输入１２。
步骤３．如果想选定单元格时出现提示框，单击“输入信息”选项卡，在“输入信息”列表框中输入想显示的文字，如“请输入月份数”，并确保“选定单元格时显示输入信息”已勾选。
步骤４．单击“确定”按钮。
通过设置后，选定的单元格区域就只能输入１～１２的月份数。
３３．２．３ 小数
限制单元格只能输入小数（包括整数）。
示例３３．２ 限制单元格只能输入占比比例
如图所示，是某市各金融机构存款总额的市场份额占比，用户可以限制占比列只能输入一个大于或等于０且小于或等于１的小数（０％～１００％）。
步骤１．选定单元格区域Ｃ４：Ｃ１９，单击菜单“数据”——“有效性”。
步骤２．单击“设置”选项卡，在“允许”下拉列表框选择“小数”，在“数据”下拉列表框选择“介于”，在“最小值”折叠框中输入０，在“最大值”折叠框中输入１。
步骤３．单击“确定”按钮。
３３．２．４序列
用户输入或选择的数据，必须是用户在“来源”折叠框中输入的引用或数据中已存在的数据。
当用户选择“序列”条件，“来源”折叠框中可以直接输入数据，也可以使用单元格引用或公式。
如果用户选择该条件，且勾选“提供下拉箭头”时，当用户选定单元格时，在单元格右侧会自动出现一个下拉箭头；单击下拉箭头，用户可选择其中的项填入单元格，而不必手动输入数据，非常方便，深得广大用户的喜爱。
１．直接输入数据
直接在“来源”折叠框中输入允许单元格输入的所有数据。
示例３３．３ 限制单元格只能输入性别
如图员工花名册所示，用户可以在性别列，利用数据有效性来制作下拉箭头，用以选择“男”或“女”，具体操作如下：
步骤１．选定单元格Ｅ２：Ｅ２８，单击菜单“数据”——‘有效性’。
步骤２．单击“设置”选项卡，在“允许”下拉列表框选择“序列”，在“来源”折叠框中输入“男，女”，并确保已勾选“提供下拉箭头”复选框（默认为勾选）。
步骤３．单击“确定”按钮。
当用户选定E2：E28中的单元格时，在单元格右侧会出现一个下拉箭头按钮，单击下拉箭头按钮（或按Alt+方向下组合键），出现下拉列表，选择一个选项，完成数据输入。
注意：“来源”中的每项内容之间必须用英文半逗号隔开，文本前后不要加引号。
深入了解 下拉箭头不出现的解决方法
如果用户在“设置”选项卡选择条件为“序列”，并且勾选了“提供下拉箭头”复选框，而选定单元格时，却没有出现下拉箭头按钮，请单击菜单“工具”——“选项”，再单击“视图”选项卡，单击“对象”中的“全部显示”单选按钮，再单击“确定’按钮。
２．使用单元格引用
在“来源”折叠框中输入引用的单元格地址。
仍以员工花名册为例，在“文化程度”列设置数据有效性。
步骤１．在一个空列（如Ｍ列）输入各种文化程度。
步骤２．选定单元格区域Ｉ２：Ｉ２８，单击菜单“数据”——“有效性”。
步骤３．单击“设置”选项卡，“允许”下拉列表框选择“序列”，“来源”折叠框中输入“=$M$2:$M$8"),并确保已勾选“提供下拉箭头”复选框。
步骤４．单击“确定”按钮。
３．使用公式
在“来源”折叠框中输入公式。
再以员工花名册为例，在“文化程度”列设置数据有效性。
如果用户为了数据表的整洁，不在本工作表Ｍ列输入各种文化程度，而把各种文化程度输入到本工作簿另一个工作表（如Sheet2）的M2：M8，就不能直接使用单元格引用，必须使用公式来引用，方法与上例基本相同。在步骤3中，折叠框内输入公式"=INDIRECT("Sheet2!M2:M8")"。
３３．２．５日期
限制单元格只能输入日期。
示例３３．４ 限制单元格中只能输入当年日期
在会计账簿中，每个会计年度的日期列，可以限制只能输入当年日期，假设当年为2007年。
步骤１．选定单元格区域，单击菜单“数据”——“有效性”。
步骤２．单击“设置”选项卡，在“允许”下拉列表框选择“日期”，在“数据”下拉列表框选择“介于”，在“开始日期”折叠框中输入“2007-1-1”，在“结束日期”折叠框中输入“2007-12-31”。
步骤３．单击“确定”按钮。
通过设置后，单元格区域就只能接受当年的日期，以防止日期输入错误。
３３．２．６ 时间
限制单元格只能输入时间。
示例３３．５ 员工上、下班签到签退表
如图所示，是某公司员工上、下班签到签退表，该公司上午上班时间为8：00~12：00，下午上班时间为2：00~5：00
公司规定员工必须在规定的时间前15分钟内签到或签退，如上午签到时间在7：45~8：00这段时间。
步骤１．选定单元格区域Ｂ４：Ｂ２６，单击菜单“数据”——“有效性”。
步骤２．单击“设置”选项卡，在“允许”下拉列表框选择“时间”，在“数据”下拉列表框选择“介于”，在“开始时间”折叠框中输入“７：４５”，在“结束时间”折叠框中输入“８：００”。
步骤３．单击“确定”按钮。
步骤４．用同样的方法在Ｃ、Ｄ、Ｅ列的数据有效性中设置不同的时间。
步骤５．把３月１日的有效性复制到３月２日～３月３１日（关于数据有效性的复制，请参阅３３．３．２）
３３．２．７ 文本长度
限制单元格可以输入的数据字符串的长度。
示例３３．６ 手机号码限制
如图所示的通讯录中，为防止手机号码输入错误，用户可以限制单元格只能输入长度为１１位的字符串。
步骤１．选定单元格区域Ｃ２：Ｃ２４，单击菜单“数据”——“有效性”。
步骤２．单击“设置”选项卡，在“允许”下拉列表框选择“文本长度”，在“数据”下拉列表框中选择“等于”
在“长度”折叠框中输入１１。
步骤３．单击“确定”按钮。
当用户输入的数据不是１１位时，Excel会自动弹出一个对话框，警告用户输入了错误的数据。
３３．２．８ 自定义
数据有效性条件可以用公式来设置。以下的例子假设Ａ１为活动单元格，“公式”折叠框中输入不同的公式，有不同的效果。
１．只能输入文本
＝ＩＳＴＥＳＴ（Ａ１）
２．只能输入数字
＝ＩＳＮＵＭＢＥＲ（Ａ２）
３．输入以“Ａ”开头的文本
公式１：＝ＬＥＦＴ（Ａ１）＝“Ａ”
公式２：＝ＦＩＮＤ（“Ａ”，Ａ１）＝１
公式１不区分大小写，如："Abc","abc"均符合条件。
公式２区分大小写，如：“Ａbc"符合条件， "abc"不符合条件。
４．最后两个字为“公司”
＝RIGHT）A1，2）=“公司”
５．第３个字符为“中”
=COUNTIF（A1，“？？中*”）
或
＝MID（A1，3，1）=“中”
３３．３ 定位、复制和删除数据有效性
数据有效性与条件格式一样，可以定位到包含数据有效性的单元格，也可以把设置好的数据有效性复制应用到其他单元格区域或删除已经设置好的数据有效性。
３３．３．１ 定位到包含数据有效性的单元格
若要选定工作表中含有数据有效性的单元格，可以用定位的方法来选定。
定位包含数据有效性的单元格有两种结果，一是定位到与活动单元格具有相同条件的数据有效性单元格；二是定位包含任何条件的数据有效性单元格。
若要定位包含任何条件的数据有效性单元格，请按以下步骤执行。
步骤１．单击菜单栏的“编辑”——“定位”（或按CTRL+G组合键或F5），Excel会自动弹出“定位”对话框。
步骤２．单击“定位”对话框的“定位条件”按钮，在弹出的“定位条件”对话框中单击“数据有效性”单选按钮
步骤３．单击“确定”按钮。
若要定位到与活动单元格具有相同条件的数据有效性单元格，请在步骤２中的“定位条件”对话框中单击“数据有效性”和“相同”单选按钮。
注意：相同条件是指数据有效性对话框中４个选项卡的设置完全一样的条件，若有一个选项卡设置不一样，则不视为相同条件的数据有效性。
３３．３．２复制数据有效性
包含数据有效性的单元格被复制时，数据有效性会被一同复制。如果只需要复制单元格的数据有效性以应用到其他单元格区域而不需要单元格的内容和其他格式，可利用选择性粘贴的相关特性来实现。
仍以员工花名册为例，假设用户设置性别列的数据有效性时，只设置了Ｅ２的数据有效性，现在需要把Ｅ２的数据有效性应用到Ｅ３：Ｅ２８，可使用以下方法。
步骤１．设置Ｅ２数据有效性（先不要选择性别）。
步骤２．选定Ｅ２，把鼠标放到单元格填充柄（单元格右下角的小点）处，当光标显示为“＋”符号时，按住鼠标右键下拉到Ｅ２８单元格，再放开鼠标左键。
通过这样下拖后，Ｅ２的数据有效性就复制到Ｅ３：Ｅ２８。
如果含有数据有效性的单元格与目标单元格不在相同的行或列时，可用选择性粘贴的方法来实现，操作步骤如下：
步骤１．复制含有数据有效性的单元格。
步骤２．选定要应用数据有效性的单元格区域，单击菜单栏的‘编辑’——“选择性粘贴”，Excel会自动弹出“选择性粘贴”对话框。
步骤３．在“选择性粘贴”对话框中的‘粘贴’选项里选择“有效性验证”选项按钮，再单击“确定”按钮。
３３．３．３ 删除数据有效性
如果用户不再需要使用单元格中的数据有效性，可以对其进行删除，本书介绍了３种方法来删除数据有效性，分别是选择性粘贴、全部清除、重新设置。
示例３３．７ 删除数据有效性
如图所示，Ａ１：Ａ１０设置了数据有效性的序列条件，用来输入“一月”～“十二月”。
１．选择性粘贴
步骤１．复制一个没有设置数据有效性的单元格。
步骤２．选定Ａ１：Ａ１０单元格，单击菜单栏的“编辑”——‘选择性粘贴’，Excel会自动弹出‘选择性粘贴’对话框。
步骤３．在“选择性粘贴”对话框中的“粘贴”选项里选择“有效性验证”选项按钮，再单击“确定”按钮。
选择性粘贴后，就删除了数据有效性。
２．全部清除
步骤１．选定Ａ１：Ａ１０单元格，单击菜单栏的“数据”——“有效性”。
步骤２．在“数据有效性”对话框中单击“清除全部”按钮。
步骤３．单击“确定”按钮。
３．重新设置
步骤１．选定Ａ１：Ａ１１单元格（把选定区域扩展到至少包含一个未设置数据有效性的单元格），单击菜单栏的“数据”——“有效性”，Excel会自动弹出一个对话框。
步骤２．单击对话框“否”按钮关闭该对话框。
步骤３．单击“数据有效性”对话框的“确定”按钮。
通过这样设置后，Ａ１：Ａ１０的数据有效性就被删除了。
若选定的单元格含有不止一种的数据有效性，Excel将自动弹出对话框，用户只需单击“确定”按钮，再单击“数据有效性”对话框的“确定”按钮，就可以清除选定区域的数据有效性。
３３．４数据有效性的高级应用示例
利用公式可以设置更为复杂的数据有效性，如防止用户重复输入数据、创建多级下拉菜单等。
３３．４．１手机号码限制
示例３３．８限制手机号码的输入
以通讯录为例，手机号码有１１位数的手机号码，也有８位数的小灵通号码，限制输入的数据只能是０～９的数字，不能输入其他文本，只用“文本长度”作为条件已不能满足用户的需求，必须使用“自定义”条件，用公式来限制。
步骤１．选定单元格区域Ｃ２：Ｃ２４，单击菜单“数据”——“有效性”。
步骤２．单击“设置”选项卡，在“允许”下拉列表框选择“自定义”，在“公式”折叠框中输入“＝AND（ISN
UMBER（C2），OR（LEN（C2）=8，LEN（C2）=11））”。
步骤３．单击“确定”按钮。
当用户输入不符合规定的数据时，Excel会自动出现对话框，警告用户输入了错误的数据。
３３．４．２ 杜绝重复输入数据
用户在输入某些数据时，如果希望输入的数据不能重复，可以使用数据有效性来限制。
１．本工作表不能重复输入数据
示例３３．９限制商品名称不能重复输入
如图所示的库存表，用户如果不小心把某个商品名称重复输入，就会造成结算错误。利用Excel的数据有效性功能，可以有效地解决这个问题，具体操作如下。
步骤１．选定单元格Ｂ２，按住鼠标左键向下拖拉以选定区域Ｂ２：Ｂ１９，单击菜单“数据”——“有效性”。
步骤２．单击“设置”选项卡，“允许”下拉列表框选择“自定义”，“公式”折叠框中输入“＝COUNTIF（B：B，B2）=1”。
步骤３．单击“确定”按钮。
通过上述步骤设置数据有效性后，Ｂ列商品名称已不能重复输入，确保库存表商品名称的唯一性。
２．工作簿内所有工作表不能重复输入数据
示例３３．１０限制输入在其他工作青史已存在的项目
如图所示的工作簿，其中每个工作表的格式都相同，用来记录当月申请了公积金贷款的员工。公司规定，每个员工公积金贷款一年最多只能申请一次。为了执行这样的规定，每位员工的姓名只允许在整个工作簿中出现一次。要设置这样的数据有效性，操作步骤如下。
步骤１．单击菜单“插入”——“名称”——“定义”（或按CTRL+F3组合键）。
步骤２．在“定义名称”对话框中，定义３个名称，分别为"shtmame","index"和 "shtlist",对应的引用位置依次为"=
REPLACE（GET。WORKBOOK（1），1，FIND{"]",GET.WORKBOOK(1)),)&T(NOW())","GET.DOCUMENT(87)+NOW()*0" "LOOKUP(ROW(INDIRECT("1:"&index)),ROW(INDIRECT("1:"&COUNTA(shtname))),shtname)"
步骤３．这３个名称依次取得工作簿中所有工作表名的水平数组、本工作表在工作簿中的位置和工作簿中第１个工作表到本工作表之间的所有工作表名的垂直数组。
步骤４．选定工作表１月的单元格区域Ａ２：Ａ６５５３６（选定单元格Ａ２，按CTRL+SHIFT+向下键组合键；或直接在名称框中输入A2：A65536，再按Enter键），单击菜单“数据”——“有效性”。
注意：本示例定义名称中，由于名称公式中相对引用了行，所以定义名称前，一定要把光标放在第２行。
步骤５．单击“设置”选项卡，在“允许”下拉列表框选择“自定义”，在“公式”折叠框中输入“＝SUM（CO
UNTIF（INDIRECT（shtlist&"!A:A"),A2))=1"，取消“忽略空值”复选框的勾选，勾选“提供下拉箭头”复选框。
步骤６．单击“确定”按钮。
步骤７．重复步骤４～步骤６，把其他１１个月工作表的Ａ２：Ａ６５５３６也设置相同的数据有效性，用户也可以用复制——选择性粘贴的方法设置其他工作表的数据有效性。
若工作表尚未输入其他内容，可用填充的方法设置其他工作表的有效性。方法是：右击工作表１月的标签，在右键快捷菜单中选择“选择全部工作表”，点击菜单“编辑”——“填充”——“至同组工作表”，在弹出的“填充成组工作表”对话框选择“全部”单选按钮，再单击“确定”按钮。
通过上述操作后，已完成了用户所需的数据有效性，当用户在２月工作表Ａ２的单元格输入１月已有的姓名，如“李婷婷”，Excel会自动弹出警告框，阻止用户继续输入。
注意：本例中使用函数GET。WORKBOOK（1）和GET.DOCUMENT（87）是宏表4.0函数，有关此函数的更多内容，请参阅第22章。
３３．４．３ 创建多级下拉列表
已经介绍了利用数据有效性在工作表中创建下拉列表的功能，但事实上，用户可以根据需要创建多级联动的下拉列表，而不仅仅单级的列表。
１．创建两级下拉列表
示例３３．１１ 商品种类及名称的两级联动选择
如图所示，在Ａ～Ｃ列保存了一份商品的种类及商品的具体名称资料，现在希望能够实现在Ｅ列选择商品种类，在Ｆ列选择只属于相应种类中的商品的效果。
步骤１．选定单元格区域Ｅ２：Ｅ１８，单击菜单“数据”——“有效性”。
步骤２．单击“设置”选项卡，在“允许”下拉列表框选择“序列”，在“来源”折叠框中输入公式“＝$A$1:$C$A"。
步骤３．单击“确定”按钮。
步骤４．用同样的方法设置Ｆ２：Ｆ１８的数据有效性，公式为“＝OFFSET($a$2,,MATCH($E2,$A$1:$C$1,)-1,COUNTA(OFFSET($A$2,,
MATCH($E2,$A$1:$C$1,0)-1,65535)))"。
通过设置后，选定Ｅ２：Ｅ１８中的单元格，将出现种类的下拉菜单，选定Ｆ２：Ｆ１８中的单元格，将出现该种类对应商品的下拉菜单。如果Ｅ列没有选择种类，则Ｆ列也无法选择。这从另一个角度上也避免了错误的产生。
２．创建三级下拉列表
示例３３．１２ 分级选择性录入居住地
如图所示，Ａ～Ｃ列是省份名、市（地）名、县（区）名的资料，现在希望在F~H列分级选择性录入数据。在这个例子中，需要用辅助列来帮助设置数据有效性。
|
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
|
来自： 昵称380475 > 《Excel 大全》
