第5章 数据统计和分析软件Excel 2010.ppt

第5章数据统计和分析软件Excel2010,北京理工大学珠海学院计算机学院,第5章数据统计和分析软件Excel2010,Excel中文版是一个功能强大的数据处理软件，它具有强大的数据计算与分析功能，可以把数据通过各种统计图的形式形象地表示出来，被广泛地应用于财务、金融、经济、审计和统计等众多领域。,了解和掌握Excel的基础知识与基本操作掌握工作表的建立、编辑以及格式化操作掌握公式和函数的使用掌握图表处理操作掌握数据库的应用,一、Excel2010的窗口组成,启动Excel2010通过Windows开始菜单选择“开始”“所有程序”命令，然后在弹出的菜单中选择“MicrosoftOffice”“MicrosoftExcel2010”命令，即可启动Excel2010。通过桌面快捷方式,Excel2010的工作界面,一、Excel2010的窗口组成,工作簿与工作表工作簿工作簿是指在Excel环境中用来存储并处理工作数据的文件。工作表工作表是指由“65,536”个行和“256”个列所构成的一个表格。,一、Excel2010的窗口组成,单元格及区域单元格是指表格中的一个格子。每个单元格都有自己的行列位置（或称坐标），单元格的坐标表示方法是：列标+行号。列号：用A、BAA、AB.IV等表示EXCEL最多256列行号：用1、2、3等表示EXCEL最多65536行,注意：先列后行,一、Excel2010的窗口组成,单元格及区域通常单元格坐标有三种表示方法：相对坐标（或称相对地址）：如A1绝对坐标（或称绝对地址）：如$A$1混合坐标（或称混合地址）：如$A1,A$1,一、Excel2010的窗口组成,单元格及区域区域是指矩形状的若干相邻单元格区域的表示：对角地址，中间用“:”相隔。例如：a1:c3,范例-区域的概念,二、管理工作簿,工作表的操作,切换工作表,插入工作表,重命名工作表,复制工作表,隐藏/显示工作表,删除工作表,移动工作表,范例-管理工作簿,选定单元格选择一个、选择相邻多个（区域）、选择非相邻单元格、选择整行或整列、选择整张表输入数据,三、建立工作表,输入字符型数据输入日期型数据日期格式：年/月/日或年-月-日时间格式：时:分:秒3.输入数值型数据4.序列填充5.输入公式(后面详讲)6.利用数据有效性进行输入,范例1-建立工作表,四、编辑单元格,1.编辑单元格数据2.剪切3.复制4.清除单元格5.删除单元格6.插入单元格,四、编辑单元格,7.添加批注对工作表中一些复杂的公式或者特殊的单元格可以加入数据批注。给单元格添加批注“审阅”选项卡下“批注”组中的“新建批注”隐藏或显示批注及其标识符“审阅”选项卡下“批注”组中的“显示/隐藏批注”,范例1,五、格式化工作表,设置数字格式,设置文本和单元格格式,设置文本的对齐方式,设置列宽和行高,边框线设置,格式化工作表,设置单元格的底纹和图案,自动套用格式,条件格式,1.设置数字格式,Excel提供了大量的数字格式，例如，可以将数字格式成带有货币符号的形式、多个小数位数，百分数或者科学记数法等。使用“格式”工具栏快速格式化数字“格式”,货币样式,百分比样式,千位分隔样式,增加/减少小数位数,范例2-数字格式化,1.设置数字格式,使用“单元格格式”对话框设置数字格式“开始”选项卡-“单元格”组-“格式”选项下的“设置单元格格式”,范例2-数字格式化,2.条件格式,在Excel中，为用户提供了条件格式，它使用户可以根据单元格的内容有选择地和自动地应用单元格的格式，避免人工处理时繁琐、重复的操作。“开始”选项卡下“样式”组中的“条件格式”,范例2-条件格式,3.设置文本和单元格格式,使用“开始”选项卡“字体”组中的按钮设置文本外观,范例2-文本,利用“设置单元格格式”对话框设置文本格式,4.设置文本的对齐方式,使用“开始”选项卡下“对齐方式”组内的按钮设置对齐方式使用“设置单元格格式”对话框设置对齐方式,范例2-文本,5.设置列宽和行高,1）设置列宽方法一：使用鼠标在列标处拖动方法二：“开始”选项卡“单元格”组中“格式”选项下的“列宽”2）设置行高方法一：使用鼠标在行标处拖动方法二：“开始”选项卡“单元格”组中“格式”选项下的“行高”,范例2-行高列宽,6.边框线的设置,默认情况下,工作表的单元格是没有格线的，通过边框线可以强调某一范围的数据。利用“边框”按钮设置单元格的边框利用“设置单元格格式”对话框设置单元格的边框,6.边框线的设置,利用“边框”按钮设置单元格的边框,单击,范例2-边框1,6.边框线的设置,利用“设置单元格格式”对话框设置单元格的边框,范例2-边框2,第一步：先选样式和颜色,第二步：作用的地方,7.设置单元格的底纹和图案,为单元格设置不同的底纹和图案，可以突出某些单元格或区域的显示效果。利用“填充颜色”按钮设置底纹利用“设置单元格格式”对话框设置图案和底纹,7.设置单元格的底纹和图案,利用“设置单元格格式”对话框设置图案和底纹,范例2-底纹,利用“填充颜色”按钮可以快速为选择的单元格设置不同的底纹。如果还想为单元格设置不同的填充效果和图案，请用“设置单元格格式”对话框。,8.套用表格格式,在显示某些表格数据时，可能会经常用到某些固定的表格格式。Excel提供了多种预定义的标准表格格式，供用户选用。,范例2-自动套用,公式与函数,创建公式,单元格的引用,常用函数,公式与函数,一、创建公式,公式可以引用同一工作表的单元格，同一工作簿不同工作表中的单元格或者其他工作簿的工作表中的单元格。,输入公式输入公式时是以一个等号（=）作为开头。在一个公式中可以包含有各种运算符号、常量、变量、函数以及单元格引用等。,一、创建公式,1.算术运算符算术运算符包括加号(+),减号(-),星号(*)，斜杠(/)，百分号()以及脱字符()注意：公式中(),范例3-+-,一、创建公式,2.文本运算符&,范例3-文本,3.比较运算符=、=,范例3-比较,一、创建公式,4.引用运算符,一、创建公式,5.运算符的优先级,二、单元格的引用,相对地址引用绝对地址引用混合地址引用三维引用,二、单元格的引用,相对地址引用所谓相对地址是指：如果将含有单元地址的公式复制或移动到别的单元格时，这个公式中的单元格引用将会根据公式移动的相对位置作相应的改变。,二、单元格的引用,绝对地址引用所谓绝对地址引用是指：对于包括绝对引用的公式，无论将公式复制到什么位置，总是引用那些单元格。,二、单元格的引用,混合地址引用单元格的混合引用是指公式中参数的行采用相对引用，列采用绝对引用；或列采用绝对引用、行采用相对引用，如$A3，A$3。当含有公式的单元格因插入、复制等原因引起行、列引用的变化，公式中相对引用部分随公式位置的变化而变化，绝对引用部分不随公式位置的变化而变化。,二、单元格的引用,混合地址引用,范例3-混合引用,二、单元格的引用,三维引用三维引用包含一系列工作表名称和单元格或单元格区域引用。三维引用的一般格式为：“工作表标签！单元格引用”“工作簿名称工作表标签！单元格引用”例如:如果想引用“Sheet1”工作表中的单元格B2，则应输入“Sheetl!B2”。例如:Book1Sheet1!B2,三、函数的使用,函数应用：掌握以下函数，按要求对工作表进行数据统计或分析统计函数：SUM，SUMIF,AVERAGE，COUNT，COUNTA,COUNTIF,MAX，MIN,RANK。数学函数：ABS,INT,ROUND,TRUNC,RAND()。日期函数：DATE,DAY,MONTH,YEAR,NOW(),TODAY()。逻辑函数：IF函数，AND函数，OR函数。,三、函数的使用,函数应用：掌握以下函数，按要求对工作表进行数据统计或分析财务函数：FV函数，PMT函数,PV函数。频率分布函数：FREQUENCY。数据库统计函数：DAVERAGE,DCOUNT,DCOUNTA,DMAX,DMIN,DSUM。查找函数：VLOOKUP,1.统计函数,范例3-统计函数,2.数学函数,3.日期函数,4.逻辑函数,（1）IF函数IF函数的功能是执行真假值判断，并根据逻辑判断值返回结果。【语法】IF(logical_test,value_if_true,value_if_false)【功能】本函数对条件表达式进行测试，如果条件成立，则取第一个值，否则取第二个值。（2）AND函数AND函数的功能是增加用于执行逻辑检验的条件。【语法】AND(logical1,logical2,)（3）OR函数OR函数的功能是对公式中的条件进行连接。【语法】OR(logical1,logical2,),范例3-逻辑函数,5.财务函数,（1）求某项投资的未来值FV函数【语法】FV(rate,nper,pmt,pv,type)【功能】基于固定利率及等额分期付款方式，返回某项投资的未来值。（2）求贷款分期偿还额PMT函数【语法】PMT(rate,nper,pv,fv,type)【功能】基于固定利率及等额分期付款方式，返回投资或贷款的每期付款额。（3）求某项投资的现值PV函数【语法】PV(rate,nper,pmt,fv,type)【功能】计算某项投资的现值。,范例3-FV函数/PMT函数/PV函数,6.频率分布函数,频率分布统计函数用于统计一组数据在各个数值区间的分布情况，这是对数据进行分析的常用方法之一。【语法】FREQUENCY(data_array,bins_array)【功能】计算一组数（data_array）分布在指定各区间（由bins_array来确定）的个数。,范例3-频率,7.数据库统计函数,范例3-数据库统计函数,8.查找函数,VLOOKUP函数的功能是进行列查找，并返回当前行中指定的列的数值。【语法】VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)【功能】搜索表区域首行满足条件的元素，确定待检索单元格在区域中的行序号，再进一步返回单元格的值。,范例3-查找函数,四、模拟分析,单变量模拟运算表单变量模拟运算表，用于调试某个变量，以获得最佳的效果。【例】某公司以年利率7%向银行贷款600,000元，规定在以后的十年内逐年进行偿还，每年应偿还给银行多少钱？,B5单元格输入的函数为：=PMT(B4,B3,B2),范例3-单变量模拟运算表,四、模拟分析,2.双变量模拟运算表利用双变量模拟运算表，根据输入两个变量的不同值，例如同时改变年利率和偿还期限来计算出家庭每年应偿还的金额。,范例3-双变量模拟运算表,图表的应用,图表的基本概念,建立图表,“图表”工具栏,图表的编辑,图表的应用,图表操作：图表类型、分类、数据系列。图表的建立与编辑：图表的建立，插入/删除/修改图表（字体：名称、倾斜、加粗、字号、下划线、颜色、删除线、上下标；边框：位置、线型、线宽、颜色；图案：底纹颜色、图案、图案颜色、边框、图案和填充）。,一、图表的基本概念,1数据系列一个数据系列是图表中所绘出的一组相关数据点，它们来自工作表的一行或一列。2分类用“分类”来组织数据系列中的值。每个数据系列中数据点的个数等于分类数。,范例4-基本概念,二、建立图表,“插入”选项卡“图表”,三、图表的编辑,图表的移动和调整大小增加和删除图表数据改变图表数据改变图表文字、颜色、图案改变数据的绘制方式改变图表的类型,范例4,三、图表的编辑,使用迷你图分析数据,范例4-迷你图,Excel2010数据库应用,数据库应用：数据的排序（包括自定义排序）筛选（自动筛选，高级筛选）分类汇总(4)数据透视表,建立数据库,数据的排序,数据的分类汇总,数据的筛选,数据库应用,数据的透视表,列标题字段名,每一行一条记录,每一列一个字段,字段数=256记录数=65535,一、建立数据库,二、数据的排序,排序练习,1.利用排序按钮2.高级排序3.自定义排序4.其他方式排序,三、数据的筛选,当筛选一个数据清单时，Excel只显示那些符合条件的记录，而将其他记录从视图中隐藏起来。1.自动筛选数据2.使用高级筛选,自动筛选-自动筛选1、2,“最大”/“最小”,想看到几项,三、数据的筛选自动筛选,如果用户想看到数据清单中最大或最小的几项，可以使用Excel的自动筛选前10个功能来进行筛选。“数据”“筛选”“自动筛选”“自动筛选前10个”,自动筛选-自动筛选3,三、数据的筛选自动筛选,当希望筛选满足一定条件的文本信息，可以使用自动筛选。“数据”“筛选”“自动筛选”“文本筛选”,自动筛选-自动筛选4,三、数据的筛选-自动筛选,当希望查找介于两个数值之间的记录时，请使用该功能。“数据”“筛选”“自动筛选”“（自定义）”,自动筛选-自动筛选5,三、数据的筛选-高级筛选,使用自动筛选功能可以方便、快速地找到符合条件的记录，但是该功能的查找的条件不能太复杂。如果需要键入复杂的筛选条件，或者将符合条件的数据输出到工作表的其他单元格中,请使用高级筛选功能。,三、数据的筛选-高级筛选,操作步骤如下：在工作表中远离数据清单的位置设置条件区域。在数据清单中选择任一单元格。“数据”“筛选”“高