加入VIP
  • 专属下载特权
  • 现金文档折扣购买
  • VIP免费专区
  • 千万文档免费下载

上传资料

关闭

关闭

关闭

封号提示

内容

首页 Excel数据分析高级应用

Excel数据分析高级应用.pdf

Excel数据分析高级应用

sha0909
2013-08-16 0人阅读 举报 0 0 暂无简介

简介:本文档为《Excel数据分析高级应用pdf》,可适用于工程科技领域

重庆邮电大学经济管理学院周玉敏要求掌握基本的Excel操作例如:单元格格式的设置、简单的公式使用等。对基本概念熟悉如:绝对引用、相对应用。以应用案例的方式讲解Excel的相关知识不再讲解基本操作部分。理论学时学时、实验学时学时缺席次取消考试资格。推荐教材:电子工业出版社《Excel与管理数据分析(第版)》杜茂康主编推荐学习网站:http:clubexcelhomenetforumphp认识Excel如何设计表格(h)函数的使用(h)图表的制作(h)数据分析(h)Excel的发展历史你认为利用Excel能做哪些工作?◦画表格◦计算:求和、计算平均值结论:Excel易学、功能简单咱们做一点测试题吧你能得多少分呢?认识Excel如何设计表格函数的使用图表的制作数据分析专业化表格设计原则利用“条件格式”和“数据有效性”设计友好表格•整张报表很少用表格线却非常清晰、明了•作为一个局外人我们可以在最短的时间了解报表的内容和所要表达的意图如果我们来做这张报表一般会是什么样呢?按照我们的习惯做出的表格一般是这样的同专业人士制作的表格相比有什么区别呢?不能看清表格的内容但依然能清楚地了解表格内容的层次没有规律的空间分隔像蜘蛛网一样的表格线不知从何看起根据项目类别将不同项目间距扩大相同项目间距缩小将最重要的放在左上角对于报表的核心数据应用下划线标出把不重要的放在下方对于重点区域用不同的符号进行标注但标注不宜过多报表正文字型一般选择“宋体”较为严谨“黑体”作为标题或需特别强调的区域显得较为庄重设置单元格格式:数字显示格式、对齐方式、填充颜色等使报表更清晰明了。完成本表涉及到的知识点:•单元格命名•数据有效性•条件格式•函数indirect的用法、名字概述可以给一个单元格或单元格区域取一个名字这个名字可以出现在公式中用来指代引用的单元格或单元格区域这会使公式的意义更加明确。名字其实是一个标识符由字母或下划线开头的一个或多个字符组成。在Excel中还可以用汉字字符作为名字。名字不区分字母的大小写比如ABC、abc、Abc都是同一个名字。可以为同一单元格或单元格区域定义多个不同的名字。名字一旦定义就可以在同一工作薄的不同工作表之间共用、名字的意义在Excel中同一个工作簿中的名字是共享的在一个工作表中定义的名字可以被另一个工作表的公式引用这使公式具有更强大的功能它能够利用单元格的名字在不同的工作表中查找到正确的单元格或单元格区域。名字给单元格的引用带来了许多方便因为它使人们不必记住单元格在工作表中的引用位置用名字就能找到它。、名字的定义在Excel中可以为一个独立的单元格、连续的单元格区域或许多不连续的单元格构成的单元格组合定义一个名字(或多个名字)方法二:公式|定义名称方法一:选中单元格名称框直接输入、名字的应用)通过名称在不同的工作表之间传递数据通过名称查找蔬菜单价指定A:B最左边为名称、名字的应用)名称结合Indirect函数的应用指定A:B区域的“最左列”为名称在D中输入公式:=INDIRECT(B)向下复制该公式!什么是数据有效性数据有效性一个包含帮助你在工作表中输入资料提示信息的工具它有如下功能:给用户提供一个选择列表限定输入内容的类型或大小自定义设置条件格式选项编辑条件格式规则条件格式:对不同的数据按不同的条件设置其显示格式Indirect(x)其中的X可以是单元格引用或名称。若X为单元格引用它必须用“”引起来如:=INDIRECT(“A”)将返回A单元格中的内容若X为单元格名称它将返回该名称所对应的单元格的内容。销售金额>=合格=<销售金额<警戒数销售金额<不合格认识Excel如何设计表格函数的使用图表的制作数据分析常用函数使用范例◦条件函数:If()◦统计函数:countif()、countifs()sumif()sumifs()◦查找函数:Vlookup()、Index()、Match()◦随机函数:rand()◦字符函数:len()、right()、left()、mid()数组公式的应用=IF(E=,D,"")IF(Logical,Valueiftrue,Valueiffalse)Logical代表逻辑判断表达式Valueiftrue表示当判断条件为逻辑“真(TRUE)”时的显示内容如果忽略返回“TRUE”Valueiffalse表示当判断条件为逻辑“假(FALSE)”时的显示内容如果忽略返回“FALSE”。①库存数<=补货③=<库存数<=下单②<库存数<准备④库存数>充足COUNTIF(range,criteria)用来搜索指定条件的单元格个数RANGE=指定作为搜索对象的单元格区域即找是否有与条件相配的单元格区域CRITERIA=指定搜索RANGE单元格的"条件",即说明条件Excel还提供了一个多条件统计函数COUNTIFS它可以一次对多个不同区域进行不同条件的计数其用法如下:COUNTIFS(rangecriteriarangecriteria……rangecriteria)【例题】有学生成绩表如下图所示。统计其中总分分以上的人数统计总分分以上物理分以下地理分以下化学分以上的人数统计姓王的同学人数。SUMIF(Range‚Criteria‚SumRange)Sumrange为需要求和的单元格、区域或引用Range为用于条件判断的单元格区域Criteria是由数字、逻辑表达式等组成的判定条件条件求和函数SUMIFS用法SUMIFS(sumrange,range,criteria,range,criteria…)其中range,range,…是计算关联条件的至个区域Criteria,criteria,…是数字、表达式、单元格引用或文本形式的至个条件用于定义要对哪些单元格求和。这些区域与条件是对应的即Criteria是用于range区域的条件criteria是用于range的条件以此类推。Sumrange是求和区域。◦功能SUMIFS函数对某一区域内满足多重条件的单元格求和。【例题】某家电商场的销售记录如下图所示的A:G所示。现要统计出统计每位职工销售各种产品的总数量并将它统计在I:L区域中。()在J单元格中输入统计李本成销售的彩电总数量的计算公式:=SUMIFS($E$:$E$,$D$:$D$,$I,$A$:$A$,J$)()将此公式向下角复制到L求和范围条件条件功能:Vlookup按列查找的方式从指定数据表区域的最左列查找特定数据它能够返回查找区域中与找到单元格位于相同行不同列的单元格内容。语法:VLOOKUP(x‚table‚n‚f)x是要查找的值table是一个单元格区域n是table区域中要返回的数据所在列的序号。n=时返回table第列中的数值n=时返回table第列中的数值以此类推。f是一个逻辑值表示查找的方式。当其为true(或)时表示模糊查找当它为false(或)时表示精确查找。()在I单元格中输入如下的查询公式:=LOOKUP(H,$A$:$B$,,TRUE)()将此公式向下角复制到IMatch格式◦Match(xrf)◦其中x是要查找的数值r可以是一个数组常量或某列(或行)连续的单元格区域其中可能包含有要查找的x。f用于指定match的查找方式它可以是或。功能◦Match(xrf)表示的意思是:在数组或连续的单元格区域r中查找x并返回x在r中的位置编号。◦当f为是match进行精确查找当f为(或)时match进行模糊查找。Match查找的方式取值函数功能r必须按降序排列查找大于或等于x的最小数值r不必排序查找等于x的第一个数值r必须按升序排列查找小于或等于x的最大数值Index(Arearcn)其中Area是个或多个单元格区域r是某行的行序号c是某列的列序号该函数返回指定的行与列交叉处的单元格引用。如果r等于则返回整行单元格引用如果c等于则返回整列单元格引用。当Area包括多个单元格区域时n=就表示结果来自于Area中的第个区域n=表示结果来源于第个单元格区域……。如果省略n表示结果来源于第个单元格区域。功能Index(Arearcn)的功能是返回Area中第n个单元格区域中的r行c列交叉处的单元格引用。已有“员工参保资料”表和“按人月汇总”表制作如下表格选择姓名、月份后查阅该职工工号和当月社保缴纳情况()姓名、月份用“数据有效性”进行设定姓名数据来源与“员工参保资料”表月份可直接在“数据有效性”对话框中输入。()“工号”查询公式:=VLOOKUP($C$,按人月汇总!$B:$D,,)()查询“单位缴费养老保险”的公式:=INDEX(按人月汇总!$E$:$P$,MATCH(按人分月调阅表!$C$,按人月汇总!$B$:$B$,),MATCH(按人分月调阅表!$E$,按人月汇总!$E$:$P$,))功能:返回大于等于及小于的均匀分布随机实数另外Excel增加函数randbetween(bottom,top)可生成两个数之间的随机数。怎样快速将七年级同学随机安排考场考试?常用字符函数◦LEN(text)计算text文本的长度◦RIGHT(text,n)该函数从text文本的右边取出n个字符。◦LEFT(text,n)该函数从text文本的左边取出n个字符◦MID(text,n,m)该函数从text文本的第n个字符起取出m个字符。=TEXT(MID(B,,(LEN(B)=)*),"#")概述数组公式:能够同时对一组或两组以上的数据进行计算计算的结果可能是一个也可能是多个。普通公式只执行一个简单计算并且返回一个运算结果。数组公式的建立方法()选中需要保存数组公式结果的单元格或单元格区域。()输入公式的内容。()按CtrlShiftEnter键。问题:在D:D求出商品的销售金额。问题:在D:D求出商品的销售金额。一般解决方法:三个普通公式问题:在D:D求出商品的销售金额。数组公式解决:数组公式的优点:()批量计算节省计算时间()不能修改其中任一部分保持公式集合的完整性。问题:在F求出商品的销售总金额普通公式解决办法:()插入辅助列先求出各商品的销售额然后再求总和。()直接在F输入公式“=SUM(B*C,B*C,B*C)”这样看上去不错可是如果有行数据一千行号数据呢?先不考虑单元格能容纳多少字符的问题就光输入公式累也得把你累趴下显然是行不通的。数组公式:选中F单元格输入公式“=SUM(B:B*C:C)”三键确认输入即可。数组公式解决:选中F单元格输入公式“=SUM(B:B*C:C)”三键确认输入即可。()三键输入数组公式。()数组公式同时进行多个计算可返回一个或多个结果。()多单元格数组公式需选中多个单元格进行输入多单元格数组公式具有保护公式的作用。()数组公式可以完成复杂的中间运算得到最终想要的运算结果。、行列数相同数组的运算、数组与单一的数据的运算、单列数组与单行数组的计算、行数(或列数)相同的单行(或单列)数组与多行多列数组的计算、行数(或列数)相同的单行(或单列)数组与多行多列数组的计算、行、列数不相等的数组计算、行、列数不相等的数组计算、行、列数不相等的数组计算认识Excel如何设计表格函数的使用图表的制作数据分析()图表制作过程()组合图()双轴图()甘特图()动态图如何制作如下所示的图?第一步:建立数据表默认生成的图形如下所示格式化图形步骤:()去掉网格线格式化图形步骤:()选中系列点右键选择“添加数据标签”并将数据标签拖动到图例下格式化图形步骤:()设置图表格式的填充色为黑色并将纵坐标、数据标签字体设为白色格式化图形步骤:()设置绘图区背景填充为黑色数据系列分别修改填充颜色。总结制作过程:()建立数据表()选择“插入”选项卡()选择需要制作的图表类型生成图形。格式化图形:()图表区()绘图区()数据系列()坐标轴(水平轴、垂直轴)()网格线()图例()图表标题。、建立组合图◦组合图表是指在一个图表中表示两个或两个以上的数据系列不同的数据系列用不同的图表类型表示。◦建立方法右击某一数据系列从弹出的快捷功能单中选择“更改系列图表类型”就可为该数据系列设置新的图表类型。计划与实施对比交换机路由器中继线电源计划实施计划与实施对比交换机路由器中继线电源计划实施柱形图柱形图和折线图的组合图双Y轴组合图有时为了便于对比分析某些数据需要在同一图表中表达几种具有一定相关性的数据。但由于数据的衡量单位不同所以就很难清晰地表达图表的意图。双轴图能够解决这一矛盾。如何制作如下的条形、折线组合双轴图?甘特图是一种以工作任务和任务完成时间为依据的图形主要用来表示工作项目中的任务和进度安排。甘特图具有简单、醒目和便于编制等特点在企业管理工作中被广泛应用。在甘特图中横轴表示时间纵轴表示项目用线条表示项目在整个工作(工程)期间的计划和实际的活动完成情况。它直观地表明任务计划在什么时候进行及实际进展与计划要求的对比。Excel没有直接提供甘特图的图表模型但可以应用它的二维堆积条型图制作出甘特图。在Excel中制作下图(b)所示的高速公路进度甘特图。、建立甘特图的时间进度表、计算出最早和最后工程时间的序列值、插入工程进度图的二维堆积条形图、隐藏“开始日期”数据系列的形图、格式化“X轴”的时间标签为工程的开始和结束之间的时间段、格式化X轴上的时间标签范围、设置X轴上的时间时间起点即第一个点对应的日期、指定“逆序类型”使时间轴显示在上方、设置“坐标轴标签”和标签位置建立动态图表下表是产品X和产品y在各个地区的销售情况试作出如右所示的每种产品的销售柱形图。动态图表的建立方法()把源数据区域的列标题A:A复制到区域A:A。()在B中输入公式=OFFSET(A,,$A$)最后的结果如右所示:动态图表的建立方法()插入单选按钮控件文件选项自定义功能区右边选项"开发工具"一栏前打钩确定后在主页面就可以看到“开发工具”动态图表的建立方法()单选按钮控件链接单元格=OFFSET(A,,$A$)认识Excel如何设计表格函数的使用图表的制作数据分析()排序()数据筛选()数据分类汇总()数据透视表()合并计算、排序规则◦数字排序规则◦字母排序规则◦逻辑值排序规则◦汉字排序规则汉语拼音序按笔画排序、排序方式◦升序◦降序排序案例某班某次期末考试成绩如图(a)所示已经计算出了每位同学的平均分现需得出一个名次表即从高分到低分的成绩表以便为前名同学发奖学金。◦按日期排序◦按汉字笔画排序、选中要排序的数据区域后,单击”排序和筛选”按钮、单击”自定义排序”按钮弹出“排序”对话框、单击”选项”按钮弹出“选项”对话框、指定”笔画序”自定义排序◦有时需要按人为指定的次序对某此资料排序。◦某大学有个学院每个学院的基本情况如图所示。要求按以下次序排序此表:计算机学院、管理学院、通信学院、财务学院、外语学院、政法学院、中文学院。方法◦单击Office按钮→“文件”→“Excel选项”→“高级”→“编辑自定义列表”Excel会弹出“选项”对话框◦按前面的方式显示出“排序”对话框中通过“序序”下面的“自定义序列”指定建立的序列为排序依据。在此输入自定义的排序序列多关键字排序◦就是对数据表中的数据按两个或两个以上的关键字进行排序。多关键字排序可使数据在第一关键字相同的情况下按第二关键字排序在第一、第二关键字都相同的情况下数据按第三关键字有序其余的以此类推。◦在Excel中最多允许有个排序关键字但不管有多少关键字排序之后的数据总是按第一关键字有序的。年某杂货店各雇员的销售数据如图(a)的A:F所示以第一季度为第一关键字、第二季度为第二关键字、第三季度为第三关键字的排序结果如图(b)的A:F区域所示排序的方式是递增。、源数据表、单击“开始”→“排序和筛选”→“自定义排序”命令弹出“排序”对话框、依次指定排序关键字、排序结果、概念数据筛选就是将数据表中所有不满足条件的记录行暂时隐藏起来只显示那些满足条件的数据行。、Excel的数据筛选方式、自动筛选自动筛选提供了快速查找工作表数据的功能只需通过简单的操作就能够筛选出需要的数据。、高级筛选高级筛选能够完成自动筛选所不能实现的复杂筛选。某公司的人员档案是按招聘报到的先后次序建立的如下图所示。可以看出图中的档案比较混乱公司的管理人员可能需要这样的信息:已婚人员的名单各部门的人员档案信息工资高于的人员有哪些奖金高于的人员有哪些各种学历的人员档案……()已婚人员的名单。()各部门的人员档案信息。()工资高于的人员有哪些。()奖金高于的人员有哪些。()各种学历的人员档案……如此等等原数据表具有自动筛标记的数据表单击“数据”|“筛选”Excel会在表格的标题边显示出自动筛选标志。可借此完成各种自动筛选!如果筛选关键字对应的列是数值型数据可通过“数字筛选”命令指定自定义筛选条件实现灵活的数据查询。在下图所示的职工档案表中筛选年龄最大的名职工。、单击“数据”!“筛选”为数据表显示出自动筛选标志、单击“年龄”右边的自动筛选下拉箭头、单击“个最大的值”弹出对话框、将“”改为即可筛选出年龄最大的位员工!、指定工作表的“筛选”标志、单击“工资筛选”中的“大于”命令、在弹出的对话框中输入工资数据条件区域的构建方法条件“与筛选”查看下图中“运维部”工资和奖金都高于元的人员。、单击“高级”按钮弹出高级筛选设置对话框、指定筛选结果的保存方式。、指定筛选条件和数据区域。认识Excel如何设计表格◦条件格式(比赛自动评分表)◦数据有效性(设计友好的表格)函数的使用◦If、sumif、count、counta、countif(多条件统计)、rank、rand、match、vlookup、offset◦数组公式图表的生成◦甘特图、气泡图、分解饼图、多系列图、双轴图◦图的美化数据分析◦排序◦自动筛选、高级筛选◦分类汇总◦数据透视表(普通数据透视表、多重合并计算数据透视表)课程要求课程内容认识Excel如何设计表格专业人士制作的表格常规表格专业化表格设计原则

用户评价(0)

关闭

新课改视野下建构高中语文教学实验成果报告(32KB)

抱歉,积分不足下载失败,请稍后再试!

提示

试读已结束,如需要继续阅读或者下载,敬请购买!

文档小程序码

使用微信“扫一扫”扫码寻找文档

1

打开微信

2

扫描小程序码

3

发布寻找信息

4

等待寻找结果

我知道了
评分:

/23

Excel数据分析高级应用

仅供在线阅读

VIP

在线
客服

免费
邮箱

爱问共享资料服务号

扫描关注领取更多福利