Excel 操作技巧Skills About Excel
版本：A Rev.20120820 （2012-8-20初版）
Email：
目录
1 操作技巧 (1)
1.1关于换行A LT+E NTER (1)
1.2快速在多上合计行中求和 (1)
1.3不复制隐藏的行或列 (1)
1.4查找通配符 (2)
1.5文本处理 (2)
1.5.1 等长文本的分割 (2)
1.5.2 不等长文本的分割 (2)
1.5.3 文本的合并 (3)
1.5.4 文本转数值的10种办法 (3)
1.5.5 把引号中的字符串“.Trends in Genetics 23 (4): 192–9“中的前半部分
英文提取出来 (3)
1.5.6 使用分列更改文本为数值 (4)
1.6高级筛选 (6)
1.6.1 如何将一列数据中在另外一列出现过的项目筛选出来 (7)
1.7条件格式 (7)
1.8数据透视表 (8)
1.8.1 应用数据透视表进行各类数据分析 (8)
1.8.2 同时显示数据与百分比： (10)
1.8.3 筛选数据： (10)
1.8.4 显示明细数据： (11)
1.8.5 自动更新： (12)
1.8.6 透视图 (12)
1.8.7 多重合并计算数据 (13)
1.9名称的引用 (15)
1.10自定义格式语法： (16)
1.11动态表头： (17)
1.12行与列的合计有时出现不相等的情况。 (17)
1.13表格的几种类型及其处理技巧 (17)
1.14快速删除工作表中所有的文本框 (18)
2 函数数组 (20)
2.1函数宝典 (20)
2.2自定义函数157个 (20)
2.3 V L OOKUP (20)
2.4O FFSET (20)
2.5IS函数 (20)
2.6数组公式特征 (21)
2.7SQL语句在EXCEL中的典型用法 (22)
3 图表 (23)
3.1复合饼图 (23)
3.2图表组合 (26)
3.3甘特图 (30)
3.4带滚动条的图表 (34)
3.5动态图表的制作 (36)
4 数据分析 (39)
4.1数据有效性 (39)
4.1.1 防止重复录入 (39)
4.1.2 只能输入日期 (40)
4.1.3 输入的内容中必须包括某字符 (41)
4.1.4 制作二级选项菜单： (41)
4.2整合数据表 (41)
4.3自定义数字格式的实用案例 (46)
4.4批量取消合并单元格并使用C TRL+E NTER填充数据 (47)
4.5隐藏小于3000的数值 (48)
4.6将一列数据转化为多列数据 (48)
4.7将多列数据转化为一列 (48)
4.8合并计算汇总多表数据 (49)
4.9利用合并计算核对数值型数据 (50)
5 实例技巧 (51)
5.1各种各样的排名汇总 (51)
5.2资产折旧 (51)
5.3巧设单元格格式打印席卡 (53)
1 操作技巧
1.1关于换行 Alt+Enter
同一单元格内，有些长数据或条列式内容必须强行换行才能对齐。方法是光标移到在需要换行的位置上同时按下Alt+Enter键(使用强行换行时，系统会同时选择自动换行功能)。
1．输入数据随时换行
用户若要在输入数据时换行，只要通过Alt+Enter组合键即可轻松实现。此方法同样可使已输入内容的单元格在光标所在处换行。
2．单元格区域内换行
将某个长行转成段落并在指定区域内换行。例如：A10内容很长，欲将其显示在A列至C列之内，步骤是：选定区域A10:C12(先选A10)，选择“编辑→填充→内容重排”，A10内容就会分布在A10:C12区域中。此法特别适合用于表格内的注释。
3．调整单元格格式换行
选定单元格，选择“格式→单元格”，在弹出的对话框中单击“对齐”，选中“自动换行”复选框，单击[确定]按钮即可。
4.录制一个宏,定义快捷键
1.2快速在多上合计行中求和
选择区域---按F5---选择“空值”---按“Alt+="快捷键。
快速在多上合计行中求和.gif
1.3不复制隐藏的行或列
a 首先选中需要复制的被隐藏了一些行或列的表格区域；
b然后点击“编辑”-“定位”-“定位条件”，在其中选择“可见单元格”；
c 复制表格区域，粘贴即可。
1.4查找通配符
在Excel中，如何替换单元格中的*、?号呢？由于二者在“查找和替换”对话框中扮演通配符的角色，代表多个、单个任意字符，而要替换这两个符号本身，需要在前面增加一个~符号，如附图所示。同理要替换“~”符号时，也需要在其前面增加一个“~”符号。
1.5文本处理
1.5.1等长文本的分割
从字符串的左边取字符：=Left (字符串，文本长度)
从字符串的右边取字符：=Right (字符串，文本长度)
从字符串的中间取字符：=Mid (字符串，文本起始位置，文本长度)
1.5.2不等长文本的分割
第1步：选中要进行分割的字符串区域。
第2步：点击“数据”－“分列”，在文本分列向导中选择适合的分隔符。
第3步：设置需要导入的列以及放置该列的位置。
1.5.3 文本的合并
=Concatenate(文本1，文本2，…) 使用连接符&：
1.5.4 文本转数值的10种办法
文本转数值的10种方法.gif
,文本转数值的10种办法.xls
1.5.5 把引号中的字符串“.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。
1.5.6使用分列更改文本为数值
如果你发现在Excel中输入的公式计算结果不正确，也许是单元格中保存的数字被保存为文本类型的原因。这时需要将文本类型的数这转为数值，方法如下：
1.6高级筛选
由于自动筛选只能筛选出简单条件的数据，因此如要在复杂条件下进行筛选就需要采用高级筛选的方式。
在需要进行筛选的数据表外设定筛选条件。
在对话框中设定：列表区域为数据表区域；条件区域为刚才在数据表外部设定的条件区域，要包括字段名称和条件所在的单元格区域。
1.6.1如何将一列数据中在另外一列出现过的项目筛选出来
1.7条件格式
条件格式可以使得符合特定条件的记录按照某种设定的格式显示。
选中要进行条件格式设定的单元格范围，执行【格式】【条件格式】
在条件格式设置界面进行条件的设定：
1.8数据透视表
1.8.1应用数据透视表进行各类数据分析
选择“数据”－“数据透视表和数据透视图”，进入透视表设置向导。
点击“下一步”，选择正确的数据范围。
点击“下一步”，选择“布局”按钮。
在如下图界面上，将右侧的字段拖入左侧相应区域内。
形成如下图的布局，点击确定按钮。
生成如下的透视表后，将鼠标悬停在想要移动的字段上，鼠标左键按下，将字段拖放到其他区域，生成自己需要的数据显示。
数据百分比显示：右键菜单－字段设置－选项，将数据显示方式改为“占同列数据总和的百分比”
1.8.2同时显示数据与百分比：
1)在【布局】里将销售收入两次拖入数据区域
2)将其中一个销售收入改为百分比显示
3)在报表项目上输入新的名称可以修改项目名称
4)将报表项目拖拽到列标题位置，可以将数值和百分比改为按列排列
1.8.3筛选数据：
点击字段名称后面的下拉菜单，可以对字段内容进行筛选。
1.8.4显示明细数据：
双击需要查看明细的数据，在新的工作表上将显示构成此汇总数据的所有明细数据。
1.对数据进行排序：
把光标放在行位置的报表项目上，【右键菜单】【字段设置】，【高级】按钮，左侧可以进行排序设置。
2.按照日期分组：
1)将日期放入行区域，【右键菜单】【组及显示明细数据】【组合】
2)在【步长】中选择需要的分组标准
3.按照数值分组：
1)将销售收入放入行区域，【右键菜单】【组及显示明细数据】【组合】
2)在【步长】中选择需要的分组标准
4.插入计算字段：
在【名称】后给计算字段定义名称
在【公式】后输入计算字段的计算公式，可以引用下面的字段
1.8.5自动更新：
【右键菜单】【表格选项】【打开时刷新】可以让报表始终有更新后的数据显示。
1.8.6透视图
可以单独生成数据透视图，也可以基于现有的透视表生成透视图。如果已经有现成的透视表，通过点击数据透视表工具条上的图表按钮可以生成一个透视图。
通过拖动透视图上的各个字段到右侧或者底部的位置，可以方便地改变图表组织数据的方式。
隐藏透视图字段按钮：
把光标悬停在任意字段上，右键菜单，选择【隐藏数据透视图字段按钮】，即可以将字段按钮隐藏；
再次显示字段按钮：
点击透视图工具条上第一项，在出现的下拉菜单中选择【隐藏数据透视图字段按钮】
如果有些数据无法在数据透视表内部分析，可引用透视表的数据作为分析基础。
1.8.7多重合并计算数据
如下图格式的即为二维表：
现在我们需要将数个格式相同的二维表汇总为一张表格，且可以区分不同表格属性进行分析，比如三张表分别为北京，上海，深圳分公司的表格。我们使用的方法是利用透视表多重合并计算数据区域的功能。
选择数据-数据透视表和数据透视图
选择第3个数据源类型：多重合并计算数据区域，点击下一步按钮：
在出现的界面上选择：自定义字段
选择需要被合并的表格区域，点击添加将其添加到所有区域；将页字段数据改为1，在项标志处输入该表格的标志，使用相同的方法将其他需要合并的表格全部添加。
透视表生成后如下图所示，已经将多个表格合并成一个：
双击字段名，可以对字段名进行修改：
1.9名称的引用
名称
名称可以代表一个单元格或者一个单元格区域，或者是常量，公式。
名称的定义：
选中需要命名的单元格或区域，在界面左上角名称框中输入名称后回车；名称的删除：
插入－名称－定义，选中需要删除的名称点击删除按钮，点确定；
名称的引用：
需要引用某单元格时输入该单元格的名称：＝名称
第1步：选中需要命名的某个单元格或单元格区域。
第2步：在左上角名称框输入命名后回车。
删除已有命名：选择“插入”－“名称”－“定义”；选中需要删除的名称，点击“删除”按钮。
名称命名的优点：
1)避免绝对引用的错误
2)对公式进行文字化表述，让公式更加容易理解
3)可以在整个工作簿中通用，引用方便
1.10自定义格式语法：
大于条件值]格式；[小于条件值]格式；[等于条件值]格式；文本格式
1.11动态表头：
"ABC公司"&YEAR(NOW())&"年"&MONTH(NOW())&"月报表"
1.12行与列的合计有时出现不相等的情况。
选择“工具”－“选项”－“重新计算”－“以显示精度为准”，选中该选项。
1.13表格的几种类型及其处理技巧
数据表类型操作目标汇总方法是否需要重复
制作
是否与数据源
有链接关系