Excel电子表格 马秀麟

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 基于分析工具库的操作

学习要点 1.Excel是什么？ Excel是一个电子表格系统。 每张工作表是二维表格；行号是数码，列号为字母。 多张工作表组合在一起构成工作簿。 用于数据的简单计算与处理。

学习要点 2.Excel的单元格如何表示？单元格内容？ Excel的单元格地址用“列号行号”表示，例如：A8. 对角线单元格表示： X5:D98 数据类型共2种； 数值型：日期、整数、小数、货币 字符串型

学习要点 3.如何向Excel输入内容？ 先选定输入单元格，然后输入内容。 建议借助顶部的专业编辑栏。 输入数值量 直接输入普通数值。 输入日期量 按照YYYY-MM-DD或YYYY/MM/DD格式输入 英美国家：MM/DD/YY格式 输入数码格式的字符串： 学号、电话号码等 以英文单引号引导。例如：’201411021108

学习要点 4.Excel中如何设置边框与底纹？ 选中要设置边框底纹的区域； 开始—“数字”对话框启动器； 选择“边框” 选择线型，选择外边框 选择线形，选择内边框 选择“底纹” 选择一种底纹形式 “确定”

学习要点 5.Excel中如何设置数值型的显示格式？ 选择要设置显示格式的数据区， 直接在“开始”的“数字”区块中选择。 或者单击“开始”的“数字”区块的对话框启动器，在“数字”选项卡中进行详细设置。 注意：日期格式的设置。 有些格式可能在“英国”区域中。

学习要点 6.Excel的条件格式？ 含义 所谓条件格式，就是把符合特定要求的文字用指定的格式标记出来。例如，不及格的标记为红色。 方法 选择数据区 开始—“条件格式” 指定“特定条件” 指定一种显示格式。

学习要点 7.Excel的顶端标题行？ 含义 大数据量（很多行）的数据表，希望表头打印在每一页的页首。 方法 页面布局——打印标题——“工作表” 在“顶端标题行”中填入作为标题行的区域。

学习要点 8.Excel中改变行高与列宽？ 含义 更好地显示表格，表格更美观。 方法 直接拖动表格顶部列标记中的分隔线； 利用菜单“开始”—“格式”，然后选择“行高”或“列宽”。

学习要点 9、常见的统计计算工作 计算每个学生的总分 计算每个学生的平均分 计算全体学生语文平均分、最高分

学习要点 10.在Excel中如何制作公式？ 说明 利用公式进行自动计算是Excel的核心功能。默认为存储公式，显示计算结果。 具备自动计算功能。 方法 制作公式 以=开头 尽量使用单元格或区域地址（建议使用鼠标单击或拖动） 注意公式的合法性。 错误的函数名 错误的地址或数据类型 所有标点都必须用英文

学习要点 11.如何复制公式？ 方法 利用填充柄 鼠标指向带公式的单元格的填充柄 向右或向下拖动鼠标。 利用复制、粘贴功能。 选择带公式的单元格，<Ctrl>+C 选择目标区域：<Ctrl>+V

学习要点 12.Excel中的函数？ 说明 函数是Excel中提升计算效率的重要工具。 常用函数 统计函数： Sum, count, countA, average, max, min 条件统计函数 Countif, sumif, averageif 日期函数 Now(), Year(日期量)， Month（日期量）、Week（日期量） 字符串函数 Text(数值，格式），Value（字符串）、left(字符串，长度) &

学习要点 13.如何计算年龄？ 含义 在公式中计算年龄是经常发生的事情。 方法 假设，生日在C2:C30，须在D列输出年龄; =(now()-C2)/365 =2014-Year(c2) 计算出第一个人的年龄。 拖动D2的填充柄，填满D2:D30的区域。

学习要点 14.如何使用带条件的统计函数？ 含义 只统计满足一定条件的数据。 统计男生的人数，统计女生的平均成绩等。 方法 =CountIF(条件区域，条件式） =sumif(条件区域，条件式，计算值区域） 例如： =countif(B2:B100,”女”) =averageif(b2:B100,”女”,d2:d100)

学习要点 15.IF的案例问题？ 在数据表中，如果有性别列，现在需要对性别进行数字化编码，男生为1，女生为2，应该如何做？ 如果需要根据学生的平均成绩给予奖金，90分以上（含）发1000，70~80分发600元，60~70分发200，应该如何做？ 基本思路 使用IF函数：=IF(条件式，式子1，式子2） 满足条件时，取式子1的值，不满足条件时取式子2的值。 例如： =if(C2=“女”,2,1) 代表：当c2中为女时，取值2，否则取值1. 方法 =if(D2>=90,1000,IF(d2>=80,600,if(d2>=60,200,0))) =if(D2>=80,if(D2>=90,1000,600),if(d2>=60,200,0))

学习要点 16.数值型学号的转换 数值型学号的转换 =Text(数值型区域，”0000000000000”) 字符串合并 =“w”& Text(数值,“格式”）

学习要点 17.如何计算学生名次，Excel公式复制中的三类单元格？ 问题与必要性 在公式复制中，发现公式中的单元格地址在公式复制到别处后，会自动变化。 在特定情况下，如何固定地址？ =Rank(某个成绩，全体成绩区) 三类地址 相对地址：列号行号 （不固定） 绝对地址：$列号$行号 （全固定） 混合地址：$列号行号， 列号$行号 （半固定）

学习要点 18.Excel中的统计图表？如何在Excel中添加图表？ 含义 依据统计数据，制作出由统计数据控制的图表。 方法 选择要制作图表的数据区域 （此时的数据区域中应包含标题行和标题列信息，以便对图表进行文字说明） 插入—图表 选择“图表类型” 直接完成。

学习要点 19.Excel中的不连续区域构造图表？ 问题 图表所依赖数据区域是不连续的，特别是图表的说明区域可能不与数据处于连续位置。 方法 先以鼠标拖动选定一个区域； 再按住<Ctrl>键，然后拖动鼠标选定其他区域。（只允许选2次） 执行插入图表的命令

学习要点 20.如何改变统计图表的外观？ 具体操作 单击工作表内部已经做好的图表； 顶部出现“图表工具”选项卡： 在“设计”中， 改变图表样式、图表类型、存放位置、切换数据等。 在“布局”中 改变图表标题、图例位置等。

学习要点 21.Excel的数据库管理功能主要有什么？ 含义 Excel的工作表，通常是一个二维表，与数据库中的关系表相同。 结构为： 表头（标题行、字段名行）， 内容（记录，每行为一个记录） 主要功能 排序 / 筛选 分类汇总 / 频度分析

学习要点 22.如何对数据表排序？ 含义 按照某个关键词对记录排列顺序。 按照多关键词对记录排序 若主关键词取值相同，则按照次关键词排列。 方法 选定要排序的数据区（含标题行） 数据—排序 选择主关键词，设置排序类型（升序/降序) 选择次关键词，设置排序类型 确定。

学习要点 23.如何对数据表进行筛选？ 含义 为数据表提供一组筛选按钮； 以便按照某个字段筛选出所需的记录。 方法 选定要排序的数据区（含标题行） 数据—筛选（自动筛选） 在每个字段名之后出现筛选按钮； 单击筛选按钮，设置筛选条件； “确定”筛选条件。 筛选过程可重复使用，以便实现复杂筛选。

学习要点 24.如何对数据表进行分类汇总？ 问题 针对学生信息表，需要统计出每个学院的学生人数，或者需要计算各个班级的平均成绩。 先按照指定字段分组，然后对每组进行统计。 方法 选定要排序的数据区（含标题行） 执行“排序”功能（按分组字段排序） 数据—分类汇总 选择分组字段； 选择“统计方式”（个数/均值/总计） 选择被计算的字段（比如工资额、语文分） “确定”分类汇总。 分类汇总前必须先按照分组字段排序。 24.如何对数据表进行分类汇总？

学习要点 25.什么是频度分析？有何价值？ 问题 针对一份学生成绩单，需要计算出优秀、良好、中等、合格或不及格的人数？ 解决方法 重复利用CountIF函数； =Countif(成绩区域,”>85”) 使用专业的频度分析命令 基于分段数据进行统计

学习要点 26.如何对数据表进行频度分析？ 方法 先设定分段数据 数据从低到高排列 段数=个数+1 在分段数据附近选择n个单元格构成的区域 在区域中输入公式： =frequency(原始数据区，分段区域) <Ctrl>+<Shift>+<Enter> 注意 分段数据应升序书写 分段时以上包含方式分段 本命令是集合函数，一定要用<Ctrl>+<Shift>+<Enter>

学习要点 27.基于子表的记录标记？ 现有学校的大表数据（全集），另外有个子表（少量人员的名单）; 现在需要根据子表的名单，在大表中做标记，把属于子表的人员标记为1。 方法 在大表相应位置输入公式： =Countif(子名单区域，单元格） 子名单区域：子表的标记性区域 单元格：大表中被判断的记录 例如： =countif(N:N,A2) 以此公式填充相应区域。

学习要点 28.为子表补充信息？ 现有学校的大表数据（全集），另外有个子表（少量人员的名单）; 现在需要根据子表的名单，从大表中找数据，把子表的信息补充完整。 方法 在子表相应单元格输入公式： =Vlookup(查找关键词,数据来源区,结果所在列的列号，查找类型） 例如： =Vlookup(X2,$A$2:$W$100,5,false） 表示从区域$A$2:$W$100的第一列中查找X2的值，然后把对应记录第5列的值求出来。 以此公式填充相应区域。

Vlookup函数一般格式 =Vlookup(关键词,数据来源区,列号，类型） 函数中自变量的含义 查找关键词： 被查找记录的标记，应该为子表中当前记录的关键词， 即要找谁？ 数据来源区： 大表中中从关键词开始的区域，通常用绝对地址来表示。即从哪里找？ 被查找内容的列序号； 即对找到记录要哪个字段值 查找类型： 通常用False，表示“精确查找”

学习要点 29.数据分析的概念？ 什么是数据分析？ 以数据说话，基于数据做实证性研究是大数据时代的特色。 数据分析融合于当前社会的方方面面。 数据推送、资源管理 数据分析的两个层次 静态数据分析 面向静态数据的分析 动态数据挖掘 针对动态数据的实时挖掘

学习要点 数据分析主要有哪些内容？ 数据分析的主要内容： 统计描述 集中性：均值、众数、中位数 离散型：方差、标准差、标准误 相关性分析 差异性分析 降维与聚类 Excel的两种技术 基于函数的数据分析 基于分析工具库的数据分析

学习要点 30.如何求取方差、标准差、均值？ 概念解释 均值：平均数 方差：Σ(xi-x)2/n 标准差：方差的平方根 命令： 均值：=average(区域） 方差：=var(区域) 标准差：=stdev(区域）

学习要点 31.相关性分析的方法？ 含义 判断两列数据是否满足一定关联性关系 相关 正相关 负相关 不相关 方法 =Correl(区域1，区域2） 检查相关性系数， 若大于0.8，为高度相关； 若小于0.4，可认为不相关。

学习要点 32.两列数据的方差齐性检验？ 含义 判断两列数据的方差是否差别不大。 先分别计算两列数据的方差，然后参考原始数据的量级，判断两列数据的方差是否差别不大 方法 =Ftest(区域1，区域2） 结论 若结果>0.05，两列数据方差齐性。 若结果<0.05，两列数据的方差差距大。

学习要点 33.什么是差异性检验？ 含义 判断两列数据是否明显地存在着差别？ 思路 需要考虑原来数据的关系 独立 / 配对 考虑原来数据的方差等级 等方差 / 异方差 考虑考察的目标 判断完全相同 / 判断优于 或 弱于

学习要点 34.如何进行差异性检验？ 命令 =Ttest(区域1,区域2,单侧/双侧, 检验类型) 参数： 单侧/双侧 ————优于弱于/相同 检验类型： 1——配对样本 2——独立等方差 3——独立异方差 实例 =Ttest(C3:C100,D3:D100,2,1) =Ttest(C3:C100,C101:C199,1,3) 34.如何进行差异性检验？

感谢聆听 谢谢！