Excel操作技巧与实例(大全)

Excel 操作技巧Skills About Excel

版本：A Rev.20120820（2012-8-20初版）

Email：jungwongc

目录

1 操作技巧............................................... 错误！未定义书签。

1.1关于换行A LT+E NTER ................................... 错误！未定义书签。

1.2快速在多上合计行中求和.............................. 错误！未定义书签。

1.3不复制隐藏的行或列.................................. 错误！未定义书签。

1.4查找通配符.......................................... 错误！未定义书签。

1.5文本处理............................................ 错误！未定义书签。

1.5.1 等长文本的分割................................ 错误！未定义书签。

1.5.2 不等长文本的分割.............................. 错误！未定义书签。

1.5.3 文本的合并.................................... 错误！未定义书签。

1.5.4 文本转数值的10种办法......................... 错误！未定义书签。

1.5.5 把引号中的字符串“.Trends in Genetics 23 (4): 192–9“中的前半部分

英文提取出来.......................................... 错误！未定义书签。

1.5.6 使用分列更改文本为数值........................ 错误！未定义书签。

1.6高级筛选............................................ 错误！未定义书签。

1.6.1 如何将一列数据中在另外一列出现过的项目筛选出来错误！未定义书签。

1.7条件格式............................................ 错误！未定义书签。

1.8数据透视表.......................................... 错误！未定义书签。

1.8.1 应用数据透视表进行各类数据分析................ 错误！未定义书签。

1.8.2 同时显示数据与百分比：........................ 错误！未定义书签。

1.8.3 筛选数据：.................................... 错误！未定义书签。

1.8.4 显示明细数据：................................ 错误！未定义书签。

1.8.5 自动更新：.................................... 错误！未定义书签。

1.8.6 透视图........................................ 错误！未定义书签。

1.8.7 多重合并计算数据.............................. 错误！未定义书签。

1.9名称的引用.......................................... 错误！未定义书签。

1.10自定义格式语法：................................... 错误！未定义书签。

1.11动态表头：......................................... 错误！未定义书签。

1.12行与列的合计有时出现不相等的情况。................. 错误！未定义书签。

1.13表格的几种类型及其处理技巧......................... 错误！未定义书签。

1.14快速删除工作表中所有的文本框....................... 错误！未定义书签。

2 函数数组............................................... 错误！未定义书签。

2.1函数宝典............................................ 错误！未定义书签。

2.2自定义函数157个.................................... 错误！未定义书签。

2.3 V L OOKUP.............................................. 错误！未定义书签。

2.4O FFSET............................................... 错误！未定义书签。

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批量取消合并单元格并使用C TRL+E NTER填充数据 .......... 错误！未定义书签。

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种办法

,

把引号中的字符串“.Trends in Genetics 23 (4): 192–9“中的前半部分英文提取出来

使用数组公式=LEFT(A1,MIN(FIND(ROW(1:10)-1,A1&1/17))-1)

也可使用公式

=LEFT(A1,MIN(FIND(ROW(1:10)-1,A1&"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步：在左上角名称框输入命名后回车。