excel数据分析高级应用-数据输入和数据格式化

《excel数据分析高级应用-数据输入和数据格式化》由会员分享，可在线阅读，更多相关《excel数据分析高级应用-数据输入和数据格式化（74页珍藏版）》请在金锄头文库上搜索。

1、重庆邮电大学经济管理学院 周玉敏 1.认识Excel 2.数据输入和表格设计 3.函数的使用 4.图表的制作 5.数据分析 数据输入 工作表的格式化 基本数据的输入 相同数据的输入 编号的输入 自定义格式输入 采用下拉列表进行数据选择输入 利用公式与函数进行查找输入 利用RAND和INT函数产生大批量的仿真数据 数据输入并非易事，请看下面的例子 【例2.1】某电信公司有用户档案如下图所示。如果不 讲技巧，一字不误的输入，费时费力。 本节将介绍一些数本节将介绍一些数 据输入技术，应用据输入技术，应用 这些技术可以减少这些技术可以减少 数据输入过程的不数据输入过程的不 少麻烦少麻烦 1、数据输入的

2、一般过程 （1）选定要输入数据的单元格。 （2）从键盘上输入数据。 （3）按Enter键后 2、输入数值 （1）正数的输入：+234，13,333,333，12E3 （2）负数的输入： -234，(234)，-12e2 （3）分数的输入: 2 2/3, 0 3/4 （4）货币数据的输入:￥123，$21 3、输入文本 字符文本应逐字输入 数字文本以开头输入，或用=“数字”方式输入。 输入文本32，可输入： =“32” 或输入：32 文本形式的公式，要通过“插入”选项卡“文本”组中的“对象” 命令 如 222 21),(x exf4、输入日期 以yy-mm-dd形式，或mm-dd形式输入 通过格

3、式化得到其它形式的日期，这样可减少输入 在“开始”选项卡“数字”组卡，单击其中的箭头，E弹出图 示的“单元格格式”设置对话框 5、输入公式 公式是对工作表中的数值进行计算的等式。公式要以等号（） 开始。 例如在A3输入：=5+2*3，结果等于 2 乘 3 再加 5。A3中将 显示11 1、填充复制相同数据 【例2.2】设要建立下图所示“学生档案”表。其中入学时 间、班级、系都是相同数据。 （1）在）在G2中输入中输入 “计算机通信计算机通信”，然，然 后后Enter。 （2）单击）单击G2，G2右右 下角会出现黑色小方下角会出现黑色小方 块，它就是块，它就是填充柄。填充柄。 （3）向下拖动填充

4、柄，向下拖动填充柄， 拖过的单元格都被填拖过的单元格都被填 入了入了“计算机通信计算机通信” 。 双击此填充 柄可自动复 制生成G列 数据 2、用Ctrl+Enter输入相同数据 【例2.2】设要建立下图所示“学生档案”表。其中入学时 间、班级、系都是相同数据。 （1）选中）选中F2：F11 （2）输入：）输入：JK001 （3）按按Ctrl+Enter 可用同样方法输可用同样方法输 入入C、G列数据列数据 1、复制输入连续的编号 连续编号（或等差、等比性质的数据）应该采用复制或序列填 充的方式进行输入。 比如公职或单位的职工编号、电话号码、手机号码、零件编号 等。 输入右图A列的学号 （1）

5、在）在A2输入输入 1003020101 在在A3输入输入1003020102 （2）选中）选中A2：A3 （3）向下拖动）向下拖动A3单元格的填充柄。单元格的填充柄。 2、填充产生连续编号 填充序列方式适用于输入较多的连续编号数据。 比如要输入电话号码：663321240663328000， 如右图所示，方法如下。 在第一个单元格中输入起始号码 在 “开始”选项的“编辑” 组，单击该组中填充控件右侧的下三角形。 弹出“序 列”对话框，在Excel弹出的 “序列”对话框中指定“列” 在步长中输入1 在终止值中输入最后一个号码。 3、利用自定义格式 产生特殊编号 人们常会遇到各种 具有一定规则的

6、特 殊格式的编号。如 右图的保险号。 1、选中要输入保险号的区域 2、在“开始”选项卡中，单击数 字组右边的箭头， Excel会弹出 单元格格式设置对话框， 。 3、选中“自定义” 4、输入自定格式： p00-000-0000 4.用自定义格式输入大数字编号 当在单元格中输入一个位数较多的数值时（比如超过15位 的整数），Excel会自动将输入的数据显示为科学记数法， 这可能并不符合我们的需要。 产生这种问题的原因是数值精度问题，Excel的默认精度 是15位，如果一个数值的长度超过15位，则15位之后的 数字都被视为0。 这种位数较多的大数字编号，可以用自定义格式进行输入, 或者用文本形式输

7、入 4.用自定义格式输入大数字 编号 建立右图所示的商场用户档 案，其中客户编号为21位， 且前18位全部相同。 若直接输入，不仅数据多， 且要出错，比如在A3输入： 356800993102155129001 ,Excel会显示为3.57E20 解决方法是定义G列保存身份证号 的单元格的自定义格式为： “356800993102155129“00 0 设置自定义格式 后，只需要输入 身份证的最后4 位编号 选中A3单元格，单击 “开始”选项卡“数字”组右下功的下 箭头，弹出设置单元格格式对话框 “类型”文本框中输入： “356800993102155129“000 1、用#,#;红色红色“

8、“工资不能为负数工资不能为负数“; “; 自定义格式 意义 自定义格式允许用户修改单元格的格式，对单元格中数据的 显示形式、字体、字型以及单元格的边框、背景等内容进 行设置。通过自定义格式不但可以将单元格数据显示为需 要的样式，而且能够在输入数据的时候显示一些重要的提 示信息，以减少输入数据的出错率。此外，通过自定义格 式的设置，还能减少单元格内重复数据的输入 。 自定义格式的形式 格式组成 : 正数格式码；负数格式码；0的格式码；文本的格式码 说明 格式代码中最多可以指定四个节。各节以分号分隔， 它们顺序定义了单元格显示正数、负数、零和文本时 的格式。 如果只指定了两个节，则第一部分用于表示

9、正数和零， 第二部分用于表示负数。如果只指定一个节，则所有 数字都会使用该格式。 如果要跳过某一节，则对该节仅使用分号即可。比如， 若将单元格的格式设置为：#,#.00;“零”; 则当在 此单元格中输入负数时，输入的负数就不会被显示出 来（被隐藏了）。 自定义格式码的例子 关于条件格式 对于不同的数据，可按不同的条件设置其显示格式。例如， 把学生成绩表中所有不及格成绩显示为红色，可以非常轻 松地看出其中不及格的成绩情况；对于企业的销售表，把 其中利润较小的或无利润的数据设置为黄色，而将利润最 大的数据设置为绿色，可以使人在查看这些数据时一目了 然。 【例2.8】在下图所示的成绩表中，060之间

10、， 6080之间，80100之间的分数分别以不同的 颜色表示和背景表示，便于查询不同分数段的信 息。 条件格式 指基于某种条件更改单元格区域中数据的表现形式，如 果条件成立就基于该条件设置单元格区域的格式（如设 置单元格的背景、用图形符号表示数据），如果条件不 成立就不设置单元格区域的格式。 Excel 2007的条件格式 Excel2007对条件格式进行了较大的改进，对条件格式 的任务分得更细，涉及的内容更多，也更具实用性。 Excel 2007的条件格式更容易达到以下效果：突出显示 所关注的单元格或单元格区域；强调异常值；使用数据 条、颜色刻度和图标集来直观地显示数据。 Excel 200

11、7条件格式中的几个概念 （1）突出显示单元格规则 对单元格区域设置一定的条件，将按指定的现则突出显示 该区域中满足条件的单元格内容，默认的规则用某种色 彩填充单元格背景。例如，如果单元格的值大于、小于 或等于某个指定值，就将单元格的背景填充为绿色，突 出显示该单元格中的值。 （2）项目选取规则 对选中单元格区域中小于或大于某个给定阈值的单元格实 施条件格式。单击此规则中的“值最大的10项”、“值 最大的10%项”、“高于平均值”等条件，Excel都会 显示出一个条件设置对话框，可通过它设置单元格中的 条件格式。 Excel 2007条件格式中的几个概念 （3）数据条 数据条以彩色条型图直观地显

12、示单元格数据。数据条的 长度代表单元格中的数值。数据条越长，表示值越高，数据条越短，表示值越低。 （4）色阶 色阶用颜色的深浅表示数据的分布和变化，包括双色阶 和三色阶。双色刻度使用两种颜色的深浅程度比较某个区域的单元格。颜色的深浅表示值的高低。 （5）图标集 使用图标集可以对数据进行注释，并可以按阈值将数据 分为三到五个类别。每个图标代表一个值的范围，其形 状或颜色表示的是当前单元格中的值相对于使用了条件格式的单元格区域中的值的比例。 Excel 2007条件格式的内容 【例2.9】某中药材公司2009年的药材销售利润表 如下图所示。 突出显示突出显示 利润小于利润小于 20的药材的药材 用

13、数据条用数据条 显示单元显示单元 格数据格数据 突出显示突出显示 利润占前利润占前 10%的药的药 材材 用图标集和色阶显示不用图标集和色阶显示不 同比例部分的数据同比例部分的数据 【例2.10】某校采用学评教制度，学生对其任课教师进行 评分。某次学评教成绩如下图所示。对该工作表进行格式 化，标识出学评教分数最差的20%，最好的15%，以及中 间的学评教成绩，以便于实施奖励与惩罚。 最好的最好的15% 最后的最后的20% ！中间的！中间的65% 自定义条件格式的建立过程 （1）选择要建立条件格式的区域，单击）选择要建立条件格式的区域，单击“开“开 始”始”“样式”组“样式”组“条件格式”“条件

14、格式”“新建“新建 规则”规则”命令。弹出图示“新建格式规则”对命令。弹出图示“新建格式规则”对 话框。话框。 （2）在）在 “格式样式”列表中选择条件格式类“格式样式”列表中选择条件格式类 型型 （3）在“类型（）在“类型（T）”的列表中选择“百分）”的列表中选择“百分 点值”，然后在“值”下面的文框中输入值点值”，然后在“值”下面的文框中输入值 的范围，如图所示。的范围，如图所示。 【例2.11】按要求设计如下图所示的表格。 格式要求： （1）“销售部门”的输入采用下拉列表的方式输入； （2）“员工姓名”的输入采用下拉列表的方式输入，下拉列表显示的 员工姓名只能显示“销售部门”中选择的该部

15、门的员工姓名； （3）根据“销售数量”的多少用数据条填充； (4) “销售金额”用货币数据格式； （5）“销售金额”中用图标标注，图标的含义如下： 销售金额=100000 用“”图标 70000=100000 用“”图标 70000=销售金额100000 用“！”图标 销售金额70000 用“”图标 设置步骤： 选择“开始”选项卡，“样式”组，单击“条件格 式”，弹出如图所示的条件格式设置的菜单。 条件格式设置条件格式设置 设置步骤： 编辑格式规则。编辑格式规则。 如右如右图所示进行修改图所示进行修改 1.认识Excel 2.如何设计表格 条件格式（比赛自动评分表） 数据有效性 （设计友好的表格） 3.函数的使用 If、sumif、count、counta、countif（多条件统计）、 rank、rand、match、vlookup、offset 数组公式 4.图表的生成 甘特图、气泡图、分解饼图、多系列图、双轴图 图的美化 5.数据分析 排序 自动筛选、高级筛选 分类汇总 数据透视表（普通数据透视表、多重合并计算数据透视表）