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

§xÂZªº EXCEL ( IF)

¦^´_ 10# luhpro


    §Ú¤w¸g ¦n®ð¾k~~  «¢«¢

·PÁ´£¿ô  ³æ¯ÂKEY ¼Æ¦r ¬O¹ïªº

01.png (88.01 KB)

01.png

02.png (110.44 KB)

02.png

03.png (102.33 KB)

03.png

Cyssuc

TOP

¦^´_ 11# Cyssuc
­º¥ý, ¥Î¨Ó°µ­pºâªº¦¡¤l¤¸¯À,¤£«Øij¤å¦r¦Ó³Ì¦n¬Oµ¹¼Æ­È,
«Øij§A±Ä¥Î =IF(TIME(16,0,0)<G96,17,...¦Ó«D =IF(TIME(16,0,0)<G96,"17",...
¦]¬°¨t²Î±N¤å¦r¦Û°ÊÂà´«¦¨¼Æ­Èªº°Ê§@¦³¥i¯à¦]¬°¯BÂIºë«×¦Ó²£¥Í»~®t.

¨ä¦¸Æ[¹î§Aµ¹ªº¤½¦¡ :
Q96ªº¦¡¤l ±q 18.5 ª½±µ¸õ 17, 13.5 ª½±µ¸õ 12  ½T¹ê¬O§A·Q­nªºµ²ªG¶Ü?
ÁÙ¬O¥´¿ù¤F?
­Y¬Oµ¥¶Z­pºâ¥i¥H¥Î =IF(G96<TIME(8,30,0),0,HOUR(G96)+IF(MINUTE(G96)<30,0.5,1)) ¨Ó§¹¦¨.
­Y¸Ó¦¡µL»~«h¥i§ï¥Î:
=IF(OR(G96>=TIME(18,30,0),G96<TIME(16,30,0)),HOUR(G96)+IF(MINUTE(G96)<30,0.5,1),IF(G96>=TIME(17,20,0),17,16.5))
¸Õ¸Õ¬Ý.

TOP

¦^´_ 11# Cyssuc

§Aªº¸ê®Æ¬O¤å¦r®æ¦¡¡A·íµM·|¿ù»~

¤½¦¡­×§ï¦p¤U

¦­¤W 8~12¡A¤U¤È 13~17

= ¦­¤W®É¼Æ + ¤U¤È®É¼Æ
= MAX(0,MIN(12,Q2*1)-MAX(8,P2*1)) + MAX(0,MIN(17,Q2*1)-MAX(13,P2*1))

©Îª½±µ¥Î¨Ó·½
= MAX(0,MIN(12/24,G2)-MAX(8/24,E2)) + MAX(0,MIN(17/24,G2)-MAX(13/24,E2))
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

        ÀR«ä¦Û¦b : ¡i¥Í©R¦b©I§l¶¡¡j¦òªû»¡¡G¡u¥Í©R¦b©I§l¶¡¡C¡v¤HµLªkºÞ¦í¦Û¤vªº¥Í©R¡A§óµLªk¾×¦í¦º´Á¡AÅý¦Û¤v¥Ã¦í¤H¶¡¡C¬JµM¥Í©R¥h¨Ó³o»òµL±`¡A§Ú­Ì§óÀ³¸Ó¦n¦n¦a·R±¤¥¦¡B§Q¥Î¥¦¡B¥R¹ê¥¦¡AÅý³oµL±`¡BÄ_¶Qªº¥Í©R¡A´²µo¥¦¯uµ½¬üªº¥ú½÷¡A¬M·Ó¥X¥Í©R¯u¥¿ªº»ù­È¡C
ªð¦^¦Cªí ¤W¤@¥DÃD