[µo°Ý] ¨âªí¸ê®Æ­«½Æ¹ï¤ñ¨Ã¼Æ¶q¬Û­¼

198188

¤U¸ü (166.95 KB)

2025-11-6 09:39

¦³¨â­Óªí Read & Data

Data ¬O¸ê®ÆÀÉ®×
Read ¬Oµ{¦¡°õ¦æÀÉ®×


°õ¦æµ{¦¡³W«h¡G
Read ªí ¦Ç¦â³¡¤À¬O­ì¦³¸ê®Æ¡A«O¯d¡C
Read ªí A Äæ  ¹ï¤ñ Data ªí H Äæ¡A
­Y§k¦X¡A½Æ»s Data ªí ¹ïÀ³ªº¤@¦C¸ê®Æ¨ì Read ªí A Äæ³Ì«á¤@¦C«á, ¼Æ¶q Read ªí  Qty * Data ªí Qty ¡]¦pÂŦⳡ¤À¡^
§¹¦¨«á¡A¦A­«½Æ¤@¦¸
Read ªí A Äæ  ¹ï¤ñ Data ªí H Äæ¡A
­Y§k¦X¡A½Æ»s Data ªí ¹ïÀ³ªº¤@¦C¸ê®Æ¨ì Read ªí A Äæ³Ì«á¤@¦C«á, ¼Æ¶q Read ªí  Qty * Data ªí Qty ¡]¦pºñ¦â¦â³¡¤À¡^

Andy2483

¦^´_ 1# 198188


    ½Ð«e½ú¤W¶Ç½d¨ÒÀÉ

198188

¦^´_  198188


    ½Ð«e½ú¤W¶Ç½d¨ÒÀÉ
Andy2483 µoªí©ó 2025-11-6 11:10

«e½ú¡Aªþ¤W½d¨Ò

hcm19522

C2=IFERROR(VLOOKUP(H2,A1:C$2,3,),1)*VLOOKUP(A2,Data!A:C,3,)

Andy2483

¦^´_ 3# 198188


    ÁÂÁ«e½úµoªí¦¹¥DÃD»P½d¨Ò,«á¾Ç¾Ç²ß¤è®×¦p¤U,½Ð«e½ú°Ñ¦Ò

Option Explicit
Sub TEST()
Dim Brr, Z, K, i&, j%, N&
Set Z = CreateObject("Scripting.Dictionary")
Brr = [Read!A1].CurrentRegion
For i = 2 To UBound(Brr): Z(Brr(i, 1)) = Val(Brr(i, 3)): Next
Brr = Range([Data!A1], [Data!A1].CurrentRegion.Offset(UBound(Brr)))
For i = 2 To UBound(Brr)
   If Z.Exists(Brr(i, 8)) Then
      Z(Brr(i, 1) & "/") = i
      Brr(i, 3) = Z(Brr(i, 8)) & "*" & Val(Brr(i, 3))
   End If
   If Z.Exists(Brr(i, 8) & "/") Then
      Z(Brr(i, 8) & "//") = i
      Brr(i, 3) = Brr(Z(Brr(i, 8) & "/"), 3) & "*" & Val(Brr(i, 3))
   End If
Next
For Each K In Z.Keys
   If InStr(K, "/") Then
      N = N + 1
      For j = 1 To UBound(Brr, 2): Brr(N, j) = Brr(Z(K), j): Next
      'Brr(N, 3) = "=" & Brr(N, 3)
   End If
Next
Workbooks.Add
[A1].Resize(N, UBound(Brr, 2)) = Brr
End Sub

198188

1. Option Explicit
   
2. Sub TEST()
   
3. Dim arr, Brr, Z, K, i&, j%, N&
   
4. Set Z = CreateObject("Scripting.Dictionary")
   
5. Brr = [Read!A1].CurrentRegion
   
6. For i = 2 To UBound(Brr): Z(Brr(i, 1)) = Val(Brr(i, 3)): Next
   
7. Brr = Range([Data!A1], [Data!A1].CurrentRegion.Offset(UBound(Brr)))
   
8. For i = 2 To UBound(Brr)
   
9.    If Z.Exists(Brr(i, 8)) Then
   
10.       Z(Brr(i, 1) & "/") = i
    
11.       Brr(i, 3) = Z(Brr(i, 8)) * Val(Brr(i, 3))
    
12.    End If
    
13.    If Z.Exists(Brr(i, 8) & "/") Then
    
14.       Z(Brr(i, 8) & "//") = i
    
15.       Brr(i, 3) = Brr(Z(Brr(i, 8) & "/"), 3) * Val(Brr(i, 3))
    
16.    End If
    
17. Next
    
18. For Each K In Z.Keys
    
19.    If InStr(K, "/") Then
    
20.       N = N + 1
    
21.       For j = 1 To UBound(Brr, 2): Brr(N, j) = Brr(Z(K), j): Next
    
22.       'Brr(N, 3) = "=" & Brr(N, 3)
    
23.    End If
    
24. Next
    
25. arr = Sheets("Read").UsedRange
    
26. Sheets("Read").Range("A" & UBound(arr) + 1).Resize(N, UBound(Brr, 2)) = Brr
    
27. End Sub

½Æ»s¥N½X

¦^´_  198188


    ÁÂÁ«e½úµoªí¦¹¥DÃD»P½d¨Ò,«á¾Ç¾Ç²ß¤è®×¦p¤U,½Ð«e½ú°Ñ¦Ò

Option Explicit
Sub  ...
Andy2483 µoªí©ó 2025-11-6 14:16

«e½ú§Ú­×§ï¦p¤W¡C

198188

¦^´_  198188


    ÁÂÁ«e½úµoªí¦¹¥DÃD»P½d¨Ò,«á¾Ç¾Ç²ß¤è®×¦p¤U,½Ð«e½ú°Ñ¦Ò

Option Explicit
Sub  ...
Andy2483 µoªí©ó 2025-11-6 14:16

Brr = Range([Data!A1], [Data!A1].CurrentRegion.Offset(UBound(Brr)))

½Ð°Ý«e½ú¡A³o¥y¦pªG·Q§ï
xFile = "Data Base.xlsx"
sheets ("Data")
À³¸Ó¦p¦ó®M¤J¡H

Andy2483

Brr = Range([Data!A1], [Data!A1].CurrentRegion.Offset(UBound(Brr)))

½Ð°Ý«e½ú¡A³o¥y¦pªG·Q§ï
x ...
198188 µoªí©ó 2025-11-6 15:12

With Workbooks("Data Base.xlsx").Sheets("Data")
   Brr = .Range(.[A1], .[A1].CurrentRegion.Offset(UBound(Brr)))
End With

198188

With Workbooks("Data Base.xlsx").Sheets("Data")
   Brr = .Range(.[A1], .[A1].CurrentRegion.Of ...
Andy2483 µoªí©ó 2025-11-6 15:38

«e½ú¡A¦pªG¼Æ¶q¨C¤@½üªº¼Æ¶q³£¥Î  «e¤@½üªºÁ`¼Æ¶q *  Data Base ªº¼Æ¶q¡AÀ³¸Ó¦p¦ó§ó§ï¡H
Á|¨Ò
¥»ÀɦǦâ¬O­ì¥»¼Æ¶q¡A
E1204 ¦@¦³6­Ó

²Ä¤@½ü¹B¦æ
E1204 ¦@¦³6­Ó
E1204 ¹ïÀ³ A1122
A1122 ¦³ 2 ¦æ, ¦p¤U
A1122  2 * 6 =12
A1122  3 * 6 =18

²Ä¤G½ü¹B¦æ
A1122 ¦@¦³30­Ó

A1122 ¹ïÀ³ B1236
B1236 ¦³ 1 ¦æ, ¦p¤U
B1236    2 * 30 = 60

Andy2483

¦^´_ 9# 198188


    ½Ð«e½ú¦Û¦æ¸Õ¸Õ¼g¤@¬q¥N½X¥ý§âData ¦P¸¹¬Û¥[¡A¦A±NRead¤ñ¹ï2¦¸Data