¥»©«³Ì«á¥Ñ yen956 ©ó 2014-3-16 10:15 ½s¿è
¬°¤°»ò ¤½¦¡(¨ç¼Æ) ¤£¯à¦Û°Êpºâ?
¦p¹Ï,
§Ú¥Î VBA ¦b AH1, AI1, ..., ¿é¤J¨ç¼Æ,
=SUM($AH$3:$AH$200)
¬°¦ó¤£¯à¤£¯à¦Û°ÊpºâÁ`©M?
Ó§O ÂùÂI, ¦A«ö Enter ¤~·|Ó§O§ó·s?
©ÎªÌ «ö ¦sÀɶs, «h¥þ³¡ §ó·s?
¬O¤£¬O¤¤¬r? ¥ÎAVG¤]¬d¤£¥X¨Ó.
vba code ¦p¤U:- Sub «Ø¥ß¤é´Á¤ñ¹ïªí()
- Dim i As Integer
- Sheets("Sheet1").Activate
-
- '®Ú¾Ú [B3], ¶ñ¤J³sÄò12Ӥ몺 µ²ºâ¤é´Á ¨ì ÄæZ
- '¨Ã¶ñ¤J³sÄò12Ӥ몺 ¦~¤ë ¨ì ÄæY, ¨Ñ [Y2] Match ¦~¤ë ¥Î
- '¦]¬°¬Y¨Ç¤ë¥÷¶W¹L1µ§, ¥B¦P¤ë¥÷¶·¼g¦b¦P¤@¦C, ¬G¥Î MATCH ¤ñ¹ï¬O§_¦P¤@¤ë¥÷ªº¸ê®Æ
- For i = 1 To 12
- Cells(i + 2, 26) = DateSerial(Year([B3]), Month([B3]) + i, 1) - 1
- Cells(i + 2, 25) = Year(Cells(i + 2, 26)) - 1911 & Month(Cells(i + 2, 26))
- Next i
- End Sub
- Sub Exyen()
- Dim Rng, chkRng As Range
- Dim i, endRow, blankRow, oldRow As Integer
-
- «Ø¥ß¤é´Á¤ñ¹ïªí
-
- Sheets("Sheet1").Activate
- [M3] = 0.0254 '¦¹¦C "É¥Î" M$3¬OÓÅܼƨëD©T©wÈ
- endRow = [A2000].End(xlUp).Row
- blankRow = 3
- oldRow = 3
-
- '¥ý¨M±ø¥ó¡GÄæA ¤§ ½s¸¹ = 1,2,3... ±Æ¦C, §_«h¥» VBA µLªk¥¿±`¹BÂà
- For i = 3 To endRow
-
- 'Y Cells(2, i + 31)<>"", «h³oµ§¸ê®Æ¤vµ²ºâ¹L, ´«¤U¤@µ§
- If Cells(2, i + 31) <> "" Then GoTo next1:
-
- '§_«h±N ½s¸¹ ¼g¤J Cells(2, i + 31)
- Cells(2, i + 31) = i - 2
-
- '±N ¥Ø«e³o¤@µ§ ÄæB ¤§ ¦~¤ë©ñ¤J [Y1], ¨Ñ [Y2] Match ¤ñ¹ï¦~¤ë ¥Î
- [Y1] = Year(Cells(i, 2)) - 1911 & Month(Cells(i, 2))
-
- '¦]¬°¬Y¨Ç¤ë¥÷¶W¹L1µ§, ¥B¦P¤ë¥÷¶·¼g¦b¦P¤@¦C, ¬G¥Î MATCH ¤ñ¹ï¬O§_¦P¤@¤ë¥÷ªº¸ê®Æ
- [Y2] = "=MATCH(Y1,Y3:Y200,0)"
-
- blankRow = [Y2] + 2
-
- '
- If blankRow <> oldRow Then
- Range(Cells(oldRow, 34), Cells(oldRow, i + 30)).Copy
- Cells(blankRow, 34).PasteSpecial xlPasteValues
- End If
-
- Cells(blankRow, i + 31) = "=ROUND($F$" & i & "*$M$3,2)"
- Cells(1, i + 31) = "=SUM(R3C" & i + 31 & ":R200C" & i + 31 & ")"
- oldRow = blankRow
- next1:
- Next
- End Sub
½Æ»s¥N½X |