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

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

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

¦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"Äæ

01

01.PNG
2018-10-18 22:28

02

02.PNG
2018-10-18 22:28

ACC.rar (4.32 KB)
Jess

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))&""
ÀH·NºÛ "EXCEL°g"  blog  ©Îhttps://hcm19522.blogspot.com/ EXCEL¨ç¼Æ

TOP

¦^´_ 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

TOP

¦^´_ 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?
Jess

TOP

¦^´_ 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

TOP

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

TOP

ÁÂÁ½׾Â,ÁÂÁ¦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
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

        ÀR«ä¦Û¦b : ¹ï¤÷¥À­nª¾®¦¡A·P®¦¡B³ø®¦¡C
ªð¦^¦Cªí ¤W¤@¥DÃD