- ©«¤l
- 169
- ¥DÃD
- 41
- ºëµØ
- 0
- ¿n¤À
- 219
- ÂI¦W
- 0
- §@·~¨t²Î
- WIN10
- ³nÅ骩¥»
- office 2007
- ¾\ŪÅv
- 20
- ©Ê§O
- ¨k
- µù¥U®É¶¡
- 2012-7-31
- ³Ì«áµn¿ý
- 2023-9-15
|
¦^´_ 30# sillykin
Option Explicit
Const ºÞ²z³¡ªù = "¼f¬d«Ç¡Bªk°È«Ç¡B·|p«Ç¡BÁ`°È«Ç¡B¦æ¬FºÞ²z«Ç¡B¼x«H«Ç¡B¥ø¹º«Ç¡B±Ð¨|µo®i«Ç¡B¸ê°T«Ç"
Const ¾°È§O = "0,°ÆÁ`¸g²z,¨ó²z,°ª¯Å±Mû,¸g²z,Åv²z¸g²z,°Æ¸g²z,°Æ²z,¤@µ¥Á¸²z,¤Gµ¥Á¸²z,·~°È±Mû,¿ì¨Æû,§U²zû,¶±û,ªA°È¥Í"
Sub Ex()
Dim I As Integer
With Sheets("¡®¡®³æ¦ì¦¨ÁZÁ`ªí¡®¡®").Range("A3")
Do While .Offset(I) <> ""
¼úª÷µ{¦¡ .Offset(I), .Offset(I, 2), .Offset(I, 4)
I = I + 1
Loop
End With
End Sub
Private Sub ¼úª÷µ{¦¡(³æ¦ì As Range, ¾ºÙ As String, ¹F¦¨ÃB As Integer)
' ****(±ø¥ó¤u§@ªí¤¤) ¨S¦³ªºÂ¾ºÙ -> °ª¯Å±Mû, Åv²z¸g²z ***
Dim Ar, ³d¥ôÃB As Integer, ¹F¦¨¦Ê¤À¤ñ As Single, ¼úª÷ As Integer, °ÆÁ`¼úª÷ As Integer
Dim ³¡ªù As String, ¾ºÙ¥N¸¹ As String, M As Integer '
'LTrim¡BRTrim »P Trim ¨ç¼Æ ¶Ç¦^¤@Ó¨S¦³«eÀYªÅ¥Õ (LTrim)¡B«á±ªÅ¥Õ (RTrim)©Î«e«á§¡µLªÅ¥ÕªºVariant (String)¡A¨ä¤¤©Ò§t¬°µ¹©wªº¦r¦ê¡C*
¾ºÙ¥N¸¹ = Trim(³æ¦ì.Offset(, 2)) '¶Ç¦^¾ºÙ¥N¸¹«e«á§¡µLªÅ¥ÕªºVariant (String)
'Split ¨ç¼Æ ¶Ç¦^¤@Ó³¯¦C¯Á¤Þ±q¹s¶}©lªº¤@ºû°}¦C¡A¥¦¥]§t«ü©w¼Æ¥Øªº¤l¦r¦ê¡C
³æ¦ì.Offset(, 7) = Application.Match(¾ºÙ¥N¸¹, Split(¾°È§O, ","), 0) 'Match ¾ºÙ¥N¸¹¦b¾°È§O¥HSplit¤À³Î¥Xªº°}¦C¤¤ªº¦ì¸m
'InStr ¨ç¼Æ ¶Ç¦^¦b¬Y¦r¦ê¤¤¤@¦r¦êªº³Ì¥ý¥X²{¦ì¸m¡A¦¹¦ì¸m¬° Variant (Long)¡C
³¡ªù = IIf(InStr(ºÞ²z³¡ªù, ³æ¦ì), "ºÞ²z³¡ªù", "Àç·~³¡ªù") 'InStr¶Ç¦^0->¨S§ä¨ì="Àç·~³¡ªù"
°ÆÁ`¼úª÷ = 0 '¹w³]¬°°ÆÁ`¼úª÷¬° 0
³d¥ôÃB = IIf(³¡ªù = "ºÞ²z³¡ªù", 300, 500)
If ³¡ªù = "Àç·~³¡ªù" Then
Ar = Array(0, 0, -10000, -7000, -5000, -4000, -3000, -2000, -1000, -500, 800, 75) '«D·F³¡(¼úÃg¼úª÷)
If ³æ¦ì.Offset(, 7) <= 10 Then
Ar = Array(0, 0, -25000, -20000, -16000, -12000, -8000, -6000, -4000, -2000, 2000, 155) '·F³¡ (¼úÃg¼úª÷)
M = Application.Match(¾ºÙ¥N¸¹, Array("°ª¯Å±Mû", "¤Gµ¥Á¸²z", "¤@µ¥Á¸²z", "°Æ¸g²z", "¨ó²z", "¸g²z", "°ÆÁ`¸g²z"), 0)
'M = > Match ªº¼Æ¦r
³d¥ôÃB = Application.Index(Array(500, 1500, 2000, 2200, "(µù1)", "(µù1)", 2500), M) '³d¥ôÃB °}¦C(°ª¯Å±Mû->100)
'Index ¶Ç¦^¤@Óªí®æ©Î½d³ò¤ºªº¬YÓȩΰѷӦì§}¡C
°ÆÁ`¼úª÷ = IIf(¾ºÙ¥N¸¹ = "°ÆÁ`¸g²z", 2000, 0)
End If
Else ' ³¡ªù => "ºÞ²z³¡ªù"
Ar = Array(0, 0, -10000, -7000, -5000, -4000, -3000, -2000, -1000, -500, 500, 55) '«D·F³¡(¼úÃg¼úª÷)
If ³æ¦ì.Offset(, 7) <= 10 Then
Ar = Array(0, 0, -25000, -20000, -16000, -12000, -8000, -6000, -4000, -2000, 1000, 75) '·F³¡ (¼úÃg¼úª÷)
M = Application.Match(¾ºÙ¥N¸¹, Array("°ª¯Å±Mû", "¤Gµ¥Á¸²z", "¤@µ¥Á¸²z", "°Æ¸g²z", "¸g²z"), 0)
'***¨S¦³ªºÂ¾ºÙ,[°ÆÁ`¸g²z],ºÞ²z³æ¦ì¦p¦³°ÆÁ`¸g²z(µ{¦¡°õ¦æ·|²£¥Í¿ù»~)*****************************
³d¥ôÃB = Application.Index(Array(100, 600, 700, 800, 900, 2500), M) '³d¥ôÃB °}¦C(°ª¯Å±Mû->100)
'ºÞ²z³¡ªù¨S¦³°ÆÁ`¸g²z -> ' °ÆÁ`¼úª÷ = IIf(¾ºÙ¥N¸¹ = "°ÆÁ`¸g²z", 2000, 0)
End If
End If
³æ¦ì.Offset(, 3) = ³d¥ôÃB
'*************************************** FÄæ (¹F¦¨¤ñ²v¢H)¥Î¤½¦¡¦³¨â¦¡¥i¥Î*******************************
³æ¦ì.Offset(, 5) = "=Round(RC[-1] / RC[-2] * 100, 0)" 'R1C1ªí¥Üªk
'³æ¦ì.Offset(, 5) = "=Round(" & ³æ¦ì.Offset(, 4).Address & " / " & ³æ¦ì.Offset(, 3).Address & "* 100, 0)"
'*******************************************************************************************************
¹F¦¨¦Ê¤À¤ñ = Round(¹F¦¨ÃB / ³d¥ôÃB * 100, 0)
'³æ¦ì.Offset(, 5) = ¹F¦¨¦Ê¤À¤ñ
¼úª÷ = 0
If ¹F¦¨ÃB / ³d¥ôÃB * 10 < 3 Then
M = 2
ElseIf ¹F¦¨ÃB / ³d¥ôÃB * 10 > 10 Then
M = 10
¼úª÷ = Ar(UBound(Ar))
Else
M = Int(¹F¦¨ÃB / ³d¥ôÃB * 10)
End If
³æ¦ì.Offset(, 6) = Ar(M) + ((¹F¦¨¦Ê¤À¤ñ - 100) * ¼úª÷) + °ÆÁ`¼úª÷ 'Ar(M) = ¼úÃgªºª÷ÃB
'¼úª÷pºâ¤½¦¡: = ¼úÃgªºª÷ÃB+ ((¹F¦¨100%¥H¤W-100)* ¼úª÷) + °ÆÁ`¼úª÷
End Sub
°õ¦æ¥X¨Ó·|µo¥Í¿ù»~ |
-
-
¿ù.JPG
(12.25 KB)
|