excle chapter-4.ppt

第4章数据格式化 Excel与数据处理第5版 本章学习目标 本章主要掌握以下内容 1 了解Excel格式化的意义2 掌握套用表格式和自定义格式化方法3 掌握应用主题格式化工作表的方法4 掌握文本格式化的方法5 掌握数据格式化的方法6 了解自定义格式化数据的方法和意义7 掌握日期格式化方法8 掌握条件格式的应用和规则管理方法 4 1工作表的格式化 Excel的格式的几个特点 应用了格式的单元格会一直保存其设置的格式 直到重新为它设置新格式或删除该格式 编辑一个单元格的内容 如输入 修改或删除单元格的数据 时 不会影响单元格原有的格式 当复制或剪切一个单元格 或区域 然后将它们粘贴到其他单元格 区域 时 单元格的格式也会一起被传递 4 1工作表的格式化 4 1 1 自动套用表格式表是Excel2010中的一种特殊表格 Excel为表预定义了许多样式 这些样式布局美观 色彩搭配合理 具有不同类型的网格线设置 用户可在工作表中直接套用这些样式 多快好省地设计出精美的工作表 下图是一个刚建立的普通的工作表 该表存在以下问题 1 表名不醒目 2 表的标题不太醒目 3 当学生较多 科目成绩较多时 看数据较困难 4 整个成绩表不太美观 4 1 1自动套用表格式 解决方法 对工作表进行格式化套用的Excel内置某种表格式后的工作表 套用表格式后 设置标题居中 并加大了字号 2 套用表格式的过程 1 单击要格式化的工作表中的任一单元格或选中要格式化的单元格区域 2 单击 开始 样式 组 套用表格格式 按钮 显示出表格式对话框 4 1 1自动套用表格式 表格式选择对话框 这些都是表格式 可以选择其中的任何一种应用于当前的工作表中 4 1 1自动套用表格式 3 表工具当激活表中的任一单元格后 单击表中的任一单元格 Excel在就会在功能区中显示出 表工具 设计 选项卡 此选项卡平时处于隐藏不显状态 表工具的设计选项卡中有许多格式化表的功能按钮 镶边行 列即将表的奇数据行列设置为一种色彩 偶数行列设置为另一色彩 删除重复行可轻松去掉表中的重复数据 转换为区域将表转换为普通工作表 4 1 2应用主题格式化工作表 主题主题是Office2010新采用的一套统一的设计元素和配色方案 是为文档提供的一套完整的格式集合 其中包括主题颜色 配色方案的集合 主题文字 标题文字和正文文字的格式集合 和相关主题效果 如线条或填充效果的格式集合 利用文档主题 可以非常容易地创建具有专业水准 设计精美 时尚的文档 4 1 2应用主题格式化工作表 例4 2 有学生成绩表如图4 5所示 应用主题对该工作表进行格式化 主题位于 页面布局 选项卡中 这些都是主题 将鼠标指向它 就会实时看见将它应用于当前表格的效果 4 1 3应用单元格样式格式化工作表 1 关于单元格样式Excel2010设计好了许多单元格样式 并将它们内置在Excel中 在进行工作表的格式化工作时 可以直接应用这些样式来格式化单元格或单元格区域 2 应用格式化单元格的步骤 1 选中要应用单元格样式的单元格或单元格区域 2 单击 开始 样式 组 单元格样式 按钮 弹出 单元格样式 选项板 如Page69图4 6所示 3 将鼠标指向 单元格样式 选项板中的各命令按钮 立即就会显示出选中单元格应用对应样式后的效果 4 1 3应用单元格样式格式化工作表 单元格样式 位于 开始 选项卡中 单元格样式就是应用色彩 字体 字型 背景和前景格式化单元格或区域 4 1 4设置工作表的边框和底纹 1 工作表的边框和底纹Excel的默认边框是虚线 无底纹 所有的表格都表示为同一种样式 如果直接在Excel下进行表格的打印则没有网格线 当然 可通过打印设置让它显示表格线 不便于特殊信息的突出显示 通过边框和底纹的设置 一方面可使表格美观 同时也可改变信息的显示方式 让人们尽快掌握表中的信息 4 1 4设置工作表的边框和底纹 2 设置边框和底纹后的工作表 例4 3 对于例4 1中所示的学生成绩表 设置边框和底纹之后 可得到如图4 7所示的表格 4 1 4设置工作表的边框和底纹 3 取消工作表的网格线 单击 视图 选项卡 取消 网格线 复选框中的勾选 4 1 4设置工作表的背景和水印 1 背景背景即置于工作表表格下面的图案 其作用是美化工作表 例4 4 某电机厂的产品销售记录如图4 9所示 为它设置一幅背景图 1 单击 页面布局 页面设置 背景 按钮 弹出为工作表背景选择文件的对话框 2 通过工作表背景对话框查找并选择要设置为工作表背景的磁盘图片文件 该文件就会被设置为活动工作表的背景 本例选择的背景文件是Windows系统默认安装的示例图片目录中的 Bluehills jpg 图像文件 4 1 4设置工作表的背景和水印 2 水印工作表背景不会被打印 也不会保留在单个工作表中或保留在另存为网页的项目中 在某些报表中 需要显示出背景图片 例如 在背景图中指明工作表属于机密信息 这类背景图片常称为水印 Excel2010没有提供水印功能 如果需要在每张打印的报表中显示出水印图形 可以通过页眉或页脚来实现 在页眉或页脚中插入图形 该图形就会从每页的顶部或底部开始显示在文本背后 调整图形或缩放图形大小 让它填充整个页面就制作出了水印 2 水印 1 单击 视图 选项卡 工作薄视图 组中的 页面布局 按钮 切换到页面视图 2 单击中间部分的 页眉 显示出 页眉页脚工具 设计 选项卡 3 单击 图片 从弹出的图片对话框中指定水印的背景图文件 4 单击 设置图片格式 弹出 设置图片格式 对话框 5 取消 锁定纵横比 和 相对原始图片大小 的勾选 6 设置 高度 和 宽度 为实际的纸张大小 4 1 6设置三维表格 通过对单元格的格式设置 自定义格式 可以创建上凸和下凹的立体效果 数据显示在这样的表格中 看起来更轻松 三维表格示例 4 1 6设置三维表格 三维表格的设置方法 1 取消工作表的网络线 2 把要设置三维效果的表格区域的底纹设置为浅灰色 一般情况下是把整个工作表的底纹都设置为浅灰色 当然 其他色彩的浅色也可以 但浅灰色的立体视觉效果更佳 4 2单元格常用格式化命令 文本的格式设置 设置字体格式主是通过 设置单元格格式 对话框进行 单击 开始 选项卡中 对齐方式 的下箭头可显示出此对话框 文本的格式设置 数据对齐方式在默认情况下 文本靠单元格的左边对齐 数值靠单元格的右边对齐 有时需要特殊的对齐方式 如斜线表头 旋转字体 垂直居中等例如 文本的格式设置 通过跨列居中按扭合并单元格选中要合并的单元格 单击跨列居中按钮取消单元格的合并选中要合并的单元格 单击跨列居中按钮通过菜单设置对齐也可以选择 格式 单元格 菜单 然后在对话框中进行设置 文本的格式设置 垂直对齐方式文本 数字的旋转同一单元格文本换行 单元格换行按钮同一单元格文本换行 Alter Enter 文本的格式设置 单元格的自动换成行格式自动换行能够根据表格的列宽自动将单元格中的内容排成一行或多行 对于制订内容较多的工作表标题行时很有用 下图第2行的标题就是通过单元格的自动换行格式设计而成的 单击它可设置或取消当前选中单元格或区域的自动换行格式 4 3格式化数据 1 Excel的自动数据格式数字 日期 时间等类型的数据在Excel中都是以数字形式存储的 同样的数字在具有不同格式的单元格中会表现出不同的形式 例4 7 在图4 18中 第3行所有单元格均输入的是3 2 第2行对应单元格是对它采用的格式 由此可见 同一数字的表现形式何其多也 4 3格式化数字 4 3 1Excel的默认数字格式Excel提供了许多数据格式 包括数据的精确度 显示方式等内容 货币 美元 欧元 人民币 百分比等 4 3 2使用系统提供的数字格式数字格式设置的常见内容有以下几种 数字的精确度以百分比方式显示数字数字的分节显示 开始 选项卡的 数字 组提供了数字格式化工具 4 3 2使用系统提供的数字格式 4 3 2使用系统提供的数字格式 3 数据格式设置案例 货币形式的数据 4 3 3自定义格式 1 自定义格式的意义自定义格式允许用户修改单元格的格式 对单元格中数据的显示形式 字体 字型以及单元格的边框 背景等内容进行设置 通过自定义格式不但可以将单元格数据显示为需要的样式 而且能够在输入数据的时候显示一些重要的提示信息 以减少输入数据的出错率 此外 通过自定义格式的设置 还能减少单元格内重复数据的输入 4 3 3自定义格式 2 自定义格式的形式格式组成正数格式码 负数格式码 0的格式码 文本的格式码说明 格式代码中最多可以指定四个节 各节以分号分隔 它们顺序定义了单元格显示正数 负数 零和文本时的格式 如果只指定了两个节 则第一部分用于表示正数和零 第二部分用于表示负数 如果只指定一个节 则所有数字都会使用该格式 如果要跳过某一节 则对该节仅使用分号即可 比如 若将单元格的格式设置为 00 零 则当在此单元格中输入负数时 输入的负数就不会被显示出来 被隐藏了 4 3 3自定义格式 在格式码中指定的字符被用来作为占位符或格式指示符 0作为一个占位符 它在没有任何数字被显示的位置上显示一个0 符号 跟在一个正数的格式后面 以保证这个正数右边会留下一个空格 空格的宽度与圆括号 的宽度一样宽 正数 负数和0靠单元格的右边线对齐 4 3 3自定义格式 3 自定义格式码的例子 4 3 3自定义格式 4 Excel常用格式码的含义 4 3 3自定义格式 5 自定义格式案例 4 3 3自定义格式 6 自定义格式的方法 1 选中要格式化的单元格 2 单击 开始 数字 组右边的下三角形 3 选择弹出对话框的 数字 标签 然后选择 分类 列表框中的 自定义 选项 4 在 类型 文本框中 输入自定义格式 5 在单元格中输入数据 数据将以自定义格式显示 4 3 4文本格式化函数Text 1 自定义格式存在的问题通过 设置单元格格式 对话框中的命令来将数字格式化为文本的方式存在一定的局限性 比如 只有整个单元格都为数字时才起作用 而且自定义格式所改变的仅仅是单元格的显示外观 其本质还是数字本身 仍然可以进行加 减等运算 再如 如果单元格中是以单引号 开头的强制为文本的数字 单元格格式化方法对它并不适用 文本格式化函数Text可以解决上述问题 4 3 4文本格式化函数Text 2 Text的功能Text是将数字格式化为真正文本的函数 在需要以可读性更高的格式显示数字或需要合并数字 文本或符号时 此函数很有用 3 Text的用法TEXT value format text value是要格式化为文本的内容 它可以是数值 计算结果为数值的公式 或是内容为数值的单元格引用 4 3 4文本格式化函数Text format text是用双引号括起来作为文本字符串的数字格式码 它可以是表4 1中除了 和 颜色代码 之外的其它格式码 例如 0 00 例如 假设单元格A1含有数字123 5 公式 TEXT A1 0 00 的结果为 123 50 TEXT A1 0 00 每小时 的结果为 123 50每小时4 Text的特殊应用用Text函数设置小数和分数对齐方式利用Text将格式化后的文本数字转换成数值型数字 4 3 4文本格式化函数Text 例4 8 用Text函数设置小数或分数的对齐方式 图4 21 用text将数字转换成文本 用text设置小数点对齐 用text转换成分数形式 用 text将文本数字转换成真正数字 4 3 4文本格式化函数Text 3 用Text将数字格式化为中文大小写数字 例4 9 某单位差旅发票如图4 22所示 当在此票据中填写D列和E列的发票张数和金额后 其余数据由Excel通过公式自动计算生成 在D11中输入大写金额生成的公式 TEXT E11 100 dbnum2 0拾0万0仟0佰0拾0元0角0分 Text中可用 dbnum2 格式码设计中文大字 用 dbnum1 设置中文小写格式 TEXT 7052 24 dbnum1 的结果为七千 五十二 二四 TEXT 705224 dbnum1 0拾0万0仟0佰0拾0元0角0分 的结果为 拾 万七仟 佰五拾二元二角四分 4 4格式化日期 1 概述在Excel中 日期和时间实际上是以序列数字的形式存储 以日期形式显示的 所以当一个单元格的格式被设置为日期格式后 即使在其中输入纯粹的数字 该数字也会被显示为一个日期 4 4格式化日期 2 日期的格式码 4 4格式化日期 3 日期格式化案例 4 4格式化日期 在Text函数中用使用日期格式码 4 4格式化日期 例4 10 在图4 26中 B列和G列是已输入的日期和时间数据 利用Text文本格式函数 日期和时间的格式码进行日期 时间和文本格式的日期时间之间的转换 C3 TEXT B3 0 00 00 D3 TEXT C3 yyyymmdd E3 TEXT C3 yyyy mm dd F3 TEXT E3 yyyy mm dd H3 TEXT G3 h m s I3 TEXT H3 hhmmss J3 TEXT G3 h m s K3 TEXT G3 h m s 在对应列向下复制上述单元格公式 4 5条件格式 关于条件格式对于不同的数据 可按不同的条件设置其显示格式 例如 把学生成绩表中所有不及格成绩显示为红色 可以非常轻松地看出其中不及格的成绩情况 对于企业的销售表 把其中利润较小的或无利润的数据设置为黄色 而将利润最大的数据设置为绿色 可以使人在查看这些数据时一目了然 4 5条件格式 1 条件格式的意义对于不同的数据 可按不同的条件设置其显示格式 比如在一个学生成绩表中 把所有不及格的成绩显示为红色 就能够很容易找到有不及格科目的学生 下面是条件格式运用的一个例子 当单元格的数值大于200时 用黑色显示 当小于100时用红色显示 100 200之间的数据用蓝色显示 此自定义条件的格式码如下 黑色 200 红色 100 蓝色 4 5条件格式 2 条件设置案例在下图所示的成绩表中 0 60之间 60 80之间 80 100之间的分数分别以不同的颜色表示和背景表示 便于查询不同分数段的信息 4 5 1Excel2010条件格式概述 条件格式指基于某种条件更改单元格区域中数据的表现形式 如果条件成立就基于该条件设置单元格区域的格式 如设置单元格的背景 用图形符号表示数据 如果条件不成立就不设置单元格区域的格式 Excel2010的条件格式Excel2010对条件格式的任务分得更细 涉及的内容更多 也更具实用性 更容易达到以下效果 突出显示所关注的单元格或单元格区域 强调异常值 使用数据条 颜色刻度和图标集来直观地显示数据 4 5 1Excel2010条件格式概述 例4 11 某中药材公司2009年的药材销售利润表如图4 27所示 Page84在图4 27所示工作表中突出显示以下信息 利润小于20的药材销售情况 销售利润最高或最低的10种药材是哪些 用条型图直观显示出各药材在各省的销售情况 用不同的图形显示出各药材在各省的销售情况 标识出利润占前30 的药材销售情况 在Excel2010中 通过条件格式可以轻松地实现上述要求 条件格式位于 开始 选项卡的 样式 组中 单击该组中的 条件格式 按钮 将显示条件格式的命令列表 如图4 28所示 4 5 1Excel2010条件格式概述 Excel2010条件格式的内容 Excel2010条件格式案例 突出显示利润小于20的药材 用数据条显示单元格数据 突出显示利润10 的药材 用图标集和色阶显示不同比例部分的数据 4 5 1Excel2010条件格式概述 Excel2010条件格式中的几个概念 1 突出显示单元格规则对单元格区域设置一定的条件 将按指定的现则突出显示该区域中满足条件的单元格内容 默认的规则用某种色彩填充单元格背景 例如 如果单元格的值大于 小于或等于某个指定值 就将单元格的背景填充为绿色 突出显示该单元格中的值 2 项目选取规则对选中单元格区域中小于或大于某个给定阈值的单元格实施条件格式 单击此规则中的 值最大的10项 值最大的10 项 高于平均值 等条件 Excel都会显示出一个条件设置对话框 可通过它设置单元格中的条件格式 4 5 1Excel2010条件格式概述 Excel2010条件格式中的几个概念 3 数据条数据条以彩色条型图直观地显示单元格数据 数据条的长度代表单元格中的数值 数据条越长 表示值越高 数据条越短 表示值越低 4 色阶色阶用颜色的深浅表示数据的分布和变化 包括双色阶和三色阶 双色刻度使用两种颜色的深浅程度比较某个区域的单元格 颜色的深浅表示值的高低 5 图标集使用图标集可以对数据进行注释 并可以按阈值将数据分为三到五个类别 每个图标代表一个值的范围 其形状或颜色表示的是当前单元格中的值相对于使用了条件格式的单元格区域中的值的比例 4 5 2条件格式规则 条件格式规则Excel2010中的条件格式是根据一组规则来实施完成的 这组规则称为条件格式规则 条件格式规则类型基于各自值设置所有单元格的格式只为包含以下内容的单元格设置格式 仅对排名靠前或靠后的数值设置格式仅对高于或低于平均值的数值设置格式仅对唯一值或重复值设置格式使用公式确定要设置格式的单元格 单击 开始 选项卡中的 条件格式 按钮 从弹出的菜单中选择 新建规则 4 5 3自定义条件格式 自定义条件格式默认的条件格式能够解决大多数问题 但有些时候并不如愿 例如默认的图标集用三种图标表示不同比例的数据 前33 中33 和后33 各用一种图表表示 如果希望的比例为前15 中45 后40 各用一种图标表示 就需要更改条件格式的规则 这可以通过自定义条件格式完成 4 5 3自定义条件格式 例4 12 某校采用学评教制度 学生对其任课教师进行评分 某次学评教成绩如图4 34左图所示 对该工作表进行格式化 标识出学评教分数最差的20 最好的15 以及中间的学评教成绩 以便于实施奖励与惩罚 最好的15 最后的20 中间的65 4 5 3自定义条件格式 自定义条件格式的建立过程 1 选择要建立条件格式的区域 单击 开始 样式 组 条件格式 新建规则 命令 弹出图示 新建格式规则 对话框 2 在 格式样式 列表中选择条件格式类型 3 在 类型 T 的列表中选择 百分点值 然后在 值 下面的文框中输入值的范围 如图所示 4 5 3自定义条件格式 重复应用条件格式在Excel2010中 对同一单元格区域 可以多次应用不同的条件格式 例如 对一个已经应用了色阶的单元格区域 可以再次应用图标集 数据条等条件格式 自定义条件格式也可以叠加 请看下面的例子 例4 13 某班期末考试如图4 36左图所示 对它进行格式化 用红色粗体显示0 60之间的成绩 对90 100之间的单元格设置背景填充图案 多次应用了条件格式的工作表 条件格式规则1 0 60分为红色 加粗 倾斜 条件格式规则2 90分以下填充图案背景 1 选中C3 E12区域 单击 开始 条件格式 突出显示单元格规则 小于 命令 在 小于 中输入60 然后在 设置为 的下拉列表中选择 自定义格式 Excel将弹出 设置单元格格式 对话框 2 在 小于 中输入60 然后在 设置为 的下拉列表中选择 自定义格式 Excel将弹出 设置单元格格式 对话框 3 单击 字体 选项 选中 字形 中的 加粗倾斜 并从 颜色 列表中选择经色 4 再次选中C3 E12区域 设置大于90的条件条件格式 通过 设置单元格格式 填充 对话框中的 图案样式 列表中 为成绩大于90的单元格选择一背景填充图案 4 5 4条件格式规则的管理 1 条件格式的限制在Excel2010中 条件格式最多可以