第2章 数据的自定义格式
数据的自定义格式指的是数据按照一定的样式显示的格式。通俗地说就是把不同类型的数据，诸如整数数据、带有小数的数据、带有计量单位的数据等，以形式各异的样式显示出来。显示样式有包含1位或2位小数的样式、带有千分位分隔符的样式、不影响计算的带有计量单位的样式、手机号码或银行账号分段显示的样式、百分数样式等。不同的数据需要不同的显示样式，这就是数据格式。然而格式就是格式，无论设置何种样式的格式，都不会改变数据本身的大小。数据自定义格式的样式更多，用户可以根据需要进行灵活设置。
技巧013 分段显示长数据
扫码看视频
应用场景
手机号码、银行账号、身份证号码等数据，由于位数较长而不利于阅读。如果按照预先设定的间隔位数，用空格有规律地分段显示这些数据，那么读取操作就容易多了。
下面以分段显示手机号码为例，介绍利用Excel的单元格格式功能分段显示长数据的方法。原始数据如图2-1所示。
图2-1
解决方案
（1）选中要分段显示的数据区域，如A2:A14单元格区域，单击鼠标右键，在弹出的快捷菜单中选择【设置单元格格式】命令，弹出【设置单元格格式】对话框，在【数字】选项卡的【分类】列表框中选择【自定义】选项，在【类型】文本框中输入自定义分段显示的样式，如“000 0000 0000”（一个0相当于1个占位符），单击【确定】按钮，分段显示效果如图2-2所示。
（2）在【类型】文本框中输入不同的显示格式，可以获得不同的分段显示效果。例如，对于C2:C14单元格区域，若在【类型】文本框中输入“00 00 00 00 00 0”,那么电话号码的分段显示效果则如图2-3所示。
图2-2
图2-3
拓展应用 分段显示用户账号
若要将一组用户账号按照4位一组显示，假设每个用户账号均有16位，4位一组，则共计4组。在【设置单元格格式】对话框的【类型】文本框中输入“0000 0000 0000 0000”，单击【确定】按钮后的显示效果如图2-4所示。
除了利用单元格格式功能分段显示长数据外，还可以借助TEXT文本函数分段显示长数据。例如，基于图2-1中的数据源（A2:A14单元格区域），在B2单元格中输入公式【=TEXT(A2,"000 0000 0000")】，然后向下复制公式，效果如图2-5所示。
图2-4
图2-5
技术看板
默认情况下，Excel中每个单元格所能显示的数字为11位。如果输入超过11位的数字，系统会自动将其转换为科学记数格式。例如，若输入“123456789012”，则会显示为“1.23457E+11”。虽然不能完整显示数据，但是录入的“123456789012”仍然完整地保存在单元格中。如果输入超过15位的数值，系统会自动将15位以后的数字自动置零。例如，若输入“1234567890123456”，则会显示为“1.23457E+15”，并且在单元格中保存下来的实际数据是“1234567890123450”，最后一位数字“6”被系统变更为“0”。
要保证输入的长数字（11位数以上）不变，可以在录入数据之前，先输入一个英文状态下的单引号，也可以先设置数字格式为【文本】（设置方式见图2-6），然后录入数据。
图2-6
技巧014 带有计量单位的数据求和
扫码看视频
应用场景
很多表格中的数据都需要提供计量单位，但如果把计量单位直接写在数字后面会造成数据无法正常运算。能否在不影响计算的情况下添加计量单位是很多财务人员梦寐以求的事，通过设置单元格格式可以让你美梦成真。
假设一张数据统计表中有数量、单价、金额等具体数据，如图2-7所示。现需要为数据添加计量单位的同时，还要保证能正常运算。
图2-7
解决方案
（1）选中C2:C9单元格区域，单击鼠标右键，在弹出的快捷菜单中选择【设置单元格格式】命令，弹出【设置单元格格式】对话框，在【数字】选项卡中选择【分类】列表框中的【自定义】选项，在【类型】文本框中输入【0千克】，然后单击【确定】按钮，如图2-8所示。
（2）再次打开【设置单元格格式】对话框，可以看到系统自动为“千”和“克”分别添加了双引号，如图2-9所示。
图2-8
图2-9
如果你觉得该内容显示凌乱，可以手工修改，如把“千克”两个字用一组双引号括起来。
（3）选中D2:D9单元格区域，单击鼠标右键，在弹出的快捷菜单中选择【设置单元格格式】命令，弹出【设置单元格格式】对话框，在【数字】选项卡的【分类】列表框中选择【自定义】选项，在【类型】文本框中输入【0.0"元/千克"】，单击【确定】按钮，如图2-10所示。
图2-10
（4）【金额】列单位的添加方法请参看【数量】列单位的添加过程。
技术看板
利用【设置单元格格式】命令添加数字后缀时应注意如下几点。
（1）添加纯汉字后缀时，可以不用添加双引号，因为系统会帮你自动添加。例如，0元会被系统自动修改为【0"元"】。
对于自定义格式，“0元”表示整数，“0.0元”表示显示一位小数。如果希望显示两位小数，则可在【类型】文本框中将其修改为【0.00元】。
（2）添加带有字母、横杆或斜杠的后缀时，务必添加双引号。例如，若要添加后缀“元/千克”，则需在【类型】文本框中输入【0.0"元/千克"】。
（3）如果希望在数字和计量单位间拉开一点距离，可以在计量单位前添加一个空格。例如，在【类型】文本框中输入【0.00" 元"】。
技巧015 自动填写表头的3种方法
扫码看视频
应用场景
很多表格的表头中都带有月份、年份等数字。例如，11月销售统计表、3月生产记录表、2018年6月设备登记台账等。在进行月份转换时，需要在编辑栏中或单元格中修改月份，虽然这不是一个复杂的操作，但是一不小心就会把其他内容误删除了。通过设置自定义单元格格式，可以实现便捷的月份或年份转换，确保准确无误地修改月份或年份信息。
解决方案
1．月份在前的表头填写方法
（1）选中表头单元格，单击鼠标右键，在弹出的快捷菜单中选择【设置单元格格式】命令，弹出【设置单元格格式】对话框，如图2-11所示。
（2）在【数字】选项卡的【分类】列表框中选择【自定义】选项，在【类型】文本框中输入【0"月工资计算表"】，单击【确定】按钮退出该对话框，如图2-12所示。
图2-11
图2-12
（3）在表头单元格中任意输入一个1~12的数字，如这里输入【3】，按【Enter】键后，表头内容便完整地呈现在该单元格中，如图2-13所示。
图2-13
技术看板
如果希望用两位数显示月份，如“06月工资计算表”，在【类型】文本框中将原来输入的【0"月工资计算表"】修改为【00"月工资计算表"】即可。
2．月份在中间的表头填写方法
（1）选中表头单元格，单击鼠标右键，在弹出的快捷菜单中选择【设置单元格格式】命令，弹出【设置单元格格式】对话框，在【数字】选项卡的【分类】列表框中选择【自定义】选项，在【类型】文本框中输入【"人力资源"00"月工资计算表"】，单击【确定】按钮，如图2-14所示。
（2）在表头单元格中任意输入一个1～12的数字，如这里输入【06】，按【Enter】键后，表头内容便完整地呈现在该单元格中，如图2-15所示。
图2-14
图2-15
3．仅显示年月的自定义表头填写方法
（1）选中表头单元格，单击鼠标右键，在弹出的快捷菜单中选择【设置单元格格式】命令，弹出【设置单元格格式】对话框，在【数字】选项卡的【分类】列表框中选择【自定义】选项，在【类型】文本框中输入【yyyy"年"m"月工资计算表"】，单击【确定】按钮，如图2-16所示。
（2）在表头单元格中输入一个完整的日期，如这里输入【2018-2-1】，按【Enter】键后，表头内容便完整地呈现在该单元格中，如图2-17所示。虽然表头没有显示是2018年2月的几日，但必须输入一个完整的日期。
图2-16
图2-17
技术看板
若需要在年份的前面添加部门名称，如“财务部”，把【自定义】的【类型】设置由【yyyy"年"m"月工资计算表"】修改为【"财务部"yyyy"年"m"月工资计算表"】即可，如图2-18（a）所示。
注意：该技巧要求录入完整的日期数据，如“2018-5-10”，输入完成后按【Enter】键，系统便会自动添加自定义格式中的内容。如果录入的日期格式错误，系统会返回错误的年月值。例如，若输入【2018.5】后按【Enter】键确认，返回的错误结果如图2-18（b）所示。
（a）
（b）
图2-18
本技巧中的两个自定义表头在录入方法上有着本质的区别，第一、二个方案录入的是一个数字，数字前后的内容通过格式设置实现；第三个方案录入的是一个日期，系统通过“yyyy”和“m”把日期中的年份和月份分别提取出来，再和自定义格式设置中的文字内容共同组成显示内容。掌握了设置原理，就可以设计出更多形式的自定义格式。
技巧016 批量编辑多个单元格区域
应用场景
当在一张表格的某个区域中需要录入一个相同的数据时，除了复制、粘贴外，还有没有其他批量录入的方法呢？答案是肯定的。下面介绍两个批量录入的方法，其中，方法1适用于在多个空白单元格区域中批量录入，方法2适用于在指定单元格区域的多个空白单元格中批量录入。
解决方案
1．在多个空白单元格区域中批量录入相同内容
（1）若要在图2-19所示的3个连续的空白单元格区域中批量录入文字【武汉加油】，则先选中A1:C7单元格区域，然后按住【Ctrl】键不放，再用鼠标分别选中B9:E15和G3:I9单元格区域，选中之后释放【Ctrl】键。
（2）在编辑栏中输入【武汉加油】，然后按【Ctrl+Enter】组合键，效果如图2-20所示。
图2-19
图2-20
2．在指定的单元格区域的多个空白单元格中批量录入相同内容
（1）若要在图2-21所示的3个单元格区域的空白单元格中批量录入文字【武汉加油】，则先选中A1:C7单元格区域，然后按住【Ctrl】键不放，用鼠标分别选中B9:E15和G3:I9单元格区域，选中之后释放【Ctrl】键。
图2-21
（2）按【Ctrl+G】组合键，在弹出的【定位】对话框中单击【定位条件】按钮，在弹出的【定位条件】对话框中选中【空值】单选按钮，单击【确定】按钮，如图2-22所示。
（3）在编辑栏中输入【武汉加油】，然后按【Ctrl+Enter】组合键，效果如图2-23所示。
图2-22
图2-23
上述两个案例均为定位功能的应用。定位功能非常强大，可用于定位空值（空白单元格）、常量、公式数字、公式逻辑值、可见单元格、条件格式等。通过定位功能指定单元格后，可以实现批量填充内容、批量删除内容、批量设置单元格格式、批量删除批注等操作。
技巧017 隐藏敏感数据
扫码看视频
应用场景
在工作中难免遇到一些敏感信息，诸如报价信息、采购价格、成本信息等。这些信息往往不希望被他人看到。若采用工作表加密的方式来保护这些信息，只能控制包含敏感信息的表格不被他人打开，但是在编辑、浏览这些信息时难免会被走过、路过的人瞄到。本技巧将帮你实现在编辑敏感数据的同时隐藏数据，如果操作迅速，即使操作人自己也看不清相关数据。
图2-24所示为一个产品数量和价格的统计表，下面通过单元格格式设置来隐藏单价信息。
图2-24
解决方案
（1）选中D2:D8单元格区域，单击鼠标右键，在弹出的快捷菜单中选择【设置单元格格式】命令。
（2）弹出【设置单元格格式】对话框，在【数字】选项卡的【分类】列表框中选择【自定义】选项，在【类型】文本框中输入3个英文分号“;;;”，如图2-25所示。
（3）单击【确定】按钮退出对话框后，发现单价信息全部被隐藏了，并且当再次录入或修改单价信息，按【Enter】键后，【单价】列单元格中仍然不显示任何内容，如图2-26所示。
图2-25
图2-26
本技巧设置的效果实质是把单元格中的信息隐藏起来，但该信息仍然存在于该单元格中。选中该单元格，可以在编辑栏内看到该单元格中的内容。同时，该单元格可以继续参与相关计算，并且不会影响计算结果。
技巧018 根据日期自动获取对应的星期
扫码看视频
应用场景
在录入数据表时，有时需要在录入日期数据的同时再录入对应的星期。若人工进行查找、填写，不仅工作量大，而且效率低下。其实，通过设置单元格格式可以轻松实现日期数据对应的星期或月份的自动填写。
解决方案
（1）如果希望在【中文星期】列中填写【日期】列数据对应的星期，则先在C2单元格中输入公式【=B2】，即调取【日期】列中的日期数据。然后拖曳C2单元格的填充柄至C7单元格，复制该公式，效果如图2-27所示。
（2）选中C2:C7单元格区域，单击鼠标右键，在弹出的快捷菜单中选择【设置单元格格式】命令，弹出【设置单元格格式】对话框，在【数字】选项卡的【分类】列表框中选择【自定义】选项，在【类型】文本框中输入【aaaa】，如图2-28所示。
图2-27
图2-28
（3）单击【确定】按钮，设置后的效果如图2-29所示。此后，若修改B列的日期数据后，C列的星期数据也会随之改变。
（4）若需显示对应的英文月份，只需在步骤（2）的【设置单元格格式】对话框中，将【类型】文本框中的【aaaa】修改为【mmmm】。设置后的效果如图2-30所示。
图2-29
图2-30
（5）若需显示对应的英文星期，也只需在步骤（2）的【设置单元格格式】对话框中，将【类型】文本框中的【aaaa】修改为【dddd】。设置后的效果如图2-31所示。
本技巧应用的关键是3个字母序列:aaaa、mmmm、dddd。其中，aaaa表示中文的星期（星期一到星期日），mmmm表示英文的月份（January到December），dddd表示英文的星期（Sunday到Saturday）。把字母的重叠数量分别调整为1个、2个、3个，可获得更多的设置效果，如aaa代表简写的星期日到星期六，m代表不带前导零的中文月份，mm代表带前导零的中文月份（01～12），mmm代表英文的月份简写（Jan到Dec），d代表不带前导零的日（1～31），dd代表带前导零的日（01～31），ddd代表简写的英文星期（Sun到Sat）。具体显示效果如图2-32所示。
图2-31
图2-32
技巧019 高效录入编码
扫码看视频
应用场景
在编制各种产品、设备等统计表的过程中，经常会涉及一些自定义的编码，如GH-020、FGT-0031、KT-024等。在录入或修改这些编码的操作中，需要反复切换输入法并配合半字线（-）和数字的录入，如果编码的数字位数较多，如GD-000001，连续录入类似的多行数据后会让人抓狂。本技巧可帮你摆脱录入此类编码的烦恼。
解决方案
（1）选中图2-33中的B2单元格，单击鼠标右键，在弹出的快捷菜单中选择【设置单元格格式】命令。
图2-33
（2）弹出【设置单元格格式】对话框，在【数字】选项卡的【分类】列表框中选择【自定义】选项，在【类型】文本框中输入【"GH-"0000】，如图2-34所示。
（3）单击【确定】按钮，退出对话框后在B2单元格中输入编码中的数字部分，如对于编码“GH-0021”，只需要在B2单元格中输入【21】，按【Enter】键后，B2单元格中显示的结果为GH-0021,如图2-35所示。
图2-34
图2-35
拓展应用 统一部分规则，实现局部高效录入
本技巧应用的局限在于编码规则的不确定性，如果编码既有GH开头的，又有TF开头的，甚至还有其他不同的编码规则，就需要重新设计解决方案了。
例如，在图2-34的B列（【编码】列）左侧插入两个辅助列——【编码前缀】列和【编码数字】列，其中，【编码前缀】列负责存放编码的前缀部分，如GH、TF等；【编码数字】列负责存放编码中的数字部分，如0021、0307等。在D2单元格中输入公式【=B2&"-"&TEXT(C2,"0000")】，此后再输入编码时，只须要在B列中输入编码前缀，在C列中输入编码数字，就可以在D列中自动生成编码了，如图2-36所示。公式中的“0000”表示编码数字由四位数组成，如果希望编码数字是两位数或六位数，则将“0000”修改为“00”或“000000”即可。
图2-36
本案例展示的是如何高效录入。通过此案例，读者可发现，需求的改变势必影响设置的改变，录入的内容规律性越强，设置越简单；规律性越差，设置越复杂，同时还引发更多的录入操作。想提高录入效率，需尽可能地统一规则，即便其中含有多个规则，也可以把相同规则的部分放在一起，实现局部高效录入。