第四篇 数据处理 与数据分析 马秀麟 2015年11月

Excel的学习要点 编辑 格式设置 输入数值型量（含日期、货币） 输入字符串型量（含数值形态的字符串） 编辑栏的使用 边框与底纹、行高、列宽 自动换行，手工换行，合并居中 数值量的显示格式 条件格式

Excel的学习要点 计算功能 公式的制作 直接书写公式 基于函数的公式

Excel的学习要点 函数与公式 简单统计函数 复杂统计函数 日期函数 条件函数 字符串函数 Sum count (CountA) average max min 复杂统计函数 Sumif, countif averageif 日期函数 Year month day weekday now 条件函数 If 字符串函数 字符串连接运算符 & 字符串转换：text

Excel的学习要点 图形功能 统计图表 简单统计图 统计图的优化

Excel的学习要点 数据库功能 数据库拼合 排序、 筛选、 分类汇总、 频度分析 高级查找 =frequency() 基于子表的查找(标注） countif 子表的填充 vlookup

Excel的学习要点 数据分析基础（概念） 方法 相关性 方差齐性（非齐性） 差异性检验 方差检验 基于函数的操作 基于分析工具库的操作 Correl ftest Ttest 基于分析工具库的操作

第四章 Excel的使用 1、电子表格的概念 电子表格用途： 电子表格的版本 数据表格——强大的计算能力 数据库功能——数据检索与排序能力 图形功能——统计图，按照数据生成图形 电子表格的版本 Lotus 123（美国莲花公司) Excel 国内CCED

第四章 Excel的使用 1、电子表格的概念 基本组成 数据类型 工作簿（文档）——多个页面 工作表——一个页面，有标记 单元格——基本存储单位 区域——多个单元格构成的矩形区域 数据类型 字符型——以字母开头，或不符合数值格式且非公式 张三， 李四， ‘201212345678， 2012-2-31 数值型——以数码开头，且符合数值格式 日期、时间格式应该属于数值型 158，427，32E5, 2011-12-12, 2023/11/12

第四章 Excel的使用 2、Excel的单元格与区域 (1)单元格： (2)区域：H2..D8, HS98:IT77 (3)工作表范围： H8, D7, IV26等，可以包含数字、公式、字符串等。 (2)区域：H2..D8,     HS98:IT77 (3)工作表范围： Excel 2000/2003 : A1:IV65536 256列。 Excel 2007： A1:XFD104,8576 (4)工作表: 更改工作表名称，更改工作表标签颜色 (5)三维表示形式： sheet1!S2 工作表名!列号行号。

第四章 Excel的使用 2、数据输入与保存 (1)输入字符串数据： (2)输入数字 (3)输入公式 (4)数据编辑： 字符对齐方式——默认为左对齐 输入数值形态的字符串——使用前导符号英文但引号’ 长字符串行内换行<Alt>+<Enter> (2)输入数字 数码开头，符合数值习惯（比如日期格式） (3)输入公式 =号开始，或者使用工具栏按钮fx 公式中的字符型量，加英文双引号，例如“女”，人名等 所有控制符号，全部使用英文符号 (4)数据编辑： 直接编辑数据    利用编辑栏编辑数据

第四章 Excel的使用 3、数据格式设置 (1)设置文字格式 (2)设置边框底纹 (3)调整列宽、行高 (4)条件格式 字型、字号、字体、颜色、合并居中 (2)设置边框底纹    （含Excel网格的处理)——【页面布局】中 (3)调整列宽、行高 直接拖动标题中的“边界” (4)条件格式 （对符合条件的文字设置格式） 【开始】功能卡中。

第四章 Excel的使用 (5)设置数据显示格式——重点： (6)手工换行 【开始】－【数字】组－右下小箭头，“数字”选项卡，设置数值型数据：数值格式、日期格式 【开始】－【数字】组－右下小箭头【对齐】字符型数据：居中、左对齐、右对齐 (6)手工换行 <Alt>-<Enter>

第四章 Excel的使用 4、Excel中的计算功能 (1)常见函数及其使用 ①常规统计函数 求和：SUM（区域） 统计个数： COUNT（区域） 【数值型量】 COUNTA（区域） 【全体类型量】 求平均值： Average(区域)        求最大值： MAX（区域）   求最小值： MIN（区域） 条件统计：Countif(数据区域，条件), Sumif(条件区域，条件，求和区域） =countif(c2:c100,”女”) =sumif(c2:c100,”女”,f2:f100)

第四章 Excel的使用 ②条件函数： ③日期函数 IF（条件式，满足时的取值，不满足时的取值） =If(C3=“女”,500,10) =if(c3=“女”,if(f3>=80,1000,500),if(f3>=80,700,400)) 女生80分以上奖励1000，否则奖励500，男生80分以上奖励700，否则奖励400. ③日期函数 Year(日期), Month(日期)， Now() =2010-year(d2) =(now()-d2)/365.25 =N2-N1 两个日期之间相差的天数（N1和N2中都存储日期量） Weekday(日期,2) 求指定日期为星期几？

第四章 Excel的使用 (2)使用公式计算 (3)复制公式 ①直接输入计算公式 ②输入带有函数的计算公式 填充方式复制： 利用剪贴板复制： 拖动填充柄 利用剪贴板复制： 选择单元格－【复制】，选择目标区域－【粘贴】

第四章 Excel的使用 在复制过程中，需要注意三种类型的公式： 相对坐标公式：形式为：列号行号。 绝对坐标公式：形式为：$列号$行号。 特点：公式被复制后将相应变动； =sum(A1:A8) 绝对坐标公式：形式为：$列号$行号。 特点：公式被复制后不会变动； =sum($A$1:A8) 混合坐标公式：形式为：行号和列号中仅有一个维度标记为$。 特点：公式被复制后，没有固定的维度发生变化。 =sum($A1:A$8)

第四章 Excel的使用 5、EXCEL的数据库功能 (1)Excel的数据库及其结构 ①Excel的标题行——字段名行

第四章 Excel的使用 (2)Excel下的数据排序 ①方法： ②注意事项 选定数据库区域，选择【数据】选项卡——【排序】， 再选择排序关键字，最后【确定】 ②注意事项 不要只选定一列排序关键字数据。 只要插入点在数据表区域，自动扩展

第四章 Excel的使用 (3)Excel的筛选功能 (1)自动筛选： 【数据】——【筛选】，启动【自动筛选】功能。  注意“筛选按钮”的使用；输入筛选条件 筛选的类型 针对“固定值”的筛选 符合范围的筛选（数值在一定范围之间） 模糊筛选 “包含”式的筛选 “指定格式”方式的筛选——带有通配符号

第四章 Excel的使用 (2)高级筛选： 设置筛选条件区域 一定包括字段名——建议从数据库字段名行复制字段名。 处于相同行的筛选条件为“与”的关系，处于不同行的筛选条件为“或”的关系。 设置输出区域 如果不另外指定输出区，可不设置输出区域 【数据】—【筛选】组块，单击【高级】，选择数据库区域， 选择条件区域， 选择输出区域，【确定】

第四章 Excel的使用 (4)分类汇总 ①分类汇总的含义：分组，计算（总计，求和，求最大，求最小）。 ②方法： ③注意事项： 选定数据库区域，选择【数据】—【分类汇总】， 再选择“分类字段”，选择“计算方式”，选择“被计算字段”，最后【确定】 ③注意事项： 只有分类字段值有序后才能正确地分类汇总 一定要先根据分类字段排序

第四章 Excel的使用 (5)频度分析 什么是频度分析 频度分析的方法与特点 对于待统计的数据样本，检查在每个分段中的频数。 例如：统计不及格、及格、良好、优秀的人数 频度分析的方法与特点 设定分段数据 n个数把数轴分为n+1段，采用上包含关系(<=) 如果需要实现>=类型的量，需做分段数据转换 集合函数 结果为多个值的集合函数 先选定结果区域，再输入公式，最后<Ctrl>+<Shift>+<Enter>

第四章 Excel的使用 公式的格式 操作实例 选定结果区域，——切切注意 =Frequency(样本区域，分段区域） 按住<Ctrl>+<Shift>，敲击<Enter> 操作实例

第四章 Excel的使用 6、EXCEL的图形功能 (1)作图方法 ③利用功能区的“设计”“布局”对图表属性进行设置 ①选定作图依据的区域 连续区域 非连续区域的选择 ②命令 【插入】【图表】，选择图标类型； 直接插入图表； （图表会按照数据源变化与调整）。 ③利用功能区的“设计”“布局”对图表属性进行设置 注意：（不能用绘图工具制作图表）。

第四章 Excel的使用 (2)图形的存在方式 (3)修改图表的信息 存在方式 改变位置 “设计”功能卡——最右侧“位置” ①独立图形 ①独立图形              ②嵌入式图形 改变位置 “设计”功能卡——最右侧“位置” (3)修改图表的信息 “设计”选项卡 图表类型（折线图、柱型图等） 行列方式 图表样式 “布局”选项卡 （标题、坐标轴、网格线） （图例（位置）、数据标志、数据表）

第五章 子表数据处理 子表数据处理的概念 依据子表实现数据筛选 依据大表数据填充子表 数据的归类

第五章 子表数据处理 countif——用于检查数据是否在某一列中存在 用法 特点 实际应用 Countif(列名:列名,单元格名) 例如： Countif(A:A,B2)——用于检查B2是否在A列存在 特点 本函数可以被复制，用于快速找到属于另一个集合的数据。 实际应用 基于子表，对大表中的记录进行标记； 以便筛选出属于子表的全部记录

第五章 子表数据处理 对于用法2： Lookup函数——用于归类计算 用法： Lookup(单元格，序列区域，取值区域） 例如：lookup(h4,{0,60,80,90},{0,200,500,1000}) Lookup(单元格,序列区域） 注意： 序列范围必须递增 对于用法1： 根据h4落在序列范围内的位置，确定其在取值区的对应值。 若h4在（0~60），则对应于第一个值0，在60-80之间，则对应200。…… 对于用法2： 把取值范围与序列区域合并了，自动取其低值。

第五章 子表数据处理 实际应用 基于大表， 对子表中的某些字段进行填充。 Vlookup函数——用于查找特定数据 用法： 例如：vlookup(b3,$b$3:$h$100,4,true) 注意： 被查找值应在查找区域的首列 返回值的列序号以区域为准。在上例中为e列 查找区域常常使用绝对坐标 实际应用 基于大表， 对子表中的某些字段进行填充。

第六章 Excel的数据分析 1.基本概念 (1)数据描述 集中性描述 离散性（波动性）描述 均值 中位数与众数 离差 样本值与均值的差 方差 总体方差 ——分母为总体个数n,【VARP】 样本方差 ——分母为自由度n-1, 【VAR】 标准差

第六章 Excel的数据分析 分布形态描述 多组数据的关系描述 正态分布 非正态分布 是否存在对应关系 方差齐性描述 配对样本 通常为对同一组被试的多次测量（一一对应地记录下来） 独立样本 不存在对应关系的多个数据集合，可能来自基于某一因素的分组，也可能不是 方差齐性描述 两组数据方差的差异不显著。称为方差齐性 （FTEST） 策略 假设方差为齐性的， 若检验概率》0.05，则假设成立 若检验概率<0.05，则假设不成立

第六章 Excel的数据分析 (2)数据分析技术 关联系分析 数据应满足的条件 类型 多组数据 配对（至少有序） 不相关 相关——相关系数 注意方法：Pearson相关 与 Spearman相关 具有函数关系——回归分析

第六章 Excel的数据分析 差异显著性性分析 概念 用途 定义：判断多组数据之间的差异是否很显著 策略：组间均值差异>>组内波动差异? 用途 对比多组数据之间的差异是否很显著 若多组数据基于某个因素产生，则可用于归因。

第六章 Excel的数据分析 类型 正态数据 2组数据——t检验 多组数据 非正态分布数据 非参数检验 配对样本 独立样本 方差非齐性——检验 多组数据 基于同一因素产生的分组——方差分析 基于不同因素产生的分组——两两组合T检验 非正态分布数据 非参数检验

第六章 Excel的数据分析 2.基本数据分析 标准差——估算标准差（样本标准差） 方差——估算方差（样本方差） 相关性分析： STDEV(区域），返回标准偏差，忽略文本和逻辑值 STDEVA(区域），返回标准偏差，不忽略文本和逻辑值 分母为自由度n-1。 方差——估算方差（样本方差） VAR(区域），计算给定数据的方差，忽略文本和逻辑值差异性分析 相关性分析： =Correl(区域1，区域2) 判断两列数据是否相关，结果为相关性系数。 |相关性系数|>0.4为相关， |相关性系数|>0.8为高度相关 STDEVP和VARP 总体标准差与总体方差 公式中分母为：n.

第六章 Excel的数据分析 3.高级数据分析 方差齐性检验——F检验 作用： 公式 返回方差无明显差异时的双尾概率， 若<0.05，则方差非齐次，即异方差 公式 FTEST（区域1，区域2） 例如：Ftest(A1:A100, B2:B200) 结论：<0.05: 异方差， >0.05：等方差

第六章 Excel的数据分析 两组数据的差异显著性检验——T检验： 作用 公式 解释 返回两列数据均值没有差异的概率值 若<0.05，则两列数据有显著性差异 公式 TTEST(区域1，区域2，单侧/双侧，检验类型） 单侧——1，双侧——2 配对检验——1，独立样本等方差——2，独立异方差——3 解释 单侧/双侧 双侧：两列数据是否在双侧都没有显著性差异，主要用于判定是否相等 单侧：两列数据在单侧是否差异显著，主要用于判断甲列数据是否明显地高于乙列，或者低于乙列。 类型 先判断是否配对样本 对于独立样本，应该先用F检验判定其方差是否齐性

第六章 Excel的数据分析 操作方法： 先确立研究目标是什么？ 确定两组数据的类型 两者有无差异性，是否显著差别，用“双侧” 两者A和B，若判断A>B或A<B，则用“单侧” 确定两组数据的类型 若为配对样本 TTEST的第4个参数直接使用“1” 若为独立样本 用FTEST检查两组数据的方差是否齐性 若结果》0.05, 则检验方式用2；（独立样本等方差） 若结果<0.05, 则检验方式用3；（独立样本异方差）

第六 Excel的数据分析 4. 基于分析工具库的数据分析 分析工具库的概念 启用分析工具库 Excel提供的专业化数据分析工具 利用【文件】—【选项】 【加载项】—【管理加载项】 把“分析工具库”前的复选框设置为有效

第六 Excel的数据分析 利用分析工具库实施数据分析 相关性分析 【数据】—【数据分析】 相关系数

第六 Excel的数据分析 两样本的差异显著性检验（t检验） 首先判断检验的类型 配对样本 独立样本 方法 【数据】—【数据分析】 Ftest函数 得到 等方差 || 异方差 方法 【数据】—【数据分析】 选择具体方法： 平均值的成对2样本分析 ||双样本等方差假设 ||双样本异方差假设独立样本的T检验

第六 Excel的数据分析 多组样本的差异显著性检验（方差分析） 基本条件 方法 作用 整个数据大致满足正态分布 基于某个因素分为多组 多组数据的方差齐性 方法 【数据】——【数据分析】 【方差分析：单因素方差分析】 作用 直接作用：判断多组数据之间是否存在显著性的差异 间接作用：归因，分析单因素是否是影响结论的关键指标

谢谢大家 马秀麟