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

[µo°Ý] formulaªº°ÝÃD

[µo°Ý] formulaªº°ÝÃD

¥»©«³Ì«á¥Ñ twosix ©ó 2014-4-22 16:09 ½s¿è

·Q½Ð°Ý¦U¦ì¤j¤j
§Ú¦b¨Ï¥Îfoumula³o­Ó¨ç¼Æ®É
Worksheets("GR912").Range("R2").Formula = "=ROUND((IF(OR($B2="5J",$B2="5Q",$B2="5N"),1,($G2/VLOOKUP($B2,³æ¦ì«OÃB!$B:$C,2,FALSE)))*VLOOKUP($C2,GR910_5A_10303!$H:$P,9,FALSE))*($Q2-$P2+1)/365,0)"

¹³³o­Ó¦³OR($B2="5J",$B2="5Q",$B2="5N")
¨ä¤¤"5J"·|¥X²{  ½sĶ¿ù»~  ¥²¶·¬O::³¯­z¦¡µ²§À

½Ð°Ý¹J¨ìformulaªº¦¡¤l¸Ì­±¦³""ªº°ÝÃD­n«ç»ò¸Ñ¨Mªü?

¦^´_ 1# twosix
¿ý»s¥¨¶°,±N§A©Ò­nªº¤½¦¡¼g¦bÀx¦s®æ¤W,¦A¨ì VBA µøµ¡¤¤¬Ýµ{¦¡½X¬O¦p¦ó¼gªº.
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¥»©«³Ì«á¥Ñ owen06 ©ó 2014-4-22 20:52 ½s¿è

¦^´_ 1# twosix


    ¦b""¥~­±¦A¥]¤@¼h""¡A¨Ò¦p""5j""¡F""5Q""¡F""5N""
    = "=OR($B2=""5J"",$B2=""5Q"",$B2=""5N"")"

  ³o¬O¼Ó¤Wª©¥D±Ð§Úªº^^

TOP

¥»©«³Ì«á¥Ñ yen956 ©ó 2014-4-22 21:09 ½s¿è

¦^´_ 1# twosix
¥ýÁn©ú¡G§Ú¹ï¤½¦¡¤]«Ü­¯¥Í,
¤£¹L§Aªº°ÝÃD§Ú¹J¹L,

§Aªº¤½¦¡§ï¬°
Worksheets("GR912").Range("R2").Formula = "=ROUND((IF(OR($B2=""5J"",$B2=""5Q"",$B2=""5N""),1,($G2/VLOOKUP($B2,³æ¦ì«OÃB!$B:$C,2,FALSE)))*VLOOKUP($C2,GR910_5A_10303!$H:$P,9,FALSE))*($Q2-$P2+1)/365,0)"
¸Õ¸Õ¬Ý!!
(¤½¦¡¦p¦ó¹B§@, ¤£­n°Ý§Ú, ¤Ó½ÆÂø¤F, ¬Ý¤£À´)

TOP

ÁÂÁ¤j®a¦b¥~­±¦A¥[­Ó""´N¥i¥H¶]¤F

TOP

¦A½Ð°Ý¤@­Óformulaªº°ÝÃD
    III = 100
    Do
    III = III - 1
    Loop While Cells(III, 1) = ""
    Jud_num = III (°²³]Jud_num =12)
    Worksheets("GR910").Range("Q4").Formula = "=sum($A$1 : $A$ Jud_num  )"

°²³]§Ú­n¦bformulaªº¤½¦¡¸Ì­±
©ñ¸mªº¬O¤@­ÓÅܰʪºJud_num  ¼Æ¦r
­n«ç»òÅý¥¦Åܦ¨
Worksheets("GR910").Range("Q4").Formula = "=sum($A$1 : $A$12  )"

TOP

¦^´_ 6# twosix


  Worksheets("GR910").Range("Q4").Formula =  "=sum($A$1 : $A$" & Jud_num & ")"

TOP

¦^´_ 7# owen06

·|¥X²{
°õ¦æ¶¥¬q¿ù»~1004 À³¥Îµ{¦¡©Îª«¥ó©w¸q¤Wªº¿ù»~
³o¼Ëªº°T®§­CXDD

TOP

¦^´_ 8# twosix


    ¤½¦¡¼gªk¥»¨­¬O¨S°ÝÃDªº¡A
    ¿ù»~1004¦]¬°¬O§Aªº
     
III = 100
    Do
    III = III - 1
    Loop While Cells(III, 1) = ""
    Jud_num = III

   ¥L¤@ª½loop¤U¥h¡A³Ì«áIII·|µ¥©ó0¡A¥i¬O¨Ã¨S¦³CELLS(0,1)³oºØªF¦è¡A
   ·íµM·|µo¥Í¿ù»~Åo~
   ¦pªG¥X²{¤£©úªº¿ù»~¡A§A¥i¥H¸ÕµÛ«öF8¤@¨B°õ¦æ¡A§âIIIªº­È§ï¤p¤@ÂI(¨Ò¦p10)¡A
   ´N¥i¥H«Ü§Öªº§ä¥X¿ù»~ªº­ì¦]¤F¡C

TOP

¦^´_ 8# twosix


    §A·Q­n°µªº¬OÃþ¦ü³o¼Ëªº·§©À¶Ü¡H

Book1.rar (6.05 KB)

TOP

        ÀR«ä¦Û¦b : ¤Hªº²´·úªø¦b«e­±¡A¥u¬Ý¨ì§O¤Hªº¯ÊÂI¡Aµ·²@¬Ý¤£¨ì¦Û¤vªº¯ÊÂI¡C
ªð¦^¦Cªí ¤W¤@¥DÃD