[求助]查找引用满足条件的值

骁骁

[求助]查找引用满足条件的值 NfINsAnt.rar (8.63 KB, 下载次数: 34)

2008-3-2 20:55 上传

点击文件名下载附件
[求助]查找引用满足条件的值

<br/><p><font size="2">求助大家，请问如何按现在的工资岗级、工龄把以测算好工资标准表的岗位工资引用过去</font></p><p><font size="2">我们公司600多人逐个查找是在太多了</font></p>

zhaoxubin_cd

<p>请参考附件：</p><p>&nbsp;</p> RMwa7lEK.rar (9 KB, 下载次数: 142)

2008-3-2 21:57 上传

点击文件名下载附件
[求助]查找引用满足条件的值

<br/>

骁骁

谢谢热心的<strong><font face="宋体">zhaoxubin_cd，问题已解决</font></strong>

min526

<p>在公式中加入ctrl+shift+enter，即會出現{}，發現傳回值特別有用，但卻不知道其意義如何得出的？</p>

4fire

<p>求助</p><p>问题与本帖的有些不同。</p><p>希望模糊查找出数据。先感谢了！</p><p>原E列公式为<br/>数组公式=VLOOKUP（B13,$I$4:$T$20,MATCH(G13,(COLUMN(J:T)-10)*5,1)+2,0)</p><p><table cellspacing="0" cellpadding="0" width="555" border="0" style="WIDTH: 417pt; BORDER-COLLAPSE: collapse;"><colgroup><col width="96" style="WIDTH: 72pt; mso-width-source: userset; mso-width-alt: 3072;"></col><col></col><col width="72" style="WIDTH: 54pt;"></col><col></col><col span="4" width="81" style="WIDTH: 61pt; mso-width-source: userset; mso-width-alt: 2592;"></col><col></col><col width="63" style="WIDTH: 47pt; mso-width-source: userset; mso-width-alt: 2016;"></col><col></col></colgroup><tbody><tr height="19" style="HEIGHT: 14.25pt; mso-height-source: userset;"><td class="xl69" width="96" height="19" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 72pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent;"><font face="宋体">原要求为</font></td><td class="xl66" width="72" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 54pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;"><font face="宋体"></font></td><td class="xl66" width="81" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 61pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;"><font face="宋体"></font></td><td class="xl66" width="81" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 61pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;"><font face="宋体"></font></td><td class="xl66" width="81" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 61pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;"><font face="宋体"></font></td><td class="xl66" width="81" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 61pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;"><font face="宋体"></font></td><td class="xl66" width="63" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 47pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;"><font face="宋体"></font></td></tr><tr height="19" style="HEIGHT: 14.25pt; mso-height-source: userset;"><td class="xl67" height="19" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent;"><font face="宋体" color="#0000ff"><strong>求E列值</strong></font></td><td class="xl71" width="459" colspan="6" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 345pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;"><font face="宋体" color="#0000ff"><strong>B列岗级等于I列岗级，同岗级按工龄引用1档2档3档4档等值</strong></font></td></tr><tr height="19" style="HEIGHT: 14.25pt; mso-height-source: userset;"><td class="xl66" width="96" height="19" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 72pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent;"><p><font face="宋体" color="#0000ff"><strong></strong></font>&nbsp;</p><p><strong><font face="宋体" color="#0000ff"></font></strong>&nbsp;</p><p><strong><font face="宋体" color="#0000ff"></font></strong>&nbsp;</p></td><td class="xl66" width="72" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 54pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;"><font face="宋体" color="#0000ff"><strong></strong></font></td><td class="xl66" width="81" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 61pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;"><font face="宋体" color="#0000ff"><strong></strong></font></td><td class="xl66" width="81" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 61pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;"><font face="宋体" color="#0000ff"><strong></strong></font></td><td class="xl66" width="81" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 61pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;"><font face="宋体" color="#0000ff"><strong></strong></font></td><td class="xl66" width="81" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 61pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;"><font face="宋体" color="#0000ff"><strong></strong></font></td><td class="xl66" width="63" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 47pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;"><font face="宋体" color="#0000ff"><strong></strong></font></td></tr><tr height="19" style="HEIGHT: 14.25pt; mso-height-source: userset;"><td class="xl70" colspan="3" height="19" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent; mso-ignore: colspan;"><font face="宋体" color="#ff0000"><strong>现希望公式达到如下效果</strong></font></td><td class="xl66" width="81" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 61pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;"><font face="宋体" color="#ff0000"><strong></strong></font></td><td class="xl66" width="81" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 61pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;"><font face="宋体" color="#ff0000"><strong></strong></font></td><td class="xl66" width="81" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 61pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;"><font face="宋体" color="#ff0000"><strong></strong></font></td><td class="xl66" width="63" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 47pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;"><font face="宋体" color="#ff0000"><strong></strong></font></td></tr><tr height="19" style="HEIGHT: 14.25pt; mso-height-source: userset;"><td class="xl67" height="19" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent;"><font face="宋体" color="#0000ff"><strong>求E列值</strong></font></td><td class="xl68" colspan="6" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent; mso-ignore: colspan;"><strong><font face="宋体" color="#0000ff">B列岗级等于I列岗级，按C列值在工资表中查找大于等于C列值的最小数值</font></strong></td></tr></tbody></table></p><p>&nbsp;</p><p>&nbsp;</p><a href="http://club.excelhome.net/viewthread.php?tid=348015&extra=&page=1#1496000">http://club.excelhome.net/viewthread.php?tid=348015&extra=&page=1#1496000</a><br/>

[此贴子已经被作者于2008-8-22 13:06:43编辑过]

xolf

原帖已回复 <a href="http://club.excelhome.net/viewthread.php?tid=348015&extra=&page=1#1496000">http://club.excelhome.net/viewthread.php?tid=348015&extra=&page=1#1496000</a>

huahelon

<p>在下是刚入门,请问公式中"(COLUMN(J:T)-10)*5"是什么思路?</p><p>滴水成河。谢谢！</p>

sdmi

求助]查找引用满足条件的值  发表回复

kmzbh

zhaoxubin_cd 发表于 2008-3-2 21:57
请参考附件：&nbsp;

有没有简易点儿的表达式