是这样的,有两张txt格式的表
表一(a.txt)
编号|名称|星级|总数|数量一|数量二|数量三|数量四|
EDFS01a001|(EDFS01a001)佛山市石湾区张槎通德电讯经营部|4|651|464|0|0|187|
EDFS01a002|(EDFS01a002)佛山市禅城区金网通电讯经营部|3|223|174|0|0|49|
EDFS01a003|(EDFS01a003)广东省佛山市禅城区凯福电讯经营部|1|34|23|0|0|11|
EDFS01a004|(EDFS01a004)佛山中宏移动通讯配件有限公司(佛山大道店)|1|57|30|0|0|27|*注 总数=数量一+数量二+数量三+数量四表二(aaa.txt)
EDFS01a001 西区
EDFS01a002 东区
EDFS01a003 东区
EDFS01a004 西区需求:要实现的功能是将数据从txt文件提取出来然后填入如下excel表格中: 总量汇总 数量一汇总 数量二汇总 数量三汇总 数量四汇总
东部
西部
*注 数量一汇总 等于 数量一 的列相加总和;数量二汇总 等于 数量二 的列相加总和;如此类推已经实现的代码如下,但是得出的结果不争取。请高手帮忙修改。
Sub test3() Dim st As String
Dim st1, i As Integer
Dim k As Integer
Dim p As String
Dim ctk As Integer '数量一
Dim lyz As Integer '数量二
Dim dzk As Integer '数量三
Dim dgdd As Integer '数量四
Dim zs As Integer '总数
Dim txtline
Dim kk As String
Dim pp As String
'读入a到Grid1
Open ThisWorkbook.Path & "\a.txt" For Input As #1
Do Until EOF(1)
Line Input #1, txtline
st = st & txtline & vbCrLf
Loop
Close #1
z = 0
S = 0
Row_l = Split(st, vbCrLf)
For i = 0 To UBound(Row_l)
Col_L = Split(Row_l(i), "|")
For t = 0 To UBound(Col_L)
If z = 0 Then
ReDim Grid1(UBound(Row_l), UBound(Col_L))
z = 1
End If
Grid1(i, t) = Col_L(t)
Next t
Next i
'读入aa到Grid2
Open ThisWorkbook.Path & "\aa.txt" For Input As #1
Do Until EOF(1)
Line Input #1, txtline
st1 = st1 & txtline & vbCrLf
Loop
Close #1
z = 0
S = 0
Row_l = Split(st1, vbCrLf)
For i = 0 To UBound(Row_l)
Col_L = Split(Row_l(i), Chr(9))
For t = 0 To UBound(Col_L)
If z = 0 Then
ReDim Grid2(UBound(Row_l), UBound(Col_L))
z = 1
End If
Grid2(i, t) = Col_L(t)
Next t
Next i
Debug.Print Grid2(0, 1)
Dim m As Integer
Dim n As Integer
Dim j As Integer
Dim f As Integer
'上面的都没问题
'这段代码是用来区分东西区的,问题就是在这里,如何把数据累加到东西区的各项中去?不太懂,请高手帮忙修改下,谢谢!
For i = 0 To UBound(Grid1)
For j = 0 To UBound(Grid2)
If Grid1(i, 0) = Grid2(j, 0) Then
If Grid2(j, 1) = "东区" Then
For m = 0 To UBound(Grid1)
zs = zs1 + Grid1(m, 3)
ctk = ctk1 + Grid1(m, 4)
lyz = lyz1 + Grid1(m, 5)
dzk = dzk1 + Grid1(m, 6)
dgdd = dgdd1 + Grid1(m, 7)
Cells(10, 2) = zs
Cells(10, 3) = ctk
Cells(10, 4) = dgdd
Cells(10, 5) = lyz
Cells(10, 6) = dzk
Next
Else
For n = 0 To UBound(Grid1)
zs = zs1 + Grid1(n, 3)
ctk = ctk1 + Grid1(n, 4)
lyz = lyz1 + Grid1(n, 5)
dzk = dzk1 + Grid1(n, 6)
dgdd = dgdd1 + Grid1(n, 7)
Cells(11, 2) = zs
Cells(11, 3) = ctk
Cells(11, 4) = dgdd
Cells(11, 5) = lyz
Cells(11, 6) = dzk
Next
End If
Exit For
End If
Next j
Next i
Sheets(1).Cells.EntireColumn.AutoFit
End Sub
表一(a.txt)
编号|名称|星级|总数|数量一|数量二|数量三|数量四|
EDFS01a001|(EDFS01a001)佛山市石湾区张槎通德电讯经营部|4|651|464|0|0|187|
EDFS01a002|(EDFS01a002)佛山市禅城区金网通电讯经营部|3|223|174|0|0|49|
EDFS01a003|(EDFS01a003)广东省佛山市禅城区凯福电讯经营部|1|34|23|0|0|11|
EDFS01a004|(EDFS01a004)佛山中宏移动通讯配件有限公司(佛山大道店)|1|57|30|0|0|27|*注 总数=数量一+数量二+数量三+数量四表二(aaa.txt)
EDFS01a001 西区
EDFS01a002 东区
EDFS01a003 东区
EDFS01a004 西区需求:要实现的功能是将数据从txt文件提取出来然后填入如下excel表格中: 总量汇总 数量一汇总 数量二汇总 数量三汇总 数量四汇总
东部
西部
*注 数量一汇总 等于 数量一 的列相加总和;数量二汇总 等于 数量二 的列相加总和;如此类推已经实现的代码如下,但是得出的结果不争取。请高手帮忙修改。
Sub test3() Dim st As String
Dim st1, i As Integer
Dim k As Integer
Dim p As String
Dim ctk As Integer '数量一
Dim lyz As Integer '数量二
Dim dzk As Integer '数量三
Dim dgdd As Integer '数量四
Dim zs As Integer '总数
Dim txtline
Dim kk As String
Dim pp As String
'读入a到Grid1
Open ThisWorkbook.Path & "\a.txt" For Input As #1
Do Until EOF(1)
Line Input #1, txtline
st = st & txtline & vbCrLf
Loop
Close #1
z = 0
S = 0
Row_l = Split(st, vbCrLf)
For i = 0 To UBound(Row_l)
Col_L = Split(Row_l(i), "|")
For t = 0 To UBound(Col_L)
If z = 0 Then
ReDim Grid1(UBound(Row_l), UBound(Col_L))
z = 1
End If
Grid1(i, t) = Col_L(t)
Next t
Next i
'读入aa到Grid2
Open ThisWorkbook.Path & "\aa.txt" For Input As #1
Do Until EOF(1)
Line Input #1, txtline
st1 = st1 & txtline & vbCrLf
Loop
Close #1
z = 0
S = 0
Row_l = Split(st1, vbCrLf)
For i = 0 To UBound(Row_l)
Col_L = Split(Row_l(i), Chr(9))
For t = 0 To UBound(Col_L)
If z = 0 Then
ReDim Grid2(UBound(Row_l), UBound(Col_L))
z = 1
End If
Grid2(i, t) = Col_L(t)
Next t
Next i
Debug.Print Grid2(0, 1)
Dim m As Integer
Dim n As Integer
Dim j As Integer
Dim f As Integer
'上面的都没问题
'这段代码是用来区分东西区的,问题就是在这里,如何把数据累加到东西区的各项中去?不太懂,请高手帮忙修改下,谢谢!
For i = 0 To UBound(Grid1)
For j = 0 To UBound(Grid2)
If Grid1(i, 0) = Grid2(j, 0) Then
If Grid2(j, 1) = "东区" Then
For m = 0 To UBound(Grid1)
zs = zs1 + Grid1(m, 3)
ctk = ctk1 + Grid1(m, 4)
lyz = lyz1 + Grid1(m, 5)
dzk = dzk1 + Grid1(m, 6)
dgdd = dgdd1 + Grid1(m, 7)
Cells(10, 2) = zs
Cells(10, 3) = ctk
Cells(10, 4) = dgdd
Cells(10, 5) = lyz
Cells(10, 6) = dzk
Next
Else
For n = 0 To UBound(Grid1)
zs = zs1 + Grid1(n, 3)
ctk = ctk1 + Grid1(n, 4)
lyz = lyz1 + Grid1(n, 5)
dzk = dzk1 + Grid1(n, 6)
dgdd = dgdd1 + Grid1(n, 7)
Cells(11, 2) = zs
Cells(11, 3) = ctk
Cells(11, 4) = dgdd
Cells(11, 5) = lyz
Cells(11, 6) = dzk
Next
End If
Exit For
End If
Next j
Next i
Sheets(1).Cells.EntireColumn.AutoFit
End Sub
另一方法是把表一作为数据源用sql来根据编号汇总。