有一组数据,经过SQL处理后,
在MSHFLEXGRIDE中的表现格式为: A1 A2 A3 A4B1 DD1 DD1 DD1 DD1
B2 DD2 DD2 DD2 DD2CSDN不能贴图,目标需求见http://club.excelhome.net/viewthread.php?tid=570204&pid=3807087&page=1&extra=page%3D1数据源特点
HG20594 150 0.6
HG20594 200 0.6
HG20594 250 0.6
HG20594 300 0.6
HG20594 10 1
HG20594 15 1
HG20594 20 1HG20593 800 0.25
HG20593 900 0.25
HG20593 1000 0.25
HG20593 10 0.6
HG20593 15 0.6如何哪位在ExcelHome已经注册,可以直接下载数据.谢谢.
只有24分,还需要每天回帖.不然连发帖的资格都没有.
在MSHFLEXGRIDE中的表现格式为: A1 A2 A3 A4B1 DD1 DD1 DD1 DD1
B2 DD2 DD2 DD2 DD2CSDN不能贴图,目标需求见http://club.excelhome.net/viewthread.php?tid=570204&pid=3807087&page=1&extra=page%3D1数据源特点
HG20594 150 0.6
HG20594 200 0.6
HG20594 250 0.6
HG20594 300 0.6
HG20594 10 1
HG20594 15 1
HG20594 20 1HG20593 800 0.25
HG20593 900 0.25
HG20593 1000 0.25
HG20593 10 0.6
HG20593 15 0.6如何哪位在ExcelHome已经注册,可以直接下载数据.谢谢.
只有24分,还需要每天回帖.不然连发帖的资格都没有.
看了<SQL基本语法教程>,目标需求没有实现.
Sql = "Select Distinct iif(Pn = 0.25, Dn),"
Sql = Sql & " iif(Pn = 1, Dn),"
Sql = Sql & " iif(Pn = 1.6, Dn),"
Sql = Sql & " iif(Pn = 2.5, Dn)"但结果是,数据错位.
0.25 1.0 1.6 2.5
10
20
..
10
20
10
20
10
20目标需求
0.25 1.0 1.6 2.5
10 10 10 10
20 20 20 20
图示效果详见
http://club.excelhome.net/thread-570204-1-1.html
需要注册,csdn也不能传附件,没法说清.晕.
这样是否能说清楚,
有一组数据,有两个字段,A字段为要处理数据的字段,B字段为条件判断字段.根据条件 将A字段,重新组织成4个字段.
B字段中 = 0.25的放到A-A1字段
B字段中 = 1.0的放到A-A2字段
B字段中 = 1.6的放到A-A3字段
B字段中 = 2.5的放到A-A4字段用iif语句-----结果是错位.-------
现有采用这种方法,目标实现,但响应速度太慢.
Sql = "Select Distinct format(Pn,'0.0#') From [主页面$] Where oName = 'HG20593'"
Set Rst = fileConnectRst(Sql, FileName) ''
'' 循环第一个Rst--SQL处理上表中的DD1-DD2
For jj = 0 To Rst.RecordCount - 1 Sql = "Select Distinct Dn From [主页面$] Where oName = 'HG20593' and Pn = " & Rst.Fields(0)
For kk = 0 To Rst1.RecordCount - 1
再将rst1所得数据通过循环,赋值组MshFlexGrid
Next jj
Function ConnectRst(Sql As String) As ADODB.Recordset 'Thisworkbook.FullName连接Rst
Dim Cnn As New ADODB.Connection
Dim Rst As New ADODB.Recordset
Cnn.Open "provider=microsoft.jet.oledb.4.0;extended properties='excel 8.0;imex=1';data source=" & ThisWorkbook.FullName
Rst.Open Sql, Cnn, adOpenStatic
Set ConnectRst = Rst
End FunctionFunction HGGrid()
''
Dim Sql As String, FileName, Str
Dim Rst As ADODB.Recordset, Rst1 As ADODB.Recordset
Dim oTypeArr(2)
Dim Sht As Worksheet
Set Sht = Sheets("主页面") ''
With Form2
Sql = "Select Distinct format(Pn,'0.0#') From [主页面$] Where oName = 'HG20593'"
Set Rst = ConnectRst(Sql)
''
Rst.MoveFirst
cc = 6
rr = 2
For jj = 0 To Rst.RecordCount - 1
Sht.Cells(1, cc + jj) = Rst.Fields(0)
Sql = "Select Distinct Dn From [主页面$] Where oName = 'HG20593' and Pn = " & Rst.Fields(0)
Set Rst1 = ConnectRst(Sql) ''
Rst1.MoveFirst
For kk = 0 To Rst1.RecordCount - 1
Sht.Cells(rr + kk + 1, cc + jj) = Rst1.Fields(0)
Rst1.MoveNext
Next kk
Rst.MoveNext
Next jj
End With
End Function
再读组织好的数据,速度就快了.