问题:如上图所示,按lon,lat分组,再进行转置。
VBA代码:
Sub admin()
Dim conn, xRs, xFd
Set conn = CreateObject("ADODB.Connection")
conn.Open "Provider=Microsoft.Jet.Oledb.4.0;" & _
"Extended Properties= 'Excel 8.0;hdr=yes;IMEX=1' ;" & _
"Data Source=" & ThisWorkbook.FullName
Set xRs = CreateObject("ADODB.RecordSet")
sSql = " Transform Sum( [Tas_t] ) Select [lon] , [lat] From [Sheet1$A:D] Group By [lon] , [lat] Pivot [Year] "
xRs.Open sSql, conn, 1, 3
i = 0
For Each xF