EXCEL運用函數和VBA對多行多列求唯壹值和重復值的方法

假如妳的數據內容在sheet1表中,那就在sheet1表中建壹個按鈕(commandbutton),雙擊把以下代碼復制進去,點下按鈕,sheet2的A列就會顯示sheet1中各單元格出現過的內容,B列就會顯示對應出現的次數,就算滿表6萬行都是數據,1分鐘內出結果.

Private?Sub?CommandButton1_Click()

On?Error?Resume?Next

Dim?dic?As?Object

Dim?Rng?As?Range

Set?dic?=?CreateObject("scripting.dictionary")

For?Each?Rng?In?ActiveSheet.UsedRange

If?Rng?<>?""?Then

dic(Rng.Value)?=?dic(Rng.Value)?+?1

End?If

Next

With?ThisWorkbook.Worksheets("sheet2")

.Columns("a:b").ClearContents

.Range("a1").Resize(dic.Count,?1)?=?Application.Transpose(dic.keys)

.Range("b1").Resize(dic.Count,?1)?=?Application.Transpose(dic.items)

End?With

End?Sub