多条件查询（不是多条件求和）求助

三家金顶

<p>见附件：如何将需要满足三个以上条件的数据从一个数据表中用公式查找出来并放到指定的单元格中？（不改变数据表顺序，也不用分类汇总之类的方法）</p><p>&nbsp;</p> 4U94bo6a.rar (2.67 KB, 下载次数: 19)

2008-8-29 15:22 上传

点击文件名下载附件
多条件查询（不是多条件求和）求助 <br/>

[此贴子已经被作者于2008-8-29 15:22:11编辑过]

yvhanym

<p>附件在哪？？</p>

liiyii1340

附件？

tinytree

<p>用ado操作</p><p></p>

tinytree

<p>vba也行</p><p></p>

mcwdl

<p>我很想看一下，大家编一下代码。</p>

mcwdl

<p><table cellspacing="0" cellpadding="0" width="343" border="0" xstr="" style="WIDTH: 258pt; BORDER-COLLAPSE: collapse;"><colgroup><col width="84" style="WIDTH: 63pt; mso-width-source: userset; mso-width-alt: 2688;"></col><col width="85" style="WIDTH: 64pt; mso-width-source: userset; mso-width-alt: 2720;"></col><col width="74" style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2368;"></col><col width="100" style="WIDTH: 75pt; mso-width-source: userset; mso-width-alt: 3200;"></col></colgroup><tbody><tr height="26" style="HEIGHT: 19.5pt; mso-height-source: userset;"><td class="xl25" width="84" height="26" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 63pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 19.5pt; BACKGROUND-COLOR: white;"><font size="2">物料名称</font></td><td class="xl25" width="85" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 64pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white;"><font size="2">品牌</font></td><td class="xl25" width="74" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 56pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white;"><font size="2">类型</font></td><td class="xl26" width="100" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 75pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white;"><font size="2">销售收入</font></td></tr><tr height="17" style="HEIGHT: 12.75pt;"><td class="xl27" height="17" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent;"><font size="2">name1</font></td><td class="xl27" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent;"><font size="2">Slek</font></td><td class="xl27" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent;"><font size="2">promo</font></td><td class="xl28" align="right" xnum="1023577.0795850657" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: yellow;"><font size="2">1,023,577 </font></td></tr><tr height="17" style="HEIGHT: 12.75pt;"><td class="xl27" height="17" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent;"><font size="2">name2</font></td><td class="xl27" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent;"><font size="2">Slek</font></td><td class="xl27" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent;"><font size="2">promo</font></td><td class="xl28" align="right" xnum="692111.3519932552" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: yellow;"><font size="2">692,111 </font></td></tr><tr height="17" style="HEIGHT: 12.75pt;"><td class="xl27" height="17" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent;"><font size="2">name3</font></td><td class="xl24" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent;"><font face="Arial" size="2">Maestro</font></td><td class="xl27" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent;"><font size="2">promo</font></td><td class="xl28" align="right" xnum="951068.48193838494" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: yellow;"><font size="2">951,068 </font></td></tr><tr height="17" style="HEIGHT: 12.75pt;"><td class="xl27" height="17" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent;"><font size="2">name4</font></td><td class="xl24" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent;"><font face="Arial" size="2">Maestro</font></td><td class="xl27" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent;"><font size="2">promo</font></td><td class="xl28" align="right" xnum="601250.66761997109" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: yellow;"><font size="2">601,251 </font></td></tr><tr height="16" style="HEIGHT: 12pt;"><td class="xl27" height="16" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12pt; BACKGROUND-COLOR: transparent;"><font size="2">name5</font></td><td class="xl27" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent;"><font size="2">Slek</font></td><td class="xl27" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent;"><font size="2">new</font></td><td class="xl28" align="right" xnum="1084531.5132657797" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: yellow;"><font size="2">1,084,532 </font></td></tr><tr height="16" style="HEIGHT: 12pt;"><td class="xl27" height="16" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12pt; BACKGROUND-COLOR: transparent;"><font size="2">name6</font></td><td class="xl27" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent;"><font size="2">Slek</font></td><td class="xl27" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent;"><font size="2">sku</font></td><td class="xl28" align="right" xnum="79411.647559456396" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: yellow;"><font size="2">79,412 </font></td></tr><tr height="17" style="HEIGHT: 12.75pt;"><td class="xl27" height="17" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent;"><font size="2">name7</font></td><td class="xl24" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent;"><font face="Arial" size="2">Hair Song</font></td><td class="xl27" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent;"><font size="2">new</font></td><td class="xl28" align="right" xnum="373701.90365324798" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: yellow;"><font size="2">373,702 </font></td></tr><tr height="17" style="HEIGHT: 12.75pt;"><td class="xl27" height="17" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent;"><font size="2">name8</font></td><td class="xl24" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent;"><font face="Arial" size="2">Hair Song</font></td><td class="xl27" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent;"><font size="2">sku</font></td><td class="xl28" align="right" xnum="287372.29166848521" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: yellow;"><font size="2">287,372 </font></td></tr><tr height="16" style="HEIGHT: 12pt;"><td class="xl27" height="16" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12pt; BACKGROUND-COLOR: transparent;"><font size="2">name9</font></td><td class="xl27" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent;"><font size="2">Slek</font></td><td class="xl27" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent;"><font size="2">sku</font></td><td class="xl28" align="right" xnum="117999.701447334" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: yellow;"><font size="2">118,000 </font></td></tr><tr height="16" style="HEIGHT: 12pt;"><td class="xl27" height="16" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12pt; BACKGROUND-COLOR: transparent;"><font size="2">name10</font></td><td class="xl27" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent;"><font size="2">Slek</font></td><td class="xl27" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent;"><font size="2">sku</font></td><td class="xl28" align="right" xnum="-326678.150574008" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: yellow;"><font size="2">-326,678 </font></td></tr><tr height="17" style="HEIGHT: 12.75pt;"><td class="xl29" height="17" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: #ff99cc;"><font size="2">name11</font></td><td class="xl30" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ff99cc;"><font face="Arial" size="2">Maestro</font></td><td class="xl29" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ff99cc;"><font size="2">promo</font></td><td class="xl31" align="right" xnum="861613.13344644732" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ff99cc;"><font size="2">861,613 </font></td></tr></tbody></table></p>

cailc1361

SoQxevbQ.rar (2.85 KB, 下载次数: 39)

2008-8-29 16:10 上传

点击文件名下载附件
多条件查询（不是多条件求和）求助 <br/>数组公式

三家金顶

谢谢！高手，简洁实用。有了｛=LARGE(IF((B4:B35=F5)*(C4:C35=F6),D4:D35),F4)｝就可以查询到物料名称了，用｛=INDEX(A4:A35,MATCH(LARGE(IF((B4:B35=F5)*(C4:C35=F6),D4:D35),F4),D4:D35,0))｝

yaoxuejian

学习了··[em01]