|
楼主 |
发表于 2015-12-4 09:46
|
显示全部楼层
本帖最后由 autumnalRain 于 2015-12-30 11:58 编辑
第三个:这个例子因为数据不够规范,比如同列>>数据类型不同,数据稀疏NULL值等 ,所以用VBA SQL需要重新加工数据。略过此法- Sub 装入字典()
- Dim r As Integer
- Set d = CreateObject("scripting.dictionary")
- r = 2
- With Sheet1
- Do While .Cells(r, 1) <> ""
- If .Cells(r, 1).Value <> "小计:" Then
- d(.Cells(r, 1).Value) = ""
- End If
- r = r + 1
- Loop
- End With
- End Sub
复制代码- Sub 写行列标题()
- Sheets.Add after:=Sheet1
- ActiveSheet.Name = "最终格式"
- [a1:f1] = Array("设备号码", "固定电话月租费", "来电显示", "区内通话费", "区间通话费", "传统国内长途费")
- [a2].Resize(d.Count) = Application.Transpose(d.keys)
- End Sub
复制代码- Sub 写入数据()
- Dim arr, brr, dic As Object, i&, j&
- Set dic = CreateObject("scripting.dictionary")
- arr = Sheet1.Range("a1").CurrentRegion '原始数据
- brr = Sheets("最终格式").Range("a1").CurrentRegion '目标数据
- For i = 2 To UBound(arr, 1)
- If arr(i, 1) <> "小计:" Then
- dic(arr(i, 1) & "|" & arr(i, 2)) = arr(i, 3)
- End If
- Next
- 'Sheet1.Range("A40").Resize(UBound(dic.keys) + 1, 2) = Application.Transpose(Array(dic.keys, dic.items))
- For i = 2 To UBound(brr, 1)
- For j = 2 To UBound(brr, 2)
- brr(i, j) = dic(brr(i, 1) & "|" & brr(1, j))
- Next
- Next
- Sheets("最终格式").Range("a1").Resize(UBound(brr), UBound(brr, 2)) = brr
- End Sub
复制代码
|
评分
-
1
查看全部评分
-
|