打印

EXCEL技术帖子

blueskyandwater

围观

驴民高工

Rank: 6 Rank: 6

自由人

1^# 大中小发表于 2010-5-2 00:22 只看该作者

EXCEL技术帖子

我搞财务会计,EXCEL使用的多!熟能生巧了!希望的我的雕虫小技帮助需要这方面技术的人!

我为人人是我为人做事宗旨!

由于我不能发图片上传文件,所以都是文字表述!

以前我计划写EXCEL书的,就写了2章,后来懒惰了!烂尾楼了...我就按照我曾经写的书来发帖!

开始是一页页发,...发累了啊!127页呢! 我全书另存为文本文件!一次性发完!技术咨询的,下面跟帖,请勿灌水! 谢谢

另外声明:

我来这里就是看看美人图,了解下国内大事,还会帮助需要我的EXCEL技术和想安装卫星电视的人

什么结党结社什么的,就别找我了!抱歉!

[ 本帖最后由 blueskyandwater 于 2010-5-2 00:41 编辑 ]

普及衛星電視才是中華民族通往民主自由之路

TOP

blueskyandwater

围观

驴民高工

Rank: 6 Rank: 6

自由人

2^# 大中小发表于 2010-5-2 00:24 只看该作者

办公软件实战培训教材（Excel）上册

2006*08--2007*08

精通办公软件培训系列教材－Excel

目录............................................................................................................................................................. 2

序言............................................................................................................................................................. 6

关于本书............................................................................................................................................. 6

第1 章基本操作....................................................................................................................................... 7

1.1. 文件..................................................................................................................................... 7

1.1.1. 另存为.......................................................................................................................... 7

1.1.1.1. 设置密码....................................................................................................... 7

1.1.1.2. 一键到位方便百倍..................................................................................... 8

1.1.2. 页面设置...................................................................................................................... 8

1.1.2.1. 页面.............................................................................................................. 8

1.1.2.2. 页边距.......................................................................................................... 8

1.1.2.3. 页眉页脚....................................................................................................... 9

1.1.2.4. 工作表: ....................................................................................................... 10

1.1.3. 属性：........................................................................................................................ 10

1.1.4. 打印............................................................................................................................ 10

1.1.5. 打印区域.................................................................................................................... 10

1.1.5. 应用示例.....................................................................................................................11

1.2. 编辑................................................................................................................................... 12

1.2.1. 选择性粘贴................................................................................................................ 12

1.2.2. 粘贴为超链接............................................................................................................ 13

1.2.3. 移动或复制工作表..................................................................................................... 13

1.2.4. 填充............................................................................................................................ 14

1.2.5. 清除............................................................................................................................ 15

1.2.6. 查找（替换）............................................................................................................ 16

1.2.7. 定位............................................................................................................................ 18

1.2.8. 链接............................................................................................................................ 19

1.3. 视图................................................................................................................................... 20

1.3.1. 分页预览.................................................................................................................... 20

1.3.2. 任务窗格.................................................................................................................... 20

1.3.3. 自定义工具栏............................................................................................................ 20

1.3.3.1. 创建自定义工具条..................................................................................... 20

1.3.3.2. 制作“专业符号”工具栏......................................................................... 21

1.3.4. 视面管理器................................................................................................................ 22

1.4. 插入................................................................................................................................... 23

1.4.1. 分页符........................................................................................................................ 23

1.4.2. 名城............................................................................................................................ 23

1.4.3. 批注............................................................................................................................ 25

1.4.2.1. 批注............................................................................................................ 25

1.4.2.2. 为单元格添加批注..................................................................................... 25

1.4.2.3. 调整批注大小或移动批注......................................................................... 25

第 2 页共 127 页

基本操作

1.4.2.4. 压缩图片..................................................................................................... 26

1.4.4. 对象............................................................................................................................ 26

1.4.3.1. 公式编辑器................................................................................................. 27

1.4.5. 超链接........................................................................................................................ 34

1.5. 格式................................................................................................................................... 35

1.4.2. 单元格........................................................................................................................ 35

1.4.2.1. 数字............................................................................................................ 35

1.4.2.2. 对齐............................................................................................................ 47

1.4.2.3. 字体............................................................................................................ 47

1.4.2.4. 边框............................................................................................................ 47

1.4.2.5. 图案............................................................................................................ 47

1.4.2.6. 保护............................................................................................................ 48

1.4.3. 工作表........................................................................................................................ 49

1.4.4. 自动套用格式............................................................................................................ 49

1.4.5. 样式............................................................................................................................ 50

1.4.6. 拼音指南.................................................................................................................... 51

1.4.7. 条件格式.................................................................................................................... 52

1.5.4.1. 重复数据（实用的技巧）：....................................................................... 52

1.5.4.2. 判断单元格的值......................................................................................... 53

1.5.4.3. 判断其他,设置当前.................................................................................... 53

1.5.4.4. 标注含有公式单元格................................................................................. 54

1.5.4.5. 自动标示当前行颜色................................................................................. 54

1.5.4.6. 标注小计和总计的行................................................................................. 55

1.6. 工具................................................................................................................................... 56

1.6.1. 共享工作簿................................................................................................................ 56

1.6.2. 保护............................................................................................................................ 58

1.6.3. 宏................................................................................................................................ 58

1.6.4. 加载宏........................................................................................................................ 58

1.6.5. 自动更正选项............................................................................................................ 59

1.6.6. 自定义........................................................................................................................ 60

1.6.7. 选项............................................................................................................................ 61

1.6.7.1. 视图............................................................................................................ 61

1.6.7.2. 重新计算..................................................................................................... 62

1.6.7.3. 编辑............................................................................................................ 63

1.6.7.4. 常规............................................................................................................ 64

1.6.7.5. 1-2-3 的帮助：........................................................................................... 64

1.6.7.6. 自定义序列：............................................................................................. 65

1.6.7.7. 错误检查..................................................................................................... 67

1.6.7.8. 安全性........................................................................................................ 67

1.6.8. 数据分析.................................................................................................................... 67

1.7. 数据................................................................................................................................... 68

1.6.1. 排序............................................................................................................................ 68

1.6.1.1. 随机排列..................................................................................................... 69

1.6.1.2. 自定义排序................................................................................................. 69

1.6.1.3. 按字体颜色排序......................................................................................... 69

1.6.1.4. 快速插入行................................................................................................. 70

1.6.2. 筛选............................................................................................................................ 71

1.6.2.1. 自动筛选..................................................................................................... 71

1.6.2.2. 高级筛选..................................................................................................... 71

第 3 页共 127 页

精通办公软件培训系列教材－Excel

1.6.3. 记录单........................................................................................................................ 73

1.6.4. 分类汇总.................................................................................................................... 74

1.6.5. 有效性........................................................................................................................ 75

1.6.5.1. 创建下拉列表选项..................................................................................... 76

1.6.5.2. 复杂的下拉表选项..................................................................................... 77

1.6.5.3. 动态的下拉表选项..................................................................................... 78

1.6.5.4. 制作会计科目菜单..................................................................................... 79

1.6.5.5. 有效性设置的其他情况............................................................................. 80

1.6.5.6. 自定义条件示例......................................................................................... 82

1.6.5.6.1. 防止输入重复值................................................................................. 82

1.6.5.6.2. 限定数据输入数量............................................................................. 82

1.6.5.6.3. 多列唯一值......................................................................................... 83

1.6.5.6.4. 跨表禁止重复输入............................................................................. 83

1.6.5.6.5. 限定总数............................................................................................. 84

1.6.5.6.6. 防止输入周末日期............................................................................. 84

1.6.5.6.7. 日期序时输入..................................................................................... 84

1.6.5.6.8. 特定前缀............................................................................................. 85

1.6.5.6.9. 和窗体结合简单例子......................................................................... 85

1.6.5.6.10. 其他问题............................................................................................. 87

1.6.5.7. 添加提示信息............................................................................................. 88

1.6.5.8. 添加出错信息............................................................................................. 89

1.6.5.9. 输入法模式................................................................................................. 90

1.6.5.10. 源于其它工作簿的列表............................................................................. 90

1.6.6. 模拟运算表................................................................................................................ 91

1.6.6.1. 单变量模拟运算表..................................................................................... 91

1.6.6.2. 双变量模拟运算表..................................................................................... 92

1.6.6.3. 模拟运算表和公式比较............................................................................. 93

1.6.6.4. 模拟运算表问题......................................................................................... 93

1.6.7. 分列............................................................................................................................ 95

1.6.8. 合并计算.................................................................................................................... 97

1.6.9. 组及分级显示............................................................................................................ 97

1.6.10. 数据透视表(图).......................................................................................................... 98

1.6.11. 列表.......................................................................................................................... 101

1.8. 窗口................................................................................................................................. 104

1.8.1. 拆分.......................................................................................................................... 104

1.8.2. 冻结.......................................................................................................................... 104

1.8.3. 当前文件.................................................................................................................. 104

1.9. 帮助................................................................................................................................. 106

1.10. 其他................................................................................................................................. 106

1.10.1. 摄影.......................................................................................................................... 106

1.10.2. 双击功能.................................................................................................................. 107

1.10.3. 其他.......................................................................................................................... 108

第 4 页共 127 页

基本操作

1.10.4. 双击工作表打不开文件............................................................................................111

1.10.5. 显示工作表名称.......................................................................................................111

1.10.6. 无宏时显示了宏警告................................................................................................113

1.10.7. 出现了“文件正由…编辑”信息............................................................................113

1.10.8. Excel 在启动时崩溃..................................................................................................113

1.10.9. 清除自定义菜单.......................................................................................................113

1.10.10. Excel 的操作快捷键..................................................................................................114

1.10.9.1. “F9”键......................................................................................................114

1.10.9.2. 快捷键大全................................................................................................114

1.10.11. 汇集多表特定数据................................................................................................... 120

1.10.12. 比较运算符为何要用引号....................................................................................... 120

1.10.13. 输入身份证号码的解决方法................................................................................... 121

1.10.14. 小数问题.................................................................................................................. 121

1.10.15. 文件修复.................................................................................................................. 122
第2 章窗体和VBA............................................................................................................................... 123

4.1. 窗体................................................................................................................................. 123

4.2. VBA................................................................................................................................. 123

4.2.1. 电子表格设奇数、偶数页打印............................................................................... 123

4.2.2. 工作表中的小写字母都变成大写........................................................................... 124

4.2.3. 一次移除文件内超链接(Excel。Word 。PPT) ....................................................... 124

4.2.4. 批量删除工作表中的定义名称............................................................................... 125

4.2.5. 条件格式的代码...................................................................................................... 125

4.2.6. 插入批量空白行...................................................................................................... 125

4.2.7. 批量删除网页复制用来打‘勾’的小图形........................................................... 126

4.2.8. 批量删除相同的数据行........................................................................................... 126

4.2.9. EXCEL 中的自动宏.................................................................................................. 126

第 5 页共 127 页

精通办公软件培训系列教材－Excel

序言

关于本书

. 本书的知识易读，易懂，易做（不做也容易）！
. 本书内容实用：所有技巧都是办公室人员工作急需知道的，
. 本书学习效果好，可以现学现用，
. 本书是你成为EXCEL 高手的捷径
. 本书让你对excel 有了更深的认识：问起来，人人都会说自己：精通Excel！甚至说： Office

不需要学习的！！！可实际你真的精通吗？大部分人真正的水平也就是基础的

1. 学习了本书，人人做事快速有效，省去加班的人工费用，有限的上班时间，做更多事情
2. 使用本书培训见效快；培训效果立竿见影
3. 本书抛砖引玉，规范企业流程，完善制度
4. 员工素质高，企业实力一定提高
版权说明:
本书初稿完成于2007 年
2010 年1 月整理成PDF
可以任意复制和分发传播
不得用于商业用途!

2010 年1 月8 日星期五

第 6 页共 127 页

基本操作

第1 章基本操作

1.1.文件
本章操作课程是按照Excel 菜单：文件编辑视图

… 顺序讲解的，说是“基础操作”，但

是涉及Excel 知识的深度和广度都远超出基础范围了！

1.1.1. 另存为
1.1.1.1. 设置密码
z 为了有效保护文档安全，经常需要设置密码：（OFFICE 密码只能防住菜鸟）

文件
→
另存为
→
工具
→
常规选项→输入密码→
确定（要覆盖原文件才能设置成功密

码，否则另存为新文件，只能是新文件有密码的）

z 还有一种设置密码方法：

→
→
工具
安全性
选项
第 7 页共 127 页

精通办公软件培训系列教材－Excel

1.1.1.2. 一键到位方便百倍
z 在另存为窗口选中,选中文件夹
z
z 在对话框左边就有了填入的文件
z 以后保存文件就不需要找文件夹了，

工具→ 添加到“我的位置”
1.1.2. 页面设置
设置和打印有关的选项，一般打印问题都在这里解决！

1.1.2.1. 页面
z 方向：选择打印的纸张方向
z 缩放：调整为，意思把多于一页的表格调整为特定页数！此操纵前，

最好先把页边距调整好（文件
→ 打印预览→ 页边距）
。此功能等同于（视图→ 分页预览
）。

1.1.2.2. 页边距
z 上下左右，页眉页脚：设置页边距；等同于

文件→ 打印预览→ 页边距
z 居中方式：数据（图表）在这个页面里面的居中（水平，垂直）位置，
见上图

第 8 页共 127 页

基本操作

1.1.2.3. 页眉页脚
z

常用的页眉页脚在页眉页脚下面用鼠标选择既可！
页面设置→自定义页眉（页脚）
z 自定义页眉（页脚）：文件

→
从左至右顺序功能（左上图）

z :设置页眉/页脚文字的属性,例如:字体,字型,大小,下划线,删除线,上标,下标
z 页码:当前第几页
z :总页数,所有数据共有几页
z :日期:系统当前日期,是自动更新的
z :时间系统当前时间, 是自动更新的
z 路径文件:此文件的绝对路径+文件名,例如
z 文件:当前文件名
z
标签名:当前表名

z

:图片:插入图片,一般是企业LOGO 或者徽标
z 设置:插入图片后,设置图片格式的
z 11(12.13):位置: 设置页眉（页脚）在在文件的左,中,右位置

实际应用例子
z 要求每张文件右下脚都要有”制表人”,？答：就在上图 ‘’13”位置直接填入:”制表人:”

说明：工作表设置（详见1。1。2。4 工作表）打印标题栏只能打印顶端（左端）标题栏，不能打印尾
端标题栏；页脚设置可以部分解决这个问题。彻底解决就需要设置复杂的公式，详见CD 文件！

z 曾经有人问：正常编辑时候是自己要求的数据和图表，但是预览和打印的时候就在页眉，页脚位置出
现一些多余的文字和数据，怎么解决？答：看看页眉，页脚的自定义；把所有多余的数据清除就
OK 了！

z 如果在一个Excel 文件中含有多个工作表，如何将多个工作表一次设置成同样的页眉和页脚？如何才
能一次打印多个工作表？把鼠标移到工作表的名称处（若你没有特别设置的话，Excel 自动设置的名
称是“sheet1、sheet2、sheet3。。。。”），然后点右键，在弹出的菜单中选择“选择全部工作表”的菜单
项，这时你的所有操作都是针对全部工作表了，不管是设置页眉和页脚还是打印你工作表。当然，如果
只选择其中几个工作表，你可用ctrl 或shift 与鼠标结合选择

z 要将第1 页在页脚自动显示为第10 页，第2 页在页脚自动显示为第11 页，怎么设置？操作：自定义
页脚: 第&[页码]+17 页

第 9 页共 127 页

精通办公软件培训系列教材－Excel

1.1.2.4. 工作表:
打印区域→设置打印区域
z 打印区域: 不打印全部文章，只打印部分,等同于文件
z 打印标题:每页都打印同样的标题!

→
z

实际应用：如表格很长,要每页都要有标题行,就点击顶端标题行设置即可,左端标题列类似。
打印：
. 网格线把虚线的网格线都打印出来
. 单色打印彩色的也只是按照黑白打印
. 按草稿方式不打印边框，彩色打印成黑白色
. 行号列标是否打印行号列标
. 批注是否打印批注

. 错误单元格打印为设置错误值打印样式

1.1.3. 属性：
填写标题，主题，作者，类别，关键词，备注这些，便于以后检索。。

1.1.4. 打印
与安装的打印件有关,打印件支持的,可以支持双面打印等高级功能

1.1.5. 打印区域
不想打印全部文章，只是打印部分

文件→打印区域设置打印区域
实践经验：如果一个工作表在编辑时候能看见全部数据图表,但是打印预览看不到某些区域，，也不
能打印出来,那取消打印区域可以解决

第 10 页共 127 页

基本操作

1.1.5. 应用示例
z 如何不使显示或打印出来的表格中包含有0 值？通常情况下，我们不希望显示或打印出来的表
格中包含有0 值，而是将其内容置为空。

方法一；使用加上If 函数判断值是否为0 的公式，即： =if（b2＋c2＋d2 = 0,””, b2＋c2＋d2）

方法二：选择

工具
→
选项
→
视图
，在

窗口选项
中去掉

零值
选项。

格式→单元格→数字
分类
方法三：使用自定义格式。选中数据区域，选择

，从

列表框

中选择

自定义，在格式框中输入 G/通用格式；-G/通用格式；；按
方法四：将0 替换成空 (选择

单元格匹配
)
方法五：定义目标EXCEL 表:

确定
按钮即可。

. 在

条件格式中将单元格数值
等于“0”，在

格式
中将

颜色
设为“白色”，确定
即可。

. 定义表中的0 值均为白色，打印时也不显示

第 11 页共 127 页

精通办公软件培训系列教材－Excel

1.2.编辑
选择性粘贴
，

查找（替换）
，

定位
是数据处理常用功能！

本节讲授数据编辑常用功能！其中

1.2.1. 选择性粘贴
非常有用的功能，一定要学会！只介绍常用功能技巧，其他有兴趣自己动手实践
粘贴：
. 全部：等同于“粘贴”（Ctrl+V）
. 公式：只复制公式
. 数值：把公式变成数值,复制过来（数值化）

格式刷
. 格式:只是复制单元格的格式（等同于）
. 批注：只是复制单元格的批注
….

运算（非常实用的技巧）：
z 加（减乘除）1

：用法等同。把大量数据一次性加（减乘除）一个特定数，应用于○固定资
产折旧的已经折旧月数等；○2 把一批数据改变正负；○

3 把一批数据一次性变成千（万）位
单位……用法如下：

空白单元格填入需要的数值→复制→选择全部数据→鼠标右键（编辑）→选择性粘贴→选中数值，
加（减乘除）
z "跳过空单元"：复制数据中含有空格的，不粘贴空单元格出来

→可见单元格→复制→选择性粘贴→转置（Transpose 函数也可以转置；在某一单元
z “转置”粘贴数据时选中，把复制的行数据变成列数据了，反之亦然！（非常实用的技巧）
复制数据再转置,不复制转置被隐藏的行或列：选择需要转置的单元区域→按下F5 → 定位条件

格输入公式“INDEX(reference,COLUMN(A1),ROW(A1))”，然后向下向右拖放。）
z "粘贴连接" 和源数据（一般是本文件）产生连接,目标数据可以随着源数据改变而更新

注意：
. 剪切不能用此功能
. 把公式数值化后的数据会有很多位的小数，需要精确数据的时候，一定注意这个问题。

第 12 页共 127 页

基本操作

1.2.2. 粘贴为超链接
和源数据（如果外部文件，指OFFICE 文件）产生连接,目标数据可以随源数据改变而自动更新。
适用于做成套的关联表。

1.2.3. 移动或复制工作表
. 工作簿：把选定工作表移至（剪切）当前打开的某个excel 文件
. 下列选定工作表之前：把选定工作表移至（剪切）某个表前面
. 建立副本：选中就不是移至（剪切），而是复制

还有一种新建工作表方法：在工作表名称上点击右键，出现：

在

常用
对话框选择

是系统的模板

电子方案表格
工作表
确定

第 13 页共 127 页

精通办公软件培训系列教材－Excel

1.2.4. 填充
z 向下(右上左)填充:
当前单元格填充上(左下右)单元格的数据
z 序列
批量填充有规律数据

选择一个单元格,单元格内须有数值
.序列是向左填充(行) ,向下填充(列)
.填充方式
.填充规律:或者是间隔
.填充到的最大值

效果如图框中的数据

说明:
填充序列还有2 个快速的方法:
方法1

.任意输入数字,
鼠标选中,同时按住Ctri 健,
出现小十字(如图),下拉鼠标

方法2

输入2 个序列数值,
鼠标选中这2 个单元格
下拉鼠标
注意:
如果填充是不产生序列,只是2 个数值填充
请下拉鼠标时候,按住Ctrl 健

第 14 页共 127 页

基本操作

z 内容重排
能够把当前单元格内容均匀分布在下面单元格内的操作;也能够快速合并多个单元格数据
范例:
单元格数据是一首唐诗,如何快速把句诗分开呢

第一步: 调整单元格列宽,如图(注意:列宽缩小到合适的长度,这样才能完全分离它):

点击

编辑填充内容重排
出现如左下图对话框,选择

效果如右下图:

确定
.如果合并多个单元格数据,.操作是类似上面步骤的;(第一步是选择所有需要合并的单元格);
注意:单元格列宽必须有足够装下所有内容的列宽;
对于数字和公式的单元格,不能使用内容重排.功能

1.2.5. 清除
类似删除,有四种选择,功能可以望文生义的!
键盘的DELete 健等同于删除 (下图) 只能删除内容,不能删除格式批注

第 15 页共 127 页

精通办公软件培训系列教材－Excel

1.2.6. 查找（替换）
格式：可以按照特定格式查找（替换）
z 范围：可在当前工作表（整个）文件查找

例子：
. 把表内单元格值为0 的改为空格
按ctrl+H 替换→查找内容输入0→替换为空→全部替换。
这样做 101 变成 11,1000001 也会变成 11，不是我们要的结果！
正确做法:要勾选"单元格匹配",只替换0 值的单元格
. 查找(替换)特殊字符: * ? ( 非常重要的知识)
如果对类似"01011061*A" 这样带有 * 的数据处理会出错的,原因就是在Excel 里面 * 表示所有数
据, ? 表示一个数据(字符)。

z 如果处理带有 * ， ? 的数据,一定先要把 * （ ? ）换成其他字符，如: +,-,@ 。。 ; 处理
完数据再把 * （ ? ）还原

z

怎样查找（替换）* ( ? ) ：编辑

→查找（替换）→ 输入"~*"( 以~*代替*,
~ 在Esc 键下边)。

如图：

注意
. 以上字符都是在拼音状态下

. "查找范围":要选择"数值"还是"公式"视情况定
. 如果是查找一定的区域，要先用鼠标选择那个区域，查找前鼠标的位置有

通配符类型用法
?（问号）任何单个字符,例如:fo?you 查找“foryou”和“foxyou”
*（星号）任何字符数.例如，*oryou 查找“Foryou”和“Allforyou”

第 16 页共 127 页

基本操作

1. 把符合条件的数据标注特定格式
通常在数据校对时要用到查找，找到之后就用不同格式区分(如字体为红色、底纹为黄色等等)，如
此重复。怎么样批量设置所有符合条件的数据？请看下面的操作：
例如：在工作表查找所有500 ，将新找到的数据自动改为红色与之区分。
z 先找到第一个数据：500 ，退出查找窗口，并将字体改为红色

z 双击格式刷，然后查找→ ，
z 不要关闭查找窗口，直接使用：Ctrl+A
z 一切OK…

查找全部
第 17 页共 127 页

精通办公软件培训系列教材－Excel

1.2.7. 定位
→
编辑
定位→定位条件
z 批注选中当前表里面所有的批注
z 常量选中当前表里面所有的是常量的单元格
z 公式选中当前表公式计算得出的数字,文本,逻辑值,错误单元格。
z 空值选中当前表里面所有的空白值的单元格……。
z 对象选中当前表里面所有对象
z 条件格式：选中当前表里面所有带条件格式的单元格
z 数据有效性：选中当前表里面所有带有数据有效性设置的单元格

我们经常需要更改（删除）一个有大量数据的表里面的特定数据,如错误值，里面的数据有公
式，文本数值，逻辑值，数字，错误值，空值… … 但是数据没有规律，一个个处理很是耗费时间
的，容易出错，可能遗漏，怎么解决？

实际应用：
例如：要把表里面的错误值全部去掉（指公式计算出来的错误值，如果单元格里面的数据就是
类似 #DIV/0! 这样子的错误文本，使用查找，替换可以解决；详见1.2.4 查找）
操作：选择数据区（表格区域）→

→
→
→
→
编辑
选中错误
公式
定位条件
定位
第 18 页共 127 页

基本操作

1.2.8. 链接
当文件有外部文件的链接，此功能才能用！

z 更新值：更新链接的数据

z 更改源：更改数据的链接源

z 打开源文件：打开引用的源文件

z 断开链接：断开源文件的的链接，直接数值化；

如果不断开，在源文件更改位置（删除）后，无法更新，将出现错误值

z 检查状态：检查源文件是否可以更新

z 启动提示：设置打开excel 文件时候的更新属性

注意：
z 工作中，我经常遇到xp 版本的excel 自动更新链接的情况，即使在是否更新对话框选择不更
新也不行！所以强烈建议：做好表格后，把有外部文件链接的数据全部数字化

z 一般情况下，我们在WORD 或EXCEL 做超级链接到EXCEL 文件中时，只能链接到该文件，
而不能链接到指定的工作表，下面的步骤是链接到具体工作表的具体位置，在WORD 中同样
适用。

第 19 页共 127 页

精通办公软件培训系列教材－Excel

1.3.视图
1.3.1. 分页预览
视图→分页预览
是（页面缩放）调整为的图形效果！

页边距
调整好，

此操纵前，最好先把

直接用鼠标拉动蓝色虚线调整！
蓝色实线是数据区，蓝色虚线是打印线

1.3.2. 任务窗格
视图→任务窗格
点击后左侧将出现任务窗格

z 新建工作簿：选择新建一个指定类型工资簿
z 剪贴板：处理复制到系统剪贴板的文字图片等
z 搜索：搜索工作簿内容
z 插入剪贴画：插入系统自带的剪贴画或者硬盘里面的图片

1.3.3. 自定义工具栏
我们每个人的工作习惯不同；所以使用excel 方式一定不同，为了提高工作效率，自己打造一个符
合自己习惯的工具条；还有很多实用的功能，常用工具条上没有。首先，打开自定义窗口：

视图→工具栏
→
自定义：（工具
→
自定义）还可以在工具条空白区域点击鼠标右键调出自定义窗口

1.3.3.1. 创建自定义工具条
第 20 页共 127 页

基本操作

第一步：工具栏
→
新建弹出新建工具栏输入名城
第二步：点击命令在类别选择需要的分类菜单选中命令里面自己需要的命令按住鼠标左键拖
到第一步新建的工具条松开鼠标，完成后见下图：
编辑自定义工具条
z 在打开自定义窗口情况下才能编辑。在要编辑的按钮上点击鼠标
右键，如下窗口
z 常用命令如下：

○1 复位按钮图像：回复原始按钮图像
○
2 编辑按钮图像：自己用鼠标画图
○

3 更改按钮图像：选择系统提供的图像更改

○4 默认样式：按钮外观有图无文字
○5 总是用文字：文字
○6 图像与文本：图像文字混用
做成自己特有的工具条，如图：
1.3.3.2. 制作“专业符号”工具栏
在编辑专业表格时，常常需要输入一些特殊的专业符号，为了方便输入，我们可以制作一个属于自
己的“专业符号”工具栏。

1.执行“工具→宏→录制新宏”命令，打开“录制新宏”对话框，输入宏名如“fuhao1”并将宏保
存在“个人宏工作簿”中，然后“确定”开始录制。选中“录制宏”工具栏上的“相对引用”按钮，
然后将需要的特殊符号输入到某个单元格中，再单击“录制宏”工具栏上的“停止”按钮，完成宏
的录制。
仿照上面的操作，一一录制好其它特殊符号的输入“宏”。
2.打开“自定义”对话框，在“工具栏”标签中，单击“新建”按钮，弹出“新建工具栏”对话框，
输入名称——“专业符号”，确定后，即在工作区中出现一个工具条。
切换到“命令”标签中，选中“类别”下面的“宏”，将“命令”下面的“自定义按钮”项拖到“专
业符号”栏上（有多少个特殊符号就拖多少个按钮）。
第 21 页共 127 页

精通办公软件培训系列教材－Excel

3.选中其中一个“自定义按钮”，仿照第2 个秘技的第1 点对它们进行命名。
4.右击某个命名后的按钮，在随后弹出的快捷菜单中，选“指定宏”选项，打开“指定宏”对话框，
选中相应的宏（如fuhao1 等），确定退出。
重复此步操作，将按钮与相应的宏链接起来。
5.关闭“自定义”对话框，以后可以像使用普通工具栏一样，使用“专业符号”工具栏，向单元格
中快速输入专业符号了。
1.3.4. 视面管理器
视面管理器同一工作表可以保存多个打印页面，打印其中不同的区域

第一步:

设置好第一种打印视图(可用

视图分页预览
功能实现,视图管理器还可以保存分页预览的状态)

第二步:

执行

视图
，单击

视面管理器
添加
按钮，输入一个名称（如:视图1）。

第三步:
重复第一步,第二步操作,可设置多个视图
第四步:

需要打印某种格式时，打开

视面管理器，选中需要打印的视图名称，单击
显示
(也可以直接双击视

图名称)，工作表即刻按事先设定好的界面显示出来。

第 22 页共 127 页

基本操作

1.4.插入
1.4.1. 分页符
插入

分页符
即是打印分页;插入一个分页符后,按钮变成
此功能可以结合分页预览页面设置等功能,实现各种打印效果

删除分页符
1.4.2. 名城
插入→名称→定义
（ CTRL+F3 ）

定义名称:
为了更加直观地标识单元格（区域）和Excel 计算需要（宏表函数必需名称）,我们可以给它们赋予
一个名称。选中单元格（区域）,鼠标单击

编辑栏
顶端的“名称框”,在其中输入名称后回车。也

可以选中要命名的单元格（区域）,单击插入→名称→定义命令,在打开的“定义名称”对话框中
输入名称后确定即可。要删除已经命名的区域,按相同方法打开定义名称对话框,选中你要删除的
名称点击删除即可。
..
输入名称: 中文,英文皆可
引用位置
是单元格,或者公式函数,宏表函数

.
. 只有点击

确定
,或者

添加
,才有效

建议使用简单易记的名称，不可使用类似A1… 的名称，因为它会和单元格的引用混淆。还有很多
无效的名称，系统会自动提示你。引用位置：可以是工作表中的任意单元格，可以是公式，也可以
是文本。在引用工作表单元格或者公式的时候，绝对引用和相对引用是有很大区别的，注意体会他
们的区别 – 和在工作表中直接使用公式时的引用道理是一样的。

第 23 页共 127 页

精通办公软件培训系列教材－Excel

名称的妙处：

. 减少输入的工作量
如果你在一个文档中要输入很多相同的文本（数据区），建议使用名称。例如：定义“DATA =
I LOVE YOU， CMULE ；因为CMULE 给我学习工作的机会！” ，你在任何单元格中输入
=DATA ，都会显示：I LOVE YOU， CMULE ；因为CMULE 给我学习工作的机会！

. 在公式中出现多次相同的数据段
例如公式 =IF(ISERROR(IF(A1>B1,A1/B1,A1)),””, IF(A1>B1,A1/B1,A1)) ，这里你就可以将
IF(A1>B1,A1/B1,A1) 定义成名称A_B ，你的公式便简化为 =IF(ISERROR(A_B),””,A_B)

. 超出某些公式的嵌套
例如IF 函数的嵌套最多为七重，这时定义为多个名称就可以解决问题了。也许有人要说，使
用辅助单元格也可以。当然可以，不过辅助单元格要防止被无意间被删除。

. 字符数超过单元格允许的最大量
名称的引用位置中的字符最大允许量也是有限制的，你可以分割为两个或多个名称。同上所述，
辅助单元格也可以解决此问题，不过不如名称方便。

. 某些EXCEL 函数只能在名称中使用
例如由公式计算结果的函数，在A1 中输入’=1+2+3 ，然后定义名称 RESULT =
EVALUATE(Sheet1!$A1) ，最后你在B1 中写入 =RESULT ，B1 就会显示6 了。还有宏
表函数也只能在名称中使用，请参考第2 章宏表函数。

. 图片的自动更新连接
例如你想要在一周内每天有不同的图片出现在你的文档中，具体做法是：

○ 找7 张图片分别放在SHEET1 A1 至A7 单元格中，调整单元格和图片大小恰好合适
1

○ 定义名称MYPIC = OFFSET(SHEET1!$A$1,WEEKDAY(TODAY(),1)-1,0,1,1)
2

3

控件工具箱
→ 文字框，在编辑栏中将EMBED("Forms 。TextBox 。1","") 改成MYPIC
就OK 了。这里如果不使用名称，是不行的。
. 其他
此外，名称和其他操作（函数），例如数据有效性的联合使用，会有更多意想不到的结果，具
体资料参见数据有效性（1.7.5.）

○

第 24 页共 127 页

[ 本帖最后由 blueskyandwater 于 2010-5-2 00:49 编辑 ]

普及衛星電視才是中華民族通往民主自由之路

TOP

blueskyandwater

围观

驴民高工

Rank: 6 Rank: 6

自由人

3^# 大中小发表于 2010-5-2 00:25 只看该作者

基本操作

1.4.3. 批注
1.4.2.1. 批注
批注是附加在单元格中，与其他单元格内容分开的注释。批注是十分有用的提醒方式，例如注释复
杂的公式如何工作，或为其他用户提供反馈。
查看批注内容

Microsoft Excel 提供了几种查看批注的方法。含有批注的单元格的右上角有三角形的批注标识
符。如果指针停在含有标识符的单元格上，就会显示该单元格的批注。您也可以连续地显示批注（单
条批注或是工作表上的所有批注）。“审阅”工具栏能使您按顺序逐条查看每项批注。您还可以将批
注打印到工作表的相应位置，或在打印输出结果的底部将批注打印成列表。
批注中的用户姓名
按用户的姓名来区分批注，就可以在共享工作簿或传送给几个用户的工作簿中，判断出键入每项批
注的操作者。当合并几个工作簿的更改时，批注也被合并，这样单元格中所有的批注文字就会按顺
序显示。

1.4.2.2. 为单元格添加批注
批注是为单元格键入的注释。单击要加批注的单元格。在“插入”菜单中，单击“批注”。在弹
出的批注框中键入批注文本。如果不想在批注中留有您的姓名，请选择并删除姓名。完成文本键入
后，请单击批注框外部的工作表区域。

1.4.2.3. 调整批注大小或移动批注
在需要更改批注的单元格上单击鼠标右键，在随后出现的快捷菜单中单击“显示批注”。单击批注
框的边框，显示尺寸控点。
请执行下列操作之一：
更改尺寸拖动批注框四边或角点上的尺寸控点。
移动批注拖动批注框的边框。
如果隐藏并重新显示批注，它将返回到其默认位置。
批注中的默认用户名可以在工具》选项》常规中修改
批注的默认字体、字体大小、颜色可以在“windows 外观”中修改“工具提示”的相应内容
修改单个批注的字体和颜色？操作：视图－批注然后在批注上单击右键选择设置批注格式

第 25 页共 127 页

精通办公软件培训系列教材－Excel

1.4.2.4. 压缩图片
在excel 里面的批注插入图片吧,这往往会导致excel 文件变得很大!可以压缩它!(这个方法适用于
excel 中所有的图片!)方法是: 视图----工具栏----图片---然后用鼠标"图片"工具栏的压缩图片按钮
----按提示操作!这个方法效果很明显,,这是个很有用的方法!

1.4.4. 对象
插入
→
对象
office 文件都可以插入其他各种类型的文件。

新建
是新建的一个空白的其他各种类型文件

显示为图标
由文件创建
链接到文件
在excel 里面是否以文件图标形式显示

直接把外部做好的文件插到office 文件里面
与文件同步更新

选中

显示为图标
会出现

更改图标按钮
可以更改文件的
图标＆显示的名称(标题)

第 26 页共 127 页

基本操作

1.4.3.1. 公式编辑器
“公式编辑器”一直是老师们的热门话题，因为很多老师在备课、出试卷时经常要用到它，尤其是理
科老师，很多试卷或教案都是在和“公式编辑器”的“亲密接触”下完成的。

“公式编辑器””应用程序不是微软公司开发的，它是Design Science 公司的Math Type“ 公式编辑
器”特别版，是为Microsoft 应用程序而定制的。

★安装

“公式编辑器”不是Office 默认安装的组件，如果要使用它，重新安装Office 至如图1 所示步骤
时，在“Office 工具”中选择“公式编辑器”，从选项中选择“从本机运行”，继续进行安装就可以将“公
式编辑器”安装成功了。

★启动

在文档中就插入了公式
编辑窗口，此时文字与公
式处于混排状态;

如果勾选显示为图标
，
在工作表中插入的是公
式3.0 的图标。双击图标，
可打开一个独立的“公式

编辑器”程序窗口，此程序窗口与Excel 程序窗口是相互独立的。

在“公式编辑器”程序窗口中编辑完公式后，单击“文件→更新”命令，或者按F3 键，工作表中
的公式即被更新，若直接关闭了“公式编辑器”程序窗口，也可完成更新操作。
以下文字摘抄自网友,在此表示谢意,内容未删改,有需要的人士可仔细研读,本书作者由于工作接触
很少原因,对公式编辑器了解不多!

★工具栏的组成

创建公式主要是由“公式编辑器”工具栏来完成的，先来认识一下它吧：“公式编辑器”工具栏的
组成如图所示，在工具栏上排列着两行共19 个按钮，将鼠标箭头停留在按钮上，会自动显示各按
钮的提示信息。

插入→ 对象，
打开对话框(如图)，

如果不选中显示为图标
第 27 页共 127 页

精通办公软件培训系列教材－Excel

用顶行的按钮可插入150 多个数学符号，其中许多符号在标准Symbol 字体中没有，如果需要输入
特殊符号，尽管在这里查找好了。

底行的按钮用于插入模板或结构，它们包括分式、根式、求和、积分、乘积和矩阵等符号，以
及各种围栏。许多模板包含插槽(键入文字和插入符号的空间)。工具板上的模板大约有120 个(分组
显示)，我们可以通过嵌套模板(把模板插入另一个模板的插槽中)来创建复杂的多级化公式，但嵌套
的模板不能超过10 级。

若要在公式中插入符号或模板，可单击工具栏上的相应按钮，然后在显示的工具板中单击特定
符号模板。

★公式的创建

下面以如图4 所示函数为例，说明一下利用“公式编辑器”创建公式的过程。
启动“公式编辑器”，从键盘上输入“y=”，再从工具栏上的“分式和根式”模板中插入“二次根式”模板，
在模板的插槽中输入对数符号“log” 。现在需要输入对数的底二分之一了，因为二分之一在下标的
位置，所以先从工具栏的“上标和下标”模板中插入“下标”模板，要输入分式，还要在“下标”插槽中
再插入一个“分式”模板，这样就形成了嵌套，现在可以在“分式”模板的分子、分母上分别输入数字
“1” 和“2” 了，完成了对数底的输入。接下来切记先用键盘上的向右光标键移动插入点，退出“下标”
插槽，然后再输入英文字母“x” ，接着插入一个“上标”模板，在插槽中输入数字“2” ，完成了“x2” 的
输入，这样整个函数就制作完成了。

公式的编辑

★选定公式中的元素

能否准确、熟练地对公式中的元素进行选定，是对公式进行编辑的基础：可以用鼠标拖动的办
法进行选定，也可以按住“Ctrl” 键并单击修饰字符进行选定，还可以利用“Shift” 键和光标键进行选
定，而用鼠标左键双击也可以选定。

★定位、移动插入点

在“公式编辑器”中，插入点的水平线沿插槽底边闪动，竖线则沿插槽顶端到底端闪动，其大小
和形状表明要键入的下一个文字或要插入的下一个符号显示的位置。

利用鼠标和键盘都可以定位插入点，用鼠标定位插入点很简单，但有时面对很小的插槽的确很
难“下手”，这时应该考虑用键盘。

第 28 页共 127 页

基本操作

利用键盘在公式中移动插入点有多种方法：
1、首选考虑的是应用光标键，它可以对插入点准确地定位，利用向右键或向左键，可在当前
的插槽或模板中向右或向左移动一个单位，而利用向上键或向下键可上移一行或下移一行。

2、“Tab” 键对快速移动插入点有很大的帮助，按下“Tab” 键，插入点移动到插槽的结尾，如果
插入点已在结尾处，则移动到下一个逻辑插槽，若同时按下了“Shift” 和“Tab” 键，则插入点会移动
到上一个插槽的结尾处。

3、要想将插入点快速移动到当前插槽的开始或结尾，“Home” 和“End” 键是最佳的选择。
★复制和移动
对于公式的复制或移动，在其创建过程或编辑的过程中都可以进行，也可以在文档中对创建好

的公式进行操作，操作方法与对文本的操作类似。也可将公式编辑过程中选中的部分元素直接粘贴

到正文中，粘贴后内容是作为“公式”保存的。
★删除公式中的项目
对于模板和插槽，在进行删除时首先要选定它们，再利用“BackSpace” 键或“Delete” 键，就可以

快速删除，若单击“编辑→清除”命令也可删除。

若要恢复删除的内容，删除项目后，立即选择“编辑”菜单中的“撤消键入”命令即可，需要注意
的是，撤消操作只能执行一次。若要删除插槽，最好先将插槽清空再删除，因为对于有些插槽，如
果不先删除包含它们的模板，是不能将其删除的。
★给公式加编号

您可以在公式与文字混排时给公式加上编号，但有时在编辑公式过程中加上编号会更好一些，
例如在书写方程组时，在编辑公式过程中可以非常容易地给每个方程加上编号(如图5 所示)，但如
果在公式与文字混排时再给方程加编号，那就比较麻烦了。
公式的格式化

★应用内置样式
“公式编辑器”窗口的“样式”菜单中提供了“数学、文字、函数、变量、希腊字母”等几种内置样

式，对这几种样式灵活、合理地运用，会使您的公式更加规范和美观。
应用样式时可以用鼠标来切换，也可以用键盘快速指定：
数学Ctrl+Shift+=；文字Ctrl+Shift+E；
函数Ctrl+Shift+F；变量Ctrl+Shift+I；
希腊字母Ctrl+Shift+G；

第 29 页共 127 页

精通办公软件培训系列教材－Excel

矩阵向量Ctrl+Shift+B；

★更改内置样式

若提供的样式不能满足需要，可以单击“样式”菜单中的“其他”命令，在打开的“其他样式”对话
框中选择“公式编辑器”样式中没有的字体和字符格式。

★自定义内置样式

用“样式”菜单中的“定义”命令可以改变为每种内置样式(数学样式无指定字体和格式)定义的字
体和字符格式。

选择“样式”菜单中的“定义”命令，打开“样式”对话框(如图6 所示)，单击要定义的样式右侧的箭头，
列表框中会显示出默认打印机的有效字体，选择要使用的字体名称即可，若要使用加粗或倾斜字符
格式，可选中“字符格式”下的“加粗”或“倾斜”复选框。

★设置字符的字号

对内置样式的定义或改变，其实是对字符的字体进行设置，若要改变字符的字号，应使用“尺
寸”菜单中的相关命令。若想改变“尺寸”的大小，可单击菜单中的“尺寸→定义”命令，打开“尺寸”
对话框(如图7 所示)，对于各项在公式中对应的位置，在右侧预览中都有详细的提示，修改好各项
尺寸的大小后单击[确定]或[应用]按钮即可。

公式的排版

第 30 页共 127 页

基本操作

★调整元素间距

在“公式编辑器”窗口中，单击“格式→间距”命令，可打开“间距”对话框(如图8 所示)，在这里
可以对公式中各元素的间距进行详细地设置，如对行距、元素间距、字符的高
度、上标高度、根式间距等进行设置，对于每一项内容，对话框右侧都有预览说明和图示，若想恢
复系统的默认设置，只要单击[默认]按钮就可以了。

★增加空格

在编辑公式时，若要增加几个空格，您一定首先想到的是键盘上的空格键(Space)，输入空格试
一试？啊！怎么不行？先打开“样式”菜单瞧瞧吧，您一定选择的是“数学”样式吧，切换到“文字”样
式中再试一试？可以输入空格了。

其实在“数学”样式中也可以用空格键输入空格，只要将输入法切换到“中文输入法”状态就可以
了，为了输入一个空格，来回不断地切换真够麻烦的，其实可以用工具栏中的[插槽和省略号]按钮
(如图3 所示)，其中提供了几种增加元素间距(或空格)的选项。

★精确调整公式

如果要对公式进行非常精确的控制、移动等，必须利用“微移”命令，“微移”可精确控制公式中
各项目的位置，在调整模板插槽的项目时尤其有用，实现的方法是：利用“Ctrl+ 光标键”可以“微移”
所选项目。

★公式的对齐

在“公式编辑器”的“格式”菜单中，专门准备了几个对齐命令，其实还有一件对齐的秘密武器，
它使公式在任意位置都能准确对齐，它隐藏在工具栏的[间距与省略号]按钮中，如图9(圆圈标识的
符号)，在每行的对齐位置插入这个符号，各行就会自动对齐。

第 31 页共 127 页

精通办公软件培训系列教材－Excel

★公式与正文的排版
如果您在编辑完公式后马上就用鼠标在公式编辑框外单击，那么得到的公式将是“嵌入型”的公
式，如果需要进行图文混排，不又是一件很头痛的事吗？
其实根本不用担心，和对待其他“嵌入型”对象一样，我们可以右击该公式，选择“设置对象格

式”项，并在“版式”选项卡中将其转换为“紧密型”环绕就可以了，当然您也可选择其他的环绕形式。
实例分析
经过前面的介绍，对“公式编辑器”的应用也略知一二了，其实它不仅能制作数学公式，制作化

学方程式也是游刃有余的，下面我们就以中学化学中硝酸的分解方程式为例(如图10 所示)，介绍
一下如何应用“公式编辑器”。

★制作过程

1、将“公式编辑器”的“样式”切换到“文字”样式，尺寸为“标准”状态。

2、用键盘输入数字和元素符号，用大写的英文字母表示元素符号，对于下标的数字可以应用
工具栏上的“下标和上标模板”制作，这样即可快速完成分子式HNO3、NO2 、O2 和H2O 的输入。
3、从工具栏中的“箭头符号模板”中选择一个“向上箭头符号”当作气体上升符号。

4、在分子式HNO3 和NO2 之间插入“底线和顶线模板”中的“双线顶线模板”，在模板插槽中输
入“或光照”三个字。

5、从工具栏的“希腊字母(大写)”符号中选择“△”符号，使其作为加热符号，插入到方程式的最
后端(错了吧！加热符号怎么放到了这里？嘿嘿，别急，往下看)，至此对方程式的制作形成了如图
11 所示的初步结果。

6、接下来就是对方程式进行调整了，主要利用的是“公式编辑器”提供的“微移”命令，选中整
个“双线顶线模板”，按着“Ctrl” 键不放，再按下光标键中的向下键，模板向下移动，直至让双线(即
等号)垂直居中。同样采用“微移”方法，利用“Ctrl” 键和光标键，将“△”符号移动至等号的正上方，
这样“△”就正式成为了加热符号(哈哈！明白了吧)。若对某方面不满意的话还可继续进行微调，整
个方程式就这样制作完成了。

★操作中应该注意的问题

1、制作下标时您可能首先想到的是插入“下标模板”，如果下标出现的次数比较多，这样要不
断地进行鼠标和键盘的切换，可以试试先将下标的数字正常输入，再集中设置下标的方法：选中数

第 32 页共 127 页

基本操作

字后，单击工具栏上的“尺寸→下标”命令，再重复几次所有下标就可以搞定了。

还有一点小小的建议：若下标后面还有气体上升或沉淀下降等符号，要先将这些符号都输入好
后再设置下标，否则会出现麻烦哦。

2、在设置加热符号时，“△”符号最终到达了它的目的地，但现在你再选中“△”符号，咦？怎
么找不着位置了？插入点有点乱了。其实，当时将最后面的“△”符号“微移”(应该叫“宏移”了！)到
了等号的上面，表面上看，“△”不在方程式最后端了，实际上它还是位于方程式最后端。如果这里
没有搞明白，以后如果编辑、修改时用鼠标选中“△”可就困难了，试着用“Shift+ 光标”键吧。

在此我们应该明白一个道理：对于公式经过“微移”处理后的元素，“管理”它们的原则是：从哪
儿里来还归哪儿管。

3、在等号的制作中，若不书写反应条件，可从键盘上直接输入等号“抵挡”一阵，若需加上反
应条件，用它作为等号可就惨了，还是利用前面介绍的方法吧。

专题结束语

经过这一番的“亲密接触”，您是不是对“公式编辑器”更了解了呢？“公式编辑器”是Office 的组
件，所以它不仅能在Word 中应用，在其他的Office 程序如Excel 、PowerPoint 中都可以自如地使
用，俗话说的好：“熟能生巧”，只要您不断地探索，相信您一定会很好地驾驭我们的好帮手——“公
式编辑器”。还有一个专业公式编辑工具:mathtype 比Excel 的公式编辑器好用些!

第 33 页共 127 页

精通办公软件培训系列教材－Excel

1.4.5. 超链接
→
插入
超链接
要显示的文字：在excel 单元格里面显示的文字
查找范围：超链接文件在本地硬盘的位置
地址：互联网地址
书签：等同于本文档中的位置，直接链接到当前工作表的某个单元格，链接到其他表，链接到事

先定义好的书签（名称）

取消超链接：（有些功能excel2000, 无法实现!）

..取消单个自动超链接:ctrl+z, 或者先输入单引号,再输入信息!或者点击鼠标右键---取消超链接。
..
关闭自动超链接:工具---自动更正选项---键入时自动设置格式---取消Internet 及网络路径替换为超链接---
确定
..取消多个超链接:复制一个空白单元格---选中超链接的那些单元格----编辑---选择性粘贴---加---
确定
. 用VBA 取消所有的超链接语句:cells 。Hyperlinks 。Delete

实用技巧：
超链接打开指定表的单元格：

z 打开超级链接对话框

z 在地址文本框中键入具体路径：如：C:\Documents and Settings\Administrator\ 桌面\基本情

况.xls#Sheet1!$B$6 （这里特指出的是在工作薄名称和工作表名称中间加入“#”）

第 34 页共 127 页

基本操作

1.5.格式
1.4.2. 单元格
1.4.2.1. 数字
格式
→

单元格
→

数字
→

自定义
见图，（自定义格式的都可删除）

虽然Excel 为用户提供了大量的数字格式，但还是有许
多用户因为工作、学习方面的特殊要求，需要使用一些
Excel 未提供的数字格式，这时我们就需要利用Excel
的自定义数字格式功能来帮助实现用户的这些特殊要
求。

# 好象是格式在位符，0 表示数位站位符，如设置单元格式为“000”，则输入数字1 时，显示为
“001”。？一般表示一个字节位置。虽然Excel 为用户提供了大量的数字格式，但还是有许多用户因
为工作、学习方面的特殊要求，需要使用一些Excel 未提供的数字格式，这时我们就需要利用Excel 的
自定义数字格式功能来帮助实现用户的这些特殊要求。 Excel 的自定义格式使用下面的通用模型：正
数格式；负数格式；零格式；文本格式。在这个通用模型中，包含三个数字段和一个文本段：大于
零的数据使用正数格式；小于零的数据使用负数格式；等于零的数据使用零格式；输入单元格的正
文使用文本格式。我们还可以通过使用条件测试，添加描述文本和使用颜色来扩展自定义格式

#,0,? 这三个字符都是数字占位符，区别在于：

. 对于#，系统会自动忽略数字前后的0 值，如：格式设置为： #。## ，对于0103 。50 则只显
示103 。5；
. 对于0，系统直接用0 来补位，如：0。00，这也是常用的，如输入103。5 则显示103。50；
如格式设置为：00000 ，输入50 则显示成00050 ，这种也是常用的编号设置法；
. 对于?，它除了具有#号功能外(去除前后的无效的0 值)，还多了一个空格占位的功能，常用于

第 35 页共 127 页

精通办公软件培训系列教材－Excel

数值小数点对齐，如：格式设置为：?。??，当输入0103 。50 或103 。5 则系统显示为103 。5_(注：
为了便于理解用_来表示一个空格占位)。

在Excel 中创建自定义数字格式的方法
z 选择要设置格式的单元格或单元格区域。
z 单击"格式"菜单中的"单元格"命令，然后单击"数字"选项卡。
z 在"分类"列表中，单击"自定义"选项。
z 在"类型"框中，编辑数字格式代码以创建所需的格式。

在Excel 自定义数字格式使用如下的表达方式：
正数的格式负数的格式零的格式文本的格式

#,##0.00 [Red]-#,##0.00 0.00 "TEXT"@
在Excel 自定义数字格式的格式代码中，用户最多可以指定四个节；每个节之间用分号进行分隔，
这四个节顺序定义了格式中的正数、负数、零和文本。如果用户在表达方式中只指定两个节，则第
一部分
用于表示正数和零，第二部分用于表示负数。如果用户在表达方式中只指定了一个节，那么所有数
字都会使用该格式。如果在表达方式中要跳过某一节，则对该节仅使用分号即可。
自定义数字格式实例

. 自动添加文本
在日常财务工作中，常常需要在金额数字后加单位"元"，这时就可以使用"0.00 元"的自定义
数字格式，当用户在单元格中录入数字后，Excel 就会自动在数字后加上单位"元"。如果需
要将单元格中录入的数字"32" 自动转换成"高三十二班"格式，只要将单元格的数字格式定义
为"高[DBNum1]G/ 通用格式班"即可。

. 在自定义数字格式中使用颜色
要设置格式中某一部分的颜色，只要在该部分对应位置用方括号键入颜色名称或颜色编号即
可。Excel 中可以使用的颜色名称有[黑色]、[蓝色]、[青色]、[绿色]、[洋红]、[红色]、[白色]、
[黄色]八种不同的颜色，此外Excel 还可以使用[颜色X]的方式来设置颜色，其中X 为1-56
之间的数字，代表了56 种不同的颜色。例如：当用户需要将单元格中的负数数字用蓝色来
表示，只要使用"#,##0.00;[ 蓝色]-#,##0.00"自定义数字格式，用户在单元格中录入负数时，

第 36 页共 127 页

基本操作

Excel 就会将数字以蓝色显示。

. 在自定义数字格式中使用条件格式
在Excel 自定义数字格式中用户可以进行条件格式的设置。当单元格中数字满足指定的条件
时，Excel 可以自动将条件格式应用于单元格。Excel 自定义数字格式中可以使用如下六种标
准的比较运算符：

运算符含义
= 等于
> 大于
< 小于
>= 大于等于
<= 小于等于
<> 不等于

在Excel 中要想设置满足指定条件数字的格式，在自定义数字格式代码中必须加入带中括号
的条件，条件由比较运算符和数值两部分组成。例如：在学生成绩工作表中，当我们想以红
色字体显示大于等于90 分的成绩，以蓝色字体显示小于60 分的成绩时，其余的成绩则以黑
色字体显示，这时只需将自定义数字格式设置为"[红色][>=90];[蓝色][<60];[黑色]"即可。值
得注意的是，当你在以后需要继续使用刚才所创建的成绩条件自定义数字格式时，你会发现
在"单元格格式"的"自定义"分类类型中找不到"[红色][>=90];[ 蓝色][<60];[ 黑色]"格式，这是因
为Excel 自动将你所创建的"[红色][>=90];[蓝色][<60]"格式修改成"[[ 红色][>=90]G/ 通用格
式;[蓝色][<60]G/ 通用格式;[黑色]G/通用格式"，你只需选择此格式即可达到同样的使用效果。

. 隐藏单元格中的数值

在Excel 工作表中，有时为了表格的美观或者别的因素，我们希望将单元格中的数值隐藏起来，
这时我们使用";;;"(三个分号)的自定义数字格式就可达到此目的。这样单元格中的值只会在编辑
栏出现，并且被隐藏单元格中的数值还不会被打印出来,但是该单元格中的数值可以被其它单元
格正常引用。

通用模型的应用：
z 使用颜色
要在自定义格式的某个段中设置颜色，只需在该段中增加用方括号括住的颜色名或颜色编号。

第 37 页共 127 页

精通办公软件培训系列教材－Excel

Excel 识别的颜色名为：[黑色]、[红色]、[白色]、[蓝色]、[绿色]、[青色]和[洋红]。Excel 也识别按
[颜色X]指定的颜色，其中X 是1 至56 之间的数字，代表56 种颜色。

z 添加描述文本

要在输入数字数据之后自动添加文本，使用自定义格式为：＂文本内容＂@；要在输入数字数
据之前自动添加文本，使用自定义格式为：@＂文本内容＂。@符号的位置决定了Excel 输入的数
字数据相对于添加文本的位置。

z 创建条件格式
可以使用六种逻辑符号来设计一个条件格式：>（大于）、>=（大于等于）、<（小于）、<=（小
与等于）、=（等于）、<>（不等于）。

由于自定义格式中最多只有3 个数字段，Excel 规定最多只能在前两个数字段中包括2 个条件
测试，满足某个测试条件的数字使用相应段中指定的格式，其余数字使用第3 段格式。如果仅包含
一个条件测试，则要根据不同的情况来具体分析。如下列自定义格式：

[红色]；[绿色]；[蓝色]；：大于0 的数字显示红色，负数显示绿色，负数显示蓝色。

[红色][<5]＂小于5＂；[蓝色]G／通用格式：小于5 的数字则显示红色的＂小于5＂文本，其它数字按通用格
式显示蓝色。

输入自定义格式＂[红色]；[绿色]＂，单击[确定]后再返回[单元格格式]对话框，则会发现Excel
已经将格式规范修改为：[红色]／通用格式；[绿色]／通用格式Excel 自动添加了一个第三段，并指
定使用通用格式。

实际上，自定义格式的通用模型就相当于如下的条件格式：[>0]正数格式；[<0]负数格式；零
格式；文本格式。

另外，我们还可以运用自定义格式来达到隐藏输入数据的目的，；＃＃；0 只显示负数和
零，输入的正数则不显示；格式；则隐藏所有的输入值。

自定义格式只改变数据的显示外观，并不改变数据的值，也就是说不影响数据的计算。

补充说明：
在Excel 自定义数字格式使用如下的表达方式：
正数的格式负数的格式零的格式文本的格式
#,##0。00 [Red]-#,##0。00 0。00 "TEXT"@
在Excel 自定义数字格式的格式代码中，用户最多可以指定四个节；每个节之间用分号进行分隔，

第 38 页共 127 页

基本操作

这四个节顺序定义了格式中的正数、负数、零和文本。如果用户在表达方式中只指定两个节，则第
一部分用于表示正数和零，第二部分用于表示负数。如果用户在表达方式中只指定了一个节，那么
所有数字都会使用该格式。如果在表达方式中要跳过某一节，则对该节仅使用分号即可。

#：数字占位符(非0 值占位符) G/ 通用格式：用常规格式显示数字
@：文本占位符 *：重复下一个字符以充满列宽
，：千位分隔符\: 用这种格式显示下一个字符 ?: 数字占位符

. 实例：要求数值后面带单位（个，元，…），数值还可以进行数学运算

自定义
→
类型
→
输入数值格式，在后面再输入引号（一定要拼音状态下），引号里面添加所需

要的文字

. 实例：把计算结果为负值的显示为红色取整并在数字后面加上“仟元“
把单元格格式改为#,##0" 仟元";[红色]-#,##0"仟元"
如果不要负号可以写成#,##0" 仟元";[红色]#,##0" 仟元"

. 一个单元格内格式问题

一个表某一列是表示重量的，数值在1-1000000 之间的数不等。如果给他们加上单位，但要求是单
位是>99 吨，之下>999 是千克，其余的是克；如何办？答：[>9999]###.00," 吨";*,*.00" 千克"

. 创建自定义格式单元格中内容是“张三”，如果要显示为“李四”，

创建方法：选取单元格A1，执行格式/单元格命令或通过单击右键并在菜单中再单击单元格格式设
置，打开单元格格式对话框。选取数字选项卡，在分类中选取自定义项，在右侧的类别框中输入“;;;"
李四"”后，单元格显示则转换为“李四”。自定义格式代码结构用户可以指定一系列代码作为数

第 39 页共 127 页

精通办公软件培训系列教材－Excel

字格式来创建自定义格式，输入的“;;;"李四"”就是一组代码，它可以把单元格的所有文本内容显
示为“李四”。
. 代码结构组成代码码分为四个部分，中间用“；”号分隔，具体如下：
正数格式；负数格式；零格式；文本格式；只显负值改为：;-G/通用格式;;
创建自定义时使用的代码代码是设置自定义格式必不可少的部分，主要有数字（文本）代码和时间
（日期）代码两种。
. 数字和文本代码常用数字和文本代码：
u “G/通用格式”：以常规的数字显示，相当于“分类”列表中的“常规”选项。
例：代码：“G/通用格式”。10 显示为10；10。1 显示为10。1。
u “0”：数字占位符。如果单元格的内容大于占位符，则显示实际数字，如果小于点位符的数量，
则用0 补足。
例：代码：“00000”。1234567 显示为1234567；123 显示为00123
代码：“00。000”。100 。14 显示为100。140；1。1 显示为01。100
u “#”：数字占位符。只显有意义的零而不显示无意义的零。小数点后数字如大于“#”的数量，
则按“#”的位数四舍五入。
例：代码：“### 。##”,12。1 显示为12。10;12 。1263 显示为：12。13
u “？”：数字占位符。在小数点两边为无意义的零添加空格，以便当按固定宽度时，小数点可对
齐，另外还用于对不等到长数字的分数

例：分别设置单元格格式为“??。??“和“???。???”,对齐结果如下：
例：设置单元格自定义格式“# ??/???”。“1.25 ”显示为“1 1/4”
u “。”：小数点。如果外加双引号则为字符。
例：代码“0。#”。“11。23”显示为“11。2”
u “%”：百分比。
例：代码“#%”。“0。1”显示为“10%”
u “，”：千位分隔符。数字使用千位分隔符。如时在代码中“，”后空，则把原来的数字缩小1000
倍。
例：代码：“#，###”。“10000 ”显示为“10，000”
代码：“#，”。10000 显示为10。

第 40 页共 127 页

基本操作

代码：“#，，”。“1000000 ”显示为“1”。
u “/”：显示下一个字符。和“”””用途相同都是显是输入的文本，且输入后会自动转变为双引号
表达。不同的“/”是显后面的文本，双引号是显示双引中间的文本。
例：代码“\ABC”或“”ABC””。均显示为“ABC”
u “*”：重复下一次字符，直到充满列宽。
例：代码：“@*-”。“ABC”显示为“ABC-------------------”
u “_”（下划线）：留下一个和下一个字符同等宽度的空格
u “文本”：显示双引号中的文本。
例：代码：“”中国”@”显示为“中国河南”
u [颜色]：用指定的颜色显示字符。可有八种颜色可选：红色、黑色、黄色，绿色、白色、兰色、
青色和洋红。
例：代码：“[青色];[红色];[黄色];[兰色]”。显示结果为正数为青色，负数显示红色，零显示黄色，
文本则显示为兰色
u [颜色N]：是调用调色板中颜色，N 是0~56 之间的整数。
例：代码：“[颜色3]”。单元格显示的颜色为调色板上第3 种颜色。
u [条件]：可以单元格内容判断后再设置格式。条件格式化只限于使用三个条件，其中两个条件是
明确的，另个是“所有的其他”。条件要放到方括号中。必须进行简单的比较。
例：代码：“[>0]”正数”；[=0]; ”零”；负数”。显示结果是单元格数值大于零显示正数，等于0 显
示零，小于零显示“负数”。
U “！”：显示“"”。由于引号是代码常用的符号。在单元格中是无法用"""来显示出来“"”。要想
显示出来，须在前加入“！”（感谢yanjie 提供）
例：代码：“#!"”。“10”显示“10"”
代码：“#!"!"”。“10”显示“10""” 2。1。1。2 时间和日期代码常用日期和时间代码
u “YYYY”或“YY”：按四位（1900~9999 ）或两位（00~99 ）显示年
u “MM”或“M”：以两位（01~12 ）或一位（1~12 ）表示月。
u “DD”或“D”：以两位（01~31 ）或一位（1-31）来表示天。
例：代码：“YYYY-MM-DD”。2005 年1 月10 日显示为：“2005-01-10”
代码：“YY-M-D”。2005 年10 月10 日显示为：“05-1-10”
u “AAAA”：日期显示为星期。

第 41 页共 127 页

精通办公软件培训系列教材－Excel

u “H”或“HH”：以一位（0~23 ）或两位（01~23）显示小时
u “M”或“MM”：以一位（0~59）或两位（01~59）显示分钟
u “S”或“SS”：以一位（0~59）或两位（01~59）显示秒
例：代码：“HH:MM:SS”。“23:1:15 ”显示为“23:01:15”
u [H]或[M]或[SS]：显示大于24 小时的小时或显示大于60 的分或秒。
2。1。2 综合应用实例在实际工作中单元格自定义功能应用非常广泛，下面介绍几个常遇到的实例
2。1。2。1 设置万元显示要求：把12345 显示为1。2
代码：“0”。”,”
2。1。2。2 设置千元显示且四舍五入保留两位小数要求：把“12345 ”显示为：12。35
代码：“#。00,”
2。1。2。3 在数字中任意插入字符要求：把“20050512”设置为：2005-05-12
代码：“0000-00-00”
2。1。2。4 在文本或数字前添加字符要求：“郑州市”显示为“河南省郑州市”
代码：“”河南省”@”
要求：“1035010121”显示为“手机号码1034010121”
代码：“”手机号码”@”或“”手机号码”#”
2。1。2。5 用EXCEL 做目录要求：在每行中加入短横线填充，做成目录状，如图所示
A3 单元格格式代码：“@*-"1"

18 位身份证号码输入的几种解决方法
相信在EXCEL 这个软件中如何输入15 位或18 位身份证号码的问题已经困挠了许多人，因为在
EXCEL 中，
输入超过11 位数字时，会自动转为科学计数的方式，比如身份证号是：123456789012345，输入后
就变成
了：1。23457E+14,要解决的方法有非常非常。。。。多种哦，呵呵，现在为大家说几种比较简单快
速的方法：

1、在A1 单元输入号码时，在前面先输入’号，就是：’123456789012345,这样单元格会默认
为该单
元为文本方式，会完整显示出15 个号码来，而不会显示那令人讨厌的科学计数方式来了。

第 42 页共 127 页

基本操作

2、如果已经输入了大量的号码，全部以科学计数显示的话，而又懒的按照上面的方法一个一
个的重新
输入的话，也有这个好方法哦，比如已在单元A1：A100 输入了号码，按以下步骤做：》选择单元
A1：A100 》
单击鼠标右键，设置单元格式》选择自定义，在‘类型’中输入‘0’即可，轻松搞定，呵呵！！

3、还有一种用函数来解决的方法：在A1：A100 已经输入大量的号码时，在B1 单元中输入：
=trim(' 'a1),
注意两个’之间是空格，这个公式的意思:先在A1 单元15 位号码前加个空格符号，然后用trim 这
个函数
把空格符号去掉。输完后，B1 单元也是显示出完整的15 位数字了。

第 43 页共 127 页

精通办公软件培训系列教材－Excel

自定义格式列表：

自定义
类型
自定义格式单元格输入显示为解释
使用文本
#,##0 "元人民币" 1000 1,000 元人民币数字自动加入后缀,数值可运算
"答案: "通用格式1000 答案: 1000 数字自动加入后缀,数值可运算
"总额是"#,##0" 美元" 1000 总额是1,000 美元数字自动加入后缀,数值可运算
#,##0, 123456789 123,457 因为 #,## 中有千位分隔符，所
以返回的数据保留分隔符；格式
中最后用千位分隔符，所以数据
显示时，千位后自动四舍五入
了，实际数值没有变，
分隔长
数字
#,##0, 12345678912 12,345,679
#,##0, 1234 1
数据确
认
0。00;"仅显示正数!" 123 123。00
如果是负数，仅显示后面的描
述，
实际数值没有变。
（表示正数格式的第一个节正
常书写，表示负数格式的第二个
节只用了描述）
0。00;"仅显示正数!" -123 仅显示正数!
0。00;"仅显示正数!" 0 0。00
值为0 时左右有“—”
0 用破
折号
0。00;"仅显示正数!" 123。56444 123。56
#,##0_);(#,##0);-0-_) 0 -0-
#,##0_);(#,##0);-0-_) 12。2 12 值为正时常规显示
#,##0_);(#,##0);-0-_) -102 (102) 值为负时用括号括起来
电话号
码(###) ###-### 8523776596295
(852377)
659-6295
###"/"###-#### 8523776596295 852377/659-6295

第 44 页共 127 页

基本操作

421897322

社会福利号

日期格
式
mmmm-yy 1995-6-1 June-95
mmmm d, yyyy 1995-6-1 June 1, 1995
dddd 1995-6-1 Thursday
mmmm d, yyyy (dddd) 1995-6-1
June 1, 1995
(Thursday)
设置不
同颜色
"It's" dddd 1995-6-1 It's Thursday
[红色][<1]0。0%;[蓝
色][>=1]#,##0;G/通用格
式
1 1
[红色][<1]0。0%;[蓝
色][>=1]#,##0;G/通用格
式
-1 -100。0%
[红色][<1]0。0%;[蓝
色][>=1]#,##0;G/通用格
式
45 45
其他符号
G/通用格式;G/通用格
式;G/通用格式;[红色]G/
通用格式
基地电子(XX)
有限公司
基地电子(XX)有
限公司
最后一个节规定了文本的颜色
G/通用格式;G/通用格
式;G/通用格式;[红色]G/
通用格式
234 234
.G/通用格式2004 .2004
###-##-#### 421897322 421-89-7322

"社会福利号"
第 45 页共 127 页

精通办公软件培训系列教材－Excel

###-##-#### 421-89-7322
=C113-TODAY() 2006-12-10 已过期100 天!
仅以文字的格式表示原来的数，
描述正数;"负数";"零";"文本" 32 正数不影响D53 至D56 单元格中数
值，可看后面的运算就知道。
正数;"负数";"零";"文本" -32 负数
正数;"负数";"零";"文本" 0 零
正数;"负数";"零";"文本" 基地电子文本隐藏单元格内容，但编辑栏里还
能看到的。不影响引用，运算。
隐藏另一种隐藏是把字体设为白色。
;;; 1234 这两种方法都是表面的隐藏，都
不能打印。
;;; -145
条件语有要求的隐藏，大于100 的数值
[>100]#,000;;; 99
句显示，否则都不显示。其余同上。
[>100]#,000;;; 102 102
[>100]#,000;;; -54
[>100]#,000;;; 基地电子

第 46 页共 127 页

基本操作

1.4.2.2. 对齐
单元格格式的对齐一般功能很简单，如果做些复杂可实现各种排版效果

注意：尽量不要使用“合并单元格”以免使用函数麻烦！如果需要居中处理，最好用跨列居中

1.4.2.3. 字体
功能常见，不再罗嗦

1.4.2.4. 边框
常用功能！此功能与其他操做结合，可以发挥意想不到的效果

绘制有三条斜线的表头
z 用绘图工具画出斜线>>画方框>>内添加文字>>去边框
z 引用WORD 中的，然后再复制过来就可以！

1.4.2.5. 图案
此功能多数是与其他操做结合使用

第 47 页共 127 页

精通办公软件培训系列教材－Excel

1.4.2.6. 保护
非常有用的功能，尤其对于做汇总表的人！

我们经常遇到这种情况：
你辛苦做成一个表格，发给别人填写，别人给你乱改表格！
本来很简单的汇总数据的工作变得很繁琐，下面的例子将
彻底解决你的烦恼！
保护的效果：
保护自己的数据表结构不被修改，公式内容不被看见；
需要输入数据的区域可以自由编辑，否则不可改动！

. 方法1。

保护(隐藏)表格公式不被修改(看见),而数据区却可以修改。解决如下:
方法1。选中表的所有区域→鼠标右键”设置单元格格式”→”保护”→取消”锁定”,”隐藏”

→选中公式区域→鼠标右键”设置单元格格式”→”保护”→选中”锁定”,”隐藏”→”工具”
→”保护”→”保护工作表”→输入”密码”→”确定”
. 方法2。(适合复杂大表)
说明：
. 浅色（黄色）是标题栏

. 深色（绿色）是公式区

. 中间（白色）是数据区

. 要求数据区可以输入修改数据，标题栏只能浏览，不可修改；
公式区能看见结果，不能修改公式，不能看见公式。

做法：
z 全选工作表→单元格格式→保护→取消锁定。
z 编辑→定位→定位条件→选公式→按确定
z 选定要保护的单元格，单元格格式→保护→勾选锁定、隐藏。
z 工具保护→工作表→选定锁定的内容→输入密码。

第 48 页共 127 页

基本操作

把数据彻底隐藏起来工作表部分单元格中的内容不想让浏览者查阅，只好将它隐藏起来了。

1.选中需要隐藏内容的单元格（区域），
“单元格格式”“数字”“分类”“自定义”
在“类型”下面的方框中输入“；；” （三个
英文状态下的分号）。
2.再切换到“保护”标签下，选中其中的“隐
藏”选项，按“确定”按钮退出。
3.执行“工具→保护→保护工作表”命令，
打开“保护工作表”对话框，设置好密码后，
“确定”返回。
经过这样的设置以后，上述单元格中的内容
不再显示出来，就是使用Excel 的透明功能
也不能让其现形。
提示：在“保护”标签下，请不要清除“锁
定”前面复选框中的“∨”号，这样可以防
止别人删除你隐藏起来的数据。
1.4.3. 工作表
z 重命名：更改工作表名字
z 隐藏：隐藏工作表
z 背景：为空白背景的工作表添加漂亮的图片
z 工作表标签颜色：更改工作表标签的颜色，便于工作区分

1.4.4. 自动套用格式
不常用，也不实用的！略去。
范例
一个EXCEL 表格设置好文本样式格式后, 如果从别的地方复制过的东西，文本的样式就改变,那么
怎样保护EXCEL 的样式?
答: 全选

选择性粘贴(具体操作参见第二节编辑) 列宽粘贴;
第 49 页共 127 页

精通办公软件培训系列教材－Excel

1.4.5. 样式
如果您经常使用相同的格式选项设置工作表中的单元格，可能需要考虑创建一种格式样式（格式选
项的集合），它可与工作簿一起保存，并且每当您设置具有相同属性的信息的格式都可以时使用它。
在创建新的格式样式或修改现有格式样式后，您可以在工作簿的任意工作表中使用该样式，也可以
将该样式复制到其他打开的工作簿中。
z 创建自己的样式
通过使用“样式”对话框（“格式”菜单，“样式”命令），您可以使用多种方法创建自己的样式。
创建新样式最简单的方法是在具有要将其分配给样式的格式特征的单元格基础上进行创建。因为您
使用自己的工作表格式来定义样式，所以这被称为按示例创建样式。
如果您在创建样式之前没有设置单元格格式，“样式名”框中将显示“常规”样式。除“常规”样
式外，Excel 还预定义了“千位分隔”、“货币”和“百分比”样式。您可以修改其中的任一样式（或
您定义的其他任何样式）。
z 基于现有格式创建新样式
在“格式”菜单上，单击“样式”。在“样式名”框中，输入新样式的名称。
注释更新后的样式将在整个工作簿中进行更改。
z 合并其他工作簿中的样式
创建新样式后，您只能在从中创建该样式的工作簿中使用它—新样式保存在当前工作簿中并且不
会显示在其他工作簿中。如果您要将样式合并到的工作簿具有相同的样式名，则新样式可能会覆盖
那些现有样式并应用于整个工作簿。
打开源工作簿（要从中复制样式的工作簿）和目标工作簿（要将样式复制到的工作簿）。
激活目标工作簿在“格式”菜单上，单击“样式”。单击“合并”。

第 50 页共 127 页

基本操作

在“合并样式来源”框中，选择要从中复制样式的工作簿（源工作簿）的名称，然后单击“确定”。
Excel 会将源工作簿中的所有样式都复制到目标工作簿中。如果源工作簿包含与目标工作簿中的样
式同名的格式样式，将会出现一条警告消息，询问您是否仍要合并具有相同名称的样式。如果单击
“是”，这些样式将被合并，源样式将应用于整个工作簿。
注释“撤消”命令无法还原合并结果。应弄清楚是否确实要将源工作簿中的所有样式都复制到目
标工作簿中。在开始合并之前，您可能还需要删除任何不需要的样式。
z 删除自定义样式
可以删除任何不再需要的自定义样式。不能删除“常规”样式。此外，如果删除“货币”、“千位分
隔”或“百分比”样式，将无法使用“格式”工具栏上的“千位分隔”、“货币样式”或“百分
比样式”按钮。删除样式:在“格式”菜单上，单击“样式”。在“样式名”下拉列表中，选择
要删除的自定义样式，然后单击“删除”。

还有一种样式功能:引用样式: A1 和R1C1
A1 引用样式: 与Lotus123 接轨
R1C1 引用样式: 延续Multiplan 的行列表示法
在创建Excel 时，微软公司就知道使用行列表示单元格和公式的意思。如果只是考虑这个因素，把
Excel 设计成行列引用的话，就会失去习惯于用Lotus123 的广大客户。微软决定把这两种引用结合
起来，让用户自己选择喜欢的样式，由此就出现了两种引用。
然而，从实用角度讲R1C1 引用就像坐标系一样，只是强制在行前加上R，在列前加上C。这样的
表示可很容易用于编制程序，关系可一一对应，检查也方便。当前单元格就表示为RC，而不是R0C0。

1.4.6. 拼音指南
在word 中添加拼音,再复制粘贴到excel 中的
当在日文版的 Microsoft Excel 中显示拼音指南并输入日文汉字（日文中使用的中国汉字）时，Excel
将自动向拼音指南中添加拼音符号。当在非日文版的 Excel 中显示拼音指南时，如果向单元格中
输入汉字，则系统不会自动添加拼音符号。通过修改拼音指南可以手动添加拼音符号。可以向在单
元格中输入的汉字中添加拼音指南如果安装了 Microsoft 中文输入法 2.0 或更高版本，则能够自
动将汉语拼音标注在选定的中文文字上。
WORD 文档的拼音指南不能使用,需要安装IME2.0(微软拼音输入法) 以上版本.

第 51 页共 127 页

精通办公软件培训系列教材－Excel

1.4.7. 条件格式
1.5.4.1. 重复数据（实用的技巧）：
z 方法一第二个及以上重复数据标注特定格式
=COUNTIF($D$11,D1)>1 （针对D 列,数据是从D1 开始的）=COUNTIF($A$97:A97,A97)>1 (针
对A 列,数据是从A97 开始) 操作：:选中数据区→ :"格式"→"条件格式" （窗口）→。在条件
上选择"公式"→输入"方法一"的公式：=COUNTIF($A$97:A97,A97)>1) →设置单元格格式
注意：1。IF 语句可以屏蔽都是0 时,条件格式无效；2。注意公式内容和数据区第一行是关联的；3。
注意锁定符号位置

z 方法二把所有重复数据设置特定格式

=COUNTIF(D,D1)>1 （针对D 列,数据是从D1 开始的）

=COUNTIF(A:A,A97)>1 (针对A 列,数据是从A97 开始的)

操作：选中数据区→"格式" →"条件格式" →。选择"公式" →。输入"方法二"的公式→3。设置单元

格格式

注意公式内容和方法一是不同的

说明：
z 有人说可以用排序啊，问题是excel 排序只能支持1000 行数据，反应也慢！
z 如果是剔除重复数据，选出不重复唯一值，可参见1。7。2 高级筛选
z 如果是选出重复数据，参见第二章函数

第 52 页共 127 页

基本操作

1.5.4.2. 判断单元格的值
z 判断错误值=isERROR(A1) ：是错误值，单元格填充红色
z 判断文本=isTEXT(A1) ：如果是文本，单元格填充黄色
z 判断数值=isNUMBER(A1) ：如果是数值，单元格填充蓝色

说明：
主要利用is 类函数，具体操纵和上面的设置重复值类似，不再累述！

1.5.4.3. 判断其他,设置当前
要求：客户名城单元格格式随着订单值变化，怎么做？
选中数据区（客户栏） "格式" →"条件格式" →。选择"公式" →。输入公式（“＝E196<0”）设
置单元格格式

→
→
第 53 页共 127 页

精通办公软件培训系列教材－Excel

1.5.4.4. 标注含有公式单元格
z 选中待处理区域，使用条件格式
z 选择公式，公式栏输入＝S，设定格式
z 效果如图：

1.5.4.5. 自动标示当前行颜色
在表内任意单元格（这里就选择A10 ）定义名
称（s），
公式栏输入：=GET.CELL(48,Sheet3!A10)
这个是宏表函数, 48 意思是单元格是否含

有公式（1，0）

z 选择整个工作表：。鼠标点击图示地方，
z 格式：条件格式
z 确定条件格式：1 选择公式；2 输入：=CELL("row")=ROW()

○○
○

3 设置格式

z 切换代码窗口：工作表点击右键查看代码（快捷键：ALT+F11）

第 54 页共 127 页

基本操作

z 切换到 ThisWorkbook 的代码窗口
z 这里选择Workbook

z 选择：SheetSelectionChange

z 输入代码：Application 。ScreenUpdating = True

1.5.4.6. 标注小计和总计的行
选中数据区（或者选择整个工作表）→ 格式→条件格式→选择"公式→输入公式（=RIGHT(RC,1)="

计" ）→ 设置单元格格式（设定字体、边框、图案）

小例子
在一个区域中（b1:G7）找到A 行一组数据中的某个数并自动变红

答：选定数据区b1:g7，设置它的条件格式为=(COUNTIF($A$1A$7,b1))
注意,b1为相对引用,这里输入所选区域的第一个取值，所选区域会自动填充。

第 55 页共 127 页

精通办公软件培训系列教材－Excel

1.6.工具
1.6.1. 共享工作簿
操作：【工具】→〖共享工作簿〗
1、什么是共享工作簿
共享工作簿是指允许网络上的多位用户同时查看和修订的工作簿（说白了就是几个人对同一工作簿
同时打开并操作,其好处是不言而喻的）。保存工作簿的用户可以看到其他用户所做的修订（必须使
用 Excel 97 或更高版本来修改共享工作簿）。每次保存共享工作簿时，该工作簿就会按其他用户在
您上次保存之后所做的修订进行更新。如果要打开共享工作簿以监视进程，则可在指定的时间间隔
内让Excel 自动更新工作簿，可以保存工作簿也可以不保存。
那么如何创建共享工作簿呢？
在【工具】菜单里的〖共享工作簿〗子菜单，将编辑选项卡里的“允许多人编辑”勾选上即可多人
同时操作工作簿了。
2、修订冲突
在向共享工作簿保存更改时，正在编辑该工作簿的其他人员可能已经将自己的更改保存在了同一个
单元格中。这种情况下就会产生修订冲突，这时将出现冲突解决对话框以便决定保存哪个人的更改。
若要保留自己的修订或其他人的修订并转到下一个修订冲突上，请单击“接受本用户”或“接受其
他用户”。
若要保留自己的所有剩余修订或所有其他用户的修订，请单击“全部接受本用户”或“全部接受其
他用户”。
若要使自己的修订覆盖所有其他用户的修订，而且不再看到“解决冲突”对话框，请关闭此功能。
3、不可用于共享工作簿中的功能
由于Excel 的部分有一些功能在工作簿共享后只能查看或使用，而不能更改，所以在工作簿共享前
应设置好这些功能。只有在停止使用共享工作簿的条件下，才能使用下面功能：

第 56 页共 127 页

基本操作

不可用的功能替换方法
创建列表无。
成块插入或删除单元格可以插入整行和整列。
删除工作表无。
合并或拆分合并的单元格无。
添加或更改条件格式单元格值更改时，现有条件格式继续存在，但不可更改这些格式
或重定义条件。
添加或更改数据有效性键入新值时，单元格继续有效，但不能更改现有数据有效性的设
置。
创建或更改图表或数据透视表可以查看现有的图表和报表。
插入或更改图片或其他对象可以查看现有的图片和对象。
插入或更改超链接现有超链接继续有效。
使用绘图工具可以查看现有的图形对象和图形。
指定、更改或删除密码现有密码仍然有效。
保护或不保护工作表或工作簿现有保护仍然有效。
创建、更改或查看方案无。
创建组及分级显示数据可以继续使用现有分级显示。
插入自动分类汇总可以查看现有分类汇总。
创建数据表可以查看现有数据表。
创建或更改数据透视表可以查看现有报表。
写入、记录、更改、查看或分配宏可以运行现有的只使用可用功能的宏。可以将共享工作簿的操作
录制在一个存储于其他非共享工作簿的宏中。
更改或删除数组公式原有数组公式继续正确地计算。

注意:

1：若要查看另外还有谁打开工作簿，请单击“工具”菜单中的“共享工作簿”，再单击“编辑”选
项卡。
2：如果希望定期自动更新其他用户的更改并加以保存或不保存，请单击“工具”菜单中的“共享
工作簿”，再单击“高级”选项卡，然后在“更新”下，单击所需的选项。
3：其他用户未保存前，其对共享工作簿进行的修订不会显示。因而要停止共享工作簿必须请所有
其他用户保存并关闭共享工作簿。否则，他们未保存的数据将会丢失。
第 57 页共 127 页

精通办公软件培训系列教材－Excel

1.6.2. 保护
保护工作表：保护当前工作表！实际应用见1。5。1。6 的单元格保护
允许用户编辑区域：设定用户可编辑区域，与保护工作表功能关联
保护工作簿：保护工作簿，可编辑工作表，不能删除,移动,添加工作表
1.6.3. 宏
如果要运行带宏的excel 文件（例如自定义公式，函数），要先设置宏的安全性为“中”，一般
系统默认是“高”，不能运行宏。工具→宏

→
安全性→安全级→中
1.6.4. 加载宏
如果把自定义的公式（函数）变成系统公式（函数）一样使
用，就需要加载宏。
1。首先复制 *.xla 文件（如果不是 *.xla 文件，需要把含有自定
义公式的excel 文件另存为扩展名为 .xla 的文件）

2。工具→加载宏
出现浏览窗口（如图2），把刚才
复制的文件粘贴到窗口里面，或者复制下面的文件夹里面：

→
浏览
C:\Documents and Settings\XXX\Application Data\Microsoft\AddIns
3。选中自定义的公式（函数），如图1
4。以后需要此公式，就如同用系统自带的公式一样

第 58 页共 127 页

基本操作

1.6.5. 自动更正选项
例如：输入公司简称“基地电子”就能自动成为“基地电子(惠
州)有限公司”，操作步骤：
出现如图“替换”框
输入“基地电子”，“替换为”框输入“基地电子(XX)有限公

→
工具→自动更正选项
自动更正
司”

z 输入特殊字符
打开excel, 按住Alt 键,按小键盘上的42677, 得到符号Φ,复制这个符号，"替换"栏输入一个字符,例如

(m)----替换为----按ctrl+v, 粘贴符号Φ----确定
以后你需要这个Φ的时候,输入(m),excel 就把它替换为Φ,很方便。
记住:你随时可以用ctrl+z 组合键来取消一个自动更正项目。(又是这个ctrl+z, 它非常有用,在取消超
级链接也用到它)
这个非常有用的小技巧,例如在使用万分号等其他特殊字符的时候,也可以采用。
z 自动更正输入的文件
在excel 中,创建的自动更正项也适用于其它的office 应用程序(反之亦然)。输入的自动更正项都存
储在一个*。acl 文件里。该文件的位置可能会不一样,而实际的文件名取决于office 的语言版本。如
果用户使用的是美国英语版本的office, 那么这个文件的名称是mso1033 。acl,它的保存位置大概是
C:\Documents and Settings\<username>\Application Data\Microsoft\office
如果用户创建了一些自定义的自动更正项,并且打算和同时共享这些项,那么只要只要复制本机上的

*。acl 文件,然后将其放到同事的相应目录下即可。注意,替换*。acl 文件会取消同事已经定义好的
自动更正选项!。。
自动更正选项：类似拼音输入法的词组记忆功能；
第 59 页共 127 页

精通办公软件培训系列教材－Excel

1.6.6. 自定义
如前项

→
→
视图
工具栏
自定义
使用“分离式”工具栏

有5 个excel 工具栏控件可以与工具栏“分离”，可以另外创建一个自由浮动的小工具栏。这个
小技巧，连很多熟悉excel 的人都不知道！ 5 个用于设置的格式的工具栏是：
1 边框2 填充颜色3 字体颜色4 自选图形5 线条颜色
上面每个控件都带有一个箭头十字，单击它会弹出一个列表！如果你单击调色板的顶部，就创建
了一个新的工具栏。如图（填充颜色的）：

第 60 页共 127 页

[ 本帖最后由 blueskyandwater 于 2010-5-2 00:51 编辑 ]

普及衛星電視才是中華民族通往民主自由之路

TOP

blueskyandwater

围观

驴民高工

Rank: 6 Rank: 6

自由人

4^# 大中小发表于 2010-5-2 00:25 只看该作者

基本操作

1.6.7. 选项
1.6.7.1. 视图
z 启动任务窗格→ 选中，每次打开excel 都启动任务窗格
z 编辑栏：是否显示编辑栏
z 状态栏：excel 窗口最底下的一栏，是否显示
z 任务栏中的窗口：

：相当于视图
任务窗格
z 批注：显示批注方式，不显示；显示标识符，显示标识符和批注
z 对象：显示对象方式，全部显示，只显示占位符，隐藏

窗口选项：

z 自动分页符：窗口显示分页符号（虚线），

z 公式：是显示公式，还是显示结果

z 网格线：是否显示网格线

z 网格线颜色：设置网格线的颜色

z 行号列标：是否显示行号列标

z 分级显示符号：

z 零值：是否显示零值

z 水平滚动条：是否显示水平滚动条

z 垂直滚动条：是否显示垂直滚动条

z 工作表标签：是否显示工作表标签

第 61 页共 127 页

精通办公软件培训系列教材－Excel

1.6.7.2. 重新计算
z 计算：是否自动重算工作表公式
z 迭代计算：一般不用
工作表选项：
z 更新远程引用：是否更新工作簿外面的引用；
z 以显示值为准：按照显示值确定数据值，会更改文件所有表格的格式
z 1904 年日期系统：更改日期设置
z 保存外部链接数据：引用外部数据是否保存数据
z 接收公式标志：

第 62 页共 127 页

基本操作

1.6.7.3. 编辑
z 单元格内部直接编辑：不能在单元格编辑，只能在编辑栏编辑
z 单元格拖放功能：是否可以使用单元格拖放功能
z 按Enter 键后移动方向：Enter 键移动方向，默认向下
z 自动设置小数点：小数点位数
z 对象随同单元格剪切复制和排序：插入的图片图形等是否随同单元格变化二变化
z 请求自动更新链接：是否请求更新链接
z 记忆式键入：是否记忆已经输入的数据

第 63 页共 127 页

精通办公软件培训系列教材－Excel

1.6.7.4. 常规
z R1C1 引用样式：选中则表的列由字母ABCB 变成数字123
z 忽略其他应用程序：如果遇到双击 Excel 文件只是出现Excel 程序时候,把这个选项去掉
详见 5.4 双击Excel 文件没有反映
z 函数工具提示:使用函数时候是否出现提示
z 最近使用的文件列表：（在菜单项文件尾部）保留最新使用的文件数，
z 新工作簿内的工作表数：新建文件有多少表;如果需要很多表,不需要一个个的新建,设置此处
z 默认文件位置：保存文件默认位置
z 启动时打开此项的所有文件：启动excel 时候同时启动的文件
z 用户名：文件的属性之一,更改此处,也影响批注的标题

1.6.7.5. 1-2-3 的帮助：
兼容以前软件的设置

第 64 页共 127 页

基本操作

1.6.7.6. 自定义序列：
自定义序列的使用操作：

z 在单元格输入需要的序列→ 点击添加序列的折叠按钮(如图2)

..选中序列→点击导入,在自定义序列的列表框最后就出现了(如图3)
..选中数据区，数据→排序→选项
z 选择自定义排序次序，点击小▼，
z 选择刚才导入的自定义序列
z 选择排序方式，确定

第 65 页共 127 页

精通办公软件培训系列教材－Excel

把基数词转换成序数词
将英文的基数词转换成序数词是一个比较复杂的问题。因为它没有一个十分固定的模式：大多数的
数字在变成序数词都是使用的“th”后缀，但大凡是以“1”、“2”、“3”结尾的数字却分别是以“st”、
“nd”和“rd”结尾的。而且，“11”、“12”、“13”这3 个数字又不一样，它们却仍然是以“th”结
尾的。因此，实现起来似乎很复杂。其实，只要我们理清思路，找准函数，只须编写一个公式，就
可轻松转换了。不信，请看：“=A2&IF（OR（value（RIGHT（A2,2） ={11,12,13}）,〃th〃,IF（OR
（value（RIGHT（A2） ={1,2,3,},CHOOSE（RIGHT（A2）,〃st〃,〃nd〃,〃rd〃）,〃th〃））”。该
公式尽管一长串，不过含义却很明确：①如果数字是以“11”、“12”、“13”结尾的，则加上“th”
后缀；②如果第1 原则无效，则检查最后一个数字，以“1”结尾使用“st”、以“2”结尾使用“nd”、
以“3”结尾使用“rd”；③如果第1、2 原则都无效，那么就用“th”。因此，基数词和序数词的转
换实现得如此轻松和快捷。

第 66 页共 127 页

基本操作

1.6.7.7. 错误检查
此表详细列出系统的错误检查的项目，如果不想出现某个错误提示，可以设置此处。

1.6.7.8. 安全性
设置excel 文件的密码,详见1.1.1 设置密码. Excel 文件的密码只能防住菜鸟！不推荐使用多级加密。

1.6.8. 数据分析
如果需要一些复杂的特别数据分析，需要添加分

析工具：工具→数据分析
此项复杂不常用，本书不做介绍

第 67 页共 127 页

精通办公软件培训系列教材－Excel

1.7.数据
1.6.1. 排序
数据
排序
是常用的排序，本书不详述基础操作！主要讲些实用的排序技巧

z

可按三个关键字进行排序，还可选择排序的方向（按行或列）、排序的方法（按字母排序按笔划
排序）。排序的功能比较简单，主要了解一下排序的顺序。
在按升序排序时，Microsoft Excel 使用如下次序（在按降序排序时，除了空白单元格总是在最后
外，其他的排序次序反转）
在按字母先后顺序对文本项进行排序时，Excel 从左到右一个字符一个字符地进行排序。文本以
及包含数字的文本，按下列次序排序:
0 1 2 3 4 5 6 7 8 9 （空格）! " # $ % & ( ) * , . / : ; ? @ [ \ ] ^ _ ` { | } ~ + < = > A B C D E F G H I J K
L M N O P Q R S T U VWX Y Z
在逻辑值中，FALSE 排在 TRUE 之前；所有错误值的优先级相同；空格始终排在最后。
z 带下划线的排序

.订单号曾经见过类似”01011061_12 ”这样带有”_”字符的,如果对它们排序会很乱，怎么解决？

→
解决如下:选中要处理的数据→数据→分列→分隔符号→下一步
（分列具体操作详见1.7.7

分列）→下一步
→在目标区域后选择处理后的数据地址→
→选中其他,在其他里面填入”_”
完成→分别按
照你的要求排序→空白单元格输入”= 排好序的第一组数据的第一个单元格&_&排好序的第二组数
据的第一个单元格→向下拉如图:
用连字符“&”来合并文本 :如果我们想将多列的内容合并到一列中，不需要利用函数，一个小小
的连字符“&”就能将它搞定（此处假定将B、C、D 列合并到一列中）。

1.在D 列后面插入两个空列（E、F 列），然后在D1 单元格中输入公式：=B1&C1&D1。
2.再次选中D1 单元格，用“填充柄”将上述公式复制到D 列下面的单元格中，B、C、D 列的内容
第 68 页共 127 页

基本操作

即被合并到E 列对应的单元格中。

3.选中E 列，执行“复制”操作，然后选中F 列，执行“编辑→选择性粘贴”命令，打开“选择性
粘贴”对话框，选中其中的“数值”选项，按下“确定”按钮，E 列的内容（不是公式）即被复制
到F 列中。
4.将B、C、D、E 列删除，完成合并工作。
提示：完成第1、2 步的操作，合并效果已经实现，但此时如果删除B、C、D 列，公式会出现错误。
故须进行第3 步操作，将公式转换为不变的“值”。
1.6.1.1. 随机排列
行不变，列按照随机排列！在一个空行中输入 =rand() 然后向后填充。选中要排序的范围后排序，
在排序选项中选择“按行排序”，并以刚才填充了随机函数的行作为主要关键字。

1.6.1.2. 自定义排序
z 自定义序列的使用例子详见选项中的自定义序列，二者结合运用才能发挥作用

1.6.1.3. 按字体颜色排序
EXCEL 能否按单元格内文字的颜色排序，或把相同颜色的行集中到一起，答案是：可以的！如图：

z 命名名称： Color （=GET 。CELL(24,名称!$A7)+RAND()*0）
z 在B 列单元格输入＝Color
z 按照B 列数值排序

第 69 页共 127 页

精通办公软件培训系列教材－Excel

注意：

z 这里的名称命名时候，当鼠标放在 7 行命名，则公式是：=GET 。CELL(24, 名
称!$A7)+RAND()*0 ；如果是10 行，则是=GET。CELL(24,名称!$A10)+RAND()*0 ；这里的相
对引用技巧，在条件格式里面同样适用！

z 这里使用了宏表函数，关于此函数的细节详见第二章宏表函数

1.6.1.4. 快速插入行
在一个很多行的的表格中，如何在每隔一定行的前面快速插入一行？
z 先在数据列旁边插入1 空列
z 在空列中输入12345678910 …直到数据结束
z 然后复制输入的这些数据，你需要多少空格就粘贴多少次；粘贴在刚才输入的数据尾部
z 按照你刚才输入好的数据排序..OK

类似上面操作：如何快速选定奇（偶）数行或列？
z 先在数据列旁边插入1 空列
z 在空列中输入1，2；1，2；1，2…直到数据结束
z 按照你刚才输入好的数据排序，或者自动筛选...OK

技巧：如何按 4 个关键字进行排序
首先，打开排序对话框，在“第三关键字”框中，单击最不重要的数据列，确定退出。然后再次打
开排序对话框，在“主要关键字”和“次要关键字”框中，单击需要排序的其他 3 个数据列，选
中所需的其他排序选项，再单击“确定”。

第 70 页共 127 页

基本操作

1.6.2. 筛选
1.6.2.1. 自动筛选
工作我们常用自动筛选。普通筛选我们都了解，本书不是基础教程，不再罗嗦，现举例介绍自动筛
选的自定义功能：

z 我们要找出包含“模具”的所有数据

选中数据→自定义→
包含→筛选
z 对文本格式的数字进行筛选

z 筛选出特定行
一份工作表想每隔50 行打印一行，筛选方法：插入一空列,输入=MOD(ROW(),50) 并复制到全数据
列,再用筛选法把有“0”的单元格选出。

1.6.2.2. 高级筛选
此功能是按照自定义的条件筛选数据，有一个用法做汇总数据很需要：筛选唯一（不重复）值。

选中待筛选数据→数据
→筛选→自动筛选→出现高级筛选窗口
z 把数据复制到其他空白区域，防止原数据被更改（1）
z 待筛选的数据区（2）
z 把筛选结果复制到的区域（第一个单元格）（3）
z 必选项：选择不重复的记录（4）

第 71 页共 127 页

精通办公软件培训系列教材－Excel

高级筛选一般用法：

. 做好条件区如图：
. 数据→ 筛选→ 高级筛选
. 把数据地复制到其他空白区域，防止原数据被更改
. 输入数据区域，
. 条件区域选择刚刚做好的数据区，

. 选择需要复制的位置
. 如果需要不重复记录，要选中选择不重复的记录
. 结果如图：

第 72 页共 127 页

基本操作

1.6.3. 记录单
Excel 可为区域生成一个内置数据表单。数据表单可在单个对话框中显示所有的列标，并且在
每个列标旁都保留一个空格以供用户填入该列的数据。用户可以输入新数据、根据单元格内容查找
数据行、更新已有数据以及从区域中删除数据。
当列出了列标的简单的表单可满足需要，并不需要更复杂或自定义的功能时，可使用数据表单。如
果区域较宽，有多列数据而不能在一屏中显示，那么使用数据表单比逐列键入数据要方便。单击“条
件”后，可以在记录单中输入条件作为搜索的依据，记录单将显示符合条件的记录

第 73 页共 127 页

精通办公软件培训系列教材－Excel

1.6.4. 分类汇总
z 选中数据区
z
z 出现如图1 窗口
z
汇总方式：
分类字段：分类汇总的类别（项目）

将分级显示列
最大
类
数据→分类汇总
汇总和，平均值，…
选定汇总项：汇总数据的列

z 其他的选项自己操作可以了解了！
z 分类汇总可以重复2 次以上,不再详述!

分类汇总可自动计算列表中各类别的汇总和总计值。当插入自动分类汇总时，
表，以便为每个分类汇总显示和隐藏明细数据行。分类汇总的方式有求和、
（最小）值、乘积、方差等。
要删除分类汇总，请在含有分类汇总的列表中，单击任一单元格，
汇总”，单击“全部删除”即可。

第 74 页共 127 页

基本操作

1.6.5. 有效性
数据→数据有效性
数据有效性：一个包含帮助你在工作表中输入资料提示信息的工具。它有如下功能:

z 给用户提供一个选择列表
z 限定输入内容的类型或大小
z 自定义设置（输入信息，出错警告，输入法）

注意: 数据有效性。它可以通过粘贴在单元格输入其它数据, 并且可以通过编辑|清除|清除所有设
置，来取消它

第 75 页共 127 页

精通办公软件培训系列教材－Excel

1.6.5.1. 创建下拉列表选项
使用数据有效性可以为一个单元格创建一个选择输入内容的下拉列表。列表数据项

z 可以在工作表的行或列中输入,

z 或者输入事先定义好的名城。具体见图：

z 以直接在来源框中输入用逗号隔开的条目替代列表

数据→数据有效性
→
设置
→允许→序列→来源
最后效果见图4

选择需要列表的单元格

→
第 76 页共 127 页

基本操作

1.6.5.2. 复杂的下拉表选项
数据有效性

创建2 个列表，在第二个列表中根据前一列表内容限制选择范围，这里是综合运用

+ INDIRECT 函数（关于此函数详见第二章常用函数）
z 创建部门列表名城（类别），见图1
z 选中需要部门列表的单元格，见图2-1；
创建部门下拉列表，结果见图2-2

z 分别把各个部门创建各自的名城，见图3
. 创建的名城一定和各自部门名等同，否则出错

z 选中需要姓名列表的单元格，创建姓名下拉列表，见图4
. 图4 来源框输入的公式：=INDIRECT(B8)

z 最后就完成了！效果见图5：
选择不同的部门，相应的出现不同部门的姓名

第 77 页共 127 页

精通办公软件培训系列教材－Excel

1.6.5.3. 动态的下拉表选项
INDIRECT 函数的作用仅为引用, 并非公式, 前面的方法不能工作于动态列表。即的某个部门的人

员增减时候，上面的方法不能解决问题！下面的方法是动态的：数据有效性+ INDIRECT 函数+

OFFSET 函数

z 把各个部门人员所在的整列，分别命名为：财务部列，销售部列，生产部列

z 选中需要姓名列表的单元格，创建姓名下拉列表
来源框输入的公式为：=OFFSET(INDIRECT($A$8),0,0,COUNTA(INDIRECT($A$8&" 列")),1)

z 最后完成，如图1

第 78 页共 127 页

基本操作

1.6.5.4. 制作会计科目菜单
以上面为基础，作个会计的科目联级菜单，选择不同的会计科目，显示不同内容的二级科目，如图：

z 做好会计的一级(二级)科目的列表

z 分别把每列数据命名，如下：
财务费用 =$D$85D$88
管理费用 =$B$85B$89
销售费用 =$C$85C$90
一级科目 =$A$85A$89
银行存款 =$F$85F$91
制造费用 =$E$85E$91

z 选择A94:A98
z

选择序列
→数据有效性
在来源框中填入
＝一级科目（=$A$85A$89）
z 效果如图：

z 选择A94:A98
z 选择
＝INDIRECT($A94)
z 效果如图：

序列
→数据有效性
在来源框中填入
第 79 页共 127 页

精通办公软件培训系列教材－Excel

1.6.5.5. 有效性设置的其他情况
z 整数：
限定单元格输入数值范围；在最大值（最小
值）窗体输入限定的数值；如果输入数值超
出限定范围将出现如下窗口：

z 其他如小数，日期，时间，文本长度：
这些操纵同上；如果在已经设置数据有效性
的单元格重新设置，将出现如图的选项，意
思是否同类的单元格也是这样更改

全部去除
. 清除所有数据有效性设置
. 允许选择任何值也可以清除设置
z 数据里面可选择的限制方式很多，如

图：

第 80 页共 127 页

基本操作

z 此例子是用公式设置数值输入
最小值（min ）取决于数据区的最小值
最大值（max ）取决于数据区的最大值

z 此例子是用公式设置日期
日期输入范围是
今天（today ）至五天后（today+5）

第 81 页共 127 页

精通办公软件培训系列教材－Excel

1.6.5.6. 自定义条件示例
数据有效性的自定义是非常实用的功能。直接在数据框内输入公式，具体例子见下面

1.6.5.6.1. 防止输入重复值
防止在工作表一定范围输入重复值。本例中, 在单元格 B3:B10 中输入的是员工编号。

z 选择单元格B3:B10
z 选择数据|有效性
z 选择

自定义
z 在公式框中输入： =COUNTIF($B$3B$10,B3)<=1
使用 COUNTIF 函数统计 B3-B10 出现次数；在$B$3B$10 范围内。结果必须是1 或 0:

1.6.5.6.2. 限定数据输入数量
z 选择单元格B123
z 选择数据有效性
z
z 在公式框中输入

选择自定义
=LEN(B123)<=A123

z 在

输入信息
输入信息：… …

第 82 页共 127 页

基本操作

1.6.5.6.3. 多列唯一值
防止在工作表多列中输入重复值。本例中, 在单元格 A49:B55 中输入的是员工编号

z 选择A49:B55

z
z 在公式框中输入公式使用两个COUNTIF 函数统计每个工号出现次数,
z 公式如下：

数据有效性
=(COUNTIF($A$49A$55,A49)+COUNTIF($B$49B$55,A49))=1

1.6.5.6.4. 跨表禁止重复输入
防止在多工作表中输入重复值。本例中, 在Sheet1 中 A 列和Sheet2 中 A 列中不能输入重复值
表1 的公式：

表2 的公式：

公式内容：

第 83 页共 127 页

精通办公软件培训系列教材－Excel

1.6.5.6.5. 限定总数
防止一个范围数据总数（$B$37B$44 ）超过指定值。本例中, 预算不能超过$10000 。

=SUM($B$37B$44)<=10000 。结果必须小于或等于$10000
1.6.5.6.6. 防止输入周末日期
z 选择单元格C3:C7
z 选择数据|有效性
z 选择“自定义”，
z

在公式框中填入
防止输入的日期为星期六或星期日。 WEEKDAY 将输入的日期返回到星期, 并且不允许其值为1

(星期日) 和 7 ( 星期六)。

z 选择单元格，选择数据有效性

z 在允许下拉框中选择自定义

z 在“公式”框中, 输入: =AND(WEEKDAY(B2)<>1,WEEKDAY(B2)<>7)

1.6.5.6.7. 日期序时输入
如果输入的日期，比上面的小则出现错误提示。

z 选择单元格C3:C7

z 选择

z

中填入

=MAX($B$60B60)

数据|有效性
选择自定义，在公式框
注意：选择区域时候是从B60 开始的；如果是从B61 开始的，公式则是 =MAX($B$61B61)

第 84 页共 127 页

基本操作

1.6.5.6.8. 特定前缀
编号必须以粤这个字或基地这个词为前缀，否则出现错误提示。

z 选择单元格C3:C7
z 选择
z

数据|有效性
→
自定义
公式框中填入 =OR(LEFT(B71,1)=" 粤",LEFT(B71,2)="基地")
1.6.5.6.9. 和窗体结合简单例子
一个非常简单的有效性+函数+窗体的小综合，见图1 ！选择窗体里面不同的数字，B112
出现不同的会计一级科目，C112 （设置了有效性）则出现不同的明细。俺很懒惰了，所以就利用
原有的数据列表了（详见1.7.5.4）^_^ ！

z 做好窗体需要的列表：B114 --- B116
z

在窗体
(视图→工具栏)工具条，选择

组合框
建立一个带有下拉列表的窗体，见图2

第 85 页共 127 页

精通办公软件培训系列教材－Excel

z 在组合框上面，点击鼠标右键设置控件格式(图3)

z 设置数据区域(B114 : B116), 单元格链接(A116), 图4

..在B112 填入=CHOOSE(A116," 管理费用明细","财务费用明细","制造费用明细") 图5
..设置C112 单元格有效性，填入 =OFFSET(管理费用,,$A$116) 如图6
z 最后效果如图7 和图1

注意：OFFSET(CHOOSE) 函数见第2 章函数

第 86 页共 127 页

基本操作

1.6.5.6.10. 其他问题
z 日期列中只能接受日期值，在数据有效性中应用什么公式？
的

操作：数据有效性
，好象也不能完全避免输入数值的问题,因为EXCEL 中日期实
际就是一个数值,如2007 年1 月6 日实际单元格数值是39088 ！可以实现，用数据有效性的自定义，
公式为：=AND(ISNUMBER(FIND("D",CELL("format",B3))),B3>=38718,B3<=39082)
其中的38718 和39082 为实际需要的日期系列数，分别是：2006 年1 月1 日，2006 年12 月31 日。
CELL 函数是判别数字格式用的。
注意：

日期
. 你必须输入2006-1-1 至2006-12-31 之间的日期，
. 你输入日期的格式要正确。
. 有效性不能限制数据粘贴，系统会将以原单元格的格式来覆盖当前格式。

z 禁止录入空格
输入： =COUNTIF(A1,"* *")=0
注意：
. 第一位置不能输入空格:countif(a1," *")=0 （注意星号*前面的空格）
. 最后一位不能输入空格:countif(a1,"* ")=0) （注意星号*后面的空格）
. 还有另外的公式也可以防止输入前后空格： =LEN(A1)=LEN(TRIM(A1))
=LEN(TRIM(A1))=LEN(A1)
. 如果字符串任何位置都不允许出现空格：=ISERROR(FIND(" ",A1))
. 如果输入的内容仅仅是数字：=ISNUMBER(A1)

操作：自定义→公式
第 87 页共 127 页

精通办公软件培训系列教材－Excel

1.6.5.7. 添加提示信息
你可以给使用电子表格的人员提示信息。在选择有数据有效性的单元格时显示输入信息。如果输入
无效数据则显示出错警告，如图：

z 选择你要应用数据有效性的单元格

z

数据→数据有效性
z 点击

z 选中

输入信息标签
选定单元格时显示输入信息。
z 标题框内输入文本. 这个文本信息将粗体

显示在提示框的顶部。

z 在输入信息框中输入提示信息。

z 点击确定或对出错警告进行设置。

的提示信息对比效果如图：

批注和数据有效性
数据有效性
的提示信息可以取代

批注
了！

第 88 页共 127 页

基本操作

1.6.5.8. 添加出错信息
数据→数据有效性
z 点击

..
出错警告
标签
z 选中
z 标题和错误信息

输入无效数据时显示出错警告
分别输入内容
. 在标题框中输入标题文本。这个文本将以粗体形式显示在弹出的信息窗口上部。
. 在出错信息框中输入要显示的信息

z

样式
下拉列表中选择一种出错警告样式，具体样式见下面：

)

停止
其作用是防止输入无效数据。

重试
取消
：则输入的无效数据突出显示,你可以重新输入。
：则自动删除无效数据, 单元格恢复原始的内容。

)

警告
为输入无效数据设置一个障碍。
是：接受无效数据输入, 选择下一个单元格。
否：无效数据突出显示, 可以重新输入。

取消：无效数据被删除,单元格恢复原始内容
..
信息
输入无效数据时给出提示信息。

确定：则接受无效数据输入, 并选择下一个单元格。
取消：无效数据被自动删除,单元格恢复原始内容。
注解: 如果Office 助手是打开的，则会弹出提示（出错警告样式不同，提示也不同的）

第 89 页共 127 页

精通办公软件培训系列教材－Excel

1.6.5.9. 输入法模式
z 打开：切换到设置的单元格中时，自

动打开中文输入法
z 随意：编辑表格随意调整英文或中文
z 关闭：关闭中文输入法，换成英文。
先选择输入法，再设置有效性

1.6.5.10. 源于其它工作簿的列表
可以使用其它工作簿中的列表作为数据有效性下拉列表。要使数据有效性能够正常运行, 包含列表
的工作簿必须是打开的。以 1.xls 工作簿为例子操作如下：

z 打开你想在其中使用数据有效性的工作簿。
z 插入 > 名称 > 自定义
z 输入列表名称，如：客户清单
z 在引用到框中, 输入名称范围的引用。例如 ='1.xls'!name
z 点击确定
z 选择你要使用数据有效性的单元格。
z 选择数据 > 有效性
z 在允许框中, 选择序列
z 在来源框中输入列表名称，例如: =顾客名单
z 点击确定

其他实例
请参见其他书籍,不再一一详述!

第 90 页共 127 页

基本操作

1.6.6. 模拟运算表
模拟运算表其实是把若干个相同公式的输入生成对应的值简化成一个公式的输入产生对应的值。

1.6.6.1. 单变量模拟运算表
根据图 1 用模拟运算表建立一业绩表
z F10 输入:=SUMIF(销售员,E10,销售额)/SUM(销售额)
z 选中数据区E10:F22 ，如图2
z

数据
出现模拟运算表对话框，如图3

→
模拟运算表
z

在输入引用列的单元格
输入 $E$10, 确定(图3)
z 每个业绩比率单元格都有统一的数据如图：

. 图2 业绩比例的白色部分是公式做的（G11=SUMIF( 销售员,E11, 销售额)/SUM(销售额)）
. 图2 圈住的表头不在运算表范围内！
. 图2 的公式输入单元格和行类别的0 行（E10）必选！此点必须记住！
. 图2 模拟运算表列输入$E$10 和F10 公式（=SUMIF( 销售员,E10,销售额)/SUM(销售额)）有关

第 91 页共 127 页

精通办公软件培训系列教材－Excel

1.6.6.2. 双变量模拟运算表
z 在I10 输入：
z 选中数据区I10:N22 ，如图1

数据
模拟运算表
出现模拟运算表对话框，如图2

z 在

..
输入引用列的单元格
输入行 $I$8, 列 $I$9,
每个业绩比率单元格都有统一的数据如图：
确定！
z

→
注意：

. 图1 的白色部分是公式做的：

. 图1 圈住的表头不在运算表范围内！
. 图1 的公式输入单元格必选！此点必须记住！

. 图1 模拟运算表行列输入和I10 公式有关

第 92 页共 127 页

基本操作

1.6.6.3. 模拟运算表和公式比较
模拟运算表
一次性输入公式，如有更改也只需要改一个地方
不用过多考虑在公式中使用绝对引用还是相对引用
表格中的数据无法单独修改
公式中引用的参数必须引用“输入引用列/行的单元格”指向的单元格

普通的方式（公式，然后复制到各个对应的地方）
公式需要复制到每个对应的单元格
需要详细考虑每个参数在复制中需要发生的/不发生的变化，以决定使用绝对引用

还是相对引用
每次如果需要更改公式，就要将所有的地方再重新复制一遍
表中的数据可以单独修改
公式中引用的参数直接指向数据列/行

1.6.6.4. 模拟运算表问题
z 使用前必须先选定一个单一的矩形区域，而且不能少于两行两列！

z 单变量模拟运算表的选定区域中最左边的一列和最上面的一行将作为公式及变量区域，其它
的地方才是模拟运算表实际进行运算的区域。

z 公式输入的位置是固定的。单变量模拟运算表中，如果你只输入了“引用列的单元格”，则
选定区域中最上面的一行为“输入公式的区域”同理，如果你只输入了“引用行的单元
格”，则选定区域中最左边的一列为“输入公式的区域”。

z 双变量模拟运算表中，选定区域中左上角的那个单元格为“输入公式的区域”

第 93 页共 127 页

精通办公软件培训系列教材－Excel

z 下图中所示的“输入引用行的单元格”以及“输入引用列的单元格”可以输入“输入公式的
区域”、“变量区域”

这里引用的单元格没有实际的意义,（如E$10, $I$8, $I$9 等）也不会在实际运算的区域中用到这
几个单元格的实际数值。引用的单元格在这里只是一个中间的变量，或者说是一个中间的代号而已。

z 模拟运算表的工作原理：
) 单变量模拟运算表:
将第一行（或第一列）中的公式向下（或向右）复制到每一个“实际运算区域”中的每一个单元格。
也就是说每个单元格中的公式都只是和它所在的列（或行）中的公式相同。每一个公式对应一列（或
一行）。再将公式中用到的引用单元格用每个单元格所对应的列（或行）变量来代替。如下例中：
ｃ114 中的公式为它所在列，也就是C 列的公式：=SUMIF(销售员,B112, 销售额)/SUM(销售额)。再
将公式中引用的单元格，也就是B112 替换成它所对应列的变量，即B114 。最后运算公式为：
=SUMIF(销售员,B114, 销售额)/SUM(销售额) 同理，D116 中的最后运算公式为：=B116
) 双变量模拟运算表
将公式区域中的公式复制到“实际运算区域”中的每一个单元格，再将公式中的行和列引用单元格
替换成它所对应行和列的变量。道理和单变量一样，我就不找例子了。（因为我实在很懒，别见怪！）
所以说模拟运算表不用考虑相对引用和绝对引用，因为它实际上都是用的相对引用。
其他详尽资料请参考其他专业书籍;CD 有各种高级应用例子,此处不再一一详述!

第 94 页共 127 页

基本操作

1.6.7. 分列
如果需要导入txt 等类型文件，或者把一批单元格的数据按照特定的规律分开，如图2：去掉序号

z 选中数据区
z →数据分列图1

z

出现步骤一
的对话窗，选择合适的文本分列方式

分隔符号：按照特定的分隔符号来分列
固定宽度：按照特定的分隔距离来分列
步骤1 选择不同，步骤2 出现不同的对话框

下一步
第 95 页共 127 页

精通办公软件培训系列教材－Excel

z 选择分隔符号：

z

选择固定宽度：
步骤3 对话框
完成
就OK 了！

第 96 页共 127 页

基本操作

1.6.8. 合并计算
本书略述

按照首行或最左列，对选定的单个或多个区域的相同部分对应的数据按选定的函数进行合并。
就是把相同的名称合并计算，引用位置也可以是几个不同的工作表，点击引用区域，添加，类似
Sumif 或者Subtotal 函数！

利用合并计算，可以汇总一个或多个源区域中的数据。
EXCEL 提供了两种合并计算的方法：按位置合并与按类别合并。
按位置合并，它要求所有数据源区的数据应相同排列，也就是说每一个数据源区的数据必
须在相对的位置。如果每个源区域中数据的排列不同，可按类别合并。
在合并计算时要注意以下几点：

1. 正确引用了所有数据源区域；
2. 在“合并计算”中选择了正确的函数；
3. 设定的目标区域足够大，能容纳合并计算后的数据；
4. 如果是“按位置合并”，必须保证每个数据源区域中的数据在相对的区域中；
5. 如果是“按类别合并”，则要注意在数据源区域中应包含“行”与“列”的字段名，
在“合并计算”中，勾选“首行”或“最左列
从合并计算的学习中，给人的提示是：今后制作表格时（尤其是那些有时间连续性的表格），对其
中的数据列与记录数的位置要尽可能地做到相对稳定。

1.6.9. 组及分级显示
本书略述
分级显示
一、使用分级显示的好处

第 97 页共 127 页

精通办公软件培训系列教材－Excel

当一个表格的数据有几万行，几百列时，数据再怎么整理也会显的杂乱无章。这时候通过对数
据进行分级显示，可以使这些庞大的数据具有层次感，并能让数据按需显示，看起来更生动。
二、分级显示的建立
1、自动建立分级显示
如果数据很规范，可以通过使用分类汇总功能轻易的得到分级显示。这也是大家常用的一种方法。
2、手动建立分级显示

当数据是一些不规则的数据或者文本时，可以使用手动建立分级显示。方法是选择中父项目下
的子项目的整行或者整列，然后用鼠标单击菜单栏的数据选项，选择组及分级显示（G）,然后选择
组合（G）。
三、分级显示的删除

当不需要分级显示的时候，用鼠标选中不需要的整行或者整列，选择菜单栏的数据选项，选择
组及分级显示（G）,然后取消组合（U）,当数据多时，后面的鼠标动作可以用F4 功能键代替。
四、分级显示的注意事项
微软的帮助中已经说的够全面了，本文的目的也仅是为了让大家了解分级显示的一些特殊功能。所
以这一节，请大家使用帮助查找。

补充：尽管样式在Excel 并不出色，但是通过合理的使用样式确实会让大型工作表更加生动。

本文就是一个使用分级显示的小示例，读者可以按左上角的1、2、3、4 体验一下效果。虽然这两
个示例都是文本,但是它同样可以应用于数据。

1.6.10. 数据透视表(图)
本书略述，市面各种书籍多有详尽的描述;相关资料参考CD 文件及其他资料

1、Excel 数据透视表能根据时间列和用户自定时间间隔对数据进行分组统计，如按年、季度、月、
日、一周等，即你的数据源表中只需有一个日期字段就足够按照（任意）时间周期进行分组了；。
2、通常，透视表项目的排列顺序是按升序排列或取决于数据在源数据表中的存放顺序；
3、对数据透视表项目进行排序后，即使你对其进行了布局调整或是刷新，排序顺序依然有效；
4、可以对一个字段先进行过滤而后再排序；
5、内部行字段中的项目是可以重复出现的，而外部行字段项目则相反；

第 98 页共 127 页

基本操作

6、通过双击透视表中汇总数据单元格，可以在一个新表中得到该汇总数据的明细数据，对其可以
进行格式化、排序或过滤等等常规编辑处理；决不会影响透视表
和源数据表本身；
7、以上第6 点对源数据是外部数据库的情况尤其有用，因为这时不存在单独的直观的源数据表供
你浏览查阅；
8、透视表提供了多种自定义（计算）显示方式可以使用；
9、如果源数据表中的数据字段存在空白或是其他非数值数据，透视表初始便以“计数”函数对其
进行汇总（计算“计数项”）；
10、透视表在进行TOP 10 排序时会忽略被过滤掉的项目，因此在使用此功能时要特别注意；
11、在一个透视表中一个（行）字段可以使用多个“分类汇总”函数；
12、在一个透视表数据区域中一个字段可以根据不同的“分类汇总”方式被多次拖动使用。
数据透视表列数限制问题:
我做一个数据透视表，想显示7 列明细信息。不知道为什么系统说我的列数过多，必须删除列才能
增加。请问如何解决啊？
解答：

1.先把透视表的列减少到系统所承受的数量，甚至更少；
2.把数据全部拖到数据区，移至行；
3.最后把需要的列拖进列字段。
使用一个动态数据
你可能使用一个动态的公式定义数据透视表的数据源. 当一个新的条目添加到表格中时，数据源自
动扩展.
1. 命名一个区域
选择插入>名称>自定义
输入一个范围名称, 例如 Database
在引用位置框中, 输入一个Offset 公式定义范围大小, 这样做的前提必须是品名列不能有空白单元
格存在. ,例如:
=OFFSET(销售额!$A$1,0,0,COUNTA( 销售额!$AA),7)
本例中, 列表在一个名称为'销售额'的工作表中, 起始单元格为A1. 公式中使用的参数是:
引用单元格: 销售额!$A$1
第 99 页共 127 页

精通办公软件培训系列教材－Excel

行偏移: 0
列偏移: 0
行数: COUNTA( 销售额!$AA)
列数: 7

注意: 如果要使用动态的列数,
请将7 替换成: COUNTA( 销售额!$11)
点击确定

2. 将定义的名称范围用于数据透视表
选择数据库中的一个单元格
选择数据>数据透视表和图表报告
选择'Microsoft Excel 数据清单或数据库
在选定区域框内, 输入范围名称, 例如
点击下一步
点击算式按钮
放置适当按钮到行,列和数据区域
点击确定, 点击完成
(待续)

第 100 页共 127 页

基本操作

1.6.11. 列表
列表可以自动扩展列表区域，可以筛选，可以自动求和、极值、平均值等又不用输入任何公式，只
需选择一下，可以随时转换为区域。
列表的优点
排序和筛选列表可按升序或降序对列表进行排序，或创建自定义排序次序。还可筛选列表以仅显
示符合指定条件的数据。
确保数据的完整性对于未链接到 SharePoint 列表的列表，您可使用 Excel 内置的数据有效性验
证功能。例如，您可以选择在列表的某一列中仅允许输入数字或日期。对于已链接到 SharePoint 列
表的列表，将自动对列表应用Windows SharePoint Services 的列表验证功能。例如，当将列表发布
并链接到运行 Windows SharePoint Services 的服务器，或在 Excel 中编辑现有的 SharePoint 列表
时，将对列表中的每一列应用数据类型规则以确保在每一列中仅允许一种数据类型。
设置列表对象的格式可像在工作表中设置单元格格式那样在列表中设置单元格的格式。
与 Windows SharePoint Services 中的列表兼容将列表发布到 SharePoint 网站后，您将创建一个自
定义 SharePoint 列表。如果发布列表或导出现有 SharePoint 列表时选择链接列表，则可脱机编辑
该列表，稍后再将更改同步到 SharePoint 列表。
关于列表
Microsoft Excel 列表提供多种功能，用于方便地管理和分析 Excel 工作表中的多组相关数据。将
某一区域指定为列表后，您可方便地管理和分析列表中的数据而不必理会列表之外的其他数据。例
如，只使用列表中所包含的数据，您可筛选列、添加总计行，甚至只使用列表中所包含的数据创建
数据透视表。
您可在工作表中设置多个列表，从而可更加灵活地根据需要将数据划分为易于管理的不同数据集。
注释无法在共享工作簿创建列表。若要创建列表，必须首先从共享中删除该工作簿。
通过与Microsoft Windows SharePoint Services 进行集成，使用 Excel 列表与其他用户共享数据变

得更加容易。只要拥有 SharePoint 网站的网站地址和创作权限，
您就可共享列表以供其他人员查看、编辑和更新该列表。如果选择
将 Excel 中的列表链接到 SharePoint 网站上的列表，则可与
SharePoint 网站同步更改以便其他用户可查看更新数据。
在 Excel 中创建列表时，使用列表中的功能以及设计用来增强这
此功能的可视元素可更方便地识别和修改列表中的内容。

第 101 页共 127 页

精通办公软件培训系列教材－Excel

1.默认情况下，在标题行中为列表中的所有列启用自动筛选功能。自动筛选允许您快速筛选或排序
数据。
2 列表周围的深蓝色边框清晰地区分出组成列表的单元格区域。
3 包含星号的行称为插入行。在此行中键入信息将自动将数据添加到列表中并扩展列表的边框。
4 可以为列表添加汇总行。单击汇总行中的单元格时，将显示聚合函数下拉列表。
5 通过拖动列表边框右下角的调整手柄，可修改列表大小。
列表的优点
排序和筛选列表可按升序或降序对列表进行排序，或创建自定义排序次序。还可筛选列表以仅
显示符合指定条件的数据。
确保数据的完整性对于未链接到 SharePoint 列表的列表，您可使用 Excel 内置的数据有效性
验证功能。例如，您可以选择在列表的某一列中仅允许输入数字或日期。对于已链接到 SharePoint
列表的列表，将自动对列表应用Windows SharePoint Services 的列表验证功能。例如，当将列表发
布并链接到运行Windows SharePoint Services 的服务器，或在 Excel 中编辑现有的 SharePoint 列
表时，将对列表中的每一列应用数据类型规则以确保在每一列中仅允许一种数据类型。
设置列表对象的格式可像在工作表中设置单元格格式那样在列表中设置单元格的格式。
与 Windows SharePoint Services 中的列表兼容将列表发布到 SharePoint 网站后，您将创建一
个自定义 SharePoint 列表。如果发布列表或导出现有 SharePoint 列表时选择链接列表，则可脱机
编辑该列表，稍后再将更改同步到 SharePoint 列表。
1、先声明：这一点仅是个人理解。定义的名称ID 选择了“列表”区域，而“列表”区域会自动一
格一格的拓展（不能隔一格），所以有点类似offset 公式做的动态名称。
先拿普通定义名称来说吧，假设定义名称为data=A1:B10, 则当你选中A1:B10 时，编辑栏左边的“名
称框”会自动显示为data ，若在输入公式时用鼠标选中A1:B10 而非输入"A1:B10", 则也会自动变为
data 。
类似的，用“列表”这个功能（它有自动在相邻区域向下拓展功能）相当于默认为一个名称比如某
列表，而我们在定义一个名称时选择了这个列表区域，就默认成“列表”名称——（这一说法有待
统一，大概意思就是这样。）而随着列表本身的拓展，定义的名称引用区域跟着拓展了。
2、多了的那个按钮只要从：数据〉筛选〉自动筛选那边去掉就行了。
个人觉得：用列表定义名称的方式来动态引用会比offset （如常见的=Offset($A$4,,,counta(A:A)-3)）
好些，阐述如下：
第 102 页共 127 页

基本操作

1、不用费劲地去写公式，尤其是多列要定义名称，用offset 还要一个个去改，碰上喜欢合并单元
格（大多人都会用合并单元格作标题之类的），这就往往在counta 后面减去一个数问题上不一致，
连改名称里的公式都要注意。
而用列表功能，只需要：插入〉名称〉指定〉首行并选择列表区域——一次性定义多个名称。要做
仅仅是选择区域、创建列表而已。
2、offset+counta 存在一个弊病：比如要引用的数据在A1:A100, 我们在后面又输入一些东西，比如
A101:A103 为空单元格，而我们在A103 下面写入东西，counta 后面减去的数要手工去修改，否则
引用区域就把A101 等空单元格包含进去，空单元格在一些函数计算中可能造成运算不便的情况。
要是A101:A103 是我们特地空的，而A104 有数据是可能影响运算结果的，由于后面输入的数据多，
把A104 包含进去，更是不妙！
而列表功能，我们在列表范围之外（指的是列表下面隔一行之后）输入东西，比如列表在A1:A100,
我们在A102 输入东西不会影响列表。如果我们想扩展列表的区域，在A101 输入就自动扩展了。
再次补充一点： czzqb 兄提出了用offset+counta 由于Offset 函数的易失性会引起重新计算问题，列
表功能定义名称的引用是对“单元格区域”的引用，没有使用任何函数，不会引起重算（打开文件
不做任何修改并关闭，会提示保存的便是使用了易失性函数引起重算）。这也是个优势！
推广建议：鉴于以上比较，对于动态引用单元格区域的采取区分数据由来而使用的建议，具体如下：
1、原始表格区域的引用，用于数据不断增加时希望能够引用该区域的公式能自动更新——用列表
区域。 2、公式得出区域的引用，无法预知有效数据个数的情况，比如常见的单列求不重复值，我
们不能确定个数，用公式多拉动复制产生多余的""或者错误区域，再用这个区域作为数据有效性引
用时需要定义名称，这种情况用offset 最合适不过了。不过已经不能用offset+counta 了，该把counta
改为countif(Range,"><") ——文本，或count(range) ——数字，或者sum(--(range<>"")) ——通用。
此外，我经常在建议使用“列表”定义动态名称，并非我就要摒弃Offset ，有的时候，2 种方法可
以结合起来用，尤其是当“列表字段较多、或者字段字段名有重复”的情况下，你可以只用“局部”
作“列表”实现动态，然后再用“列表定义的动态名称”作Offset 的第一参数来偏移（这样的offset
公式就很简单了）达到引用其他部位也能随着行数数据的增加而变化的效果。2006-4-23 补充
呵呵，这个帖子打满补丁了：“并非我就要摒弃Offset ”——如果能摒弃用Offset 作的动态引用定
义名称的就摒弃吧。A1:index(A:A,……)这类的引用就可以在一定程度上替代了

第 103 页共 127 页

精通办公软件培训系列教材－Excel

1.8.窗口
1.8.1. 拆分
可以把一个文件拆分为2，4 个各自独立的窗口
右上角：

右下角：

1.8.2. 冻结
当数据很多需要上下左右翻页时，需要保持标题栏一直显现！要固定顶端（左端）标题行：

选择要固定的单元格，然后窗口
→
冻结窗格
z 据我个人工作经验，不只冻结标题栏，还要冻结一行货二行数据，有利后期数据处理
z 当出现不能查看行列数据时候一般两种情况：隐藏，还有就是冻结了行列！

1.8.3. 当前文件
z 快速翻屏：
把鼠标放在滚动条，
单击右键，弹出如下菜单，

第 104 页共 127 页

基本操作

z 当用鼠标选取数值的时候，状态栏（在窗口最下边的，左边一般是“就绪”，右边是“数字”）

就显示“合计”数，还可以显示其他的形式等！把鼠标放在状态栏，单击右键，如上图：
z 快捷选表：如果文件中的表很多，选取很麻烦！
选择有更快的选表方法：把鼠标放在左下角上（），
单击右键，弹出如下菜单，选表非常的快捷！
z 快速定位单元格

第 105 页共 127 页

精通办公软件培训系列教材－Excel

1.9.帮助
Office 的帮助非常的全面！大家应该充分的利用！一遇到不懂的地方，首先求助于OFFICE 助手！

1.8.其他
1.10.1. 摄影
在Excel 中，如果需要在一个页面中反映另外一个页面的更改，我们一般用粘贴连接等方式来
实现。但是，如果需要反映的内容比较多，特别是目标位置的格式编排也必须反映出来的时候，再
使用连接数据的方式就行不通了。好在Excel 早为我们准备了“照相机”，你只要把希望反映出来
的那部分内容“照”下来，然后把“照片”粘贴到其他的页面即可。
据我个人经验，发现当你给他人看数据，但是不想泄漏数据的来源，也不想他人复制数据时候，这
个照相功能就非常的有用！

z

准备照相机
（这个操作在前项

视图
→ 类别→ 工具，在右边
→ 工具栏→ 自定义有详细说明）
) 自定义工具→
) 命令
命令→ 摄影，
将其拖到工具栏的任
意位置。

z 给目标“拍照” 假设我们要让Sheet2 中的部分内容自动出现在Sheet1 中。
) 拖动鼠标选择Sheet2 中需要“拍照”的内容。

) 鼠标单击工具栏上的照相机按钮，于是这个选定的区域就被“拍”了下来。

z 粘贴“照片”

第 106 页共 127 页

基本操作

) 打开Sheet1 工作表。
) 在需要显示“照片”的位置上单击鼠标左键，被“拍摄”的“照片”就立即粘贴过来了。

在Sheet2 中调整“照片”的各种格式，粘贴到Sheet1 中的内容同步发生变化。图片工具栏对

这个照片也是有效的！，这个“照片还可以自由的旋转呢！怎么样？这个数码照相机还不错吧！

1.10.2. 双击功能
双击功能使Excel 如虎添翼
z 在工具栏右侧的空白处双击，快速打开自定义对话框。
z 在单元格中双击，单元格进入编辑状态。
z 在“格式刷”按钮上双击，格式刷可以反复多次使用。（此方法同样适用于Word）

特别提示再次单击“格式刷”按钮，或者按Esc 键，可以取消“格式刷”功能。
z 在标题栏上双击，Excel 窗口由最大化（原始状态）还原到原始状态（最大化）大小。
z 选中单元格，鼠标移至该单元格的右下角成细十字线状时，双击鼠标，即可将该单元格中的

内容快速填充到下面的单元格中。

特别提示要实现此填充，该单元格左侧（或右侧）相邻列中必须有连续的数据。
z 工具条浮动在工作表区中，在工具条的标题栏上双击，该工具条返回工作表区上部（下部）。
z 在窗口左上角Excel 标志上双击，则退出Excel（如果当前文档没有保存，系统会提示保存）。
z 在工作表名称（Sheet1 等）上双击，即可对工作表名称进行“重命名”。
z 将鼠标移至垂直滚动条上端与编辑区交界处成双向拖拉箭头状时，双击鼠标，即可将编辑区

拆分为上、下两个窗口。此时，如果将鼠标移至两个窗口分界线上双击，即可撤销窗口的拆分。
z 将鼠标移至水平滚动条右端成双向拖拉箭头状时，双击鼠标，即可将编辑区拆分为左、右两
个窗口。此时，如果将鼠标移至两个窗口分界线上双击，即可撤销窗口的拆分。

z 鼠标移至列标与列标交界处成双向拖拉箭头状时，双击鼠标，即快速将左侧1 列设置为“最
适合的列宽”。如果选中多列，执行此操作，即可将选中的多列设置为“最适合的列宽”。行类
似操作：将鼠标移至行标与行标交界处成双向拖拉箭头状时，双击鼠标，即可快速将上面1 行
设置为“最适合的行高”。如果选中多行，然后执行此操作，即可将选中的多行设置为“最适
合的行高”。

z 在菜单上双击，即可将菜单中所有的菜单项（包括不常用的菜单项）全部展开。
z 如果某列（行）有多个连续的空白或有数据的单元格，选中最下（上，左，右）面一个单
元格，然后将鼠标移至该单元格上（下，左，右）边缘处（单元格边框）成梅花状时，双击鼠

第 107 页共 127 页

精通办公软件培训系列教材－Excel

标，即可跳转到最上（下，左，右）面一个单元格中。
特别提示如果下面全部是空白单元格，则向下跳转的操作无效。
z 双击数据透视表中的数据, 可在新的工作表中列出该数据的明细

1.10.3. 其他
z 引用同一工作薄中另外一工作表的单元格
= Worksheets(" 工作表名称").Cells(1, 1) ，如当前工作表为sheet1，引用sheet2 中的B2,则=sheet2!B2

z 不显示0 值
方法1：if (A1<>"", A 。, "")
方法2：对于整个工作表中的0 值全不显示，[工具]-[选项]-[视图]，清除0 值选项。
方法3：用自定义数字格式，其中"#" 和"?" 有屏蔽0 值的效果。比如："G/通用格式;G/通用格式;#"，
"G/通用格式;G/通用格式;?" 。

z 带文字的合计数
例子：总计：=SUM(A1:A29) 就是想让它经过自动求和后在一个单元格内显示总计：100 。
有多种方法实现，如下：

) ="总计："&sum(A1:A29)

) 把A30 格式设为"总计:"#0.00;" 总计:"-#0.00;" 总计:"0.00;@

) 将A30 单元格格式自定义为 "总计："0.000 即可,方便对E44 的引用计算

) =CONCATENATE(" 合计：",SUM(A1:A29))
z 粘贴链接单元格图片:选择某区域(如A2:C7) →→→复制按住 SHIFT, 选编辑粘贴链接图片,
此功能类似：摄影
z 对长公式进行错误查找：点击公式，按F9，出现出错的部分。ESC 复原，CTRL+Z 为undo 。
z 利用背景实现套打的解决方案

利用背景套打主要在于数据打印位置的确定，关键就是要使图片和实物之间的尺寸保持一致，这里我引
入一个中间参照物—空白表(只有表格线的表)。具体操作以套打支票为例说明：
) 将支票扫描成图片。

) 打印一个空白表，使其与支票尺寸一致(需反复调整打印，也可行、列分别打印)。

第 108 页共 127 页

基本操作

) 用“画图”的缩放功能调整图片大小，导入excel 作背景，并使其与空白表大小一致(亦需反复调

整导入，每次均用原图缩放，再另存为一个文件)。

) 根据图片背景调整好单元格，填入数据后套打支票，效果是匹配度达99%。

) 由于每次都是用原图缩放，故可取得缩放比例作为参数，再套打其他表格时，即可直接依参数缩

放图片。思路：因为空白表=支票，图片=空白表，所以图片=支票。该方案已证实可行。
z 显示出被隐藏掉了的行（第1 行，第1 列很难取消隐藏）

) 选中隐藏的上、下行，右击鼠标，选“取消隐藏”

) Ctrl+A-----格式-----行-----取消隐藏(能够一次显示所有隐藏的行或列)

) 另一法(工作表处于未保护状态):假如A1 被隐藏了在名称框中键入A1,回车按 Ctrl+Shift+0

或 Ctrl+Shift+9(只显示选定的隐藏列或行)

) 光标移到行号 4 上部变成上下箭头状, 按住了, 拖也要把它拖出来!(慢，不好操作)

) 选择整个工作表（点击左上角），然后再选择菜单中的行，选择最适合的行高，然后就OK!,同样

可以把隐藏的列显示出来。(这个办法最好，能够一次显示所有隐藏的行或列)
z 按“Ctrl+~”可以一次显示所有公式(而不是计算结果)。再按一次回到计算结果。
z 复制隐藏后的表格到一个新表格中不显示隐藏的内容：crtl+g －选可见单位格－复制－粘贴。
z 有关输入数字的提示

①、可作为数字使用的字符在 Microsoft Excel 中，数字只可以为下列字符：
0 1 2 3 4 5 6 7 8 9 + - ( ) , / $ % .
②、Excel 将忽略数字前面的正号(+)，并将单个句点视作小数点。所有其它数字与非数字的组合
均作文本处理。
③、输入分数为避免将输入的分数视作日期，请在分数前键入 0（零），如键入 0 1/2。
④、输入负数请在负数前键入减号 (-)，或将其置于括号( )中。
⑤、对齐数字在默认状态下，所有数字在单元格中均右对齐。如果要改变其对齐方式，请单击“格
式”菜单“单元格”命令，再单击“对齐”选项卡，并从中选择所需的选项。
⑥、数字的显示方式单元格中的数字格式决定 Excel 在工作表中显示数字的方式。如果在“常规”
格式的单元格中键入数字，Excel 将根据具体情况套用不同的数字格式。例如，如果键入 $14.73，
Excel 将套用货币格式。如果要改变数字格式，请选定包含数字的单元格，再单击“格式”菜单上
的“单元格”命令，然后单击“数字”选项卡，再根据需要选定相应的分类和格式。
⑦、“常规”数字格式如果单元格使用默认的“常规”数字格式，Excel 会将数字显示为整数（789）、
第 109 页共 127 页

精通办公软件培训系列教材－Excel

小数（7.89），或者当数字长度超出单元格宽度时以科学记数法（7.89E+08）表示。采用“常规”
格式的数字长度为 11 位，其中包括小数点和类似“E”和“+”这样的字符。如果要输入并显示多
于 11 位的数字，可以使用内置的科学记数格式（指数格式）或自定义的数字格式。

⑧、15 位限制无论显示的数字的位数如何，Excel 都只保留 15 位的数字精度。如果数字长度超
出了 15 位，Excel 则会将多余的数字位转换为零 (0)。
⑨、将数字作为文本输入即使用“单元格”命令将包含数字的单元格设置为“文本”格式，Excel
仍将其保存为数字型数据。如果要使 Microsoft Excel 将类似于学号之类的数字解释为文本，需
要先将空单元格设置为“文本”格式，再输入数字。如果单元格中已经输入了数字，需要对其应用
“文本”格式，然后单击每一个单元格并按 F2 键，再按 ENTER 键重新确认数据。
⑩、区域设置可作为数字使用的字符取决于“控制面板”中“区域设置”内的选项。这些选项也
决定了数字的默认格式，例如：在美国系统中句号 (.) 作为小数点使用。
第 110 页共 127 页

基本操作

1.10.4. 双击工作表打不开文件
有时候遇到双击文件只是打开Excel 程序,打开文件要在Excel 程序里面打开;解决如下:
方法1
在选项 -常规 -设置 -忽略其他应用程序去掉这个选项

方法1:
重新注册Excel：关闭Excel →开始→运行→打开→输入“Excel /regserver”

1.10.5. 显示工作表名称
1) 定义函数
Function sname(idx As Byte)
sname = Sheets(idx).Name
End Function
在任一单元格输入如=SNAME（1），参数为第几张工作表
2) 定义名称：=get 。document(1) ;结果为:[Book1 。xls]Sheet1
3) 定义名称：=mid(get 。document(1),find("]",get 。document(1))+1,31)&T(Now())

4.工作表输入：=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,31)
=MID(CELL("filename"),FIND("]",CELL("filename"))+1,100)
5。定义名称 =get 。workbooks(1) ？？？
6。定义名称=GET。WORKBOOK(1) 结果为:[Book1 。xls]Sheet1

第 111 页共 127 页

精通办公软件培训系列教材－Excel

7。 sh=MID(sheet,FIND("]",sheet)+1,31)&T(NOW())
sheet=GET 。WORKBOOK(1)
a1=INDEX(sh,ROW(A1))
8。定义名称 =MID(GET。DOCUMENT(1),FIND("]",GET 。DOCUMENT(1))+1,100)
9。取本工作簿内其他表名:
定义X=MID(GET。WORKBOOK(1),FIND("]",GET 。WORKBOOK(1))+1,100)
此时X 为由工作簿内所有工作表名构成的一维横向数组,怎么取出来自己想
=index(x,row()) 向下拖，即可一次取出全部工作表名。
写成=index(x,row(A1))&TEXT(NOW(),"") 较好，这样可以随工作表名的改变而即时改变。
10 。定义名称：工作表=REPLACE(GET 。WORKBOOK(1),1,FIND("]",GET 。
WORKBOOK(1)),)&T(NOW())
则在A1 输入以下公式即取得当前表名称
=工作表
若要取得所有表名称则定义
工作表名称=LOOKUP(ROW(INDIRECT("1:"&COLUMNS( 工作表))),MATCH( 工作表,工作表,),工作
表)
在A2：A10 （根据表多少而定）输入以下公式
=工作表名称即可（数组公式）

11. 工作表输入：=RIGHT(CELL("filename"),LEN(CELL("filename"))-FIND("]",CELL("filename")))
12.以下宏将在a 列传回所有工作表名称。(很实用)
Sub Maco1()
For i = 1 To Sheets.Count
Cells(i, 1) = Sheets(i).Name
Next
End Sub
第 112 页共 127 页

基本操作

1.10.6. 无宏时显示了宏警告
按Alt+F11 调出代码窗口---如果Thisworkbook 和每个工作表中没有代码模块，那么，看看工作簿
中包含任何VBA 模块（例如模块1），那么就删除该模块！因为即使是空的VBA 模块也会触发宏
警告对话框！

1.10.7. 出现了“文件正由…编辑”信息
可能由于excel 崩溃造成的，在崩溃时文件没有被释放（即没有被正确保存和关闭）。并说，重新启
动是解决这个问题的唯一方法。其实，可以按ctrl+shift+Delete 这3 个键，关闭excel 程序，再打开
excel 就行了，用不着重新启动！

1.10.8. Excel 在启动时崩溃
Excel 在启动时会打开一个*.xlb 文件，其中是关于菜单和工具栏的自定义信息。如果这个损坏，将
会导致Excel 在启动时崩溃。另外，如果这个文件大小超过500KB ，也会使Excel 崩溃。
如果Excel 在启动时崩溃，试着删除*。xlb 文件，方法是：关闭Excel----开始---搜索---输入“*.xlb”
----搜索出这个文件删除它----重新启动Excel。（注意，删除*.xlb 文件也将删除所有工具栏和菜单栏
的自定义设置

1.10.9. 清除自定义菜单
自定义菜单很方便,但是如果如果做多了自定义菜单,影响视觉的;怎么彻底清除自定义菜单呢?删除
这个文件“*.xlb” , 方法见上面!

第 113 页共 127 页

精通办公软件培训系列教材－Excel

1.10.10. Excel 的操作快捷键
1.10.9.1. “F9”键
在Excel 中当我们调试一个复杂的公式时可能需要知道公式某一部分的值，可以用以下的办法来获
得：双击含有公式的单元格，选定公式中需要获得值的那部分公式，按“F9”键，Excel 就会将被
选定的部分替换成计算的结果，按“Ctrl+Z”可以恢复刚才的替换。如果选定的是整个公式的话，
就可以看到最后的结果。
例如，有这样一条公式：(A1*B1+C1)/D1 ，选定公式中的A1*B1 （假设A1 和B1 的值分别为12 和
8），按“F9”键，Excel 就会将A1*B1 这部分转换为96。这时，公式就变成了(96+C1)/D1 。如果继
续选定D1（假设D1 的值为6），并按“F9”键，Excel 会将公式转换为(A1*B1+C1)/6。当你完成
了公式的计算部分，想恢复成原来的公式，按“Esc”即可。

1.10.9.2. 快捷键大全
快捷键功能版本
ALT + Enter 在单元格编辑状态，可强制换行
ALT+' （撇号）显示“样式”对话框不详
ALT+= 相当于输入SUM 函数不详
Alt+165 （小键盘）人民币符号不详
Alt+178 （小键盘）平方不详
Alt+179 （小键盘）立方不详
Alt+186 （小键盘）度数不详
ALT+A 在替换时选择全部替换
Alt+d+e 分列不详
Alt+Enter 在单元格中换行
Alt+f+v 预览
ALT+F11 显示“Visual Basic 编辑器” 不详
Alt+F4 关闭窗口EXCEL 不详
ALT+F8 显示“宏”对话框不详
Alt+H 打开"帮助"菜单不详

第 114 页共 127 页

基本操作

ALT+W+F 冻结窗格
ctr+shift+; 插入當前時間不详
ctrl + ；当前日期
ctrl+ -删除行列单元格
CTRL+ *（星号）选定当前单元格周围的区域
ctrl+' 重复输入上一个单格数据<不含格式>
CTRL+"-" (数字键盘上的减号) 删除单元格/行/列2000
CTRL+"+" 数字键盘上的加号) 插入单元格/行/列2000
Ctrl +。(Ctrl+点) 可以顺序定位在所选区域的四个角
CTRL+; 快速输入日期(=today())
ctrl+`(1 左边) 公式审核不详
CTRL+`（TAB 键上面）公式与数值的切换
ctrl++( 数字键盘) 插入行列单元格
Ctrl+←↑→↓ 至连续区域的最←↑→ ↓一个单元格 xp
CTRL+0 （零）隐藏列不详
CTRL+1 单元格格式设置。
CTRL+1 显示“单元格格式”对话框
CTRL+2 对字体的加粗与不加粗。
CTRL+3 字体设置成斜体。
CTRL+4 下划线
CTRL+6 在隐藏对象、显示对象与对象占位符之间切换不详
Ctrl+9 隐藏行 xp
CTRL+A 选定整个工作表
ctrl+a 列出当前函数的详细参数帮助不详
Ctrl+B 黑体字 xp
CTRL+C 复制选定区域
Ctrl+D
在所选范围内，复制最上一栏的公式(效果于用鼠标拖动单格
左下角的黑十字架)
Ctrl+D 复制上一單元格的內容<含格式> xp

第 115 页共 127 页

精通办公软件培训系列教材－Excel

CTRL+D 向下填充
CTRL+DELETE 删除插入点到行末的文本不详
Ctrl+End 到区域的结束处
CTRL+ENTER 选定的单元格全部为输入内容不详
Ctrl+F 查詢
ctrl+F10 最大化
CTRL+F11 插入Microsoft Excel 4 。0 宏工作表
CTRL+F4 关闭EXCEL 当前的活动表
ctrl+F5 还原大小
ctrl+F9 最小化窗口
Ctrl+G 定位
CTRL+H 查找和替换
Ctrl+Home 是强迫回到最前一个单元格不详
Ctrl+I 斜体字 xp
Ctrl+K 插入超链接不详
Ctrl+N 新增工作簿 xp
Ctrl+O 打開工作簿
Ctrl+P 打印當前工作表 xp
CTRL+PAGE DOWN 移动到工作簿中的下一个工作表不详
CTRL+PAGE UP 移动到工作簿中的上一个工作表不详
Ctrl+PgDn 到下一个工作表
CTRL+R 把左边的单元格复制过来
CTRL+R 向右填充
CTRL+S 存盘不详
CTRL+SHIFT+ 加号插入空白单元格不详
CTRL+SHIFT+ 箭头键选定区域扩展到单元格同行同列的最后非空单元格
CTRL+SHIFT+! 应用具有千位分隔符且负数用负号 (-) 表示不详
CTRL+SHIFT+# 应用年月日“日期”格式不详
CTRL+SHIFT+$ 应用带两个小数位的“贷币”格式不详

第 116 页共 127 页

基本操作

CTRL+SHIFT+% 应用不带小数位的“百分比”格式不详
CTRL+SHIFT+& 应用外边框不详
CTRL+SHIFT+( 左括号取消隐藏行不详
CTRL+SHIFT+) 右括号取消隐藏列不详
CTRL+SHIFT+* （星号）选定当前单元格周围的区域不详
CTRL+SHIFT+@ 应用小时和分钟“时间”格式，并标明上午或下午不详
CTRL+SHIFT+^ 应用带两个小数位的“科学记数”数字格式不详
CTRL+SHIFT+_ 删除外边框不详
CTRL+SHIFT+~ 设置单元格格式为常规
Ctrl+Shift+0 取消隐藏列 xp
CTRL+SHIFT+1 设置单元格格式为数值,千分号加2 位小数
CTRL+SHIFT+2 设置单元格格式为时间格式12:00AM
CTRL+SHIFT+3 设置单元格格式为日期格式日-月-年
CTRL+SHIFT+4 设置单元格格式为货币格式,美金$
CTRL+SHIFT+5 设置单元格格式为百分比
CTRL+SHIFT+6 设置单元格格式为科学计数法
CTRL+SHIFT+7 设置单元格格式为四周加边框
Ctrl+Shift+9 取消隐藏行 xp
CTRL+SHIFT+END 将选定区域扩展到工作表的最后一个使用的单元格不详
CTRL+SHIFT+ENTER 将公式作为数组公式输入不详
CTRL+SHIFT+F3 由行或列标志创建名称不详
CTRL+SHIFT+HOME 将选定区域扩展到工作表的开始不详
CTRL+SHIFT+PageUP( 或
PageDown)
选择一组工作表
CTRL+SHIFT+SPACEBAR 选定了一个对象，选定工作表上的所有对象不详
Ctrl+shift+方向键按所指方向选取直到非空格的所有项。
Ctrl+Shift+ 向右键选定或取消选定右面的一个字不详
Ctrl+Shift+ 向左键选定或取消选定左面的一个字不详
CTRL+SPACEBAR 选定整列不详

第 117 页共 127 页

精通办公软件培训系列教材－Excel

ctrl+sshift+a 列出当前函数的详细参数帮助不详
CTRL+TAB EXCEL 活动窗口间切换
Ctrl+U 下划線
CTRL+V 粘贴选定区域
Ctrl+W 关闭当前窗口(同样适用于IE 窗口)
CTRL+X 剪切选定区域
CTRL+Z 取消上一次操作
ctrl+ 鼠标滚轮快速调整OFFICE WORD\EXCEL 窗口大小不详
CTRL+ 数字。。+1。。+2 加粗。。+3 加斜。。+4 加下横线不详
Ctrl+ 向左键左移一个单词
enter 粘贴不详
ESC 取消单元格或编辑栏中的输入项
F2 编辑活动单元格并将插入点放置到线条末尾
F4 是切换相对值为绝对值，前一次操作
F4
是切换相对值为绝对值，若原公式为 “= A1” ，按第一次
F4 快捷键变为 “=$A$1” ，按第二次F4 快捷键变为
“=A$1”，按第三次F4 快捷键变为 “=$A1”。不详
F4 重复键\可重复工作表添加删除等许多许多
F4 或 CTRL+Y 重复最后一次操作
F9 键可看联机帮助\可使图表变为静态图表\数组引用变为数组
Alt+F4 關閉Excel xp
shift + enter 可实现enter 的反功能不详
SHIFT+ 箭头键将选定区域扩展一个单元格宽度
SHIFT+BACKSPACE 如果选定了多个单元格则只选定其中的单元格不详
shift+ctrl+f 字体不详
shift+ctrl+p 调整大小不详
SHIFT+CTRL+PAGE DOWN 选择工作簿中当前和下一个工作表不详
SHIFT+CTRL+PAGE UP 选择当前工作簿或上一个工作簿不详
shift+delete 剪切不详

第 118 页共 127 页

基本操作

Shift+End 选定从插入点到末尾的内容不详
SHIFT+F11 插入新工作表
SHIFT+F2 插入单元格批注
Shift+Home 选定从插入点到开始处的内容不详
shift+insert 复制不详
SHIFT+PAGE DOWN 将选定区域向下扩展一屏不详
SHIFT+PAGE UP 将选定区域向上扩展一屏不详
SHIFT+SPACEBAR 选定整行不详
SHIFT+ 空格中文输入法中全角/半角切换不详
Shift+ 向右键选定或取消选定右面的一个字符不详
Shift+ 向左键选定或取消选定左面的一个字符不详

第 119 页共 127 页

精通办公软件培训系列教材－Excel

1.10.11. 汇集多表特定数据
把sheet1 到sheet100 的a18 这一格，依序贴到sheet0 的a1 到a100
操作：方法一：公式
可在A1 储存格输入以下公式，再行拖曳至A100 即可。
=INDIRECT("Sheet"&ROW()&"!A18")
方法二：VBA
Sub Macro1()
Sheets("sheet0").Select '选择工作表 sheet0
For r = 1 To 100 '将工作表 1~200 里面的 a18 复制到 sheet0 的 A1~A200
Cells(r, 1) = Worksheets(CStr(r)).Range("a18")
On Error Resume Next
Next r
End Sub

1.10.12. 比较运算符为何要用引号
(1) 为什么在公式内，运用 "比较运算符号" 时，要把数据连同比较运算符号用 "双引号" 括住，
而其它算术符号就不需要？
例如：=Countif(A1:A30,">=10") 是可以接受，但不加双引号时，便遭拒绝。
(2) 又请问：如上例，要比较的数值是要参照某单元格时，公式应怎样做？
操作：这是CountIF()的规定。第二个参数为字符串，必须用双引号扩起来，除非条件是等于一个
数值。
比如：=COUNTIF(A1:A30,">=4")
=COUNTIF(A1:A30,"condition")
=COUNTIF(A1:A30,220)
用引用作参数（假设B1 值为4）：
=COUNTIF(A1:A30,">=" & B1)

第 120 页共 127 页

基本操作

1.10.13. 输入身份证号码的解决方法
相信在EXCEL 中如何输入15 位或18 位身份证号码或其他长串数字的问题已经困挠了许多
人，因为在EXCEL 中，输入超过11 位数字时，会自动转为科学计数的方式，比如身份证号是：
123456789012345 ，输入后就变成了：1.23457E+14, 要解决的方法有很多种，现为大家说几种比较
简单快速的方法：

1、在A1 单元输入号码时，在前面先输入’号，注意必需是在拼音状态的,这样单元格会认为
该单元为文本方式，会完整显示出15 个号码来，而不会显示科学计数方式来了。

2、批量操作方法，例如:已在单元A1：A100 输入了数据，按以下步骤做：

选择A1：A100 → 鼠标右键→ 设置单元格式→ 选择自定义→ ‘类型’中输入 0 , 如图

3、用函数来解决的方法：在A1：A100 已经输入大量的号码时，在B1 单元中输入： =trim(' 'a1),
注意两个’之间是空格，这个公式的意思:先在A1 单元15 位号码前加个空格符号，然后用trim 这
个函数把空格符号去掉。输完后，B1 单元也是显示出完整的15 位数字了。

1.10.14. 小数问题
如果是精确计算,那么要慎用工具条上的显示小数位数按钮

来控制小数位数，它只是

显示变化了，实际还是按原来的数计算。
如：1.2345 ，你显示两位，为1.23 ，而实际他是按1.2345 去计算的，
最保险是用round 函数，=round ( 数据,2) 即可。
还可在选项里设置按显示计算，但是会产生意想不到的后果,此方法也要慎用。
如果是财务数据,建议无论用什么函数都要在最外面加上 round() 函数.

第 121 页共 127 页

精通办公软件培训系列教材－Excel

1.10.15. 文件修复
一个Excel 文件是一个工作簿，一般可以包含255 个工作表，每个工作表中可以包含大量的数
据。如果一个Excel 文件部分受损，不能正常打开，该怎么办呢？

手动处理1 进入Word ，打开要修复的XLS 文件，如果Excel 只有一个工作表，会自动以
表形式装入Word ，若文件是由多个工作表组成，每次只能打开一个工作表。打开后，先将文件中
损坏的数据删除。用鼠标选中［表格］→［转换］→［表格转文本]，注意可用“，”间隔符
或其它分隔符，另存为一个TXT 文本文件。在Excel 中直接打开该文本文件，在打开时，Excel 会
提示文本导入向导，一般情况下只要直接点击［下一步］即可，打开后另存为其它的Excel 文件即
可。注意：这种修复的方法是利用Word 的直接读取Excel 文件的功能实现，该方法在文件头
没有损坏，只是文件内容有损坏的情况下比较有效；对文件头已经损坏的Excel 文件，此方法可能
不成功，必须借助于其它方法。

手动处理2 用Excel 修复工具Concept Data 公司提供了一个专门用于修复Excel 损坏
文件的修复工具— ExcelRecovery ，能够对Excel 5.0/97/2000 的文件进行恢复处理.网络还有其他
修复工具!

第 122 页共 127 页

基本操作

第2 章窗体和VBA

4.1. 窗体
待续

4.2. VBA
在Excel 中自定义函数
Excel 函数虽然丰富，但并不能满足我们的所有需要。我们可以自定义一个函数，来完成一些特定
的运算。下面，我们就来自定义一个计算梯形面积的函数：

1.执行“工具→宏→Visual Basic 编辑器”菜单命令（或按“Alt+F11 ”快捷键），打开Visual Basic
编辑窗口。
2.在窗口中，执行“插入→模块”菜单命令，插入一个新的模块——模块1。
3.在右边的“代码窗口”中输入以下代码：
Function V（a,b,h）
V = h*（a+b）/2
End Function
4.关闭窗口，自定义函数完成。
以后可以像使用内置函数一样使用自定义函数。
提示：用上面方法自定义的函数通常只能在相应的工作簿中使用。
4.2.1. 电子表格设奇数、偶数页打印
利用 Get。Document 的方法设置
Sub PrintDoubleSided()
Dim Totalpages As Long
Dim pg As Long
Dim oddoreven As Integer
On Error GoTo enditt
Totalpages = ExecuteExcel4Macro("Get 。Document(50)")

第 123 页共 127 页

精通办公软件培训系列教材－Excel

oddoreven = InputBox("Enter 1 for Odd, 2 for Even")
For pg = oddoreven To Totalpages Step 2
ActiveWindow 。SelectedSheets 。PrintOut from:=pg, To:=pg
Next pg
enditt:
End Sub

4.2.2. 工作表中的小写字母都变成大写
运行以下程序：(测试通过)
Sub ConvertToUpperCase()
Dim Rng As Range
Worksheets("Sheet1").UsedRange.Select
For Each Rng In Selection.Cells
If Rng.HasFormula = False Then
Rng.value = UCase(Rng.value)
End If
Next Rng
End Sub

4.2.3. 一次移除文件内超链接(Excel。Word 。PPT)
Excel
ActiveSheet 。Hyperlinks 。Delete
Word
For Each link In ActiveDocument 。Hyperlinks
link 。Delete
Next
PowerPoint
For Each link In ActiveWindow 。Selection。SlideRange。Hyperlinks
link 。Delete
Next

第 124 页共 127 页

基本操作

4.2.4. 批量删除工作表中的定义名称
Sub DelName()
For Each Name In ThisWorkbook 。Names
Name 。Delete
Next
End Sub

4.2.5. 条件格式的代码
在单元格a1:z50 区域内如果1-3 范围则单元格数据是绿色的，否则是红色
On Error Resume Next

For Each c In Sheets(1) 。Range("a1:z50")
If c 。Value = 1 Or c 。Value = 2 Or c 。Value = 3 Then
c。Font。Color = RGB(255, 0, 0)

Else
If c 。Value = 6 Or c 。Value = 8 Or c 。Value = 9 Then
c。Font。Color = RGB(0, 255, 0)

Else
If c 。Value = 5 Or c 。Value = 10 Or c 。Value = 11 Then
c。Font。Color = RGB(0, 0, 255)
End If

End If
End If
Next c

4.2.6. 插入批量空白行
Sub nolo()

Dim i

For i = 1 To100
i= i + 1

第 125 页共 127 页

精通办公软件培训系列教材－Excel

Cells(i, 1) 。Insert Shift:=xlDown
Next i
End Sub

4.2.7. 批量删除网页复制用来打‘勾’的小图形
Sub test()
ActiveSheet 。Hyperlinks 。Delete
For Each d In ActiveSheet 。OLEObjects
If UCase(d。ProgId) Like "*CHECK*" Then d 。Delete
Next
End Sub

4.2.8. 批量删除相同的数据行
Sub test()

Application 。ScreenUpdating = False

N=0

For r = [A65536] 。End(xlUp) 。Row To 1 Step -1

V = Cells(r, 1) 。Value

If Application 。WorksheetFunction 。CountIf(Columns(1), V) > 1 Then

Rows(r) 。EntireRow 。Delete

N=N+1

End If

Next r

Application 。ScreenUpdating = True
End Sub

4.2.9. EXCEL 中的自动宏
如果想作一个在EXCEL 工作薄打开时就自动执行的宏, 请将此宏的名称,取为auot_open, 如果在打
开excel 工作薄时不执行自动宏, 只须按信SHIFT 键就可以了
下面的代码可以将任意日期（1000-01-01------>9999-12-31）转换为汉字显示

第 126 页共 127 页

基本操作

Sub Test()
MsgBox datename(Date)
End Sub
Function datename(ByVal mydate As Date) As String
Dim i As Long, d(3) As String, myyear As String
myyear = Format(mydate, "yyyy")
For i = 1 To4
d(0) = d(0) & Mid(" ○一二三四五六七八九", CInt(Mid(myyear, i, 1)) + 1, 1) 'trans year to chinese
character
Next
d(1) = "年" & MonthName(Month(mydate)) 'add month name in chinese as “十月”
d(2) = Choose(Day(mydate) \ 10 + 1, "", " 十", "二十", "三十") & Mid(" 一二三四五六七八九",
Day(mydate) Mod 10 + 1, 1) & " 日"
d(3) = "，" & WeekdayName(Weekday(mydate)) 'add weekday name in chinese as “星期三”
datename = Join(d, "") 'combine year,month and day,weekday
datename = Replace(datename, " ", "") 'avoid space in result
End Function

第 127 页共 127 页

[ 本帖最后由 blueskyandwater 于 2010-5-2 00:52 编辑 ]

普及衛星電視才是中華民族通往民主自由之路

TOP

blueskyandwater

围观

驴民高工

Rank: 6 Rank: 6

自由人

5^# 大中小发表于 2010-5-2 00:26 只看该作者

发完了!看着我都晕啊!

[ 本帖最后由 blueskyandwater 于 2010-5-2 00:47 编辑 ]

普及衛星電視才是中華民族通往民主自由之路

TOP

blueskyandwater

围观

驴民高工

Rank: 6 Rank: 6

自由人

6^# 大中小发表于 2010-5-2 00:29 只看该作者

技术咨询的,下面跟帖,请勿灌水! 谢谢

[ 本帖最后由 blueskyandwater 于 2010-5-2 00:54 编辑 ]

普及衛星電視才是中華民族通往民主自由之路

TOP

big_jackass

大坏蛋

卫戍部队

Rank: 15

基地坏蛋联盟

偶然贡献奖杰出贡献

7^# 大中小发表于 2010-5-2 00:30 只看该作者

回复 1# blueskyandwater 的帖子

上传图片可以使用图床
教程请见：
https://cmule6.info/viewthread.php?tid=272467

还有附件可以上传到讯6（Google一下就知道），发下载网址上来。分数先给你加了

[ 本帖最后由 big_jackass 于 2010-5-2 00:32 编辑 ]

TOP

blueskyandwater

围观

驴民高工

Rank: 6 Rank: 6

自由人

8^# 大中小发表于 2010-5-2 00:32 只看该作者

留给其他人

[ 本帖最后由 blueskyandwater 于 2010-5-2 00:53 编辑 ]

普及衛星電視才是中華民族通往民主自由之路

TOP

blueskyandwater

围观

驴民高工

Rank: 6 Rank: 6

自由人

9^# 大中小发表于 2010-5-2 00:33 只看该作者

占位,给后面咨询技术的人,回复用的!

[ 本帖最后由 blueskyandwater 于 2010-5-2 00:53 编辑 ]

普及衛星電視才是中華民族通往民主自由之路

TOP

blueskyandwater

围观

驴民高工

Rank: 6 Rank: 6

自由人

10^# 大中小发表于 2010-5-2 00:56 只看该作者

引用:

原帖由 big_jackass 于 2010-5-2 00:30 发表上传图片可以使用图床教程请见： https://cmule6.info/viewthread.php?tid=272467还有附件可以上传到讯6（Google一下就知道），发下载网址上来。分数先给你加了

谢谢,基地是敏感地方,发了下载地址,就知道我了...

只要有心学习,文字也是一样的!

而且我经常来解决各位提出的EXCEL问题

普及衛星電視才是中華民族通往民主自由之路

TOP

big_jackass

大坏蛋

卫戍部队

Rank: 15

基地坏蛋联盟

偶然贡献奖杰出贡献

11^# 大中小发表于 2010-5-2 01:02 只看该作者

回复 10# blueskyandwater 的帖子

推荐的图床和讯6服务器都不在国内，基本上是安全的。

不过谨慎一些是好事。

TOP

老西

一坛一世界～

基地副总

Rank: 30

基地首席菜鸟大队长

首席劳模基地十字军偶然贡献奖杰出贡献

12^# 大中小发表于 2010-5-2 01:10 只看该作者

回复 10# blueskyandwater 的帖子

没你想的那么严重·～

发到这里去，没问题的～
http://ishare.iask.sina.com.cn/
图文并茂，多好，要是PDF格式的就更爽了～

TOP

老西

一坛一世界～

基地副总

Rank: 30

基地首席菜鸟大队长

首席劳模基地十字军偶然贡献奖杰出贡献

13^# 大中小发表于 2010-5-2 01:12 只看该作者

回复 10# blueskyandwater 的帖子

当然，这里还有N多网盘，随便挑～

http://www.free8.com/yingpan

TOP

jenderlee

驴民顾问

Rank: 8 Rank: 8

基地评论员

14^# 大中小发表于 2010-5-2 01:32 只看该作者

这帖子必须收藏

TOP

梦欺骗你我

驴民工程师

Rank: 5 Rank: 5

15^# 大中小发表于 2010-5-2 10:48 只看该作者

强烈感谢.

TOP

well530

驴民代表

Rank: 10

16^# 大中小发表于 2010-5-2 13:42 只看该作者

先收藏，再慢慢学习！谢谢LZ

TOP

@cmule 该用户已被删除	17^# 大中小发表于 2010-5-2 13:52 只看该作者提示: 作者被禁止或删除内容自动屏蔽
	TOP

xhypsy

驴民高工

Rank: 6 Rank: 6

18^# 大中小发表于 2010-5-2 17:01 只看该作者

先支持一下楼主，但是看着真累，能否做成PDF电子书，在电驴发布呢

TOP

yutaohu

驴民技师

Rank: 4

19^# 大中小发表于 2010-5-2 17:42 只看该作者

我搞过档案之类,觉得没什么领导重用我,就没往下发展了

TOP

yinghuashijie

驴民技师

Rank: 4

20^# 大中小发表于 2010-5-2 17:45 只看该作者

学习一下了

TOP

舍兀

驴民工程师

Rank: 5 Rank: 5

21^# 大中小发表于 2010-5-2 20:30 只看该作者

Excel要学的东西太多了，收藏了！谢谢楼主

TOP

hkron

驴民工程师

Rank: 5 Rank: 5

22^# 大中小发表于 2010-5-3 01:42 只看该作者

不错啊，有用的

TOP

accessdiver

驴民工程师

Rank: 5 Rank: 5

23^# 大中小发表于 2010-5-4 13:58 只看该作者

先谢LZ, 再收藏, 最后有空学习之.

另听一跳槽到日企的同事说, 在日企常用EXCEL, 水平大涨.

TOP

geohuskyer

臭猴子

等待验证

驱逐共匪，恢复中华！

24^# 大中小发表于 2010-5-4 15:01 只看该作者

我用VBA算不错，嘿嘿，有空可以和大家多交流。

TOP