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

¦p¦ó¥Îvba¨ú±obomªí¤¤ªº¸ê®Æ

¦p¦ó¥Îvba¨ú±obomªí¤¤ªº¸ê®Æ

½Ð°Ý¦p¦ó¨ÌÕu²£«~½s¸¹¦Übomªí¤¤¨ú±o¬ÛÃö¸ê®Æ.
¥Ø«e¥Î¨ç¦¡³B²z¦ý«Ü³Â·Ð,»Ý¤@µ§¤@µ§Âà¶K¦¨¼ÆÕu,
½Ðª¾¹Dªº¤j¤j±Ð¾É¤@¤U.

BOM_1.zip (17.51 KB)

¦^´_ 6# ANGELA

dictionaryª«¥ó¡A­Yª½±µµ¹ITEM¤èªk¡A¥L±NÀx¦s¦¨³Ì«á³Q¼g¤Jªº­È
©Ò¥H¡A¹J¨ì­«½ÆªºKEY®É¡A¨Ï¥Î¦¹ªk¤]¯àÁקK¦]¥[¤J¬Û¦PKEY­È®É¡AAdd¤èªk²£¥Í¿ù»~
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 5# Hsieh
硏¨s¤F«Ü¤[ÁÙ¬O¦³¤£¸Ñªº¦a¤è,¥H¤U¬O§Ú®ø¤Æ¤Î¤£À´ªº¦a¤è½Ð«ü¾É,ÁÂÁÂ
    Sub tt()
Dim Ay(), C As Range, A As Range     '«Å§i AY()¬O°}¦C C ¬O½d³ò  A ¬O½d³ò
Set d = CreateObject("Scripting.Dictionary") '®w¦s
Set ds = CreateObject("Scripting.Dictionary") '¯Á¤Þ
For Each A In Range([E2], [E65536].End(xlUp)) 'A ªº½d³ò¦bSHEET1 ªºE2¨ì³Ì«á¤@µ§¸ê®Æ  ®w¦s
d(UCase(A)) = A.Offset(, 2)  'µ¹D(A)--GÄæ®w¦s¼Æ¶q ?¬O§_­«½Æªº¹s¥ó½s¸¹·|«O¯d³Ì«á¤@­Ó?
Next
With Sheets("¯Á¤Þ")
For Each A In .Range(.[A2], .[A65536].End(xlUp)) '¯Á¤Þ
ds(A.Value) = A.Offset(, 1)   'DS(A) ¨ú±oBOMªíªº¦WºÙ
Next
Ar = [A65536].End(xlUp).Resize(, 4) 'AR «ü©w¬°³Ì«á¤@µ§ªºA:D
With Sheets(ds(Ar(1, 3)))  '³Ì«á¤@µ§CÄæ©Ò¹ïÀ³ªº¤u§@ªí(BOM)
Set A = .Rows(2).Find(Ar(1, 3)).EntireColumn.SpecialCells(xlCellTypeConstants, 1)
'«ü©w A = ²Ä¤G¦C§ä¨ìSHEET1³Ì«á¤@µ§CÄæ¹ïÀ³¨ìªºÄ椤¦³¼Æ­È¸ê®ÆªºÄæ¦ì
For Each C In A
ReDim Preserve Ay(s)  '­«·s«Å§i°}¦CAY(S)¤j¤p
x = .Cells(C.Row, 1).Value '«ü©w X = A°}¦C¤¤¦³­ÈªºAÄ檺¹s¥ó½s¸¹
Ay(s) = Array(Ar(1, 1), Ar(1, 2), Ar(1, 3), Ar(1, 4), x, C * Ar(1, 4), d(x) - C * Ar(1, 4), Date)
'¬°¤°»òd(x)·|§ä¨ì³Ì«á¤@µ§®w¦s¸ê®Æ?DICTIONARY¬O¬Û¦P¹s¥ó½s¸¹·|Âл\±¼­ì¨Óªº­È¶Ü?
s = s + 1
Next
End With
End With
[A65536].End(xlUp).Resize(s, 8) = Application.Transpose(Application.Transpose(Ay)) 'Âà¸m
End Sub

TOP

¦^´_ 4# ANGELA
  1. Sub TNT()
  2. Dim Ay(), C As Range, A As Range
  3. Set d = CreateObject("Scripting.Dictionary") '®w¦s
  4. Set ds = CreateObject("Scripting.Dictionary") '¯Á¤Þ
  5. For Each A In Range([E2], [E65536].End(xlUp)) '®w¦s
  6.    d(UCase(A)) = A.Offset(, 2)
  7. Next
  8. With Sheets("¯Á¤Þ")
  9. For Each A In .Range(.[A2], .[A65536].End(xlUp)) '¯Á¤Þ
  10.    ds(A.Value) = A.Offset(, 1)
  11. Next
  12. Ar = [A65536].End(xlUp).Resize(, 4)
  13. With Sheets(ds(Ar(1, 3)))
  14. Set A = .Rows(2).Find(Ar(1, 3)).EntireColumn.SpecialCells(xlCellTypeConstants, 1)
  15. For Each C In A
  16. ReDim Preserve Ay(s)
  17. x = .Cells(C.Row, 1).Value
  18.    Ay(s) = Array(Ar(1, 1), Ar(1, 2), Ar(1, 3), Ar(1, 4), x, C * Ar(1, 4), d(x) - C * Ar(1, 4), Date)
  19.    s = s + 1
  20. Next
  21. End With
  22. End With
  23. [A65536].End(xlUp).Resize(s, 8) = Application.Transpose(Application.Transpose(Ay))
  24. End Sub
½Æ»s¥N½X
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 2# Hsieh


    ¤S¨Ó³Â·Ðª©¥D¤F,¸ÕµÛ­×§ï¤F¤@¤U¦ý¤£²z·Q,½Ðª©¥D¦AÀ°¤U¦£.
    ½Ð¬Ýªþ¥ó.

BOM_1.zip (15.56 KB)

TOP

¦^´_ 2# Hsieh


     ÁÂÁª©¥Dªº¸Ñµª§Ú·|¸ÕµÛ­×§ï,
   ­è¶i¤Jvba»â°ì,¥ý®ø¤Æ¬Ý¬Ý,¦³¤£À´¦A¨Ó³Â·Ðª©¥D¸Ñµª.

TOP

¦^´_ 1# ANGELA


    ¸Õ¸Õ¬Ý
  1. Sub TNT()
  2. Dim Ar()
  3. With Sheets(Left([C11], 2))
  4. Set a = .Rows(2).Find([C11])
  5. For Each c In a.EntireColumn.SpecialCells(xlCellTypeConstants, 1)
  6. Set b = [E2:E10].Find(.Cells(c.Row, 1))
  7. ReDim Preserve Ar(s)
  8. Ar(s) = Array([A11], [B11], [C11], [D11], .Cells(c.Row, 1), c * [D11], b.Offset(, 2) - c * [D11], Date)
  9. s = s + 1
  10. Next
  11. End With
  12. [A11].Resize(s, 8) = Application.Transpose(Application.Transpose(Ar))
  13. End Sub
½Æ»s¥N½X
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

        ÀR«ä¦Û¦b : ¯àµ½¥Î®É¶¡ªº¤H¡A¥²¯à´x´¤¦Û¤v§V¤Oªº¤è¦V¡C
ªð¦^¦Cªí ¤W¤@¥DÃD