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

bin_yang168

<p>=HLOOKUP(A9&amp;B9,IF({1;0},TRANSPOSE($A$2:$A$6&amp;$B$2:$B$6),TRANSPOSE($C$2:$C$6)),2,)数组公式</p>

小米渣

<p>好贴！学习ing！</p>[em02]

格子布圣诞鹿

<div class="msgheader">QUOTE:</div><div class="msgborder"><b>以下是引用<i>bin_yang168</i>在2007-8-27 20:25:24的发言：</b><br/><p>=HLOOKUP(A9&amp;B9,IF({1;0},TRANSPOSE($A$2:$A$6&amp;$B$2:$B$6),TRANSPOSE($C$2:$C$6)),2,)数组公式</p></div><p>转置过来用HLOOKUP，亏你想得出来！！！</p>

zxc304

真得好好学一下,BB我是没机会了

上东阳光

大家好好再想想[em01]

sailez113

<p>想要金币！</p><p>｛=SUM(IF(A2:A6=A9,IF(B2:B6=B9,C2:C6,0),0))｝</p>

Ewido

<div class="msgheader">QUOTE:</div><div class="msgborder"><b>以下是引用<i>sailez113</i>在2007-8-28 16:10:31的发言：</b><br/><p>想要金币！</p><p>｛=SUM(IF(A2:A6=A9,IF(B2:B6=B9,C2:C6,0),0))｝</p></div><p>不错的答案 很好的补充</p><p>&nbsp;</p><p>学习了</p>

[此贴子已经被作者于2007-9-2 20:26:39编辑过]

业余爱好者

<p>大家水平有的</p><p>一下子弄出这么多来了</p><p>{=SUM((A9&amp;B9=A2:A6&amp;B2:B6)*C2:C6)}</p><p>这个同类的有,一样的好象没看到,呵呵,算不算一个呢?</p>

BeatIt

<p>好贴啊</p><p>感觉一下子学了好多函数呢！！</p><p>今天收获真大</p>

wyg0575

<table cellspacing="0" cellpadding="0" width="343" border="0" xstr="" style="WIDTH: 258pt; BORDER-COLLAPSE: collapse;"><colgroup><col width="72" style="WIDTH: 54pt;"></col><col></col><col width="75" style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2400;"></col><col></col><col width="58" style="WIDTH: 44pt; mso-width-source: userset; mso-width-alt: 1856;"></col><col></col><col width="138" style="WIDTH: 104pt; mso-width-source: userset; mso-width-alt: 4416;"></col><col></col></colgroup><tbody><tr height="19" style="HEIGHT: 14.25pt;"><td width="72" height="19" style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 54pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent;"><p></p></td><td class="xl22" width="75" style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 56pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #ccffcc;"><font size="2">产品名称</font></td><td class="xl22" width="58" style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 44pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #ccffcc;"><font size="2">型号</font></td><td class="xl22" width="138" style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 104pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #ccffcc;"><font size="2">销售数量</font></td></tr><tr height="20" style="HEIGHT: 15pt;"><td height="20" xfmla="=B2&amp;C2" style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent;">AFD</td><td class="xl23" style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent;"><font face="Arial" size="2">A</font></td><td class="xl23" style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent;"><font face="Arial" size="2">FD</font></td><td class="xl23" align="right" xnum="" style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent;"><font face="Arial" size="2">10</font></td></tr><tr height="20" style="HEIGHT: 15pt;"><td height="20" xfmla="=B3&amp;C3" style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent;">ECD</td><td class="xl23" style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent;"><font face="Arial" size="2">E</font></td><td class="xl23" style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent;"><font face="Arial" size="2">CD</font></td><td class="xl23" align="right" xnum="" style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent;"><font face="Arial" size="2">20</font></td></tr><tr height="20" style="HEIGHT: 15pt;"><td height="20" xfmla="=B4&amp;C4" style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent;">EAB</td><td class="xl24" style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #ffff99;"><font face="Arial" size="2">E</font></td><td class="xl24" style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #ffff99;"><font face="Arial" size="2">AB</font></td><td class="xl24" align="right" xnum="" style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #ffff99;"><font face="Arial" size="2">30</font></td></tr><tr height="20" style="HEIGHT: 15pt;"><td height="20" xfmla="=B5&amp;C5" style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent;">DFE</td><td class="xl23" style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent;"><font face="Arial" size="2">D</font></td><td class="xl23" style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent;"><font face="Arial" size="2">FE</font></td><td class="xl23" align="right" xnum="" style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent;"><font face="Arial" size="2">45</font></td></tr><tr height="20" style="HEIGHT: 15pt;"><td height="20" xfmla="=B6&amp;C6" style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent;">EXE</td><td class="xl23" style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent;"><font face="Arial" size="2">E</font></td><td class="xl23" style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent;"><font face="Arial" size="2">XE</font></td><td class="xl23" align="right" xnum="" style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent;"><font face="Arial" size="2">20</font></td></tr><tr height="20" style="HEIGHT: 15pt;"><td height="20" xstr="" xfmla="=B7&amp;C7" style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent;"><font face="Arial" size="2"></font></td><td class="xl23" style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent;"><font face="Arial" size="2"></font></td><td class="xl23" style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent;"><font face="Arial" size="2"></font></td><td class="xl23" style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent;"><font face="Arial" size="2"></font></td></tr><tr height="19" style="HEIGHT: 14.25pt;"><td height="19" xfmla="=B8&amp;C8" style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent;">产品名称型号</td><td class="xl22" style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #ccffcc;"><font size="2">产品名称</font></td><td class="xl22" style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #ccffcc;"><font size="2">型号</font></td><td class="xl22" style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #ccffcc;"><font size="2">销售数量</font></td></tr><tr height="20" style="HEIGHT: 15pt;"><td height="20" xfmla="=B9&amp;C9" style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent;">EAB</td><td class="xl23" style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent;"><font face="Arial" size="2">E</font></td><td class="xl23" style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent;"><font face="Arial" size="2">AB</font></td><td class="xl24" align="right" xfmla="=VLOOKUP(A9,A2:D6,4,FALSE)" xnum="" style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #ffff99;"><font face="Arial" size="2">30</font></td></tr></tbody></table><p></p><p>=VLOOKUP(A9,A2:D6,4,FALSE)</p><p>&nbsp;</p><p>不要搞的那么复杂，加个辅助列，简单实用就好</p>

[此贴子已经被作者于2007-9-3 21:15:31编辑过]