¼Ï¯Ã¤ÀªRªí-¦h­«ªí®æ¶×Á`

198188

¨D¸Ñ
½Ð°Ý¦p¦ó¥Î¼Ï¯Ã¤ÀªRªí°µªº¹Ï¤ù1ªº®ÄªG
®ÄªG¦p¤U
PART-NO                       02F-09F               10F-17F
E1011-900                        344                     344
F211                                   6880                   5505

­ì¦]
FRAME-NO         QTY       02F-09F                                     10F-17F
WS100                   1             111        (1*111)=111               296              (1*296)=296
WS101                   1             18          (1*18)=18                     48                (1*18)=48
WS102                   1             111        (1*111)=111
WS103                   1             18          (1*18)=18
WS104                   1             74          (1*74)=74
WS105                   1             12          (1*12)=12
TOTAL:                                                         344                                                344

­ì¦]
FRAME-NO         QTY       02F-09F                                               10F-17F
WS100                   16             111       (16*111)=1776                  296      (16*296)=4736
WS101                   16             18         (16*18)=288                         48       (16*48)=769
WS102                   16             111       (16*111)=1776
WS103                   16             18          (16*18)=288
WS104                   16             74          (16*74)=1184
WS105                   16             12          (16*12)=192
WS106                   16             74          (16*74)=1184
WS107                   16             12          (16*12)=192
TOTAL                                                        6880                                 5505

198188

¨D¸Ñ
½Ð°Ý¦p¦ó¥Î¼Ï¯Ã¤ÀªRªí°µªº¹Ï¤ù1ªº®ÄªG
®ÄªG¦p¤U
PART-NO                       02F-09F           ...
198188 µoªí©ó 2023-11-4 16:34

¤£À´±o¦p¦ó­pºâ

198188

¤£À´±o¦p¦ó­pºâ
198188 µoªí©ó 2023-11-4 21:13

¨D²ªk¡A¥[¤W¤£¦P»y¨¥ªºexcel, ¤U­±¤¤¤å»yªk¡A¦b¨ä¥L»y¨¥ª©¥»ªºexcel·|¥X²{error, ¦³µL¸Ñ¨M¤è¦¡¡H


    Sub Summary()
    Sheets.Add After:=ActiveSheet
    Sheets("¤u§@ªí1").Select
    Sheets("¤u§@ªí1").Name = "Summary"
    Range("D30").Select
    Sheets("Part List").Select
    Columns("A:A").Select
    Selection.Copy
    Sheets("Summary").Select
    Range("K1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Part List").Select
    Columns("G:G").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Summary").Select
    Range("L1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Part List").Select
    Columns("C:C").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Summary").Select
    Range("M1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Frame List").Select
    Range("B1:F1").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Summary").Select
    Range("N1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Columns("K:R").Select
    Columns("K:R").EntireColumn.AutoFit
    Range("N2").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(VLOOKUP(RC[-2],'Frame List'!C[-13]:C[-8],2,0),0)*M2"
    Range("O2").Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(VLOOKUP(RC[-3],'Frame List'!C[-14]:C[-9],3,0),0)*M2"
    Range("P2").Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(VLOOKUP(RC[-4],'Frame List'!C[-15]:C[-10],4,0),0)*M2"
    Range("Q2").Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(VLOOKUP(RC[-5],'Frame List'!C[-16]:C[-11],5,0),0)*M2"
    Range("R2").Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(VLOOKUP(RC[-6],'Frame List'!C[-17]:C[-12],6,0),0)*M2"
    Range("N2:R2").Select
    Selection.AutoFill Destination:=Range("N2:R306")
    Range("N2:R306").Select
    Columns("K:R").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("A1").Select
    Application.CutCopyMode = False
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Summary!R1C11:R1048576C18", Version:=6).CreatePivotTable TableDestination _
        :="Summary!R1C1", TableName:="ÏEÇG猂1", DefaultVersion:=6
    Sheets("Summary").Select
    Cells(1, 1).Select
    With ActiveSheet.PivotTables("ÏEÇG猂1").PivotFields("PART-NO")
        .Orientation = xlRowField
        .Position = 1
    End With
    ActiveSheet.PivotTables("¼Ï¯Ã¤ÀªRªí1").AddDataField ActiveSheet.PivotTables("¼Ï¯Ã¤ÀªRªí1" _
        ).PivotFields("02F-09F"), "¥[Á` - 02F-09F", xlSum
    ActiveSheet.PivotTables("¼Ï¯Ã¤ÀªRªí1").AddDataField ActiveSheet.PivotTables("¼Ï¯Ã¤ÀªRªí1" _
        ).PivotFields("10F-17F"), "¥[Á` - 10F-17F", xlSum
    ActiveSheet.PivotTables("¼Ï¯Ã¤ÀªRªí1").AddDataField ActiveSheet.PivotTables("¼Ï¯Ã¤ÀªRªí1" _
        ).PivotFields("18F-25F"), "¥[Á`- 18F-25F", xlSum
    ActiveSheet.PivotTables("¼Ï¯Ã¤ÀªRªí1").AddDataField ActiveSheet.PivotTables("¼Ï¯Ã¤ÀªRªí1" _
        ).PivotFields("26F-33F"), "¥[Á` - 26F-33F", xlSum
    ActiveSheet.PivotTables("¼Ï¯Ã¤ÀªRªí1").AddDataField ActiveSheet.PivotTables("¼Ï¯Ã¤ÀªRªí1" _
        ).PivotFields("34F-42F"), "¥[Á` - 34F-42F", xlSum
    ActiveWorkbook.ShowPivotTableFieldList = False
    Columns("K:R").Select
    With Selection.Font
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
    End With
    ActiveWindow.ScrollColumn = 1
    Range("A1").Select
End Sub

Andy2483

¦^´_ 3# 198188


    ÁÂÁ½׾Â,ÁÂÁ«e½úµoªí¦¹¥DÃD»P½d¨Ò
«á¾ÇÂǦ¹©«½m²ß°}¦C»P¦r¨å,¾Ç²ß¤è®×¦p¤U,½Ð«e½ú°Ñ¦Ò
20231107_2.zip (33.51 KB)

11/07­×¥¿ª©
¤U¸ü¦¸¼Æ: 3

2023-11-7 07:57

°õ¦æ«e:

¤U¸ü (65.51 KB)

2023-11-6 14:30

°õ¦æµ²ªG:

¤U¸ü (121.02 KB)

2023-11-6 14:30

Option Explicit
Sub TEST()
Dim Arr, Brr, Crr, Z, i&, j%, R&, c%, N&, T$, T2$, T3$, T4$
Set Z = CreateObject("Scripting.Dictionary")
c = Sheets(1).[IV1].End(xlToLeft).Column
Brr = Range(Sheets(1).Cells(1, c), Sheets(1).[A65536].End(3))
Crr = Range(Sheets(2).[G1], Sheets(2).[A65536].End(3))
ReDim Arr(1 To UBound(Crr), 1 To c)
For i = 2 To UBound(Brr): Z(Trim(Brr(i, 1)) & "/r") = i: Next
For i = 2 To UBound(Crr)
   R = Z(Crr(i, 1))
   If R = 0 Then N = N + 1: R = N: Arr(N, 1) = Crr(i, 1): Z(Crr(i, 1)) = R
   T = Trim(Crr(i, 7))
   For j = 2 To UBound(Brr, 2)
      If Z(T & "/r") = "" Then Arr(R, j) = 0 Else Arr(R, j) = Arr(R, j) + Brr(Z(T & "/r"), j)
   Next
Next
If R = 0 Then Exit Sub
With Sheets(3)
   T2 = .[A65536].End(3): T3 = Left(.[B1], 5)
   T4 = .[A65536].End(3)(0): .UsedRange.Clear
   With .[A2].Resize(R, UBound(Arr, 2))
      .Value = Arr: .Sort KEY1:=.Item(1), Order1:=1, Header:=2
   End With
   For j = 1 To c: Brr(1, j) = T3 & Brr(1, j): Next
   .[A1].Resize(1, c) = Brr: .[A1] = Sheets(2).[A1]
   .Cells(R + 2, 1) = T4: .Cells(R + 3, 1) = T2
   .Cells(R + 3, 2).Resize(1, c - 1).Value = "=SUM(B2:B" & R + 1 & ")"
   Union(.[1:1], .Rows(R + 3)).Font.Bold = True: Application.Goto .[A1]
End With
Set Z = Nothing: Erase Brr, Crr, Arr
End Sub

singo1232001

¦^´_ 3# 198188


    Sub test()
Set CN = CreateObject("adodb.connection"): V = Application.Version
If V >= 12 Then V = "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 12.0;"
If V < 12 Then V = "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 8.0;"
CN.Open V & "Data Source=" & ThisWorkbook.FullName
Set s = Sheets("¤u§@ªí2"): s.[A:Z].ClearContents
For Each Z In Sheets
If Z.Name Like "*µ¡¥É*¶q*" Then Set s0 = Z : ar = s0.[B1:F1]
If Z.Name Like "*µ¡¥É*²M*" Then Set s1 = Z
Next
For Each Z In ar: p = p & "b.[" & Z & "], ": Next : p = "a.[PART-NO], " & Left(p, Len(p) - 2)
For Each Z In ar: p1 = p1 & "sum([" & Z & "]) as '" & Z & "', ": Next :p1 = Left(p1, Len(p1) - 2)
For Each Z In ar
p2 = p2 & "IIF([" & Z & "] IS NULL, 0, [" & Z & "]) AS [" & Z & "], "
:Next :p2 = "[FRAME-NO] as [FRAME-NO], " & Left(p2, Len(p2) - 2)
q = "select [PART-NO], " & p1 & " from ( select " & p & " from ( "
q = q & " select [PART-NO] ,[FRAME-NO] from [" & s1.Name & "$A1:G] ) as a left join ( "
q = q & " select " & p2 & " from [" & s0.Name & "$A1:F]  "
q = q & " ) as b on a.[FRAME-NO] = b.[FRAME-NO] ) group by [PART-NO] "
s.[A4].CopyFromRecordset CN.Execute(q)
s.[B3:F3] = ar: s.[A3] = "FRAME-NO"
End Sub

2.zip (88.81 KB)

¤U¸ü¦¸¼Æ: 3

2023-11-6 23:53

198188

¦^´_  198188


    ÁÂÁ½׾Â,ÁÂÁ«e½úµoªí¦¹¥DÃD»P½d¨Ò
«á¾ÇÂǦ¹©«½m²ß°}¦C»P¦r¨å,¾Ç²ß¤è®×¦p¤U,½Ð«e ...
Andy2483 µoªí©ó 2023-11-6 14:31

   ·PÁ«e½ú«üÂI¡A¤£¹L¼Æ¶q¶W¹L1ªº³fªºµª®×¨S¦³­¼¥H¼Æ¶q¡C
Á|¨Ò
¦bsheet "Part List" ùØ Part-No: EG039-900; Frame-No: WS100; Qty: 2
¦bsheet "Frame List ùØ Frame-No: WS100: 02F-09F: 111
02F-09F ¥X¨Óªºµª®×À³¸Ó¬O2*111=222

Andy2483

¦^´_ 6# 198188


   
If Z(T & "/r") = "" Then Arr(R, j) = 0 Else Arr(R, j) = Arr(R, j) + Brr(Z(T & "/r"), j) * VaL(Crr(i, 3))

Andy2483

ÁÂÁ½׾Â,ÁÂÁ¦U¦ì«e½ú
«á¾Ç­q¥¿½Æ²ß¤ß±oµù¸Ñ¦p¤U,½Ð¦U¦ì«e½ú«ü±Ð

Option Explicit
Sub TEST()
Dim Arr, Brr, Crr, Z, i&, R&, N&, j%, c%, T$, T2$, T3$, T4$
'¡ô«Å§iÅܼÆ:(Arr,Brr,Crr,Z)¬O³q¥Î«¬ÅܼÆ,(i,R,N)¬Oªø¾ã¼ÆÅܼÆ,(j,c)¬Oµu¾ã¼ÆÅܼÆ,
'(T,T2,T3,T4)¬O¦r¦êÅܼÆ
Set Z = CreateObject("Scripting.Dictionary")
'¡ô¥OZ³o³q¥Î«¬ÅܼƬO ¦r¨å
c = Sheets(1).[IV1].End(xlToLeft).Column
'¡ô¥Oc³oµu¾ã¼ÆÅܼƬO ²Ä1ªí²Ä1¦C³Ì¥k°¼¦³¤º®eªº¯Á¤ÞÄ渹
Brr = Range(Sheets(1).Cells(1, c), Sheets(1).[A65536].End(3))
'¡ô¥OBrr³o³q¥Î«¬ÅܼƬO ¤Gºû°}¦C,¥H½d³òÀx¦s®æ­È±a¤J:
'²Ä1ªícÅܼÆÄæ²Ä1¦CÀx¦s®æ¨ì ²Ä1ªíAÄæ³Ì«á¦³¤º®eÀx¦s®æ
Crr = Range(Sheets(2).[G1], Sheets(2).[A65536].End(3))
'¡ô¥OCrr³o³q¥Î«¬ÅܼƬO ¤Gºû°}¦C,¥H½d³òÀx¦s®æ­È±a¤J:
'²Ä2ªí[G1]Àx¦s®æ¨ì ²Ä2ªíAÄæ³Ì«á¦³¤º®eÀx¦s®æ
ReDim Arr(1 To UBound(Crr), 1 To c)
'¡ô«Å§iArr³o³q¥Î«¬ÅܼƬO ¤GºûªÅ°}¦C,Áa¦V½d³ò¯Á¤Þ¸¹1¨ìCrr°}¦CÁa¦V³Ì¤j¯Á¤Þ¦C¸¹,
'¾î¦V½d³ò1¨ì cÅÜ¼Æ ¯Á¤ÞÄ渹
For i = 2 To UBound(Brr): Z(Trim(Brr(i, 1)) & "/r") = i: Next
'¡ô³]¶¶°j°é!i±q2 ¨ìBrr°}¦CÁa¦V³Ì¤j¯Á¤Þ¦C¸¹
'¡ô¥Oi°j°é¦C1ÄæBrr°}¦C­È¥h°£ÀY§ÀªÅ¦r¤¸,³s±µ"/r"²Õ¦¨ªº¦r¦ê·íkey,
'item¬OiÅܼÆ,¯Ç¤JZ¦r¨å¤¤
For i = 2 To UBound(Crr)
'¡ô³]¶¶°j°é!i±q2 ¨ìCrr°}¦CÁa¦V³Ì¤j¯Á¤Þ¦C¸¹
   R = Z(Trim(Crr(i, 1)))
   '¡ô¥OR³oªø¾ã¼ÆÅܼƬO¥H i°j°é¦C1ÄæCrr°}¦C­È¥h°£ÀY§ÀªÅ¦r¤¸ ¦r¦ê,
   '¬dZ¦r¨å¦^¶ÇItem­È
   If R = 0 Then N = N + 1: R = N: Arr(N, 1) = Crr(i, 1): Z(Trim(Crr(i, 1))) = R
   '¡ô¦pªGRÅܼƬO0!´N¥ON³oªø¾ã¼Æ²Ö¥[1,¥ORÅܼƦPNÅܼƭÈ,
   '¥ONÅܼƦC1ÄæArr°}¦C­È¬O i°j°é¦C1ÄæCrr°}¦C­È
   '¥Oi°j°é¦C1ÄæCrr°}¦C­È¥h°£ÀY§ÀªÅ¦r¤¸ ¦r¦ê·íkey,RÅܼƭȷíitem ¯Ç¤JZ¦r¨å¸Ì
   T = Trim(Crr(i, 7))
   '¡ô¥OT³o¦r¦êÅܼƬO i°j°é¦C7ÄæCrr°}¦C­È¥h°£ÀY§ÀªÅ¦r¤¸ ¦r¦ê
   For j = 2 To UBound(Brr, 2)
   '¡ô³]¶¶°j°é!j±q2 ¨ìBrr°}¦C¾î¦V³Ì¤j¯Á¤ÞÄ渹
      If Z(T & "/r") = "" Then Arr(R, j) = 0 Else Arr(R, j) = Arr(R, j) + Brr(Z(T & "/r"), j) * Val(Crr(i, 3))
      '¡ô¦pªG¥HTÅܼƳs±µ"/r"²Õ¦¨ªº·s¦r¦ê¬dZ¦r¨å¦^¶Çitem¬OªÅ¦r¤¸,
      '´N¥ORÅܼƦCjÅܼÆÄæArr°}¦C­È¬O0
      '§_«h´N¥ORÅܼƦCjÅܼÆÄæArr°}¦C­È¬O ²Ö¥[(Brr°}¦C­È* i°j°é¦C3ÄæCrr°}¦C­È)
      'Brr°}¦C­È:(TÅܼƳs±µ"/r"²Õ¦¨ªº·s¦r¦ê¬dZ¦r¨å¦^¶Çitem)¦C,jÅܼÆÄæBrr°}¦C­È
   Next
Next
If R = 0 Then MsgBox "¨S¦³²Å¦Xªº¸ê®Æ": Exit Sub
'¡ô¦pªGRÅܼƬO0!´N¸õ¥X´£µøµ¡~~~,µ²§ôµ{¦¡°õ¦æ
With Sheets(3)
'¡ô¥H¤U¬OÃö©ó²Ä3ªíªºµ{§Ç
   T2 = .[A65536].End(3): T3 = Left(.[B1], 5)
   '¡ô¥OT2³o¦r¦êÅܼƬOAÄæ³Ì«á¦³¤º®eÀx¦s®æ¦r¦ê
   '¡ô¥OT3³o¦r¦êÅܼƬO[B1]Àx¦s®æ¥ª°¼5­Ó¦r¤¸
   T4 = .[A65536].End(3)(0): .UsedRange.Clear
   '¡ô¥OT4³o¦r¦êÅܼƬO AÄæ³Ì«á¦³¤º®eÀx¦s®æ«e¤@®æ¦r¦ê
   With .[A2].Resize(R, UBound(Arr, 2))
   '¡ô¥H¤U¬OÃö©ó²Ä3ªí[A2]ÂX®i¦V¤URÅܼƦC,ÂX®i¦V¥k(Arr¾î¦V³Ì¤j¯Á¤ÞÄ渹)Äæ,
   'Ãö©ó¦¹½d³òÀx¦s®æªºµ{§Ç
      .Value = Arr: .Sort KEY1:=.Item(1), Order1:=1, Header:=2
      '¡ô¥O¦¹½d³òÀx¦s®æ­È¥HArr°}¦C±a¤J
      '¡ô¥O¦¹½d³òÀx¦s®æ°µ²Ä1Ä欰°ò·ÇªºµL¼ÐÃDº¥¼W±Æ§Ç
   End With
   For j = 1 To c: Brr(1, j) = T3 & Brr(1, j): Next
   '¡ô³]¶¶°j°é!j±q1¨ìcÅܼÆ
   '¥O²Ä1¦Cj°j°éÄæBrr°}¦C­È¬O T3ÅܼƳs±µ¦Û¨­°}¦C­È²Õ¦X¦¨ªº·s¦r¦ê
   .[A1].Resize(1, c) = Brr: .[A1] = Sheets(2).[A1]
   '¡ô¥O²Ä3ªí[A1]ÂX®i¦V¥kcÅܼÆÄæ½d³òÀx¦s®æ­È ¥HBrr°}¦C­È±a¤J
   '¡ô¥O²Ä3ªí[A1]Àx¦s®æ­È¦P ²Ä2ªí[A1]Àx¦s®æ­È
   .Cells(R + 2, 1) = T4: .Cells(R + 3, 1) = T2
   '¡ô¥O²Ä3ªíAÄæ(RÅܼÆ+2)¦CÀx¦s®æ¬O T2ÅܼÆ
   .Cells(R + 3, 2).Resize(1, c - 1).Value = "=SUM(B2:B" & R + 1 & ")"
   '¡ô¥O²Ä3ªíBÄæ(RÅܼÆ+3)¦CÀx¦s®æÂX®i¦V¥k(cÅܼÆ-1)Äæ½d³òÀx¦s®æ­È¬O¤½¦¡
   '¤½¦¡:SUM()¥[Á` B2¨ìBÄæ(RÅܼÆ+1)¦C
   'C~FÄ椽¦¡·|¦Û°ÊÅܤÆ
   Union(.[1:1], .Rows(R + 3)).Font.Bold = True: Application.Goto .[A1]
   '¡ô¥O²Ä1¦C»P³Ì«á¦CÀx¦s®æ¦rÅ鬰²ÊÅé
   '¡ô¥O´å¼Ð¸õ¨ì²Ä3ªí[A1]Àx¦s®æ
End With
Set Z = Nothing: Erase Brr, Crr, Arr
'¡ô¥OÄÀ©ñÅܼÆ
End Sub

singo1232001

¦^´_ 6# 198188
3.zip (87.47 KB)

¤U¸ü¦¸¼Æ: 3

2023-11-7 10:40

¤U¸ü (31.74 KB)

2023-11-7 10:49

Sub test2()
Set CN = CreateObject("adodb.connection"): V = Application.Version
If V >= 12 Then V = "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 12.0;"
If V < 12 Then V = "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 8.0;"
CN.Open V & "Data Source=" & ThisWorkbook.FullName
Set s = Sheets("¤u§@ªí2"): s.[A:Z].ClearContents
For Each Z In Sheets
If Z.Name Like "*µ¡¥É*¶q*" Then Set s0 = Z: ar = s0.[B1:F1]
If Z.Name Like "*µ¡¥É*²M*" Then Set s1 = Z
Next
For Each Z In ar: p = p & "b.[" & Z & "]*[QTY] as [" & Z & "] , ": Next
For Each Z In ar: p1 = p1 & "sum([" & Z & "]) as '" & Z & "', ": Next
For Each Z In ar
p2 = p2 & "IIF([" & Z & "] IS NULL, 0, [" & Z & "]) AS [" & Z & "], ": Next
p = "a.[PART-NO], " & Left(p, Len(p) - 2)
p1 = Left(p1, Len(p1) - 2)
p2 = "[FRAME-NO] as [FRAME-NO], " & Left(p2, Len(p2) - 2)

q = "select [PART-NO], " & p1 & " from ( "
q = q & " select " & p & " from ( "
q = q & " select [PART-NO] ,[FRAME-NO], [QTY] from [" & s1.Name & "$A1:G]"
q = q & " ) as a left join ( "
q = q & " select " & p2 & " from [" & s0.Name & "$A1:F]  "
q = q & " ) as b on a.[FRAME-NO] = b.[FRAME-NO] "
q = q & " ) group by [PART-NO] "
s.[A4].CopyFromRecordset CN.Execute(q)
s.[B3:F3] = ar: s.[A3] = "FRAME-NO"
End Sub



---¥Dµ{§Çµù¸Ñ
Sub test()
    ' ³Ð«Ø¤@­Ó·sªºADODB³s±µ¹ï¶H
    Set CN = CreateObject("adodb.connection"): V = Application.Version
    ' ®Ú¾ÚExcelªºª©¥»¿ï¾Ü¦X¾Aªº¼Æ¾Ú´£¨Ñµ{§Ç
    If V >= 12 Then V = "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 12.0;"
    If V < 12 Then V = "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 8.0;"
    ' ¶}±Ò³s±µ¨ì·í«e¤u§@ï
    CN.Open V & "Data Source=" & ThisWorkbook.FullName
   
    ' ²M°£¤u§@ªí2ªºA¨ìZ¦Cªº¤º®e
    Set s = Sheets("¤u§@ªí2"): s.[A:Z].ClearContents
   
    ' ¹M¾ú©Ò¦³¤u§@ªí¥H´M§ä¤Ç°t¯S©w¦WºÙ¼Ò¦¡ªº¤u§@ªí
    For Each Z In Sheets
        If Z.Name Like "*µ¡¥É*¶q*" Then Set s0 = Z: ar = s0.[B1:F1]
        If Z.Name Like "*µ¡¥É*²M*" Then Set s1 = Z
    Next
   
    ' ²Õ¦XSQL»y¥y¤¤»Ý­n¥Î¨ìªº¦r¬q¦Cªí
    For Each Z In ar: p = p & "b.[" & Z & "]*[QTY] as [" & Z & "] , ": Next
    For Each Z In ar: p1 = p1 & "sum([" & Z & "]) as '" & Z & "', ": Next
    For Each Z In ar
        p2 = p2 & "IIF([" & Z & "] IS NULL, 0, [" & Z & "]) AS [" & Z & "], "
    Next
   
    ' ¥h°£³Ì«á¤@­Ó¦h¾lªº³r¸¹
    p = "a.[PART-NO], " & Left(p, Len(p) - 2)
    p1 = Left(p1, Len(p1) - 2)
    p2 = "[FRAME-NO] as [FRAME-NO], " & Left(p2, Len(p2) - 2)
   
    ' ºc«Ø§¹¾ãªºSQL¬d¸ß»y¥y
    q = "select [PART-NO], " & p1 & " from (  select " & p & " from ( "
    q = q & " select [PART-NO] ,[FRAME-NO], [QTY] from [" & s1.Name & "$A1:G]  ) as a left join ( "
    q = q & " select " & p2 & " from [" & s0.Name & "$A1:F]  "
    q = q & " ) as b on a.[FRAME-NO] = b.[FRAME-NO] ) group by [PART-NO] "
   
    ' °õ¦æSQL¬d¸ß¨Ã±Nµ²ªG½Æ»s¨ì¤u§@ªí2ªºA4³æ¤¸®æ
    s.[A4].CopyFromRecordset CN.Execute(q)
   
    ' ¦b¤u§@ªí¤W³]¸m¦r¬qªíÀY
    s.[B3:F3] = ar
    s.[A3] = "FRAME-NO"
End Sub


---SQL»yªkµù¸Ñ
-- ¿ï¾Ü³¡¥ó½s¸¹©M¹ïÀ³¼Æ¶q½d³òªºÁ`©M
select [PART-NO],
       sum([02F-09F]) as '02F-09F',
       sum([10F-17F]) as '10F-17F',
       sum([18F-25F]) as '18F-25F',
       sum([26F-33F]) as '26F-33F',
       sum([34F-42F]) as '34F-42F'
-- ±q¥H¤U¤l¬d¸ßªºµ²ªG¤¤¶i¦æ¿ï¾Ü
from (  
    -- ¿ï¾Ü³¡¥ó½s¸¹©M¸g¹L¼Æ¶q¥[Åv«áªº¦U¼Æ¶q½d³ò
    select a.[PART-NO],
           b.[02F-09F]*[QTY] as [02F-09F],
           b.[10F-17F]*[QTY] as [10F-17F],
           b.[18F-25F]*[QTY] as [18F-25F],
           b.[26F-33F]*[QTY] as [26F-33F],
           b.[34F-42F]*[QTY] as [34F-42F]  
    -- ±q¤@­Óªí®æ¤¤¿ï¾Ü³¡¥ó½s¸¹¡B®Ø¬[½s¸¹©M¼Æ¶q
    from ( select [PART-NO], [FRAME-NO], [QTY]
           from [µ¡¥É???²M³æ$A1:G] ) as a
    -- ¥ª°¼³s±µ¤@­Óªí®æ¡A¸Óªí®æ¬°¦U¼Æ¶q½d³ò´£¨Ñ¤FªÅ­ÈªºÀˬd©M¹w³B²z
    left join (  
        select [FRAME-NO],
               IIF([02F-09F] IS NULL, 0, [02F-09F]) AS [02F-09F],
               IIF([10F-17F] IS NULL, 0, [10F-17F]) AS [10F-17F],
               IIF([18F-25F] IS NULL, 0, [18F-25F]) AS [18F-25F],
               IIF([26F-33F] IS NULL, 0, [26F-33F]) AS [26F-33F],
               IIF([34F-42F] IS NULL, 0, [34F-42F]) AS [34F-42F]
        from [µ¡¥É¤À§å¼Æ¶q$A1:F]   
    ) as b
    -- ³s±µ±ø¥ó¬O¨â­Ó¤l¬d¸ßªº®Ø¬[½s¸¹¬Û¦P
    on a.[FRAME-NO] = b.[FRAME-NO]  
)
-- ³Ì«á«ö³¡¥ó½s¸¹¶i¦æ¤À²Õ¡A¹ï¨C²Õ¶i¦æ²Î­p
group by [PART-NO]


³o­Ó¬d¸ßªº¥Øªº¬O¬°¤F¦X­p¨C­Ó³¡¥ó½s¸¹¡]PART-NO¡^¦b¤£¦Pªº¼Æ¶q½d³ò¡]¦p02F-09F, 10F-17F µ¥¡^¤ºªº¼Æ¶q¡A¨Ã±Nµ²ªG§@¬°·sªº¦CÅã¥Ü¡C³o¬O³q¹L¹ï¨â­Óªí¶i¦æ¥ª³s±µ¨Ã¦b³s±µ¤§«á¹ï¼Æ¶q¶i¦æ­¼ªk¾Þ§@¨Ó¹ê²{ªº¡A¦pªG­ìªí¤¤ªº¼Æ¾Ú¬°ªÅ¡]NULL¡^¡A«h¸Ó¼Æ¶q½d³òªº­È±N³Qµø¬° 0¡C³Ì«á¡A¬d¸ß«ö·Ó³¡¥ó½s¸¹¹ï³o¨Ç¥[Åv«áªº¼Æ¶q¶i¦æ¤F¤À²Õ©M¨D©M¡C


---SQL§ïVBAµù¸Ñ
' ªì©l¤Æ¬d¸ß¦r²Å¦ê q
q = "select [PART-NO], " & p1 & " from (  select " & p & " from ( "
' ¦¹³B q ¶}©lºc«Ø¤@­Ó´O®M¬d¸ß
' ­º¥ý¡A¿ï¾Ü PART-NO ©M¥Ñ p1 Åܶqªí¥Üªº¤@¨t¦C¨Ï¥Î»E¦X¨ç¼Æ sum() ªº¦C¡A³o¨Ç¦C¦W¥Ñ¤u§@ªí s0 ªº B1:F1 ³æ¤¸®æ¨M©w
' p1 ¤¤ªº¦C¬O¥Ñ¤§«e´`Àôºc«Øªº¡A¥]§t¤F±N¨C­Ó¦CªºÁ`©M­«·s©R¦W¬°¸Ó¦C¦Wªº»y¥y

q = q & " select [PART-NO], [FRAME-NO], [QTY] from [" & s1.Name & "$A1:G]  ) as a left join ( "
' ±µ¤U¨Ó²K¥[¤F±q s1 ¤u§@ªí¿ï¾Ü PART-NO, FRAME-NO, QTY ³o¤T¦Cªº¤l¬d¸ß
' ¦¹¤l¬d¸ß³Q½á¤©§O¦W a¡A¨Ã­p¹º»P§O¦W¬° b ªº¥t¤@­Ó¤l¬d¸ß¶i¦æ¥ª³s±µ

q = q & " select " & p2 & " from [" & s0.Name & "$A1:F]  "
' ³o¸Ì²K¥[¤F²Ä¤G­Ó¤l¬d¸ß¡A³o¦¸¬O±q s0 ¤u§@ªí¤¤¿ï¾Ü¥Ñ p2 Åܶqªí¥Üªº¦C
' p2 ¥]§t¤F¤@¨t¦Cªº IIF »y¥y¡A¥Î¨ÓÀˬd s0 ¤u§@ªí¤¤ªº¦C¬O§_¬° NULL¡A¦pªG¬° NULL «h´À´«¬° 0¡A§_«h«O¯d­ì­È
' ³o¨Ç¦C¤]³Q­«·s©R¦W¬°­ì¨Óªº¦C¦W

q = q & " ) as b on a.[FRAME-NO] = b.[FRAME-NO] ) group by [PART-NO] "
' ³Ì«á¡A±N¨â­Ó¤l¬d¸ß³q¹L FRAME-NO ¶i¦æ¥ª³s±µ¡A¨Ã¦b¥~¼h¬d¸ß¤¤«ö PART-NO ¶i¦æ¤À²Õ
' ³o·N¨ýµÛ§Ú­Ì±N±o¨ì«ö PART-NO ¤À²Õªº¨C­Ó PART-NO ªº©Ò¦³¦CªºÁ`©M¡A¨ä¤¤¨Ó¦Û s0 ªº¼Æ¾Ú±N¥H 0 ´À´«ªÅ­È

' ±N¾ã­Ó¬d¸ß½á­Èµ¹ q Åܶq
s.[A4].CopyFromRecordset CN.Execute(q)
' °õ¦æ¤W­z SQL ¬d¸ß¡A¨Ã±Nµ²ªG±q°O¿ý¶°½Æ»s¨ì¤u§@ªí s ¤¤ A4 ³æ¤¸®æ¶}©lªº¦ì¸m

198188

ÁÂÁ½׾Â,ÁÂÁ¦U¦ì«e½ú
«á¾Ç­q¥¿½Æ²ß¤ß±oµù¸Ñ¦p¤U,½Ð¦U¦ì«e½ú«ü±Ð

Option Explicit
Sub TEST()
Dim A ...
Andy2483 µoªí©ó 2023-11-7 10:16

   ÁÂÁ±zªº¸ÔºÉª`¸Ñ