海上月明

editer by sun
posts - 162, comments - 51, trackbacks - 0, articles - 8
   :: 首页 :: 新随笔 ::  :: 聚合  :: 管理

[转]EXCEL中的多条件查询(整理)

Posted on 2011-03-29 18:48 pts 阅读(2697) 评论(0)  编辑  收藏 所属分类: Office
EXCEL中的多条件查询(整理)from:http://hi.baidu.com/binaryfan/blog/item/54dbf7099971cdc63ac7635f.html
2009-08-17 21:32

在EXCEL里面有多个方法可以完成多条件查询,先来了解合并数组这个概念:

如果Sheet2是这样的:

,B ,C

张三,一月,90

张三,二月,85

张三,三月,95

那么If({1,0},Sheet2!A1:A3&Sheet2!B1:B3,Sheet2!C1:C3)就是:

张三一月,90

张三二月,85

张三三月,95

也可以用Choosen({1,2},Sheet2!A1:A3&Sheet2!B1:B3,Sheet2!C1:C3),结果相同。现在就可以用vlookup来进行多条件查询了:

{=vlookup(A1&B1,If({1,0},Sheet2!A1:A3&Sheet2!B1:B3,Sheet2!C1:C3),2,false)}

还可以用sum函数直线多条件合计功能:

{=sum((Sheet2!A1:A3=A1)*(Sheet2!B1:B3=B1)*Sheet2!C1:C3)}

这个功能也很有意思,另外还可以用index+match来解决,有一个值得一提的小技巧是,对于长的公式需要同时按Ctrl+Shift+Enter来给单元内容加上{}

在理解上述公式之前需要理解if({1,0},......)公式的含义,见下:

IF({1,0},……)公式的解释

把它分解为一块一块,把相关的知识一一弄明白,合起来一定能明白。

1、IF(条件,返回值1,返回值2)

首先你要弄懂这个函数,如果条件为真,函数的结果为“返回值1”,如果条件为假,函数的结果是“返回值2”:

if(true,10,100)=10

if(1,"A","B")="A"

if(false,20,30)=30

if(0,"你","我")="我"


2、{1,2,3,4}是一个数组,一个数组能存储多个数值,数组的表示方式是{}。

{1,0}是个水平数组,它有两个值,一个是1,另一个是0。

{"张三","李四","王五"}也是一个水平数组,它有三个值。


3、连接符&也是这个公式中的一个知识点。

"A" & "B" & "C"="ABC"

"Aaa"&"Bcd"&"123"="AaaBcd123"


字符串可以连接,数组也可以连接:

{1,2,3}&{"A","B","C"}={"1A","2B","3C"}


单元格区域也可以连接:

假如A1:A10的数据为1到10,B1:B10的数据为a到j。

A1:A10&B1:B10的结果就是

1a

2b

3c

...

10j

再看个实际点的例子: 




4、再回过来说IF()函数

当条件是一个单变量时,总代表true和false,即“真”和“假”,它总是返回两个值当中的一个。

但是

但是,当条件不是单变量,而是一个数组时,当条件为{1,0}时,结果如何呢?

记住,返回的结果与条件一样,是个数组,而且,与条件数组的列数相同。


课堂上,老师说:同学们好,我们来做个游戏,当老师举起左手的时候,你说“语文”,当老师举起右手的时候,你说100,清楚了没有?同学们都说清楚了,于是:


老师举起左手,一同学回答“语文”:If(左手,"语文",100)="语文"

老师的话隐含了一层意思,左手为真,右手为假,左手为1,右手为0:If(1,"语文",100)="语文"

接下去的游戏自然还有:If(右手,"语文",100)=100

即 If(0,"语文",100)=100


轮到第三个同学了,老师把两只手都举了起来,那么这同学怎么回答?

也许这位同学会说“老师,你有病?”当然,我更希望这位同学能回答:“语文”,100。

不是么:IF({左手,右手},"语文",100)={"语文",100}

前面提过,左手是真,右手是假,写标准点,就是:

IF({true,false},"语文",100)={"语文",100}

也是:IF({1,0},"语文",100)={"语文",100}


你糊涂了吗?

没有?

没有就好。别钻牛角尖,这个比方打得可能不太合适。


接着,老师还会把游戏继续做下去,再把规则改一改:老师举左手的时候,回答科目,举右手的时候,回答成绩。

老师举起左手,同学D回答:语文、数学、英语、计算机

老师举起右手,同学E回答:100、90、95、80

这时,老师把左右手都举了起来,老师说,如果懂了,请正确说出结果。

于是,有同学回答:"语文",100;"数学",90;"英语",95;"计算机",80

老师满意极了。


用我们熟悉的方式表现出来,就是:

=IF({1,0},科目,成绩)={"语文",100;"数学",90;"英语",95;"计算机",80}

只有注册用户登录后才能发表评论。


网站导航: