|
 
 我们约会吧!
|
2#
发表于 2005-5-19 12:55
| 只看该作者
Excel高级应用
一、常用公式
术语：
参数：指输入给公式的值。
返回值：公式的计算结果。
1、vlookup，hlookup,查找。
比如我们想达到这样一个目的，在单元格A1中输入产品编号，在单元格B1中自动显示产品名称。当然，电脑是不知道根据编号来做出判
断的，如果人的记性好，是可以做到的，但是还得输入，是不是很烦，还记得我在Excel操作技巧中说的吗，我们就是天生的懒人。幸好有了比
尔大哥。
当然，我们可以做到让电脑知道，产品编号与产品名称的对应关系，所以我们要事先输入一个产品编号与产品名称的对应关系，也就是
一个表，电脑是不是很厉害，很快它就记住了呢。不过我们不能怕麻烦，当你将此表建好后，你还可以用到其它方面。
建好表(与Excel中的表不同，这里表示一组数据)后，假使这个表的区域为G1:H1000,G列为编码，H列为名称。所以，在B1中，我们输入
=VLOOKUP(A1,G1:H1000,2,TRUE)
这个公式是什么意思，A1,表示我们要查找A1，在哪里查找呢，在G1:H1000中查找，2，表示我们从哪一列得到结果，就是表G1:H1000中
的第2列，所以，意思是说，在G1:H1000中的G列中查找A1的值，如果找到，将得到本表中的第2列对应的值，如果G101与A1的值相同，将得到H1
01的值。
另：本公式有四个参数，除第二个参数为一个区域外，其它参数还可以是其它的公式，也叫公式的套嵌，这再其它公式中也一样。第四
个参数为TRUE,或是不输入表示精确匹配，为FALSE表示大致匹配，上面的公式也可以写成
=VLOOKUP(A1,G1:H1000,2,)
第四个参数没有输入，也就是什么都没有（什么都没有与空格，0，等是有很大的区别的）。
大致匹配：例如，a 与 abc或是bac,是大致匹配，"金帝"与"中粮金帝食品（深圳）有限公司"是大致匹配。
如果编码名称对照表大于两列，比如是G1:J1000,如是名称是在I列，那么公式就要写成
=VLOOKUP(A1,G1:J1000,3,) 或是 =VLOOKUP(A1,G1:I1000,3,)
当然，这是被动获取数据，还可能用于主动获取，即已经有了编码，来查询名称。
2、公式参数的锁定（可能我对这个功能的叫法不规范）
比如上面的vlookup公式，如果我们要输入很多产品编号，都要得到产品名称，我们可以把所有的产品编吗输入，比如在A1:A10中输入了
编号，将B1中的填充柄双击，B1:B10就全都填充上公式了（也可以复制过去），还记得在Excel操作技巧中介绍过吗？但是我们会发现一个问题
，在B2中公式就不对了，变成了=VLOOKUP(A2,G2:H1001,2,),这显然不行，因为我们的编码对照表是G1:H1000,这样，对照表中的第一条记录就
不能被查询到。在B3中就变成了=VLOOKUP(A3,G3:H1002,2,) ... B10 中的公式就变成了 = VLOOKUP(A10,G10:H1009,2,)，有一个方法可以解决
，将B1中的公式写成=VLOOKUP(A2,$G$2:$H$1001,2,),再填充一次，是不是没问题了？如果你将B1写成=VLOOKUP($A2,$G$2:$H$1001,2,)也行，
因为公式在复制（或自己动填充）时，是在同一列中进行的，所以A不管有没有锁定，都是不变的，如果你写成了=VLOOKUP(A$2,$G$2:$H$1001,
2,)或是=VLOOKUP($A$2,$G$2:$H$1001,2,),就会发现，填充或复制后，B1:B10中，所有的公式都是一样的，因为在A2这个参数中，你在2前面加
了一下$符号，所以复制时，A2不会对应的变为A3,A4....
2、ISERROR, 公式的结果是否出错
还是用第一个来举例，如果你输入的编码在表中用vlookup查不到，就会显示#N/A(或其它错误结果),这是我们就可以把公式写成：
=IF(ISERROR(VLOOKUP($A2,$G$2:$H$1001,2,)),,VLOOKUP($A2,$G$2:$H$1001,2,))
其中，ISERROR(VLOOKUP($A2,$G$2:$H$1001,2,))是IF公式的第一个参数，表示一个条件，对于VLOOKUP($A2,$G$2:$H$1001,2,),如果查
找的结果不存在，那么VLOOKUP最终会得到
#N/A，所以再计算ISERROR(#N/A)，很显然#N/A是一个错误，这句话是对的，所以，ISERROR(#N/A)=TRUE,所以最终公式就变为了IF(TRUE,,VLOO
KUP($A2,$G$2:$H$1001,2,)) =
,结果是"什么都没有"、"空"（不同于空格或是0，可能有时会显示为"0"），同样，如果VLOOKUP($A2,$G$2:$H$1001,2,)找到了，没有出错，就
变成了IF(FALSE,,VLOOKUP($A2,$G$2:$H$1001,2,)) = VLOOKUP($A2,$G$2:$H$1001,2,),这样就达到了我们的要求了。
3、sumif,条件求和
有一个销售表，如果我们要求出某个产品（已知编码）的总销售量，表如下：
A B C D
┌────┬──────┬──────┬──────┐
1 │分店代码│ 产品代码 │ 产品名称 │ 产品销量 │
├────┼──────┼──────┼──────┤
2 │ A001 │ C001 │ 产品1 │ 10 │
├────┼──────┼──────┼──────┤
3 │ A001 │ C002 │ 产品2 │ 11 │
├────┼──────┼──────┼──────┤
4 │ A001 │ C003 │ 产品3 │ 12 │
├────┼──────┼──────┼──────┤
5 │ A002 │ C001 │ 产品1 │ 10 │
├────┼──────┼──────┼──────┤
6 │ A001 │ C003 │ 产品3 │ 12 │
├────┼──────┼──────┼──────┤
7 │ A003 │ C002 │ 产品2 │ 11 │
├────┼──────┼──────┼──────┤
8 │ A002 │ C002 │ 产品2 │ 11 │
├────┼──────┼──────┼──────┤
9 │ A003 │ C003 │ 产品3 │ 12 │
├────┼──────┼──────┼──────┤
10 │ A002 │ C003 │ 产品3 │ 12 │
├────┼──────┼──────┼──────┤
11 │ A003 │ C001 │ 产品1 │ 10 │
├────┼──────┼──────┼──────┤
12 │ A002 │ C003 │ 产品3 │ 12 │
├────┼──────┼──────┼──────┤
13 │ A003 │ C002 │ 产品2 │ 11 │
├────┼──────┼──────┼──────┤
14 │ A003 │ C003 │ 产品3 │ 12 │
└────┴──────┴──────┴──────┘
此表中，比如要查C001的销量总计
X Y Z
┌────┬──────┬──────────────────┐
1 │产品代码│ 产品名称 │销量合计 │
├────┼──────┼──────────────────┤
2 │ C001 │ 产品1 │=sumif($A$2:$A$10,$X2,$D$2:$D$10) │
└────┴──────┴──────────────────┘
同样，加上$符号，是为了向下复制公式。公式的含义是指，在A2:A10区域中查找与X2匹配的所有的项，再把这些项对应的在D2 10区域内的所
有对应的数据求和，所以最终结果就是：30.
4、数组公式
预备知识：数组，比如{10,12,7,45,36,18,73}这是一个一维的数组，共有7个元素，第3个元素的值7。
对于有些公式（并不是所有的），可以接收数组作为参数，比如sum,像sum(1,2,3,4),它的参数就是一个数组：{1,2,3,4},
对于有些公式，不但可以接收数组作为参数，它的计算结果（我们通常叫返回值）也可以是数组，例如if。
所以，我们可以通过将sum和if两个函数组合一起，来得到我们想要的结果。
问题：在sumif公式中，可以按条件求和，但是，如果有多个条件呢,比如，我们要求A1分店的C001产品的销量汇总，用sumif就不行
了，但我们可以这样
=SUM(IF($A$2:$A$15="A001",IF($B$2:$B$15="C001",$D$2:$D$15,0),0))
或 =SUM(IF(($A$2:$A$15="A001" *($B$2:$B$15="C001",$D$2:$D$15,0))
对于第二种形式，是什么意呢，原来，在excle中，TRUE(真)的值是1,FALSE(假)的值是0,所是大于等于1的数，也可以表示为TRUE,小于
等于0的数可以表示为FALSE，而且
+，-,*,/这些运算符号，也可能对数组进行运算，所以，分店代码为A001,且产品代码为C001这个可以用($A$2:$A$15="A001"*($B$2:$B$15="C
001"来表示，因为1*1=1,0*0=0,1*0=0,0*1=0,所以，这个乘号就也可以进行Bool值（TRUE,FALSE）运算了，即TRUE*TRUE=TRUE,FALSE*FALSE =
FALSE,TRUE*FALSE = FLASE,如果条件不是分店代码为A001,且产品代码为C001,而是分店代码为A001,或产品代码为C001，这个条件就可以表示
为：($A$2:$A$15="A001"+($B$2:$B$15="C001",因为TRUE + TRUE = 2 为TRUE,TRUE + FLASE = 1,为TRUE,FLASE + FALSE = 0,为FALSE,所以
就达到 ，* 可以作为逻辑运算符的AND,+可以作为OR。
X Y Z
┌────┬──────┬────────────────────────────┐
1 │分店代码│ 产品代码 │销量合计 │
├────┼──────┼────────────────────────────┤
2 │ A002 │ C001 │=SUM(IF(($A$2:$A$15=$X2)*($B$2:$B$15=$Y2),$D$2:$D$15,0))│
└────┴──────┴────────────────────────────┘
¤¤¤很关键的一点，当此公式输完后，不能直接按回车，要同时按住Ctrl+Shift,再按回车，这样，数组公式才生效果，对所有的
数组公式是一样的。 |
|
发表于 2005-5-21 00:29
| 
