Excel技巧小集.docx

Excel技巧小集.docx

《Excel技巧小集.docx》由会员分享，可在线阅读，更多相关《Excel技巧小集.docx（7页珍藏版）》请在冰豆网上搜索。

Excel技巧小集

Excel2000技巧小集

自动保存:

许多人在工作时遇到没电或死机，那时就喊惨了，有可能白做多个钟头的工作。

那时要是用到“自动保存“的功能就好了，“Excel自动保存“功能不象word那样默认有的，必需要自己加上的。

操作：

“工具/加载宏”，选取“自动保存”然后在工具里就有了。

单击它可以进行设置。

自动启动

如果经常要编辑某个文件，你总是先运行Excel，再打开这个文件，不知你觉不觉得烦，如果一运行Excel，就打开这个文件就好了。

操作一：

将文件另存为在X:

\programfiles\MicrosoftOffice\Office\XLStart下。

（X：

为盘符）操作二：

为文件建一个快捷方式，放在桌面或任务条上，直接运行它。

Excel剪贴板

以往复制或剪切的数据都会被记录在WINDOWS剪贴板中，但是只能记录一项数据，而Excel剪贴板可容纳12笔复制或剪切的数据，这样用起来好方便。

操作：

执行“视图/工具/剪贴板”命令，以人工方式将剪贴板工具栏显示出来，做几个复制或剪贴操作，Excel会自动在剪贴板显示，如要粘贴，点一下剪贴板的图标就行。

快速填充

许多人在做填充时，喜欢使用鼠标的拖放功能。

这里有一个更快速的方法，只要双击填充控点就行了，Excel会自动根据前一列的行数去填充了。

插入新工作表

SHIFT+F11

编辑单元格批注

SHIFT+F2

显示“样式”对话框

ALT+'（撇号）

隐藏行

CTRL+9

取消隐藏行

CTRL+SHIFT+（左括号

隐藏列

CTRL+0（零）

取消隐藏列

CTRL+SHIFT+）右括号

在公式中键入函数名之后，显示公式选项板

CTRL+A

插入空白单元格

CTRL+SHIFT+加号

选定当前单元格周围的区域

CTRL+SHIFT+*（星号）

选定区域扩展到单元格同行同列的最后非空单元格

CTRL+SHIFT+箭头键

将选定区域扩展到行首

SHIFT+HOME

将选定区域扩展到工作表的开始

CTRL+SHIFT+HOME

将选定区域扩展到工作表的最后一个使用的单元格

CTRL+SHIFT+END

选定整列

CTRL+SPACEBAR

选定整行

SHIFT+SPACEBAR

在隐藏对象、显示对象与对象占位符之间切换

CTRL+6

显示或隐藏“常用”工具栏

CTRL+7

使用箭头键启动扩展选中区域的功能

F8

选定所有带批注的单元格

CTRL+SHIFT+O（字母O）

Excel中对交叉内容进行报表合并

在学校的成绩统计中，有的老师会遇到这样的问题：

比如说手头有两份成绩报表，一份是语文成绩，另一份是数学成绩，现在需要将这两份成绩报表合并起来，并计算出总分。

本来这在Excel中是非常容易实现的事情，但这还不算，最大的困难在于这两份成绩报表中的学生情况并不一致，即有一部分学生只有单科成绩，这样一来，两份成绩报表中的学生名单就出现了交叉（既有相同部分，又有不同部分）。

对于这种问题，我们应该怎样解决呢？

有的人可能会想到用VBA写一段代码来完成这样的工作，但毕竟这东东不是每个人都会的，单为了这么点小事情去专门学习VBA，又显得有些小题大做了。

况且这摆在眼前的工作，哪里来得及等你去学习VBA。

（等你学好了，估计也要下岗了！

）其实，会者不难，只要你能灵活运用Excel中的函数与公式，这个问题也是能被轻松解决的。

在介绍笔者的具体方案之前，请大家先耐心来进行一些有关此方案的Excel函数及公式知识的准备工作。

（先别急嘛，所谓磨刀不误砍柴功！

）

首先我们要来学习的是Excel中的COUNTIF函数。

COUNTIF（range,criteria）函数的功能是计算给定区域内满足特定条件的单元格的数目。

Range参数是需要计算其中满足条件的单元格数目的单元格区域。

而Criteria参数则用以确定哪些单元格将被计算在内的条件，其形式可以为数字、表达式或文本。

假设A1:

A5中的内容分别为“张三”、“李四”、“王五”、“张三”、“张三”，则COUNTIF（A1:

A5,"张三"）等于3。

接下来上场的是笔者最钟情的VLOOKUP函数，它在笔者的工作中无数次发挥了巨大的威力，其作用可能仅次于IF和SUM函数，所以笔者在此向大家吐血推荐！

（作广告？

）VLOOKUP（lookup_value,table_array,col_index_num,range_lookup）函数的功能是在表格或数值数组的首列查找指定的数值，并由此返回表格或数组当前行中指定列处的数值。

Lookup_value参数为需要在数据表第一列中查找的数值。

Table_array参数为需要在其中查找数据的数据表，可以使用对区域或区域名称的引用，例如数据库或数据清单。

Col_index_num参数为table_array中待返回的匹配值的列序号。

Col_index_num为1时，返回table_array第一列中的数值；col_index_num为2，返回table_array第二列中的数值，以此类推。

最后一个参数Range_lookup为一逻辑值，指明函数VLOOKUP返回时是精确匹配还是近似匹配。

当其值为0时，将返回精确值；当其值为1时，将返回近似匹配值。

关于VLOOKUP函数，在Excel的帮助文件中有非常详细的解释，本文限于篇幅的原因不能在此讲得更多，大家有兴趣的话可以自己进行学习。

另外在下面的方案中还将用到一些别的函数，如IF、ISNA等，就不单独介绍了，笔者将在介绍方案时一并向大家讲解。

最后我们要了解的是关于Excel公式中绝对引用和相对引用的概念。

相对引用是指公式中对单元格或单元格区域的引用仅仅是相对于包含公式的单元格的相对位置。

引用的源单元格或单元格区域会随着公式所在单元格的改变而改变。

例如A1单元格包含公式＝B1，当我们把A1的公式复制到A2后，则A2单元格包含公式＝B2，B1自动被调整为B2了。

这一点在大量复制公式时特别有用，因此也是Excel公式的默认引用方式。

绝对引用与相对引用恰恰相反，无论公式所在单元格怎么变动，引用的源单元格或单元格区域也不会发生任何改变。

绝对引用的标识符是美元符号“$”。

好了，下面就来正式介绍此问题的解决方案了：

假设已有工作簿文件“成绩单”，其工作表“语文”和“数学”分别是前文所说的两份成绩报表。

新建一工作表，将其命名为“索引”，在A1、B1单元格中分别输入“索引”和“姓名”。

选择“语文”工作表，将其中的学生姓名字段的所有内容全部复制到“索引”工作表中B列，接着再复制“数学”工作表的同样内容。

复制完成后稍作检查，要保证“索引”工作表中B列的数据区域中不要有空单元格，否则会影响后面的公式。

（当然，将公式设置得更为复杂的话，也可以忽略空单元格，但这个就请各位自己去摸索了）假设最后一个学生姓名在单元格B24，下面我们来为这些包含重复内容的学生姓名数据建立索引，以过滤掉重复内容。

先在A2单元格中输入数字“1”，然后再在A3单元格中输入公式“=IF（COUNTIF（B3:

$B$24,B3）>1,A2,1+A2）”。

这个公式的含义是如果在单元格区域B3:

B24中，单元格B3中的学生姓名的出现次数大于1的话，就返回A2——也就是公式所在单元格的上一个单元格的值，注意这里使用的是相对引用，此时即返回数值1；如果单元格B3中的学生姓名的出现次数不大于1的话，就返回另一个数值，该数值等于A2的值加上1。

在表示单元格区域B3:

B24时，笔者在标识该区域的结束单元格时用到了绝对引用，虽然实际上单元格B24后面已经没有任何数据了，但这样做一来是为了强调在编辑公式时需要养成良好的习惯，二来是为了与同一公式中的其它相对引用进行对比。

绝对引用保证了COUNTIF函数不会超出指定范围，而相对引用又保证COUNTIF函数进行判断的条件每次都不同，且进行统计的范围每次都在缩小，如此才能避免对判断过的单元格进行重复统计。

最后我们将单元格A3的公式向下进行拖曳到单元格A24，以进行公式的快速复制。

这样，索引过程就完成了。

结果如图一所示。

有朋友可能要问了“有没有搞错，索引值怎么会有这么多相同的？

”实际上，这个索引值是专门配合后面的VLOOKUP函数来使用的，光靠这个数字是没有用的。

不过我还是可以告诉你，相同的索引值中，第一次出现的为有效索引值。

再新建一工作表，将其命名为“汇总”。

在A1、B1、C1、D1、E1单元格中分别输入“索引”、“姓名”、“语文”、“数学”、“总分”。

在A2单元格中输入数字1，然后向下拖曳到单元格A25，得到从1到24的序列数。

在B2单元格中输入公式“=VLOOKUP（A2,索引!

$A$2:

$B$24,2,0）”，参照前文对VLOOKUP函数的介绍，我们可以知道此公式的功能在于根据A列相应单元格的数值，在“索引”工作表指定区域中找到对应的数值，返回给当前的单元格。

Lookup_value参数和Table_array参数分别运用了相对引用和绝对引用，都是为了复制公式的需要。

在此，我们还利用了VLOOKUP函数的另一个特性，即如果Table_array中有多个值与Lookup_value对应，则函数仅响应第一个出现的值。

现在明白为什么“索引”工作表中有多个重复值也没关系了吧！

然后在C2和D2中分别输入公式“=IF（ISNA（VLOOKUP（B2,语文!

$A$2:

$B$13,2,0））,0,VLOOKUP（B2,语文!

$A$2:

$B$13,2,0））”、“=IF（ISNA（VLOOKUP（B2,数学!

$A$2:

$B$13,2,0））,0,VLOOKUP（B2,数学!

$A$2:

$B$13,2,0））”，用于根据B列中的学生姓名从“语文”和“数学”工作表中取得他们相应的成绩。

ISNA（）是用来判断VLOOKUP（）的值是否有效的函数，即如果VLOOKUP（）找不到相应的成绩时，ISNA（）返回TRUE。

再结合IF（），此公式的完整含义为如果相应的学生有对应的成绩，则返回他的成绩，否则为0。

因为有许多学生只有单科成绩，所以这样的判断还是必要的，否则当VLOOKUP（）找不到对应的成绩时，会返回值“#N/A”，不但难看，而且影响后面总分的计算。

在E1单元格中输入公式“=C2+D2”就能得到总分，这个公式应该没什么好说的吧！

最后，同时选中C1、D1、E1单元格并往下进行拖曳以复制公式。

怎么样，全都算好了吧！

学生一个不多也一个不少，刚刚好，而且他们的成绩也一分不差，是不是很容易！

见图二所示。

好了，现在再回过头去理解一下方案中所有的公式，应该都是非常简单了的吧！

按照本文的思路，如果出现类似的问题，相信也难不倒你啦！

单击此处下下载示范工作簿个公式的含义是如果在单元格区域B3:

B24中，单元格B3中的学生姓名的出现次数大于1的话，就返回A2——也就是公式所在单元格的上一个单元格的值，注意这里使用的是相对引用，此时即返回数值1；如果单元格B3中的学生姓名的出现次数不大于1的话，就返回另一个数值，该数值等于A2的值加上1。

在表示单元格区域B3:

B24时，笔者在标识该区域的结束单元格时用到了绝对引用，虽然实际上单元格B24后面已经没有任何数据了，但这样做一来是为了强调在编辑公式时需要养成良好的习惯，二来是为了与同一公式中的其它相对引用进行对比。

绝对引用保证了COUNTIF函数不会超出指定范围，而相对引用又保证COUNTIF函数进行判断的条件每次都不同，且进行统计的范围每次都在缩小，如此才能避免对判断过的单元格进行重复统计。

最后我们将单元格A3的公式向下进行拖曳到单元格A24，以进行公式的快速复制。

这样，索引过程就完成了。

结果如图一所示。

有朋友可能要问了“有没有搞错，索引值怎么会有这么多相同的？

”实际上，这个索引值是专门配合后面的VLOOKUP函数来使用的，光靠这个数字是没有用的。

不过我还是可以告诉你，相同的索引值中，第一次出现的为有效索引值。

再新建一工作表，将其命名为“汇总”。

在A1、B1、C1、D1、E1单元格中分别输入“索引”、“姓名”、“语文”、“数学”、“总分”。

在A2单元格中输入数字1，然后向下拖曳到单元格A25，得到从1到24的序列数。

在B2单元格中输入公式“=VLOOKUP（A2,索引!

$A$2:

$B$24,2,0）”，参照前文对VLOOKUP函数的介绍，我们可以知道此公式的功能在于根据A列相应单元格的数值，在“索引”工作表指定区域中找到对应的数值，返回给当前的单元格。

Lookup_value参数和Table_array参数分别运用了相对引用和绝对引用，都是为了复制公式的需要。

在此，我们还利用了VLOOKUP函数的另一个特性，即如果Table_array中有多个值与Lookup_value对应，则函数仅响应第一个出现的值。

现在明白为什么“索引”工作表中有多个重复值也没关系了吧！

然后在C2和D2中分别输入公式“=IF（ISNA（VLOOKUP（B2,语文!

$A$2:

$B$13,2,0））,0,VLOOKUP（B2,语文!

$A$2:

$B$13,2,0））”、“=IF（ISNA（VLOOKUP（B2,数学!

$A$2:

$B$13,2,0））,0,VLOOKUP（B2,数学!

$A$2:

$B$13,2,0））”，用于根据B列中的学生姓名从“语文”和“数学”工作表中取得他们相应的成绩。

ISNA（）是用来判断VLOOKUP（）的值是否有效的函数，即如果VLOOKUP（）找不到相应的成绩时，ISNA（）返回TRUE。

再结合IF（），此公式的完整含义为如果相应的学生有对应的成绩，则返回他的成绩，否则为0。

因为有许多学生只有单科成绩，所以这样的判断还是必要的，否则当VLOOKUP（）找不到对应的成绩时，会返回值“#N/A”，不但难看，而且影响后面总分的计算。

在E1单元格中输入公式“=C2+D2”就能得到总分，这个公式应该没什么好说的吧！

最后，同时选中C1、D1、E1单元格并往下进行拖曳以复制公式。

怎么样，全都算好了吧！

学生一个不多也一个不少，刚刚好，而且他们的成绩也一分不差，是不是很容易！

见图二所示。

好了，现在再回过头去理解一下方案中所有的公式，应该都是非常简单

Excel97大提速

1、快速启动Excel97。

若您日常工作中要经常使用Excel97，可以在启动Win95时启动它，设置方法：

（1）启动“我的电脑”进入Windows目录，依照路径“StartMenu\Programs\启动”来打开“启动”文件夹：

（2）打开Excel97所在的文件夹，用鼠标将Excel图标拖到“启动”文件夹，这时Excel97的快捷方式就被复制到“启动”文件夹中，下次启动Win95就可快速启动Excel97了。

2、快速获取帮助。

对于工具栏或屏幕区，您只需按组合键Shift＋F1，然后用鼠标单击工具栏按钮或屏幕区，它就会弹出一个帮助窗口，上面会告诉该元素的详细帮助信息。

7、“窗口”菜单则包含一个名为“多窗口”的命令，选用该命令，则出现一个按字母顺序列出所有已打开的工作簿名字的对话框，只需单击其中需要的名字即可。

8、快速插入Word表格。

Excel可以处理Word表格中列出的数据，您可用以下方法快速插入Word表格：

（1）打开Word表格所在的文件；

（2）打开要处理Word表格的Excel文件，并调整好两窗口的位置，以便能看见表格和要插入表格的区域；（3）选中Word中的表格；（4）按住鼠标左键，将表格拖到Excel窗口中，松开鼠标左键将表格放在需要的位置即可。

   9、快速链接网上的数据。

您可以用以下方法快速建立与网上工作簿中数据的链接：

（1）打开Internet上含有需要链接数据的工作簿，并在工作簿选定数据，然后单击“编辑”菜单的“复制”命令；

（2）打开需要创建链接的Excel97工作簿，在需要显示链接数据的区域中，单击左上角单元格；（3）单击“编辑”菜单中的“选择性粘贴”命令，在“选择性粘贴”对话框中，选择“粘贴链接”按钮即可。

若您想在创建链接时不打开Internet工作簿，可单击需要链接处的单元格，然后键入（=）和URL地址及工作簿位置，如：

=http:

//www.J

   10、快速创建工具栏。

通过工具栏您可以快捷地访问常用的命令或自定义的宏，您可以根据需要快速创建自己的工具栏。

方法为：

单击“工具”菜单中的“自定义”命令，选择“工具栏”选项卡，单击“新建”按钮，输入“新建工具栏”名称，然后单击“确定”。

这时新建工具栏出现在窗口，您就可以用鼠标把其他工具栏中的按钮拖到新建工具栏中，该按钮就会在此“落户”。

若在拖动时按着Ctrl键，则会将按钮复制过来。

注意：

不能将按钮拖到“自定义”对话框或工作表中，否则该按钮将会被删除。

   11、利用模板创建工作簿。

模板是一用来作为创建其它工作簿的框架形式，利用它可以快速地创建相似的工作簿。

创建模板方法为：

（1）打开一个要作为模板的工作簿；

（2）选择“文件”菜单中“另存为”命令，打开“另存为”对话框；（3）在“文件名”框中输入模板的名字，从“保存类型”列表中选定“模板（＊.xlt）”选项，这时“保存位置”会自动切换到默认的模板文件夹Templates文件夹；（4）在“保存位置”中选择“电子表格模板”文件夹，单击“保存”即可。

这样，您就可以根据该模板快速创建新工作簿了。

   12、用“超级连接”快速跳转到其它文件。

用超级链接在各个位置之间跳转十分方便，若您要切换到其它文件，只需用鼠标指向带有下划线的蓝色超级链接文件，然后单击鼠标即可跳转到超级链接所指向的子位置上去，看完后若要返回，只需单击“Web”工具栏上的“返回”按钮即可。

定单元格，然后移动鼠标指针到单元格边框上，按下鼠标左键并拖动到新位置，然后释放按键即可移动。

若要复制单元格，则在释放鼠标之前按下Ctrl即可。

   4、快速查找工作簿。

您可以利用在工作表中的任何文字进行搜寻，方法为：

（1）单击工具栏中的“打开”按钮，在“打开”对话框里，输入文件的全名或部分名，可以用通配符代替；

（2）在“文本属性”编辑框中，输入想要搜寻的文字，最好是您认为是唯一的单词或短语，以便搜寻更容易成功；（3）选择“开始查找”即可。

在找到满足条件的文件前，“打开”对话框的状态栏都会显示“找到了0个文件”的信息，您应该耐心等待，只有当“打开”按钮由灰化状态变成可用状态时，才表明搜寻结束。

   5、快速打印工作表。

若选择“文件”菜单中“打印”命令来打印，会出现“打印”对话框让您选择，程序繁琐。

若要跳过该对话框，您可以单击“常用”工具栏上的“打印”按钮或者按下Shift键并单击“打印预览”按钮，Excel将使用“选定工作表”选项打印。

   6、快速切换工作表。

按Ctrl＋PageUp组合键可激活前一个工作表，按Ctrl＋PageDown组合键可激活后一个工作表。

您还可用鼠标去控制工作表底部的标签滚动按钮快速地移动工作表的名字，然后单击工作表进行切换。

   7、快速切换工作簿。

对于较少工作簿切换，可单击工作簿所在窗口。

要对多个窗口下的多个工作进行切换，用“窗口”菜单最方便。

“窗口”菜单的底部列出了已打开了工作簿的名字，要直接切换到一个工作簿，从“窗口”菜单选择它的名字即可。

“窗口”菜单最多能列出9个工作簿，若多于9个，“窗口”菜单则包含一个名为“多窗口”的命令，选用该命令，则出现一个按字母顺序列出所有已打开的工作簿名字的对话框，只需单击其中需要的名字即可。

   8、快速插入Word表格。

Excel可以处理Word表格中列出的数据，您可用以下方法快速插入Word表格：

（1）打开Word表格所在的文件；

（2）打开要处理Word表格的Excel文件，并调整好两窗口的位置，以便能看见表格和要插入表格的区域；（3）选中Word中的表格；（4）按住鼠标左键，将表格拖到Excel窗口中，松开鼠标左键将表格放在需要的位置即可。

   9、快速链接网上的数据。

您可以用以下方法快速建立与网上工作簿中数据的链接：

（1）打开Internet上含有需要链接数据的工作簿，并在工作簿选定数据，然后单击“编辑”菜单的“复制”命令；

（2）打开需要创建链接的Excel97工作簿，在需要显示链接数据的区域中，单击左上角单元格；（3）单击“编辑”菜单中的“选择性粘贴”命令，在“选择性粘贴”对话框中，选择“粘贴链接”按钮即可。

若您想在创建链接时不打开Internet工作簿，可单击需要链接处的单元格，然后键