- ©«¤l
- 1446
- ¥DÃD
- 40
- ºëµØ
- 0
- ¿n¤À
- 1470
- ÂI¦W
- 0
- §@·~¨t²Î
- Windows 7
- ³nÅ骩¥»
- Excel 2010 & 2016
- ¾\ŪÅv
- 50
- ©Ê§O
- ¨k
- ¨Ó¦Û
- ¥xÆW
- µù¥U®É¶¡
- 2020-7-15
- ³Ì«áµn¿ý
- 2024-10-21
|
¥»©«³Ì«á¥Ñ Andy2483 ©ó 2023-6-6 16:20 ½s¿è
ÁÂÁ½׾Â,ÁÂÁ¦U¦ì«e½ú
«á¾ÇÂǦ¹©«½m²ß°}¦C»P¦r¨å,¾Ç²ß¤è®×¦p¤U,½Ð¦U¦ì«e½ú«ü±Ð
¸ê®Æªí:
µ²ªGªí°õ¦æ«e:
°õ¦æµ²ªG:
Option Explicit
Sub TEST()
Dim Brr, Crr, Y, T$, T1$, V&, i&, Sh1 As Worksheet, Sh2 As Worksheet
'¡ô«Å§iÅܼÆ
Set Sh1 = Sheets("N90122Áʳf©ú²Ó¶K¤W"): Set Sh2 = Sheets("µÒ¬[")
'¡ô¥OÅܼƸˤJª«¥ó(¤u§@ªí)
Set Y = CreateObject("Scripting.Dictionary")
'¡ôYÅܼƬO ¦r¨å
Brr = Range(Sh1.[J1], Sh1.Cells(Rows.Count, 1).End(3))
'¡ô¥OBrrÅܼƬO ¤Gºû°}¦C,¥HA~JÀx¦s®æȱa¤J°}¦C¤¤
For i = 2 To UBound(Brr)
'¡ô³]¶¶°j°é
T1 = Brr(i, 1): T = Brr(i, 3): V = Brr(i, 6)
'¡ô¥OT1ÅܼƬO ²Ä1Äæ°}¦CÈ(¦r¦ê),¥OTÅܼƬO ²Ä3Äæ°}¦CÈ(¦r¦ê),
'¥OVÅܼƬO ²Ä6Äæ°}¦CÈ(¼ÆÈ)
If InStr(T1, "1") = 1 Then Y(T) = Y(T) + V / 10
'¡ô¦pªGT1ÅܼƲÄ1Ó¦r¬O 1?
'¬O´N¥O¦bY¦r¨å¸ÌªºTÅܼÆkey¨äitem²Ö¥[(VÅܼư£10)ªº¼ÆÈ
Next i
Brr = Range(Sh2.[B2], Sh2.Cells(Rows.Count, 1).End(3))
'¡ô¥OBrrÅܼƬO ¤Gºû°}¦C,´«¥HA~BÀx¦s®æȱa¤J°}¦C¤¤
ReDim Crr(1 To UBound(Brr), 1 To 1)
'¡ô¥OCrrÅܼƬO ¤GºûªÅ°}¦C,Áa¦V½d³ò¦PBrr°}¦C,¾î¦V1~1
For i = 1 To UBound(Brr)
'¡ô³]¶¶°j°é
Crr(i, 1) = Val(Y(Brr(i, 1) & Brr(i, 2)))
'¡ô¥OCrr°}¦CȬO ²Ä1ÄæBrr°}¦Cȳs±µ²Ä2ÄæBrr°}¦C²Õ¦¨ªº·s¦r¦ê,¬d
'¬dY¦r¨å±o¨ìªºitemȦAÂà¤Æ¬°¼ÆȪºÈ(¸É0)
Next i
Sh2.[E2].Resize(UBound(Crr)) = Crr
'¡ô¥Oµ²ªGªí±q[E2]¶}©l½d³òÀx¦s®æ¥HCrr°}¦Cȼg¤J
Set Y = Nothing: Erase Brr, Crr
'¡ô¥OÄÀ©ñÅܼÆ
End Sub |
|