excel数据处理-第三部分统计图表与公式函数

第三局部公式函数与统计图表

张杰公式和函数7数组公式及其应用名字函数简介使用Excel帮助理解函数常用的工作表函数逻辑函数数学和三角函数7.1公式及其应用由运算符、数值、字符串、变量和函数组成。必须以“＝〞开头。“＝〞后跟数值、运算符、变量或函数，可有括号。例如：B5“＝6+7*6/3+(6/5)*56〞，计算结果将显示在单元格B5中。公式运算的优先级引用引用：在一个单元格的公式中用到了其他单元格中的内容。引用是通过单元格的位置或单元格的名称来实现。在默认的状态下，单元格采用列标号和行号来识别的，如A1，B3，C4等都惟一确定了一个单元格。引用相对引用相对引用也称相对地址，它用列标与行号直接表示单元格，如A2，B5，D8等都是相对引用。如果某个单元格的公式被复制到另一个单元格时，原单元格内公式中的地址在新单元格中需要发生相应的变化，就需要用相对引用来实现。引用绝对引用在表示单元格的列标与行号前面加“$〞符号的单元格引用就称为绝对引用。它的最大特点是在复制操作过程中，公式中的单元格地址始终保持不变。使用方法：选中要作为绝对引用的单元格后按F4键。混合引用相对地址与绝对地址可以混合使用“G3=$C3+$D3+$E3+$F3〞3.1数组公式及其应用数组公式的建立方法建立数组公式进行批量数据的处理，操作步骤为：如果希望数组公式返回一个结果，可先选中保存计算结果的单元格；如果数组公式返回多个结果，那么需先选中要保存数组公式计算结果的单元格区域。输入公式的内容。公式输入完成后，按Ctrl＋Shift＋Enter键结束。实例用数组公式计算两个数据区域的乘积用数组公式计算多列数据之和7.2名字Excel允许为单元格或单元格区域定义名字，即用一个名字来代替单元格或区域的引用，在需要引用该单元格时，直接用它的名字就行了。名字的好处公式的意义更加清楚，便于他人理解。在一个工作表中定义的名字，可在同一工作簿的任一工作表中直接应用。7.2名字名字的定义使用菜单栏定义名字定义方法一次只能定义一个名字适用于一个单元格或单元格区域名字的定义【插入】【名称】【定义】用指定的方式定义名字一次定义多个名字，可以使用指定方式。7.2名字名字的定义用名字栏定义名字在名字栏输入名字后按回车键即可名字栏7.2名字引用另一个工作表中的名字定义一个名字后，该名字就能被工作簿中的各个工作表直接引用。行列交叉点所谓行列交叉点，就是指〔行的名字列的名字〕确定的单元格。【插入】【名称】【指定】【首行】【最左列】7.3函数简介函数是Excel已经定义好了的一些特殊公式函数可以对一个或多个数据进行计算，然后把计算结果存放到某个单元格中。强大的函数功能把Excel与普通的电子表格区分开来。普通的电子表格如Word的表格、中文之星中的表格，只能完成一般的表格制作功能，进行相当简单的数据处理。函数是一些预定义的公式，使用一些称为参数的特定数值按特定的顺序或结构进行计算。Excel提供了大量的内置函数：财务、工程、统计、数据库、时间、数学等。7.4.1函数调用函数的语法Excel函数结构大致分为函数名和参数表两局部如：函数名〔参数1,参数2,…〕“函数名〞说明要执行的运算“函数名〞后用圆括号括起来的是参数表，参数表说明函数使用的单元格或数值＝AVERAGE〔A1:B5,C1:D2,12,32〕7.4.1函数调用等号，如果函数位于公式的开头函数名各参数之间用逗号隔开参数表参数用括号括起7.4.1函数调用函数的语法没有参数的函数称为无参函数无参函数的调用形式为：函数名〔〕无参函数名后的圆括号是必需的。如PI函数：PI()Today(),now()7.4.1函数调用函数调用直接输入前提是知道函数名及函数的参数，就可以直接输入。使用函数向导通过函数向导，可以知道函数需要的各个参数及参数的类型，方便的输入并不熟悉的函数。使用Excel帮助理解函数7.5常用工作表函数自动求和按钮SUM函数：加和AVERAGE函数：平均值SQRT函数：平方根COUNT、COUNTA和COUNTBLANK函数COUNT：统计数值数据的个数COUNTA：统计非空单元格的个数COUNTBLANK：统计空白单元格的数目count(12,ab,25,360,gh,ma,18)Max和Min函数：返回最大/最小值Max((number1,number2,…))Rank函数：返回一个数值在数值列表中的排序

Rank(number1,ref,order)Countif函数：计算满足给定条件的单元格个数

countif(range,criteria)

countif(range,criteria)22237.6逻辑函数比较运算和逻辑运算比较运算比较运算可以比较两个值的大小、相等或不等关系。在Excel中，比较运算的结果是一个逻辑值，不是TRUE就是FALSE。等于【＝】大于【>】小于【<】大于等于【>=】；小于等于【<=】不等于【<>】例题：楼盘展销费用预算表5.xlsxIF条件函数：判断真假IF(logical_test,value_if_true,value_if_false)H3=IF(F3>=5000,IF(F3>=10000,〞big〞，〞median〞),〞small〞)257.6逻辑函数AND逻辑与：所有逻辑值为真，才返回TRUEAND(logical1,logical2,…，logical255)26OR逻辑或函数：任意一个为真那么返回TRUEOR(logical1,logical2,…，logical255)27IFERROR函数：计算错误时范围用户指定的值Iferror(value,value_if_error)287.7数学和三角函数几个常用的数学函数随机函数RAND=rand()产生一个大于等于0且小于1的均匀分布的随机数。RANDBETWEEN〔bottom，top〕产生一个介于bottom和top之间的随机整数。取整函数INT该函数用于取整，返回实数x舍入后不大于x的整数值。ROUND〔number，n〕该函数返回某个数字按指定位数四舍五入的数字。7.7数学和三角函数ROMAN〔number，form〕将阿拉伯数字转换为文本形式的罗马数字。Number是需要转换的阿拉伯数字，只能取0～3999之间的数值。逆矩阵函数MINVERSE〔array〕(了解)Array是具有相等行列数的数值数组〔即方阵〕。MMULT〔array1，array2〕(了解)计算两数组的矩阵乘积。结果矩阵的行数与array1的行数相同，列数与array2的列数相同。DATE函数：DATE(year,month,day)DATE(2021,4,25)Today()函数当前系统日期Year和Month函数：返回年份/月份Year(DATE(2021,4,25))Now()函数：返回当前日期和时间所对应的序列Hour和Minute函数：返回小时/分钟Weekday:计算格顶的日期是星期几【ctrl】+；【ctrl】+【shift】+；317.8日期及时间函数函数的应用实例利用函数制作2021年五一劳动节的倒计时牌图表的使用8主要内容认识Excel图表图表类型图表的建立图表的修改格式化图表复杂图表的建立数理统计图表的应用8.1认识Excel图表8.2图表类型嵌入式图表是把图表直接插入到数据所在的工作表主要用于说明数据与工作表的关系用图表来说明和解释工作表中的数据，具有很强的说服力。图表工作表是把图表和与之相关的源数据表分开存放图表放在一个独立的工作表中，用于创立图表中的数据表那么存放在另一个独立的工作表中。8.2图表类型Excel标准图表类型柱形图即直方图，常用来表示不同工程之间的比较结果，也可用来比照数据在一定时间内的变化情况。为Excel默认类型。条形图显示了各个工程之间的比较情况，纵轴表示分类，横轴表示值。面积图面积图用于显示不同数据系列之间的比照关系，同时也显示各数据系列与整体的比例关系，尤其强调随时间的变化幅度。8.2图表类型Excel标准图表类型折线图强调数据的开展趋势。饼图强调总体与局部的关系，常用于表示各组分局部在总体中所占的百分比。其它标准图圆环图、气泡图、雷达图、股价图、曲面图、XY散点图、锥形图、圆柱图和棱锥图等14中标准图表类型。柱形图是竖直条形，而条形图是水平条形，二者可交换使用。用来表示一组或几组分类相关的数值，显示各个工程之间的比较情况，一般用横轴表示分类，纵轴表示值，它主要强调各个值之间的比较而并不太关心时间。常用于不同现象的比较，也可以采用时间顺序描述现象的开展趋势。39柱形图的子图表类型有７种，使用时要注意各类型的特点及适用情况折线图是将图表中各点之间用线段相继连接起来而形成的连续图形，图中各点的高度代表该点的数据值，它一般用来描述某一变量在一段时间内的变动情况，能较好地反映事物的开展趋势。40饼图显示了构成数据系列的工程相对于工程总和的比例大小，只能显示一个数据序列，或每一个饼图只能显示一个数据序列。假设有多种数据序列需要用图表显示，就需要多张饼图或用圆环图。当希望强调某个重要元素时，饼图就是最适宜的选择。一个饼图可以别离出一个或多个扇区。激活图表并单击任一扇区即可选择整个饼图，然后单击要别离的扇区把它拖离中心即可。41圆环图与饼图只有两点不同：〔１〕可以显示多个数据系列。〔２〕中心有一个洞。注意：多于一个系列的圆环图很难解释。如处干圆环外部的扇区有相对较大的尺寸而具有一定欺骗性，因此使用圆环图应慎重。许多情况下，使用堆积柱形图比圆环图能更好地表示其含义。42ＸＹ散点图表示两个数据序列之间的某种相互关联性。它既可以显示多个数据系列的数值间的关系，也可以将两组数字绘制成一系列的ＸＹ坐标。特别应该注意的是ＸＹ散点图不能对数据透视图报表使用。43雷达图是一种“蜘蛛网式〞的图形，图中的直线数决定于一个数据序列中的数据个数。各条直线之间是等角度分割，直线上数据点离开中心点的距离由它所代表的数据点的相对值决定。每一个数据序列在雷达图上表示一个多边形，这时，雷达图中有多个多边形。每个分类都有它自己的数值轴，每个数值轴都从中心向外辐射。而线条那么以相同的顺序连接所有的值。在本例图表中，食品Ｃ是覆盖了最大面积的数据系列，它代表了维生素含量最高的食品。448.3图表的建立建立图表【插入】【图表】在常用工具栏中点击“图表向导〞按钮图表建立的步骤建立数据表选择图表类型选择图表数据系列修改图表格式选择图表的位置选定单元格区域D2:E18，切换到【插入】，单击【图表】，选择【柱状图】，选择要插入的图表类型，【确定】例题：汽车销售表1.xlsx注意：在图表的制作过程中、制作完成后均有很多种修饰工程，可根据自己的爱好和需要，按照提示，选择满意的背景、色彩、子图表、字体等修饰图表468.4修改图表修改图表类型【图表】【图表类型】数据系列操作添加数据系列删除数据系列修改数据系列的产生方式【图表】【源数据】添加数据标志默认状态下，图表向导创立的图表中没有数据标志。数据标志可以是各数据点的值，也可以是与数据点有关的分类轴标志。【图表】【图表选项】【数据标志】8.4修改图表图例操作图例用来说明数据系列使用的标志或符号，用以区分不同的数据系列。【图表】【图表选项】【图例】单元格与数据点的值的修改在Excel中，工作表单元格中的数据与图表中的对应数据点是相互影响的，在任何时候，图表都是工作表数据的真实反映。取消或设置网格线8.5格式化图表调整图表大小改变图表颜色、图案、边框设置背景图片右键菜单【图表区格式】【图案】【填充效果】【图片】填充数据系列右键菜单【数据系列格式】8.6复杂图表的建立目的在Excel图表不能满足应用需要的时候，可以运用一些小技巧建立相对复杂的图表，如建立组合图表、图片图表、为图表添加趋势线等。8.6复杂图表的建立添加趋势线趋势线以图形方式显示了数据的开展趋势，它常用于预测分析。支持趋势线的图表类型有非堆积型面积图、条形图、柱形图、折线图、股价图、气泡图和XY散点图等。添加趋势线【图表】【添加趋势线】利用趋势线进行预测对现有趋势线进行向前〔或向后〕的延伸，然后根据延伸的趋势线可以分析数据的开展趋势，并借此进行数据预测。8.6复杂图表的建立建立组合图组合图是指在一个图表中表示两个或两个以上的数据系列，而且不同的数据系列用不同的图表类型表示。至少有三种不同的方法可以创立组合图表采用图表向导建立组合图表，2003版利用Excel内置的“自定义类型〞建立组合图表把已建立好的图表中的一个数据系列改变为另一种图表类型2003版本中含有组合图的选择2021版本实例必须含两个以上的系列先建立柱状图,然后选中其中一个系列设置所选内容格式次坐标轴设置更改数据类型折线图8.7数理统计图表的应用累计频率分布图和直方图输入源数据表确定分组组距＝〔最大数据值－最小数据值〕/组数每组上边界值＝每组下边界值＋组距假设第一组的下边界值＝最小数据值－组距用Excel的分析工具库绘制频数分布直方图【工具】【数据分析】【直方图】8.7数理统计图表的应用一元回归预测作出XY散点图添加趋势线【选项】【显示公式】8.7数理统计图表的应用绘制正态分布图计算正态分布函数值NORMDIST〔X，均值，标准差，FALSE〕计算标准正态累积分布函数值NORMSDIST〔X〕绘制正态分布图第四局部统计分析局部统计数据的搜集与整理统计数据的描述随机变量的概率分布参数估计假设测验方差分析相关和回归分析主要内容FIRST！！excel2021中数据分析工具的加载！！！【文件】

【选项】【excel选项】

【转到】

【加载宏】

【分析工具库】

【确定】

【数据】

【分析工具】

1.统计数据的搜集与整理统计数据的搜集组织抽样调查，首先从调查对象中抽取局部单位作为样本，然后对样本单位进行调查，再根据调查取得的样本数据推断总体的数量特征。分析工具库中的“抽样〞工具周期模式：等距抽样或机械抽样随机模式：适合于纯随机抽样、分类抽样、整群抽样和阶段抽样注意：随机抽样任何数值都可以被屡次抽取!【数据】-【筛选】-【高级筛选】统计数据的整理用frequency函数进行统计分组Frequency(data_array,bins_array),以一列垂直数组返回某个区域中数据的频率分布,data_array表示用来计算频率的一组数据或单元格区;Bins_array表示为前面数据进行分隔一列数值;【ctrl】+【shift】+enter，数组公式不能按enter用直方图工具进行分组、计算频率和绘图可将数据分组，计算样本单位在各组中出现的次数〔频数〕和频率，绘出次数分布直方图和累计频率折线图。2.统计数据的描述集中趋势的描述众数出现次数最多的变量值－－函数Mode假设是分组资料：中位数处于中间位置的数值－－函数Median对于分组资料：平均数函数－－Average2.统计数据的描述2.统计数据的描述离散程度的描述标准差求样本标准差的函数Stdev求总体标准差的函数Stdevp方差求样本方差的函数Var求总体方差的函数Varp百分位数函数：Percentile(array,k),k必须为0-1离均差平方和：DEVSQ(number1,number2,…)四分位数：QUARTILE(array,quart)Array:为需要求得四分位数的并按从小到大顺序排列的数组Quart：0,1,2,3,4描述统计工具的使用【工具】--【数据分析】--【描述统计】3.随机变量的概率分布二项分布0、1总体中n+1种变量有它各自的概率组成的一个分布。计算二项分布下变量概率的函数－－Binomdist需要输入：试验成功的次数、独立试验的总次数、一次试验中成功的概率、是否累计分布频率〔true、false〕泊松分布描述小概率事件，可作为二项分布在一定条件下的一种近似。泊松分布下变量值的概率计算的函数－－Poisson需要输入：事件出现的次数、期望值、是否累计分布频率〔true、false〕3.随机变量的概率分布正态分布正态分布是应用最广的一种分布，许多自然界和社会经济领域的随机变量都服从正态分布。平均数和标准差是正态分布的两个特征值。用于标准正态分布的函数Normsdist及其逆函数Normsinv用于非标准正态分布的函数Normdist及其逆函数Norminv4.参数估计统计推断概述参数估计用样本统计数估计总体的参数假设测验对总体提出一定的假设，然后根据样本数据检测建设是否成立各种分布的临界值的计算方法T分布【Tinv】

分布【Chiinv】F分布【Finv】标准正态分布【Normsinv】4.参数估计总体平均数的区间估计【Confidence】正态总体非正态总体，但样本量大于30两个平均数差数的区间估计根据公式进行样本容量确实定根据公式进行假设样本取自50名乘车上班的旅客，他们花在路上的平均时间为30分钟，总体标准偏差为2.5分钟。假设alpha=.05，计算95%可信区间。5.假设检验5.假设检验根本概念单个样本平均数的假设测验两个样本平均数的假设测验－组群比较两个样本平均数的假设测验－成比照较利用分析工具库进行假设测验【T检验：平均数的成对二样本分析】【T检验：双样本等方差假设】【T检验：双样本异方差假设】【T检验：双样本平均差检验】T检验：双样本等方差假设T检验：平均数的成对二样本分析6.方差分析方差分析是常用的一种分析统计数据的方法，它可以用于分析一种因素或多种因素对某一事物有无显著影响。单因素方差分析【方差分析：单因素方差分析】无重复双因素方差分析【方差分析：无重复双因素分析】可重复双因素方差分析【方差分析：可重复双因素分析】方差分析：单因素方差分析如果在选取数据时包含了水平标志，那么选择标志位于第一行方差分析：无重复两因素分析〔练习〕方差分析：可重复双因素分析T检验：TTEST(array1,