Hsieh

¦^´_ 3# p6703
¦pªG¦³¤F°t¹ï²M³æ
C2=SUMPRODUCT((IF(COUNTIF(©ú²Ó!$B$2:$B$23,²Î­p!$B2),©ú²Ó!$B$2:$B$23,©ú²Ó!$C$2:$C$23)=$B2)*(©ú²Ó!$A$2:$A$23=²Î­p!$A2)*(©ú²Ó!$D$2:$D$23))

¦ý¬O¤½¦¡¨ú±o©Ò¦³°t¹ï²M³æ·|«Ü³Â·Ð¡A¥B·í¼Æ¶q¦h®É¥i¯à·|«ÜºC
ª½±µ¨Ï¥ÎVBA°õ¦æ·|¤ñ¸û«ê·í

1. Sub ex()
   
2. Set d = CreateObject("Scripting.Dictionary")
   
3. With Sheets("©ú²Ó")
   
4.   For Each a In .Range(.[A2], .[A2].End(xlDown))
   
5.      For Each b In a.Offset(, 1).Resize(, 2)
   
6.        If IsEmpty(d(a & b)) Then d(a & b) = Array(a, b, a.Offset(, 3))
   
7.      Next
   
8.   Next
   
9. End With
   
10. With Sheets("²Î­p")
    
11. .UsedRange.Offset(1) = ""
    
12. .[A2].Resize(d.Count, 3) = Application.Transpose(Application.Transpose(d.items))
    
13. End With
    
14. End Sub

½Æ»s¥N½X