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

[µo°Ý] Excel ¤À©î¼Æ¶q¡C

[µo°Ý] Excel ¤À©î¼Æ¶q¡C

Sheet1 ¬O¸ê®Æ¨Ó·½

FÄæ¤ÎGÄæ ¼Æ¶q¤@¶}©l¬O¤@¼Ëªº¡C

¦ý§Ú­n®Ú¾Ú J Äæ ¤ÀÃþ¨Ó¶i¦æ¼Æ¶q¤À©î

¥ÎGÄæ¨Ó¤À©î¼Æ¶q¡C

JÄæ ¥u¦³¨â­Ó©T©w

1­Ó¬O ST ©ÎªÌ HT  ¨S¦³²Ä¤T­Ó


Àɮצ³¾÷·|JÄæ¥þ¬O ST ©ÎªÌHT, ©ÎªÌ¨âªÌ¤@°_¦s¦b¡C

¦ÓST ¤À©î¼Æ¶q ­n¬° 3000 §À¼Æ­n100¤º¡C

¨Ò¦p

5412  ­n¤À©î¥H3000 ¬°°ò¼Æ

©Ò¥H­n¦A·s¼W¤@¦C

GÄæ«h¬° 3000 ¤@¦C©M2412¤@¦C¡C

FÄæ¬O¯dµ¹§Ú¬Ý¨ì©³¦³¦h¤Ö¼Æ¶q¡C

¦pªGJÄ欰 ST, ¼Æ¶q¬° 3100  ¨º´N¤£­n·s¼W¤@¦C ©î¼Æ¡C

¦ý¦pªG¬O 3101 §À¼Æ ¤j©ó100, «h­n©î 3000 ¤@¦C, 101¤@¦C¡C


¦pªGJÄæ¬OHT ¤]¬O, HT ­n¤À©î¥H1000 ¬°°ò¼Æ,¤]¬O100¤º §À¼Æ¡C



1200 ©î 1000 ¤Î 200


¤À©î§¹¼Æ¶q«á,«h¦A«ö·ÓHÄæ MO# ¨Ó·s¼WªÅ¥Õ¦C¡C

¦pªGHÄæ ±qH2 ¶}©l¶µ¥Ø­Ó¼Æ¬O2ªº­¿¼Æ´N¤£¥Î·s¼W¤@¦C¡C

¦pªG¬O³æ¼Æ´N­n·s¼W¤@¦C¡C

§Ú­n¦bSheet1 ·s¼W¤@­Ó«ö¶s,«Ý§Ú¦bSheet1 ¦³¸ê®Æ«á,ÂIÀ»«ö¶s¥i¥H®Ú¾Ú JÄæ¤ÎGÄæ°µ·s¼WÄæ©î¼Æ,³Ì«á¥HHÄæ­n·s¼WªÅ¥Õ¦C¡C

±o¥Xµ²ªG,¥H·s¼W¤@­Ó¤u§@ªí±o¥X¡C



´N¬O§Ú¦bSheet1ÂIÀ»«ö¶s«á±o¥Xµ²ªG¦b·s¤@­Ó¤u§@ªí¤¤¡C  (ºt¥Üµ²ªG¬°Sheet2)


³Â·Ð¤j®a¡C




¤À©î¼Æ¶q.rar (8.59 KB)

ÀH·NºÛ "EXCEL°g"  blog  ©Îhttps://hcm19522.blogspot.com/ EXCEL¨ç¼Æ

TOP

Sub TEST_A1()
Dim Arr, Brr, i&, j%, k%, Cn%, V%, V1%, V2%, N&, R&
Sheets("Sheet2").[a:j].ClearContents
Arr = Range(Sheets("Sheet1").[j1], Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp)(2))
ReDim Brr(1 To 30000, 1 To 10)
For i = 2 To UBound(Arr) - 1
    V = IIf(Arr(i, 10) = "HT", 1000, 3000) '°£¼Æ
    V1 = Val(Arr(i, 7)) '¼Æ¶q
    Cn = Int(V1 / V) + 1 '¤À©î¦æ¼Æ
    V2 = V1 Mod V '¾l¼Æ
    If V2 < 101 And Cn > 1 Then Cn = Cn - 1: V2 = V + V2
    For j = 1 To Cn
        N = N + 1
        For k = 1 To 10: Brr(N, k) = Arr(i, k): Next
        Brr(N, 7) = IIf(j = Cn, V2, V)
    Next j
    If Arr(i, 8) <> Arr(i + 1, 8) And N Mod 2 = 1 Then N = N + 1
Next i
Sheets("Sheet2").[a1:j1] = Sheets("Sheet1").[a1:j1].Value
Sheets("Sheet2").[a2].Resize(N, 10) = Brr
End Sub

TOP

Sub TEST_A1()
Dim Arr, Brr, i&, j%, k%, Cn%, V%, V1%, V2%, N&, R&
Sheets("Sheet2").[a:j].ClearCont ...
­ã´£³¡ªL µoªí©ó 2021-10-24 16:54



·PÁ­ã¤jÀ°¦£,§¹¥þ²Å¦X­n¨D¡C¦A¦¸·PÁ»դUÀ°¦£¡C ÁÂÁÂ

TOP

¥»©«³Ì«á¥Ñ Andy2483 ©ó 2023-11-14 10:14 ½s¿è

¦^´_ 3# ­ã´£³¡ªL


    ÁÂÁ½׾Â,ÁÂÁ«e½ú«ü¾É
«á¾Ç¾Ç²ß«e½úªº¤è®×,¤ß±oµù¸Ñ¦p¤U,½Ð«e½ú¦A«ü¾É

Option Explicit
Sub TEST_A1()
Dim Arr, Brr, i&, N&, R&, j%, k%, Cn%, V%, V1%, V2%
'¡ô«Å§iÅܼÆ:(Arr,Brr)¬O³q¥Î«¬ÅܼÆ,(N,R)¬Oªø¾ã¼Æ,(j,k,Cn,V,V1,V2)¬Oµu¾ã¼Æ
Sheets("Sheet2").[a:j].ClearContents
'¡ô¥O¦W¬°"Sheet2" ¤u§@ªíªºA:JÄæÀx¦s®æ²M°£¤º®e
Arr = Range(Sheets("Sheet1").[j1], Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp)(2))
'¡ô¥OArr³o³q¥Î«¬ÅܼƬO ¤Gºû°}¦C,¥Hªí1ªº[J1]¨ìAÄæ³Ì«á¦³¤º®eÀx¦s®æªº¤U¤@®æ,¥H¦¹½d³òÀx¦s®æ­È±a¤JArr°}¦C¤¤
ReDim Brr(1 To 30000, 1 To 10)
'¡ô«Å§iBrr³o³q¥Î«¬ÅܼƬO¤GºûªÅ°}¦C,Áa¦V½d³ò±q¯Á¤Þ¸¹1¨ì30000,¾î¦V½d³ò±q¯Á¤Þ¸¹1 ¨ì10
For i = 2 To UBound(Arr) - 1
'¡ô³]¶¶°j°é!i±q2 ¨ìArr°}¦CÁa¦V³Ì¤j¯Á¤Þ¦C¸¹-1
  V = IIf(Arr(i, 10) = "HT", 1000, 3000) '°£¼Æ
    '¡ô¥OV³oµu¾ã¼Æ¬O IIF()¦^¶Ç­È:¦pªGi°j°é¦C²Ä10ÄæArr°}¦C­È¦Û¦r¦ê "HT",True¦^¶Ç¼Æ­È1000,False¦^¶Ç¼Æ­È 3000
    V1 = Val(Arr(i, 7)) '¼Æ¶q
    '¡ô¥OV1³oµu¾ã¼Æ¬O i°j°é¦C²Ä7ÄæArr°}¦C­ÈÂন¼Æ­È
    Cn = Int(V1 / V) + 1 '¤À©î¦æ¼Æ
    '¡ô¥OCn³oµu¾ã¼Æ¬O V1Åܼư£¥HVÅܼƫá¥h°£¤p¼Æªº¾ã¼Æ­È+1
    V2 = V1 Mod V '¾l¼Æ
    '¡ô¥OV2³oµu¾ã¼Æ¬O V1Åܼư£¥HVÅܼƪº¾l¼Æ
    If V2 < 101 And Cn > 1 Then Cn = Cn - 1: V2 = V + V2
    '¡ô¦pªGV2ÅܼÆ<101 ¥BCnÅܼÆ>1 ? True´N¥OCnÅܼÆ-1:¥OV2ÅܼƬO¦Û¨­²Ö¥[VÅܼÆ
    For j = 1 To Cn
    '¡ô³]¶¶°j°é!j±q1 ¨ìCnÅܼÆ
        N = N + 1
        '¡ô¥ONÅܼƲ֥[1
        For k = 1 To 10: Brr(N, k) = Arr(i, k): Next
        '¡ô³]¶¶°j°é!¥Ok±q1¨ì10:±Ni¦CkÅܼÆÄæArr°}¦C­È±a¤J NÅܼƦCkÅܼÆÄæBrr°}¦C¤¤
        Brr(N, 7) = IIf(j = Cn, V2, V)
        '¡ô¥ONÅܼƦC²Ä7Äæ°}¦C­È¬OIIF()¦^¶Ç­È: ¦pªGjÅܼƦPCnÅܼÆ!¦^¶ÇV2ÅܼÆ,§_«h¦^¶ÇVÅܼÆ
    Next j
    If Arr(i, 8) <> Arr(i + 1, 8) And N Mod 2 = 1 Then N = N + 1
    '¡ô¦pªGi°j°é¦C²Ä8ÄæArr°}¦C­È»P¤U¤@¦C²Ä8ÄæArr°}¦C­È¤£¦P,¥BNÅܼư£¥H2ªº¾l¼Æ¬O1?
    'True´N¥ONÅܼƲ֥[1

Next i
Sheets("Sheet2").[a1:j1] = Sheets("Sheet1").[a1:j1].Value
'¡ô¥Oªí2ªº¼ÐÃD¦C¦P ªí1ªº¼ÐÃD¦C
Sheets("Sheet2").[a2].Resize(N, 10) = Brr
'¡ô¥Oªí2ªº[A2]ÂX®i¦V¤UNÅܼƦC,ÂX®i¦V¥k10Ä檺½d³òÀx¦s®æ­È¥HBrr°}¦C­È±a¤J
End Sub
===========================================================

Option Explicit
Sub TEST()
Dim Brr, Crr, V%, V1%, Q%, i&, j%, R&
Dim S1 As Worksheet, S2 As Worksheet
Set S1 = Sheets("Sheet1"): Set S2 = Sheets("Sheet2")
Brr = Range(S1.[J1], S1.Cells(Rows.Count, "A").End(3))
ReDim Crr(1 To 10000, 1 To 10)
For i = 2 To UBound(Brr)
   Q = Val(Brr(i, 7))
   V = IIf(Brr(i, 10) = "HT", 1000, 3000)
qq:
   If Q <= 0 Then GoTo i01 Else R = R + 1: V1 = Q - V
   For j = 1 To 10: Crr(R, j) = Brr(i, j): Next
   Crr(R, 7) = V * -(V1 >= 0) + Q * -(V1 < 0)
   Q = V1: GoTo qq
i01: Next
S2.[A:J].ClearContents
S2.[A1:J1] = S1.[A1:J1].Value
S2.[a2].Resize(R, 10) = Crr
Set S1 = Nothing: Set S2 = Nothing: Erase Brr, Crr
End Sub
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

(¿é¤J½s¸¹12006) googleºô§}:https://hcm19522.blogspot.com/
ÀH·NºÛ "EXCEL°g"  blog  ©Îhttps://hcm19522.blogspot.com/ EXCEL¨ç¼Æ

TOP

        ÀR«ä¦Û¦b : «H¤ß¡B¼Ý¤O¡B«i®ð¤TªÌ¨ã³Æ¡A«h¤Ñ¤U¨S¦³°µ¤£¦¨ªº¨Æ¡C
ªð¦^¦Cªí ¤W¤@¥DÃD