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

《Excel操作技巧与实例(大全).doc》由会员分享，可在线阅读，更多相关《Excel操作技巧与实例(大全).doc（56页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、Excel 操作技巧Skills About Excel版本：A Rev.20120820（2012-8-20初版）Email：jungwongcn目录1 操作技巧11.1 关于换行 Alt+Enter11.2 快速在多上合计行中求和11.3 不复制隐藏的行或列11.4 查找通配符21.5 文本处理21.5.1 等长文本的分割21.5.2 不等长文本的分割21.5.3 文本的合并31.5.4 文本转数值的10种办法31.5.5 把引号中的字符串“.Trends in Genetics 23 (4): 1929“中的前半部分英文提取出来31.5.6 使用分列更改文本为数值41.6 高级筛选61.

2、6.1 如何将一列数据中在另外一列出现过的项目筛选出来71.7 条件格式71.8 数据透视表81.8.1 应用数据透视表进行各类数据分析81.8.2 同时显示数据与百分比：101.8.3 筛选数据：101.8.4 显示明细数据：111.8.5 自动更新：121.8.6 透视图121.8.7 多重合并计算数据131.9 名称的引用151.10 自定义格式语法：161.11 动态表头：171.12 行与列的合计有时出现不相等的情况。171.13 表格的几种类型及其处理技巧171.14 快速删除工作表中所有的文本框182 函数数组202.1 函数宝典202.2 自定义函数157个202.3 vLoo

3、kup202.4 Offset202.5 IS函数202.6 数组公式特征212.7 SQL语句在EXCEL中的典型用法223 图表233.1 复合饼图233.2 图表组合263.3 甘特图303.4 带滚动条的图表343.5 动态图表的制作364 数据分析394.1 数据有效性394.1.1 防止重复录入394.1.2 只能输入日期404.1.3 输入的内容中必须包括某字符414.1.4 制作二级选项菜单：414.2 整合数据表414.3 自定义数字格式的实用案例464.4 批量取消合并单元格并使用Ctrl+Enter填充数据474.5 隐藏小于3000的数值484.6 将一列数据转化为多列

4、数据484.7 将多列数据转化为一列484.8 合并计算汇总多表数据494.9 利用合并计算核对数值型数据505 实例技巧515.1 各种各样的排名汇总515.2 资产折旧515.3 巧设单元格格式打印席卡531 操作技巧1.1 关于换行 Alt+Enter同一单元格内，有些长数据或条列式内容必须强行换行才能对齐。方法是光标移到在需要换行的位置上同时按下Alt+Enter键(使用强行换行时，系统会同时选择自动换行功能)。1输入数据随时换行用户若要在输入数据时换行，只要通过Alt+Enter组合键即可轻松实现。此方法同样可使已输入内容的单元格在光标所在处换行。 2单元格区域内换行将某个长行转成段

5、落并在指定区域内换行。例如：A10内容很长，欲将其显示在A列至C列之内，步骤是：选定区域A10:C12(先选A10)，选择“编辑填充内容重排”，A10内容就会分布在A10:C12区域中。此法特别适合用于表格内的注释。3调整单元格格式换行 选定单元格，选择“格式单元格”，在弹出的对话框中单击“对齐”，选中“自动换行”复选框，单击确定按钮即可。4.录制一个宏,定义快捷键1.2 快速在多上合计行中求和选择区域-按F5-选择“空值”-按“Alt+=快捷键。1.3 不复制隐藏的行或列a 首先选中需要复制的被隐藏了一些行或列的表格区域；b然后点击“编辑”-“定位”-“定位条件”，在其中选择“可见单元格”；

6、c 复制表格区域，粘贴即可。1.4 查找通配符在Excel中，如何替换单元格中的*、?号呢？由于二者在“查找和替换”对话框中扮演通配符的角色，代表多个、单个任意字符，而要替换这两个符号本身，需要在前面增加一个符号，如附图所示。同理要替换“”符号时，也需要在其前面增加一个“”符号。1.5 文本处理1.5.1 等长文本的分割从字符串的左边取字符：=Left (字符串，文本长度)从字符串的右边取字符：=Right (字符串，文本长度)从字符串的中间取字符：=Mid (字符串，文本起始位置，文本长度)1.5.2 不等长文本的分割第1步：选中要进行分割的字符串区域。第2步：点击“数据”“分列”，在文本分

7、列向导中选择适合的分隔符。第3步：设置需要导入的列以及放置该列的位置。1.5.3 文本的合并=Concatenate(文本1，文本2，)使用连接符&：1.5.4 文本转数值的10种办法,1.5.5 把引号中的字符串“.Trends in Genetics 23 (4): 1929“中的前半部分英文提取出来使用数组公式=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，

8、以避免find时出错。这是简化公式的常用手法，与此类似作用的还有519。1.5.6 使用分列更改文本为数值如果你发现在Excel中输入的公式计算结果不正确，也许是单元格中保存的数字被保存为文本类型的原因。这时需要将文本类型的数这转为数值，方法如下：1.6 高级筛选由于自动筛选只能筛选出简单条件的数据，因此如要在复杂条件下进行筛选就需要采用高级筛选的方式。在需要进行筛选的数据表外设定筛选条件。在对话框中设定：列表区域为数据表区域；条件区域为刚才在数据表外部设定的条件区域，要包括字段名称和条件所在的单元格区域。1.6.1 如何将一列数据中在另外一列出现过的项目筛选出来1.7 条件格式条件格式可以使

9、得符合特定条件的记录按照某种设定的格式显示。选中要进行条件格式设定的单元格范围，执行【格式】【条件格式】在条件格式设置界面进行条件的设定：1.8 数据透视表1.8.1 应用数据透视表进行各类数据分析选择“数据”“数据透视表和数据透视图”，进入透视表设置向导。点击“下一步”，选择正确的数据范围。点击“下一步”，选择“布局”按钮。在如下图界面上，将右侧的字段拖入左侧相应区域内。形成如下图的布局，点击确定按钮。生成如下的透视表后，将鼠标悬停在想要移动的字段上，鼠标左键按下，将字段拖放到其他区域，生成自己需要的数据显示。数据百分比显示：右键菜单字段设置选项，将数据显示方式改为“占同列数据总和的百分比”

10、1.8.2 同时显示数据与百分比：1) 在【布局】里将销售收入两次拖入数据区域2) 将其中一个销售收入改为百分比显示3) 在报表项目上输入新的名称可以修改项目名称4) 将报表项目拖拽到列标题位置，可以将数值和百分比改为按列排列1.8.3 筛选数据：点击字段名称后面的下拉菜单，可以对字段内容进行筛选。1.8.4 显示明细数据：双击需要查看明细的数据，在新的工作表上将显示构成此汇总数据的所有明细数据。1. 对数据进行排序：把光标放在行位置的报表项目上，【右键菜单】【字段设置】，【高级】按钮，左侧可以进行排序设置。2. 按照日期分组：1) 将日期放入行区域，【右键菜单】【组及显示明细数据】【组合】2

11、) 在【步长】中选择需要的分组标准3. 按照数值分组：1) 将销售收入放入行区域，【右键菜单】【组及显示明细数据】【组合】2) 在【步长】中选择需要的分组标准4. 插入计算字段：在【名称】后给计算字段定义名称在【公式】后输入计算字段的计算公式，可以引用下面的字段1.8.5 自动更新：【右键菜单】【表格选项】【打开时刷新】可以让报表始终有更新后的数据显示。1.8.6 透视图可以单独生成数据透视图，也可以基于现有的透视表生成透视图。如果已经有现成的透视表，通过点击数据透视表工具条上的图表按钮可以生成一个透视图。通过拖动透视图上的各个字段到右侧或者底部的位置，可以方便地改变图表组织数据的方式。隐藏透

12、视图字段按钮：把光标悬停在任意字段上，右键菜单，选择【隐藏数据透视图字段按钮】，即可以将字段按钮隐藏；再次显示字段按钮：点击透视图工具条上第一项，在出现的下拉菜单中选择【隐藏数据透视图字段按钮】如果有些数据无法在数据透视表内部分析，可引用透视表的数据作为分析基础。1.8.7 多重合并计算数据如下图格式的即为二维表：现在我们需要将数个格式相同的二维表汇总为一张表格，且可以区分不同表格属性进行分析，比如三张表分别为北京，上海，深圳分公司的表格。我们使用的方法是利用透视表多重合并计算数据区域的功能。选择数据-数据透视表和数据透视图选择第3个数据源类型：多重合并计算数据区域，点击下一步按钮：在出现的界

13、面上选择：自定义字段选择需要被合并的表格区域，点击添加将其添加到所有区域；将页字段数据改为1，在项标志处输入该表格的标志，使用相同的方法将其他需要合并的表格全部添加。透视表生成后如下图所示，已经将多个表格合并成一个：双击字段名，可以对字段名进行修改：1.9 名称的引用名称名称可以代表一个单元格或者一个单元格区域，或者是常量，公式。名称的定义：选中需要命名的单元格或区域，在界面左上角名称框中输入名称后回车；名称的删除：插入名称定义，选中需要删除的名称点击删除按钮，点确定；名称的引用：需要引用某单元格时输入该单元格的名称：名称第1步：选中需要命名的某个单元格或单元格区域。第2步：在左上角名称框输入

14、命名后回车。删除已有命名：选择“插入”“名称”“定义”；选中需要删除的名称，点击“删除”按钮。名称命名的优点：1) 避免绝对引用的错误2) 对公式进行文字化表述，让公式更加容易理解3) 可以在整个工作簿中通用，引用方便1.10 自定义格式语法：大于条件值格式；小于条件值格式；等于条件值格式；文本格式1.11 动态表头：ABC公司&YEAR(NOW()&年&MONTH(NOW()&月报表1.12 行与列的合计有时出现不相等的情况。选择“工具”“选项”“重新计算”“以显示精度为准”，选中该选项。1.13 表格的几种类型及其处理技巧数据表类型操作目标汇总方法是否需要重复制作是否与数据源有链接关系多字

15、段数据列表记录叠加并按表名区分导入外部数据 + SQL语句不需要有二维表多个二维表生成透视表透视表多维数据区域合并不需要有同一文件内的报表数据汇总=sum(begin:end!B2)不需要有不同文件中的报表数据汇总合并计算不需要有非标准数据表汇总建议修改原表的结构或者仅作为终端报表，上层再建立一个数据源表作为收集数据使用需要无1.14 快速删除工作表中所有的文本框有时公莫名其妙地在工作表中添加许多文本框，想删除所有文本框可以用附图中的方法。其他插入的对象，如图片、自选图形等都可以用同样的方式删除哦。2 函数数组2.1 函数宝典2.2 自定义函数157个2.3 vLookup2.4 Offset

16、2.5 IS函数可以检验数值的类型并根据参数取值返回 TRUE 或 FALSE。函数如果为下面的内容，则返回 TRUEISBLANK值为空白单元格。ISERR值为任意错误值（除去 #N/A）。ISERROR值为任意错误值（#N/A、#VALUE!、#REF!、#DIV/0!、#NUM!、#NAME? 或 #NULL!）。ISLOGICAL值为逻辑值。ISNA值为错误值 #N/A（值不存在）。ISNONTEXT值为不是文本的任意项（注意此函数在值为空白单元格时返回T）。ISNUMBER值为数字。ISREF值为引用。ISTEXT值为文本。2.6 数组公式特征对单元格区域进行多重计算的计算方式与普通

17、计算公式的区别是录入公式结束后，需要同时按下 ctrl + shift + enter ，其特征是在公式两端会出现一对大括号。2.7 SQL语句在EXCEL中的典型用法3 图表3.1 复合饼图当数据系列内值的大小差异较大时，可以制作复合饼图以便数据显示更为清楚，如下图所示：选择饼图中的复合饼图子图表类型：点击下一步，切换到图例标签，将显示图例的勾去掉：切换到数据标志标签，把类别名称和百分比打勾：选中数据系列，右键菜单，选择数据系列格式：切换到选项标签，将第二绘图区包含最后的值改为：4复合饼图制作完成。3.2 图表组合一个图表中的不同数据系列可以采用不同的图表类型显示，如下图：首先制作一个柱形图

18、：在图表工具条上选择数据系列 Compensation:在图表工具条上选择数据系列格式按钮：在数据系列格式界面上，切换到坐标轴标签，将主坐标轴改为次坐标轴：右键点击Compensation,选择图表类型：将其图表类型改为折线图；用同样的方式操作Production系列，将其图表类型改为面积图；完成图表组合的制作。下拉菜单式图表：写入如下的公式：index函数查找出的值由A9单元格来指定。制作三维饼图，其数据区域为A3:I3,A9:I9,需要手工选取。点击“视图”-“工具栏”-“窗体”，调出窗体工具条：在窗体工具条上选择组合框，在饼图上拖放出一个组合框：右键选中组合框，在菜单中选择“设置控件格式

19、”：在控制标签中做如下设置：其中单元格链接为存储控件选中项目序号的单元格。完成下拉菜单式图表的制作：3.3 甘特图如果想制作如下图的甘特图，需要以下4列数据：首先制作堆积条形图：点击下一步，切换到系列标签，删除结束日期系列：生成的条形图如下图所示：下面转换纵坐标的项目排列次序：双击纵坐标，切换到刻度，将分类次序反转和数据轴交叉于最大分类选项打勾。此时纵坐标次序已经反转双击开始日期数据系列，在数据系列格式对话框中将边框和内部都改选为无，此时开始日期数据系列隐藏。此时需要将横坐标开始日期改为真正的项目开始日期：在excel中，每一个日期都对应一个数值，选中B2,查看2008-7-1对应数值为396

20、30双击横坐标，切换到刻度，将最小值改为39630，同样将横坐标最大值改为真正的项目结束日期。甘特图制作完成。3.4 带滚动条的图表该图表中可以随着点击滚动条的动作而动态翻看源数据中的大量数据。定义两个动态引用的名称：Period: = offset(7.动态图表!$A$1,7.动态图表!$D$1,0,10,1)Data: = offset(7.动态图表!$B$1,7.动态图表!$D$1,0,10,1)制作折线图，选择系列标签，删除period系列：将“值”和“分类（X）轴标志”设置为如下图内容：调出窗体工具条，在图表外绘制滚动条，右键选中滚动条，选择设置控件格式：在单元格链接中设置为D1:3

21、.5 动态图表的制作选中北京，深圳，上海以下的区域，分别定义北京，深圳，上海的名称选择视图-工具栏-窗体，选择选项按钮，画出如下三个选项按钮：右键选中选项按钮，在右键菜单中选择设置控件格式，在跳出的对话框中选择控制标签，在单元格链接中选择A16：在A17单元格中输入公式：=choose(a16,”北京”,”上海”,”深圳”)，为A17定义名称：choose在A2,A3,A4单元格中输入以下内容：在B16单元中写入以下公式，并复制到该行1-12月的单元格中：=VLOOKUP(A3,INDIRECT(choose),COLUMN()-1,0)依据此数据表制作双曲线图：4 数据分析4.1 数据有效性

22、4.1.1 防止重复录入4.1.2 只能输入日期4.1.3 输入的内容中必须包括某字符=not(iserror(find(中国,g30)4.1.4 制作二级选项菜单：首先将一级选项的每个项目定义一个名称，该名称内容包括相应的二级项目；制作一级项目的有效性；制作二级项目的有效性：内容为：=INDIRECT(g6)，其中g6为设定了有效性的一级选项所在的单元格。4.2 整合数据表我们经常需要将几个表格中的数据整合成一张表，如下图所示，将5个月的数据（目前分布在5张表格上）整合到一个总表上：我们使用的方法是利用office的查询工具query进行的，这样做的优点在于今后对于新数据的更新可以自动化刷新

23、，而不需要重复的进行整合操作（如复制粘贴）。office的查询工具query在典型安装office时是不会被安装的，我们需要首先检查我们的office是否已经安装了query，方法如下：l 打开Excel，点击【数据】菜单中【导入外部数据】【新建数据库查询】，若系统提示安装，则说明该功能未被安装，此时可能需要在光驱中插入Office安装光盘完成该功能的安装。l 在光驱中插入安装光盘后，安装过程中选择自定义安装；点开Excel前面的加号，选中要安装的功能前面的下拉菜单，选择【从本机运行】；点开【Office工具】前面的加号，选中【Microsoft Query】的下拉菜单，选择【从本机运行】，然

24、后执行余下的安装过程即可。下面开始整合工作：选择excel files*:选择要导入数据的excel文件：出现选择数据表的界面：如果以上界面提示错误，点击以上界面的“选项”按钮，出现以下界面，将系统表打勾即可：任选一个字段到右侧，点下一步按钮：直到以下界面出现，选择第二项，点完成按钮：此时出现query程序界面，点击工具条上SQL按钮：出现以下界面：在界面中将SQL语句改为以下：出现以下提示，点确定按钮：出现数据查询结果，选文件菜单-将数据返回EXCEL:选择在新工作表中存储数据：几张数据表被成功地整合在一起。该查询数据表与数据源保持着动态链接关系，可以随时刷新数据，不需要重复操作。右键菜单里

25、选择刷新数据即可对数据进行刷新。4.3 自定义数字格式的实用案例4.4 批量取消合并单元格并使用Ctrl+Enter填充数据Ctrl+Enter的作用：用当前输入项填充选定的单元格区域。4.5 隐藏小于3000的数值4.6 将一列数据转化为多列数据4.7 将多列数据转化为一列4.8 合并计算汇总多表数据4.9 利用合并计算核对数值型数据5 实例技巧5.1 各种各样的排名汇总5.2 资产折旧直线折旧法计算资产折旧=SLN（Cost,Satvage,Life）固定余额递减折旧法计算资产折旧=DB（Cost,Satvage,Life,Year）双倍余额递减折旧法计算资产折旧=DDB（Cost,Satvage,Life,Year,2）可变余额递减折旧法计算资产折旧=VDB（Cost,Satvage,Life,StartPeriod,EndPeriod,Factor,NoSwitch）年数总和折旧法计算资产折旧=SYD（Cost,Satvage,Life,Year）5.3 巧设单元格格式打印席卡开会时常会为参会人员设置座席卡，这需要打印一正一反的两个姓名。这是一问题可通过设置单元格格式轻松实现，做法请点“查看大图”观看动画。