<p>人力资源统计中常用的多条件统计函数SUMPRODUCT:</p><p><table cellspacing="0" cellpadding="0" width="677" border="0" style="WIDTH: 508pt; BORDER-COLLAPSE: collapse;"><colgroup><col width="27" style="WIDTH: 20pt; mso-width-source: userset; mso-width-alt: 864;"></col><col width="64" style="WIDTH: 48pt; mso-width-source: userset; mso-width-alt: 2048;"></col><col width="53" style="WIDTH: 40pt; mso-width-source: userset; mso-width-alt: 1696;"></col><col width="57" style="WIDTH: 43pt; mso-width-source: userset; mso-width-alt: 1824;"></col><col width="476" style="WIDTH: 357pt; mso-width-source: userset; mso-width-alt: 15232;"></col></colgroup><tbody><tr height="19" style="HEIGHT: 14.25pt;"><td class="xl24" width="27" height="19" style="BORDER-RIGHT: #ccffff 0.5pt solid; BORDER-TOP: #ccffff 0.5pt solid; BORDER-LEFT: #ccffff 0.5pt solid; WIDTH: 20pt; BORDER-BOTTOM: #ccffff 0.5pt solid; HEIGHT: 14.25pt; BACKGROUND-COLOR: silver;"><font face="宋体"> </font></td><td class="xl25" width="64" style="BORDER-RIGHT: #ccffff 0.5pt solid; BORDER-TOP: #ccffff 0.5pt solid; BORDER-LEFT: #ccffff; WIDTH: 48pt; BORDER-BOTTOM: #ccffff 0.5pt solid; BACKGROUND-COLOR: silver;"><font face="宋体">A</font></td><td class="xl25" width="53" style="BORDER-RIGHT: #ccffff 0.5pt solid; BORDER-TOP: #ccffff 0.5pt solid; BORDER-LEFT: #ccffff; WIDTH: 40pt; BORDER-BOTTOM: #ccffff 0.5pt solid; BACKGROUND-COLOR: silver;"><font face="宋体">B</font></td><td class="xl25" width="57" style="BORDER-RIGHT: #ccffff 0.5pt solid; BORDER-TOP: #ccffff 0.5pt solid; BORDER-LEFT: #ccffff; WIDTH: 43pt; BORDER-BOTTOM: #ccffff 0.5pt solid; BACKGROUND-COLOR: silver;"><font face="宋体">C</font></td><td class="xl25" width="476" style="BORDER-RIGHT: #ccffff 0.5pt solid; BORDER-TOP: #ccffff 0.5pt solid; BORDER-LEFT: #ccffff; WIDTH: 357pt; BORDER-BOTTOM: #ccffff 0.5pt solid; BACKGROUND-COLOR: silver;"><font face="宋体">D</font></td></tr><tr height="19" style="HEIGHT: 14.25pt;"><td class="xl25" height="19" style="BORDER-RIGHT: #ccffff 0.5pt solid; BORDER-TOP: #ccffff; BORDER-LEFT: #ccffff 0.5pt solid; BORDER-BOTTOM: #ccffff 0.5pt solid; HEIGHT: 14.25pt; BACKGROUND-COLOR: silver;"><font face="宋体">1</font></td><td class="xl26" style="BORDER-RIGHT: #ccffff 0.5pt solid; BORDER-TOP: #ccffff; BORDER-LEFT: #ccffff; BORDER-BOTTOM: #ccffff 0.5pt solid; BACKGROUND-COLOR: transparent;"><font face="宋体">姓名</font></td><td class="xl26" style="BORDER-RIGHT: #ccffff 0.5pt solid; BORDER-TOP: #ccffff; BORDER-LEFT: #ccffff; BORDER-BOTTOM: #ccffff 0.5pt solid; BACKGROUND-COLOR: transparent;"><font face="宋体">性别</font></td><td class="xl26" style="BORDER-RIGHT: #ccffff 0.5pt solid; BORDER-TOP: #ccffff; BORDER-LEFT: #ccffff; BORDER-BOTTOM: #ccffff 0.5pt solid; BACKGROUND-COLOR: transparent;"><font face="宋体">工资</font></td><td class="xl26" style="BORDER-RIGHT: #ccffff 0.5pt solid; BORDER-TOP: #ccffff; BORDER-LEFT: #ccffff; BORDER-BOTTOM: #ccffff 0.5pt solid; BACKGROUND-COLOR: transparent;"><font face="宋体">备注</font></td></tr><tr height="19" style="HEIGHT: 14.25pt;"><td class="xl25" height="19" style="BORDER-RIGHT: #ccffff 0.5pt solid; BORDER-TOP: #ccffff; BORDER-LEFT: #ccffff 0.5pt solid; BORDER-BOTTOM: #ccffff 0.5pt solid; HEIGHT: 14.25pt; BACKGROUND-COLOR: silver;"><font face="宋体">2</font></td><td class="xl26" style="BORDER-RIGHT: #ccffff 0.5pt solid; BORDER-TOP: #ccffff; BORDER-LEFT: #ccffff; BORDER-BOTTOM: #ccffff 0.5pt solid; BACKGROUND-COLOR: transparent;"><font face="宋体">张三</font></td><td class="xl26" style="BORDER-RIGHT: #ccffff 0.5pt solid; BORDER-TOP: #ccffff; BORDER-LEFT: #ccffff; BORDER-BOTTOM: #ccffff 0.5pt solid; BACKGROUND-COLOR: transparent;"><font face="宋体">男</font></td><td class="xl26" style="BORDER-RIGHT: #ccffff 0.5pt solid; BORDER-TOP: #ccffff; BORDER-LEFT: #ccffff; BORDER-BOTTOM: #ccffff 0.5pt solid; BACKGROUND-COLOR: transparent;"><font face="宋体">60</font></td><td class="xl27" style="BORDER-RIGHT: #ccffff 0.5pt solid; BORDER-TOP: #ccffff; BORDER-LEFT: #ccffff; BORDER-BOTTOM: #ccffff 0.5pt solid; BACKGROUND-COLOR: transparent;"><font face="宋体" color="#ff0000">B7=SUMPRODUCT((C2:C6="女")*(D2:D6>=70)*(D2:D6<95))</font></td></tr><tr height="19" style="HEIGHT: 14.25pt;"><td class="xl25" height="19" style="BORDER-RIGHT: #ccffff 0.5pt solid; BORDER-TOP: #ccffff; BORDER-LEFT: #ccffff 0.5pt solid; BORDER-BOTTOM: #ccffff 0.5pt solid; HEIGHT: 14.25pt; BACKGROUND-COLOR: silver;"><font face="宋体">3</font></td><td class="xl26" style="BORDER-RIGHT: #ccffff 0.5pt solid; BORDER-TOP: #ccffff; BORDER-LEFT: #ccffff; BORDER-BOTTOM: #ccffff 0.5pt solid; BACKGROUND-COLOR: transparent;"><font face="宋体">李四</font></td><td class="xl26" style="BORDER-RIGHT: #ccffff 0.5pt solid; BORDER-TOP: #ccffff; BORDER-LEFT: #ccffff; BORDER-BOTTOM: #ccffff 0.5pt solid; BACKGROUND-COLOR: transparent;"><font face="宋体">女</font></td><td class="xl26" style="BORDER-RIGHT: #ccffff 0.5pt solid; BORDER-TOP: #ccffff; BORDER-LEFT: #ccffff; BORDER-BOTTOM: #ccffff 0.5pt solid; BACKGROUND-COLOR: transparent;"><font face="宋体">70</font></td><td class="xl28" style="BORDER-RIGHT: #ccffff 0.5pt solid; BORDER-TOP: #ccffff; BORDER-LEFT: #ccffff; BORDER-BOTTOM: #ccffff 0.5pt solid; BACKGROUND-COLOR: transparent;"><font face="宋体">性别为女工资>=70且<90的人数</font></td></tr><tr height="19" style="HEIGHT: 14.25pt;"><td class="xl25" height="19" style="BORDER-RIGHT: #ccffff 0.5pt solid; BORDER-TOP: #ccffff; BORDER-LEFT: #ccffff 0.5pt solid; BORDER-BOTTOM: #ccffff 0.5pt solid; HEIGHT: 14.25pt; BACKGROUND-COLOR: silver;"><font face="宋体">4</font></td><td class="xl26" style="BORDER-RIGHT: #ccffff 0.5pt solid; BORDER-TOP: #ccffff; BORDER-LEFT: #ccffff; BORDER-BOTTOM: #ccffff 0.5pt solid; BACKGROUND-COLOR: transparent;"><font face="宋体">王五</font></td><td class="xl26" style="BORDER-RIGHT: #ccffff 0.5pt solid; BORDER-TOP: #ccffff; BORDER-LEFT: #ccffff; BORDER-BOTTOM: #ccffff 0.5pt solid; BACKGROUND-COLOR: transparent;"><font face="宋体">男</font></td><td class="xl26" style="BORDER-RIGHT: #ccffff 0.5pt solid; BORDER-TOP: #ccffff; BORDER-LEFT: #ccffff; BORDER-BOTTOM: #ccffff 0.5pt solid; BACKGROUND-COLOR: transparent;"><font face="宋体">80</font></td><td class="xl29" style="BORDER-RIGHT: #ccffff 0.5pt solid; BORDER-TOP: #ccffff; BORDER-LEFT: #ccffff; BORDER-BOTTOM: #ccffff 0.5pt solid; BACKGROUND-COLOR: transparent;"><font face="宋体" color="#0000ff">B8=SUMPRODUCT((C2:C6="女")*(D2:D6>=70)*(D2:D6<95)*D2:D6)</font></td></tr><tr height="19" style="HEIGHT: 14.25pt;"><td class="xl25" height="19" style="BORDER-RIGHT: #ccffff 0.5pt solid; BORDER-TOP: #ccffff; BORDER-LEFT: #ccffff 0.5pt solid; BORDER-BOTTOM: #ccffff 0.5pt solid; HEIGHT: 14.25pt; BACKGROUND-COLOR: silver;"><font face="宋体">5</font></td><td class="xl26" style="BORDER-RIGHT: #ccffff 0.5pt solid; BORDER-TOP: #ccffff; BORDER-LEFT: #ccffff; BORDER-BOTTOM: #ccffff 0.5pt solid; BACKGROUND-COLOR: transparent;"><font face="宋体">赵六</font></td><td class="xl26" style="BORDER-RIGHT: #ccffff 0.5pt solid; BORDER-TOP: #ccffff; BORDER-LEFT: #ccffff; BORDER-BOTTOM: #ccffff 0.5pt solid; BACKGROUND-COLOR: transparent;"><font face="宋体">女</font></td><td class="xl26" style="BORDER-RIGHT: #ccffff 0.5pt solid; BORDER-TOP: #ccffff; BORDER-LEFT: #ccffff; BORDER-BOTTOM: #ccffff 0.5pt solid; BACKGROUND-COLOR: transparent;"><font face="宋体">90</font></td><td class="xl28" style="BORDER-RIGHT: #ccffff 0.5pt solid; BORDER-TOP: #ccffff; BORDER-LEFT: #ccffff; BORDER-BOTTOM: #ccffff 0.5pt solid; BACKGROUND-COLOR: transparent;"><font face="宋体">性别为女工资>=70且<90的人员的工资合计</font></td></tr><tr height="19" style="HEIGHT: 14.25pt;"><td class="xl25" height="19" style="BORDER-RIGHT: #ccffff 0.5pt solid; BORDER-TOP: #ccffff; BORDER-LEFT: #ccffff 0.5pt solid; BORDER-BOTTOM: #ccffff 0.5pt solid; HEIGHT: 14.25pt; BACKGROUND-COLOR: silver;"><font face="宋体">6</font></td><td class="xl26" style="BORDER-RIGHT: #ccffff 0.5pt solid; BORDER-TOP: #ccffff; BORDER-LEFT: #ccffff; BORDER-BOTTOM: #ccffff 0.5pt solid; BACKGROUND-COLOR: transparent;"><font face="宋体">周七</font></td><td class="xl26" style="BORDER-RIGHT: #ccffff 0.5pt solid; BORDER-TOP: #ccffff; BORDER-LEFT: #ccffff; BORDER-BOTTOM: #ccffff 0.5pt solid; BACKGROUND-COLOR: transparent;"><font face="宋体">女</font></td><td class="xl26" style="BORDER-RIGHT: #ccffff 0.5pt solid; BORDER-TOP: #ccffff; BORDER-LEFT: #ccffff; BORDER-BOTTOM: #ccffff 0.5pt solid; BACKGROUND-COLOR: transparent;"><font face="宋体">95</font></td><td class="xl26" style="BORDER-RIGHT: #ccffff 0.5pt solid; BORDER-TOP: #ccffff; BORDER-LEFT: #ccffff; BORDER-BOTTOM: #ccffff 0.5pt solid; BACKGROUND-COLOR: transparent;"><font face="宋体"> </font></td></tr><tr height="19" style="HEIGHT: 14.25pt;"><td class="xl25" height="19" style="BORDER-RIGHT: #ccffff 0.5pt solid; BORDER-TOP: #ccffff; BORDER-LEFT: #ccffff 0.5pt solid; BORDER-BOTTOM: #ccffff 0.5pt solid; HEIGHT: 14.25pt; BACKGROUND-COLOR: silver;"><font face="宋体">7</font></td><td class="xl30" style="BORDER-RIGHT: #ccffff 0.5pt solid; BORDER-TOP: #ccffff; BORDER-LEFT: #ccffff; BORDER-BOTTOM: #ccffff 0.5pt solid; BACKGROUND-COLOR: transparent;"><font face="宋体"> </font></td><td class="xl30" align="right" xfmla="=SUMPRODUCT((C3:C7="女")*(D3:D7>=70)*(D3:D7<95))" style="BORDER-RIGHT: #ccffff 0.5pt solid; BORDER-TOP: #ccffff; BORDER-LEFT: #ccffff; BORDER-BOTTOM: #ccffff 0.5pt solid; BACKGROUND-COLOR: transparent;"><font face="宋体">2</font></td><td class="xl31" colspan="2" style="BORDER-RIGHT: #ccffff 0.5pt solid; BORDER-TOP: #ccffff 0.5pt solid; BORDER-LEFT: #ccffff; BORDER-BOTTOM: #ccffff 0.5pt solid; BACKGROUND-COLOR: transparent;"><font face="宋体" color="#ff0000"> </font></td></tr><tr height="19" style="HEIGHT: 14.25pt;"><td class="xl25" height="19" style="BORDER-RIGHT: #ccffff 0.5pt solid; BORDER-TOP: #ccffff; BORDER-LEFT: #ccffff 0.5pt solid; BORDER-BOTTOM: #ccffff 0.5pt solid; HEIGHT: 14.25pt; BACKGROUND-COLOR: silver;"><font face="宋体">8</font></td><td class="xl30" style="BORDER-RIGHT: #ccffff 0.5pt solid; BORDER-TOP: #ccffff; BORDER-LEFT: #ccffff; BORDER-BOTTOM: #ccffff 0.5pt solid; BACKGROUND-COLOR: transparent;"><font face="宋体"> </font></td><td class="xl30" align="right" xfmla="=SUMPRODUCT((C3:C7="女")*(D3:D7>=70)*(D3:D7<95)*D3:D7)" style="BORDER-RIGHT: #ccffff 0.5pt solid; BORDER-TOP: #ccffff; BORDER-LEFT: #ccffff; BORDER-BOTTOM: #ccffff 0.5pt solid; BACKGROUND-COLOR: transparent;"><font face="宋体">160</font></td><td class="xl33" colspan="2" style="BORDER-RIGHT: #ccffff 0.5pt solid; BORDER-TOP: #ccffff 0.5pt solid; BORDER-LEFT: #ccffff; BORDER-BOTTOM: #ccffff 0.5pt solid; BACKGROUND-COLOR: transparent;"><font face="宋体" color="#0000ff"> </font></td></tr><tr height="19" style="HEIGHT: 14.25pt;"><td class="xl25" height="19" style="BORDER-RIGHT: #ccffff 0.5pt solid; BORDER-TOP: #ccffff; BORDER-LEFT: #ccffff 0.5pt solid; BORDER-BOTTOM: #ccffff 0.5pt solid; HEIGHT: 14.25pt; BACKGROUND-COLOR: silver;"><font face="宋体">9</font></td><td class="xl30" style="BORDER-RIGHT: #ccffff 0.5pt solid; BORDER-TOP: #ccffff; BORDER-LEFT: #ccffff; BORDER-BOTTOM: #ccffff 0.5pt solid; BACKGROUND-COLOR: transparent;"><font face="宋体"> </font></td><td class="xl30" style="BORDER-RIGHT: #ccffff 0.5pt solid; BORDER-TOP: #ccffff; BORDER-LEFT: #ccffff; BORDER-BOTTOM: #ccffff 0.5pt solid; BACKGROUND-COLOR: transparent;"><font face="宋体"> </font></td><td class="xl30" style="BORDER-RIGHT: #ccffff 0.5pt solid; BORDER-TOP: #ccffff; BORDER-LEFT: #ccffff; BORDER-BOTTOM: #ccffff 0.5pt solid; BACKGROUND-COLOR: transparent;"><font face="宋体"> </font></td><td class="xl30" style="BORDER-RIGHT: #ccffff 0.5pt solid; BORDER-TOP: #ccffff; BORDER-LEFT: #ccffff; BORDER-BOTTOM: #ccffff 0.5pt solid; BACKGROUND-COLOR: transparent;"><font face="宋体"> </font></td></tr></tbody></table></p>
[此贴子已经被作者于2006-12-13 15:53:05编辑过] |