作者:乔山办公网日期:
返回目录:excel表格制作
以下为代码及注释知:
Sub main()
Set dic = CreateObject("scripting.dictionary") '定义词典
arr = Range("A1:C500") '假设最大行数为500,将A1至C500区域放入数组
For i = 1 To UBound(arr) '从1到数组最大行数循环
If arr(i, 1) <> "" And arr(i, 1) <> "装置" Then '提出空格和标题行道
dic(arr(i, 1)) = dic(arr(i, 1)) + arr(i, 3) '用字典去除重复,并且进行回数量累加
End If
Next i
[e2].Resize(dic.Count, 1) = Application.Transpose(dic.keys) '将字典的答keys(即A列不重复的值)转置并放在E列
[f2].Resize(dic.Count, 1) = Application.Transpose(dic.items) '将字典的kItems(即累加结果)转置并放在E列
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Address(0, 0) <> "B1" Then Exit Sub
Dim dic As Object
arr = Range([a1], [a65536].End(xlUp))
Set dic = CreateObject("scripting.dictionary")
For i = 1 To UBound(arr, 1)
dic(arr(i, 1)) = ""
Next
[a65536].End(xlUp).Offset(-dic.Count + 1, 1).Resize(dic.Count, 1) = Application.Transpose(dic.keys)
End Sub
右键工百作表度标知签》粘道贴内代码容
直接在记事本来里写:
dir /b *.* >1.txt
然后另存为1.bat,注意扩展名必须是 bat 或 cmd 。
双击运行,1.txt 里就是当前文件夹下源所有文件名。然后在excel里处理一知下,想怎么弄就怎么弄。
vba写的话,立即窗口里一句也能搞定:道
具体的路径你自己改就行。
用字袭典百法度知
Sub test()
Dim i%, j%
Sheets("表道A").select
Set d = CreateObject("Scripting.Dictionary")
For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row
d(Cells(i, j).Value) =""
Next
y = d.keys
With Sheets("表B")
For i = 0 To UBound(y)
.Cells(i + 1, "A") = y(i)
Next
End With
End Sub