ªð¦^¦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)

¦^´_ 2# Hsieh


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

TOP

¦^´_ 2# Hsieh


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

BOM_1.zip (15.56 KB)

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

        ÀR«ä¦Û¦b : ±o²z­nÄǤH¡A²zª½­n®ð©M¡C
ªð¦^¦Cªí ¤W¤@¥DÃD