分类：Excel 2016实用技巧大全

当工作表中的数据超过一页，想要查看后面的数据时，唯一的做法是向下拉动滚动条，但是此举将导致标题行消失，不利于查看数据，可能导致判断失误。例如，在图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群下载文件和视频教材，从而方便练习，同时验证本案例中的技巧与操作结果。