Excel2007-函数及统计图表使用技巧.ppt

1、Excel函数及统计图表应用技巧,议程,编辑公式 常用函数的运用 函数的高级使用 数据的处理技巧 数据的整理 数据透视表中的计算,在Excel中编辑公式,在Excel中编辑公式,点击等号（=）开始编辑公式 在公式中可以使用加、减、乘、除四则混合运算，括号、乘方（2表示2次方）等 公式复制过程中存在的问题 公式复制和引用关系 相对引用和绝对引用 在公式中按F4键可以在相对引用和绝对引用之间进行切换,追踪单元格引用,公式公式审核 追踪引用的单元格 追踪从属单元格 追踪单元格引用错误,常用函数的运用,数学函数,SUM、Average 加权平均的计算 MAX、MIN Round、RoundUp、Rou

2、ndDown 四舍五入函数 Rank 排名函数,文本函数,字符运算 使用“&”符号连接字符 Len 计算字符串长度 Left、Mid、Right截取字符串函数 例：根据身份证号计算生日,日期函数的使用,条件判断函数- IF,使用逻辑判断函数进行条件判断 奖金、税金的计算,常用统计函数的使用,Sumif 对满足指定条件的数值求和 CountIF 计算区域中满足给定条件的单元格的个数,函数的高级使用,高级查询函数的使用,VLOOKUP( )函数 在表格的最左列查找某个数值，并返回该值右边所对应的第N列的数值 Vlookup(要查找的值，范围，要显示的内容所在列数，查找类型) HLOOKUP( )函

3、数 在表格的首行查找指定的数值，并由此返回该值下方所对应的第N行的数值,查询函数的运用,Match 在一组数据中查找指定值所处的位置 Match函数和Vlookup函数结合使用 注意$(绝对引用)的使用,解决函数运算错误问题,Iserror 函数 如果值为任何错误值，则返回 TRUE Iserr 函数 如果值为除 #N/A 以外的任何错误值，则返回 TRUE,财务函数,折旧函数： SLB直线折旧法 DB单倍余额折旧法 DDB双倍余额折旧法,财务函数,投资函数： PMT 基于固定利率及等额分期付款方式，返回贷款的每期付款额 PV 返回投资的现值 NPV 通过使用贴现率以及一系列未来支出（负值）和

4、收入（正值），返回一项投资的净现值 FV 基于固定利率及等额分期付款方式，返回某项投资的未来值,数据的处理技巧,公式的反推,为公式指定目标值求解 工具单变量求解 ToolGoal Seek,目标值预测,根据现有公式，随变量的变化，对目标值变化的预测。 数据(Data)模拟运算表(Table) 单变量预测 双变量预测,表格的基本操作,选择区域 用鼠标直接拖动选中矩形区域 在行列标记上单击选中整行整列 再拖动鼠标的同时按住Ctrl键选择不连续区域 按快捷键Ctrl+Shift+8（或Ctrl+*）可以选中当前单元格周围连续的矩形区域 按住Shift键可以选中两点之间区域,选择表格续,快速移动光标

5、Ctrl+home 将光标移动到第一个单元格 Ctrl+End 将光标移动到表格最后一个单元格 Ctrl+ 将光标移动到当前区域最右列(其他方向箭头依此类推) 则Ctrl+Shift+向下箭头,选择当前单元格至当前区域最后一行的内容,表格的编辑和调整,鼠标拖动数据拖动表格边框 直接拖拽边框是移动； 按住CTRL键拖拽相当于复制操作； 按ALT键拖拽在其他Sheet间移动； 按SHIFT键拖拽将表格从原位置移出并插入到目标位置。（可用于交换两列位置）,表格的编辑和调整,序列填充 填充柄 ：选中区域右下角的黑点 拖拽填充柄可实现序列填充，如：January, February, March等 一切

6、和数字有关的数据都有序列功能 反向拖动填充柄相当于删除 自定义填充序列,表格的编辑和调整,鼠标拖动调整宽高 调整行高列宽，直接拖动行列标记间分割线 同时选中多行或多列，调整其中任意一列（行）的宽度，其他各列（行）同时改变 双击行列标记间分割线可根据内容自动调整 精确调整宽高 格式行行高 格式列列宽,数据的整理,正确的录入数据,正确的数字格式 设置数字货币格式 日期型数字格式（非日期型格式靠分列进行转换） 文本型数字格式（以单引号开头输入文本） 自定义数字格式 单元格格式快捷键 Ctrl+1,将规则文本导入EXCEL,Data Column to Text 如果文本文件中的数据以制表符、冒号、分

7、号或其他字符分隔，请选择“分隔符号”。 如果文本文件中的所有项的长度相同，请选择“固定宽度”。 可以手动调整分列的位置,也可以跳过某列而不导入.,表格的编辑和调整,剪切、复制、粘贴的使用 剪贴板的使用 选择性粘贴 数值,格式,公式,列宽 操作:加,减,乘,除 转置:行列互换,套用表格格式,套用表格样式 操作:选中表体,开始样式套用表格格式,30,丰富的条件格式,开始条件格式 什么是条件格式 将满足特定条件的数据以指定格式显示 使用条件格式显示单元格的数据 例：将表格中大于500的数字以红色显示 目的：突出显示满足某个条件数据 可以设置颜色、图标等 可以使用颜色,图标集等作为条件的区分 最多可以

8、设置64种格式,管理条件格式规则,根据单元格值判断 判断单元格唯一性与重复 使用公式：活动单元格不能直接参与判断时使用公式 调整规则顺序,定义数据验证,数据有效性 定义有效数据范围：数值、日期、序列 设置输入信息 设置出错警告 圈释无效数据,创建下拉列表,数据 有效性设置 允许列表 数据源：选择下拉菜单数据源 如果数据源在另外一个sheet则需要先给数据源命名,对数据表格的页面设置,页面设置 页面布局页面设置方向、缩放比例、页边距 分页预览视图下调整打印范围 视图分页预览 打印重复标题行： 页面设置工作表:打印重复标题行，打印重复标题列 打印选项 打印顺序,打印数据表格,Office按钮打印

9、打印范围 打印内容 打印选择区域 打印当前工作表 打印整个工作薄 打印份数,数据窗口的控制,冻结窗格 视图窗口冻结窗格 拆分窗口 视图窗口拆分窗口 新建并并排查看窗口 视图窗口并排查看 组合特定区域 数据分级显示组合,数据的安全性,保护工作薄 保护工薄的窗口和结构 设置文件的打开和修改口令 保护工作表 整张工作表的保护 部分数据区域的保护 高级数据区域保护,Excel 数据处理技巧,表格的基本操作,选择区域 用鼠标直接拖动选中矩形区域 在行列标记上单击选中整行整列 再拖动鼠标的同时按住Ctrl键选择不连续区域 按快捷键Ctrl+Shift+8（或Ctrl+*）可以选中当前单元格周围连续的矩形区

10、域 按住Shift键可以选中两点之间区域,表格的基本操作,选择区域 用鼠标直接拖动选中矩形区域 在行列标记上单击选中整行整列 再拖动鼠标的同时按住Ctrl键选择不连续区域 按快捷键Ctrl+Shift+8（或Ctrl+*）可以选中当前单元格周围连续的矩形区域 按住Shift键可以选中两点之间区域,选择表格续,快速移动光标 Ctrl+home 将光标移动到第一个单元格 Ctrl+End 将光标移动到表格最后一个单元格 Ctrl+ 将光标移动到当前区域最右列(其他方向箭头依此类推) 则Ctrl+Shift+向下箭头,选择当前单元格至当前区域最后一行的内容,表格的编辑和调整,鼠标拖动数据拖动表格边框

11、 直接拖拽边框是移动； 按住CTRL键拖拽相当于复制操作； 按ALT键拖拽在其他Sheet间移动； 按SHIFT键拖拽将表格从原位置移出并插入到 目标位置。（可用于交换两列位置）,表格的编辑和调整,序列填充 填充柄 ：选中区域右下角的黑点 拖拽填充柄可实现序列填充，如：January, February, March等 一切和数字有关的数据都有序列功能 反向拖动填充柄相当于删除 自定义填充序列,表格的编辑和调整,鼠标拖动调整宽高 调整行高列宽，直接拖动行列标记间分割线 同时选中多行或多列，调整其中任意一列 （行）的宽度，其他各列（行）同时改变 双击行列标记间分割线可根据内容自动调整 精确调整宽

12、高 格式行行高 格式列列宽,正确的录入数据,正确的数字格式 设置数字货币格式 日期型数字格式（非日期型格式靠分列进行转换） 文本型数字格式（以单引号开头输入文本） 自定义数字格式 单元格格式快捷键 Ctrl+1,表格的编辑和调整,剪切、复制、粘贴的使用 剪贴板的使用 选择性粘贴 数值,格式,公式, 列宽 操作:加,减,乘,除 转置:行列互换,编辑专业美观的表格,套用表格格式,套用表格样式 操作:选中表体,开始样式套用表格格式,49,丰富的条件格式,开始条件格式 什么是条件格式 将满足特定条件的数据以指定格式显示 如何设置条件格式 Format Conditional Format 使用条件格式

13、显示单元格的数据 例：将表格中大于500的数字以红色显示 目的：突出显示满足某个条件数据 可以设置颜色、图标等 可以使用颜色,图标集等作为条件的区分 最多可以设置64种格式,管理条件格式规则,根据单元格值判断 判断单元格唯一性与重复 使用公式：活动单元格不能直接参与判断时使用公式 调整规则顺序,定义数据验证,数据有效性 定义有效数据范围：数值、日期、序列 设置输入信息 设置出错警告 圈释无效数据,创建下拉列表,数据 有效性设置 允许列表 数据源：选择下拉菜单数据源 如果数据源在另外一个sheet则需要先给数据源命名,对数据表格的页面设置,页面设置 页面布局页面设置方向、缩放比例、页边距 分页预

14、览视图下调整打印范围 视图分页预览 打印重复标题行： 页面设置工作表:打印重复标题行，打印重复标题列 打印选项 打印顺序,打印数据表格,Office按钮打印 打印范围 打印内容 打印选择区域 打印当前工作表 打印整个工作薄 打印份数,数据窗口的控制,冻结窗格 视图窗口冻结窗格 拆分窗口 视图窗口拆分窗口 新建并并排查看窗口 视图窗口并排查看 组合特定区域 数据分级显示组合,数据的安全性,保护工作薄 保护工薄的窗口和结构 设置文件的打开和修改口令 保护工作表 整张工作表的保护 部分数据区域的保护 高级数据区域保护,用图表说话,使用图表分析数据,插入图表 应用图表样式 选中图表设计图表样式 更改图

15、表类型 设计更改图表类型 明确图表分析的目标,明确图表分析目标,柱状图对比关系的几种选择,条型图,分类标志易读 时间趋向弱,处理丢失数据,忽略丢失数据，有空距 将丢失数据处理为0 内插值,次坐标轴,制作第2Y轴图表,首先创建为折线图 在以第2Y轴为刻度的系列单击鼠标右键，设置数据系列格式 在坐标轴位置选择“次坐标轴” 更改此系列的图表类型以和主坐标轴区分.,涨跌柱线,趋势线,趋势线的的估计值与实际数值的接近程度,处理趋势线,支持趋势线的图表类型 柱形图、条形图、折线图、面积图、XY散点图、股价图、气泡图 适合趋势线的数据 成对的数字数据 基于时间的数据 添加趋势线,线性趋势预测,添加线性趋势线 趋势线预测 设置截距 显示公式：Y=ax+b 显示R平方值,图表替数据说话-根据数据设计图表,分组对比,坐标轴多级分类,不同内容数据对比,不同内容数据对比2,可以腾飞的图表,添加辅助数据项,处理自选图形