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

◦ 设置数字货币格式 ◦ 日期型数字格式（非日期型格式靠分列进 行转换） ◦ 文本型数字格式（以单引号开头输入文本） ◦ 自定义数字格式

单元格格式快捷键 Ctrl+1
将规则文本导入EXCEL

Data Column to Text
◦ 如果文本文件中的数据以制表符、冒号、 分号或其他字符分隔，请选择“分隔符 号”。 ◦ 如果文本文件中的所有项的长度相同，请 选择“固定宽度”。 ◦ 可以手动调整分列的位置,也可以跳过某列 而不导入.
公式复制过程中存在的问题 公式复制和引用关系 相对引用和绝对引用 在公式中按F4键可以在相对引用和绝对 引用之间进行切换

追踪单元格引用
公式—公式审核 追踪引用的单元格 追踪从属单元格 追踪单元格引用错误

常用函数的运用
数学函数

SUM、Average
◦ 加权平均的计算

单元格格式快捷键 Ctrl+1
表格的编辑和调整
剪切、复制、粘贴的使用 剪贴板的使用 选择性粘贴

◦ 数值,格式,公式, 列宽 ◦ 操作:加,减,乘,除 ◦ 转置:行列互换
编辑专业美观的表格
套用表格格式

套用表格样式
◦ 操作:选中表体,开始样式套用表格格式
丰富的条件格式


定义数据验证
数据—有效性 定义有效数据范围：数值、日期、序列 设置输入信息 设置出错警告 圈释无效数据

创建下拉列表
数据 有效性设置 允许列表 数据源：选择下拉菜单数据源 如果数据源在另外一个sheet则需要先给数据 源命名

对数据表格的页面设置
•
页面设置
• 页面布局页面设置方向、缩放比例、 页边距 • 分页预览视图下调整打印范围
MAX、MIN Round、RoundUp、RoundDown
◦ 四舍五入函数

Rank 排名函数
文本函数

字符运算
◦ 使用“&”符号连接字符
Len 计算字符串长度 Left、Mid、Right截取字符串函数 例：根据身份证号计算生日
日期函数的使用
函数 Today
Year Month Date

级数 应纳税所得额 上一范围上限 税率 扣除数
条件判断函数-- IF
1
2 3
不超过500元的
超过500元至2000元部分 超过2000元至5000元部分
0
500 2000
5%
10% 15%
0
25 125
4
5 6
超过5000元至20000元部分
超过20000元至40000元部分 超过40000元至60000元部分
开始—条件格式 什么是条件格式
如何设置条件格式
◦ 将满足特定条件的数据以指定格式显示
使用条件格式显示单元格的数据
◦ Format Conditional Format

可以使用颜色,图标集等作为条件的区分 最多可以设置64种格式
◦ 例：将表格中大于500的数字以红色显示 ◦ 目的：突出显示满足某个条件数据 ◦ 可以设置颜色、图标等

鼠标拖动数据－拖动表格边框
◦ ◦ ◦ ◦ 直接拖拽边框是移动； 按住CTRL键拖拽相当于复制操作； 按ALT键拖拽在其他Sheet间移动； 按SHIFT键拖拽将表格从原位置移出并插入到 目标位置。（可用于交换两列位置）
表格的编辑和调整

序列填充
◦ 填充柄 ：选中区域右下角的黑点 ◦ 拖拽填充柄可实现序列填充，如：January, February, March等 ◦ 一切和数字有关的数据都有序列功能 ◦ 反向拖动填充柄相当于删除 ◦ 自定义填充序列

鼠标拖动数据－拖动表格边框
◦ ◦ ◦ ◦ 直接拖拽边框是移动； 按住CTRL键拖拽相当于复制操作； 按ALT键拖拽在其他Sheet间移动； 按SHIFT键拖拽将表格从原位置移出并插入 到目标位置。（可用于交换两列位置）
表格的编辑和调整

序列填充
◦ 填充柄 ：选中区域右下角的黑点 ◦ 拖拽填充柄可实现序列填充，如：January, February, March等 ◦ 一切和数字有关的数据都有序列功能 ◦ 反向拖动填充柄相当于删除 ◦ 自定义填充序列
表格的编辑和调整
剪切、复制、粘贴的使用 剪贴板的使用 选择性粘贴

◦ 数值,格式,公式,列宽 ◦ 操作:加,减,乘,除 ◦ 转置:行列互换
套用表格格式

套用表格样式
◦ 操作:选中表体,开始样式套用表格格式
丰富的条件格式
开始—条件格式 • 什么是条件格式
•
• 将满足特定条件的数据以指定格式显示
5000
20000 40000
20%
25% 30%
375
1375 3375
7
8 9
超过60000元至80000元部分
超过80000元至100000元部分 超过100000元部分
60000
80000 100000
35%
40% 45%
6375
10375 15375
常用统计函数的使用

Sumif
◦ 对满足指定条件的数值求和
财务函数

折旧函数：
◦ SLB直线折旧法 ◦ DB单倍余额折旧法 ◦ DDB双倍余额折旧法
财务函数

投资函数：
◦ PMT 基于固定利率及等额分期付款方式， 返回贷款的每期付款额 ◦ PV 返回投资的现值 ◦ NPV 通过使用贴现率以及一系列未来支出 （负值）和收入（正值），返回一项投资 的净现值 ◦ FV 基于固定利率及等额分期付款方式，返 回某项投资的未来值
◦ 打印份数
数据窗口的控制

冻结窗格
◦ 视图窗口冻结窗格
拆分窗口
◦ 视图窗口拆分窗口
新建并并排查看窗口
◦ 视图窗口并排查看

组合特定区域
◦ 数据分级显示组合
数据的安全性

保护工作薄
◦ 保护工薄的窗口和结构 ◦ 设置文件的打开和修改口令

保护工作表
◦ 整张工作表的保护 ◦ 部分数据区域的保护 ◦ 高级数据区域保护
功能 取得计算机当前日期
根据日期值，取出日期所属年份 根据生日求年龄=year(today())-year(生日) 根据日期值，取出日期所属月份 根据年、月、日值返回特定日期值
Datedif 两个日期值间的差 工龄=Datedif(参加工作日期,today(),”Y”)
使用逻辑判断函数进行条件判断 奖金、税金的计算
选择表格续

快速移动光标
◦ Ctrl+home 将光标移动到第一个单元格 ◦ Ctrl+End 将光标移动到表格最后一个单元 格 ◦ Ctrl+ 将光标移动到当前区域最右列(其 他方向箭头依此类推) ◦ 则Ctrl+Shift+向下箭头,选择当前单元格至 当前区域最后一行的内容．
表格的编辑和调整
•
使用条件格式显示单元格的数据
• 例：将表格中大于500的数字以红色显示 • 目的：突出显示满足某个条件数据 • 可以设置颜色、图标区分 最多可以设置64种格式
管理条件格式规则
根据单元格值判断 判断单元格唯一性与重复 使用公式：活动单元格不能直接参与判断 时使用公式 调整规则顺序
Excel函数及统计图表应用技巧
议程
编辑公式 常用函数的运用 函数的高级使用 数据的处理技巧 数据的整理 数据透视表中的计算

在Excel中编辑公式
在Excel中编辑公式

点击等号（=）开始编辑公式
◦ 在公式中可以使用加、减、乘、除四则混 合运算，括号、乘方（^2表示2次方）等
数据的处理技巧
公式的反推
为公式指定目标值求解 工具—单变量求解 Tool—Goal Seek

目标值预测
根据现有公式，随变量的变化，对目标 值变化的预测。 数据(Data)—模拟运算表(Table) 单变量预测 双变量预测

表格的基本操作

选择区域
◦ 用鼠标直接拖动选中矩形区域 ◦ 在行列标记上单击选中整行整列 ◦ 再拖动鼠标的同时按住Ctrl键选择不连续区 域 ◦ 按快捷键Ctrl+Shift+8（或Ctrl+*）可以选中 当前单元格周围连续的矩形区域 ◦ 按住Shift键可以选中两点之间区域
表格的编辑和调整

鼠标拖动调整宽高
◦ 调整行高列宽，直接拖动行列标记间分割线 ◦ 同时选中多行或多列，调整其中任意一列 （行）的宽度，其他各列（行）同时改变 ◦ 双击行列标记间分割线可根据内容自动调整

精确调整宽高
◦ 格式行行高 ◦ 格式列列宽
正确的录入数据

正确的数字格式
◦ 设置数字货币格式 ◦ 日期型数字格式（非日期型格式靠分列进 行转换） ◦ 文本型数字格式（以单引号开头输入文本） ◦ 自定义数字格式
表格的编辑和调整

鼠标拖动调整宽高
◦ 调整行高列宽，直接拖动行列标记间分割 线 ◦ 同时选中多行或多列，调整其中任意一列 （行）的宽度，其他各列（行）同时改变 ◦ 双击行列标记间分割线可根据内容自动调 整

精确调整宽高
◦ 格式行行高 ◦ 格式列列宽
数据的整理
正确的录入数据

正确的数字格式

选择区域
◦ 用鼠标直接拖动选中矩形区域 ◦ 在行列标记上单击选中整行整列 ◦ 再拖动鼠标的同时按住Ctrl键选择不连续区 域 ◦ 按快捷键Ctrl+Shift+8（或Ctrl+*）可以选中 当前单元格周围连续的矩形区域 ◦ 按住Shift键可以选中两点之间区域