Excel在办公中的应用·提升[2013.04]

2
公式与函数
1.绝对引用与相对引用
实例：主减速比表
使用F4进行相对引用/绝对引用切换，加以条件格式
6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 28 4.67 4.00 3.50 3.11 2.80 2.55 2.33 2.15 2.00 1.87 1.75 1.65 1.56 1.47 1.40 1.33 1.27 1.22 29 4.83 4.14 3.63 3.22 2.90 2.64 2.42 2.23 2.07 1.93 1.81 1.71 1.61 1.53 1.45 1.38 1.32 1.26 30 5.00 4.29 3.75 3.33 3.00 2.73 2.50 2.31 2.14 2.00 1.88 1.76 1.67 1.58 1.50 1.43 1.36 1.30 31 5.17 4.43 3.88 3.44 3.10 2.82 2.58 2.38 2.21 2.07 1.94 1.82 1.72 1.63 1.55 1.48 1.41 1.35 32 5.33 4.57 4.00 3.56 3.20 2.91 2.67 2.46 2.29 2.13 2.00 1.88 1.78 1.68 1.60 1.52 1.45 1.39 33 5.50 4.71 4.13 3.67 3.30 3.00 2.75 2.54 2.36 2.20 2.06 1.94 1.83 1.74 1.65 1.57 1.50 1.43 34 5.67 4.86 4.25 3.78 3.40 3.09 2.83 2.62 2.43 2.27 2.13 2.00 1.89 1.79 1.70 1.62 1.55 1.48 35 5.83 5.00 4.38 3.89 3.50 3.18 2.92 2.69 2.50 2.33 2.19 2.06 1.94 1.84 1.75 1.67 1.59 1.52 36 6.00 5.14 4.50 4.00 3.60 3.27 3.00 2.77 2.57 2.40 2.25 2.12 2.00 1.89 1.80 1.71 1.64 1.57 37 6.17 5.29 4.63 4.11 3.70 3.36 3.08 2.85 2.64 2.47 2.31 2.18 2.06 1.95 1.85 1.76 1.68 1.61 38 6.33 5.43 4.75 4.22 3.80 3.45 3.17 2.92 2.71 2.53 2.38 2.24 2.11 2.00 1.90 1.81 1.73 1.65 39 6.50 5.57 4.88 4.33 3.90 3.55 3.25 3.00 2.79 2.60 2.44 2.29 2.17 2.05 1.95 1.86 1.77 1.70 40 6.67 5.71 5.00 4.44 4.00 3.64 3.33 3.08 2.86 2.67 2.50 2.35 2.22 2.11 2.00 1.90 1.82 1.74 41 6.83 5.86 5.13 4.56 4.10 3.73 3.42 3.15 2.93 2.73 2.56 2.41 2.28 2.16 2.05 1.95 1.86 1.78 42 7.00 6.00 5.25 4.67 4.20 3.82 3.50 3.23 3.00 2.80 2.63 2.47 2.33 2.21 2.10 2.00 1.91 1.83 43 7.17 6.14 5.38 4.78 4.30 3.91 3.58 3.31 3.07 2.87 2.69 2.53 2.39 2.26 2.15 2.05 1.95 1.87 44 7.33 6.29 5.50 4.89 4.40 4.00 3.67 3.38 3.14 2.93 2.75 2.59 2.44 2.32 2.20 2.10 2.00 1.91 45 7.50 6.43 5.63 5.00 4.50 4.09 3.75 3.46 3.21 3.00 2.81 2.65 2.50 2.37 2.25 2.14 2.05 1.96 46 7.67 6.57 5.75 5.11 4.60 4.18 3.83 3.54 3.29 3.07 2.88 2.71 2.56 2.42 2.30 2.19 2.09 2.00 47 7.83 6.71 5.88 5.22 4.70 4.27 3.92 3.62 3.36 3.13 2.94 2.76 2.61 2.47 2.35 2.24 2.14 2.04
1/1
2.函数快速输入
使用快捷键SHIFT+F3 配合TAB键，使用“公式记忆式输入”
1/1
3.函数类别
财务 日期与时间

1/1
数学与三角函数 统计 查找与引用 数据库 文本 逻辑 信息 工程
4.文本函数
比较两个单元格
“=”或EXACT(Text1,Text2) 前者不区分大小写，后者为精确匹配 “&”或CONCATENATE(text1,text2,...)
设置
✎ 允许的内容及范围
输入信息
✎ 设置单元格的提示信息
出错警告
✎ 警告、停止、信息
输入法模式：
✎ 中/英输入法自动切换
实例：下拉列表的创建
1/1
13.选择性粘贴
仅保留数值 粘贴格式
相当于格式刷
粘贴为图片
图片：矢量
在本单元格进行数据的运算 跳过空单元格：源数据为空时，保留目标单元格 转置：行列颠倒粘贴
Office
Excel在办公中的应用
2013年04月
定位：高效办公·为我所用·基于EXCEL2010
数据〃格式 公式〃函数
图表〃透视
1
数据与格式
1. 选择单元格
鼠标选取：拉选、选择整列/整行 在名称栏直接输入单元格地址，支持区域输入 CTRL+A
光标位于非空单元格，按一次，选择全部连续数据；
按两次，选择整个单元格 光标位于空白单元格，按一次直接选择整个工作表
双击非空单元格左/右/上/下边框
配合SHITF可进行选择
支持：CTRL-多重选择，SHIFT-连续选择
1/1
2. 单元格对齐与合并
合并后居中/合并单元格 跨越合并
每行分别合并
跨列居中
解决函数引用无法选择问题
自动换行
可使用ALT强制换行
缩小字体填充
根据文字多少自动缩放
1/1
3.同表格数据的比较
窗口的冻结
以选定单元格左上角十字线为界，仅第四象限活动 可直接使用菜单：冻结首行/冻结首列 拖拉至合适位置
窗口的拆分
双击拆分钮，系统快速调整至选定单元格； 选定A1单元格时，系统自动进行均等拆分
1/1
6.格式的设置
身份证号的处理

1/1
单元格设置为“文本”格式后再输入 以英文格式撇号打头“’”（快速文本格式设置） 显示为空：开始—选项—高级，取消勾选“在具有零
零值显示设置
值的单元格中显示零(Z)” 显示为短横线：数据格式设置为“会计专用”
设置“货币”格式
负数显示为红色
按条件标记单元格
使用条件格式
其他“条件格式”
1/1
8.表
菜单栏增加“表格工具” 名称：表1,2… ，可被引用
可转化为普通区域
勾选汇总行
Fra Baidu bibliotek1/1
9.排序
常规：升序/降序 自定义排序
可不先选定数据，系统自动判断列标题 可用“筛选”功能替代
1/2
9.排序
实例：删除表格空行（SAP导出的数据比较常见）
提取字符
2/2
5.日期时间函数
Excel使用整数部分存储日期，使用小数部分存储时间，如
0表示1900-1-0 0:00:00 41361.895表示2013-3-28 21:28:48 直接相减 日期：TODAY()，或CTRL+; 时间： NOW() ，或CTRL+: NOW()函数带有日期和时间
连接文本
1/2
4.文本函数
字符长度
LEN(text)或LENB(text) LEFT(text, [num_chars])或LEFT(text, [num_chars]) RIGHT(text,[num_chars])或RIGHTB(text,[num_bytes]) MID(text, start_num, num_chars)或 MIDB(text, start_num, num_bytes)
CTRL+ENTER
结合TAB键和ENTER键输入
限制区域输入
5.数据的编辑
数据的移动（替换）
拖动选中区域边框至新区域将直接替换目标区域 配合CTRL键，将保留原单元格内容 使用SHIFT，拖动选中区域进行单元格位置的移动
数据的移动（非替换）
配合CTRL键，将保留原单元格内容
1/1
14.高效编辑
格式刷的使用，双击可无限次使用 快捷键使用

1/1
撤销操作：CTRL+Z 重复操作：F4或CTRL+Y 绝对引用/相对引用切换：F4 快速移动至A1单元格：CTRL+HOME 快速移动至表格末端：CTLR+END 移动光标至行首/末、列首/末：CTRL+↑/↓/←/→ 公式/结果显示切换：CTRL+` 使用ALT+↓，可快速选择同列已输入的内容
同一工作表中，多表可同时保留筛选（表头不能重复）
表格工具的使用：“套用表格格式”

1/1
4.数据的输入
快速输入

1/1
单击拖拉可填充序列或进行复制 双击可实现智能填充：自动结束于左/右列数据终止处 先选择单元，输入数据按组合键，全部填充相同内容 使用TAB向右移动，结束后ENTER结束跳转到下行首 选中区域，仅使用TAB或ENTER进行切换
922 348
933 967
711 322
922 348
933 967
711 322
2/2
10.筛选
多项选择、按字体/填充颜色筛选/排序，可叠加
1/1
11.查找与替换
快速选择工作表中的全部特定字符/格式 使用格式替换
查找工作表/工作簿
查找公式/查找值
1/1
12.数据有效性
限制单元格内容的输入
7.条件格式
使用数据条
可直观比较同表数据大小 先选择单元格 按绝对值/占比 “格式”无效
273 857 -256 -524 -126 408 216 24 178 211 -230 547 -406 -641 464 1569 -841 365 -93 -243 405 -786 665 2116 693 -455 -721 240 -896 433 208 -17 870 991 -398 39 887 525 81 -363

哈尔滨 112 91 924 629 827 849 983 621 长沙 387 735 709 52 893 286 1030 804
添加辅助列
郑州 885 869 948 396 734 978 201 413 辅助 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 哈尔滨 112 91 924 629 827 849 983 621 长沙 387 735 709 52 893 286 1030 804 郑州 885 869 948 396 734 978 201 413 辅助 2 1 15 11 5 7 8 14 4 10 3 6 9 12 13 哈尔滨 91 112 348 621 629 827 849 922 924 983 长沙 735 387 967 804 52 893 286 933 709 1030 郑州 869 885 322 413 396 734 978 711 948 201 辅助 1 2 4 5 7 8 10 11 14 15 哈尔滨 112 91 924 629 827 849 983 621 922 348 长沙 387 735 709 52 893 286 1030 804 933 967 郑州 885 869 948 396 734 978 201 413 711 322