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

[µo°Ý] ¸ê®Æ¤À©î°ÝÃD¡C

[µo°Ý] ¸ê®Æ¤À©î°ÝÃD¡C

¥»©«³Ì«á¥Ñ stephenlee ©ó 2021-1-23 11:34 ½s¿è

½Ð°Ý¦U¦ì°ª¤â,¦p¦ó¥ÎVBA °µ¥H¤U­n¨D,«D±`·PÁÂ

A¦ÜF ¬O¸ê®Æ¨Ó·½, H¦ÜN Äæ¬O§Ú°µ¨Ò¤l»¡©úªº¸ê®Æ, ¥­®É¤£·|¦s¦b¦bExcel ¤º, ¥u¦³AÄæ¦ÜFÄæ¸ê®Æ¦Ó¤w, ¦P®É¸ê®Æ¦³®É¦³¦h¦³¤Ö,AÄæ ¬°ªÅ¥Õ¡C
·í¤¤BÄæ©MFÄæ§Ú­n¹ïÀ³¶i¦æ¤À©î¼Æ¶q,¨Ò¦p¦pªG¸Ó¦æªºITEM ¬°SC1 , «h DÄæQTY ¨º­n¤À©î¼Æ¶q¬° ­n¨Dªº¼Æ¶q, ¨Ò¦pIIÄæ¹ïÀ³ªº©î¼Æ­n¨D¬°900
¨ºD2 «K­n¤À©î900¥X¨Ó,°£¤FQTY­n¤À©î¼Æ¶q¥~,¨ä¥L¸ê®Æ¤£ÅÜ.¦ýD2¦]¬°¦h©ó900, ©Ò¥H·s¼W¦æ¼Æ¥h¤À©î¼Æ¶q¥X¨Ó,¥t¥~¤]­n¹ïÀ³ ¤ÀÃþ KÄ檺¸ê®Æ, ¦pªG FÄ檺¤ÀÃþ¬O3ªº¸Ü
­n¦Û°ÊÀ´¥Î§Ú¨Ò¤l©Ò¥Ü,À´¤À¤T­Ó²Õ¦X¥h¦X¦¨DÄæQTYªº¼Æ¶q

¨Ò¦p F2= 3  ­Ó¤ÀÃþ  §Y¬O = 3­Ó²Õ¦X ¨C­Ó²Õ¦X­n¤À©î²Õ¦X¦¨D2 ªº¼Æ¶q

·í¤À©î¼Æ¶q«á,¦Û°Ê¦bA Äæ "Page" ¶ñ¤W 1,2 , ©ÎªÌ 3,4 , ©ÎªÌ5 ©ÎªÌSupport³o¼Ë¡C


¸ê®Æ¨Ó·½¤£ÅÜ, ·í§Ú°õ¦æVBA «á,¥L·|±N¹Bºâµ²ªG¥H·s¼W¤@±i¤u§@ªíÅã¥Üµ²ªG,¨Ò¦pSheet2 ³o¼Ë¡C

¨C¦¸·í§Ú±N¸ê®Æ½Æ»s¦ÜSheet1 ªºAÄæ¦ÜFÄæ«á, °õ¦æVBA «h¨C¦¸³£·s¼W¤@±i¤u§@ªíÅã¥Üµ²ªG¥X¨Ó¡C


°õ¦æVBA«á¥H¶K¤W­Èªº¤èªk·s¼W¤u§@ªí,«h¤£·|±N¸ê®Æ¨Ó·½ªº®æ¦¡³£½Æ»s¹L¥h,¨Ò¦p¦r«¬¤Î¦rÅé¤j¤p³£¤£­n¸ò¹L¥h¡C



¸ê®Æ¨Ó·½:




°õ¦æ«áªºµ²ªG:




¤ÀÃþ­n¨D.zip (9.12 KB)

Sub TEST_A01()
Dim Arr, Brr, i&, j%, k%, X%, V1&, V2&, N&
Arr = Sheets("Sheet1").UsedRange
ReDim Brr(1 To 20000, 1 To 6)
For X = 1 To 6: Brr(1, X) = Arr(1, X): Next
For i = 2 To UBound(Arr)
    V1 = Int((Arr(i, 4) - 1) / Arr(i, 9))
    V2 = Arr(i, 4) - V1 * Arr(i, 9)
    For j = 12 To UBound(Arr, 2)
        If Arr(i, j) = "" Then GoTo j01
        For k = 1 To V1 + 1
            N = N + 1
            For X = 2 To 6: Brr(N + 1, X) = Arr(i, X): Next
            Brr(N + 1, 1) = Arr(i, j)
            Brr(N + 1, 4) = IIf(k > V1, V2, Arr(i, 9))
        Next k
j01: Next j
Next i
With Sheets.Add
     .[A1].Resize(N + 1, 6) = Brr
     .Name = Format(Now, "yyyymmdd-hhmmss")
End With
End Sub

Xl0000051.rar (14.89 KB)


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

TOP

¥»©«³Ì«á¥Ñ °a¤ªºµ ©ó 2021-1-24 18:56 ½s¿è

¦^´_ 1# stephenlee
°Ñ¦Ò¤F ·Ç¤jªº¼gªk  ¶¶«K½m²ß ¦³ªÅªº¸Ü¤]¶¶«KÀ°§Ú¬Ý¬Ý ¬O§_¥¿±` ·PÁÂ
  1. Public Sub ¸ê®Æ¤À©î½m²ß()
  2. Application.ScreenUpdating = False
  3. Arr = [A1].CurrentRegion
  4. Ar = [{900, "1,2", "3,4", "5"; 900, "1,2", "3,4","-"; 1800, "Support","-","-"}]

  5. Sheets.Add(After:=Sheets(1)).Name = "µ²ªG" & Format(Now, "-YYYY-MM-DD")
  6. For Y = 1 To UBound(Arr, 2): Cells(1, Y) = Arr(1, Y): Next Y
  7. For X = 1 To UBound(Ar)
  8.     A = Int(Arr(2 + k, 4) / Ar(X, 1)) + 1
  9.     C = Arr(2 + k, 4) - (A - 1) * Ar(X, 1)
  10.     For i = 2 To UBound(Ar, 2)
  11.         If Ar(X, i) <> "-" Then
  12.         For Y = 1 To A
  13.             If u < A Then u = u + 1
  14.             E = IIf(u = A, C, Ar(X, 1))
  15.             Cells(2 + G, 1) = Ar(X, i)
  16.             Cells(2 + G, 2) = Arr(2 + k, 2)
  17.             Cells(2 + G, 3) = Arr(2 + k, 3)
  18.             Cells(2 + G, 4) = E
  19.             Cells(2 + G, 5) = Arr(2 + k, 5)
  20.             Cells(2 + G, 6) = Arr(2 + k, 6)
  21.             G = G + 1
  22.         Next Y
  23.         End If
  24.     u = 0
  25.     Next i
  26. k = k + 1
  27. Next X
  28. Application.ScreenUpdating = True
  29. End Sub
½Æ»s¥N½X

TOP

Sub TEST_A01()
Dim Arr, Brr, i&, j%, k%, X%, V1&, V2&, N&
Arr = Sheets("Sheet1").UsedRange
ReDim  ...
­ã´£³¡ªL µoªí©ó 2021-1-23 17:38



   ÁÂÁ­ã¤jÀ°¦£,¸g´ú¸Õ¬O§¹¥þOKªº¡C

¤£¹L¤£¦n·N«ä.§Ú§Ñ¤F»¡ H¨ìN¨º, ªº²Õ¦X¤£¬O©T©wªº

¨Ò¦p  SC1 ©î¼Æ¤@©w¬O 900  , SC2 ©î¼Æ¤@©w¬O900,SC3 ©î¼Æ ¤@©w¬O 1800, ¦ý¥Lªº²Õ¦X¤£¬O SC1 Âê©w¤F ¤ÀÃþ ²Ä 3 , ¦ÓSC2 ¤£¬OÂê©w¤F¬O²Ä2, ¤£¹LSC3 ´N¥²©w¬O1 ¡C

¨Ò¦p SC1 ªº¤ÀÃþ¥i¥H¥h 2 , ´N¬O 1,2 ©M 3.4 ¡C ¦ÓSC2 ªº¤ÀÃþ¥i¥H¥h 3, ´N¬O 1,2 + 3,4 +5


¦ÓSC3 «h¤@ª½¤£ÅÜ¡C

¦ÓSC2©MSC3 ¥¼¥²¨C¦¸³£¦³¸ê®Æ, ©Ò¥H¸ò©î¼Æ«á¤]­n¸ò¤ÀÃþ¨Ó·s¼W¦æ¼Æ¡C

¦Ó°µªk³£¬O¸ò¤§«e­n¨D¤@¼Ë,¶ñ¤W¸ê®Æ«á,®Ú¾Ú¼Æ¶q¤Î¤ÀÃþ°õ¦æ¦A¥H·s¤@­Ó¤u§@ªíÅã¥Ü¡C ¥u¤£¹L¬O«ö·Ó¨C¦¸©Ò¶ñ¤WªºItem ©M¤ÀÃþ¨Ó§PÂ_°µ¾ã²z¡C

¯S§O¤£¦n·N«ä,¦A³Â·Ð§A¡C «D±`·P¿E§Aªº²Ó¤ßÀ°¦£¡CÁÂÁÂ


¤ÀÃþ­n¨D §ó·s.zip (10.78 KB)

TOP

¦^´_  stephenlee
°Ñ¦Ò¤F ·Ç¤jªº¼gªk  ¶¶«K½m²ß ¦³ªÅªº¸Ü¤]¶¶«KÀ°§Ú¬Ý¬Ý ¬O§_¥¿±` ·PÁÂ
°a¤ªºµ µoªí©ó 2021-1-24 18:54



    ÁÂÁ¤j¤j²Ó¤ßÀ°¦£,«D±`·PÁÂ, ¸g´ú¸Õ³£¬Ookªº,¤£¹L§Ú»Ý­n¦A§ó·s¤º®e,©Ò¥H´N¤£¦n·N«ä¦A³Â·Ð§A¤F¡CÁÂÁÂ

TOP

¦^´_ 4# stephenlee


item ¥u¦³³o¤TºØ???
¦Ó¤ÀÃþ³Ì¦h¬O3???

¨ä¹ê¨Æ¥ý¦bH:N¿é¤J­n¤À©îªº¸ê®Æ´N¥i¥H? ¨S¦³ªº¸ê®Æ¥¦¤]¤£·|¶]¥X¨Ó

TOP

¦^´_ 4# stephenlee

°Ñ¦Ò¬Ý¬Ý:
Xl0000051-02.rar (16.26 KB)

TOP

¦^´_  stephenlee


item ¥u¦³³o¤TºØ???
¦Ó¤ÀÃþ³Ì¦h¬O3???

¨ä¹ê¨Æ¥ý¦bH:N¿é¤J­n¤À©îªº¸ê®Æ´N¥i¥H?  ...
­ã´£³¡ªL µoªí©ó 2021-1-26 14:45



    ¬Oªº,¥u¦³¤T­ÓITEM, ¦A¦¸·PÁ­ã¤j,¡C

TOP

¦^´_  stephenlee

°Ñ¦Ò¬Ý¬Ý:
­ã´£³¡ªL µoªí©ó 2021-1-26 15:11



    §¹¥þ²Å¦X­n¨D¤º®e,¤£¦n·N«ä¤@ª½³Â·Ð§A¡C
«D±`·PÁ»դU¤@ª½À°¦£,¦Ó¥B«D±`§Ö¦aÀ°¤F§Ú¸Ñ¨M°ÝÃD,À°§Ú¸`¬Ù¤F«Ü¦h®É¶¡¡C¦A¦¸·PÁ¡C

TOP

¦^´_ 5# stephenlee

¤£ ¬O§Ú¦b³Â·Ð§A  ¬O§Ú­É§AªºÃD¥Ø¨Ó¾Ç²ß ®ö¶O¤F§AÄ_¶Qªº®É¶¡ §Ú·P¨ì©êºp ÁÂÁ§AÀ°§Ú´ú¸Õ ·P®¦

TOP

        ÀR«ä¦Û¦b : ¤Ñ¤W³Ì¬ü¬O¬P¬P¡A¤H¥Í³Ì¬ü¬O·Å±¡¡C
ªð¦^¦Cªí ¤W¤@¥DÃD