第2章 数据的格式化

对工作表数据进行格式化，不仅能够美化工作表，而且可以使工作表数据的含义更加清晰。例如，对不同的文本设置不同大小的字号，用不同的颜色表示不同的信息，设置数据的不同对齐方式等，可把表中数据所代表的信息表达得更清楚，更容易被他人认可和接受。

尤其重要的是：在输入某些数据时，通过数字格式化还能够减少输入，提高工作效率。

2.1 工作表的格式化

2.1.1 自动套用格式

Excel提供了约17种已做好的格式对工作表进行格式设置，用户在工作表中直接套用这些格式，可以提高工作效率，同时也可美化工作表，突出显示这些工作表中的数据。可以说，使用自动套用格式设置工作表，是最简单也是最直接的工作表格式化方法。

1. 自动套用格式的样例

设有一张学生成绩表，如图2.1所示。

这张成绩表尽管已经能够表示数据信息，但还存在如下缺点：

⊙ 表名不醒目。

⊙ 表的标题（学号、姓名……）也不太醒目。

⊙ 当学生较多，科目成绩较多时，看数据较困难。上、下行数据容易混淆，比如在看F10的数据时，易看成F9或F11的数据。

⊙ 整个成绩表不太美观。

可以对该表进行一些格式化工作，如标题居中、字号放大，不同的行使用不同的背景色或前景色（这样就不会看错行）。

格式化工作表最直接的方式就是使用自动套用格式。图2.2就是利用Excel的自动套用格式对图2.1格式化，再将标题设置为跨列居中后的结果。显然它表达的信息更易被接受，也更便于表中数据的输入、修改和查看。

2. 自动套用格式的操作过程

利用Excel的自动套用格式进行工作表的格式化过程如下：

＜1＞ 单击要格式化的工作表中的任一单元格（或选中要格式化的单元格区域）。

＜2＞ 选择“格式”|“自动套用格式”菜单命令，弹出如图2.3所示的对话框，从中选择一种需要的格式，然后单击“确定”按钮。

Excel提供了16种可用的自动套用格式，有经典、简单、三维、会计等。用户可根据表格中的实际内容选择需要的格式，对工作表进行格式化设置。

2.1.2 设置工作表的边框和底纹

Excel的默认边框是虚线、无底纹，所有的表格都表示为同一种样式。如果在Excel的默认方式下打印工作表，打印出的结果将没有表格线（当然，可通过打印设置让它显示表格线），不便于特殊信息的突出显示。通过边框和底纹的设置，一方面可使表格美观，同时也可改变信息的显示方式，让人们尽快掌握表格中的信息。

对于图2.1中所示的学生成绩表，设置边框和底纹之后，可得到如图2.4所示的表格。

比之于图2.1，该表格有以下变化：在无数据的地方，没有边框线；不同的学生数据行的单元格底色（底纹）不同。

设置工作表的边框和底纹的操作过程如下：

＜1＞ 在工作表中输入原始成绩（如图2.1所示）。

＜2＞ 选中整个工作表（单击工作表的全选按钮，列标字母A左边的空白“列标”）。

＜3＞ 选择“工具”|“选项”菜单命令，在弹出的“选项”对话框中取消网格线，如图2.5所示。

＜4＞ 选中要设置网格线的单元格区域，单击工具栏中边框线工具按钮右边的下拉按钮，在如图2.6所示的边框下拉列表中，先选择实边框线（经过此步骤，数据的周围就有表格线了），再选择“粗匣框线”，就把表的边框设置成了粗边框线。

＜5＞ 选中表格标题行（学号、姓名所在的行）所在的区域，然后选择“格式”|“单元格”菜单命令，在Excel弹出的“单元格格式”对话框中选中“图案”标签，如图2.7所示，从中选择一种颜色，“示例”中将显示颜色的样例。

＜6＞ 选择表中的偶数行数据所在的区域，把它的底纹设置为一种颜色（本例为浅黄色）；选择表中的奇数行数据所在的区域，把它的底纹设置为另一种颜色（本例为浅蓝色）。

经过上述选择和设置之后，图2.1所示的学生成绩表就成为了图2.4所示的数据表。在本例中，读者需要掌握工作表边框线的设置和取消方法（请反复运用第4步的操作方法对工作表中各种类型的边框线进行设置，在实际运用中，表格的网格线也是很重要的）。

2.1.3 设置三维表格

若表格总是以平面的形式出现，就显得很单调。通过单元格的格式设置（自定义格式），可以创建出具有上凸或下凹立体效果的表格，数据显示在这样的表格中，看起来更轻松。

图2.8所示的三维表格的建立过程如下（以设置上凸效果的表格为例）。

＜1＞ 选中整个工作表，把它的网络线去掉（参考设置边框和底纹的第3步）。

＜2＞ 把要设置三维效果的表格区域的底纹设置为浅灰色（一般情况下是把整个工作表的底纹都设置为浅灰色。当然，其他色彩的浅色也可以，但浅灰色的立体视觉效果最佳）。

＜3＞ 选择要设置上凸的单元区域。本例同时选中了单元格区域B3:F3、B5:F5、B7:F7、B9:F9、B11:F11。

＜4＞ 选择“格式”|“单元格”菜单命令，弹出“单元格格式”对话框，如图2.9所示，选中“边框”标签，在“颜色”框中选择“白色”。

＜5＞ 单击图2.9中的“边框”区域中的上边框按钮和左边框按钮，然后单击“确定”按钮。此时这两个按钮处于下凹状态（好像被按下去一样），在图2.9中看不出有什么变化（其实是有白色的左边框线和上边框线的，因为它的颜色与其背景色一样，所以看不出）。

说明：本操作的先后顺序不能颠倒，即先选择上边框线和左边框线，再设置其颜色是不行的。

＜6＞ 再次选中前面那些单元格区域（即B3:F3，B5:F5，B7:F7，B9:F9，B11:F11），把它们的下边框和右边框设置为黑色（参考第＜4＞、＜5＞步，只不过在第＜4＞步中改选黑色），然后单击“确定”按钮。

经过上述操作步骤，就把单元格区域B2:F12设置成了如图2.8所示的上凸视觉效果。

把单元格区域设置成如图2.8右半部分所示的“下凹”视觉效果的操作步骤与上述步骤完全一样，只不过把选中的单元格区域的上边框线和左边框线设置为黑色，而把下边框线和右边框线的颜色设置成了白色而已。

在三维立体表格中显示数据，可使数据看起来更轻松，更美观，在数字型数据较多时不易出错。图2.10就是在上面设置的上凸效果的表格中显示数据的一个范例。

在图2.8所示的表格中输入数据，然后把上凸区域的字体设置成一种颜色，把下凹区域中的字体设置成另一种颜色，最终结果如图2.10所示。显然，在这种表格中查看数据较为轻松，且不会看错。

2.2 文本的格式设置

使用Word编辑、排版文档时，文字的设置大致包括字体、字型、修饰、对齐方式、字体颜色等诸多内容。在Excel中，文本的设置也包括这些内容。

1. 字体、字形、字体颜色设置

字体有不同的样式：无格式、黑体、斜体、带下划线、加删除线等。图2.11显示了字号的大小、字体、下划线等格式的设置样式。

字体设置的方法非常简单，选中要进行格式设置的单元格或字符之后，选择“格式”|“单元格”菜单命令，系统会弹出如图2.12所示的“单元格格式”对话框，选中其中的“字体”标签，可对单元格中的字体进行各种设置。

在该对话框中可对选择的单元格或字符的字体、字号、下划线、删除线、上/下标等内容进行设置。其具体操作方法很简单，请读者自己尝试。合理地设置工作表中的文本可使工作表更美观，其中的信息表达更准确，易被他人所接受。

2. 设置字符及数字的对齐方式

单元格的默认对齐方式是：文本靠单元格的左边对齐，数字靠单元格右边对齐。在一般情况下，使用Excel的默认对齐方式就可以了，但有时需要特殊的对齐方式，如斜线表头（在Excel中不应该存在这样的表头）、旋转字体等，这时可通过Excel的对齐方式进行设置。图2.13是一个对齐方式的样例。

（1）跨列居中

在图2.13所示的表格中，第一行的标题文字处在单元格区域A1:F1中。这些数据只属于一个单元格，它是一个合并单元格，操作步骤如下：

＜1＞ 在单元格A1中输入整个标题的文字。

＜2＞ 选中单元格区域A1:F1后，单击格化工具栏中的跨列居中按钮，然后把该单元格的字号设置为16。也可通过“格式”菜单进行设置，操作如下：选中单元格区域A1:F1，然后选择“格式”|“单元格”菜单命令，弹出单元格格式设置的对话框；单击该对话框中的“对齐”标签（如图2.14所示），选中“文本控制”中的“合并单元格”，然后单击“确定”按钮。

（2）垂直对齐方式

请注意图2.13中的第一、二行（表的标题行）数据，它们都只有一行数据，这一行的行高较其他行都高（拖动行号1下面的边线就可增大或减小该行的高度）。当增大行高时，Excel在垂直方向上的对齐方式是向下对齐的，不能使文字显示在该行的中间，得不到如图2.13所示的效果。通过对Excel单元格的格式设置，使它在竖直方向上能居中对齐就行了。

选中要设置垂直居中的单元格（或单元格区域），在出现的如图2.14所示的“单元格格式”对话框中，在“垂直对齐”下拉列表中选择“居中”对齐方式。

（3）文本、数字的旋转

在图2.13中，单元格A2内的文字是倾斜的，这种格式的表头在日常工作中较常见。如果表中的数据没有运算，最好在Word中处理，在Excel中制作这样的表格较为勉强，而且做出的效果较差。A2中的斜线是画上去的，但其中的倾斜文字可以通过单元格中数据的旋转实现，操作如下：

＜1＞ 将单元格A2中的数据输入为3行。输入“年度”后按Alt+Enter键，再输入“产量（斤）”后按Alt+Enter键，最后输入“产品名”后按Enter键。

提示：在一个单元格中输入数据后按Alt+Enter键将把输入点换到本单元格的下一行输入，也就是说，把同一单元格的内容分成了两行。

＜2＞ 选中单元格A2，然后出现如图2.14所示的对话框，向下拖动“方向”编辑框中的红色小菱形块到-45°（也可在下边的编辑框中直接输入“-45”）。

＜3＞ 单击“确定”按钮后，A2中的内容就如图2.13中的文字所示，再画几条斜线即可。

＜4＞ 设置B2:E2的垂直方向对齐方式为“居中”。

（4）单元格内文本换行

有时一个表格的标题内容较多，为了适合最终打印纸张的大小，需要把标题的内容显示在多行上，如图2.15所示。

许多工作人员在遇到这样的问题时，往往先把两行合并为一行，再输入表头数据；也有的工作人员把表头数据输入在两行靠近的单元格内，然后设置表格的网格线；也有人采用前面所讲的方法（在输入一个单元格的内容时，采用Alt+Enter换行）。这些方法都不太好，因为它们非常机械，当表格的内容或列宽需要调整时，就需要做许多重复的工作。比如，如果删除“C语言程序设计”数据列，这时将重新调整表格的数据列宽度，如表头只占一行，这时麻烦就大了。

建立类似图2.15的表的最简单方法为：把单元格区域A2:G2设置为“自动换行”，操作如下：

＜1＞ 选中单元格区域A2:G2，选择“格式”|“单元格”菜单命令，然后在图2.14所示的对话框中，选中“文本控制”栏中的“自动换行”。

＜2＞ 输入单元格区域A2:G2中的内容。

＜3＞ 调整第2行的高度为两行高度（向下拖动行号2下面的行线到合适的高度释放即可）。

＜4＞ 随意拖动A2:G2列线，调整各列的宽度，系统将根据各列的宽度把第2行的数据分为2行。

采用“自动换行”的方法有一个好处，当调整表的各列宽度时，系统将根据列宽自动调整单元格的内容在一行显示，两行显示，三行显示……这时上述问题就不存在了。删除“C语言程序设计”列数据后，用户调整各列的宽度，系统将自动根据各列的宽度调整表头的数据在一行显示或多行显示，这显然更符合实际需要。

（5）取消合并

对一个采用了单元格合并的工作表而言，有时会带来一些问题。比如，在把Excel的工作表（有许多合并单元格）复制到Word中进行处理时，就会遇到许多问题，简单的解决方法是取消单元格的合并。此外，有时因表格数据的变化或表格布局的变化，也需要取消单元格的合并。

取消合并的方法非常简单，只需选中已合并的单元格，选择“格式”|“单元格”菜单命令，然后在图2.14所示的对话框中，不选中“文本控制”栏中的“合并单元格”，就可把已经合并的单元格分解为原来的模样。

提示：在Excel 2003中，只需选中合并单元格，再次单击跨列居中按钮，就可将合并单元格还原到合并前的状态。

2.3 格式化数据

有时在一个单元格中输入的内容会与显示内容不一致。例如，在一个单元格中输入一个带区号的电话号码“02362460111”，在单元格中显示的却是“23624660111”；输入一个数据12/5，却发现显示的是12月5日。诸如上述的问题还有许多，它们都与数据格式化有关。

在Excel中，同一个数字可以被格式化为多种不同的形式。在图2.16中，第2行所有单元格均输入的是3.2，第3行对应单元格是对它采用的格式。由此可见，同一数字的表现形式何其多也！

本节将深入讨论Excel的数据格式化问题。

1. Excel的自动数据格式

存储在Excel单元格的数字、日期、时间等都是纯数字，没有经过格式化。当用户输入这些数据后，Excel会自动对其进行格式化。前面所遇到的正是这个问题，当输入“02362460111”时，Excel认为它是一个数字，数字前面的0显然可以丢掉；当输入“12/5”时，Excel认为输入的是日期，因为当它发现两个或三个数字用“/”（或“-”）作间隔符时，就认为该数字是一个日期。

有时候，Excel的默认数字格式会导致一些问题，如果确实需要显示“12/5”这个分数，怎么办？很简单，对单元格的数据进行格式化。

2. 使用系统提供的数据格式

Excel提供了许多数据格式，包括数据的精确度、显示方式等内容（货币：美元、欧元、人民币，百分比等）。常见的数据格式设置有以下几种：数据的精确度，以百分比显示数据，数据的分节显示。格式栏中的工具按钮就是用于设置这些格式的。

① 增加或减少小数位数。选中要增加或减少小数位数的单元格或单元格区域，然后单击工具栏中按钮，可增加小数位数；单击按钮，将减少一位小数位数。

② 设置百分比显示方式。选中要以百分比方式显示数据的单元格区域，单击“格式”工具栏上的百分比样式按钮。

③ 分节显示数据。一个太大的数值如1233453333，不容易看清它的大小，若把它显示为“1,233,453,333”，就容易看清它的大小了。直接输入“1,233,453,333”又太费事。简单的方式就是设置单元格的格式为数据的分节显示，操作方法为：选中要增加小数位数的单元格或单元格区域，单击“格式”工具栏上的“千位分隔样式”按钮。

④ 货币数据显示方式。有时，财务报表需要货币形式的数据，而且要求这些数据参与诸如汇总、分类统计之类的计算，这就需要将单元格中的数据设置为货币数据，操作方法如下。

选中要设置货币格式的单元格或单元格区域，单击“格式”工具栏的“货币样式”按钮。另外的一种方法是：在输入数据时先输入货币符号。如输入￥1234，就应在中文输入法下按Shift+$键（这种方法是输入了“￥”符号），接着输入1234，这时就输入了￥1234。这是一个货币数据，表示1234元。要输入$1234，可在该单元格中直接输入“$1234”。

图2.17是一个货币格式设置的样例。

图中销售表的建立方法如下：

＜1＞ 在单元格A1中输入标题，然后把单元格区域A1:E1合并，跨列居中。

＜2＞ 输入单元格区域A2:D6中的数据（只输入数据，人民币符号“￥”和小数点等不必输入，它是通过格式设置显示的。比如B4单元格，只需直接输入1130就行了）。

＜3＞ 在单元格C8中输入求和公式“=SUM(C2:C6)”；在单元格D8中输入“=SUM(D2:D6)”；在单元格E3中输入百分比计算公式“=D3/$D$8”，并把该公式复制到E4、E5、E6中。

＜4＞ 选中单元格区域B3:B6，然后单击工具栏中的“货币样式”按钮。用同样的方法设置单元格区域D3:D6中的数据为货币格式。

＜5＞ 选中单元格区域E3:E6，然后单击工具栏中的“百分比样式”按钮。

经过上述格式设置后，结果如图2.17所示。如果该表中的货币是美元怎么办？若是只有一两个数据，则可在输入数据时就先输入“$”符号，再输入数字。若是有许多数据需要设置为“$”格式，则操作方法如下：

＜1＞ 选中要设置格式的单元格或单元格区域。

＜2＞ 选择“格式”|“单元格”菜单命令，弹出如图2.18所示的“单元格格式”对话框，选择“数字”标签。

＜3＞ 在“分类”栏中选择“货币”，然后从“货币符号”列表框中选择需要的货币符号。

经过上述3个步骤，就把选中单元格或单元格区域中的数据设置成了需要的货币表现形式。

注意：格式设置仅仅对数据的显示形式起作用，而单元格中的内容仍然是数字，它可以参与数据的一切运算。

3. 自定义格式

（1）认识自定义格式

Excel提供的内部数据格式能够满足多数表格的需求，但也有例外。比如，在编制独特的数字目录、电话号码、产品编号等类型的表格时，往往找不到合适的数据格式，这时就要运用Excel的自定义格式功能了。

Excel的数字自定义格式具有强大的功能，不仅能够把数据显示为人们需要的形式，而且能简化数据输入，检查数据的正确性。图2.19是使用自定义格式显示数据的一个例子，本例概括了数字自定义格式的主要用途。

在图2.19中，把D列中的数据设置成B列同行所描述的自定义格式，然后在D列中输入C列所描述的数据，显示结果如D列所示。

例如，把单元格D4的格式设置为“"PMT-ABC-X"000”后，在其中输入1，这时的数据就显示为“PMT-ABC-X001”。这样的自定义格式能够简化数据输入。

Excel的自定义格式代码由4部分组成，其先后次序如下：正数的格式码→负数的格式码→0的格式码→文本的格式码。

说明：① 在格式代码中最多可以指定4节，各节之间以分号分隔，依次定义了正数、负数、零和文本的格式。如果只指定2节，则第1节用于表示正数和0，第2节用于表示负数。如果只指定1节，则所有数字都会使用该格式。如果要跳过某一节，则对该节仅使用“；”即可。② 格式码的每个组成部分用“；”分隔。③ 在格式码中指定的字符被用来作为占位符或格式指示符。0作为一个占位符，在没有任何数字被显示的位置上显示一个0。④ 符号“_)”跟在一个正数的格式后面，以保证这个正数右边留下一个空格，空格的宽度与圆括号“)”的宽度一样。⑤ 正数与负数的默认对齐方式都是右对齐。

根据上述的说明，考虑如下自定义格式“ $#,##0_)；($#,$$0) "零" ”。如果一个单元格的自定义格式被设置为上述格式，它将以“$#,##0_)”格式显示正数，以“($#,$$0)”格式显示负数，一个单元格的数据如果是0，将在此单元格中显示“零”。例如，在一个具有此格式的单元格中输入1239，则在该单元格中将显示“$1,239”；如果输入“-45320”，则将在该单元格中被显示为“($45,320)”；如果在该单元格中输入0，就会在该单元格中显示“零”。

由此可见，单元格的格式对数据的显示有着较大的影响。

（2）Excel提供的自定义格式控制符

Excel提供了一些格式控制字符，这些控制字符可用于自定义格式的设置，如表2-1所示。

（3）应用举例

至此，读者对Excel的自定义格式及格式的作用应有一定的了解，但怎样应用它，可能尚有一定的问题，这里加以说明。在Excel的任何单元格中创建自定义格式，应遵循以下的操作步骤：

＜1＞ 选中要进行格式化的单元格或单元格区域。

＜2＞ 选择“格式”|“单元格”菜单命令，弹出如图2.20所示的“单元格格式”对话框，选择“数字”标签，然后选择“分类”列表框中的“自定义”。

＜3＞ 在“类型”组合框中输入自定义格式（或从下面的列表框中选择一种已经定义好的自定义格式）。设置好“自定义”格式控制符后，单击“确定”按钮。

＜4＞ 在单元格中输入数据，这时数据将以自定义格式显示。

图2.21是一些格式设置后的情况，请读者分析。图中A列是C列同行单元格设置的自定义格式，B列是在C列中输入的数据，C列是输入数据后的显示结果。例如，在C6中设置的自定义格式为：zk#，当在C6中输入7575时，最终该数据显示为“zk7575”，其中数字7575前的字符“zk”是由Excel根据自定义格式码加上去的。

注意：虽然C6中显示的是zk7575，但该单元格的值仍然是7575，是一个数字，而不是文本“zk7575”。

2.4 格式化日期

在不同的工作环境中，日期和时间的表示方式有所不同，如中国和西方一些国家对日期和时间的表示是不同的，有时需要对日期和时间进行一些计算。在Excel中，日期和时间实际上是以序列数字的形式存储在单元格中的，所以在一个单元格的格式被设置为日期格式后，即使在其中输入一个纯粹的数字，该数字也会被显示为日期。此外，中文Excel还允许把日期和时间设置为中文格式，图2.22就是日期的各种中文格式的应用事例。其格式化过程与数字的自定义格式过程完全一样。图2.22的A列描述的是C列同行数据日期格式，B列表示在C列输入的数据，C列是输入B列数据后的显示结果。例如，把单元格C2的格式设置为：二OO一年三月十四日（表示日期的一种中文格式），则在其中输入B2所示内容时，Excel会在C2中显示“二OO一年二月二十一日”。

日期和时间的格式设置过程如下：选中要设置格式的单元格后，选择“格式”|“单元格”菜单命令，弹出如图2.23所示对话框，选择“数字”标签，在“分类”列表框中选择“日期”，再在“类型”列表框中选择需要的日期格式。

2.5 条件格式

对于不同的数据，可按不同的条件设置其显示格式。如一个学生成绩表，把所有不及格成绩显示为红色，可以非常轻松地看出其中不及格的成绩情况。对于企业的销售表，把其中利润较小的或无利润的设置为红色，可以使人在查看这些数据时一目了然。

下面讲述条件格式的一种设置方法。在图2.24所示的表格中，分别以不同的颜色表示0～60之间、60～80之间、80～100之间的数字。

图2.24可以通过自定义格式或菜单命令实现。用菜单命令实现的过程如下：

＜1＞ 选择要设置条件格式的单元格区域（本例是C3:F8）。

＜2＞ 选择“格式”|“条件格式”菜单命令，弹出如图2.25所示的对话框，从中设置满足条件的数字范围（本例中，从“介于”下拉列表中选择条件“小于”，然后在“介于”右边的编辑框中输入60，意为“小于60”这个条件）。

＜3＞ 设置好条件之后，再设置满足该条件的格式。单击图2.25中的“格式”按钮，弹出如图2.26所示的对话框，从中设置单元格中的字体、字形、字号的大小、字体的颜色，以及单元格的背景色（单击该对话框中的“图案”标签，从中选择一种颜色）和边框。在本例中，设置字体的颜色为“红色”（单击图2.26中“颜色”编辑框旁的下拉按钮，从弹出的调色框中选择“红色”；然后单击“图案”标签，从中选择淡青色作为背景图案）。

＜4＞ 格式设置好后，单击“确定”按钮，返回到图2.25所示的对话框；单击“添加”按钮，将弹出设置第二个条件的对话框，其设置过程与第一个条件的设置过程完全一样。

2.6 自定义格式的应用

在现实工作中，许多工作都可通过单元格格式的设置得到简化，不熟悉格式的应用时将会多做许多额外的事情，主要体现在数据的输入和信息的表现方面。

现举几个格式设置的应用实例，这些实例表明通过格式设置确实可以简化工作，提高效率。

【例2.1】 在数字单元格中增加文本。

如果有较多的单元格或单元格区域需要输入相同的文本，如学生编号、工作人员的代号等，可以通过设置单元格的自定义格式，把编号中相同的部分或文本中相同的部分固定为格式，这样可以减少输入，而且可使数据更为准确。

现在，假设要建立图2.27所示的学生成绩登记表。

输入该成绩表有两个问题：① 学号难以输入且容易出错；② 教师一般是按试卷的顺序输入学生成绩的，试卷没有顺序，往往按试卷中的学号在该成绩表中去查找该学生。

许多老师输入这样的成绩表大部分时间都花在输入学号上。有人采用第1章介绍的填充法很快就能建立学号，但另一个问题产生了：表中的学号是有序的，而输入的成绩（按试卷中的顺序输入）是无序的，这样就需要在该成绩表中查找学生的姓名及学号。

像这类问题的解决方案是把学号一列单元格区域的格式定义为："329901"00。这样在输入学生成绩时只需要输入学号的最后两位编码，其余的编码可由自定义格式扩展产生。例如第一份试卷的学生情况为：32990123，王明，89。我们可直接在第一个学生成绩记录（图2.27为第6行）中输入该学生的成绩。其输入方法是：A6中直接输入“23”，B6中输入“王明”，D6中输入“89”。

依上述方法输入所有的学生成绩，该表做好后，按A列数据排序该表的成绩部分（即A5:F15单元格区域），最后就得到一个有序的符合要求的学生成绩表。这种方法可以节省许多时间。

【例2.2】 设置自定义格式，控制用户输入（使用户输入的数据在有效的范围内）。

利用Excel的自定义格式，可以对输入的数据进行简单的验证，对错误的数据进行提示，如一个人的工作年份不可能小于0，一年的月份不可能超过12，使用产品成本作为除数计算一个销售人员的报酬不允许出现零成本等。

假设要建立一张工作人员的档案表，可以用自定义格式对其中的某些数据进行有效性限制，如图2.28所示。

如果在B列中输入正数，将出现红色的警告信息“姓名不能输入数字”；如果输入一个负数，将会出现“姓名不能是数字”的红色警告信息；如果输入0，就会显示“姓名不能是0”的警告信息。如果输入正确的姓名，Excel就会接受输入，不会显示任何出错信息。在图2.28中，A列表示的是输入信息，B列是输入信息后的显示结果。例如，在单元格B5中输入35，Excel就不会接受该输入，而显示一个红色的出错信息“姓名不能输入数字”。事实上，B列的单元格已被设置成下述的自定义格式了：

[红色]"姓名不能输入数字"；[红色]"姓名不能是数字"；[红色]"姓名不能是0"

D列被设置的自定义格式为：“1997年3月”，所以在单元格D5中输入“01/1/2”，Excel将显示该输入为“2001年1月”。显然，这种格式设置可以简化工作表的数据输入工作。

对于工作年龄和基本工资所在的列，因为这两个数据不可能为负，所以设置其自定义格式为：

G/通用格式；[红色]"工作年龄不能是负数"；[红色]0；[红色]"工作年龄不能是文本"

在这种格式下，当在F列和G列中输入负数或文本时，将用红色显示“工作年龄不能是负数”或“工作年龄不能是文本”之类的提示信息。如果输入0，Excel将会把它显示为红色，提示输入者仔细核查，因为工龄为0的情况并不多见。注：本例用到了颜色格式，有关颜色的自定义格式代码可参考Excel的帮助，或本书的补充材料。

【例2.3】 隐藏不需要的零值。

在许多时候，工作表因某种原因会出现较多的零值。在使用公式时，这种情况尤其突出。图2.29是一个计算学生综合成绩的工作表，其中G列的综合成绩计算公式较为复杂，为了简化工作，在输入学生成绩之前，预先在单元格G3中输入了公式“=SUM(C3:E3*0.7)+F3*0.3”，并将该公式向下复制到了G300（这是最后一个学生综合成绩所在的单元格）。

当某个学生的成绩正确输入之后，Excel会自动按上述公式计算出该生的综合成绩，但如果学生的成绩没有输入，就会在G列的相应单元格中显示一个零值。这是一个较为普遍的问题，如在一些财务工作表、预算工作表、工程管理工作表或工资档案管理工作表中都会出现这种问题。工作人员因某种需要，可能预先在工作表中输入一些计算公式。但没有输入数据，这时就会在计算公式所在的单元格中显示一个零值，和图2.29表示的情况基本相似。

在Excel中，至少有三种方法可以隐藏工作表中的零值。

第一种方法是隐藏整个工作表中所有的零值，操作如下：选择“工具”|“选项”菜单命令，在弹出的“选项”对话框中，选择“视图”标签，然后清除“窗口”栏中“零值”复选框的选中标志。如果要显示零值，只需要选中零值前面的复选标志就行了。

这种方式的缺点是，会把工作表中所有的零值都隐藏起来。如在图2.29中，王五的微机成绩也会被隐藏起来，这就不对了。总之，使用上面的隐藏方法，整个工作表中不会显示一个数值0。

第二种方法是通过单元格的自定义格式设置，把工作表中不需要的零值隐藏起来。这种方法较为实用。操作如下：选中要隐藏零值的单元格区域（本例中选中G3:G12），选择“格式”|“单元格”菜单命令，在弹出的“单元格格式”对话框中选择“数字”标签，然后在“分类”列表中选择“自定义”，并在自定义格式的编辑框中输入格式“G/通用格式；"综合成绩不能为负"；”。

根据前面的知识，可知该自定义格式的意义为：正数将被正常显示，如果计算出的综合成绩是一个负数，将显示一个出错信息“综合成绩不能为负”。如果综合成绩是0，将不会显示。

事实上，在Excel的单元格中，如果不需要显示某种类型的数据，只要通过自定义格式把该单元格格式的相应部分设置为“；”即可。例如，若某单元格的自定义格式为“；；；；”，则不论该单元格中有什么样的数据，都不会显示出来。

第三种方法是通过IF公式设置。这种方法通过对单元格中公式的结果进行判定，如果单元格的最终结果是0，就显示空白；否则，显示单元格的原值。本例中，可在G列要显示综合成绩的单元格中输入以下公式“=IF(SUM(C3:E3*0.7)+F3*0.3＜＞0, SUM(C3:E3*0.7)+F3*0.3,"")”。

注意：这是一个数组输入公式，在输入时须同时按Ctrl+Shift+Enter键。

【例2.4】 定义电话号码、保险号码的简化输入形式。

机器零件编号、电话号码、社会保险号码等数据类型都有一种较为特殊的格式，如电话号码由区号和本局号码组成。机器零件编号的格式可能是XX-XXX-XXXX或其他形式。这几种类型的编号往往由几部分组成，每部分的编码位数是相同的。如果按这些数据类型的原样输入数据，往往效率较低，如果通过Excel的自定义格式，则可高效地输入数据。图2.30是一个通过自定义格式输入电话号码的例子。

全国大多数电话号码都是7位数字，区号则可能有多位。输入电话号码时的麻烦不是号码数字本身，而是要在区号与本局号码之间加一个分隔符，如括号或连接线。但通过自定义格式可以省去这个麻烦。图2.30表示的就是这种意思。

在图2.30中，B列中显示了电话号码的区域的自定义格式为“0####-#######”。该格式将从输入数据的右边自动分出7位数据，然后在它的前面加一个连字符“-”，并且自动在输入数据的最前面添加一个0。例如，在单元格A2中输入数据8963821240，Excel将自动把该数据显示为“0896-3821240”。

但这种格式设置中有一个小问题，对于8位电话号码来说，将会把本局号码的最高位放到区域中显示。例如，输入“2362460128”，Excel会将其显示为“0236-2460128”，而不是“023-62460128”。处理这个问题的简单方法是，完整地输入该电话号码，包括区号。对于前面的问题，就直接在单元格中输入“023-62460128”。毕竟，8位电话号码是少数。

图2.30中的C列显示了电话号码的单元格格式的自定义格式为“(0####)#######”。

小结

工作表的格式化包括：边框线的粗细、虚实、颜色，单元格及单元格区域的字体、字形、字号，工作表及单元格的前景色和背景色、背景图案等。适当地进行工作表的标题、边框线及单元格区域的格式化工作，可以制作出精美的工作表。“自动套用格式”可以简化工作表的格式化过程。把经常要重复处理的工作表做成模板，可以节省格化工作所花费的时间。自定义格式对于单元格的数据输入、数据显示非常重要，在输入大量有规律的数据时，通过单元格自定义格式的设置可以简化输入。此外，用户还可以利用自定义格式对输入到单元格中的数据进行简单的正确性检验。

习题2

2.1 如何设置单元格的边框和底纹？

2.2 单元格的自定义格式分为几部分，自定义格式有哪些功能？

2.3 建立如图2.31所示的职工档案表，并按要求设置各单元格或数据列的格式。

格式设置要求：

（1）大标题二号字，“制表时间”与大标题在同一行上（用Alt+Enter键换行）。

（2）第2、3行为标题行，按图2.31所示进行相关单元格的合并，字号为11号。

（3）设置“职工编号”的自定义格式，在实际输入时，只需要输入数字，由Excel自动进行编号的转换，如输入3，Excel将把它显示为“ZG003”。

（4）设置“家庭电话”与“办公电话”的电话号码格式，前面的区号及括号是自定义格式加上去的，请参考图中的单元格C4。

（5）设置“参加工作时间”的自定义格式，如输入“1998/2/1”，将显示为“一九九八年二月一日”。

（6）设置“基本工资”的数据格式，当输入正数时，正常显示；当输入负数时，显示“工资不会是负数”的错误信息，当输入数据为0时，不显示。

（7）同步骤6设置“工资”和“扣款”中的其他数据列。

（8）计算“实发工资”，并把该列数据设置为人民币数据显示形式。

2.4 用条件格式设置数据的格式，用颜色表示数据的意义和布局。设有学生期末考试成绩表，如图2.32所示，按要求设置数据的格式。

要求：用3种不同的背景色和前景色表示3种不同意义的数据，不及格的背景色为淡红色；60～80分的数据为另一种背景色，80～100分的成绩又是另外一种背景色。

用同样的方法设置3类不同意义数据的前景色。

2.5 设置单元格区域的边框、背景和图案，建立具有立体效果的工作表，如图2.33所示。