ecel数据统计分析

乔红qqqhhh9988@主要内容*第1章基础知识与基本操作*第2章公式、常用函数与地址引用第3章工作簿与工作表第4章格式化工作表*第5章图表与打印输出*第6章数据处理与管理*第7章函数与应用第8章数据分析第9章其他第1章基础知识与基本操作•一、Excel2007简介•Excel2007是目前市场上功能最强大的电子表格制作软件,它和Word、PowerPoint、Access等组件一起,构成了Office2007办公软件的完整体系。Excel不仅具有强大的数据组织、计算、分析和统计功能,还可以通过图表、图形等多种形式形象地显示处理结果,更能够方便地与Office2007其他组件相互调用数据,实现资源共享。显示模式显示比例1.2Excel2007的新界面和以前的版本相比,Excel2007的工作界面颜色更加柔和,更贴近于WindowsVista操作系统。Excel2007的工1.“文件”菜单单击Excel工作界面左上角的Office按钮,可以打开“文件”菜单。在该菜单中,用户可以利用其中的命令新建、打开、保存、打印、共享以及发布工作簿。‘「V1Murn番返侵用的文格1m«用快相打开(Q)2成3：计篇，1Ii招，®霣hl新⑤407生度，赚te(另存鳩.5析售业绝表la与j|Hfi)0»7S台-场毎曰置业觳统计聚8五台颺®髩日鷺此載氏什表«•(&►2五ft蔺贼月實业觀什表耦上半条员rwiti^s2^雌(D)►10月份ftlww业番學钎喊筠成本it算聚U?发布(L!)►财计ira4dQj朔幻07芘度考樓擊07庄度，S極2P«UJz\*B33tXteilAJ2.快速访问工具栏Excel2007的快速访问工具栏中包含最常用操作的快捷按钮,方便用户使用。单击快速访问工具栏中的按钮,可以执行相应的功能铲建打开/贿电子BB件WST印nHHSQS✓

_y恢复升序排序味利E序其他命2%能区下方西示($)功給区帝＜1姻1)3.标题栏标题栏位于窗口的最上方,用于显示当前正在运行的程序名及文件名等信息。如果是刚打开的新T作簿文件,用户所看到的文件名是Bookl,这是Excel2007默认建立可以最小化、最的文件名。单击标题栏右端的按钮,大化或关闭窗口。_SH番用供计褰•MicrosoftExcel4.功能区功能区是在Excel2007工作界面中添加的新元素，它将旧版本Excel中的菜单栏与工具栏结和在一起，以选项卡的形式列出Excel2007中的操作命令。默认情况下，Excel2007的功能区中的选项卡包括:“开始”诜项卡、“插入”诜项卡、“页面布局”选项卡、“公式X:卡、U>1fl'“u_•>A!■介."l州%SfWRW‘斯WffJtan«M5.状态栏与显示模式状态栏位于窗口底部,用来显示当前工作区的状态。Excel2007支持3种显录模式,分别为“普通”模式、“页面布局”模式与“分页预览”模式,单击Excel2007窗口左下角的按钮可以切换显示模式。就绪□-启动/退出/关闭-窗口/工作簿/工作表•电子表格、工作簿(.xlsx.xlsm)、工作表标签、•工作表与单元格-文件的建立与打开-文件的保存与加密保存-保存工作区:视图/保存工作区Book2功能区编辑栏HA列标行号-垂直滚动条工作表格区水平滚动条工作表标签Jheetl状态栏显示比例、、、、Vk、、、、、、＜、、、、、、、、、、、、、、、、、、、、、、、、、、、VX、、、、、、、、、、、、、、V＜、、、、、、、、、、、、、、、、、、、、BIQ23

i5jzi~i89i10,11i12:13,14J516.一1?.!__Excel窗口中的各种元素“文件”菜单快速访问工具栏标题栏r^.MAJUfiiSH该郎蓽闻现E力_|-，XAA•11・至觀X?械—Aymx.tir

/i雉唎—贼■4jB/U，A.a’丨塞3-"?'%,粒3*酌玲，

田格式，J_.-^'A**5?*it；ri«-|雜-脚-.f^■利对柿式醉華元慵蝴第1章基础知识与基本操作数据类型与数据输入•数值型数据与输入-日期格式与输入•分数形式3/8;3-8；当前日期CTRL+;-文本型、逻辑型数据与输入•身份证数据录入___-输入大批数据的方法•多个工作表数据录入•等差数列•在单元格内输入/显示多行文本:-开始/对齐方式组第1章基础知识与基本操作•三、选定/修改/删除-选定行/列/单元格-修改、删除数据删除与清除的区别？：开始第1章基础知识与基本操作•四、输入序列与自定义序列一自动填充序列•用填充柄填充数据序列•等差数列:开始的两个单元格数据，然后拖曳•用命令填充等差或等比序列-开始1填充序列•自动填充文字序列•自定义序列-在工作表中,选择要在填充序列中使用的项目列表。一单击“Office按钮/“Excel选项”。--“常用”,/“使用Excel时采用的首选项”下单击“编辑自定义列表”。-“从单元格中导入序列”/“导入”第1章基础知识与基本操作•五、移动插入/复制插入/交换数据•移动插入-Shift+左键-右键拖曳•复制插入-Ctrl+Shift+左键—右键拖曳•交换数据-相邻的两行或两列做移动插入,即可实现数据交换第1章基础知识与基本操作•六、插入行/列/单元格•插入行/列-选定行、列或单元格（选定的数量与将要插入的数量是等同的）一插入菜单3行（列）,或者右键插入•插入单元格-选定单元格区域（选定的数量与将要插入的数量是等同的）-插入菜单3单元格,或者右键插入1951第1章基础知识与基本操作•七、命名行/列/单元格区域•命名的好处-通过名字引用单元格区域，要比用地址引用更加直观；-通过名字可以快速选定单元格区域•命名的方法-单元格区域命名:公式=＞定义名称-为选定区域的行/列命名，公式=＞根据所选内创建第1章基础知识与基本操作•八、批注•批注是为单元格加注释•■—个单元格加批注后,右上角会出现一个三角标识•当鼠标指向这个标识的时候,显示批注信息•审核=>新建批注,或者右键插入批注_第2章公式、常用函数与地址引用•二课时•本章重点:•运算符、公式与常用函数•相对地址、绝对地址和混合地址•公式的复制对公式中地址引用的影响•难点:•相对地址、绝对地址和混合地址的正确使用第2章公式、常用函数与地址引用■—、公戎:■以等号开始，可以包含函数、地址引用、运算符、常量。运算符含义优先渡负号或单目减高%百分号A乘方\/乘、除+、-加、减&文本连接运算举例关系运〉|•心^^ll^、、s、、、、、第2章公式、常用函数与地址引用-单元格区域的表示方法-简单公式举例•1.地址引用三种形式：•在公式输入时，按F4键可在几种引用之间转换。•在将公式复制到其他单元时起作用（移动时不变化）o举例:复制/移动/插入/删除单元格对公式，.的影响、_。。.第2章公式、常用函数与地址引用•数组公式:-数组的概念:对一组数据进行运算，结果也是一组数据•注意:-使用“Ctrl+Shift+Enter”组合键代替“Enter”才能正确显不结果-运算结果不能单独改变举例第2章公式、常用函数与地址引用二、函数函数可以完成特殊功能或操作,使用函数可以简化和缩短公式,甚至可以完成公式不能完成的功能或操作。（-）函数格式、输入与嵌套25•函数格式:函数名（自变量1,自变量2,......）•*一个函数的自变量可以有0—30个第2章公式、常用函数与地址引用•函数输入(调用):-插入/函数-按钮fx-或shift+f3•函数嵌套:将某一函数作为另一函数的参数调用。最多可以嵌套七层。•例:=if(average(a2:a6)>50，sum(b2:b6)，0)第2章公式、常用函数与地址引用-函数嵌套:将某一函数，作为另一函数的参数调用。最多可以嵌套七层。•例:=if(average(a2:a6)〉50，sum(b2:b6)，0)第2章公式、常用函数与地址引用（二）常用函数1.求和函数SUM（参数1,参数2,...）功能:求一系列数据的累加和。举例2.算术平均值函数AVERAGE（参数1,参数2,...）。功能:求一系列数据的算术平均值。3.修剪平均值TRIMMEAN（数据区，百分比）•先从数据系列的头部和尾部除去一定百分比的数据点，然后再求平均值。•比如，我们在计算选手平均分数中常用去掉一个最高分，去掉一个最低分，xx号选手的最后得分，就可以使用该函数来计算。4.最大值函数MAX（参数1,参数2,...）。功能:求一系列数据的最大值。5.最小值函数MIN（参数1,参数2,...）功能:求一系列数据的最大值。6.求数据集中第K个最大值,ARGE（array，k）与第K个最小值SMALL（数据区域，k）第2章公式、常用函数与地址引用•7.统计个数函数COUNT、COUNTA.COUNTBLANK•(1)COUNT(参数1，参数2，...)•功能：求一系列数据中数值型数据的个数。•(2)COUNTA(参数1，参数2,...)•功能：求“非空”单元格的个数。•(3)COUNTBLANK(参数1，参数2,...)•功能：求“空”单元格的个数。......第2章公式、常用函数与地址引用\19518.U!舍五入函数ROUND(数值型参数,N)。•功能:按指定的位数对数值进行四舍五入。•ROUND(number,num_digits)-Number需要进行四舍五入的数字。-Num.digits指定的位数,按此位数进行四舍五入。•说明-如果num_digits大于0,则四舍五入到指定的小数位。-如果num_digits等于0,则四舍五入到最接近的整数。-如果num_digits小于0,则在小数点左侧进行四舍五入。第2章公式、常用函数与地址引用9.条件函数IF(逻辑表达式，表达式1，表达式2)•功能:执g真假值判断，根据逻辑计算的真假值，返回不向结果。•IF(logical_test，value_ifLtrue，value_ifLfalse)10.条件求知函数SUMIF(条件数i[Z,“条件”，步知姑捉、•功能/根据指定条件对若干单元格求和。•SUMIF(range,criteria,sum_range)-Range为用于条件判0^的单元格区域。-Criteria为确定哪些单元格将被相加求和的条件。-Sum_range是需要求和的实际单元格。第2章公式、常用函数与地址引用11.条件计数COUNTIF(条件数据区，“条件”)O•功能:计算区域中满足给定条件的单元格的个数。•COUNTIF(range,criteria)-Range为需要计算其中满足条件的单元格数目的单元格区域。-Criteria为确定哪些单元格将被计算在内的条件。•AVERAGEIF函数•返回某个区域内满足给定条件的所有单元格的平均值(算术平均值)。•语法•AVERAGEIF(range,criteria,average_range)错误值错误值错误值出现的原因举例说明#DIV/O!被除数为0=3/0#N/A引用了无法使用的数值#NAME?不能识别的名字=SUN(A1:A4)#NULL!交集为空=SUM(A1:A3B1:B3)#NUM!数据类型不正确=SQRT(-4)#REF!引用无效单元格引用单元格被删除#VALUE!不正确的参数或运算符-4+”a”########免度不够，加宽即可第3章工作簿与工作表•一、选定/移动/复制工作表-选定工作表-在工作簿内“移动/复制”工作表-不同的工作簿之间“移动/复制”工作表-插入/删除/重新命名工作表第3章工作簿与工作表•二、保护工作表•目的:-不允许对工作表中的某些或全部单元格进行修改操作；-根据需要选择是否允许插入行/列、删除行/列等操作。•即可以对整个工作表进行保护，也可以只保护指定的单元格区域。保护工作表f'x'0保护工作表及技定的单元格内容(Q)取消工作衷保护时使用的$码(E):允许此工作表的所有用户进行(0)$选定未拽定的单元格勹设S弟元格格式□设置列格式[□设3行格式j摘入列□摘入行口插入超H接3瞧列I破定II取消I人、、、、、、、、、、、、、、、、、、、第3章工作簿与工作表•1.保护工作表的条件•条件：-被保护的单元格区域处在“锁定”状态；-执行工具菜单=>保护=>保护工作表。-默认情况下,工作表中的每个单元格都处于“锁定”状态；k、、、、YSS、、、、Y\、VW\、、、、、、VV\、、、、、、、、、VVV\、、、、、、、、、、、、、、、、、\\YS、、、、、、、、、、、、、、、、、、、、VWVVV\、、、、、、、、、、、U、\^X、、、、、、YS、、、、、、、、、、、、、、、、、、、、、、、VV\、、、、、、、、VvVWi第3章工作簿与工作表•2.保护工作表的步骤①选定修改的单元格区域,右键=＞设置单元格格式3保护3取消“锁定”；②选定修改的单元格区域,右键=＞设置单元格格式=＞保护=＞选中“锁定”（单元格缺省是锁定的）；③审核3保护工作表。④或开始一格式下拉-----=＞保护工作表第3章工作簿与工作表•3.撤销工作表的保护•将要撤销的工作表变成当前工作表；•单击工具菜单=>保护=>撤销工作表保护;如果保护工作表时设置了密码,必须输入密码后才可以撤销对工作表的保护。第3章工作簿与工作表•4.隐藏工作表•隐藏工作表-开始----格式下拉3隐藏和取消隐藏•取消隐藏第3章工作簿与工作表•5.隐藏公式•隐藏公式后，在编辑栏和单元格均看不到公式，只能在单元格看到公式的计算结果。•隐藏公式:①选定要隐藏的单元格区域a设置单元格格式=>保护3隐藏；②保护保护工作表。•恢复显示公式:撤销工作表保护;第3章工作簿与工作表•三、保护工作簿•目的:-禁止删除、移动、重命名或插入工作表；-也可以禁止执行移动、缩放、隐藏和关闭工作簿窗口等操作。•如果保护工作簿时设置了密码,只有知道密码才能取消保护。第3章工作簿与工作表•五、同时显示一个工作簿的多个工作表-在默认情况下,一个工作簿内的所有工作表在一个窗口中打开,通过单击工作表标签显示不同工作表中的内容。-若希望同时看到一个工作簿内的多个工作表:①视图=>新建窗口；②视图O全部重排或并排查看；第3章工作簿与工作表•六、同时显示多个工作簿的工作表-打开多个工作簿后,同时显示多个工作簿的工作表的操作与上述操作一样,只是放弃选择“当前活动工作簿的窗口”复选框。kUY^^XVWV^sxs^SV^VVvwvv^VvVvvvvVVvVW^V^s'V^wvvSSVWwV^vvWvvVvsV^WvsY^VVVvWVVsV^VWV^VVvVvvvwwvvVVvvVWtfvwvvVVVSvvVVSXVwvv^VsxSXV^VV^^第3章工作簿与工作表•七、拆分窗口•一个工作表窗口可以拆分为“2个窗格”或“4个窗格”，分隔条将窗格分开。•窗口拆分后，能方便地浏览一个工作表的不同部分。•拆分的方法:-鼠标拖曳滚动条上的“拆分条”；-或者，视图=＞拆分；•取消拆分的方法:-双击滚动条上的“拆分条”；-或者，视图=＞取消拆分；•wWV\wvft^、、WvwVV\、\wWY\vW%Wvwv、、、、vk\W^\YWvwVWv<v<W\、、、、、、WgttWKV^VWVvVwVv¥S、、v\、\\、、\W$X、、、、WWVWVSX、、wvV<SSW^\、Ws\,第3章工作簿与工作表•八、冻结窗格•如果工作表过大，在向下或向右滚动显示时会看不到表头，这时可以采用“冻结”行或列的方法，冻结始终要显示的前几行或前几列。•冻结窗格的方法:①选择一个单元格；②视图3冻结窗格。•撤销冻结的方法:-视图=>冻结窗格下拉-----取消冻结窗格第4章格式化工作表•改变数据显示格式:开始一格式下拉一设置单元格格式“常规”格式按钮名称与按钮改变后的数据格式1210.6货币样式”¥1,210.601210.6%“百分比样式”121060%1210.6，“千位分隔样式”1,210.601210.6心‘增加小数位数”1210.6001210.6£“减少小数位数”1211第4格式化工作表、改变数据的显示格式选定要改变显示格式的单元格区域；开始--格式下拉=>设置单兀格格式—---数字,或者右键3设置单元格格式3数字；单元格格式1•在“分类”列表中选择数据格式的类第4章格式化工作表•二、自定义数据的显示格式•格式/单元格/数字/分类框----自定义-0与#的区别:-设置千、百万格式V‘_L

多參、ULLtE、1951第4章格式化工作表三、数据的格式修饰数据的格式修饰-包括改变数据的字体、字型、字号、颜色等。BIU▼方法:-开始----格式下拉=>设置单元格格式格式工具栏的按钮-单元格格式=>字体k、、、V\、、、、、、、、V\、、、、、第4章格式化工作表条件格式•条件格式用于为满足条件的单元格数据设置特定的文字格式、边框和底纹。•选定单元格区域二格式菜单=＞条件格式色阶wxxwswxxxww.Sxwwxxxwxw.wwxxkxwsxxxxwxswswwswxwxwwxwwsx'•色阶作为一种直观的指示,可以帮助了解数据分布和数据变化。双色刻度使用两种颜色的渐变来帮助比较单元格区域。颜色的深浅表示值的高低。•使用双色（或三色）刻度设置所有单元格的格式•选择区域、表或数据透视表中的一个或多个单元格。•开始”“样式”组中,单击“条件格式”旁边的箭头,然后单击“色阶”。•选择双色刻度（或三色）。•使用数据条设置所有单元格的格式•使用图标集设置所有单元格的格式•新建规则•管理规则第4章格式化工作表•五、表格的格式修饰-调整行高/列宽-隐藏行/列/标号•页面布局---工作表选项组---查看-对齐方式•开始---对齐方式组-大标题的合并居中与合并单元格•开始…对齐方式组----合并后居中按钮第5章图表与打印输出•一、图表的类型与组成Excel提供了十几种图表类型,每个图表类型又细分为多个子类型。—柱形图-条形图-折线图-饼图-面积图-XY散点图-圆环图-股价图第5章图表与打印输出•图表的组成•一士图表由以下部分组成:-图表标题-坐标轴与坐标轴标题-图例—绘图区•二、创建图表•插入图表--ALT+F1•设置格式--ctrl+l-创建常用图表-柱形图、折线图、XY散点图、饼图、圆环图-*创建/修饰组合图表第5章图表与打印输出•三、图表编辑与格式修饰-图表编辑-图表的格式修饰-*调整柱形图数据标志间距与应用举例第5章图表与打印输出打印设置-打印页面的基本设置•页面布局-添加“页眉/页脚”•插入/文本组/页眉和页脚•五、打印预览与打印-打印预览•文件/打印/打印预览•视图/工作薄视图:分页预览-打印输出.文件/打印第5章图表与打印输出•打印技巧:-使用分页符-打印区域与重复标题的设置-打印非连续区域•方法1:复制为图片•方法2:使用照相机复制图片-照相机•区别:照相机得到的图片会随原图变化而变化，菜单复制的图片不会改变第6章数据处理与管理•一、数据清单•第一行必须包含每列的标题。数据区域内不应有任何空行或空列。•每列应仅包含一种类型的数据,例如,文本放到一列,而数值放到另一列。记录单”的使用-用“记录单”编辑数据清单一用“记录单”查找数据清单中符合条件的记录第6章数据处理与管理二、筛选与局级筛选-自动筛选与应用举例—局级筛选与应用举例•条件设置一与（同行）、或（错行）•公式作为筛选条件-公式的结果作为条件来筛选记录:-条件区的第一行为空，但是仍然是条件区域的一部分。（由于系统通过公式中的地址引用来确定条件所在的歹ij,因此不需要表头。）-公式中用作“条件”的引用必须用相对地址引用，公式中其他所有的引用都必须用绝对引用。排序•1.排序原则：-数值：按数值大小-字母与符号：0~9空格!”#$%&()*，./:;?_八」｛1｝〜+<=>A~Z-汉字•按拼音字母排序：“大”<“小”•按笔划排序：按笔划多少•按“自定义序列”排序-逻辑值排序原则：FALSE<TRUE第6章数据处理与管理•注意：-无论升序还是降序排列，空白单元格总是排在最后面所有错误值的优先级相同；排序时，隐藏的行/列也会参与排序，最好取消隐藏•2、简单排序•3.自定义序列排序•排序技巧举例：快速制作工资条第6章数据处理与管理•五、分类汇总•分类汇总是指在数据清单中按某一列数据的值对数据清单进行分类后，按不同的类对数据进行统计。•分类汇总时要:-确定数据清单中的一列为分类字段；-分类字段中的同类别的数据在相邻的单元格（预排序）；-确定分类汇总的方式（总和、个数、平均值）；-确定要统计哪些数据列。第6章数据处理与管理•六、数据透视表•数据透视表是对原有的数据清单重组并建立一个统计报表。•数据透视表是一种交互式的、交叉制表的Excel报表,用户对数据进行汇总和分析。•OfficeExcel2007中，“数据透视表和数据透视图”命令被分成以下两个命令:•“数据透视表”、“数据透视图”•“插入”选项卡上的“表”组中的“数据透视表”组内命令第6章数据处理与管理•七、分级显示•分级显示可以隐藏数据表中的若干行/列,只显示指定的行/列数据。•分级显示通常用于隐藏数据表的明细数据行/列,而只显示汇总行/列。•一般情况下,汇总行在明细行的下面,汇总列在明细列的右侧。第6章数据处理与管理•八、合并计算•用三维公式实现合并计算按位置合并计算•按分类合并计算第6章数据处理与管理•九、列表•为了更好地对工作表中的数据区进行独立的管理,可以将每一个数据区创建为一个列表。•一个工作表中可以创建多个列表,一个列表相当于一个独立的数据集,可以对列表进行筛选、添加行和创建数据透视表等操作。•Excel函第7章函数与应用数一共11类,分别是数据库工作函数、日期与时间函数、工程函数、信息函数、财务函数、逻辑函数、统计函数、查找和引用函数、文本函数、数学和三角函数和用户自定义函数。•函数的构成:-函数名:不区分大小写。-参数表:用逗号分隔的参数1,参数2,...,参数N(N<30)构成；，参数可以是常数、单元格地址、单元格区域、单元格区i或的名綠或函邊I。-返回值:根据参数表给出的参数计算得到的结果。第7章函数与应用•函数嵌套•函数嵌套是指一个函数可以作为另一个函数的参数使用。•例如:ROUND(AVERAGE(A2:C2)，0)-AVERAGE的返回值作为ROUND的参数；-Excel函数嵌套最多可嵌套七级。函数的输入•单击/x•插入菜单3函数•快捷键:Shift+F3第7章函数与应用与三:\1951XWWXXWXKWWX'•绝对值函数ABSABS(number)返回数字的绝对值(对值的实数-例子:solValue)numberABS(5.8)=5.8ABS(-4.3)=4.3•取整函数INT需要计算其绝•INT(number)-将数字向下舍入到最接近的整数()-number需要进行向下舍入取整的实数-例子:1NT(8.9)将8.9向下舍入到最接近的整数(8)INT(-8.9)将-8.9向下舍入到最接近的整数(-9)A24NT(A2)返回单元格A2中正实数的小数部分第7章函数与应用•求余函数MOD•MOD(number,divisor)()•返回两数相除的余数,结果的正负号与除数相同,符号不同取互补数。•number被除数•divisor除数•例子,MOD(7,4)=3第7章函数与应用•平方根函数SQRT(number)•返回number的平方根()•例子:SQRT(4)=2•说明:如果参数Number为负值，函数SQRT返回错误值#NUM!第7章函数与应用•随机函数RAND()•返回大于等于0及小于1的均匀分布随机()数,每次计算工作表时都将返回一个新的数值。•若要生成a与b之间的随机实数，请使用:RAND()*(b-a)+a•例子:产生1-20之间的整数:INT(RAND()*20)+lFORECAST(x,known_y’s,known_x’s)已知线型趋势值函数FORECAST^数值为己有的x值和y值,再利用线性回归对新值进行预测()x为需要进行预测的数据点known_y’s为因变量数组或数据区域known_x's为自变量数组或数据区域第7章函数与应用•中位数函数MEDIAN•MEDIAN(numberl,number2,...)•返回给定数值集合的中值()，如果参加统计的数据的个数为偶数()，返回位于中间的两个数的平均值。•例子:MEDIAN(1,5,2)=2MEDIAN(2,1,4,3)=2.5(2和3的平均数)•说明:-参数应为数字，或者是包含数字的名称、数组或引用:-如果数组或引用参数包含文本、逻辑值或空白单元格，则这些值将被忽略:但包含零值的单元格将计算在内。序函数RANKRANK（number,ref,order）返回一个数字在数字列表中的排位。数字的排位是其大小与列表中其他值的比值（如果列表已排过序,则数字的排位就是它当前的位置）。Ref为数字列表数组或对数字列表的引用。Ref中的非数值型参数将被忽略。Order为一数字,指明排位的方式。如果order为（）零）或,MicrosoftExcel对数字的排位是基于ref为按照降序排歹I的列表。如果,MicrosoftExcel对数字的排位是基于ref为按照升序排列的列表。函数RANK对重复数的排位相同。但重复数的存在将影响后续数值的排位。例如,在一列按升序排列的整数中,如果整数10出现两次,|其排位为财11的排位为7、、、（没有排位为第7章函数与应用•众数函数MODE众•数(Mode):一组中出现最频繁的数或数列。•MODE(numberl,number2,...)•返回在某一数组或数据区域中出现频率最多的数值•如果数据集合中不含有重复的数据，则MODE数返回错误值#N/A•例子:MODE(2,5,6,5,8,6,11,6,8)=6MODE(2,6,5,6,2,6,5,5)=6第7章函数与应用•众数、中位数和平均数:•在一组数值中，众数（）是出现频率最高的数值，而中位数（）是位于中间的.值，平均数（）是平均后的值，所有这些求中函数都不能单独地完全描绘所有数据。•假设数据分布在三个区域中,其中一半分布在一个较小数值区中,另外一半分布在两个较大数值区中。函数AVERAGE和函数MEDIAN可能会返回位于数据点稀疏处的中间值；而函数MODE则会返回位于数据点密集处的较小值。第7章函数与应用•FREQUENCY(data_array,bins_array)•频数函数FREQUENCY以一列垂直数组返回某个区域中数据的频率()分布•data.array为一数组或对一组数值的引用，用来计算频率。•bins_array为间隔的数组或对间隔的引用，该间隔用于奸data_array中的薮值进行分组。•bins^array要求从小到大存放，每个数据表示一个分段点，统计的结果包含该数据以及小于该数据的个数•计算结果是一个数组，用“Ctrl+Shift+Enter”输入•例子:统计“职工情况表”不同年龄段的人数。第7章函数与应用•标准差函数STDEV•标准差(StandardDeviation):反映一组数据于数据的平均值的离散程度。标准差函数STDEV(参数1,参数2,...)功能:返回自变量表列中数据的标准偏差。自变量为数值型。——标准差反映数据相对于均值的离散程度。第7章函数与应用方差:（加平方是为了消除+_号的影响）标准差:（便于与平均值对比）•如果标准差比较小,说明数据于平均值的离散程度就比较小,平均值能够反映数据的均值,具有统计意义。•如果标准差比较大,说明数据与平均值的离散程度就比较大（数据中含有非常大或非常小的数）,平均值在一定程度上失去数据“均值”的意义。.•逻辑函数第7章函数与应用•逻辑与函数AND•AND(logicall,logical2,...)•所有参数的逻辑值为真时，返回TRUE；只要一个参数的逻辑值为假，即返回FALSE。•说明:-参数必须是逻辑值TRUE或FALSE,或者包含逻辑值的数组或引用。-如果数组或引用参数中包含文本或空白单元格，则这些值将被忽略。-如果指定的单元格区域内包括非逻辑值，则AND将返第7章函数与应用•逻辑或函数OR(logical1,logical,...)•任何一个参数逻辑值为TRUE,即返回TRUE;所有的参数均为FALSE,才返回FALSEo•逻辑非函数NOT•NOT(logical)•函数值取参数的反值。参数值为TRUE则函数值为FALSE；反之，参数值为FALSE则函数值为TRUE。•例子:工资补助表1951rw

算榇准如下T补助1的计算标准是:局级500,处级300,其余人员100。补助2的计算标准是:处级以上且工资低于2000的补助100o在H3输入公式计算“补助1”:=IF(OR(F3=”正局级”，F3=”副局级”)，500，IF(OR(F3=”正处级”，F3=”副处级”)，300，100))在13输入公式计算“补助2”:=IF(AND(NOT(F3=”科员”)，G3<2000)，100,0)或=IF(AND(F3<〉”科员”，G3<2000)，100,0)图5.66逻辑函数的应用举例第7章函数与应用•数据库函数•数据库函数的格式:DatabaseFunction(database,field,criteria)其中:•database是数据清单区•field用于用于指明要统计的列，field可以是:-字段()名所在的单元格地址；-带英文双引号的“字段名”:-字段在数据清单中的位置，“1”表示第1列，等等。•Criteria:条件区，约定与高级筛选一样。第7章函数与应用•条件区:条件区的第一行是字段名或者是数据清单中字段名所在单元格地址的引用,或为空白（公式作为筛选条件）；•从第二行开始设置筛选条件:筛选条件出现在不同的行表示“或”关系，筛选条件出现在相同行表示“与”关系；•在条件中允许出现代表任意一个字符串，“?”代表任意一个字符；第7章函数与应用•求和函数DSUM(database，field,criteria)•功能:对database区的field字段求满足条件区条件的数据的累加和•说明:-DSUM和SUM不同，SUM求任意数据的累加和，DSUM求数据清单中一列数据中满足条件的数据累加和；-DSUM包含了SUMIF的功能。SUMIF只能对给定的一个条件求数据的累加和，而DSUM可以对给定的多个条件求指定的列数据的累加和。第7章函数与应用•其他数据库函数•平均值函数DAVERAGE•最大值函数DMAX•最小值函数DMIN•统计数值型数据的个数DCOUNT-省略field字段,对数据清单区求满足条件的个数•统计个数DCOUNTA-省略field字段,对数据清单区求满足条件的个数第7章函数及应用五、财务函数及应用1.偿还函数PMT(rate,nper,pv,fv,type)舊&第7章函数与应用六、日期函数及其应用100（1）取日期的天数函数DAY（日期参数）功能:用整数（1〜31）返回日期在一个月中的序号。例如:DAY（2008/10/l）=lo•（2）取日期的月份函数MONTH（日期参数）功能:用整数（1〜12）返回日期的月份。例如:MONTH（2008/10/1）=10。(3)取日期的年函数YEAR(日期参数)功能:用位整数返回日期的年份。Ui例如:YEAR(2008/7/l9)=2008o•(4)取当前日期和时间函数NOW()功能:返回当前计算机系统的日期和时间，包拮牟、月、曰相时尚O说明:NOWO函数的值会根据计算机系统的不同的日期和时间自动更新。例如:若输入=NOW()显示2006/7/1915:48:57,按F9键或编辑其他单元格，系统重新计算日期和时间。•(5)取当前日期函数TODAYQ功能:返回当前计算机系统的日期，包括年、月、曰。说明:TODAYO函数的值会根据计算机系统的不同的日期自动更新。•【例1.64】根据图69的“B列”职工的出生日期，计算职工到目前为止的年龄。如果在“职工情况表”中填写“出生日期”，要比填写“年龄”更科学。因为随着年龄的增长，所填写的“年龄”已经失去意义。因此最好填写“出生日期”，再根据“出生日期”计算到目前夫止的年龄。操作是:在C2输入公式=YEAR(TODAY())-YEAR(B2),然后再复制到C3:C8o•【例1.65】统计图69的数据表中1960年以前出生的人数(不含1960年出生的)。操作是:(1)建立条件区:在E7单元格输入公式:=YEAR(B2)<1960。(2)在E4单元格输入公式计算1960年以前出生的人数:=DCOUNT(A1:C8，，E6:E7)图69日期函数应用举例第7章函数与应用七、文本函数及其应用•1.文本函数•(1)字符串长度函数LEN(S)、LENB(S)•功能:返回字符串的长度。•LEN与LENB的区别是:LEN中的一个汉字为1个字符；LENB中的一个汉字为2个字符。•例如:LEN(“计算机CPU”)=6;LENB(“计算机CPU’>9□第7章函数与应用•(2)截取子串函数MID(S,m,n)、MIDB(S,m,n)•功能:返回S串从m位置开始的共n个字符或文字。•MID（”计算机中央处理器CPU”,4,5）=”中央处理器”。•MIDB（”计算机中央处理器CPU”,7,4）=”中央。•MID(“CPU”,2,1)=“P”O第7章函数与应用•（3）截取左子串函数LEFT（S［，n］）•功能:返回S串最左边的n个字符。如果省略n，献Un=l。•LEFT（”计算机中央处理器CPU”，4）=”计算机中”•LEFTB（”计算机中央处理器CPU”，4）=”计算”•（4）截取右子串函数RIGHT（S［,n］）、RIGHTBtS［,n］）•功能:返回S串最右边的n个字符。如果省略n，献认n=l。•RIGHT（”计算机中央处理器CPU”，6）=“处理器CPU”•RIGHTB（”计算机中央处理器CPU”,9）=“处理器CPU”第7章函数与应用•(6).数值转文本函数TEXT(数值型数据，格式)•功能:按给定的“格式”将“数值型数据”转换成文本型数据。•例如:TEXT(12・345，”$##,##0・00”)=”$12・35”TEXT(37895，”yy-mm-dd”)=03-10-01第7章函数与应用•(7)文本转数值函数VALUE(S)•例如:VALUE(”12.80”)=12.8VALUE(”AB”)=#VALUE!•(8)大小写字母转换函数LOWER(S)、UPPER(S)•LOWER与UEEER都不改变文本中的非字母的字符。•转换为小写字母函数LOWER(S)例如:=LOWER(”aBCdE”)的结果是”abcde”。•转换为大写字母函数UPPER(S)例如:=UPPER(“aBCdE”)的结果是“ABCDE”。HL#,蜘ULLtE、1951八、查找和引用函数及应用第7章函数与应用1.按列查找函数VLOOKUP（查找值，数据区，列标，匹配类型）。•功能:VLOOKUP函数在第二个自变量给定的数据区域的最左列中查找第一个自变量给定的值，然后将第三个自变量指定列值返回到单元中。•匹配的类型:•若省略=true=l,即返回近似值（小于查找值的最大值，数据区域的第一列必须排序）•若为0=false,要求精确匹配值。第8章数据分析六课时本章重点:用假设方法求解线性回归分析规划求解第8章数据分析•_、用假设方法求解•目的：研究当变量在一定范围内变化时，与之相关的的公式的结果。•1.使用“单变量求解”命令求特定解一108通过调整一个单元格中的值,从而求得指定元格中的特定值的方法•*目标单元格要包含公式。第8章数据分析•2.运用模拟运算表作模拟分析-单变量输入“模拟运算表”•对一个变量输入不同的值,模拟它对一个或多个公式影响的结果-双变量输入“模拟运算表”•对两个变量输入不同的值,模拟它对一个公式的影响结果第8章数据分析•二、线性回归分析114•在大量观察数据的基础上,利用数理统计方法建立因变量与自变量之间的回归关系函数表达式（称回归方程式）。•当研究的因果关系只涉及因变量和一个自变量时,叫做一元回归分析；当研究的因果关系涉及因变量和两个或两个以上自变量时,叫做多元回归分析。第8章数据分析•［例1］对销售额进行多元回归分析预测,数据见图。ABCD11销售额（万元〉电视广告费用（万元）报纸广告费用（万元）年份296050151994390020201995495040151996592025251997695030331998794035231999894025422000994030252001第8章数据分析解:本题可用二元线性回归分析来求解:设定变量:¥=销售额，乂1=电视广告费用，X2=报纸广告费用方程为:Y=a1X1+a2X2+b通过线性回归分析确定apa2，b的值，从而确定方程。第8章数据分析1.操作方法与步骤(1)建立数据模型(2)工具一加载宏/“分析工具库”复选框(3)工具一数据分析/“回归”(4)设置“回归”对话框中参数:“Y值输入区”，“X值输入区”，“输出区域”(5)根据结果得出方程第8章数据分析•三、规划求解-“规划”是数学概念,它是指运用微积分和线性代数的方法,在满足一组约束条件的情况下,求出一个多变量函数极值的模型。数学规划是运筹学的一个分支,主要包括线性规划、非线性规划、动态规划和整数规划等。-规划求解可求得工作表上某个单元格（目标单元格）中公式的最优值。（对直接或间接与目标单元格中公式相关联的一组单元格中的数值进行调整,最终在目标单元格公式中求得期望的结果）第8章数据分析•意义:规划求解用于在生产或工作中的一些问题或项目在受多个因素的制约的前提下如何获得最佳的结果。如获得最大的利润、最小的成本等。____•注意事项:-在创建模型过程中，对“规划求解”模型中的可变单元格数值应用约束条件，而且约束条件可以引用其他影响目标单元格公式的单元格。-添加规划求解命令:工具/加载宏/规划求解第8章数据分析•1、求解线性规划问题118-例：•工厂生产3种玩具：狗、娃娃、大熊猫;・3种玩具的净利润（元）：90、75.50•3种玩具各用机工（小时）：3、4、5•3种玩具各用手工（小时）：4、3、2第8章数据分析•工厂极限生产能力:机工:＜=400h手工:〈二280h•要求:a.生产玩具狗的数量〈=50件生产玩具大熊猫数量〉=32件•求满足以上条件时,如何安排各玩具的生产数量以获得最大利润。第8章数据分析1.建立数据表如图:2.进行规划求解在规划求解参数设置中输入:目标单元格地址可变单元格地址约束条件（如有多个逐一添加）3.单击求解结果:如生产20只玩具狗、45个芭比娃娃、32只大熊猫，可以得到最大利润;第8章数据分析求解的结果可形成:报告告果报报结性值算感限运敏极第8章数据分析•*关干规划求解方法的若干选项-在调用规划求解工具时，单击“选项”，可以打开“规划求解选项”对话框:-1）最长运算时间•在此设定求解过程的时间。可输入的最大值为32767（秒），默认值100（秒）可以满足大多数小型规划的求解要求。第8章数据分析-2）迭代次数•在此设定求解过程中迭代运算的次数，限制求解过程的时间。可输入的最大值为32767•默认值100次可满足大多数小型规划的求解要求。-3）精度•在此输入用于控制求解精度的数字，以确定约束条件单元格中的数值是否满足目标值:•上下限。精度值必须表示为小数（0到1之间），输入数字的小数位越多，精度越高。-4）允许误差•在此输入满足整数约束条件并可被接受的目标单元格求解结果与真实的最佳结果间的百分偏差。这个选项只应用于具有整数约束条件的问题。设置的允许误差值越大，求解过程就越快。&第8章数据分析-5）收敛度•在此输入收敛度数值,当最近五次迭代后目标单元格中数值的变化小于”收敛度”框叫置的数值时,椒划求解”停止运行。收敛度只应用于非线性规划求解问题,并且必须表示夕小数（0到1之间）。设置的数值越小,收敛度就越高。例如,0_0叨1表示比001更小的个对差别。收敛度越小,”规划求解”得到结果所需的时间就越长。-6）采用线性模型•当模型中的所有关系都是线性的,并且希望解决线性优化问题时,选中此复选框可加速求解进程。-7）显示迭代结果•如果选中此复选框,每进行一次迭代后都将中断”规划求解”,并显示当前的迭代结果。-8）自动按比例缩放•如果选中此复选框,当输入和输出值量级差别很大时,可自动按比例缩放数值。例如,基于百万美元的投资将利润百__________________________________________________________________________________________________________________第8章数据分析-9）假定非负•如果选中此复选框,则对于在“添加约束”对话框的“约束值”框中没有设置下限的所有可变单元格,假定其下限为零。-10）估计•指定在每个一维搜索中用来得到基本变量初始估计值的逼近方案。•正切函数:使用正切向量线性外推。•二次方程:用二次方程外推法,提高非线性规划问题的计算精度。第8章数据分析-11）导数•指定用于估计目标函数和约束函数偏导数的差分方案。•向前差分:用于大多数约束条件数值变化相对缓慢的问题。•中心差分:用于约束条件变化迅速,特别是接近限定值的问题。虽然此选项要求更多的•计算,但在”规划求解”不能返回有效解时也许会有帮助。第8章数据分析-12）搜索•指定每次的迭代算法,以确定搜索方向。•牛顿法:用牛顿法迭代需要的内存比共钝法多,但所需的迭代次数少。•共轭法:比牛顿法需要的内存少,但要达到指定精度需要较多次的迭代运算。当间题较大和内存有限,或步进迭代进程缓慢时,可用此选项。-13）装入模型•显示”装入模型"对话框,输入对所要加载的模型的引用。-14）保存模型•显示”保存模型”对话框,在其中可指定保存模型的位置。只有需要在工作表上保存多个漠型时,才使用此命令。第一个模型会自动保存。第8章数据分析•2.求解方程组的解XY+Z=O^+2-4=0第8章数据分析移动平均•移动平均法是根据时间序列资料,逐项推移,依次计算移动平均,来反映现象的长期趋势。特别是现象的变量值受周期变动和不规则变动的影响,起伏较大,不能明显地反映现象的变动趋势时,运用移动平均法,消除这些因素的影响,进行动态数据的修匀,以利于进行长期趋势的分析和预测。第8章数据分析•简单移动平均的计算公式:•设xi为时间序列中的某时间点的观测值，其样本数为N;每次移动地求算术平均值所采用的观测值的个数为n(n的取值范围:2<n〈t-l)，则在第t时间点的移动平均值久Af—(x,++•••+X{_n+])=—nn•式中:Mx——第t时间点的移动平均值，•也可当作第t+1时间点的预测值，•即:yi+i,i或yA-i•移动平均分析工具及其公式可以基于d•、的、均、值、'，、溉、第8章数据分析•［例1］某公司1994至2005年销售额数据如图8.30所示。进行三年移动平均,并预测2006年销售额。操作步骤:•建立模型A,1,,：年份变际销ft®2199428031995300419%350'5199?370'6V99S420"71999440一J820004909200148010200250011200?520122004630132005770J8章数据分析27•设置“移动平均”对话框,确定。确定I取消帮助QD|3间隔（S）■|$c$i17囝表输出（C）17标准误差0®移动乎均输出选项输出区域必:新工作表组（P）新工作薄00铺入输入区域（X）:[7标志位于第一行（DI遛a过ai1951•工具4数据分析/“移动平均”第8章数据分析移动平均910三年移动平均误差1112136781357911数据点10005000•结果如图。单元格区域D5:D12即为标准误差。根据公式yi+1=MtW2006年的销售额预测应为M2005,所d^j640。123451FGHIJABCD年份实际销售粕例J28019953001996350I

3401r、例，37038036.9684551998-12041033.66501651999柳45036.968455200049。47029.4392029100148049024.49489742002500150014.1421356200?5201550jr47.95831522004630\640/88.88194422005770V/+实际值预醐第8章数据分析•8.5指数平滑•指数平滑是在移动平均的基础上的进一步扩展。指数平滑法是用过去时间数列值的加权平均数作为趋势值,越靠近当前时间的指标越具有参考价值,因此给予更大的权重,按照这种随时间指数衰减的规律对原始数据进行加权修匀。所以它是加权移动平均法的一种特殊情形。其基本形式是根据本期的实际值Yt和本期的趋势值,分别给以不同权数a和1-a,计算加权平均数作为下期的趋势值。第8章数据