¦b¤£©T©w½d³ò¤º§ä¥X¬Û¦P¸ê®Æ

jesscc

¦p¦ó¦b¤£©T©w½d³ò¤º(¦p¹Ï01ªº Z12:AK~¡A¸ê®Æ¦C¼Æ¥ÑRange(.[D12], .[D65536].End(xlUp)¨M©w)
§ä¥X¬Û¦Pªº¼t°Ó¡AµM«á§â¬Û¦Pªº¼t°Ó±Æ¦C¦b¤@°_¡A¸ê®ÆÂন¹³¹Ï02³o¼Ë

¹Ï02"¦¨«~"Äæ¦ìªº¼Æ­È¡A¥Ñ¹Ï01ªº"QTY"Äæx"DEMAND PCS"Äæ

¤U¸ü (44.36 KB)
01

2018-10-18 22:28

¤U¸ü (28.82 KB)
02

2018-10-18 22:28

ACC.rar (4.32 KB)

¤U¸ü¦¸¼Æ: 10

2018-10-18 22:29

hcm19522

A12:B23{=IFERROR(INDIRECT("¤u§@ªí1!"&TEXT(RIGHT(SMALL(IF((MOD(COLUMN($AB12:$AK24),3)=1)*(¤u§@ªí1!$AB$12:$AK$24>0),CODE(¤u§@ªí1!$AB$12:$AK$24)/1%%+ROW(AB$12:AK$24)/1%+COLUMN($AB12:$AK24)-(COLUMN(A1)=2)*2),ROW(A1)),4),"!R0C00"),),"")

G12:G23{=IF(A12="","",SUM(IFERROR(¤u§@ªí1!M$12:M$24*(¤u§@ªí1!AB$12:AK$24=A12)*(¤u§@ªí1!Z$12:AI$24=B12)*¤u§@ªí1!AA$12:AJ$24,)))

I12:I23{=INDEX(¤u§@ªí1!D:D,LARGE(IF((¤u§@ªí1!AB$12:AK$24=A12)*(¤u§@ªí1!Z$12:AI$24=B12),ROW(D$12:D$24)),1))&""

Kubi

¦^´_ 1# jesscc

½Ð°Ñ¦Ò

1. Sub test()
   
2.     Dim arr
   
3.     Dim brr()
   
4.     arr = Range("D12", "AK" & [D65536].End(xlUp).Row)
   
5.     For i = 1 To UBound(arr)
   
6.         For j = 23 To 33 Step 3
   
7.             If arr(i, j) <> "" Then
   
8.                 n = n + 1
   
9.                 ReDim Preserve brr(1 To 9, 1 To n)
   
10.                 brr(1, n) = arr(i, j + 2)
    
11.                 brr(2, n) = arr(i, j)
    
12.                 brr(7, n) = arr(i, 10) * arr(i, j + 1)
    
13.                 brr(9, n) = arr(i, 1)
    
14.             End If
    
15.         Next j
    
16.     Next i
    
17.     With Sheets("¤u§@ªí2")
    
18.         .Rows("12:65536").Delete
    
19.         .[A12].Resize(n, 9) = Application.Transpose(brr)
    
20.         .Select
    
21.     End With
    
22. End Sub

½Æ»s¥N½X

jesscc

¦^´_ 3# Kubi

¸ò»Ý¨D¥Øªºµy¦³®t²§¡A¬Û¦Pªº¼t°Ó¨S¦³±Æ¦C¦b¤@°_¡C¤£¹L³o³¡¤À¥Î¤H¤u¿z¿ï¤]¨SÃö«Y¡AÁÂÁ¤j¤j¡C
¥t¥~¦³­Ó¤p°ÝÃDarr©Mbrr()¦³¤°»ò®t§O?

Kubi

¦^´_ 4# jesscc
Q1¡G¬Û¦Pªº¼t°Ó¨S¦³±Æ¦C¦b¤@°_
A1¡G¥u­n¦bWith Sheets("¤u§@ªí2")¤ºªº.Select¤§«e´¡¤J©³¤U³o¦æµ{¦¡½X
.[A12].Resize(n, 9).Sort key1:=.[A12]

Q2¡Garr©Mbrr()¦³¤°»ò®t§O?
A2¡Garr¡GÄÝÀRºA°}¦C¡A¥Î¦b¥i½T»{°}¦C¤j¤pªº±¡ªp
brr()¡GÄݰʺA°}¦C¡A¥Î¦bµLªk½T»{°}¦C¤j¤pªº±¡ªp

jesscc

ÁÂÁÂK¤jªºÀ°¦£

Andy2483

ÁÂÁ½׾Â,ÁÂÁ¦U¦ì«e½ú
«á¾ÇÂǦ¹©«½m²ß°}¦C.°j°é»P±Æ§Ç,¾Ç²ß¤è®×¦p¤U,½Ð¦U¦ì«e½ú«ü±Ð

Option Explicit
Sub TEST()
Dim Brr, Crr, i&, j%, N&
¤u§@ªí2.UsedRange.Offset(11).ClearContents
Brr = Range([¤u§@ªí1!IV11].End(xlToLeft)(2), [¤u§@ªí1!D65536].End(3))
ReDim Crr(1 To 1000, 1 To 9)
For i = 1 To UBound(Brr)
   For j = 23 To UBound(Brr, 2) Step 3
      If Trim(Brr(i, j)) = "" Then GoTo i01 Else N = N + 1
      Crr(N, 1) = Brr(i, j + 2): Crr(N, 2) = Brr(i, j): Crr(N, 9) = Brr(i, 1)
      Crr(N, 7) = Val(Brr(i, j + 1)) * Val(Brr(i, 10))
   Next
i01: Next
If N = 0 Then Exit Sub
With ¤u§@ªí2.[A12].Resize(N, 9)
   .Value = Crr
   .Sort KEY1:=.Item(1), Order1:=1, Header:=2
   Application.Goto .Cells
End With
End Sub