Excel在统计工作中的应用技巧

1、Excel在统计工作中的应用技巧,2017年11月,岗位知识培训,Excel是统计工作以及日常工作的基本工具，根据培训要求和工作实际，我着重介绍Excel在数据搜集、录入、整理、汇总、展示等过程中的应用技巧，同时再简要介绍有关常用统计公式与函数的使用技巧。,一、用Excel搜集与整理数据,用Excel搜集数据 用Excel录入数据 用Excel整理数据 用Excel作统计图,（一）用Excel搜集数据,搜集数据的方法有多种，可以采用统计报 表、典型调查、重点调查或抽样调查。 针对抽样调查，Excel的数据分析工具中提 供了一个专门的“抽样”工具，可以帮助使 用者快速完成抽样工作。,使用Exce

2、l进行抽样，首先要对各个总 体单位进行编号，编号可以按随机原则， 也可以按有关标志。,例 ：假定有100名消费者，从中选取15人进行工资收入调查。 首先，将100名消费者编号为1100号，利用填充功能输入编号。,向右填充：,第一步：单击“数据”菜单，选择“数据分析”选项,说明：若无数据分析选项，可在“文件”菜单下选择选项加载项转到，在弹出的对话框中选择分析工具库，便可出现数据分析选项。OFFICE20102013版本均可以。,其次，利用“抽样”工具进行抽样的具体操作如下：,第二步：打开“数据分析”对话框，从其对话框的“分析工具”列表中选择“抽样”选项,第三步：打开“抽样”对话框，确定输入区域、

3、抽样方法和输出区域,1确定输入区域 只能对于数值型数据进行抽样，文本形式数据无效。 2选定抽样方法 在“抽样方法”框中，有“周期”和“随机”两种抽样模式。 （1）“周期”模式即所谓的等距抽样。此种抽样方法，需要确定周期间隔，周期间隔是由总体单位数除以要抽取的样本数而求得的整数。若要在100个总体单位中抽取5个样本，则在“间隔”框中输入20。 （2）“随机”模式适用于简单随机抽样等。,3指定输出方向 在“输出选项”框中有三种输出去向。在“输出区域”框中输入存放样本单位的起始单元格编号。在本例中，输入区域为$A$18。也可以通过选择“新工作表”或“新工作簿”将抽样结果放在新工作表或新工作薄中。,第

4、四步：单击“确定”按钮后，在指定的位置给出抽样的结果。,（二）用Excel录入数据,1、输入日期、时间 在单元格中插入当前日期： Ctrl + ; 在单元格中插入当前时间：Ctrl + Shift + ;,（1）输入负数 在单元格中输入负数时，可在负数前输入“-”作标识，也可将数字置在（）括号内来标识。,2、输入数字,（2）输入分数 Excel中日期的输入方法也是用斜杠来区分年月日的，为了避免将输入的分数与日期混淆，在单元格中输入分数时，要在分数前输入“0”（零）以示区别，并且在“0”和分子之间要有一个空格隔开。 这种方式存储的分数可以作为数值参与运算，而 1/2 作为文本存储的分数不能参与运

5、算。,例： 输入“1/2”，按回车键则显示“1月2日”； 输入“0 1/2”，按回车键则显示“1/2”，编辑栏里显示0.5； 输入“8 1/2”，按回车键则单元格显示“8 1/2”，而在编辑栏中显示“8.5”。,（3）输入小数 当需要输入大量带有固定小数位的数字或带有固定位数的以“0”字符串结尾的数字时 正数表示小数点后的位数，负数表示小数点前零的个数。,正数表示小数点后的位数，负数表示小数点前零的个数。 如果要在输入“88”后显示“0.088”，要在“位数”微调框中输入“3”。 如果要在输入“88”后显示88000”，就在“位数”微调框中输入“-3”。 注意：在完成输入带有小数位或结尾零字符

6、串的数字后，应清除对“自动设置小数点”符选框的选定，以免影响后续的输入。,（4）文本类型数字输入 组织机构代码、电话号码、行业代码等需要将数字当成文本输入。常用的录入的方式有三种: 1）、单元格格式的设置,2）、在输入第一个字符前，键入单引号“ ”； 例：键入027，单元格中显示027 3）、先键入等号“= “ ” 。 例：键入=001，单元格中显示001,编辑自定义填充序列 解决日常工作中，一些无法通过常规方法实现快速填充的序列。 例如生产经营活动一、生产经营活动二、生产经营活动三.等。,（5）填充功能,“excel选项” “高级” “常规” “创建用于排序和填充序列的列表” “编辑自定义列

7、表”,输入完成后单击“添加”“确定”,（三）用Excel整理数据,在统计工作中，我们从企业或个体户取得了大量的基层调查数据。这些数据是杂乱的，没有规则的，因此，必须对搜集到的大量的原始数据加工整理。 Excel提供了多种数据整理工具：排序、筛选、分组、分类汇总、数据透视表、直方图等。,1、数据有效性 数据有效性用来定义在单元格中可以输入或应该在单元格中输入哪些数据。 通过配置数据有效性，可以防止用户输入无效数据，或者圈释无效数据。,例：设置工业个体户从业人员人数的有效性规则为：020人。,设置数据有效性的方法：,（1）在未输入数据前，可以这样做： “数据” “数据工具” “数据有效性”,（2）

8、在未输入数据前，还可以这样做：,在来源框中输入“许昌市,洛阳市”，选项时间用西文 的逗号（,）隔开,（3）在输入数据后，可以这样做：,2、条件格式,主要用于查找符合某些条件的数据，对满足条件的单元格，可以通过字体或者颜色上面的变化等，做出一些醒目的标记。 也常用于分段显示某些数据，如不同工资水准、成绩。,（1）突出显示单元格,“开始” “样式” “条件格式” “突出显示单元格规则”,例：快速检查是否有重复的个体户。,（2）分段显示数据,“开始” “样式” “条件格式” “色阶”,例：使用色阶，分段显示单元格，实现主营业务收入的可视化效果。,（3）清除条件格式规则,（四）用Excel作统计图,E

9、xcel提供了大量的统计图形供用户根据需要选择使用。Excel提供的图形工具有：柱形图、折线图、饼图、散点图、面积图、环形图、雷达图等。 各种图的作法大同小异，下面以两轴线柱图为例，介绍制作统计图的工作步骤。,STEP 1 选中数据，点击“插入”“柱形图；,STEP 2 在需要更改的系列的图例上点击右键，选择“设置数据系列格式”；,STEP 3 在“系统选项”“系列绘制在”中，选择“次坐标轴”，然后单击“关闭”,STEP 4在欲改为折线的系列的柱形上单击右键，选择“更改系列图表类型”；,STEP 5 在“更改图表类型”中，选择“折线图”；,STEP 6 添加“数据标签”等进行完善和美化。,二、

10、EXCEL打印设置,在我们实际工作中，如何把收集、整理、汇总和分析所得到的EXCEL表格数据进行标准化、规范化的打印？大家应该使用得比较多，下面主要介绍三个技巧： 技巧一：设置打印标题 假如打印内容较多，需要打印多页，通过设置顶端标题行，实现在每一页上打印列标签；通过设置左端标题列，实现在每一页上打印行标签。 “页面布局” 打印标题” “页面设置”,技巧二：插入页码 “插入” “文本” “页眉和页脚”,返回普通视图，可在“视图”选项卡上的“工作簿视图”组中单击“普通”,技巧三：不打印错误值,三、统计公式与函数,Excel作为数据处理软件，要达到提升数据综合分析能力、实现高效统计的目标，还必须全

11、面、熟练掌握和应用统计公式与函数。 1.在单元格中输入含等号的运算式，EXCEL中称之为公式，都是数学里面的基本运算。 2.EXCEL中提供了丰富的函数，分为财务函数、日期与时间函数、数学与三角函数、统计函数、查找引用函数、数据库函数、文本函数、逻辑函数等几大类。 说明：由于大部分函数专业性强，本次岗位知识培训主要介绍几个在统计工作中常用的函数及与使用公式函数相关的知识点，其他可根据自己的需要自行学习。,1.数据的保护 1)对文件的保护 第一步，创建完成一个EXCEL文档后，点击“文件”菜单的子菜单“另存为”，如下图：,第二步，再点击“工具”菜单下拉栏中的“常规选项” 这时输入文件共享所需要的

12、打开权限密码就可实现对文档的保护。,（二）常用函数的应用,1.常用统计函数 求数组或单元格数字的算术平均值函数AVERAGE 求数据集最大值函数MAX与最小值函数MIN 求数据集中第K个最大值LARGE与第k个最小值SMALL 求数据集中的中位数函数MEDIAN 求数据集中出现频率最多的数（众数）函数MODE,3.VLOOKUP函数（字段匹配函数） 强大的vlookup函数是做统计分析中最常用的函数之一，因为很多字段信息都是分布在各个不同的表里面，原数据表可能没有我们需要的字段，而需要从其他数据表中获取相关的字段信息，这时候就是发挥vlookup函数作用的时候了。,特别说明： 一是括号里有四个

13、参数，是必需的。最后一个参数range_lookup是个逻辑值，我们常常输入一个0字，或者False;其实也可以输入一个1字，或者true。 二是Lookup_value是一个很重要的参数，它可以是数值、文字字符串、或参照地址。参照地址的单元格格式类别与去搜寻的单元格格式的类别要一致。 三是在使用该函数时，lookup_value的值必须在table_array中处于第一列。 四是存在无数多列数据时，则第三个参数可使用函数COLUMN函数，即COLUMN(目标值所在列)，注意判断是否需+1或-1。列数较小时，也可直接输入数值。 五是VLOOKUP中的V参数表示垂直方向。还有一个横向查找函数HL

14、OOKUP，和VLOOKUP函数属于一类函数，HLOOKUP是按行查找的，VLOOKUP是按列查找的，使用方法基本一致。,COUNT函数只是最基本的统计分析函数，是其他计数类函数的基础。下面给大家介绍几个CONUT函数家族的其他几个成员。 COUNTA函数。 COUNTA函数计算区域中不为空的单元格的个数。 COUNTBLANK函数。计算指定单元格区域中空白单元格的个数。 COUNTIF函数。用法一：利用COUNTIF函数识别重复数据。用法二：按照条件格式计数。,6.字段分列与合并函数 在数据分析的过程中，很多时候我们都需要进行数据抽取，即保留原数据表中某些字段的部分信息，组成一个新的字段。可以是截取某一个字段的部分信息字段分列；也可以是将某几个字段合并成为一个