Hsieh

¦^´_ 5# cosplay123

­«ÂI¬O¸ê®ÆªºÃöÁp¦p¦ó§PÂ_?
½Ð¤W¶Ç²³æ½d¨Ò»¡©ú

Hsieh

¦^´_ 7# XDshining
D2°}¦C¤½¦¡
=IF(ROW(A1)>SUM(1/COUNTIF($A$2:$A$9,$A$2:$A$9)),"",INDEX($A:$A,MIN(IF(ISERROR(MATCH($A$2:$A$9,$D$1:D1,0)),ROW($A$2:$A$9),"")),))
¦V¤U½Æ»s
E1°}¦C¤½¦¡
=IF(COLUMN(A$1)>SUM(1/COUNTIF($B$2:$B$9,$B$2:$B$9)),"",INDEX($B:$B,MIN(IF(ISERROR(MATCH($B$2:$B$9,$D$1:D$1,0)),ROW($B$2:$B$9),""))))
¦V¥k½Æ»s
E2=(SUMPRODUCT(($A$2:$A$9&$B$2:$B$9=$D2&E$1)*1)>0)*1
¦V¥k¦V¤U½Æ»s

¤U¸ü (11.21 KB)

2012-3-12 23:39

VBA

1. Sub ex()
   
2. Set d = CreateObject("Scripting.Dictionary")
   
3. Set d1 = CreateObject("Scripting.Dictionary")
   
4. Set d3 = CreateObject("Scripting.Dictionary")
   
5. 
   
6. For Each a In Range([A2], [A65536].End(3))
   
7. d(a & a.Offset(, 1)) = a & a.Offset(, 1)
   
8. d1(a & "") = ""
   
9. d3(a.Offset(, 1) & "") = ""
   
10. Next
    
11. [d2].Resize(d1.Count, 1).Value = Application.Transpose(d1.keys)
    
12. [E1].Resize(, d3.Count) = d3.keys
    
13. For i = 2 To d1.Count + 1
    
14.   For j = 5 To d3.Count + 4
    
15.     Cells(i, j) = d.exists(Cells(i, 4) & Cells(1, j)) * (-1)
    
16.   Next
    
17. Next
    
18. End Sub

½Æ»s¥N½X