Excel数据分析高级应用-数据输入和数据格式化.pdf

重庆邮电大学经济管理学院 周玉敏 1.认识Excel 2.数据输入和表格设计 3.函数的使用 4.图表的制作 5.数据分析 数据输入 工作表的格式化 基本数据的输入 相同数据的输入 编号的输入 自定义格式输入 采用下拉列表进行数据选择输入 利用公式与函数进行查找输入 利用RAND和INT函数产生大批量的仿真数据 数据输入并非易事，请看下面的例子【例2.1】某电信公司有用户档案如下图所示。如果不讲技巧，一字不误的输入，费时费力。本节将介绍一些数本节将介绍一些数据输入技术，应用据输入技术，应用这些技术可以减少这些技术可以减少数据输入过程的不数据输入过程的不少麻烦少麻烦 1、数据输入的一般过程（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 文本形式的公式，要通过“插入”选项卡“文本”组中的“对象”命令 如 22221),(xexf4、输入日期 以yy-mm-dd形式，或mm-dd形式输入 通过格式化得到其它形式的日期，这样可减少输入 在“开始”选项卡“数字”组卡，单击其中的箭头，E弹出图示的“单元格格式”设置对话框 5、输入公式 公式是对工作表中的数值进行计算的等式。公式要以等号（）开始。例如在A3输入：=5+2*3，结果等于 2 乘 3 再加 5。A3中将显示11 1、填充复制相同数据【例2.2】设要建立下图所示“学生档案”表。其中入学时间、班级、系都是相同数据。（1）在）在G2中输入中输入“计算机通信计算机通信”，然，然后后Enter。（2）单击）单击G2，G2右右下角会出现黑色小方下角会出现黑色小方块，它就是块，它就是填充柄。填充柄。（3）向下拖动填充柄，向下拖动填充柄，拖过的单元格都被填拖过的单元格都被填入了入了“计算机通信计算机通信”。双击此填充柄可自动复制生成G列数据 2、用Ctrl+Enter输入相同数据【例2.2】设要建立下图所示“学生档案”表。其中入学时间、班级、系都是相同数据。（1）选中）选中F2：F11 （2）输入：）输入：JK001 （3）按按Ctrl+Enter 可用同样方法输可用同样方法输入入C、G列数据列数据 1、复制输入连续的编号 连续编号（或等差、等比性质的数据）应该采用复制或序列填充的方式进行输入。比如公职或单位的职工编号、电话号码、手机号码、零件编号等。输入右图A列的学号 （1）在）在A2输入输入 1003020101 在在A3输入输入1003020102（2）选中）选中A2：A3（3）向下拖动）向下拖动A3单元格的填充柄。单元格的填充柄。2、填充产生连续编号 填充序列方式适用于输入较多的连续编号数据。比如要输入电话号码：663321240663328000，如右图所示，方法如下。在第一个单元格中输入起始号码 在“开始”选项的“编辑”组，单击该组中填充控件右侧的下三角形。弹出“序列”对话框，在Excel弹出的“序列”对话框中指定“列”在步长中输入1 在终止值中输入最后一个号码。3、利用自定义格式产生特殊编号 人们常会遇到各种具有一定规则的特殊格式的编号。如右图的保险号。1、选中要输入保险号的区域 2、在“开始”选项卡中，单击数字组右边的箭头，Excel会弹出单元格格式设置对话框，。3、选中“自定义”4、输入自定格式：p00-000-0000 4.用自定义格式输入大数字编号 当在单元格中输入一个位数较多的数值时（比如超过15位的整数），Excel会自动将输入的数据显示为科学记数法，这可能并不符合我们的需要。产生这种问题的原因是数值精度问题，Excel的默认精度是15位，如果一个数值的长度超过15位，则15位之后的数字都被视为0。这种位数较多的大数字编号，可以用自定义格式进行输入,或者用文本形式输入 4.用自定义格式输入大数字编号 建立右图所示的商场用户档案，其中客户编号为21位，且前18位全部相同。若直接输入，不仅数据多，且要出错，比如在A3输入：356800993102155129001,Excel会显示为3.57E20 解决方法是定义G列保存身份证号的单元格的自定义格式为：356800993102155129000 设置自定义格式后，只需要输入身份证的最后4位编号 选中A3单元格，单击“开始”选项卡“数字”组右下功的下箭头，弹出设置单元格格式对话框 “类型”文本框中输入：356800993102155129000 1、用&运算符组合数据 “&”为字符连接运算，其作用是把前后的两个字符串（也可以是数值）连接为一个字符串。如“ADKDKD”&“DKA”的结果为ADKDKDDKA。123&45&678的结果为“12345678”设下图E列数据由C、D列数据组合而成。在E3中输入公式=C3&D3，然后向下复制！2、用&和文本函数的结合&常和文本函数left、right、mid等合用，这几个函数都很简单，其调用语法如下：left(text,n)right(text,n)mid(text n1,n2)其中：left截取text文本左边的n个字符；比如left(1234,2)=12 right截取text文本右边的n个字符；比如right(1234,2)=34 mid从text的第n1字符开始，取n2个字符；比如MID(1234,2,2)=23 3、用&和文本函数的结合案例 某电话公司的缴费帐号由身份证号的后15位（全长18位），后接电话号码构成。如下图所示。用&和文本函数可以很快建立缴费帐号。在I3输入公式“=Right(G3,17)&H3”，然后向下填充复制该公式到I列的其余单元格 1、下拉列表的适用范围 项目个数少而规范的数据，比如职称、工种、单位及产品类型等，这类数据适宜采用Excel的“数据有效性”检验方式，以下拉列表的方式输入。例如：某校教师档案如右图所某校教师档案如右图所示，其中的职称列数据示，其中的职称列数据只能从只能从“讲师、教授、讲师、教授、副教授、助教副教授、助教”中选中选择。这类数据用下拉列择。这类数据用下拉列表输入，方便而准确。表输入，方便而准确。2、下拉列表建立方法（1）选中要建立下拉列表的单元格区域。（2）然后选择功能区中的“数据”选项卡，单击“数据工具”组中的“数据有效性”控件 （3）选择“设置”标签，然后从“允许”下拉列表中选择“序列”选项。（4）在“来源”文本框中输入职称名字“助教,讲师,副教授,教授”对输入到单元格中的数据进行一定程度的限制，比如限制小数位数、日期和时间的范围、字符的个数等。当输入的数据不符合限定规则时，Excel就显示一些警告信息，并且不接受输入的数据。如果工作表的数据是由其他人输入的，则有必要在别人输入数据时给出一些简要的提示信息，或对数据输入的规则作些说明 数据录入 工作表的格式化 该表存在以下问题该表存在以下问题:1、表名不醒目。、表名不醒目。2、表的标题不太醒目。、表的标题不太醒目。3、当学生较多，科目成绩较、当学生较多，科目成绩较多时，看数据较困难。多时，看数据较困难。4、整个成绩表不太美观。、整个成绩表不太美观。整张报表很少用表格线，却非常清晰、明了 作为一个局外人我们可以在最短的时间了解报表的内容和所要表达的意图 如果我们来做这张报表，一般会是什么样呢？按照我们的习惯，做出的表格一般是这样的 同专业人士制作的表格相比有什么区别呢？不能看清表格的内容，但依然能清楚地了解表格内容的层次 没有规律的空间分隔，像蜘蛛网一样的表格线，不知从何看起 根据项目类别将不同项目间距扩大，相同项目间距缩小；将最重要的放在左上角，对于报表的核心数据应用下划线标出，把不重要的放在下方；对于重点区域用不同的符号进行标注，但标注不宜过多；报表正文字型一般选择“宋体”较为严谨，“黑体”作为标题或需特别强调的区域，显得较为庄重；设置单元格格式：数字显示格式、对齐方式、填充颜色等，使报表更清晰明了。【例2.3】套用的Excel内置某种表格式对工作表进行格式化 套用表格式的过程套用表格式的过程（1）单击要格式化的工作表中的任一单元格或选中要格式化的单元格区域。）单击要格式化的工作表中的任一单元格或选中要格式化的单元格区域。（2）单击“开始”）单击“开始”“样式”组“样式”组“套用表格格式”按钮，显示出表格式对话“套用表格格式”按钮，显示出表格式对话框。框。表格式选择对话框 这些都是表格这些都是表格式，可以选择式，可以选择其中的任何一其中的任何一种应用于当前种应用于当前的工作表中的工作表中 表工具 当激活表中的任一单元格后（单击表中的任一单元格），Excel在就会在功能区中显示出“表工具|设计”选项卡（此选项卡平时处于隐藏不显状态）表工具的设计选表工具的设计选项卡中有许多格项卡中有许多格式化表的功能按式化表的功能按钮钮 镶边行、列即将表的奇镶边行、列即将表的奇数据行列设置为一种色数据行列设置为一种色彩，偶数行列设置为另彩，偶数行列设置为另一色彩一色彩 删除重复行可轻松去删除重复行可轻松去掉表中的重复数据掉表中的重复数据。转换为区域将表转换转换为区域将表转换为普通工作表为普通工作表 主题 主题是Office 2007新采用的一套统一的设计元素和配色方案，是为文档提供的一套完整的格式集合。其中包括主题颜色（配色方案的集合）、主题文字（标题文字和正文文字的格式集合）和相关主题效果（如线条或填充效果的格式集合）。利用文档主题，可以非常容易地创建具有专业水准、设计精美、时尚的文档。【例2.4】有学生成绩表下图所示，应用主题对该工作表进行格式化。主题位于主题位于“页面布局”“页面布局”选项卡中选项卡中 这些都是主题，将这些都是主题，将鼠标指向它，就会鼠标指向它，就会实时看见将它应用实时看见将它应用于当前表格的效果。于当前表格的效果。1、关于单元格样式 Excel 2007设计好了许多单元格样式，并将它们内置在Excel中，在进行工作表的格式化工作时，可以直接应用这些样式来格式化单元格或单元格区域。2、应用格式化单元格的步骤：（1）选中要应用单元格样式的单元格或单元格区域；（2）单击“开始”“样式”组“单元格样式”按钮，弹出“单元格样式”选项板，如Page83图4.6所示。（3）将鼠标指向“单元格样式”选项板中的各命令按钮，立即就会显示出选中单元格应用对应样式后的效果。”单元格”单元格样式“位样式“位于”开始于”开始“选项卡“选项卡中中 单元格样式就是应用色单元格样式就是应用色彩、字体、字型、背景彩、字体、字型、背景和前景格式化单元格或和前景格式化单元格或区域区域 1、工作表的边框和底纹 Excel的默认边框是虚线、无底纹，所有的表格都表示为同一种样式。如果直接在Excel下进行表格的打印则没有网格线（当然，可通过打印设置让它显示表格线），不便于特殊信息的突出显示。通过边框和底纹的设置，一方面可使表格美观，同时也可改变信息的显示方式，让人们尽快掌握表中的信息。设置边框和底纹后的工作表【例2.5】对于例2.3中所示的学生成绩表，设置边框和底纹之后，可得到如下图所示的表格。3、取消工作表的网格线 单击单击“视图”“视图”选项卡选项卡 取消取消“网格“网格线”线”复选框中复选框中的勾选的勾选 通过对单元格的格式设置（自定义格式），可以创建上凸和下凹的立体效果，数据显示在这样的表格中，看起来更轻松。【例2.6】三维表格示例 三维表格的设置方法。（1）取消工作表的网络线。（2）把要设置三维效果的表格区域的底纹设置为浅灰色（一般情况下是把整个工作表的底纹都设置为浅灰色。当然，其他色彩的浅色也可以，但浅灰色的立体视觉效果更佳）。Excel的常见数据类型 数字型、日期型、文本型、逻辑型数据 数字型表现形式多样：货币、小数、百分数、科学计数法、各种编号、邮政编码、电话号码等多种形式 【例2.7】建立如下所示的职工档案表，并按要求设置各单元格或数据列的格式。格式设置要求：（1）大标题18号字，“制表时间”与大标题在同一行上（用Alt+Enter键换行）；（2）第2、3行为标题行，按图所示进行相关单元格的合并，字号为11号；（3）设置“职工编号”的自定义格式，在实际输入时，只需要输入数字，由Excel自动进行编号的转换，如输入3，Excel将把它显示为“ZG003”；（4）设置“家庭电话”与“办公电话”的电话号码格式，前面的区号及括号是自定义格式加上去的，请参考图中的C4单元格；（3 3）自定义自定义格式格式：ZG000ZG000 （4 4）自定义自定义格式格式：“(023)“#023)“#格式设置要求：（5）设置“参加工作时间”的自定义格式，如输入“1998/2/1”，Excel将它显示为“一九九八年二月一日”。（6）设置“基本工资”的数据格式，当输入正数时，正常显示，当输入负数时，显示“工资不会是负数”的错误信息，当输入数据为0时，不显示。（7）同步骤6设置“工资”和“扣款”中的其他数据列。（8）计算“实发工资”，并把该列数据设置为人民币数据显示形式。（6 6）自定义自定义格式格式：#,#;#,#;红色红色 工资不能为负数工资不能为负数;自定义格式 意义 自定义格式允许用户修改单元格的格式，对单元格中数据的显示形式、字体、字型以及单元格的边框、背景等内容进行设置。通过自定义格式不但可以将单元格数据显示为需要的样式，而且能够在输入数据的时候显示一些重要的提示信息，以减少输入数据的出错率。此外，通过自定义格式的设置，还能减少单元格内重复数据的输入。自定义格式的形式 格式组成:正数格式码；负数格式码；0的格式码；文本的格式码 说明 格式代码中最多可以指定四个节。各节以分号分隔，它们顺序定义了单元格显示正数、负数、零和文本时的格式。如果只指定了两个节，则第一部分用于表示正数和零，第二部分用于表示负数。如果只指定一个节，则所有数字都会使用该格式。如果要跳过某一节，则对该节仅使用分号即可。比如，若将单元格的格式设置为：#,#.00;“零”;则当在此单元格中输入负数时，输入的负数就不会被显示出来（被隐藏了）。自定义格式码的例子 关于条件格式 对于不同的数据，可按不同的条件设置其显示格式。例如，把学生成绩表中所有不及格成绩显示为红色，可以非常轻松地看出其中不及格的成绩情况；对于企业的销售表，把其中利润较小的或无利润的数据设置为黄色，而将利润最大的数据设置为绿色，可以使人在查看这些数据时一目了然。【例2.8】在下图所示的成绩表中，060之间，6080之间，80100之间的分数分别以不同的颜色表示和背景表示，便于查询不同分数段的信息。条件格式 指基于某种条件更改单元格区域中数据的表现形式，如果条件成立就基于该条件设置单元格区域的格式（如设置单元格的背景、用图形符号表示数据），如果条件不成立就不设置单元格区域的格式。Excel 2007的条件格式 Excel2007对条件格式进行了较大的改进，对条件格式的任务分得更细，涉及的内容更多，也更具实用性。Excel 2007的条件格式更容易达到以下效果：突出显示所关注的单元格或单元格区域；强调异常值；使用数据条、颜色刻度和图标集来直观地显示数据。Excel 2007条件格式中的几个概念（1）突出显示单元格规则 对单元格区域设置一定的条件，将按指定的现则突出显示该区域中满足条件的单元格内容，默认的规则用某种色彩填充单元格背景。例如，如果单元格的值大于、小于或等于某个指定值，就将单元格的背景填充为绿色，突出显示该单元格中的值。（2）项目选取规则 对选中单元格区域中小于或大于某个给定阈值的单元格实施条件格式。单击此规则中的“值最大的10项”、“值最大的10%项”、“高于平均值”等条件，Excel都会显示出一个条件设置对话框，可通过它设置单元格中的条件格式。Excel 2007条件格式中的几个概念（3）数据条 数据条以彩色条型图直观地显示单元格数据。数据条的长度代表单元格中的数值。数据条越长，表示值越高，数据条越短，表示值越低。（4）色阶 色阶用颜色的深浅表示数据的分布和变化，包括双色阶和三色阶。双色刻度使用两种颜色的深浅程度比较某个区域的单元格。颜色的深浅表示值的高低。（5）图标集 使用图标集可以对数据进行注释，并可以按阈值将数据分为三到五个类别。每个图标代表一个值的范围，其形状或颜色表示的是当前单元格中的值相对于使用了条件格式的单元格区域中的值的比例。Excel 2007条件格式的内容【例2.9】某中药材公司2009年的药材销售利润表如下图所示。突出显示突出显示利润小于利润小于20的药材的药材 用数据条用数据条显示单元显示单元格数据格数据 突出显示突出显示利润占前利润占前10%的药的药材材 用图标集和色阶显示不用图标集和色阶显示不同比例部分的数据同比例部分的数据【例2.10】某校采用学评教制度，学生对其任课教师进行评分。某次学评教成绩如下图所示。对该工作表进行格式化，标识出学评教分数最差的20%，最好的15%，以及中间的学评教成绩，以便于实施奖励与惩罚。最好的最好的15%最后的最后的20%！中间的！中间的65%自定义条件格式的建立过程（1）选择要建立条件格式的区域，单击）选择要建立条件格式的区域，单击“开“开始”始”“样式”组“样式”组“条件格式”“条件格式”“新建“新建规则”规则”命令。弹出图示“新建格式规则”对命令。弹出图示“新建格式规则”对话框。话框。（2）在）在“格式样式”列表中选择条件格式类“格式样式”列表中选择条件格式类型型（3）在“类型（）在“类型（T）”的列表中选择“百分）”的列表中选择“百分点值”，然后在“值”下面的文框中输入值点值”，然后在“值”下面的文框中输入值的范围，如图所示。的范围，如图所示。【例2.11】按要求设计如下图所示的表格。格式要求：（1）“销售部门”的输入采用下拉列表的方式输入；（2）“员工姓名”的输入采用下拉列表的方式输入，下拉列表显示的员工姓名只能显示“销售部门”中选择的该部门的员工姓名；（3）根据“销售数量”的多少用数据条填充；(4)“销售金额”用货币数据格式；（5）“销售金额”中用图标标注，图标的含义如下：销售金额=100000 用“”图标 70000=销售金额100000 用“！”图标 销售金额=100000 用“”图标 70000=销售金额100000 用“！”图标 销售金额70000 用“”图标 设置步骤：选择“开始”选项卡，“样式”组，单击“条件格式”，弹出如图所示的条件格式设置的菜单。条件格式设置条件格式设置 设置步骤：编辑格式规则。编辑格式规则。如右如右图所示进行修改图所示进行修改 1.认识Excel 2.如何设计表格 条件格式（比赛自动评分表）数据有效性（设计友好的表格）3.函数的使用 If、sumif、count、counta、countif（多条件统计）、rank、rand、match、vlookup、offset 数组公式 4.图表的生成 甘特图、气泡图、分解饼图、多系列图、双轴图 图的美化 5.数据分析 排序 自动筛选、高级筛选 分类汇总 数据透视表（普通数据透视表、多重合并计算数据透视表）