一些实用的Excel使用技巧（课程结束，自由讨论）

倒挂的猴子

QUOTE Created By 兴高彩烈 At 2005-7-4
上次公司SEND偶去学回来的，都是小技巧，但是用得好的话，可以大大提高EXCEL的使用效率哦， [M05]

虽然只花了几百块钱去学，但是浪费了我一个周末， [M07]

[M25] [M25] [M21]

CC同学,有没有完整的文档呀? [M29]

兴高彩烈

5、相对引用/绝对引用

有时候我们会在一个单元格中输入一个公式，然后利用拖动的方式复制与该公式类似的公式。比如说，有ABC三列，我们希望在D列中得到这三列的和，于是我们A行的D列中输入公式=A+B+C，A行D列有了符合要求的结果。

可是BCDE…列中也有一样的数据，我们当然不希望在这些列中需要重复输入这样的公式，这时候只要选中A行D列，点中其右下角再拖动就可以复制这些公式了。
当然我们的实用技巧没有这么简单，我知道这个大家都晓得了，不要小看我哦。
那么，假如你希望D列中得到的结果是A行A列的内容加上其余各行的B、C列，怎么办呢？

这个时候就回到我们的题目：相对引用和绝对引用了。

在EXCEL中，对于一个单元格的引用分为相对引用和绝对引用。简单来说，当你实用相对应用的时候，EXCEL记住的是该单元格和周围其它单元格之间"相对"的位置。也就是说，假如你原先引用了某一列的内容，而后来该列之前插入了一个新列导致它的列号改变了，如果它是相对引用，系统会自动的调整列号，使你的引用仍然是正确的。在上述复制公式的过程中，EXCEL也使用了相同的逻辑，它认为你需要的是相对于本行的同一行的数据，因此复制下来的公式会自动做调整。

但是当你使用绝对引用的时候，EXCEL记住的就是固定该坐标的值，那么假如有新插入的列使该单元格的行号列号改变了，所引用的值可能就会变成其它单元格的内容——这样讲清楚吗？

灵活利用相对引用和绝对引用，可以在狠多情况下快速得到需要的结果。狠方便哦。

再说一下，通常EXCEL默认你的引用是相对引用，如果要改成绝对引用，可以在行号或者列号之前直接加$，也可以选中带引用的单元格再F4（噎，原来F4还有这种用途！），点一次则行和列都为绝对引用，点两次，只有列是绝对引用的，行仍然相对引用……
注意行和列是可以分开引用的。也就是说，我们可以绝对引用行，相对引用列……

说起来有点复杂，用一次大家就了改啦，[M14]

兴高彩烈

QUOTE Created By 倒挂的猴子 At 2005-7-4
[M25] [M25] [M21]

CC同学,有没有完整的文档呀? [M29]

有一个狠简单的WORD文档，是我听课的笔记，给你倒是无所谓，但是你要一看就懂，那算你牛。

因为真的狠简单， [M24]

害我现在还要一个个解释，都不知道最后要花多少时间呢， [M10]

倒挂的猴子

QUOTE Created By 兴高彩烈 At 2005-7-4
[quote]QUOTE Created By 倒挂的猴子 At 2005-7-4
[M25] [M25] [M21]

CC同学,有没有完整的文档呀? [M29]

有一个狠简单的WORD文档，是我听课的笔记，给你倒是无所谓，但是你要一看就懂，那算你牛。

因为真的狠简单， [M24]

害我现在还要一个个解释，都不知道最后要花多少时间呢， [M10][/quote]

我不牛,猴子都不是牛! [M13]

还是乖乖地看一个个解释好了! [M44]

兴高彩烈

6、数据筛选

这个有点简单，不怎么好意思写出来。

当我们在EXCEL中有狠多以列表形式显示的数据的时候，可能需要快速浏览符合某些条件的行。最简单的方式就是使用自动筛选。
使用的方式是，选中列表头所在的行，或者行中的某个单元格，从菜单中选择"数据>筛选>自动筛选"，则所选的行所有的单元格或者指定的单元格中会出现一个下拉列表的标志，点击该标志你就知道怎么回事了。

这也是一个整理数据的非常好的方式。有时候我们想知道符合某种条件的记录有多少条又不想使用公式来计算，用自动筛选的时候，EXCEL左下方的状态栏会显示有多少条符合条件的记录被选中，这样我们就可以快速的得到统计值了。

还有高级筛选功能，不过因为自动筛选已经狠强大了，偶从来没有用过高级筛选，不好意思哈， [M20]

兴高彩烈

7、数据有效性

某些时候我们需要定制EXCEL使某个单元格只能接受指定的输入。比如说有一个单元格只能输入1-100之间的数字来表示百分比，有一个单元只能接受一个列表中的内容，怎么办呢？

答案就是"数据有效性"啦。

选择进行有效性控制的单元格（也可以用SHIFT或CTRL键一次选中多个单元格同时对它们进行有效性控制），然后再从菜单中选择"数据>有效性…"(Data > Validation…)，可以看到有效性设置对话框。

缺省的选择是"Any value"，还可以有狠多别的选择，你一看就明白啦。

最酷的是"列表"（"List"），选择List后，你可以在"来源"("Source")处指定该单元格能输入的值的列表，注意选项之间必须以英文的逗号来分隔。

但是也有可能我们需要的"列表"常常会变动，经常进入这个表格来改动选择列表狠麻烦，怎么办？

当然有办法啦！

你可以在任意的一个sheet（注意不一定是要与你所需要设定的单元格在同一个sheet）中把你所需要的选择列表输入好（必须输入在单行或单列中），然后选中它们，在EXCEL工作区域左上角的输入框为它们定义一个名字，比如List。

然后在"来源"处你可以输入=List或者=Sheet!List(如果List与当前单元格不在同一个sheet，sheet1为List所在的sheet的名字)，OK，回到你的单元格，可以看到右下角有个小三角，点之，则看到List的内容都列在里面了。

一个比较好的实践，是将List的内容放在一个单独的sheet里面，再将该sheet隐藏，这样别人就不会看到这个列表或者无意中改动了它啦。

再补充一哈，你可以为错误的输入定制输入提示和错误提示。同样在刚才的"数据有效性"对话框里面。用得好的话，你的Excel就象一个定制的应用程序啦。

兴高彩烈

8、组及分级显示

有时候会看到别人的EXCEL文档内容可以狠酷的分级显示，象这样：



怎么让自己的文档也可以用这样的形式组织起来，从而使数据显得更有条理呢？

狠简单，利用"组及分组显示"。

选择你需要合成在一个组里的数据，选择菜单上的"数据>组及分组显示>分组"，就可以把这批数据定义在一个组里，文档左边自动会出现一个"-"号，点击它就可以把这批数据折叠起来。

可以定义多个级别的分组，方法类似。

也可以让Excel给你自动分组，不过这样一来对你的数据的要求比较高，必须是原先已经整理得比较有条理的数据，个人觉得使用起来没有手动分组方便。

兴高彩烈

9、条件格式

某些情况下，我们可能需要给一个表格的不同数据设置不同的显示格式。比如说，数值在1-60之间的用红色字体，在60-80用黄色字体，80-100用绿色字体，等等。当然我们可以一个个设置，但是假如这些内容是经常会变动的，那么每次修改数值以后还要重新更改格式，就显得狠麻烦。

或者比如说我们使用了上面所说的"数据有效性"，给指定单元格设置了选择列表，我们希望当用户选择列表的某个值的时候把这个值显示成一种颜色，选择另一个值的时候显示成另一种颜色，怎么办呢？

用条件格式。

使用的方式是选中你要指定条件格式的区域，再从菜单中选择"格式 > 条件格式…"(Format > Conditional Formatting…)，就可以给它们指定满足不同条件的情况下要用的不同格式了。

遗憾的是条件格式最多只能适应三种情况，假如你有第四种情况同样需要设置成另一个格式的话，可以先用普通的格式设定方式把它的格式设置好，这样就有了四种不同的格式。再多，至少我是不知道怎么办了。

兴高彩烈

10、选择性粘贴

常常我们也会遇到这种情况。在表格的某些单元格中，有一些用公式计算出来的值，我们希望把计算的结果复制到另一个地方去，但是直接使用copy and paste的结果，是Excel直接把公式复制过去了，得不到我们所要的结果。

这个时候就可以用上"选择性粘贴"了。

选中要复制的内容，copy之，然后在目的地址处点右键，选择"选择性粘贴"("paste special")，可以看到一个选择性粘贴的对话框，你可以选择要粘贴的内容，包括公式、值、格式、注释、数据有效性、列宽，等等。最常用的就是选择性的粘贴"值"了。

选择性粘贴还可以使用在想把横列的内容改成纵列或者纵列改成横列的情况。一行已经输入好的数据要改成以列的方式排列，一个一个复制当然狠麻烦，你可以选中该行内容，复制以后选择"选择性粘贴"，再选择对话框最下方的"Transpose"（我的系统是英文的，不知道这个在中文系统里是怎么说的）就可以了。

兴高彩烈

11、几个好用的公式

1、自动求和

严格来讲这不算公式，是一个非常方便的快速统计的方法，但是这个功能常常被忽略。
我们有时会需要统计已经输入的一批数据的合计数、平均值、个数等等来做参考，为它们写一个公式计算是一种方式，但是假如这个结果不是我们的文档所需要的正式数据而只是一个参考数值，写一个公式就显得麻烦。

这时你可以直接选中需要统计的数据，注意到EXCEL右下角出现Sum=***了吗？在那上面点鼠标的右键，原来你还可以选择让系统自动统计狠多别的数据呢！



这下子方便多了吧？

兴高彩烈

11、几个好用的公式（2）

2、IF，COUNT和SUM

IF(logical_test, value_if_true, value_if_false): 执行真假值判断，根据对指定条件进行逻辑评价的真假而返回不同的结果。IF可最多嵌套7层。
COUNT: 统计参数表中的数字参数和包含数字的单元格的个数。
SUM: 对若干个单元格的内容求和。

这几个公式结合使用，妙用无穷。

比如说我们有一个表，记录测试的情况。测试类型有好几种，测试的结果也有好几种。我们想知道测试重要性为"High"，而测试结果为"OK"的记录总共有多少条，怎么算呢？

假设测试数据放在Sheet1中，重要性在B列的2到100行，结果在C列的2到100行，我们通常愿意在Sheet2中统计结果（这是一个比较好的习惯。把具体数据和统计数据分来，看起来会有条理一些）。那么可以写一个这样的公式来计算上述所求数据：
SUM(IF('Sheet1'!B2:B100="High",IF('Sheet'!D2:D100="OK",1,0)))

公式先用IF判断D2到D100中是否有等于"OK"的值，是则返回1，否则返回0，为了便于说明，我们称这个临时结果为X1；
然后再嵌套一层IF判断，如果B2到B100有等于"High"的，则结果是刚才计算出来的X1，否则为0。
也就是说，如果只满足D为OK的条件不满足B为HIGH，结果是0；如果只满足B不满足D列条件，结果为X1也是0。

这样讲可以明白吗？

还有一个狠重要的提示。在EXCEL中，输入完复合公式以后，不能直接按ENTER来使公式生效，一定要输入SHIFT+CTRL+ENTER，这样EXCEL才会接受你所输入的公式。所以假如有时候别人的表格里用的好好的公式，你复制过来以后无论如何结果就一直是0，狠有可能就是你没有输入上述复合按键来激活公式了——不要问我为什么，我也不知道MICROSOFT为虾米要这样设计，人家总有人家的苦衷吧。

公式的嵌套和复合使用有一点难理解，不过我们常常可以通过复制别人已有的公式来得到自己需要的结果，所以只要大致明白公式的含义应该就可以了。

如果还是觉得狠复杂，后面我们会讲一个狠好用的帮我们做复杂的求和的宏工具。

兴高彩烈

11、几个好用的公式（3）

3、DSUM和DCOUNT

DSUM(database, field, criteria): 对满足给定条件的数据库中记录的字段（列）数据总和。
DCOUNT(database, field, criteria): 从满足给定条件的数据库记录的字段（列）中，计算包含数值的单元格数目。

介个东东用起来也系狠棒D，效果类似于嵌套的IF和COUNT/SUM，但是更灵活写出来的公式也更简洁（废话，既然如此干吗你刚才还介绍复杂的IF和COUNT/SUM呀？！问住我了，:p）。

假如说在下面这个表中，我们要计算产品A在东区销售的总体情况，大家想一想，怎么做最方便呢？



当然我们可以用上面说的IF和SUM的组合，但是当条件增加了，比如说还想知道具体由某个人销售出去的在东区的总和，那公式就越来越复杂了。

用DSUM可以狠简洁的做这个计算，条件是要使用一个辅助的表格。

在表格的旁边空白区域——假设是H1到I2的区域（是不是可以把条件写到另外的Sheet中，我没有去研究，大伙儿用的时候试一下就知道了），我们输入两行信息，第一行是：Product, Region，第二行：A, East。注意这里第一行是我们要统计的信息的行头，一定要和表格里面的行头是一致的，第二行是查询的条件。

然后在需要结果的地方输入公式=DSUM(B1:F21,4,H1:I2)，就可以得到结果了。狠容易理解对不对？从B1到 F21中，查询满足条件H1:I2的数据在第四行（从B开始计算，Sale是第四个行）的总和。

DCOUNT的用法类似。

兴高彩烈

11、几个好用的公式（4）

4、VLOOKUP

VLOOKUP(lookup_value, table_array, col_index_num, range_lookup): 搜索表区域首列满足条件的元素，确定待检索单元格在区域中的行序号，再进一步返回选定单元格的值。默认情况下，表是以升序排序的。

介个东东用起来就酷了！

比如说，仍然是在上述那个表格中，我们想查找第十号单的销售数量，怎么办？

已有的公式似乎都不能解决这个问题，幸亏这个时候VLOOKUP横空出世！

可以使用公式VLOOKUP(10,A2:F21,5,FALSE)来得到结果。它的意思是：查找区域A2到F21中首列为10的记录,返回第五列的数值。（美中不足的是，查询条件必须在查询区域的首行，缺乏了足够的灵活性）

注意在EXCEL中文版，关于这个公式的帮助信息存在一个BUG：RANGE_LOOKUP域的帮助信息错误（与事实相反）

而事实上，False才是精确查找， True为大致匹配

这个参数有什么用呢？

在上述公式中，如果第一列为6.2,而第四个条件为FALSE,我们如果在查询条件处输入"6"，那么将无法得到结果;如果第四个条件为TRUE,将查询出首列值为6的数值

兴高彩烈

11、几个好用的公式（5）

5、综合练习

讲了半天，如果没有一个酷一点的例子来说明，大家的印象可能不够深刻哈，好人做到底，这是我们上课的作业，可以说明狠多问题，有心学习的同学可以一起来做做看。

下面是我们希望实现的效果。这是一个出货记录表，表中的第二个Sheet存放员工信息，第三个Sheet存放产品信息，我们希望用户只能从下拉列表中选择员工编号，然后系统自动的在编号选择完以后显示员工姓名和所属地区；同样的，下拉列表包含了产品代码列表，选择一个产品以后，直接显示该产品的型号和单价等，最后，用户输入产品数量（大于等于1的数字），系统自动计算出它的总价。



利用我们原先讲过的"数据有效性"，可以狠容易的给员工编号和产品代码指定下拉列表。记得，你可以把整个A列选中来指定A列中所有行的数据有效性，这样方便狠多。

至于产品数量，同样也是数据有效性问题。在有效性对话框中指定它为数字且必须大于1就可以了。注意一个好的输入提示和错误输入提示信息，可以使你做的这个表格显得更专业。

最后就是选了员工编号显示员工信息和选择产品编号显示产品信息的问题了。显然，它们都可以用VLOOKUP来实现，公式分别为：
姓名：VLOOKUP(A2,员工信息!$A$2:$D$11,2,FALSE)
地区：VLOOKUP(A2,员工信息!$A$2:$D$11,4,FALSE)
产品：VLOOKUP(D2,产品信息!$A$2:$D$7,2,FALSE)
型号：VLOOKUP(D2,产品信息!$A$2:$D$7,3,FALSE)

基本上这样就可以实现我们所要的，从下拉列表选择一个值，得到相应的具体信息这样一个功能了。

可是且慢，还有一个问题，大家想到了吗？

那就是当编号没有选择的时候，其余项会出现"#N/A"这样的显示，看起来狠难看，这显然不是我们可以接受的。

解决的方法是在VLOOKUP外嵌套一个IF语句，例如对于姓名，我们的公式改为：IF(A2="","",VLOOKUP(A2,员工信息!$A$2:$D$11,2,FALSE))

这样就一切OK啦，是不是狠厉害呀？:D

最后，假如你不希望自己做好的表格会被使用的人修改，你可以把指定单元格锁定。在上述例子中，可以先选中员工姓名、地区等需要系统自动计算的单元格，然后右键选择"格式化单元格"("Format Cell")，在对话框的最后一个Page"保护"（protection）中，可以选中"锁定"("locked")。这还没有完，再选择"工具 > 保护 > 保护工作表"，可以看到一个对话框，你可以在那里选择你要保护的对象。这里我们选择"选择未锁定单元格"(select unlocked cell)，你还可以指定一个解锁的密码，这样以后要解开单元格的锁定时，只有知道密码的人才有权限。

好了，现在看看你的表格，除了下拉列表和数量输入框，其它地方都是锁定的，用户只能从列表中选择员工和产品信息、输入数量，其余一切自动完成，而且别人也不知道你是如何实现的——棒不棒？

兴高彩烈

12、数据透视表

这个话题讲起来可就大了，我也只是懂一点皮毛。平时需要我做大量数据统计的情况不多，因此基本没有使用过这个功能。但是因为它实在太强大，有理由相信，必定有部分同学获悉有这个功能以后会灰常黑皮，所以我还是稍微讲一讲。

来龙去脉什么的我没有本事讲，就以一个例子来说明数据透视表功能的强大，真正有兴趣的同学可以在做了这个例子的基础上再参考一下帮助文件，收获一定会狠大。

对于我自己来说，学习使用一个工具，并不是把它的每一个参数、选项都了解得非常透彻才算有效，我认为，只要大体上知道工具可以实现的功能，使用的时候再根据这些去查询系统的帮助文件，效率就狠高了。如果大家也是抱着这种态度来看下面的说明，应该会觉得放松一些。

下面是一个数据表：


从这个表来看数据，当然狠翔实，但是缺乏直观的比较，如果要做各种类型的数据汇总，可以想象一定要花狠多时间来写大量的公式。用EXCEL提供的快速图表建立方法当然也可行，但是又不足够灵活。怎么办呢？

数据透视表可以解决上述的问题。

建立数据透视表的方式如下：从菜单的"数据"项中选择"数据透视表"（Data  PivotTable and PivotChart Report…），在Wizard的指导下建立一个新的透视表框架（在最后一个对话框中，我们选择在新的Sheet中建立透视表，和前面一直强调的一样，这是一个比较好的习惯。把数据和统计的结果分开，看起来会比较清晰）。



好了，现在EXCEL自动在新的表单中建立了一个空白的数据透视表。假设我们想按照地区-->产品--> 型号这样一个顺序来分类汇总我们的数据,那么先将地区按纽如下拖动:



再将产品、型号拖动至地区下方，将日期拖动到行标题处



最后将销售量拖动到数据区域：


OK，这样一个很清晰的数据透视表就完成了。数据分类汇总非常清晰，而且操作起来无比简单。

对于多余的分类（如型号），如果我们不需要对它们进行汇总统计，可以直接用鼠标将该字段移走；需要时，可以重新从菜单上移回来；汇总的层次也可以通过拖动列头调整位置来实现。

在上述表格中，假如我们除了总体的汇总还需要按月进行汇总，可以在"日期"上点击鼠标右键，选择"组及分级显示>组合" （依据可多选）



如果需要按季度汇总，双击季度按纽，在下列对话框中选择"分类汇总>自动"



如果需要按地区分页显示统计数据，先将地区拖动到数据表顶部，从右键菜单中选择"分页显示"



最后，还可以轻易通过数据透视表创建数据透视图（点击图表按纽）



到这里可能有好学好思考的同学要问了：既然这个东东这么好用操作又简单，为虾米你不总用呀？问得好，还不是因为使用这个表对原始数据的要求比较高嘛。

使用数据透视表，原始数据必须：
c) 一列只放一个性质的数据
d) 每行必须是一条完整的记录
e) 必须要有标题行
f) 对于内容重复的单元格，不要随意合并（尤其是在列标题处）。原始数据无须追求格式。
g) 原始数据变更以后，数据透视表必须更新。

清楚了吧？好好学习，天天向上啊！