EXCEL操作技巧企业培训版.docx

EXCEL操作技巧企业培训版.docx

《EXCEL操作技巧企业培训版.docx》由会员分享，可在线阅读，更多相关《EXCEL操作技巧企业培训版.docx（68页珍藏版）》请在冰豆网上搜索。

EXCEL操作技巧企业培训版

序言5

关于本书5

Excel5

第1章基本操作6

1.1文件6

1.1.1另存为6

1.1.2页面设置6

1.1.2.1页面6

1.1.2.2页边距6

1.1.2.3页眉页脚7

1.1.2.4工作表:

8

1.1.3属性：

8

1.1.4打印区域8

1.2编辑8

1.2.1选择性粘贴8

1.2.2粘贴为超链接9

1.2.3移动或复制工作表9

1.2.4查找（替换）9

1.2.5定位10

1.2.6链接10

1.3视图11

1.3.1分页预览11

1.3.2任务窗格11

1.3.3自定义工具栏11

1.4插入12

1.4.1名城12

1.4.2批注13

1.4.3对象14

1.4.4超链接14

1.5格式15

1.5.1单元格15

1.5.1.1数字15

1.5.1.2对齐19

1.5.1.3字体19

1.5.1.4边框19

1.5.1.5图案19

1.5.1.6保护19

1.5.2工作表20

1.5.3自动套用格式20

1.5.4条件格式20

1.6工具21

1.6.1保护21

1.6.2宏21

1.6.3加载宏22

1.6.4自动更正选项22

1.6.5自定义22

1.6.6选项23

1.6.6.1视图23

1.6.6.2重新计算23

1.6.6.3编辑24

1.6.6.4常规24

1.6.6.51-2-3的帮助：

24

1.6.6.6自定义序列：

25

1.6.6.7错误检查25

1.6.6.8安全性25

1.6.7数据分析26

1.7数据26

1.7.1排序26

1.7.2筛选26

1.7.2.1自动筛选26

4.7.2.2高级筛选26

1.7.3记录单27

1.7.4分类汇总28

1.7.5有效性28

1.7.6模拟运算表31

1.7.7分列32

1.7.8合并计算33

1.7.9组及分级显示33

1.7.10数据透视表和数据透视图34

1.8窗口34

1.8.1拆分35

1.8.2冻结35

1.8.3当前文件35

1.9帮助36

第2章常用函数（公式）37

2.1函数基础知识37

2.2常用函数40

2.2.1常用函数40

2.2.1.1SUM40

2.2.2数据库函数：

40

2.2.3查找引用函数40

2.2.4日期时间函数40

2.2.5财务会计函数40

2.2.6信息函数40

2.3数组40

2.4宏表函数40

第3章图表40

第4章窗体和VBA40

第5章综合应用案例分析40

第6章经典技巧大典40

序言

关于本书

本书给那些经常使用EXCEL，又不熟悉它的强大功能的人准备！

本书不讲类如安装，新建，保存等非常基础的东东，如果这些都不会就不要读此书了！

本书可以达到的效果是：

！

Excel

第1章基本操作

1.1文件

1.1.1另存为

为了有效保护文档安全，经常需要设置密码：

（OFFICE密码只能放菜鸟）

A.文件→另存为→工具→常规选项→输入密码→确定（要覆盖原文件才能设置成功密码，否则另存为新文件，只能是新文件有密码的）

还有一种设置密码方法：

B工具→选项→安全性→输入密码→确定

1.1.2页面设置

1.1.2.1页面

1.方向：

选择打印的纸张方向

2.缩放：

.调整为，意思把多于一页的表格调整为特定页数！

此操纵前，最好先把页边距调整好（.文件→打印预览→

页边距）。

此功能等同于（视图→）分页预览。

1.1.2.2页边距

1.上下左右，页眉页脚：

设置页边距；等同于.文件→

打印预览→页边距

2.居中方式：

数据（图表）在这个页面里面的居中（水平，

垂直）位置，

1.1.2.3页眉页脚

1.自定义页眉（页脚）：

文件→页面设置→自定义页眉（页脚）

从左至右顺序功能

01.

:

设置页眉/页脚文字的属性,例如:

字体,字型,大小,下划线,删除线,上标,下标

02.

页码:

当前第几页

03.

:

总页数,所有数据共有几页

04.

:

日期:

系统当前日期,是自动更新的

05.

:

时间系统当前时间,是自动更新的

06.路径文件:

此文件的绝对路径+文件名,例如

07.文件:

当前文件名

08.标签名:

当前表名

09.

:

图片:

插入图片,一般是企业LOGO或者徽标

10.

设置:

插入图片后,设置图片格式的

11（12.13）:

位置:

设置页眉（页脚）在在文件的左,中,右位置

实际应用例子:

1.要求每张文件右下脚都要有”制表人”,？

答：

就在上图‘’13”位置直接填入:

”制表人:

”

说明：

工作表设置（详见1.1.2.4工作表）打印标题栏只能打印顶端（左端）标题栏，不能打印尾端标题栏；页脚设置可以部分解决这个问题。

彻底解决就需要设置复杂的公式，详见CD文件！

2.曾经有人问：

正常编辑时候是自己要求的数据和图表，但是预览和打印的时候就在页眉，页脚位置出现一些多余的文字和数据，怎么解决？

答：

看看页眉，页脚的自定义；3.

1.1.2.4工作表:

1.打印区域:

不打印全部文章，只打印部分,等同于.文件→打印区域→设置打印区域

2.打印标题:

每页都打印同样的标题!

实际应用：

如表格很长,要每页都要有标题行,就点击顶端标题行设置即可,左端标题列类似.

3.打印：

网格线把虚线的网格线都打印出来

单色打印彩色的也只是按照黑白打印

按草稿方式不打印边框，彩色打印成黑白色

行号列标是否打印行号列标

批注是否打印批注

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

1.1.3属性：

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

如果文件繁多，你就喜欢这个功能了。

1.1.4打印区域

不想打印全部文章，只是打印部分

.文件→打印区域→设置打印区域

实践经验：

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

1.2编辑

1.2.1选择性粘贴

非常有用的功能，一定要学会！

只介绍常用功能，其他有兴趣自己动手实践

粘贴

1.全部：

等同于“粘贴”（Ctrl+V）

2.公式：

只复制公式

3.把公式变成数值,复制过来（数值化）

4.格式:

只是复制单元格的格式

5.批注：

只是复制单元格的批注

运算（非常实用的技巧）

1.加（减乘除）：

用法等同。

把大量数据一次性加（减乘除）一个特定数据，应用于固定资产折旧的折旧月数等；把一批数据改变正负；把一批数据一次性变成千，万位单位…用法如下：

空白单元格填入需要的数值→复制→选择全部数据→鼠标右键（编辑）→选择性粘贴→选中“数值”，“加（减乘除）”

2."跳过空单元"不复制空单元格"

3.“转置”粘贴数据时选中，把复制的行数据变成列数据了，反之亦然！

（非常实用的技巧）

4."粘贴连接"和源数据（一般是本文件）产生连接,目标数据可以随着源数据改变而更新

1.2.2粘贴为超链接

和源数据（外部文件，一般是OFFICE文件）产生连接,目标数据可以随源数据改变而自动更新。

适用于做套表

1.2.3移动或复制工作表

1.工作簿：

把选定工作表移至（剪切）当前打开的某个excel文件

2.下列选定工作表之前：

把选定工作表移至（剪切）某个表前面

3.建立副本：

选中就不是移至（剪切），而是复制

1.2.4查找（替换）

1．格式：

可以按照特定格式查找（替换）

2．范围：

可在当前工作表，当前整个文件查找

例子：

1.想把表内单元格值为0的为改为空格

按ctrl+H替换→查找内容输入0→替换为空→全部替换。

这样做101变成11,1000001也会变成11，不是我们要的结果！

正确做法:

要勾选"单元格匹配",只替换0值的单元格

2.查找（替换）特殊字符:

*?

（非常重要的知识）

如果对类似"01011061*A"这样带有*的数据处理会出错的,原因就是在Excel里面*表示所有数据,?

表示一个数据（字符）.

A.如果处理带有*，?

的数据,一定先要把*（?

）

换成其他字符，如:

+,-,@..;处理完数据再把*（?

）还原

B.怎样查找（替换）*（?

）:

编辑→查找（替换）→输入

"~*"（以~*代替*,~在Esc键下边）.如图

注意:

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

2."查找范围":

要选择"数值"还是"公式"视情况定

3.如果是查找一定的区域，要先用鼠标选择那个区域，查找前鼠标的位置有

1.2.5定位

.编辑→定位→定位条件

1.批注选择当前表里面所有的批注

2.常量选择当前表里面所有的常量

3.公式选择当前表里面所有公式计算得出的数字，文本，逻辑值，错误值

4.空值选择当前表里面所有的空白值

…….

实际应用：

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

例如：

要把表里面的错误值全部去掉（指公式计算出来的错误值，如果单元格里面的数据就是类似#DIV/0!

这样子的错误文本，使用查找，替换可以解决；详见1.2.4查找），

答：

选择数据区（表格区域）→.编辑→定位→定位条件→公式→选中错误

1.2.6链接

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

1.更新值：

更新链接的数据

2.更改源：

更改数据的链接源

3.打开源文件：

打开引用的源文件

4.断开链接：

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

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

5.检查状态：

检查源文件是否可以更新

6.启动提示：

设置打开excel文件时候的更新属性

注意：

工作中，我经常遇到xp版本的excel自动更新链接的情况，即使在是否更新对话框选择不更新也不行！

所以强烈建议：

做好表格后，把有外部文件链接的数据全部数字化

1.3视图

1.3.1分页预览

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

此操纵前，

最好先把页边距调整好，直接用鼠标拉动蓝色虚线调整！

蓝色实线是数据

区，蓝色虚线是打印线

1.3.2任务窗格

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

1新建工作簿：

选择新建一个指定类型工资簿

2.剪贴板：

处理复制到系统剪贴板的文字图片等

3.搜索：

搜索工作簿内容

4.插入剪贴画：

插入系统自带的剪贴画或者硬盘里面的图片

1.3.3自定义工具栏

我们每个人的工作不同，习惯不同；所以使用excel方式一定不同，为了更快的提高工作效率，自己打造一个符合自己习惯的工具条；其实还有很多实用的功能，系统工具条没有体现出来，下面是操纵步骤：

打开自定义窗口：

视图→工具栏→自定义：

（工具→自定义）

还可以在工具条空白区域点击鼠标右键调出自定义窗口

创建自定义工具条

第一步：

工具栏→新建弹出新建工具栏输入名城

第二步：

点击命令在类别选择需要的分类菜单选中命令里面自己需要的命令按住鼠标左键拖到第一步新建的工具条松开鼠标，完成后见下图：

编辑自定义工具条

1.在打开自定义窗口情况下才能编辑。

在要编辑的按钮上点击鼠标右键，如下窗口

2.常用命令如下：

复位按钮图像：

回复原始按钮图像

编辑按钮图像：

自己用鼠标画图

更改按钮图像：

选择系统提供的图像更改

默认样式：

按钮外观有图无文字

总是用文字：

文字

图像与文本：

图像文字混用

做成自己特有的工具条，如图：

1.4插入

1.4.1名城

名称定义（ctrl+F3）

1.名称:

为了更加直观地标识单元格或单元格区域,我们可以给它们赋予一个名称,单元格区域,鼠标单击编辑栏顶端的“名称框”,在其中输入名称后回车.

也可以选中要命名的单元格或单元格区域,单击“插入→名称→定义”命令,在打开的“定义名称”对话框中输入名称后确定即可.

如果你要删除已经命名的区域,可以按相同方法打开“定义名称”对话框,选中你要删除的名称点击删除即可.

1.输入名称:

中文,英文皆可

2.引用位置是单元格,或者公式函数,宏表函数

3.只有点击"确定",或者"添加",才有效

1.4.2批注

一、批注是附加在单元格中，与其他单元格内容分开的注释。

批注是十分有用的提醒方式，例如注释复杂的公式如何工作，或为其他用户提供反馈。

查看批注内容  MicrosoftExcel提供了几种查看批注的方法。

含有批注的单元格的右上角有三角形的批注标识符。

如果指针停在含有标识符的单元格上，就会显示该单元格的批注。

您也可以连续地显示批注（单条批注或是工作表上的所有批注）。

“审阅”工具栏能使您按顺序逐条查看每项批注。

您还可以将批注打印到工作表的相应位置，或在打印输出结果的底部将批注打印成列表。

批注中的用户姓名  按用户的姓名来区分批注，就可以在共享工作簿或传送给几个用户的工作簿中，判断出键入每项批注的操作者。

当合并几个工作簿的更改时，批注也被合并，这样单元格中所有的批注文字就会按顺序显示。

二、为单元格添加批注

批注是为单元格键入的注释。

单击要加批注的单元格。

在“插入”菜单中，单击“批注”。

在弹出的批注框中键入批注文本。

如果不想在批注中留有您的姓名，请选择并删除姓名。

完成文本键入后，请单击批注框外部的工作表区域。

三、调整批注大小或移动批注

在需要更改批注的单元格上单击鼠标右键，在随后出现的快捷菜单中单击“显示批注”。

单击批注框的边框，显示尺寸控点。

请执行下列操作之一：

更改尺寸  拖动批注框四边或角点上的尺寸控点。

移动批注  拖动批注框的边框。

如果隐藏并重新显示批注，它将返回到其默认位置。

批注中的默认用户名可以在工具》选项》常规中修改

批注的默认字体、字体大小、颜色可以在“windows外观”中修改“工具提示”的相应内容

1.4.3对象

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

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

显示为图标：

在excel里面是否以文件图标形式显示

由文件创建直接把外部做好的文件插到office文件里面

链接到文件：

与文件同步更新

1.4.4超链接

插入→超链接

要显示的文字：

在excel单元格里面显示的文字

查找范围：

超链接文件在本地硬盘的位置

地址：

互联网地址

书签：

等同于本文档中的位置，直接链接到当前工作表的某个单元格，链接到其他表，链接到事先定义好的书签（名称）

1.5格式

1.5.1单元格

1.5.1.1数字

在Excel中使用自定义格式功能：

先选中数据区→格式→单元格→数字→自定义见图表1.5.1，自定义格式的都可删除）

#好象是格式在位符，0表示数位站位符，如设置单元格式为“000”，则输入数字1时，显示为“001”。

？

一般表示一个字节位置。

　　Excel的自定义格式使用下面的通用模型：

正数格式；负数格式；零格式；文本格式。

在这个通用模型中，包含三个数字段和一个文本段：

大于零的数据使用正数格式；小于零的数据使用负数格式；等于零的数据使用零格式；输入单元格的正文使用文本格式。

我们还可以通过使用条件测试，添加描述文本和使用颜色来扩展自定义格式

通用模型的应用。

　　1.使用颜色

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

Excel识别的颜色名为：

[黑色]、[红色]、[白色]、[蓝色]、[绿色]、[青色]和[洋红]。

Excel也识别按[颜色X]指定的颜色，其中X是1至56之间的数字，代表56种颜色。

　　2.添加描述文本

　　要在输入数字数据之后自动添加文本，使用自定义格式为：

＂文本内容＂@；要在输入数字数据之前自动添加文本，使用自定义格式为：

@＂文本内容＂。

@符号的位置决定了Excel输入的数字数据相对于添加文本的位置。

　　3.创建条件格式

　　可以使用六种逻辑符号来设计一个条件格式：

>（大于）、>=（大于等于）、<（小于）、<=（小与等于）、=（等于）、<>（不等于）。

由于自定义格式中最多只有3个数字段，Excel规定最多只能在前两个数字段中包括2个条件测试，满足某个测试条件的数字使用相应段中指定的格式，其余数字使用第3段格式。

如果仅包含一个条件测试，则要根据不同的情况来具体分析。

如下列自定义格式：

[红色]；[绿色]；[蓝色]；：

大于0的数字显示红色，负数显示绿色，负数显示蓝色。

　　[红色][<5]＂小于5＂；[蓝色]G／通用格式：

小于5的数字则显示红色的＂小于5＂文本，其它数字按通用格式显示蓝色。

输入自定义格式＂[红色]；[绿色]＂，单击[确定]后再返回[单元格格式]对话框，则会发现Excel已经将格式规范修改为：

[红色]／通用格式；[绿色]／通用格式Excel自动添加了一个第三段，并指定使用通用格式。

　　实际上，自定义格式的通用模型就相当于如下的条件格式：

[>0]正数格式；[<0]负数格式；零格式；文本格式。

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

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

#,0,?

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

对于#，系统会自动忽略数字前后的0值，如：

格式设置为：

#.##，对于0103.50则只显示103.5；

对于0，系统直接用0来补位，如：

0.00，这也是常用的，如输入103.5则显示103.50；如格式设置为：

00000，输入50则显示成00050，这种也是常用的编号设置法；

对于?

，它除了具有#号功能外（去除前后的无效的0值），还多了一个空格占位的功能，常用于数值小数点对齐，如：

格式设置为：

?

.?

?

，当输入0103.50或103.5则系统显示为103.5_（注：

为了便于理解用_来表示一个空格占位）。

补充说明：

在Excel自定义数字格式使用如下的表达方式：

　　正数的格式　　　负数的格式　　　零的格式　　　文本的格式

　　　#,##0.00　　　[Red]-#,##0.00　　　0.00　　　"TEXT"@

在Excel自定义数字格式的格式代码中，用户最多可以指定四个节；每个节之间用分号进行分隔，这四个节顺序定义了格式中的正数、负数、零和文本。

如果用户在表达方式中只指定两个节，则第一部分用于表示正数和零，第二部分用于表示负数。

如果用户在表达方式中只指定了一个节，那么所有数字都会使用该格式。

如果在表达方式中要跳过某一节，则对该节仅使用分号即可。

#：

数字占位符（非0值占位符）G/通用格式：

用常规格式显示数字

@：

文本占位符*：

重复下一个字符以充满列宽

，：

千位分隔符\:

用这种格式显示下一个字符?

:

数字占位符

实例一：

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

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

实例二：

自定义类型

自定义格式

单元格输入

显示为

解释

使用文本

#,##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.00;"仅显示正数!

"

123.56444

123.56

0用破折号

#,##0_）;（#,##0）;-0-_）

0

-0-

值为0时左右有“—”

#,##0_）;（#,##0）;-0-_）

12.2

12

值为正时常规显示

#,##0_）;（#,##0）;-0-_）

-102

（102）

值为负时用括号括起来

电话号码

（###）###-###

852*********

（852377）659-6295

###"/"###-####

852*********

852377/659-6295

日期格式

mmmm-yy

1995-6-1

June-95

mmmmd,yyyy

1995-6-1

June1,1995

dddd

1995-6-1

Thursday

mmmmd,yyyy（dddd）

1995-6-1

June1,1995（Thursday）

"It's"dddd

1995-6-1

It'sThursday

设置不同颜色

[红色][<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/通用格式

德赛电子（惠州）有限公司

德赛电子（惠州）有限公司

最后一个节规定了文本的颜色

G/通用格式;G/通用格式;G/通用格式;[红色]G/通用格式

234

234

其他符号

©G/通用格式

2004

©2004

###-##-####

421897322

421-89-7322

"社会福利号"###-##-####

421897322

社会福利号421-89-7322

=C113-TODAY（）

2006-12-10

已过期100天!

描述

正数;"负数";"零";"文本"

32

正数

仅以文字的格式表示原来的数，不影响D53至D56单元格