最新Excel在统计工作中的应用技巧课件.ppt

1、2017年11月一、用Excel搜集与整理数据 用用Excel搜集数据搜集数据用用Excel录入数据录入数据用用Excel整理数据整理数据 用用Excel作统计图作统计图（一）用Excel搜集数据 搜集数据的方法有多种，可以采用统计报搜集数据的方法有多种，可以采用统计报表、典型调查、重点调查或抽样调查。表、典型调查、重点调查或抽样调查。针对抽样调查，针对抽样调查，Excel的数据分析工具中提的数据分析工具中提供了一个专门的供了一个专门的“抽样抽样”工具，可以帮助使工具，可以帮助使用者快速完成抽样工作。用者快速完成抽样工作。例例：假定有：假定有100名消费者，从中选取名消费者，从中选取15人人进

2、行工资收入调查进行工资收入调查。首先，将首先，将100名消费者编号为名消费者编号为1100号，利号，利用填充功能输入编号。用填充功能输入编号。向右填充：向右填充：第一步：单击第一步：单击“数据数据”菜单，选择菜单，选择“数据分析数据分析”选项选项第二步：打开“数据分析”对话框，从其对话框的“分析工具”列表中选择“抽样”选项1确定输入区域确定输入区域只能对于只能对于数值型数值型数据进行抽样，文本形式数据无数据进行抽样，文本形式数据无效。效。2选定抽样方法选定抽样方法在在“抽样方法抽样方法”框中，有框中，有“周期周期”和和“随机随机”两种抽样两种抽样模式。模式。（1）“周期周期”模式即所谓的等距抽

3、样。此种抽样方模式即所谓的等距抽样。此种抽样方法，需要确定周期间隔，周期间隔是由总体单位数法，需要确定周期间隔，周期间隔是由总体单位数除以要抽取的样本数而求得的除以要抽取的样本数而求得的整数整数。若要在。若要在100个个总体单位中抽取总体单位中抽取5个样本，则在个样本，则在“间隔间隔”框中输入框中输入20。（2）“随机随机”模式适用于简单随机抽样等。模式适用于简单随机抽样等。3指定输出方向指定输出方向在在“输出选项输出选项”框中有三种输出去向。在框中有三种输出去向。在“输出输出区域区域”框中输入存放样本单位的起始单元格编框中输入存放样本单位的起始单元格编号。在本例中，输入区域为号。在本例中，输

4、入区域为$A$18。也可以通。也可以通过选择过选择“新工作表新工作表”或或“新工作簿新工作簿”将抽样结果放将抽样结果放在新工作表或新工作薄中。在新工作表或新工作薄中。第四步：单击第四步：单击“确定确定”按钮后，在指定的位置给按钮后，在指定的位置给出抽样的结果。出抽样的结果。（二）用Excel录入数据1、输入日期、时间在单元格中插入当前日期：Ctrl+;在单元格中插入当前时间：Ctrl+Shift+;（1）输入负数输入负数 在单元格中输入负数时，可在负数前输入在单元格中输入负数时，可在负数前输入“-”作标作标识，也可将数字置在（）括号内来标识。识，也可将数字置在（）括号内来标识。2 2、输入输入

5、数字数字（2）输入分数 Excel中日期的输入方法也是用斜杠来区分中日期的输入方法也是用斜杠来区分年月日的，为了避免将输入的分数与日期混淆，年月日的，为了避免将输入的分数与日期混淆，在单元格中输入分数时，要在分数前输入在单元格中输入分数时，要在分数前输入“0”（零）以示区别，并且在（零）以示区别，并且在“0”和分子之间要有和分子之间要有一个空格隔开。一个空格隔开。这种方式存储的分数可以作为数值参与运算，这种方式存储的分数可以作为数值参与运算，而而 1/2 作为文本存储的分数不能参与运算。作为文本存储的分数不能参与运算。例：例：输入输入“1/2”，按回车键则显示，按回车键则显示“1月月2日日”；

6、输入输入“0 1/2”，按回车键则显示，按回车键则显示“1/2”，编辑栏里，编辑栏里显示显示0.5；输入输入“8 1/2”，按回车键则单元格显示，按回车键则单元格显示“8 1/2”，而在编辑栏中显示而在编辑栏中显示“8.5”。（3）输入小数 当需要输入大量带有固定小数位的数字或带有当需要输入大量带有固定小数位的数字或带有固定位数的以固定位数的以“0”字符串结尾的数字时字符串结尾的数字时 正数表示小数点后的位数，负数表示小数点前零的个数。正数表示小数点后的位数，负数表示小数点前零的个数。正数表示小数点后的位数，负数表示小数点前正数表示小数点后的位数，负数表示小数点前零的个数。零的个数。如果要在输

7、入如果要在输入“88”后后显示显示“0.088”，要在，要在“位数位数”微调框中输入微调框中输入“3”。如果要在输入如果要在输入“88”后后显示显示88000”，就在，就在“位位数数”微调框中输入微调框中输入“-3”。在完成输入带有小数位或结尾零字符串在完成输入带有小数位或结尾零字符串的数字后，应清除对的数字后，应清除对“自动设置小数点自动设置小数点”符选框符选框的选定，以免影响后续的输入。的选定，以免影响后续的输入。（4）文本类型数字输入组织机构代码、电话号码、行业代码等需要将数组织机构代码、电话号码、行业代码等需要将数字当成文本输入。字当成文本输入。常用的录入的方式有三种常用的录入的方式有

8、三种:1）、单元格格式的设置）、单元格格式的设置2）、）、在输入第一个字符前，键入单引号在输入第一个字符前，键入单引号“”；例：键入例：键入027，单元格中显示，单元格中显示0273）、）、先键入等号先键入等号“=“”。例：键入例：键入=001，单元格中显示，单元格中显示001编辑自定义填充序列编辑自定义填充序列解决日常工作中，一些无法通过常规方法实解决日常工作中，一些无法通过常规方法实现快速填充的序列。现快速填充的序列。例如生产经营活动一、生产经营活动二、生例如生产经营活动一、生产经营活动二、生产经营活动三产经营活动三.等。等。（5）填充功能“excel选项”“高级”“常规”“创建用于排序和

9、填充序列的列表”“编辑自定义列表”输入完成后单击“添加”“确定”（三）用Excel整理数据 在统计工作中，我们从企业或个体户在统计工作中，我们从企业或个体户取得了大量的基层调查数据。取得了大量的基层调查数据。这些数据这些数据是杂乱的，没有规则的，因此，必须对是杂乱的，没有规则的，因此，必须对搜集到的大量的原始数据加工整理。搜集到的大量的原始数据加工整理。Excel提供了多种数据整理工具：提供了多种数据整理工具：排排序、筛选、分组、分类汇总序、筛选、分组、分类汇总、数据透视、数据透视表、直方图表、直方图等等。1 1、数据有效性、数据有效性 数据有效性用来定义在单元格中可以输入或应该在单元格中输入

10、哪些数据。通过配置数据有效性，可以防止用户输入无效数据，或者圈释无效数据。例：设置工业个体户从业人员人数的有效性规则为：020人。设置数据有效性的方法：设置数据有效性的方法：（1）在）在未输入数据前未输入数据前，可以这样做：，可以这样做：“数据数据”“数据工具数据工具”“数据有效性数据有效性”（2）在未输入数据前，还可以这样做：在来源框中输入“许昌市,洛阳市”，选项时间用西文 的逗号（,）隔开（3 3）在）在输入数据后输入数据后，可以这样做：，可以这样做：2 2、条件格式、条件格式 主要用于查找符合某些条件的数据，对主要用于查找符合某些条件的数据，对满足条件的单元格，可以通过字体或者颜色满足条

11、件的单元格，可以通过字体或者颜色上面的变化等，做出一些醒目的标记。上面的变化等，做出一些醒目的标记。也常用于分段显示某些数据，如不同工也常用于分段显示某些数据，如不同工资水准、成绩。资水准、成绩。（1）突出显示单元格“开始”“样式”“条件格式”“突出显示单元格规则”例：例：快速快速检查检查是否是否有有重复的个体户。重复的个体户。（2）分段显示数据“开始”“样式”“条件格式”“色阶”例：使用色阶，分段显示单元格，实现主营业务收入的可视化效果。（3）清除条件格式规则（四）用Excel作统计图 Excel提供了大量的统计图形供用户根据需要提供了大量的统计图形供用户根据需要选择使用。选择使用。Exce

12、l提供的图形工具有：柱形图、提供的图形工具有：柱形图、折线图、饼图、散点图、面积图、环形图、雷折线图、饼图、散点图、面积图、环形图、雷达图等。达图等。各种图的作法大同小异，下面以各种图的作法大同小异，下面以两轴线柱图两轴线柱图为例，介绍制作统计图的工作步骤。为例，介绍制作统计图的工作步骤。STEP 1 选中数据，点击选中数据，点击“插入插入”“柱形图柱形图；STEP 2 在需要更改的系列的图例上在需要更改的系列的图例上点击右键，点击右键，选择选择“设置数据系列格式设置数据系列格式”；STEP 3 在在“系统选项系统选项”“系列绘制在系列绘制在”中，选择中，选择“次坐标轴次坐标轴”，然后单击然后

13、单击“关闭关闭”STEP 4 在在欲改为折线的系列的柱形欲改为折线的系列的柱形上单击右键，上单击右键，选择选择“更改系列图表类型更改系列图表类型”；STEP 5 在在“更改图更改图表表类型类型”中，选择中，选择“折线图折线图”；STEP 6 添加添加“数据标签数据标签”等进行完善和美化。等进行完善和美化。二、EXCEL打印设置技巧一：技巧一：设置打印标题设置打印标题假如打印内容较多，需要打印多页，通过设置顶端标题行，实现在每一页上打印列标签；通过设置左端标题列，实现在每一页上打印行标签。“页面布局”打印标题”“页面设置”技巧二：插入页码技巧二：插入页码“插入插入”“文本文本”“页眉和页脚页眉和

14、页脚”返回普通视图，可在返回普通视图，可在“视图视图”选项卡上的选项卡上的“工作工作簿视图簿视图”组中单击组中单击“普通普通”技巧三：不打印错误值技巧三：不打印错误值三、统计公式与函数 Excel作为数据处理软件，要达到提升数据综合分析能力、实现高效统计的目标，还必须全面、熟练掌握和应用统计公式与函数。1.在单元格中输入含等号的运算式，EXCEL中称之为公式，都是数学里面的基本运算。2.EXCEL中提供了丰富的函数，分为财务函数、日期与时间函数、数学与三角函数、统计函数、查找引用函数、数据库函数、文本函数、逻辑函数等几大类。说明：由于大部分函数专业性强，说明：由于大部分函数专业性强，本次岗位知

15、识培训主本次岗位知识培训主要介绍几个在统计工作中常用的函数及与使用公式函数要介绍几个在统计工作中常用的函数及与使用公式函数相关的知识点，其他可根据自己的需要自行学习。相关的知识点，其他可根据自己的需要自行学习。1.数据的保护数据的保护1)对文件的保护对文件的保护第一步，创建完成一个第一步，创建完成一个EXCEL文档后，点击文档后，点击“文件文件”菜菜单的子菜单单的子菜单“另存为另存为”，如下图：如下图：第二步，再点击第二步，再点击“工具工具”菜单下拉栏中的菜单下拉栏中的“常规选项常规选项”这时输入文件共享所需要的打开权限密码就可实现对这时输入文件共享所需要的打开权限密码就可实现对文档的保护。文

16、档的保护。（二）常用函数的应用1.1.常用统计函数常用统计函数求数组或单元格数字的算术平均值函数求数组或单元格数字的算术平均值函数AVERAGE求数据集最大值函数求数据集最大值函数MAX与最小值函数与最小值函数MIN求数据集中第求数据集中第K个最大值个最大值LARGE与第与第k个最小值个最小值SMALL求数据集中的中位数函数求数据集中的中位数函数MEDIAN求数据集中出现频率最多的数（众数）函数求数据集中出现频率最多的数（众数）函数MODE3.VLOOKUP函数（字段匹配函数）强大的强大的vlookup函数是做统计分析中最常用的函函数是做统计分析中最常用的函数之一，因为很多字段信息都是分布在各

17、个不同数之一，因为很多字段信息都是分布在各个不同的表里面，原数据表可能没有我们需要的字段，的表里面，原数据表可能没有我们需要的字段，而需要从其他数据表中获取相关的字段信息，这而需要从其他数据表中获取相关的字段信息，这时候就是发挥时候就是发挥vlookup函数作用的时候了。函数作用的时候了。特别说明：特别说明：一是一是括号里有四个参数，是必需的。最后一个参括号里有四个参数，是必需的。最后一个参数数range_lookup是个逻辑值，我们常常输入一个是个逻辑值，我们常常输入一个0字，字，或者或者False;其实也可以输入一个其实也可以输入一个1字，或者字，或者true。二是二是Lookup_val

18、ue是一个很重要的参数，它可是一个很重要的参数，它可以是数值、文字字符串、或参照地址。参照地址的单以是数值、文字字符串、或参照地址。参照地址的单元格格式类别与去搜寻的单元格格式的类别要一致元格格式类别与去搜寻的单元格格式的类别要一致。三是三是在使用该函数时，在使用该函数时，lookup_value的值必须在的值必须在table_array中处于第一列。中处于第一列。四是存在无数多列数据时，则第三个参数可使用四是存在无数多列数据时，则第三个参数可使用函数函数COLUMN函数，即函数，即COLUMN(目标值所在列目标值所在列)，注，注意判断是否需意判断是否需+1或或-1。列数较小时，也可直接输入数

19、。列数较小时，也可直接输入数值。值。五是五是VLOOKUP中的V参数表示垂直方向。还有一个横向查找函数HLOOKUP，和VLOOKUP函数属于一类函数，HLOOKUP是按行查找的，VLOOKUP是按列查找的，使用方法基本一致。COUNT函数只是最基本的统计分析函数，是其他计函数只是最基本的统计分析函数，是其他计数类函数的基础。下面给大家介绍几个数类函数的基础。下面给大家介绍几个CONUT函数函数家族的其他几个成员。家族的其他几个成员。COUNTA函数函数。COUNTA函数计算区域中不为空函数计算区域中不为空的单元格的个数。的单元格的个数。COUNTBLANK函数函数。计算指定单元格区域中空白计

20、算指定单元格区域中空白单元格的个数。单元格的个数。COUNTIF函数函数。用法一：利用用法一：利用COUNTIF函数识别函数识别重复数据重复数据。用法二：按照条件格式计数用法二：按照条件格式计数。6.字段分列与合并函数在数据分析的过程中，很多时候我们都需要进行在数据分析的过程中，很多时候我们都需要进行数据抽取，即保留原数据表中某些字段的部分信数据抽取，即保留原数据表中某些字段的部分信息，组成一个新的字段。可以是截取某一个字段息，组成一个新的字段。可以是截取某一个字段的部分信息的部分信息字段分列；也可以是将某几个字字段分列；也可以是将某几个字段合并成为一个新的字段段合并成为一个新的字段字段合并。字段合并。（1）LEFT、RIGHT函数（字段分列函数）函数（字段分列函数）函数作为Excel处理数据的一个最重要手段，功能是十分强大的，甚至可以用Excel来设计复杂的统计管理表格或者小型的数据库系统。因此在生活和工作实践中，除了上面所介绍的几种常见函数之外，其他函数的使用方法可以参考EXCEL帮助及相关的书籍。谢谢大家！谢谢大家！