第3章数据的编辑与格式化 在Excel中可以对单元格的数据进行编辑处理,通过格式化突出显示重要的数据内容,最后通过打印设置完成打印输出。 视频讲解 3.1数据的编辑 3.1.1修改单元格的内容 选择要编辑内容的单元格,输入数据,按Enter键或Tab键,覆盖原来单元格的数据。如果要修改单元格中的内容,可以双击单元格,或单击选中单元格,然后按F2功能键进入单元格编辑状态,或单击选中单元格,然后定位到编辑栏,均可修改单元格的内容。 另外,可以通过“替换”命令查找并替换数据内容。单击“开始”选项卡,选择“编辑”组中的“查找和替换”|“替换”命令(快捷键为Ctrl+H),可以打开“查找和替换”对话框。 【提示】 当单元格数据内容比较复杂时,建议选中单元格后在编辑栏中输入和编辑数据。 3.1.2清除单元格的内容 在Excel中,选择单元格或数据区域后按键盘上的Delete或Backspace键,或者右击单元格或数据区域,选择其快捷菜单中的“清除内容”命令,只能删除单元格或数据区域的内容(公式和数据),其格式(包括数字格式、条件格式和边框)、批注等内容仍保留。 单击“开始”选项卡上“编辑”组中的“清除”按钮,选择其中的“全部清除”“清除格式”“清除内容”“清除批注”“清除超链接(不含格式)”“删除超链接(含格式)”命令,可以清除单元格或数据区域的全部或部分(格式、内容、批注、超链接等)。其中,“清除超链接(不含格式)”命令仅仅清除超链接而保留格式,“删除超链接(含格式)”命令则清除超链接以及格式。 3.1.3单元格数据的分行处理 通过单击“开始”选项卡,选择“编辑”组中的“填充”|“两端对齐”命令,可以对存放在一个单元格中的数据进行分行处理,填充到同列的各单元格。这些数据往往包含一些整齐的内容,例如四字成语“百里挑一金玉满堂海阔天空满腹经纶春暖花开绘声绘影国色天香金玉良缘掌上明珠”,分行结果参见图31(a); 或者包含用空格分隔的文本或数字,例如“The quick brown fox jumps over the lazy dog”“10000 10 100 1000 1 100 10000 100000 10 1000000”等,分行结果参见图31(b)所示。注意观察分行数据单元格第一列的宽度。 图31单元格数据的分行素材和结果(成语和句子) 【例31】单元格数据的分行示例。打开“fl31分行.xlsx”,参照图32,将A1单元格中的内容“10000 10 100 1000 1 100 10000 100000 10 1000000”分散填充到各行。 【参考步骤】 (1) 将A1单元格的内容复制到A3单元格中。 (2) 选择A3单元格,适当调整A列的列宽到一个较小的数值(以容纳最小的数据为宜),本例调整A列的列宽到可以容纳数据1的位置,参见图32中A7单元格的列宽大小。 (3) 单击“开始”选项卡,选择“编辑”组中的“填充”|“内容重排”命令,Excel会弹出“文本将超出选定区域”提示对话框,单击“确定”按钮。 图32单元格数据的分行素材和结果(用空格分隔的数字) (4) A3单元格中的各数据被自动填充到A列的各单元格中(A3:A12数据区域)。 【注意】 如果单元格的列宽调整得较大,则“分行”后可能某个单元格中会包含多项内容。 (5) 将A1单元格的内容复制到D3单元格中。 (6) 选择D3单元格,参照图32中D3单元格的列宽大小调整D列的列宽(以容纳D3单元格中的数据内容10000)。 (7) 单击“开始”选项卡,选择“编辑”组中的“填充”|“内容重排”命令,在随后弹出的“文本将超出选定区域”提示对话框中单击“确定”按钮。 (8) D3单元格中的各数据被自动填充到D列的各单元格中(D3:D11数据区域),如图32所示。 (9) 注意观察D7单元格,其中存放着1和100两个数据内容,显然因为“内容重排”列宽选择得不合适,造成数据“分行”的错误结果。 3.1.4单元格数据的分列处理 通过单击“数据”选项卡上“数据工具”组中的“分列”按钮,可以拆分Excel某一列中一个或多个单元格的内容,将其作为单独的数据放置到相邻列的单元格中。 【例32】单元格数据的分列示例。打开“fl32分列.xlsx”,参照图33,将A1单元格中的职工编号、姓名、职称、入会日期、基本工资、补贴、奖金和总计信息分别填充到C~J列。 图33单元格数据的分列素材和结果(职工信息) 【参考步骤】 (1) 选择A1:A16单元格。 (2) 单击“数据”选项卡上“数据工具”组中的“分列”按钮。 (3) 使用“文本分列向导”实现数据分列。“文本分列向导”共分为以下3步: ① 选择最合适的文件类型(分隔符号或者固定宽度)。本例选择“分隔符号”,单击“下一步”按钮。 ② 指定字段间使用的分隔符号。指定“其他”分隔符号“#”作为分列数据所包含的分隔符,在“数据预览”区域中观察数据分列结果,单击“下一步”按钮。 ③ 设置各列的数据格式。在“数据预览”区域中单击“编号”列,选择“文本”数据格式。将光标定位到“目标区域”文本框,单击当前工作表的C1单元格,设置分列数据放置的起始位置,如图34所示,单击“完成”按钮。 (4) 参照图33,适当调整C1:J16数据区域中各字段的列宽,设置基本工资、补贴、奖金和总计金额的数据格式,并添加边框。 图34文本分列向导步骤3之3(设置数据格式和目标区域) 【提示】 在文本分列向导步骤3之3中,“常规”数据格式将数值转换为数字。所以本例的“编号”列一定要设置为“文本”数据格式,否则“编号”不能正确显示(只显示数字1、2、3、…,而不是编号0001、0002、0003、…)。类似地,像身份证号等信息,也必须设置为“文本”数据格式才能得到正确的操作结果。 3.1.5多列数据合并成一列 通过“剪贴板”任务窗格,或者使用“&”运算符,或者使用CONCATENATE函数,或者使用Excel的快速填充功能,均可以将多列数据合并成一列,实现单元格合并的功能。 【例33】多列数据合并成一列示例。打开“fl33单元格列合并素材.xlsx”,参照图35,将A列~G列数据区域中的职工编号、姓名、入会日期、基本工资、补贴、奖金和总计信息合并到I列。要求保留入会日期以及基本工资、补贴、奖金和总计信息的原始格式。 图35多列数据合并成一列的素材和结果(职工信息) 【参考步骤】 方法一(在“素材1”工作表中使用“&”运算符完成): (1) 使用“&”运算符,在I1单元格中输入字符串拼接的公式“=A1&" "&B1&" "&TEXT(C1,"yyyy/mm/dd")&" "&TEXT(D1,"¥0,000")&" "&TEXT(E1,"¥0,000")&" "&TEXT(F1,"¥0,000")&" "&TEXT(G1,"¥0,000")”,并向下填充至I16单元格。 (2) 适当调整I列的列宽,并添加边框,结果如图35所示。 【提示】 本例使用TEXT函数保留入会日期以及基本工资、补贴、奖金和总计信息的原始格式。TEXT函数的具体说明请参见本教程第6章。 方法二(在“素材2”工作表中使用CONCATENATE函数完成): (1) 使用CONCATENATE函数,在I1单元格中输入字符串拼接的公式“=CONCATENATE(A1," ",B1," ",TEXT(C1,"yyyy/mm/dd")," ",TEXT(D1,"¥0,000")," ",TEXT(E1,"¥0,000")," ",TEXT(F1,"¥0,000")," ",TEXT(G1,"¥0,000"))”,并向下填充至I16单元格。 (2) 适当调整I列的列宽,并添加边框,结果如图35所示。 3.1.6多行数据合并成一行 通过单击“开始”选项卡,选择“编辑”组中的“填充”|“内容重排”命令,不仅可以实现3.1.3节的单元格数据的分行处理,还可以实现其逆操作——多行数据合并成一行。 【例34】多行数据合并成一行示例。打开“fl34单元格行合并.xlsx”,参照图36,将A2:A16数据区域中的姓名合并到A2单元格中。 图36多行数据合并成一行的结果(姓名清单) 【参考步骤】 (1) 调整A列的列宽到足以容纳A2:A16数据区域中所有姓名的宽度。 (2) 选择A2:A16数据区域,单击“开始”选项卡,选择“编辑”组中的“填充”|“内容重排”命令。 3.1.7删除重复的行 对于Excel数据区域中的内容,可以通过单击“数据”选项卡上“数据工具”组中的“删除重复值”按钮删除数据区域中重复的行信息; 而对于Excel表格内容,不仅可以通过单击“数据”选项卡上“数据工具”组中的“删除重复值”按钮,还可以通过单击“表格工具”的“设计”选项卡中的“删除重复值”按钮删除Excel表格中重复的行信息。 【提示】 在使用“删除重复值”功能时将会永久删除重复数据,所以在删除重复值之前最好将原始数据复制到另一个工作表中,以免意外丢失任何信息。 【拓展】 使用“高级筛选”功能也可以删除数据区域或表格中重复的信息,具体参见本书第12章。 【例35】删除重复的行示例。打开“fl35删除重复项.xlsx”,参照图37,删除Excel表格(位于A1:B40单元格区域)中重复的行内容。 图37删除重复行的素材和结果(供应商信息) 【参考步骤】 方法一(在“素材1”工作表中使用“数据”选项卡中的“删除重复值”按钮): (1) 单击A1:B40数据区域中的任一单元格,使光标定位到表格中。 (2) 单击“数据”选项卡上“数据工具”组中的“删除重复值”按钮,弹出“删除重复值”对话框,选择一个或多个包含重复值的列。本例选择表格的所有列,如图38所示。单击“确定”按钮。 图38选择一个或多个包含重复值的列 (3) Excel将弹出发现并删除了15个重复值、保留了24个唯一值的提示信息,单击“确定”按钮。 方法二(在“素材2”工作表中使用“表格工具”中的“删除重复值”按钮): (1) 单击A1:B40数据区域中的任一单元格,将光标定位到表格中。 (2) 单击“表格工具”的“设计”选项卡上“工具”组中的“删除重复值”按钮,在弹出的“删除重复值”对话框中选择表格的所有列,单击“确定”按钮。 (3) 在随后弹出的提示信息对话框中单击“确定”按钮。 视频讲解 3.2设置单元格格式 通过设置工作表中单元格的格式,可以使数据分析的结果易于阅读理解,更有表现力。单元格的格式包括数字、对齐、字体、边框、填充和保护的设置。设置单元格格式的方法有如下4种: (1) 使用“开始”选项卡中的格式化命令,如图39所示。 图39“开始”选项卡中的格式化命令 图310迷你工具栏 (2) 使用迷你工具栏。在选择单元格或者单元格区域时右击,将显示迷你工具栏,如图310所示,可以快速实现常用的格式化。 (3) 使用“设置单元格格式”对话框。通过快捷键Ctrl+1,或者通过鼠标右键快捷菜单中的“设置单元格格式”命令,或者单击“开始”选项卡上“字体”组右下角的“字体设置”对话框启动器按钮,打开“设置单元格格式”对话框,设置单元格格式。 (4) 使用常用的格式化快捷键。例如,Ctrl+B(粗体)、Ctrl+I(斜体)、Ctrl+U(下画线)、Ctrl+Shift+%(百分比样式)、Ctrl+T(格式化为表格)等。 3.2.1字体 用户可以使用“开始”选项卡上“字体”组中的各命令按钮对所选单元格或区域进行字体、字号、加粗、倾斜、下画线、字体颜色等设置,如图311(a)所示。 当然,也可以通过快捷键Ctrl+1打开“设置单元格格式”对话框,如图311(b)所示,在“字体”选项卡中设置字体格式。 3.2.2对齐 用户可以使用“开始”选项卡上“对齐方式”组中的各命令按钮对所选单元格或区域中的数据进行顶端对齐、垂直居中、底端对齐、左对齐、居中、右对齐、文本方向、自动换行、合并后居中等设置,如图312(a)所示。其中,在“合并后居中”下拉列表中还提供了“合并后居中”“跨越合并”“合并单元格”以及“取消单元格合并”命令,如图312(b)所示。 当然,也可以通过快捷键Ctrl+1打开“设置单元格格式”对话框,在“对齐”选项卡中设置对齐方式。 图311设置字体格式 图312设置对齐方式 3.2.3数字格式 数值数据和日期时间数据在Excel内部均为数值,通过设置其显示格式可以显示为不同的数字格式、货币格式、日期和时间格式。 用户可以使用“开始”选项卡上“数字”组中的各命令按钮对所选单元格或区域中的数字数据进行数字格式、会计数字格式、百分比样式、千位分隔样式、增减小数位数等设置。其中,在“数字格式”下拉列表中提供了“常规”“数字”“货币”“会计专用”“短日期”“长日期”“时间”“百分比”“分数”“科学记数”以及“文本”等数字格式。 当然,也可以通过快捷键Ctrl+1打开“设置单元格格式”对话框,在“数字”选项卡中设置数字格式,如图313所示。 图313设置数字格式 3.2.4边框 用户可以使用“开始”选项卡上“字体”组中的边框按钮对所选单元格或区域进行边框设置,如图314(a)所示。 当然,也可以通过快捷键Ctrl+1打开“设置单元格格式”对话框,在“边框”选项卡中设置边框格式,如图314(b)所示。 图314设置边框格式 3.2.5填充 用户可以使用“开始”选项卡上“字体”组中的填充颜色按钮对所选单元格或区域进行单元格背景色(底纹)设置,如图315(a)所示。 图315设置填充格式(底纹) 当然,也可以通过快捷键Ctrl+1打开“设置单元格格式”对话框,在“填充”选项卡中设置背景色、填充效果、图案颜色、图案样式等填充格式,如图315(b)所示。 3.2.6单元格样式 单元格样式包含单元格的数字、对齐、字体、边框、填充、保护等格式。通过单元格样式既可以保持格式的一致性,也可以减少设置格式的工作量。如果设置多个单元格为相同样式,则可以应用同一个预设单元格样式或者自定义单元格样式,稍后若修改单元格样式,这些单元格样式会自动更改。 1. 应用预定义单元格样式 Excel提供了许多系统预设的单元格样式,如图316所示,用户可以根据自己的喜好在“开始”选项卡上“样式”组的“单元格样式”中选择“好、差和适中”“数据和模型”“标题”“主题单元格样式”以及“数字格式”各系列的样式,设置被选单元格或数据区域的单元格样式。 图316设置单元格样式 2. 新建单元格样式 如果用户对预设的单元格样式不满意,还可以通过扩展的“单元格样式”中的“新建单元格样式”命令自定义单元格样式的数字、对齐、字体、边框、填充、保护等格式。 新建单元格样式的一般步骤如下: (1) 选择一个单元格,设置其样式。 (2) 单击“开始”选项卡,选择“样式”组中的“单元格样式”|“新建单元格样式”命令,打开“样式”对话框。 (3) 输入样式名称。当然,也可以根据需要进一步调整样式。最后单击“确定”按钮,创建样式。 3. 复制其他工作簿中的单元格样式 用户可以通过扩展的“单元格样式”中的“合并样式”命令将其他打开的工作簿中的单元格样式复制到当前工作簿中,以达到合并单元格样式的目的。 4. 使用模板中的单元格样式 基于模板创建的工作簿会继承模板中的单元格样式,因此可以把常用的样式包括在模板中,从而实现样式的共享。 3.2.7自动套用表格格式 Excel需要处理的数据单元格区域组成了数据表格,即第一行为标题,其他各行为数据。使用“套用表格格式”样式功能可以把这种类型的数据转换为表格,并自动应用表格样式,包括粗体显示的标题行格式、交替底纹显示的镶边行格式、交替底纹显示的镶边列格式、边框底纹等。 使用表格格式可以快速实现单元格区域的格式化。 1. 应用预定义表格样式 Excel提供了许多预定义的表格样式,可以用于快速设置表格样式。预定义的表格样式分为3类,即浅色、中等色和深色,如图317所示。 图317套用表格样式 将区域转换为表格并应用表格样式的方法如下: (1) 选择当前单元格所在的区域,按快捷键Ctrl+T,将所选区域转换为表格,并自动应用默认表格样式。 (2) 选择当前单元格所在的区域,按快捷键Ctrl+Q,显示“快速分析”工具栏,选择“表格”选项卡中的“表”,将所选区域转换为表格,并自动应用默认表格样式。 (3) 选择当前单元格所在的区域,通过单击“开始”选项卡上“样式”组中的“套用表格样式”命令按钮,打开表格格式下拉列表,选择其中的表格样式,将所选区域转换为表格,并应用所选表格样式。 (4) 选择当前单元格所在的区域,通过选择“插入”选项卡上“表格”组中的“表格”命令,将当前单元格所在的区域转换为表格,并自动应用默认表格样式。 2. 新建表格样式 用户也可以根据需要创建和应用自定义表格样式。新建表格样式的方法如下: (1) 新建表格样式。单击“开始”选项卡,选择“样式”组中的“套用表格格式”|“新建表格样式”命令,打开“新建表样式”对话框,设置各项目的样式。 (2) 复制表格样式并修改。右击已经存在的表格样式,选择快捷菜单命令“复制”,创建该表格样式的副本。然后在表格样式中右击该副本,选择快捷菜单命令“修改”,打开“修改表样式”对话框,进行样式的修改。 3.2.8复制和删除格式 几种比较方便、快捷的复制格式的方法如下: (1) 单击“开始”选项卡上“剪贴板”组中的“格式刷”按钮。 (2) 选择源区域复制后,在目标区域右击,单击“粘贴选项”中的“格式”按钮。 (3) 选择源区域复制后,在目标区域右击,选择“选择性粘贴”命令,在弹出的对话框中选择“格式”选项。 单击“开始”选项卡上“编辑”组中的“清除”按钮,选择其中的“清除格式”命令,可以删除单元格或数据区域的所有格式。 3.2.9设置文档主题 在Office(包括Excel)中,主题(Theme)包含各种主题风格的颜色、字体和效果(图形),可以通过选择并应用主题实现专业级别的文档格式风格。 Excel中包含了若干预定义主题,用户也可以根据需要自定义主题,还可以下载并使用主题。 1. 应用预定义主题 单击“页面布局”选项卡,选择“主题”组中的“主题”命令,选择要应用的主题,如图318所示。 图318选择要应用的主题 2. 自定义主题 自定义主题的一般步骤如下: (1) 应用预定义主题。在预定义主题的基础上进行主题的定制化。 (2) 自定义主题颜色。通过“页面布局”选项卡上“主题”组中的“颜色”命令选择主题的颜色,也可以使用“自定义颜色”命令自定义颜色。 (3) 自定义主题字体。通过“页面布局”选项卡上“主题”组中的“字体”命令选择主题的字体,也可以使用“自定义字体”命令自定义字体。 (4) 自定义效果(图形)。通过“页面布局”选项卡上“主题”组中的“效果”命令选择主题的效果。 (5) 保存当前主题。通过“页面布局”选项卡上“主题”组中的“主题”|“保存当前主题”命令保存主题文件。 3. 使用主题文件 通过“页面布局”选项卡上“主题”组中的“主题”|“浏览主题”命令可以使用、保存或者下载共享主题文件中的主题。 【例36】设置文档主题。打开“fl36文档主题.xlsx”,设置不同主题,对比各种主题的效果,如图319所示。 图319设置文档主题 【参考步骤】 (1) 设置文档主题为“切片”,效果如图319(a)所示。 (2) 设置文档主题为“电路”,效果如图319(b)所示。 3.2.10格式化应用举例 【例37】格式化应用示例。打开“fl37格式化.xlsx”,参照图320,对单元格进行格式化。 图320格式化应用示例 【参考步骤】 (1) 自动套用表格格式。选择表格区域中的任意单元格(例如D4),按快捷键Ctrl+T,把D4所位于的单元格区域A3:D808转换为表格,并自动应用默认表格样式。 (2) 调整列宽。选择列A到列D,双击列分隔线,自动调整列宽。 (3) 设置数字格式。选择D4:D808(提示: 选择D4单元格,然后按快捷键Ctrl+Shift+↓),从“数字格式”下拉列表框中选择“货币”,减少小数位数以保留数值的整数部分。 (4) 设置标题格式。选择A1:D1,然后选择“合并后居中”命令,应用单元格样式“标题1”。 (5) 把表转换为区域。选择表格中的任意单元格(例如D4),然后选择“表格工具”|“设计”|“工具”|“转换为区域”命令。 视频讲解 3.3数字格式 数字格式主要用于设置数值、日期和时间的格式,是Excel数据格式化的重点和难点。 3.3.1预定义数字格式 在“开始”选项卡的“数字”组中包含了最常用的数字格式化命令,分别用于设置会计格式、百分比格式、千分位格式、增加小数部分位数以及减少小数部分位数。 在“开始”选项卡上“数字”组的数字格式列表框中包含了预定义的数字格式,预定义的数字格式的含义参见表31。 表31预定义数字格式的含义 格式说明 常规输入数字时Excel所应用的默认数字格式。在多数情况下,采用“常规”格式的数字以输入的方式显示。如果单元格的宽度不够显示整个数字,则“常规”格式会用小数点对数字进行四舍五入。“常规”数字格式还对较大的数字(12位或更多位)使用科学记数(指数)表示法 数值数字的一般表示。可以指定要使用的小数位数、是否使用千位分隔符以及如何显示负数 货币显示带有默认货币符号的货币值。可以指定要使用的小数位数、是否使用千位分隔符以及如何显示负数 会计专用显示货币值,但是会对齐货币符号和数字的小数点 日期日期又分为“短日期”和“长日期”两种。根据指定的类型和区域设置(国家/地区),将日期和时间序列号显示为日期值。以星号(*)开头的日期格式受在“控制面板”中指定的区域日期和时间设置的影响,不带星号的格式不受“控制面板”设置的影响 时间根据指定的类型和区域设置(国家/地区),将日期和时间序列号显示为时间值。以星号(*)开头的时间格式受在“控制面板”中指定的区域日期和时间设置的影响,不带星号的格式不受“控制面板”设置的影响 百分比将单元格值乘以100,并用百分号(%)显示结果。可以指定要使用的小数位数 分数根据所指定的分数类型以分数形式显示数字 科学记数以指数表示法显示数字,用E+n替代数字的一部分。例如,两位小数的“科学记数”格式将12345678901显示为1.23E+10,即用1.23乘以10的10次幂。可以指定要使用的小数位数 文本将单元格的内容视为文本,并在输入时准确显示内容,即使输入数字也是如此 【说明】 将数字格式应用于单元格之后,如果Microsoft Excel在单元格中显示#####,则可能是单元格列宽不够,无法正常显示该数据。此时可以双击包含出现#####显示信息的单元格的列的右边界自动调整列宽,当然也可以通过拖动右边界调整列宽,以正常显示该数据。 3.3.2自定义数字格式 1. 自定义格式代码 在自定义格式代码中,用西文半角的分号分隔不同的区段,每个区段的代码作用于相应类型的数据。 完整的自定义格式代码的组成结构如下: 条件值1格式; 条件值2格式; 不满足条件值1&条件值2的格式; 文本格式 【注意】最多只能在前两个区段使用运算符表示条件值,第3个区段自动以除前两个区段条件以外的情况作为其条件值。例如,成绩不低于90分时显示“优秀”,低于90分且不低于60分时显示“合格”,低于60分时显示“不合格”。Excel不接受以下的自定义格式代码: [>=90]"优秀";[>=60]"合格";[<60]"不合格" 正确的自定义格式代码为: [>=90]"优秀";[>=60]"合格";"不合格" 在实际应用中,用户无须每次都严格按照4个区段编写格式代码。对于包含条件值的格式代码而言,区段至少两个。不足4个区段的条件值代码结构的含义如表32所示。 表32不足4个区段的条件值代码结构的含义 区段数条件值代码结构的含义 2区段1作用于满足条件值1的数据,区段2作用于满足条件值2的数据 3 区段1作用于满足条件值1的数据,区段2作用于满足条件值2的数据,区段3作用于其他情况的数据 在未指定条件值时,默认的条件值为0,因此自定义格式代码的组成结构也可简化为: 正数格式;负数格式;零值格式;文本格式 同样,用户无须每次都严格按照4个区段来编写格式代码。如果仅指定了两个区段代码,则第一部分用于正数和零值,第二部分用于负数。如果仅指定了一个区段代码,则该部分将用于所有数字。如果要跳过某一区段代码,则必须为要跳过的区段保留分号。简化的自定义格式代码结构的含义如表33所示。 表33简化的自定义格式代码结构的含义 区段数条件值代码结构的含义 1格式代码作用于所有类型的数据(正数、负数、零值、文本) 2区段1作用于正数和零值,区段2作用于负数 3区段1作用于正数,区段2作用于负数,区段3作用于零值 例如,对于自定义格式代码: [绿色]+0;[红色]-0 正数和0显示为绿色并且带+号,负数显示为红色并且带-号。 【技巧】 如果希望隐藏单元格中输入的任何数据,则自定义格式代码为“;;;”,但是不能隐藏由公式产生的#REF、VALUE!、#NAME?等错误提示信息。 2. 单元格格式代码 用户可以使用表34中的单元格格式代码自定义数字格式,其中“”表示空格。 表34单元格数字格式代码及含义 代码含义示 例 代 码示 例 数 据显 示 结 果 G/通用格式常规格式G/通用格式123.455123.455 0数字占位符,不足位数使用0填充000000 000.0078.9 78.9000079 078.90 #数字占位符,只显示有效数字,不显示无意义的零值#.## #.##123.4 123.1263123.4 123.13 ?(1) 数字占位符,不足位数使用空格填充,以实现对齐小数点的效果 (2) 用于显示分数分母的位数0.?? #??/??? #??/??? #??/???123.4 0.25 1.25 12.008123.4 1/4 11/4 121/125 .小数点0. 0.#12 12.3812. 12.4 ,千分位分隔符。若在代码的最后出现“,”,则将数字缩小为原来的千分之一#,# #,#,千 #,#,,百万 0.0,,百万123456789 123456789 123456789 123456789123,456,789 123,457千 123百万 123.5百万 %显示百分数0.0% #%0.3456 0.345634.6% 35% E科学记数法0.00E+00567895.68E+04 ymd hms mmm mmmm ddd dddd aaa aaaa年月日。yyyy对应4位年份、yy对应两位年份; mm、dd对应两位月和天 时分秒。hh、mm、ss对应两位时、分和秒 英文月份的缩写 英文月份的全称 英文星期的缩写 英文星期的全称 中文星期的缩写 中文星期的全称yyyy/mm/dd yy/m/d m/d yyyy hh:mm:ss h:m:s mmm d, yyy mmmm, yyyy m/d, ddd m/d, dddd mm/dd, aaa mm/dd, aaaa2020/4/1 10:08:122020/04/01 20/4/1 4/1 2020 10:08:12 10:8:12 Apr 1,2020 April,2020 4/1,Wed 4/1, Wednesday 04/01,三 04/01,星期三 "引号。用于显示汉字"人民币"#,#1234人民币1,234 续表 代码含义示 例 代 码示 例 数 据显 示 结 果 \占位符,用于显示其后一位字符 用于转义特殊符号,例如.、"等0\.000\.0\.019216811192.168.1.1 !占位符,用于显示其后一位字符 用于转义特殊符号,例如.、"等0!.000!.0!.0 #!" #!"!"19216801 100 100192.168.0.1 100" 100"" @文本占位符@"再"@ ; ; ; @@@努力 学习努力再努力 学习学习学习 *重复其后一个字符,并填充至列宽0* **; **; **; **12345 1234567812345 ************ _添加与其后一个字符等宽的空格,可用于将正负数小数点对齐0.00_); (0.00) 0.00_); (0.00)-123.456 3.3(123.46) 3.30 [条件]使用比较运算符及数值设置条件[>=90]"优秀"; [>=60]"合格"; "不合格"62 98 54合格 优秀 不合格 [颜色]8种颜色名称: 黑色、绿色、白色、蓝色、洋红色、黄色、蓝绿色、红色或者颜色索引1~56[蓝色]; [红色]; [黄色]; [绿色]10 0 -10 努力正数10显示为蓝色,零显示为黄色,负数-10显示为红色,文本"努力"显示为绿色 【注意】 对于单元格[颜色]格式,在英文版Excel中使用英文颜色代码(56种),在中文版Excel中必须使用中文颜色代码(8种)。 3. Excel颜色索引 Excel颜色索引对照表如表35所示。 表35Excel颜色索引对照表 索引号颜色索引号颜色索引号颜色索引号颜色 1黑色 Black 2白色 White 3红色 Red 4鲜绿色 Bright Green 5蓝色 Blue 6黄色 Yellow 7粉红色 Pink 8青绿色 Turquoise 9深红色 Dark Red 10绿色 Green 11深蓝色 Dark Blue 12深黄色 Dark Yellow 13紫罗兰 Violet 14青色 Teal 15灰25%色 Gray25% 16灰50% Gray50% 17海螺色 Periwinkle 18梅红色 Plum+ 19象牙色 Ivory 20浅青绿色 Lite Turquoise 续表 索引号颜色索引号颜色索引号颜色索引号颜色 21深紫色 Dark Purple 22珊瑚红 Coral 23海蓝色 Ocean Blue 24冰蓝色 Ice Blue 25深蓝色 Dark Blue+ 26粉红色 Pink+ 27黄色 Yellow+ 28青绿色 Turquoise+ 29紫罗兰 Violet+ 30深红色 Dark Red+ 31青色 Teal+ 32蓝色 Blue+ 33天蓝色 Sky Blue 34浅青绿 Light Turquoise 35浅绿色 Light Green 36浅黄色 Light Yellow 37淡蓝色 Pale Blue 38玫瑰红色 Rose 39淡紫色 Lavender 40茶色 Tan 41浅蓝色 Light Blue 42水绿色 Aqua 43酸橙色 Lime 44金色 Gold 45浅橙色 Light Orange 46橙色 Orange 47蓝灰色 BlueGray 48灰色40% Gray40% 49深青色 Dark Teal 50海绿色 Sea Green 51深绿色 Dark Green 52橄榄色 Olive Green 53褐色 Brown 54梅红色 Plum 55靛蓝色 Indigo 56灰色80% Gray80% 3.3.3数字格式化应用举例 【例38】数值数据的输入和格式化示例。打开“fl38输入编辑(数值).xlsx”,参照图321,按照以下要求输入数据内容并设置数据格式。 (1) 在C2:C7单元格区域以万为单位显示账户余额。 (2) 在D2:D7单元格区域显示金额的中文大写数字信息。 (3) 在E2:E7单元格区域显示金额的中文小写数字信息。 (4) 在G2:G7单元格区域显示成绩的等级信息。成绩≥90显示绿色的“优秀”,<60显示红色的“不及格”,其他分数显示蓝色的“及格”。 (5) 性别的快速输入。在H2:H7单元格区域通过输入数字“1”和“0”代替“男”和“女”的输入。 图321数值数据的输入和编辑 【参考步骤】 (1) 以万为单位显示账户余额。选中C2:C7单元格区域并右击,选择其快捷菜单中的“设置单元格格式”命令,然后选择“自定义”分类,在“类型”中输入“0!.0000"万"”。 (2) 将数字转换为中文大写信息。选中D2:D7单元格区域并右击,选择其快捷菜单中的“设置单元格格式”命令,然后选择“特殊”分类,在“类型”中选择“中文大写数字”。 (3) 将数字转换为中文小写信息。选中E2:E7单元格区域并右击,选择其快捷菜单中的“设置单元格格式”命令,然后选择“特殊”分类,在“类型”中选择“中文小写数字”。 (4) 将百分制成绩转换为等级信息。选中G2:G7单元格区域并右击,选择其快捷菜单中的“设置单元格格式”命令,然后选择“自定义”分类,在“类型”中输入“[绿色][>=90]"优秀"; [红色][<60]"不及格"; [蓝色]"及格"”。 (5) 快速输入性别。选中H2:H7单元格区域并右击,选择其快捷菜单中的“设置单元格格式”命令,然后选择“自定义”分类,在“类型”中输入“[=1]"男"; [=0]"女"; 输入有误; 输入有误”。输入数字“1”,将显示“男”; 输入数字“0”,将显示“女”; 对于其他输入,均显示“输入有误”。 【说明】 (1) 在自定义单元格格式时,还可以用“0\.0000"万"”或“0"."0000"万"”的方式以万为单位显示账户余额。 (2) 在自定义格式代码中最多只能在前两个区段设置条件,例如本例中的“[=1]"男"; [=0]"女"; 输入有误; 输入有误”。 【例39】文本数据的输入和编辑示例。打开“fl39输入(文本).xlsx”,参照图322,按照以下要求输入数据内容并设置数据格式。 (1) 输入班级信息。在B2:B7单元格区域输入“1”“2”等数字时显示为“1班”“2班”,即数字后面自动加上“班”。 (2) 输入城市地址信息。在C2:C7单元格区域输入城市和街道信息,其中城市和街道之间要分行。 (3) 输入邮政编码信息。在D2:D7单元格区域以“邮政编码”格式输入数据内容。 (4) 输入固话区号信息。在E2:E7单元格区域利用西文半角的前缀符号“'”输入数据内容。 (5) 输入符号信息。在F2:F7单元格区域输入各种符号。 图322输入文本数据 【参考步骤】 (1) 输入班级信息。选中B2:B7单元格区域并右击,选择其快捷菜单中的“设置单元格格式”命令,然后选择“自定义”分类,在“类型”中输入“;;;@"班"”。参照图322,在B2:B7数据区域输入“1”“2”等数字。 (2) 输入城市地址信息。在C2:C7单元格区域,利用Alt+Enter键实现城市和街道信息的分行输入。 (3) 输入邮政编码信息。选中D2:D7单元格区域并右击,选择其快捷菜单中的“设置单元格格式”命令,选择“特殊”分类中的“邮政编码”类型,然后输入数据内容。 (4) 输入固话区号信息。在E2:E7单元格区域输入固话区号,注意在其前面加西文半角的“'”号。 (5) 输入符号信息。在F2:F7单元格区域分别选择“插入”|“符号”命令,在“符号”对话框中利用Wingdings、Symbol、(普通文本)等字体输入各种符号。 视频讲解 3.4条件格式化 条件格式化基于条件更改单元格区域、Excel表格以及数据透视表的外观: 突出显示所关注的数据; 强调异常值; 使用数据条、色阶(颜色刻度)和图标集来直观地显示数据。 3.4.1设置条件格式 通过“开始”选项卡上“样式”组中的“条件格式”|“突出显示单元格规则”“最前/最后规则”“数据条”“色阶”以及“图标集”等命令可以设置所选数据的条件格式,如图323所示。 图323条件格式 1. 突出显示单元格规则 此命令用于对满足指定条件(大于、小于、介于、等于、文本包含、发生日期、重复值)的单元格选择或设置数字、字体、边框和填充等格式。如果要设置“大于或等于”“不介于”“不等于”以及是否空值、是否错误值等条件,可以利用“其他规则”命令新建条件格式规则。 2. 项目选取规则 此命令用于对排名靠前或者靠后(前10项、前10%、最后10项、最后10%、高于平均值、低于平均值)的数值设置格式。其中,排名名次或百分比可以在相应的设置对话框中根据实际需求进行更改。 3. 数据条 此命令用于添加带(渐变或实心)颜色的数据条,以代表某个单元格中的值,值越大数据条越长。 4. 色阶 此命令用于为所选单元格区域添加颜色渐变,以帮助用户了解数据分布和数据变化。颜色指明每个单元格值在该区域内的位置。 5. 图标集 此命令用于选择一组图标(包括方向、形状、标记、等级图标样式),以代表所选单元格中的值。 使用图标集可以对数据进行标注,并可以按阈值将数据分为3~5个类别。每个图标代表一个值的范围。 例如,使用“五象限图标集”标识成绩五级制(优、良、中、及格、不及格)等级信息,“黑色圆”图标代表优(90~100分),“四分之一为白色的圆”图标代表良(80~89分),“四分之二为白色的圆”图标代表中(70~79分),“四分之三为白色的圆”图标代表及格(60~69分),“纯白圆”图标代表不及格(<60分)。 3.4.2新建条件格式 新建条件格式规则有以下3种方法: (1) 选择“开始”选项卡上“样式”组中的“条件格式”|“突出显示单元格规则”“最前/最后规则”“数据条”“色阶”以及“图标集”中的“其他规则”命令新建条件格式规则。 (2) 选择“开始”选项卡上“样式”组中的“条件格式”|“新建规则”命令。 (3) 选择“开始”选项卡上“样式”组中的“条件格式”|“管理规则”命令,在随后弹出的“条件格式规则管理器”对话框中单击“新建规则”按钮。 3.4.3删除条件格式 删除条件格式规则有以下两种方法: (1) 选择“开始”选项卡上“样式”组中的“条件格式”|“清除规则”命令,可以清除所选单元格或者整个工作表、所选Excel表格、所选数据透视表的规则。 (2) 在“条件格式规则管理器”对话框中选中不再需要的条件规则,单击“删除规则”命令按钮。 3.4.4管理条件格式 选择“开始”选项卡上“样式”组中的“条件格式”|“管理规则”命令,将弹出“条件格式规则管理器”对话框,可以查看、新建、编辑和删除工作表中所有的条件格式规则,如图324所示。 图324“条件格式规则管理器”对话框 3.4.5使用“快速分析”工具栏创建条件格式 选择要条件格式化的数据区域,在右下角将显示快速分析图标,单击该图标(快捷键为Ctrl+Q)将显示“快速分析”工具栏,选择“格式化”选项卡中的条件格式化命令可以快速设置条件格式,如图325所示。 图325使用“快速分析”工具栏设置条件格式 3.4.6使用公式自定义条件格式 使用预定义的条件格式规则可以快速实现大多数条件格式化需求,对于复杂的条件格式化,可以使用公式来实现自定义条件格式。 使用公式自定义条件格式的方法如下: (1) 选择要格式化的区域。 (2) 新建使用公式自定义条件格式的规则。选择“开始”选项卡上“样式”组中的“条件格式”|“新建规则”命令,打开“新建格式规则”对话框,如图326所示。选择“使用公式确定要设置格式的单元格”,然后输入公式,并设置格式。 图326新建使用公式自定义条件格式的规则 常见的使用公式自定义格式的例子如下: (1) 偶数行填充颜色。使用公式“=ISEVEN(ROW())”。 (2) 最大值的行填充颜色。使用公式“=$A1=MAX($A$1:$A$100)”。 (3) 出生日期为上半年的行填充颜色。使用公式“=MONTH($A1)<=6”。 3.4.7条件格式化应用举例 【例310】条件格式化示例。打开“fl310条件格式.xlsx”,参照图327,设置学生的语文、数学、英语、物理、化学、政治、生物7门课程成绩的条件格式。具体要求如下: (1) 语文成绩。大于或等于90分以黄色填充色、绿色文本突出显示,小于60分以浅红色填充色、深红色文本突出显示。 (2) 数学成绩。低于平均分的数学成绩用绿色字体、浅红色填充突出显示。 (3) 英语成绩。以黄色渐变填充、黑色实心边框的数据条显示英语成绩。 (4) 物理成绩。以浅红色填充色、深红色文本突出显示前5名的物理成绩。 (5) 化学成绩。使用“五象限图标集”标识化学成绩五级制(优、良、中、及格、不及格)等级信息,其中“黑色圆”图标代表“优”(90~100分),“四分之一为白色的圆”图标代表“良”(80~89分),“四分之二为白色的圆”图标 代表“中”(70~79分),“四分之三为白色的圆”图标代表“及格”(60~69分),“纯白圆”图标代表“不及格”(<60分)。 (6) 政治成绩。采用色阶(渐变色)的方式标识政治成绩,其中100分为绿色,0分为红色,中间值60分为黄色。 (7) 生物成绩。使用红色十字图标标识出生物不及格的学生成绩。 (8) 使用橙色填充标识出数学成绩小于平均分的学生姓名。 图327条件格式化的结果(学生成绩信息) 【参考步骤】 (1) 设置语文成绩条件格式。 ① 选中E2:E17单元格区域,选择“开始”选项卡上“样式”组中的“条件格式”|“突出显示单元格规则”|“小于”命令。 ② 弹出“小于”对话框,在左边的文本框中输入“60”,右边的格式选择“浅红填充色深红色文本”,单击“确定”按钮。 ③ 保持E2:E17单元格区域处于选中状态,选择“开始”选项卡上“样式”组中的“条件格式”|“突出显示单元格规则”|“其他规则”命令。 ④ 在弹出的“新建格式规则”对话框中选择单元格值的条件为“大于或等于”,在其后的文本框中输入“90”。单击“格式”按钮,在弹出的“设置单元格格式”对话框中设置黄色填充、绿色字体格式,单击“确定”按钮,关闭“设置单元格格式”对话框。然后单击“确定”按钮,关闭“新建格式规则”对话框。 (2) 设置数学成绩条件格式。 ① 选中F2:F17单元格区域,选择“开始”选项卡上“样式”组中的“条件格式”|“最前/最后规则”|“低于平均值”命令。 ② 在弹出的“低于平均值”对话框中选择“针对选定区域,设置为”下拉列表框中的“自定义格式”命令,设置绿色字体、浅红色填充数据格式。 (3) 设置英语成绩条件格式。 ① 选中G2:G17单元格区域,选择“开始”选项卡上“样式”组中的“条件格式”|“数据条”|“其他规则”命令。 ② 在弹出的“新建格式规则”对话框中选择“填充”为“渐变填充”,填充“颜色”为“黄色”; 选择“边框”为“实心边框”,边框“颜色”为“黑色”。 (4) 设置物理成绩条件格式。 ① 选中H2:H17单元格区域,选择“开始”选项卡上“样式”组中的“条件格式”|“最前/最后规则”|“前10项”命令。 ② 在弹出的“前10项”对话框中将“10”改为“5”,选择“浅红填充色深红色文本”格式。 (5) 设置化学成绩条件格式。 ① 选中I2:I17单元格区域,选择“开始”选项卡上“样式”组中的“条件格式”|“图标集”|“其他规则”命令。 ② 在弹出的“新建格式规则”对话框中选择“图标样式”为“五象限图”,然后参照图328,将“类型”分别改为“数字”,将“值”分别改为90、80、70、60。 图328新建格式规则(化学成绩五级制等级) (6) 设置政治成绩条件格式。 ① 选中J2:J17单元格区域,选择“开始”选项卡上“样式”组中的“条件格式”|“色阶”|“其他规则”命令。 ② 在弹出的“新建格式规则”对话框中选择“格式样式”为“三色刻度”,然后参照图329,分别将“最小值”“中间值”和“最大值”的“类型”改为“数字”,将“值”分别改为0、60、100,将“颜色”分别改为红色、黄色、绿色。 图329新建格式规则(政治成绩渐变色阶) (7) 设置生物成绩条件格式。 ① 选中K2:K17单元格区域,选择“开始”选项卡上“样式”组中的“条件格式”|“图标集”|“其他规则”命令。 ② 在弹出的“新建格式规则”对话框中选择“图标样式”为“三个符号(无圆圈)”,将前两行的图标分别改为“无单元格图标”和“红色十字图标”,将“类型”均改为“数字”,将“值”分别改为60、0,此时“图标样式”会变成“自定义”。 (8) 使用橙色填充标识出数学成绩小于平均分的学生姓名。 ① 选中B2:B17单元格区域,选择“开始”选项卡上“样式”组中的“条件格式”|“新建规则”命令。 ② 在弹出的“新建格式规则”对话框中选择规则类型为“使用公式确定要设置格式的单元格”,在“为符合此公式的值设置格式”下的文本框中输入公式“=$F2