ªð¦^¦Cªí ¤W¤@¥DÃD µo©«

[µo°Ý] ®É¯²°±¨®³õ¦¬¶OexceL°ÝÃD¡A¨D±Ð

[µo°Ý] ®É¯²°±¨®³õ¦¬¶OexceL°ÝÃD¡A¨D±Ð

§A­Ì¦n ¨®³õ.rar (8.8 KB) ¡A¨D±Ð?


1.¨®½ø¶i¤J®É¯²°±¨®³õ®É¡A¤£¤À¥­¤é©Î¤½²³°²´Á¡A¨C¤p®É¦¬¶O¬°18¤¸¡C
2.®É¯²°±¨®³õ¤©¨®¥D¤@¦¸©ÊÀu´f¤©¦U¨®¥D¡A­Y©ó¦­¤W08:00¦Ü±ß¤W08:00®Éªy¨®¡AÀu´f»ù¬°100¤¸¡C­Y©ó±ß¤W08:00¦Ü¦­¤W08:00®Éªy¨®¡AÀu´f»ù¬°100¤¸¡C
3.¦¹¬°¤@¦¸©ÊÀu´f¡A¤Î«á°±ªy¨C¤p®É¬°18¤¸¥¿¡C
4.¨Ò¤l:¨®¥D©ó¦­08:30®É°±ªy¡A19:30®ÉÂ÷¶}¡A°±ªy¬ù11¤p®É¡A¦X¦@¦¬¶O¬°18¤¸X11¤p®É¡A§Y198¤¸¡A¦ý¦]Àu´f®É¬q¡A¬G¥u¶·¥I100¤¸¡C

5.¨Ò¤l:¨®¥D©ó¦­08:30®É°±ªy¡A21:30®ÉÂ÷¶}¡A°±ªy¬ù13¤p®É¡A¦X¦@¦¬¶O¬°18¤¸X13¤p®É¡A§Y234¤¸¡A¦ý¦]Àu´f®É¬q¡A¨ä¤¤¦­¤W08:00¦Ü±ß¤W08:00®Éªy¨®¡AÀu´f»ù¬°100¤¸¡A¥u¶·¥I100¤¸¡A¥t¥~¨â¤p®É¦¬¶O¬°18¤¸X2¤p®É¡A©Ò¥H¨®¥D¶·¥I¨®¸ê136¤¸¡C

¨®³õ.rar (8.8 KB)

¨®³õ.rar (8.8 KB)

¥»©«³Ì«á¥Ñ ML089 ©ó 2017-3-31 20:35 ½s¿è

=SUMPRODUCT(--TEXT(ROUNDUP(FREQUENCY(TEXT(C3,"0!:00")*24*60+ROW(INDIRECT("1:"&INT((B5+TEXT(C5,"0!:00") - (B3+TEXT(C3,"0!:00")))*24*60))),"8:00"*24*60+(ROW(1:9)-1)*12*60)/60,0)*18,"[>100]!1!0!0;0"))

ROW(1:9) ±±¨î°±¨®³Ìªø¤Ñ¼Æ4.5¤Ñ¡A­Y³Ì¦h30¤Ñ®É´N§ï¬°ROW(1:60)
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

ÁÂÁ¡A§Ú¦¬¤u¸Õ¤U¥ý¡A¤S¤@¦¸¦hÁ§A¡A¥ç·Q°Ý¡A¬O§_¤£¨¬¤@¤p®É¥ç§@¤@¤p®É­pºâ¡A¨Ò¦p¡A¨®¥D11:00¤J®É¯²¨®³õ¡A12:39®ÉÂ÷¶}¡A»Ý¥I¨â¤p®É¦¬¶O¡A§Y18x2¡A¦@36¤¸!

TOP

¦^´_ 3# lesaux001

=SUMPRODUCT(--TEXT(ROUNDUP(FREQUENCY(TEXT(C3,"0!:00")*24*60+ROW(INDIRECT("1:"&INT((B5+TEXT(C5,"0!:00") - (B3+TEXT(C3,"0!:00")))*24*60))),"8:00"*24*60+(ROW(1:9)-1)*12*60)/60,0)*18,"[>100]!1!0!0;0"))

ROW(1:9) ±±¨î°±¨®³Ìªø¤Ñ¼Æ4.5¤Ñ¡A­Y³Ì¦h30¤Ñ®É´N§ï¬°ROW(1:60)


¤½¦¡¤¤ ROUNDUP(..../60, 0) ´N¬O±N¤£¨¬¤@¤p®É¼W¬°¤@¤p®É¡C
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

§Ú±N«YµL±ø¤½¦¡¶K©óD4®æ¤º¡A¦ý¥¼¦¨¥\¡A¥X²{value¡A½ÐÀ°À°¦£

TOP

¦^´_ 6# lesaux001

°±¨®¶O¥Î­pºâ.rar (10.11 KB)

¤U¸üÀɮ׬ݬÝ

¥i¯à¬O¯uªºÀɮ׮榡»P§Aµ¹ªº½d¨ÒÀɮ׮榡¤£¦P§a


½Ð¥Î«ö ¦^ÂСA¤~¯à§Ö³t³qª¾§Ú
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

·í§Ú¥´¶}®É¡A§Ú¨£¨ì136¤¸¡A¦Ó§Ú¥»¨­¥Îoffice2013¡A·í§Ú«ö±Ò¥Î½s¿è®É¡A¥»¨­136¤¸(§YD4)¦ì¸m¡A¥ß§YÅܦ¨"value"·Ð½Ð½ç±Ð!

TOP

¦^´_ 6# ML089
·í§Ú¥´¶}®É¡A§Ú¨£¨ì136¤¸¡A¦Ó§Ú¥»¨­¥Îoffice2013¡A·í§Ú«ö±Ò¥Î½s¿è®É¡A¥»¨­136¤¸(§YD4)¦ì¸m¡A¥ß§YÅܦ¨"value"·Ð½Ð½ç±Ð!

TOP

¦^´_ 6# ML089
§A¦n¡A§Ú§ï¥Îwindows office 20007¡A³£¬O¥X²{"value"¦r²´¡A·Ð½ÐÀ°¦£

TOP

¦^´_ 9# lesaux001

§ÚµLªk§PÂ_§Aªº°ÝÃD¬O¤°»ò?

§A¥ý¿é¤J¤é´Á©Î®É¶¡´ú¸Õ¡A¤½¦¡ªº¥¿½T©Ê¦p¦ó¡C
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

        ÀR«ä¦Û¦b : ¨ü¤HÂI¤ô¤§®¦¡A¶··í´é¬u¥H³ø¡C
ªð¦^¦Cªí ¤W¤@¥DÃD