[求助]求表格中"时间合计"的计算公式,谢谢先!

swimdefish

<table cellspacing="0" cellpadding="0" width="726" border="0" style="WIDTH: 543pt; BORDER-COLLAPSE: collapse;"><colgroup><col width="70" style="WIDTH: 53pt; mso-width-source: userset; mso-width-alt: 2240;"></col><col span="7" width="83" style="WIDTH: 62pt; mso-width-source: userset; mso-width-alt: 2656;"></col><col width="75" style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2400;"></col></colgroup><tbody><tr height="30" style="HEIGHT: 22.5pt; mso-height-source: userset;"><td class="xl25" width="70" height="30" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 53pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 22.5pt; BACKGROUND-COLOR: transparent;"><font face="宋体">　</font></td><td class="xl26" width="83" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 62pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent;"><strong><font face="宋体">Monday</font></strong></td><td class="xl26" width="83" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 62pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent;"><strong><font face="宋体">Tuesday</font></strong></td><td class="xl26" width="83" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 62pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent;"><strong><font face="宋体">Wedsday</font></strong></td><td class="xl26" width="83" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 62pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent;"><strong><font face="宋体">Thursday</font></strong></td><td class="xl26" width="83" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 62pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent;"><strong><font face="宋体">Friday</font></strong></td><td class="xl26" width="83" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 62pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent;"><strong><font face="宋体">Saturday</font></strong></td><td class="xl26" width="83" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 62pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent;"><strong><font face="宋体">Sunday</font></strong></td><td class="xl27" width="75" 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: transparent;"><font face="宋体" color="#ff0000"><strong>时间合计</strong></font></td></tr><tr height="39" style="HEIGHT: 29.25pt; mso-height-source: userset;"><td class="xl29" height="39" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 29.25pt; BACKGROUND-COLOR: transparent;"><strong><font face="宋体" color="#0000ff" size="2">张三</font></strong></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: transparent;"><font face="宋体" size="2">7:30-15:30</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: transparent;"><font face="宋体" size="2">11:30-15:30</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: transparent;"><font face="宋体" size="2">10:30-18:30</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: transparent;"><font face="宋体" size="2">7:30-15:30</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: transparent;"><font face="宋体" size="2">8:30-15:30</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: transparent;"><font face="宋体" size="2">8:00-15:00</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: transparent;"><font face="宋体" size="2">　</font></td><td class="xl25" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent;"><font face="宋体">　</font></td></tr><tr height="39" style="HEIGHT: 29.25pt; mso-height-source: userset;"><td class="xl29" height="39" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 29.25pt; BACKGROUND-COLOR: transparent;"><strong><font face="宋体" color="#0000ff" size="2">李四</font></strong></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: transparent;"><font face="宋体" size="2">11:00-19:30</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: transparent;"><font face="宋体" size="2">11:00-15:30</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: transparent;"><font face="宋体" size="2">9:00-16:30</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: transparent;"><font face="宋体" size="2">11:00-19:30</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: transparent;"><font face="宋体" size="2">9:00-14:30</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: transparent;"><font face="宋体" size="2">　</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: transparent;"><font face="宋体" size="2">8:00-15:00</font></td><td class="xl25" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent;"><font face="宋体">　</font></td></tr><tr height="39" style="HEIGHT: 29.25pt; mso-height-source: userset;"><td class="xl28" height="39" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 29.25pt; BACKGROUND-COLOR: transparent;"><strong><font face="宋体" color="#ff0000">时间合计</font></strong></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="宋体">　</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="宋体">　</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="宋体">　</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="宋体">　</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="宋体">　</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="宋体">　</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="宋体">　</font></td><td class="xl25" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent;"><font face="宋体">　</font></td></tr></tbody></table><p>本人是个菜菜鸟啊,请教坛子上的高手如何设置上面表格中的"时间合计"(即工作小时的合计)的计算公式啊???</p><p>谢谢嗷!</p><p></p><p></p>

wddn

<p>数组公式：</p><p>I2=24*SUM(IF(B2:H2="",,RIGHT(B2:H2,5)-LEFT(B2:H2,FIND("-",B2:H2)-1)))</p><p>或：</p><p>I2=24*SUM(IF(B2:H2="",,REPLACE(B2:H2,1,FIND("-",B2:H2),)-LEFT(B2:H2,FIND("-",B2:H2)-1)))</p><p></p>

swimdefish

<p>万分感谢这位朋友的帮助,</p><p>可是我把您发的两个公式都复制\粘贴试用了,数字出不来呀?!</p><p>&nbsp;</p><p><img src="attachments/dvbbs/2008-4/200841821183510120.jpg" border="0" onclick="zoom(this)" onload="if(this.width>document.body.clientWidth*0.5) {this.resized=true;this.width=document.body.clientWidth*0.5;this.style.cursor='pointer';} else {this.onclick=null}" alt="" /><br/></p><p>请朋友再关注一下哦,是不是我粘贴的不对啊?</p>

wddn

Ea3iY3wX.rar (2.35 KB, 下载次数: 46)

2008-4-20 21:44 上传

点击文件名下载附件
[求助]求表格中"时间合计"的计算公式,谢谢先!

<br/>