双条件查找征解(每个答案奖励5个金币)

兰色幻想 发表于 2007-8-25 12:49

这个题目很简单,出这个题目的是想尽可能多的征集不同的答案,作为双条件查找的一个答案小集吧. 规则: 1、每个不同的答案奖励5个金币，答案和前面贴子相同和极其近似的不奖励。  2、每楼只能提供1个答案，如果有多个案，须分开楼层发贴。  3、上传答案时，必须把公式贴出来。   题目：根据提供的产品名称和型号查找销售数量，如下图。

lpz001 发表于 2007-8-25 12:53

=SUM(($A$2:$A$6=A9)*($B$2:$B$6=B9)*$C$2:$C$6)挺笨的一个答案哈，重在参与！开动脑筋，咯嘀咯嘀 
[此贴子已经被作者于2007-8-25 12:55:53编辑过]

格子布圣诞鹿 发表于 2007-8-25 12:54

{=INDEX(C2:C6,MATCH(A9&B9,A2:A6&B2:B6,0))}

格子布圣诞鹿 发表于 2007-8-25 12:55

=SUMPRODUCT((A2:A6=A9)*(B2:B6=B9)*(C2:C6))

80008 发表于 2007-8-25 12:56

<table cellspacing="0" cellpadding="0" width="278" border="0" xstr="" style="WIDTH: 209pt; BORDER-COLLAPSE: collapse;"><colgroup><col width="75" style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2400;"></col><col></col><col span="2" width="58" style="WIDTH: 44pt; mso-width-source: userset; mso-width-alt: 1856;"></col><col></col><col width="87" style="WIDTH: 65pt; mso-width-source: userset; mso-width-alt: 2784;"></col><col></col></colgroup><tbody><tr height="19" style="HEIGHT: 14.25pt;"><td class="xl22" width="75" height="19" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 56pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 14.25pt; BACKGROUND-COLOR: #ccffcc;">产品名称</td><td class="xl22" width="58" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 44pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #ccffcc;">型号</td><td class="xl22" width="58" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 44pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #ccffcc;">A&B</td><td class="xl25" width="87" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 65pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #ccffcc;">销售数量</td></tr><tr height="20" style="HEIGHT: 15pt;"><td class="xl23" height="20" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent;">A</td><td class="xl23" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;">FD</td><td class="xl23" xfmla="=A2&"|"&B2" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;">A|FD</td><td class="xl26" xnum="" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;">10</td></tr><tr height="20" style="HEIGHT: 15pt;"><td class="xl23" height="20" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent;">E</td><td class="xl23" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;">CD</td><td class="xl23" xfmla="=A3&"|"&B3" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;">E|CD</td><td class="xl26" xnum="" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;">20</td></tr><tr height="20" style="HEIGHT: 15pt;"><td class="xl24" height="20" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: #ffff99;">E</td><td class="xl24" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #ffff99;">AB</td><td class="xl23" xfmla="=A4&"|"&B4" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;">E|AB</td><td class="xl27" xnum="" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #ffff99;">30</td></tr><tr height="20" style="HEIGHT: 15pt;"><td class="xl23" height="20" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent;">D</td><td class="xl23" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;">FE</td><td class="xl23" xfmla="=A5&"|"&B5" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;">D|FE</td><td class="xl26" xnum="" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;">45</td></tr><tr height="20" style="HEIGHT: 15pt;"><td class="xl23" height="20" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent;">E</td><td class="xl23" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;">XE</td><td class="xl23" xfmla="=A6&"|"&B6" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;">E|XE</td><td class="xl26" xnum="" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;">20</td></tr><tr height="20" style="HEIGHT: 15pt;"><td class="xl23" height="20" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent;"></td><td class="xl23" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;"></td><td class="xl23" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;"></td><td class="xl26" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;"></td></tr><tr height="19" style="HEIGHT: 14.25pt;"><td class="xl22" height="19" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 14.25pt; BACKGROUND-COLOR: #ccffcc;">产品名称</td><td class="xl22" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #ccffcc;">型号</td><td class="xl22" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #ccffcc;">　</td><td class="xl25" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #ccffcc;">销售数量</td></tr><tr height="20" style="HEIGHT: 15pt;"><td class="xl23" height="20" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent;">E</td><td class="xl23" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;">AB</td><td class="xl23" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;"></td><td class="xl27" xfmla="=VLOOKUP(A9&"|"&B9,$C$2:$D$6,2,0)" xnum="" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #ffff99;">30</td></tr><tr height="20" style="HEIGHT: 15pt;"><td class="xl23" height="20" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent;"></td><td class="xl23" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;"></td><td class="xl23" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;"></td><td class="xl26" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;"></td></tr></tbody></table>加一个辅助列，公式 =VLOOKUP(A9&"|"&B9,$C$2:$D$6,2,0)  
[此贴子已经被作者于2007-8-25 12:57:57编辑过]

格子布圣诞鹿 发表于 2007-8-25 12:56

=DSUM(A1:C6,"销售数量",A8:B9)

80008 发表于 2007-8-25 13:02

=VLOOKUP(A9&B9,IF({1,0},$A$2:$A$6&B2:B6,$C$2:$C$6),2,0) 
[此贴子已经被作者于2007-8-25 13:03:53编辑过]

ddrs 发表于 2007-8-25 13:02

=LOOKUP(1,0/((A2:A6=A9)*(B2:B6=B9)),C2:C6)来一个先，不知道有没有重复！

ddrs 发表于 2007-8-25 13:04

=LOOKUP(1,0/(A2:A6&B2:B6=A9&B9),C2:C6)再一个！

格子布圣诞鹿 发表于 2007-8-25 13:06

=DGET(A1:C6,"销售数量",A8:B9)

页: [1] 2 3 4 5 6 7 8

Excel精英培训网's Archiver

双条件查找征解(每个答案奖励5个金币)