数据库统计函数与数据透视表.ppt

第4章 数据库统计函数与数据透视表,本章内容 4.1 数据库 4.2 数据库统计函数 4.3 数据透视表,4.1 数据库,内部数据库指工作表内具有规范二维结构的数据区域，即通常所指的数据清单。 数据库需要具有规范的结构，对应每一列为数据库的一个字段，而每一行是数据的一条记录，第一行为各自断的字段名或称为列标。 创建数据库首先要设计数据库的结构，设定每列的字段名，然后根据字段类别为每一列的单元格制定格式，最后输入观测数据。,防止输入错误的数据,单击F4单元格，选择数据/有效性命令，出现数据有效性对话框，单击设置选项卡，单击允许后的下拉按钮，选择整数，单击数据的下拉按钮，选择大于，在最小值文本框中输入“0”。,选择数据有效性对话框的出错警告选项卡，单击样式后的下拉按钮，选择停止，在错误信息文本框中输入“数量输入错误，数量应为正整数”，确定。,选择A3：I3单元格，选择数据/记录单命令，出现记录单输入对话框，依次将各项按照表3-1种的数据输入相应的文本框中，输入一项后按Tab键进入下一信息的输入，输入完一个观测值后按新建按钮，继续输入。,采用记录单输入数据,数据的查询与筛选,例：由于客服中心接到客户投诉，售后服务人员需要查询该笔销售单的销售人员和详细销售记录。 （1）打开工作簿3-1，选择数据清单所在A3：I18对应的区域。 （2）单击数据/记录单命令，出现记录单对话框，单击条件按钮，进入Criteria对话框，在销售单号文本框中输入相应的单号，回车。,例：筛选出该月“惠普”电脑所有的销售单，并计算该品牌电脑的总销量。 （1）打开工作簿3-1，选择数据清单所在A3：I18对应的区域。 （2）单击数据/筛选/自动筛选命令，在每个工作表中列表所在的单元格后出现了一个下拉按钮，单击列表“生产商”单元格后的下拉按钮，选择“惠普”。sumif,Excel的筛选命令主要分为自动筛选和高级筛选，自动筛选主要是按选定列所包含的内容筛选，它适用于简单条件下的筛选，高级筛选则可以设置更为复杂的筛选条件，适用于复杂条件下的筛选。,例：筛选 “惠普笔记本”对应的销售单。 （1）取消前面自动筛选的结果。 （2）设定筛选条件区域，单击A20、B20单元格，在编辑栏输入“类别”、“生产商”，单击A21、B21单元格在编辑栏输入“笔记本”、“惠普”。 （3）选择数据清单所在A3：I18对应的区域，单击数据/筛选/高级筛选，出现高级筛选对话框，单击选中将筛选结果复制到其他位置单选按钮，单击条件区域后的折叠按钮，选择A20：B21单元格区域，单击复制到后的折叠按钮，选择A23：I23单元格区域，完成后确定。,筛选条件区域的设置： 条件区域必须具有列标签；在列表之下面的移行中，输入所要匹配的条件；请确保在条件值与区域之间至少留了一个空白行。 如果对于某一列具有两个或多个筛选条件，可直接在各行中从上到下一次输入各个条件。 若要在两列或多列中查找满足单个条件的数据，请在条件区域的同一行中输入所有条件。 若要找到满足一列条件或另一列条件的数据，请在条件区域的不同行中输入条件。 若要找到满足两组条件（每一组条件都包含针对多列的条件）之一的数据行，请在各行中输入条件。,数据分类汇总：Excel的分类汇总功能可自动计算列表中的分类汇总和总计值。当插入自动分类汇总时，Excel将分级显示列表，以便为每个分类汇总显示和隐藏明细数据行。,例：对表3-1中的该月销售数据进行分类汇总： 对比分析当月台式电脑和笔记本电脑的销售业绩；对比分析三种生产厂商的销售业绩；根据月销售清单，确定出本月“金牌销售员”。 （1）选择数据清单所在A4：I18对应的区域，选择数据/排序命令，出现排序对话框，单击 主要关键字后的下拉按钮，选择“类别”，单击选中升序单选按钮，完成后确定。,（2）排序后按“类别”进行分类汇总，选择A3：I18单元格区域，选择数据/分类汇总命令，出现分类汇总对话框，在分类字段下拉列表中选择“类别”，在汇总方式中选择求和，在选定汇总项列表中单击选中数量和总价复选框，单击选中 替换当前分类汇总和汇总结果显示在数据下方复选框，完成后确定。,根据分类汇总的结果，可以画出笔记本电脑和台式电脑在销售数量和销售总金额上的对比图。,（1）单击插入图表按钮，在图表类型对话框中选择饼形/三维饼形，单击下一步按钮。 （2）在源数据对话框的数据区域选项卡中，单击选中系列产生在/列单选按钮，单击数据区域后的折叠按钮，选择Ctrl+F10+F20单元格区域；选择系列选项卡，在名称后的文本框中输入“销售数量对比图”，单击分类标志后的折叠按钮，选择Ctrl+C10+C20，单击下一步。 （3）在图标选项对话框中，直接单击下一步，进入图标位置对话框，单击完成。,自己练习分析“三种厂家的销售业绩”和“根据月销售清单，确定本月金牌销售员”,4.2 数据库统计函数,数据库函数即Dfunctions，具有相同的语法格式：Dfunctions（database，field，criteria）其中， Dfunctions为数据库函数的名称，在Excel中共有12个数据库函数。 database为构成数据清单和数据库的单元格区域，数据库是包含一组相关数据的列表，其中包含相关信息的行为记录，而包含数据的列为字段。列表的第一行包含着每一列的标志项。 field为函数所适用的数据列或称作字段，数据清单中的数据列应位于第一行且具有标志项。Field可以为文本，也可以为数据清单中数据列的位置。Field也可以省略，省略后函数将返回数据清单中所有满足条件的值。 criteria为一组包含给定条件的单元格区域。可以为参数criteria指定任意区域，只要至少包含一个列标志和列标志下方用于设定条件的单元格。,计数函数,与普通的计数函数COUNT和COUNTA类似，数据库计数函数也按是否计入非数字项分为DCOUNT和DCOUNTA两个函数，DCOUNT函数仅计入包含数字单元格且满足条件的记录的个数，而DCOUNTA为满足给定条件下的非空单元格的数目（包括非数字单元格）。 例，（1）确定销售量恰为2台的销售单数；（2）销售量大于2台小于5台的销售单数；（3）销售单中为“个人”客户的“笔记本”电脑的销售单数。,销售量恰为2台的销售单数,（1）设定条件区域，单击A21单元格，输入“数量”，单击A22单元格，输入条件“=2”。 （2）运用DCOUNT函数计数，单击B22单元格，在编辑栏中输入“=DCOUNT（A3：I18，F3，A21：A22）”。 （3）运用DCOUNTA函数计数，单击B23单元格，输入“= DCOUNTA（A3：I18，F3，A21：A22）”。 单元格格式为“常规” 练习做其他两问。,求和、乘积函数,数据库函数的求和函数为DSUM，用来返回数据清单中满足所有指定条件的数字之和。数据函数的乘积函数为DPRODUCT，用来返回数据库的列中满足指定条件的数值的乘积。 例，（1）确定单笔销售总价大于等于20000元以上所有销售单的合计总价款；（2）单笔销售数量大于5台以上的所有销售单数量的乘积。,单笔销售总价大于等于20000元以上所有销售单的合计总价款,（1）设定条件区域，单击A21单元格，输入“总价”，单击A22单元格，输入条件“=20000”。 （2）运用DSUM函数求和，单击B22单元格，在编辑栏中输入“=DSUM（A3：I18，H3，A21：A22）”。 依次类推可以求出单笔销售数量大于5台以上的所有销数单数量的乘积。,最值函数,数据库中的最值函数主要为最大值函数DMAX和最小值函数DMIN，其中DMAX用来返回给定条件的最大数值，DMIN用来返回给定条件的最小数值。 例，（1）确定单价中小于15000元中的最高值对应的销售单单价；（2）总价中大于20000元的最小销售单对应的总价。 大家试试看！,均值、方差、标准差函数,作为统计中常用的描述统计，均值和方差或者标准差是一类经常用到的统计量，在数据库函数中同样给出了求解数据库满足条件的均值函数DAVERAGE、计算样本方差函数DVAR（若为样本总体方差，应采用DVARP函数），和计算样本标准差函数DSTDEV（若为样本总体标准差，应采用DSTDEVP函数）。 例，（1）确定销售单价大于10000元的单笔销售单单价均值；（2）若例中给出的清单数据仅为总销售数据中的一个样本，试估算单笔总价大于25000元的观测值样本的方差和标准差。 大家试试看！,其他数据库函数,除了上面介绍的4大类主要的数据库函数外，数据库函数中还有一个DGET函数，DGET函数为从数据清单或列中提取符合指定条件的单个值。 例，确定销售总价中介于20000元到25000元的销售单的总价。 （如果没有满足条件的记录，则函数DGET将返回错误值#VALUE！。如果有多个记录满足条件，则函数DGET将返回错误值#NUM！。） 大家试试看！,4.3 数据透视表,Excel仅能提供一个由行和列组成的二元的数据结构，当需要在统计上反映多维的问题时需要用到数据透视表功能，在有限的二维数据平面里表达三维的概念。 一、数据透视表的创建 例，创建数据透视表和数据透视图，分析两类客户中销售员对不同生产厂家电脑的销售总金额。,（1）单击数据/数据透视表和数据透视图命令，出现数据透视表和数据透视图向导对话框，单击选中请制定待分析数据的数据源类型/Microsoft Office Excel数据列表或数据库单选按钮，选中所需创建的报表类型/数据透视图（及数据透视表）单选按钮，下一步。 （2）单击选定区域后的折叠按钮，选择A3：I18对应的单元格区域，下一步。 （3）单击数据透视表显示位置/现有工作表单选按钮，单击现有工作表后的折叠按钮，选择A21单元格。,（4）单击布局按钮，出现布局对话框。在对话框的右侧为数据清单中的各列，中间位置为数据清单的布局设置，设计布局时单击右侧列标兵拖动之相应位置即可。单击客户类型并拖动至“页”区域，单击总价并拖动至“数据”区域，单击生产商并拖动至“行”区域，单击销售员并拖动至“列”区域，若想更改对应区域的设置，可在单击将其拖动出相应的区域后重新设定需要显示的项即可，确定。 （5）单击选项按钮，出现数据透视表选项对话框，根据需要改变相应的选项设置。最后确定。 数据透视表和数据透视图可以快速构建基于数据库的统计分析报表和图形，并根据不同的目的而选择不同的显示内容，实现在有限的二维空间中，通过页字段、行字段、列字段和数据字段的选择实现不同的统计分析和统计绘图。,二、数据透视表的编辑 数据透视表的编辑主要分为两大类：一类是在数据透视表中更改或添加页字段、行字段、列字段和数据字段；另一类是改变数据透视表原有的汇总方式，选择数据字段汇总平均值、最值甚至标准差。 例，（1）在行字段中添加型号，分析对应销售员销售不同型号的数量；（2）在行字段中添加类别，且数据字段不再为订单总金额，而是分析总订单的平均金额。,在行字段中添加型号，分析对应销售员销售不同型号的数量 （1）首先在行字段中添加型号项，在数据透视表字段列表对话框中，单击型号，从添加到后的下拉列表框中选择分类轴选项，单击添加到按钮，完成“型号”添加。 （2）将数据字段由表示销售总金额的“总价”改为“数量”，单击数据透视图左上角中“求和项：总价”区域，按Delete删除，