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

[µo°Ý] ¼Æ¶q¤À©î¹j¦æ°ÝÃD¡C

[µo°Ý] ¼Æ¶q¤À©î¹j¦æ°ÝÃD¡C

¤u§@ªí1¬O§Ú±q¥t¥~¤@­Ó¤u§@ªí ½Æ»s±o¨Óªº¡C
­n¥Î³o­Ó¤u§@ªí»Pword °µ¶l¥ó¦X¨Ö, ¦ý§Ú»Ý±NQty³B ©î¤À ¬° 3000¤@¦æ¡C
¦]¥Î¶l¥ó¦X¨Ö«á,¥´¦L¥X¨Ó, ¶K¦b¬ÛÃö³fª«¤W, ±N¤j©ó3000ªº¼Æ¶q°£3000, §À¼Æ³Ì¤j­n¦b100¤º¡C

§Y¬O§À¼Æ³Ì¤j3100 ¤º ·í¤@¦æ, ¦h©ó3100 «h¤À©î¦h¤@¦æ¡C ¦P®É¦]word ¤º¦³¤@­ÓA4 SIZEªº ¶l¥ó¦X¨Ö¡C
¤@±iA4 ¯È, ¦³¥|­Ó¤p¥|¤è®æ¡C

©Ò¥H©î¼Æ«á,¦P®É¦A¤À Name, ¬° 4¦æ¤@±iA4¯È¡C¤£¨¬4¦æªº,¤]­n¤À©î¦æ¼Æ¥X¨Ó¡C
¦]¬°Name ¬O¤£¦P³fª«,¤£¯à »P¤§«eªº¸ê®Æ¦X¨Ö¡C§_«h·|¶Ã¡C

¥»¨­¤w¸g¦³VBA ¸ê®Æ,·í§ÚÂIÀ»«ö¶s«á(VBA),·|¦Û°Ê·s¼W¤@­¶¦P®É°õ¦æ¥H¤W­n¨D°Ê§@¡C ¨Ò¦p¤u§@ªí2

¦]¬°¤w¸g¬OÂê©w¤F3000, ¤Î§À¼Æ³Ì¤j3100¡C

¨º¦pªG§Ú¼W¥[¤@Äæ¬O­n¨Ì·Ó HÄæ,Color ¨Ó°µ©î¼Æ¡C  (¤u§@ªí3)

¦pªGHÄ欰"RED" ªº¸Ü, «h©î1000¡C §À¼Æ³Ì¤j:1100¡C

¦pªGHÄ欰"YELLOW" ªº¸Ü,«h©î3000, §À¼Æ³Ì¤j:3100¡C


¤@±i¤u§@ªí¥u¦s¦b ¨âºØ  Color, ¦ý¬O¤£¤@©w¹³¤u§@ªí3 ¨º¼Ë, ¦³¾÷·|²Ä20-25¦C¬ORED.
26-30 ¤S¬OYELLOW, ³o¼Ë, ¦ÓGROUP1 ©M GROUP2 ¬O¤@©w»Pcolor ¦¨¤@²Õªº¡C

²Ä¤G¦Ü²Ä¤C¦C ¬ORED °tGROUP1
²Ä¤K¦Ü²Ä¤Q¤E ¬OYELLOW °tGROUP2

¤£·|¦s¦bGROUP1 ¤¤¦s¦b¦³2­Ó clolor §Y¬O¤£·|1¦³RED ©MYELLOW ¦P®É¦s¦b¡C




¦p¦ó¨Ì·Ó ¤u§@ªí3 ¸òColor ¨Ó°µ©î¼Æ¤Î¤À¦æ?  (¤u§@ªí4)

¦]¸ê®Æ¦³¦h¦³¤Ö, ¦Ó¥B¤£¤î GROUP1
question.zip (27.45 KB)

Sub ¤À©î¼Æ¶q()
Dim Arr, Brr, i&, j%, k%, C&, Qty&, U&, V&, N&
Arr = Range([I1], [A65536].End(xlUp))
ReDim Brr(1 To 30000, 1 To 9)
For i = 2 To UBound(Arr)
    If i > 2 And Arr(i, 9) <> Arr(i - 1, 9) Then N = N + 1
    C = 3000: If Arr(i, 8) = "RED" Then C = 1000
    Qty = Arr(i, 4): U = 0
    For j = 1 To Int((Qty - 1) / C) + 1
        N = N + 1
        For k = 1 To 9: Brr(N, k) = Arr(i, k): Next
        Brr(N, 5) = U + 1
        V = IIf(Qty > C, C, Qty)
        U = U + V
        Qty = Qty - V
        Brr(N, 6) = U + IIf(Qty > 100, 0, Qty)
        Brr(N, 7) = V + IIf(Qty > 100, 0, Qty)
        If Qty <= 100 Then Exit For
    Next j
Next i
Sheets(1).[A1:I1] = Arr
Sheets(1).[A2:I3].Resize(N) = Brr
End Sub


====================================

TOP

¥»©«³Ì«á¥Ñ hcm19522 ©ó 2020-7-18 16:14 ½s¿è

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

½Ð±Ð "­ã¤j"  §ä«ä¸ô
¼Æ¾Ú¥u¦³A2  B2:B21 §¡¤£©T©w ,D F¦]¤½¦¡¤Óªø ,¨Dºë²
https://blog.xuite.net/hcm19522/twblog/589232991
ÀH·NºÛ "EXCEL°g"  blog  ©Îhttps://hcm19522.blogspot.com/ EXCEL¨ç¼Æ

TOP

¦^´_ 3# hcm19522


RANDBETWEEN §Úªºª©¥»(2000)µLªkºc¦¨°}¦C, ©Ò¥H, ³oºØÃD§Ú¤j³£¤£µª,
¹ê»Ú¹B¥Î, ÁÙ¬O¥Îrand°µ»²§U§a! ¤£·Q¦b¤é±`¥Î¤£¨ìªº¤½¦¡¤W°µ¤å³¹~~

TOP

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


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

TOP

        ÀR«ä¦Û¦b : ­n¥Î¤ß¡A¤£­n¾Þ¤ß¡B·Ð¤ß¡C
ªð¦^¦Cªí ¤W¤@¥DÃD