Excel操作技巧与实例大全Word下载.docx

Excel操作技巧与实例大全Word下载.docx

《Excel操作技巧与实例大全Word下载.docx》由会员分享，可在线阅读，更多相关《Excel操作技巧与实例大全Word下载.docx（46页珍藏版）》请在冰豆网上搜索。

1.14快速删除工作表中所有的文本框错误！

2函数数组错误！

2.1函数宝典错误！

2.2自定义函数157个错误！

未定义书签

2.3VLOOKUP错误！

2.4OFFSET错误！

2.5IS函数错误！

2.6数组公式特征错误！

2.7SQL语句在EXCEL中的典型用法错误！

3图表错误！

3.1复合饼图错误！

3.2图表组合错误！

3.3甘特图错误！

3.4带滚动条的图表错误！

3.5动态图表的制作错误！

4数据分析错误！

4.1数据有效性错误！

4.1.1防止重复录入.错误！

4.1.2只能输入日期.错误！

4.1.3输入的内容中必须包括某字符.错误！

4.1.4制作二级选项菜单：

4.2整合数据表错误！

4.3自定义数字格式的实用案例错误！

4.4批量取消合并单元格并使用CTRL+ENTER填充数据错误！

4.5隐藏小于3000的数值错误！

4.6将一列数据转化为多列数据错误！

4.7将多列数据转化为一列错误！

4.8合并计算汇总多表数据错误！

4.9利用合并计算核对数值型数据错误！

5实例技巧错误！

5.1各种各样的排名汇总错误！

5.2资产折旧错误！

5.3巧设单元格格式打印席卡错误！

操作技巧

关于换行Alt+Enter

同一单元格内，有些长数据或条列式内容必须强行换行才能对齐。

方法是光标移到在需要换行的位置上同时按下Alt+Enter键（使用强行换行时，系统会同时选择自动换行功能）。

1．输入数据随时换行

用户若要在输入数据时换行，只要通过Alt+Enter组合键即可轻松实现。

此方法同样可使已输入内容的单元格在光标所在处换行。

2．单元格区域内换行

将某个长行转成段落并在指定区域内换行。

例如：

A10内容很长，欲将其显示在A列至C列之内，步骤是：

选定区域A10:

C12（先选A10），选择“编辑→填充→内容重排”，A10内容就会分布在A10:

C12区域中。

此法特别适合用于表格内的注释。

3．调整单元格格式换行

选定单元格，选择“格式→单元格”，在弹出的对话框中单击“对齐”，选中“自动换行”复选框，单击[确定]按钮即可。

4.录制一个宏,定义快捷键

快速在多上合计行中求和

选择区域---按F5---选择“空值”---按“Alt+="

快捷键。

快速在多上合计行中求和.gif

不复制隐藏的行或列

a首先选中需要复制的被隐藏了一些行或列的表格区域；

b然后点击“编辑”-“定位”-“定位条件”，在其中选择“可见单元格”；

c复制表格区域，粘贴即可。

查找通配符

在Excel中，如何替换单元格中的*、?

号呢？

由于二者在“查找和替换”对话框中扮演通配符的角色，代表多个、单个任意字符，而要替换这两个符号本身，需要在前面增加一个~符号，如附图所示。

同理要替换“~”符号时，也需要在其前面增加一个“~”符号。

文本处理

等长文本的分割

从字符串的左边取字符：

=Left（字符串，文本长度）

从字符串的右边取字符：

=Right（字符串，文本长度）

从字符串的中间取字符：

=Mid（字符串，文本起始位置，文本长度）

不等长文本的分割

第1步：

选中要进行分割的字符串区域。

第2步：

点击“数据”－“分列”，在文本分列向导中选择适合

的分隔符

第3步：

设置需要导入的列以及放置该列的位置

文本的合并

=Concatenate（文本1，文本2，⋯）

使用连接符&

：

文本转数值的10种办法

把引号中的字符串“.TrendsinGenetics23（4）:

192–9“中

的前半部分英文提取出来

使用数组公式=LEFT（A1,MIN（FIND（ROW（1:

10）-1,A1&

1/17））-1）

也可使用公式=LEFT（A1,MIN（FIND（ROW（1:

"

0123456789"

））-1）

说明：

1/17是一个包含了0到9的数字小数。

目的是在原数据的后面连接上“0123456789"

，以避免find时出错。

这是简化公式的常用手法，与此类似作用的还有5^19。

使用分列更改文本为数值

如果你发现在Excel中输入的公式计算结果不正确，也许是单元格中保存的数字被保存为文本类型的原因。

这时需要将文本类型的数这转为数值，方法如下：

高级筛选

由于自动筛选只能筛选出简单条件的数据，因此如要在复杂条件

下进行筛选就需要采用高级筛选的方式。

在需要进行筛选的数据表外设定筛选条件。

在对话框中设定：

列表区域为数据表区域；

条件区域为刚才在数据表外部设定的条件区域，要包括字段名称和条件所在的单元格区域。

如何将一列数据中在另外一列出现过的项目筛选出来

条件格式

条件格式可以使得符合特定条件的记录按照某种设定的格式显示。

选中要进行条件格式设定的单元格范围，执行【格式】【条件格式】

在条件格式设置界面进行条件的设定：

数据透视表

应用数据透视表进行各类数据分析

选择“数据”－“数据透视表和数据透视图”，进入透视表设置向导。

点击“下一步”，选择正确的数据范围

点击“下一步”，选择“布局”按钮

在如下图界面上，将右侧的字段拖入左侧相应区域内

形成如下图的布局，点击确定按钮

生成如下的透视表后，将鼠标悬停在想要移动的字段上，鼠标左

键按下，将字段拖放到其他区域，生成自己需要的数据显示。

数据百分比显示：

右键菜单－字段设置－选项，将数据显示方式改为“占同列数据总和的百分比”

1）在【布局】里将销售收入两次拖入数据区域

2）将其中一个销售收入改为百分比显示

3）在报表项目上输入新的名称可以修改项目名称

4）将报表项目拖拽到列标题位置，可以将数值和百分比改为按列排

列

筛选数据：

点击字段名称后面的下拉菜单，可以对字段内容进行筛选。

显示明细数据：

双击需要查看明细的数据，在新的工作表上将显示构成此汇总数据的所有明细数据。

1.对数据进行排序：

把光标放在行位置的报表项目上，【右键菜单】【字段设置】，

【高级】按钮，左侧可以进行排序设置。

2.按照日期分组：

1）将日期放入行区域，【右键菜单】【组及显示明细数据】【组合】

2）在【步长】中选择需要的分组标准

3.按照数值分组：

1）将销售收入放入行区域，【右键菜单】【组及显示明细数据】【组合】

2）在【步长】中选择需要的分组标准

4.插入计算字段：

在【名称】后给计算字段定义名称

在【公式】后输入计算字段的计算公式，可以引用下面的字段

自动更新：

【右键菜单】【表格选项】【打开时刷新】可以让报表始终有更新后的数据显示。

透视图

可以单独生成数据透视图，也可以基于现有的透视表生成透视图。

如果已经有现成的透视表，通过点击数据透视表工具条上的图表按钮可以生成一个透视图。

通过拖动透视图上的各个字段到右侧或者底部的位置，可以方便地改变图表组织数据的方式。

隐藏透视图字段按钮：

把光标悬停在任意字段上，右键菜单，选择【隐藏数据透视图字段按钮】，即可以将字段按钮隐藏；

再次显示字段按钮：

点击透视图工具条上第一项，在出现的下拉菜单中选择【隐藏数据透视图字段按钮】

如果有些数据无法在数据透视表内部分析，可引用透视表的数据作为分析基础。

多重合并计算数据

如下图格式的即为二维表：

现在我们需要将数个格式相同的二维表汇总为一张表格，且可以区分不同表格属性进行分析，比如三张表分别为北京，上海，深圳分公司的表格。

我们使用的方法是利用透视表多重合并计算数据区域的功能。

选择数据-数据透视表和数据透视图

选择第3个数据源类型：

多重合并计算数据区域，点击下一步按钮：

在出现的界面上选择：

自定义字段

选择需要被合并的表格区域，点击添加将其添加到所有区域；

将页字段数据改为1，在项标志处输入该表格的标志，使用相同的方法将其他需要合并的表格全部添加。

透视表生成后如下图所示，已经将多个表格合并成一个：

双击字段名，可以对字段名进行修改：

名称的引用

名称

名称可以代表一个单元格或者一个单元格区域，或者是常量，公式。

名称的定义：

选中需要命名的单元格或区域，在界面左上角名称框中输入名称后回车；

名称的删除：

插入－名称－定义，选中需要删除的名称点击删除按钮，点确定；

名称的引用：

需要引用某单元格时输入该单元格的名称：

＝名称

选中需要命名的某个单元格或单元格区域

在左上角名称框输入命名后回车。

删除已有命名：

选择“插入”－“名称”－“定义”；

选中需要删除的名称，点击“删除”按钮。

名称命名的优点：

1）避免绝对引用的错误

2）对公式进行文字化表述，让公式更加容易理解

3）可以在整个工作簿中通用，引用方便

自定义格式语法：

大于条件值]格式；

[小于条件值]格式；

[等于条件值]格式；

文本格式

动态表头：

ABC公司"

&

YEAR（NOW（））&

年"

"

MONTH（NOW（））&

月"

报表"

行与列的合计有时出现不相等的情况。

选择“工具”－“选项”－“重新计算”－“以显示精度为准”，选中该选项。

表格的几种类型及其处理技巧

数据表类型

操作目标

汇总方法

是否需要重复制作

是否与数据源有链接关系

多字段数据列表

记录叠加

导入外部数据+

SQL语句

不需要

有

并按表名区分

二维表

多个二维表生成透视表

透视表多维数据区域合并

同一文件内的报表

数据汇总

=sum（begin:

end!

B2）

不同文件中的报表

合并计算

非标准数据表

汇总

建议修改原表的结构或者仅作为终端报表，上层再建立一个数据源表作为收集数据使用

需要

无

快速删除工作表中所有的文本框

有时公莫名其妙地在工作表中添加许多文本框，想删除所有文本框可以用附图中的方法。

其他插入的对象，如图片、自选图形等都可以用同样的方式删除哦。

函数数组

函数宝典

自定义函数157个

vLookup

vlookup函数详解@Excel_Home.xls

Offset

OFFSE引T用函数应用_ByGdliyy.xls

IS函数

可以检验数值的类型并根据参数取值返回TRUE或FALSE。

函数

如果为下面的内容，则返回TRUE

ISBLANK

值为空白单元格。

ISERR

值为任意错误值（除去#N/A）。

ISERROR

值为任意错误值（#N/A、#VALUE、!

#REF!

、#DIV/0!

、#NUM、!

#NAME或#NULL!

）。

ISLOGICA

值为逻辑值。

ISNA

值为错误值#N/A（值不存在）。

?

ISNONTEX

T值为不是文本的任意项（注意此函数在值为空白单元格时返回

T）。

ISNUMBER

值为数字。

ISREF

值为引用。

ISTEXT

值为文本。

数组公式特征

对单元格区域进行多重计算的计算方式与普通计算公式的区别

是录入公式结束后，需要同时按下ctrl+shift+enter，其特征

是在公式两端会出现一对大括号。

SQL语句在EXCEL中的典型用法

图表

复合饼图

当数据系列内值的大小差异较大时，可以制作复合饼图以便数据显示更为清楚，如下图所示：

选择饼图中的复合饼图子图表类型：

点击下一步，切换到图例标签，将显示图例的勾去掉：

切换到数据标志标签，把类别名称和百分比打勾：

选中数据系列，右键菜单，选择数据系列格式：

切换到选项标签，将第二绘图区包含最后的值改为：

4

复合饼图制作完成

图表组合一个图表中的不同数据系列可以采用不同的图表类型显示，

在图表工具条上选择数据系列Compensation:

在图表工具条上选择数据系列格式按钮：

在数据系列格式界面上，切换到坐标轴标签，将主坐标轴改为次坐标轴：

右键点击Compensation,选择图表类型：

将其图表类型改为折线图；

用同样的方式操作Production系列，将其图表类型改为面积图；

完成图表组合的制作。

下拉菜单式图表：

写入如下的公式：

index函数查找出的值由A9单元格来指定

制作三维饼图，其数据区域为A3:

I3,A9:

I9,需要手工选取

点击“视图”-“工具栏”-“窗体”，调出窗体工具条：

在窗体工具条上选择组合框，在饼图上拖放出一个组合框：

右键选中组合框，在菜单中选择“设置控件格式”：

在控制标签中做如下设置：

其中单元格链接为存储控件选中项目

序号的单元格

完成下拉菜单式图表的制作：

甘特图

如果想制作如下图的甘特图，需要以下4列数据：

首先制作堆积条形图：

点击下一步，切换到系列标签，删除结束日期系列：

生成的条形图如下图所示：

面转换纵坐标的项目排列次序：

双击纵坐标，切换到刻度，将分类次序反转和数据轴交叉于最大

分类选项打勾

此时纵坐标次序已经反转

双击开始日期数据系列，在数据系列格式对话框中将边框和内部

都改选为无，此时开始日期数据系列隐藏。

此时需要将横坐标开始日期改为真正的项目开始日期：

在excel中，每一个日期都对应一个数值，选中B2,查看2008-7-1对应数值为39630

双击横坐标，切换到刻度，将最小值改为39630，同样将横坐标

最大值改为真正的项目结束日期

甘特图制作完成

带滚动条的图表

该图表中可以随着点击滚动条的动作而动态翻看源数据中的大量数据。

定义两个动态引用的名称：

Period:

=offset（'

7.动态图表'

!

$A$1,'

7.动态图表

'

$D$1,0,10,1）

Data:

$B$1,'

制作折线图，选择系列标签，删除period系列：

将“值”和“分类（X）轴标志”设置为如下图内容：

调出窗体工具条，在图表外绘制滚动条，右键选中滚动条，选择设置控件格式：

在单元格链接中设置为D1:

动态图表的制作

选中北京，深圳，上海以下的区域，分别定义北京，深圳，上海的名称

选择视图-工具栏-窗体，选择选项按钮，画出如下三个选项按钮：

右键选中选项按钮，在右键菜单中选择设置控件格式，在跳出的

对话框中选择控制标签，在单元格链接中选择A16：

在A17单元格中输入公式：

=choose（a16,”北京”,”上海”深圳”），为A17定义名称：

choose

在A2,A3,A4单元格中输入以下内容：

在B16单元中写入以下公式，并复制到该行1-12月的单元格中：

=VLOOKUP（A3,INDIRECT（choose）,COLUMN（）-1,0）

依据此数据表制作双曲线图：

数据分析

数据有效性

防止重复录入

只能输入日期

输入的内容中必须包括某字符

=not（iserror（find（"

中国"

g30）））

制作二级选项菜单：

首先将一级选项的每个项目定义一个名称，该名称内容包括相应的二级项目；

制作一级项目的有效性；

制作二级项目的有效性：

内容为：

=INDIRECT（g6），其中g6为设定了有效性的一级选项所在的单元格。

整合数据表

我们经常需要将几个表格中的数据整合成一张表，如下图所示，

将5个月的数据（目前分布在5张表格上）整合到一个总表上：

我们使用的方法是利用office的查询工具query进行的，这样做的优点在于今后对于新数据的更新可以自动化刷新，而不需要重复的进行整合操作（如复制粘贴）。

office的查询工具query在典型安装office时是不会被安装的，我们需要首先检查我们的office是否已经安装了query，方法如下：

打开Excel，点击【数据】菜单中【导入外部数据】－【新建数据库查询】，若系统提示安装，则说明该功能未被安装，此时可能需要在光驱中插入Office安装光盘完成该功能的安装。

在光驱中插入安装光盘后，安装过程中选择自定义安装；

点开Excel前面的加号，选中要安装的功能前面的下拉菜单，选择【从本机运行】；

点开【Office工具】前面的加号，选中【MicrosoftQuery】的下拉菜单，选择【从本机运行】，然后执行余下的安装过程即可。

面开始整合工作：

选择excelfiles*: