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

[µo°Ý] ¥¨¶°°õ¦æ½wºC§ïµ½

[µo°Ý] ¥¨¶°°õ¦æ½wºC§ïµ½

½Ð±Ð¤@¤U¦U¦ì«e½ú°ª¤â¡A¤p§Ì°µ¤F¤@¬q¤ñ¸û­q³æªº¥¨¶°¡A¦ý¬O°õ¦æ¤W¬Û·í½wºC¡A¤£ª¾¬O§_¦³¨ä¥L¼gªk¥i¥[³t
¥D­n¬O®Ú¾Ú¹F¥æ¤é/®Æ¸¹/­q³æ½s¸¹/¶µ¦¸¨Ó§P©w¬O¨â¤Ñªº¦P¤@±i­q³æ¥h¤ñ¸û

¦]¬°¨C¤é·|¦³¤J®w©Ò¥H¥¿±`day2À³¸Ó·|¤ñday1¤Ö¡A­Y¬O¼W¥[«hµø¬°²§±`

¦]¬°¨C¦¸°õ¦æ¦¹¬q³£·|¬Û·í½wºC¡A¤£ª¾¬O§_¦³§ïµ½ªº¤èªk¡AÁÂÁ¡I
­q³æ¤ñ¸û-°ÝÃD.rar (53.27 KB)
Adam

Sub ¤ñ¸û()
Dim xRow1&, xRow2&, xTT$
[day2!R1] = "¬Q¤é":   [day2!S1] = "®t²§"
'¡õRÄ椽¦¡ªº¡e¹w³]¤½¦¡¦r¦ê¡f
xTT = "=SUMPRODUCT((B2=day1!B$2:B$//)*(day2!E2=day1!E$2:E$//)*(day2!N2=day1!N$2:N$//)*(day2!O2=day1!O$2:O$//),day1!H$2:H$//)"

xRow1 = [day1!A65536].End(xlUp).Row
xRow2 = [day2!A65536].End(xlUp).Row
'¡õ±N¡e¹w³]¤½¦¡¦r¦ê¡f¤¤ªº¡e//¡f´À´«¬°¹ê»Ú¡eday1¡f³Ì«á¤@¦C¸¹¡A¶ñ¤J¢àÄæ
[day2!R2].Resize(xRow2 - 1) = Replace(xTT, "//", xRow1)
[day2!S2].Resize(xRow2 - 1) = "=IF(H2-R2>0,""¼W¥["","""")"
End Sub

·|½wºC¬O¦]¬°¤½¦¡¡e¥þÄæ¤Þ¥Î¡f¡A¸ê®Æ¥u¦³¢´¢¯¢¯µ§¥ª¥k¡A­­©w°Ñ·Ó½d³ò§Y¥i¡I
­Y¸ê®Æµ§¼Æ¯uªº«Ü¦h¡A¥i§ï¥Î¦r¨åÀɤÎARRAY

TOP

¦^´_ 2# ­ã´£³¡ªL

ª©¥Dªºµ{¦¡½X¦n±j¡A­è­è¦Û¤v¥ÎArray¸Õ¡A¦ý³t«×ÁÙ¬O«ÜºC¡AÀ³¸Ó¬O¦Û¤v¥Î¿ù¤èªk
¥i¥H½Ðª©¥D¥Ü½d¤@¤U¦p¦ó¥Î¦r¨å¤ÎArray¾Þ§@¶Ü?
¯u¤ß·Q¦Vª©¥D¾Ç²ß¡A·PÁÂ
Excel VBAºô­¶¸ê®Æ¦¬¶°±Ð¾Ç:
http://forum.twbts.com/thread-20848-1-1.html

TOP

¦^´_ 2# ­ã´£³¡ªL

·PÁª©¥D¥X¤â¨ó§U¡A¥[§Ö«Ü¦h¡A¸ê®Æ¤j¦h¤£·|¶W¹L¤dµ§©Ò¥HÀ³¸Ó³o¼Ë´N¥i¥H¤F¡A·PÁ¡I
Adam

TOP

¦^´_ 3# iamaraymond
  1. Sub ¤ñ¸û2()
  2. Dim Arr, Brr, xD, i&
  3. Set xD = CreateObject("Scripting.Dictionary")
  4. Arr = Range([day1!Q1], [day1!A65536].End(xlUp))
  5. For i = 2 To UBound(Arr)
  6.     xD(Arr(i, 2) & Arr(i, 5) & Arr(i, 14) & Arr(i, 15)) = Val(Arr(i, 8))
  7. Next i

  8. Arr = Range([day2!Q1], [day2!A65536].End(xlUp))
  9. ReDim Brr(1 To UBound(Arr), 1 To 2)
  10. Brr(1, 1) = "¬Q¤é": Brr(1, 2) = "®t²§"
  11. For i = 2 To UBound(Arr)
  12.     Brr(i, 1) = Val(xD(Arr(i, 2) & Arr(i, 5) & Arr(i, 14) & Arr(i, 15)))
  13.     If Val(Arr(i, 8)) > Brr(i, 1) Then Brr(i, 2) = "¼W¥["
  14. Next i

  15. [day2!R1:S1].Resize(UBound(Arr)) = Brr
  16. End Sub
½Æ»s¥N½X

TOP

¦^´_ 5# ­ã´£³¡ªL


    ªGµM¬O°ª¤â¡A§ó§Ö¤F¡A¤£ª¾­ã¤j¥­®É±q¨Æ¤°»ò¤u§@¡A«ç»ò·|¦³¦p¦¹²`«pªº¥\¤O¡A¤Ó±j¤j¤F
Adam

TOP

¦^´_ 5# ­ã´£³¡ªL

·PÁª©¥D¡A§Ú¦b¬ã¨s¬Ý¬Ý¡A¤@ª½³£¤£¤Óª¾¹D¦p¦ó¨Ï¥Î¦r¨å@@
Excel VBAºô­¶¸ê®Æ¦¬¶°±Ð¾Ç:
http://forum.twbts.com/thread-20848-1-1.html

TOP

¥»©«³Ì«á¥Ñ Andy2483 ©ó 2023-5-30 13:48 ½s¿è

¦^´_ 5# ­ã´£³¡ªL


    ÁÂÁ½׾Â,ÁÂÁ«e½ú
«á¾ÇÂǦ¹©«¾Ç²ß«e½úªº¤è®×,¤è®×¾Ç²ß¤ß±oµù¸Ñ¦p¤U,½Ð«e½ú¦A«ü¾É

°õ¦æ«e:


°õ¦æµ²ªG:



Sub ¤ñ¸û2()
Dim Arr, Brr, xD, i&
'¡ô«Å§iÅܼÆ
Set xD = CreateObject("Scripting.Dictionary")
'¡ô¥OxDÅܼƬO ¦r¨å
Arr = Range([day1!Q1], [day1!A65536].End(xlUp))
'¡ô¥OArrÅܼƬO ¤Gºû°}¦C,¥H "day1"ªíA~QÄæÀx¦s®æ­È±a¤J°}¦C¤¤
For i = 2 To UBound(Arr)
'¡ô³]¶¶°j°é
    xD(Arr(i, 2) & Arr(i, 5) & Arr(i, 14) & Arr(i, 15)) = Val(Arr(i, 8))
    '¡ô¥O²Ä(2,5,14,15)Äæ°}¦C­È²Õ¦¨ªº·s¦r¦ê·íkey,item¬O 8Äæ°}¦C­ÈÂà¼Æ­È
Next i
Arr = Range([day2!Q1], [day2!A65536].End(xlUp))
'¡ô¥OArr°}¦C´«¸Ë "day2"ªíA~QÄæÀx¦s®æ­È
ReDim Brr(1 To UBound(Arr), 1 To 2)
'¡ô«Å§iBrrÅܼƬO ¤GºûªÅ°}¦C,Áa¦V½d³ò¦PArr°}¦C,¾î¦V½d³ò1~2
Brr(1, 1) = "¬Q¤é": Brr(1, 2) = "®t²§"
'¡ô¥OBrr°}¦C²Ä1¦C¬O¼ÐÃD¦C
For i = 2 To UBound(Arr)
'¡ô³]¶¶°j°é!
    Brr(i, 1) = Val(xD(Arr(i, 2) & Arr(i, 5) & Arr(i, 14) & Arr(i, 15)))
    '¡ô¥OBrr²Ä1Äæ°j°é°}¦C­È¬O ¥H²Ä(2,5,14,15)Äæ°}¦C­È²Õ¦¨ªº·s¦r¦ê,¬d
    '¬dxD¦r¨å¦^¶Çitem­ÈÂà¼Æ­È

    If Val(Arr(i, 8)) > Brr(i, 1) Then Brr(i, 2) = "¼W¥["
    '¡ô¦pªG²Ä8ÄæArr°j°é°}¦C­ÈÂà¼Æ­È ¤j©ó Brr1Äæ°}¦C­È,
    '´N¥OBrr²Ä2Äæ°}¦C­È¥H "¼W¥["¦r¦ê ¼g¤J

Next i
[day2!R1:S1].Resize(UBound(Arr)) = Brr
'¡ô¥OBrr°}¦C­È±q "day2"ªí[R1]¶}©l¼g¤JÀx¦s®æ¤¤
End Sub
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

ÁÂÁ½׾Â,ÁÂÁ¦U¦ì«e½ú
«á¾ÇÂǦ¹©«¾Ç²ß¥H«e½úªº¤è®×§ï¦¨¤@­Ó°}¦C³B²zªº¤è®×,¾Ç²ß¤è®×¦p¤U,½Ð¦U¦ì«e½ú«ü±Ð

Sub TEST()
Dim Brr, Y, i&, Sh1 As Worksheet, Sh2 As Worksheet
'¡ô«Å§iÅܼÆ
Set Y = CreateObject("Scripting.Dictionary")
'¡ô¥OYÅܼƬO ¦r¨å
Set Sh1 = Sheets("day1"): Set Sh2 = Sheets("day2")
'¡ô¥OSh1ÅܼƬO ¦W¬° "day1"ªº¤u§@ªí,¥OSh2ÅܼƬO ¦W¬° "day2"ªº¤u§@ªí
Brr = Range(Sh1.[Q1], Sh1.[A65536].End(3))
'¡ô¥OBrrÅܼƬO ¤Gºû°}¦C,¥HSh1¤u§@ªíA~QÄæÀx¦s®æ­È±a¤J°}¦C¤¤
For i = 2 To UBound(Brr)
'¡ô³]¶¶°j°é
   Y(Brr(i, 2) & Brr(i, 5) & Brr(i, 14) & Brr(i, 15)) = Val(Brr(i, 8))
   '¡ô¥O²Ä(2,5,14,15)Äæ°}¦C­È²Õ¦¨ªº·s¦r¦ê·íkey,item¬O ²Ä8Äæ°}¦C­ÈÂà¼Æ­È
Next
Brr = Range(Sh2.[Q2], Sh2.[A65536].End(3))
'¡ô¥OBrr°}¦C´«¸Ë Sh1¤u§@ªíA~QÄæÀx¦s®æ­È(¤£§t¼ÐÃD¦C)
For i = 1 To UBound(Brr)
'¡ô³]¶¶°j°é
   Brr(i, 1) = Val(Y(Brr(i, 2) & Brr(i, 5) & Brr(i, 14) & Brr(i, 15)))
   '¡ô¥OBrr²Ä1Äæ°j°é°}¦C­È¬O ¥H²Ä(2,5,14,15)Äæ°}¦C­È²Õ¦¨ªº·s¦r¦ê,¬d
    '¬dY¦r¨å¦^¶Çitem­ÈÂà¼Æ­È(¥Hµ²ªG­ÈÂл\­ì°}¦C­È)

   If Val(Brr(i, 8)) > Brr(i, 1) Then Brr(i, 2) = "¼W¥[" Else Brr(i, 2) = ""
   '¡ô¦pªG²Ä8ÄæBrr°j°é°}¦C­ÈÂà¼Æ­È ¤j©ó Brr²Ä1Äæ°}¦C­È,
    '´N¥OBrr²Ä2Äæ°}¦C­È¥H "¼W¥["¦r¦ê ¼g¤J,§_«h´NÅýªÅ¥Õ

Next
Sh2.[R2].Resize(UBound(Brr), 2) = Brr: Sh2.[R1:S1] = [{"¬Q¤é","®t²§"}]
'¡ô¥OBrr°}¦C­È±qSh2ªí[R2]¼g¤JÀx¦s®æ¤¤,¥O[R1:S1]¼g¤J¦C¼ÐÃD
Set Y = Nothing: Set Sh1 = Nothing: Set Sh2 = Nothing: Erase Brr
'¡ô¥OÄÀ©ñÅܼÆ
End Sub
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

        ÀR«ä¦Û¦b : ¬°¤H³B¥@­n¤p¤ß²Ó¤ß¡A¦ý¤£­n¡u¤p¤ß²´¡v¡C
ªð¦^¦Cªí ¤W¤@¥DÃD