分类：准确性篇

当工作表中的数据超过一页，想要查看后面的数据时，唯一的做法是向下拉动滚动条，但是此举将导致标题行消失，不利于查看数据，可能导致判断失误。例如，在图3.79所示的状态下，无法了解第4列的数据属于什么项目。

如何才能解决既能看到后面的数据又能同时看到标题行呢？

解题步骤

锁定窗格可以让指定行、指定列的数据永远保持在视线内，具体操作步骤如下。

1.将滚动条向上拉，直到看到第一行数据。

2.选择B3单元格，然后单击功能区的“视图”→“冻结窗格”→“冻结拆分窗格”。

3.下拉垂直滚动条，可以发现第一行和第二行总是保持在视线内，效果如图3.80所示。

如果向右拉水平滚动条，那么可以发现首列同样保持在视线内。

知识扩展

1.锁定窗格工具的作用在于将上面若干行或左边若干列保存在视线以内，通常这些行或列都属于报表的标题，让它们保持在视线内有助于查看报表，了解每行或每列的数据属于哪个项目。

2.根据需求不同，允许只锁定行或只锁定列，假设要锁定前3行而不锁定列，那么应该选择A3单元格后再单击冻结拆分窗格；假设要锁定前1列而不锁定行，那么应该选择B1单元格后再单击冻结拆分窗格；假设既要锁定前1行又要锁定前一列，那么应该选择B2单元格后再单击冻结拆分窗格。

3.只能冻结前N行或者前N列，不能冻结中间行或中间列。例如，只冻结第3行或第2列是无法实现的。

4.一个工作表只能锁定一次窗格，单击一次“冻结拆分窗格”菜单后，菜单马上变成“取消冻结窗格”，再次单击则只能取消上次的设置。

5.如果要让最下方的若干行永远保持在视线内，只能采用“拆分”菜单才能实现，在后面有相关的案例介绍。

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

下图中B列只能输入身份证号码，禁止输入手机号码等其他数据，也禁止输入不符合规则的身份证号码。如何才能实现？

解题步骤

身份证号码都有固定的规则，不按规则输入的都禁止输入即可。使用公式计算B列的值是否符合规则，然后在数据验证中加以限制，具体操作步骤如下。

1.在I2输入以下公式：

2.将公式向下填充到I11，然后隐藏I列，避免意外删除公式。

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

4.在“数据验证”对话框中将“允许”下拉列表的默认值“任何值”修改为“自定义”，然后在公式框中输入公式“=i2”，操作界面如图3.74所示。

5.打开“出错警告”选项卡，并按图3.75的方式指定警告信息。

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

7.在B2单元格输入身份证号码“＇330401197502177647”，当单击Enter键后Excel不会产生任何提示，表示输入的号码符合规则。

此处要注意身份证号码前面必须有半角状态下的撇号，否则身份证号码的最后3位数字有可能丢失。

之所以说有可能丢失而不是一定会丢失，是因为部分特殊身份证号码以X结尾，这种号码本身属于文本，和超长数值的性质不同。

8.在B3单元格输入身份证号码“＇750423199407146087”，当单击Enter键后Excel会弹出如图3.76所示的警告信息，因为身份证号码没有以75开头的，只有以15、35、45和65开头的。

9.单击“重试”按钮，然后重新输入身份证号码“＇4504231994071460871”，当单击Enter键后Excel会弹出如图3.77所示的警告信息，因为此身份证号码尽管前12位没有问题，但是其长度为19位，超过了标准长度。

10.单击“重试”按钮，然后重新输入身份证号码“＇4504231994077460871”，当单击Enter键后Excel会弹出如图3.78所示的警告信息，代表日期的8位数字“19940774”格式有误，一个月的天数上限是31，而此处输入的值是74。

知识扩展

1.我国的身份证号码在10多年以前采用的15位数字，现在改成了18位，其中前6位用于说明身份证持有人所在地区的信息，第7位到第14位表示身份证号码持有人的出生年月日，第15位到第17位表示顺序码，即同一地区同一日期有多人出生时，使用顺序码来区分，第18位是校验码，校验前面的数字否是正确，有计算规律。

2.我国身份证号码的前两位按表3.1所示的方式编码。

从表3.1中可以看出四川省的身份证号码是以51开头的，吉林省的身份证号码是以22开头的，湖北的身份证号码则是以42开头。如果某身份证号码以7开头则说明该号码一定有误。

3.身份证号码的第7位到第14位用于说明持有人的出生日期，因此不可能小于“19010101”也不可能大于今天的日期。

4.本例的数据验证公式使用了AND函数限制4个条件必须同时满足，否则就禁止输入。

=AND(LEN(B2)=18,OR(LEFT(B2,2)={＂11＂,＂12＂,＂13＂,＂14＂,＂15＂,＂21＂,＂22＂,＂23＂,＂31＂,＂32＂,＂33＂,＂34＂,＂35＂,＂36＂,＂37＂,＂41＂,＂42＂,＂43＂,＂44＂,＂45＂,＂46＂,＂50＂,＂51＂,＂52＂,＂53＂,＂54＂,＂61＂,＂62＂,＂63＂,＂64＂,＂65＂,＂81＂,＂82＂}),AND(MID(B2,7,8)＞＂19010101＂,MID(B2,7,8)＜=TEXT(NOW(),＂yyyymmdd＂)),ISNUMBER(TEXT(MID(B2,7,8),＂0000-00-00＂)*1))

公式中的“LEN（B2）=18”表示身份证号码只允许长度为18位，否则不符合规则。公式中的“OR（LEFT（B2，2）={＂11＂，＂12＂，＂13＂，＂14＂，＂15＂，＂21＂，＂22＂，＂23＂，＂31＂，＂32＂，＂33＂，＂34＂，＂35＂，＂36＂，＂37＂，＂41＂，＂42＂，＂43＂，＂44＂，＂45＂，＂46＂，＂50＂，＂51＂，＂52＂，＂53＂，＂54＂，＂61＂，＂62＂，＂63＂，＂64＂，＂65＂，＂81＂，＂82＂}）”表示身份证号码的前两位必须是 11、12、13、14、15、21、22、23、31、32、33、34、35、36、37、41、42、43、44、45、46、50、51、52、53、54、61、62、63、64、65、81、82 中的一个，只要满足条件之一即可，这是OR函数的功能。

公式中的“AND（MID（B2，7，8）＞＂19010101＂，MID（B2，7，8）＜=TEXT（NOW（），＂yyyymmdd＂）））”表示从身份证号码的第7位开始、提取出来的8位数必须大于“19010101”，同时小于等于今天的日期，两个条件必须同时满足，这是AND函数的功能。其中NOW函数用于产生当前时间值，而TEXT函数则负责将时间值转换成与身份证号码中代表日期的数字同样的格式，便于比较。

公式中的“ISNUMBER（TEXT（MID（B2，7，8），＂0000-00-00＂）*1）”表示从身份证号码的第7位开始、提取出来的8位数必须是日期。判断一个8位数是否为日期的方法是：使用Text函数转换成“0000-00-00”格式，然后将它乘以 1，如果返回值是数值，表示它是日期，如果返回值是错误值，则表示不是日期。例如，公式“=ISNUMBER（TEXT（19870218，＂0000-00-00＂）*1）”的运算结果为True，而公式“=ISNUMBER（TEXT（19870248，＂0000-00-00＂）*1）”的运算结果为False，因为1987年2月只有28天，不存在48天，因此此数字不能代表日期。

当输入的身份证号码同时满足以上4个条件时，Excel将不会禁止用户输入当前号码。

5.数据验证的公式有所限制，禁止在公式中使用常量数组，因此本例没有在数据验证对话框中直接输入公式，而是将公式写在辅助区域中，然后在数据验证对话框中检查辅助区域是否等于True即可。

6.输入汉字、英文单词、标点符号、长度不等于18位等情况都会被阻止，但是粘贴单元格则不会被阻止，因此为了提升输入数据的准确性，使用了数据验证的区域应采用手工输入方式。

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

下图中C2的公式计算结果正确，但将公式向下填充后所产生的所有结果都是错误的，如何解决此问题呢？

解题步骤

产生图 3.70 所示的问题是因为计算选项没有设置好，修改一下就行，操作方法：单击功能区的“公式”→“计算选项”→“自动”，工作表中的所有公式会马上重算、更新。

图3.71是操作菜单，而图3.72则是操作结果。

知识扩展

1.Excel有“自动”、“手动”和“除模拟运算外自动重算”3种计算模式，“自动”表示修改被引用单元格的值时公式自动重算一次，避免数据源变化时公式结果未更新。“手动”表示按下F9键时才计算公式，其他时间公式不更新，图3.70中填充公式后得到错误结果就是因为当时处于手动模式下，填充所产生的公式没有重算。“除模拟运算外自动重算”表示执行模拟运算以外的情况下都自动重算，工作中常用的是自动和手动。

以上3种模式下都会在保存工作簿、打开作簿时重算一次，换言之，打开工作簿和保存工作簿时总会计算一次所有公式，不受计算选项的设置所影响。

2.自动计算的好处是公式总能及时更新，确保计算结果正确，坏处是工作簿中存在大量公式时会导致打开、编辑、保存等操作变慢。手动计算的好处是不影响工作簿的打开、编辑与保存速度，只要在需要更新公式时按下F9键就能确保公式的计算结果正确，当工作簿中需要大量使用公式时宜采用手动模式。

3.本例中的Rank函数用于计算数据的名次，它的语法如下：

RANK(number,ref,[order])

第一参数代表要参与排序的数值，第二参数代表一组数据，函数将计算第一参数的值在这组数据中的排列顺序。第三参数代表排序方式，是可选参数，赋值为0或忽略参数时表示降序排序，赋值为其他值时表示升序排序。

本例公式“=RANK（B2，$B$2：$B$11）”表示计算B2的值在B2：B11区域中的降序排名。

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

数值区域中混有文本时会干扰数值运算，绝大多数函数在运算时都会忽略文本，导致运算结果不准确。

在一大片区域中找出所有文本仅凭肉眼是没有办法完成的。例如，在图3.66所示的B2：G11区域中有两个文本，仅凭双眼观察是无法找出它们的，如何才能瞬间找到所有文本单元格呢？

解题步骤

Excel提供了定位工具，用于定位符合各种条件的单元格，其中包含文本所在单元格，具体操作步骤如下。

1.选择B2：G11区域，按F5键或＜Ctrl+G＞组合键，弹出“定位”对话框。

2.在“定位”对话框中单击“定位条件”按钮，取消选择“定位条件”对话框，操作界面如图3.67所示。

3.在“定位条件”对话框中选中“常量”单选按钮，然后取消选择下方的“数字”、“逻辑值”和“错误”3个复选框，设置界面如图3.68所示。

4.单击“确定”按钮保存设置，同时返回工作表界面，此时可以看到定位结果——C5 和D7处于选中状态，表示这两个单元格都是文本，效果如图3.69所示。

知识扩展

1.“定位条件”对话框可以区分文本、数值、逻辑值、错误值、有公式的单元格、空值、可见的单元格、设置了条件格式的单元格等各种对象，在工作中会经常需要用到“定位条件”。本例要求找出所有文本，采用“定位条件”是唯一的快速解决问题的方法。

2.文本有两种：其一是常量文本，即直接在单元格中输入的文本，“Excel”、“VBA”、“E 灵插件”和“你好”都是文本常量；其二是公式文本，即通过公式计算出来的文本，如 A1单元格是文本，那么B1单元格的公式“=a1”直接引用A1的值，那么B1就是公式文本。

3.本例的成绩区域都是手工输入的，因此仅需定位常量即可。假设不确定该区域中是否包含公式，应该定位两次才行，第一次定位常量文本，第二次定位公式文本。

4.本例B2：G11区域中包含两个文本：一个是在单元格处于文本格式状态下输入的数字，从而使数字转换为文本，另一个是将字母o当作数字0输入，从而变成了文本。肉眼很难判断这种错误，使用定位工具找出混在数值区域中的文本最准确也最快捷。

5.定位时不能公式和常量同时定位，必须分两次执行。

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

当报表的标题较多时（如超过20列），查看报表将极其不便。因为越靠近屏幕右方的内容离行标题就越远，就越容易看错位。如果能对当前行着色，则可以解决此问题，那么如何才能着色当前行呢？

解题步骤

使用条件格式可以实时地突出显示当前行，具体操作步骤如下。

1.选择A1单元格，然后按组合键＜Ctrl+A＞选中当前已用区域。

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

3.从“选择规则类型”列表框中选择“使用公式确定要设置格式的单元格”，然后在下方的文本框中输入公式“=CELL（＂row＂）=ROW（）”，操作界面如图3.62所示。

4.单击“格式”按钮，弹出“设置单元格格式”对话框，然后打开“填充”选项卡，在其中单击红色，表示符合条件的区域将填充红色的背景，操作界面如图3.63所示。

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

6.单击数据区域中的A3单元格，然后按下F9键，Excel会自动将已用区域的当前行添加背景色，从而突出显示、便于查阅数据，效果如图3.64所示。

7.单击E8单元格，再次按下F9键，Excel会自动将已用区域的当前行添加背景色，从而突出显示、便于查阅数据，效果如图3.65所示。

知识扩展

1.CELL函数用于提取有关单元格的格式、位置或内容的信息，它的语法如下：

CELL(info_type,[reference])

其中第一参数代表要提取的信息类别，第二参数代表要提取哪一个单元格的信息。第二参数是可选参数，当忽略第二参数时表示提取活动单元格的信息。

本例中“CELL（＂row＂）”代表提取活动单元格的行号，如果参数“row”替换成“Col”则表示提取列号。

公式中的“ROW（）”表示提取公式所在单元格的行号。

公式“=CELL（＂row＂）=ROW（）”表示：如果某个单元格的行号等于活动单元格的行号，那么该单元格就符合条件。显然，只有隶属于当前行的单元格的行号才有可能等于活动单元格的行号，因此在条件格式中使用以上公式将只标示当前行的单元格。

2.公式并不会在选择单元格时自动更新计算结果，只有保存工作簿、打开工作簿、修改单元格的值或按下F9键时才会更新。

3.条件格式仅作用于添加条件格式前所选定的区域，本例选中有数据的区域后再添加条件格式，因此右方或下方的空白单元格是永远不会着色的。

4.如果希望对当前列着色，那么改用公式“=CELL（＂col）=ROW（）”即可。

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

在单元格中输入身份证号码时，单元格会将身份证号码显示为科学记数法，如6.543E+17，同时还会丢失后面几位数值。例如，在单元格中输入 654300199103073299 后自动变成654300199103073000，效果如图3.59所示：

要如何才能让身份证号码显示完整呢？

解题步骤

Excel会在单元格中的数值长度大于等于12位时显示为科学记数法，从而减少数值的长度。身份证号码包含15位和18位两种形式，不管哪种形式都会显示为科学记数法。

Excel的数值计算精度是15位，显示精度也是15位，当数值超过15位时，Excel不能正确显示15位以后的值，而是以0占位，因此在图3.59中输入654300199103073299后会自动变成654300199103073000，右边3位数不管原来是什么都会变成0。

解决以上两个问题可采用同一个方法——输入身份证号码之前先录入半角状态的单引号“＇”。当输入“＇654300199103073299”后单元格会显示为图3.60所示的效果。

知识扩展

1.身份证号码是不需要参与数值运算的，因此以文本形式输入单元格中并不会带来负面影响，但可以解决本疑难中的两个问题。

2.将身份证号码以文本形式输入单元格后，单元格的左上角会显示一个绿色的倒三角符号，如果想关闭该符号，可以选中单元格后单击单元格左方的箭头，然后从弹出的下拉菜单中选择“忽略错误”。

3.对于身份证号码丢失最后3位数值的问题，只能前期预防，无法后期补救，对于已经输入单元格且已经丢失3位数值的身份证号码，没有任何办法找回丢失的数值，只能重新输入。

4.半角状态和全角状态的单引号有着本质区别，并非仅仅粗细不同。半角符号可将数值转换成文本，但是符号本身不会显示出来；全角撇号也可以将数值转换成文本，但是符号本身会显示在单元格中，而且会影响此单元格的数据参与运算。图3.61中是两种形式的撇号对比。

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

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

解题步骤

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

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

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

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

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

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

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

知识扩展

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

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

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

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

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

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

解题步骤

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

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

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

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

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

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

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

6.继续输入“东莞万”，然后再次单击倒三角符号，Excel会弹出以“东莞万”3字开头的所有客户名称，效果如图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单元格符合条件。

本例公式的含义是：以 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.设置了数据验证后，默认状态下输入的值不符合需求时会弹出错误警告，但本例需要在输入部分字符后调出下拉列表，因此需要禁用错误警告。将“输入无效数据时显示出错警告”复选框即表示禁用错误警告。

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

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

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