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

­pºâ¤£­«½Æ¸ê®Æ¥X²{¦¸¼Æ [¤w¸Ñ¨M]

­pºâ¤£­«½Æ¸ê®Æ¥X²{¦¸¼Æ [¤w¸Ñ¨M]

¥»©«³Ì«á¥Ñ loyyee ©ó 2011-9-17 00:06 ½s¿è

½Ð°Ý­n¦p¦ó¥Î¥¨¶°­pºâ¤U¦C¤£­«½Æ¸ê®Æ¥X²{¦¸¼Æ?
         A             B
1  0001        A1
2  0001        A2
3  0001        A2
4  0001        A3
5  0002        A1
6  0002        B1
7  0002        B1
8  0002        B1

±o¨ìµ²ªG¡G0001¥X²{¤£­«½Æ¦¸¼Æ=3¡B0002¥X²{¤£­«½Æ¦¸¼Æ=2

¥»©«³Ì«á¥Ñ dechiuan999 ©ó 2011-9-4 19:41 ½s¿è

§A¦n¡G
  ½Ð°Ý§Aªº­pºâ°ò¦¬°¦ó©O¡H
¦pªG¥H0002¥X²{¤£­«ÂЦ¸¼Æ¬°=2®É¡A
·Q¥²¬O¥HA¤ÎB¦X¨Ö¬°KEY­È¡A
0002A1 ¤@¦¸
0002B1 ¤T¦¸¡A¤£­«ÂЦ¸¼Æ¬°¤G
¨º0001À³¬°1¦¸¤~¦X²z¡C
0001¦p¦ó­pºâ¤£­«ÂЦ¸¼Æ¬°=3©O¡H
¦pªG¤W­z±À²z¥¿½T®É¡A½Ð¸Õ¸Õ¤U¦C»yªk
Sub aa()
   
    '½Ð¥ý¥Ñ¤u¨ã¦C¤Þ¥ÎMicrosoft Scripting Rungtime
   
    Dim mDic As Scripting.Dictionary
    Dim mSht As Worksheet
    Dim mRng As Range, mRng1 As Range
    Dim E As Range
    Dim mTmp, mVal
    Dim i%
   
    Set mDic = CreateObject("scripting.dictionary")
    Set mSht = Worksheets(1)
    With mSht
        Set mRng = .Range("a1", Range("a" & .Rows.Count).End(xlUp))
            For Each E In mRng
                If Not E.Value = Empty Then
                    If Not mDic.Exists(E.Value & "_" & E.Offset(, 1).Value) Then
                        mDic(E.Value & "_" & E.Offset(, 1).Value) = 1
                        
                    Else
                        mDic(E.Value & "_" & E.Offset(, 1).Value) = mDic(E.Value & "_" & E.Offset(, 1).Value) + 1
                    End If
                    
                End If
            Next
            
            For Each E In mRng
                E.Offset(, 3).Value = E.Value & "_" & E.Offset(, 1).Value
            Next
            
            mKey = mDic.Keys
         
            For i = LBound(mKey) To UBound(mKey)
               mVal = mDic(mKey(i))
               If mVal > 1 Then
                  Set mRng1 = mSht.Columns(4).Find(what:=mKey(i), lookat:=xlWhole, searchorder:=xlByRows, searchdirection:=xlNext)
                  If Not mRng1 Is Nothing Then
                    mRng1.Offset(, -1).Value = mVal - 1
                  End If
                End If
            Next
            .Columns(4).ClearContents            
    End With            
End Sub

TOP

¦^´_ 2# dechiuan999


    ±z¦n¡A©êºp¨S»¡²M·¡¡AA.BÄæ¦ì¬O­n¦X¨Ö°_¨Ó¬Ý¤~¹ï¡C
0001 ¥X²{:A1.A2.A3=3¦¸

TOP

  1. Sub nn()
  2. Set d = CreateObject("Scripting.Dictionary")
  3. For Each a In Range([A1], [A1].End(xlDown))
  4.   If IsEmpty(d(a.Value)) Then
  5.      d(a.Value) = a.Offset(, 1)
  6.   ElseIf IsError(Application.Match(a.Offset(, 1), Split(d(a.Value), ","), 0)) Then
  7.      d(a.Value) = d(a.Value) & "," & a.Offset(, 1)
  8.   End If
  9. Next
  10. For Each ky In d.keys
  11.   d(ky) = Array(ky, UBound(Split(d(ky), ",")) + 1)
  12. Next
  13. [C1].Resize(d.Count, 2) = Application.Transpose(Application.Transpose(d.items))
  14. End Sub
½Æ»s¥N½X
¦^´_ 3# loyyee
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 4# Hsieh

·PÁÂHsiehµ{¦¡¥i¥H¥Î¤F¡C
¦A½Ð°Ý¤@¤U¦p¦ó¦b F3Àx¦s®æ¥[¤Jµù¸ÑÅã¥Ü­pºâ¥X¨Óªºµ²ªG¡G

µù¸Ñ¤º®e¦p¤U~

0001 ¦¸¼Æ=3
0002 ¦¸¼Æ=2

µ§¼Æ¡G2 (¦]¥X²{¡G0001¡B0002¤Gµ§¸ê®Æ)

TOP

¦^´_ 5# loyyee
  1. Sub nn()
  2. Set d = CreateObject("Scripting.Dictionary")
  3. For Each a In Range([A1], [A1].End(xlDown))
  4.   If IsEmpty(d(a.Value)) Then
  5.      d(a.Value) = a.Offset(, 1)
  6.   ElseIf IsError(Application.Match(a.Offset(, 1), Split(d(a.Value), ","), 0)) Then
  7.      d(a.Value) = d(a.Value) & "," & a.Offset(, 1)
  8.   End If
  9. Next
  10. For Each ky In d.keys
  11.   d(ky) = Array(ky, UBound(Split(d(ky), ",")) + 1)
  12. If mystr = "" Then
  13.   mystr = Join(d(ky), "¦¸¼Æ=")
  14.   Else
  15.   mystr = mystr & Chr(10) & Join(d(ky), "¦¸¼Æ=")
  16. End If
  17. Next
  18. mystr = mystr & Chr(10) & "µ§¼Æ= " & d.Count & "(¦]¥X²{ : " & Join(d.keys, "¡B") & Application.Text(d.Count, "[DBNum1]") & "µ§¸ê®Æ)"
  19. [C:E] = ""
  20. [C1].Resize(d.Count, 2) = Application.Transpose(Application.Transpose(d.items))
  21. [C1].Offset(d.Count, 3) = mystr
  22. End Sub
½Æ»s¥N½X
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

·PÁÂHsiehµ{¦¡¥i¥H¥Î¤F¡C

TOP

        ÀR«ä¦Û¦b : ¬Ý§O¤H¤£¶¶²´¡A¬O¦Û¤v­×¾i¤£°÷¡C
ªð¦^¦Cªí ¤W¤@¥DÃD