Excel基础知识.ppt

第1章 统计基础与数据描述,excel数据处理 公式与函数的操作 分类汇总与数据透视表,在一行或一列中产生等差或等比数列 向单元格输入第一个数据。 选择“编辑”“填充”“序列”命令。,填充数据,导入数据,选择“文件”“打开”命令。 在“打开”对话框中的“文件类型”中选择“文本文件”。,单击“打开”按纽，弹出对话框，选中“分隔符号”单选按钮，在“导入起始行”中输入“1”，其它选项不变。,单击“下一步”按纽，弹出对话框，在“分隔符号”中选中“tab键”和“空格”复选框，其它选项不变。,单击“下一步”按纽，弹出对话框，保持系统默认的设置，单击“完成”即可。 。,工作表窗口的拆分和冻结,（1）工作表拆分 选择某一个单元格，作为拆分的分割位置。 选择“窗口拆分”命令。 （2）工作表冻结 选择某一个单元格，作为冻结的分割点。 选择“窗口冻结窗口”命令。,1公式的创建 （1）选择要建立公式的单元格。 （2）键入“=”。 （3）输入公式的内容，比如“=sum(b4:g4)”。 （4）为了完成公式的编辑，确认输入的公式，可按enter键或用鼠标单击编辑栏左侧的“”按钮；如果要取消编辑的公式，则可单击编辑栏的“”按钮。,公式、函数,几个输入公式的简单实例： 200*35 常量运算 g2*120 使用单元格地址（变量） =sum(b4:g4) 使用求和函数,表1-1 算术运算符,表1-2 比较运算符,3公式编辑 编辑公式时如果有问题，excel会出现错误信息，常见的错误信息有以下几种： （1）#(宽度不够) （2）#div/0！(除数为0) （3）#value！(参数或操作数不对) （4）#name?(公式中有无法识别的名称或函数) （5）#n/a （引用了没有可用数值的单元格） （6）#ref! （引用了无效的单元格） （7）#num! （数字有问题） （8）#null!（指定的两个区域没有交集）,选定单元格e3,输入公式“b3+c3+d3”,公式复制的常用操作 利用单元格填充句柄： 在某单元格内输入公式； 鼠标指向该单元格的填充句柄，并向下拖动鼠标即可。 利用单元格复制和粘贴： 在某单元格内输入公式； 复制该单元格，向其他单元格粘贴即可。,组数据与非组数据,注：当数据量相当大时，需要将非组数据转换为一个频率表。,例 上证180指数2002年至2004年对应的的月度观测值如表所示，试求指数对应的月收益率，其中月收益率（本月指数值上月指数值）上月指数值。,1、新建一空白工作表，单击a1单元格，输入表头“上证180指数”，选择a1：d1单元格，合并，字体选择宋体，字号选择12。,2、单击单元格a2，输入“时间”；单击单元格b2，输入“指数”；单击单元格c2，输入“收益率”。,3、单击单元格a3，输入“20021”，右击，在单元格格式对话框中选择数字选项卡，在分类列表框中选择自定义选项，在类型文本中输入“yyyy-m”,单击确定，使用自动填充。,4、求解每月收益率。,注：对数据进行分组时，应首先判断数据大体所在的区域，找出最大值和最小值，然后合理的确定出对应的分组区间（组距），且区间不能有重叠部分。,运用countif函数将非组数据转化为组数据,说明： countif函数用于计算区域内满足给定条件的单元格个数。 语法： countif（range,criteria），range计算其中满足条件的单元格数目的单元格区域；criteria为确定那些单元格将被计算在内的条件，其形式可以位数字、表达式和文本,1、新建一空白工作表，选择a1：e1单元格，合并，对齐，输入表头“上证180按指数分组”， 。,2、分别在a2，b2，c2，d2，e2单元格设定列名：组号、上证180、累积频率、观测值数目、观测值比例 。,3、设定分组区间，单击单元格b3，在编辑栏中输入“=2400” ，分别在b4 、 b5 、 b6 、 b7 、 b8 、 b9 、 b10、 b11、 b12单元格中输入“2400.0-2500.0” , “2500.0-2600.0” , “2600.0-2700.0”, “2700.0-2800.0”, “2800.0-2900.0”, “2900.0-3000.0” , “3000.0-3100.0”, “3100.0-3200.0”, “3200.0-3300.0” 。,4、运用自动填充功能设定组号 。,5、确定分组中”=2400”的累积频率 。单击c3单元格，选择插入函数命令，选择统计选项。,6、选择选择函数中的countif选项 。,7、在函数参数对话框中，单击range后的折叠按钮，选择b3：b38对应的区域 。,8、在函数参数对话框中，在criteria文本框中输入“=2400” 。见“上证综指”,数据库统计函数与数据透视表,一、数据库的创建,1、新建一工作表，命名为“内部数据库”。,2、设定列标题 分别在a3：g3单元格中输入标题。选中a3g3对应的单元格区域，右击鼠标在弹出的下拉列表中选择“设置单元格格式”对话框，在“数字”选项卡中选择“文本”，并做好相应设置。,3、设置数据清单格式 选中a4c4对应的单元格区域，右击鼠标在弹出的下拉列表中选择“设置单元格格式”对话框，在“数字”选项卡中选择“文本”。,4、设置数量的输入格式 选中d4单元格，右击鼠标在弹出的下拉列表中选择“设置单元格格式”对话框，在“数字”选项卡中选择“数值” 。,5、设置每笔单价及总价,6、采用记录单输入数据，选中a3g3单元格，选择“数据”“记录单” 。,二、数据的筛选,问题：联想笔记本的所有销售单，并计算总销量。,1、打开工作表，将具体数据复制到“sheet”中，表头命名为“数据筛选”。,2、选中单元格a3-g3，选择“数据”“筛选”|”自动筛选”命令。,3、单击单元格d23，在编辑栏中输入”=sum(d4:d18)”。,(1)自动筛选功能,(2)高级筛选功能,问题：“由单位购买戴尔笔记本”对应的 销售单。,1、取消自动筛选。,2、在工作表的其他单元格位置设定筛选条件区域,如a24、b24单元格，在编辑栏中输入“客户类型”和“生产商”。在单元格a25，b25中输入“单位”、“戴尔”。,3、选中a3到g22区域，选择“数据”“筛选”“高级筛选”命令，弹出“高级筛选”，在“方式”选项框下选择“将筛选结果复制到其它位置”选项，单击“条件区域”后的折叠按钮，选择a24b25单元格区域,单击”复制”后的折叠按钮,选择a27-g27单元格,最后单击”确定”按纽。,三、数据的分类汇总,问题：分析三家厂商的销售业绩。,1、打开工作表，将具体数据复制到“sheet3”中，表头命名为“按生产厂商进行分类汇总”。,2、首先按照生产商排序,选择单元格区域a3-g22,选择”数据”|”排序”命令,出现”排序”对话框,单击”主要关键字”后的下拉按纽,选择”生产商”,并选择”升序”方式,单击”确定”按纽。,3、按照”生产商”进行分类汇总,选择单元格区域a3-g22,选择”数据”|”分类汇总”命令,出现对话框,在”分类字段” 下拉列表中选择”生产商”,在汇总方式下拉列表中选择”求和”,在”选定汇总项” 列表中选择”数量”和”总价” 复选框,然后选中”替换当前分类汇总”和”汇总结果显示在数据下方”复选框。,注:单击分类汇总结果表左侧二级目录按钮,四、数据库统计函数,(一)计数函数（dcount dcounta）,问题1：销售量大于6小于10的销售单数。,1、打开工作表，复制到新工作表，表头为“数据库统计函数”。,2、设定条件区域，在单元格a24和b24中均输入“数量”，在单元格c24中输入“计数结果”，在单元格a25和b25中分别输入“6”和“=10”。,3、运用dcount计数函数，单击单元格c25，在编辑栏中输入“ dcount（a3：g22，d3，a24：b25）”。,4、运用dcounta函数，单击单元格c26，在编辑栏中输入“ dcounta（a3：g22，d3，a24：b25）”。,问题2：销售单位中，“单位”购买“戴尔”的销售数量。,1、设定条件区域，在单元格a24和b24中均输入“客户类型”和“生产商”，在单元格c24中输入“计数结果”，在单元格a25和b25中分别输入“单位”和“戴尔”。,2、运用dcounta函数，单击单元格c25，在编辑栏中输入“ dcounta（a3：g22，d3，a24：b25）”。,(二)求和、乘积函数（dsum dproduct）,问题1：单笔销售总价大于等于40000元的所有销售单的合计总价款。,1、打开工作表，复制到新工作表，表头为“数据库求和函数”。,2、先设定条件区域，在单元格a24和b24中均输入“总价”，“求和结果”，在单元格a25中输入“40000” 。,3、运用dsum求和函数，单击单元格b25，在编辑栏中输入“ dsum（a3：g22，f3，a24：a25）”。,问题2：单笔销售数量至少为8台的所有销售单数量乘积。,1、打开工作表，复制到新工作表，表头为“数据库乘积函数”。,2、先设定条件区域，在单元格a24和b24中均输入“数量”，“乘积结果”，在单元格a25中输入“8” 。,3、运用dproduct函数，单击单元格b25，在编辑栏中输入“ dsum（a3：g22，d3，a24：a25）”。,(三)最值函数,最大值函数 dmax 最小值函数 dmin,问题1：单价中小于10000元的最高对应的销售单单价。,1、打开工作表，复制到新工作表，表头为“数据库极值函数”。,2、先设定条件区域，在单元格a24和b24中均输入“单价”，“极值”，在单元格a25中输入“10000” 。,3、运用dmax函数，单击单元格b25，在编辑栏中输入“ dsum（a3：g22，e3，a24：a25）”。,问题2：总价中大于40000元的最小销售单对应的总价。,1、打开工作表，复制到新工作表，表头为“数据库极值函数”。,2、先设定条件区域，在单元格a24和b24中均输入“单价”，“极值”，在单元格a25中输入“40000” 。,3、运用dmin函数，单击单元格b25，在编辑栏中输入“ dsum（a3：g22，f3，a24：a25）”。,四、数据透视表,功能：采用数据透视表“透视”，就可以在二维数据表中表达三维的概念。,(一)创建透视图表（基于现有数据清单）,问题：如何分析两类客户中销售员对不同生产厂家电脑的销售总额。,1、打开工作表，复制到新工作表，表头为“数据库透视表”。,2、 选择“数据”“数据透视表和数据透视图”命令，弹出对话框，在“请指定待分析数据的数据源类型”的选框中选择“microsoft office excel 数据列表或数据库”，在“所需创建的报表类型”的选框中选择“数据透视图”，单击“下一步”按钮。,3、 弹出对话框，单击选择区域的折叠按钮，选择a3：g22单元格区域，返回对话框，然后单击“下一步”按钮。,4、弹出对话框，在“数据透视表显示位置”的选框中选择“现有工作表”，单击折叠按钮，选择单元格a24，返回对话框。,5、 单击“布局”按钮，来设置数据透视表。随即弹出对话框，对话框的右侧为数据清单中的各列，中间位置为数据清单的布局位置，涉及布局时只需单击右侧列标题并拖动至相应位置上即可。 在本例中，单击“客户类型”拖动至“页”区域 ，单击“生产商”拖动至“行”区域，单击“总价”拖动至“数据”区域，单击“销售员”拖动至“列”区域。 如果想更改对应区域的位置，可以再次将该变量拖动出对应的区域后重新设定需要显示的变量即可。然后单击“下一步”按钮。,6、单击对话框中的“选项”按钮，将出现 “数据透视表选项”，对话框，单击“确定”按钮。,7、单击对话框中的“确定”按钮，则在原工作表中将产生对应的数据透视表，并生成一种单独的数据透视图“chart1”。,说明：从输出的数据透视表和数据透视图可以绘制所需的统计报表和图形，并根据不同的需要加以修改，如选择不同的行字段、列字段、页字段，可以实现不同的统计分析和统计图表。,五、编辑数据透视表,数据透视表的编辑主要分为两大类： （1）在数据透视表中更改或添加页字段、行字段、列字段。 （2）改变数据透视表原有的汇总方式，选择数据字段汇总最值和平均值。,例1 在行字段添加”单价”,分析对应销售员销售不同单价笔记本的数量.,1、打开数据透视表，在“数据透视表字段列表”对话框中,单击“单价”按钮,在”添加到”后的下拉列表框中选择