Excel：分页显示大数据，原来方法这么多
1、01，函数法先来看看数据格式
3、最大页码如果判断？中心思想：总行数 / 每页条数-----这个数值，如果是小数，我都想上舍入取整数就是12.5页，我显示13页总行数= COUNTA(数据!A:A像粜杵泳)-1 COUNTA(数据!A:A) ----包换表头的总数据条数COUNTA(数据!A:A)-1 ----就是去掉表头后的数据总条数每页条数=K2单元格向上舍入小数点，用函数： ROUNDUP(数字，小数位数)这里最后公式：=IFERROR(ROUNDUP((COUNTA(数据!A:A)-1)/K2,0),1)用了一个IFERROR函数，容错也显示1页
5、知道了位置，用什么函数来返回结果？MATCH脑栲葱蛸函数语法：INDEX(数组或范围，在数组和范围里行的位置，在数组和范围里列的位置像粜杵泳)一参数范围：实际数据范围，注意绝对锁定二参数，行号，就是编号+1三参数列，就是从1开始到3的数字过程函数：INDEX(数据!$A$1:$C$1000,编号+1,COLUMN(A1))结果：INDEX(数据!$A$1:$C$1000, ($G$2-1)*$K$2+1+1,COLUMN(A1))▍这个公式，是可以得到第一条数据结构，我需要根据下拉，得到正确结果只要下拉行数编号，要使用ROW函数语法：ROW(单元格) 返回的是单元格行数我这里写入ROW(A1)，通过下拉，是里面A1变化为B1，C1，D1，E1，得到1，2，3，4结果还要限制条数，这里用IF函数来判断，只要超过条数，就显示空，让函数出错最终这部分函数：IF(ROW(A1)<=$K$2,ROW(A1)-1,"")
7、02，VBA（单元格方法）先看效果，VBA单元格方法，会比函数还简单，数据真正多的时候，反而更快
9、通过这个属性，很代码很容易就写出来了Sub 单元格办法猾诮沓靥() Dim rng As Range Dim lngPages As Long '页数 Dim lngNum As Long '每页条数 Dim lngRow As Long '第一个数值行 Dim lngCol As Long '总数据列数 '------------------下面是程序开始部分------------- lngPages = Range("I2").Value 'I2单元格值 lngNum = Range("M2").Value 'M2单元格值 '函数部分学习，知道编号+1就是行号 lngRow = (lngPages - 1) * lngNum + 1 + 1 '数据最大列数 lngCol = Sheets("数据").Cells(1, Columns.Count).End(xlToLeft).Column '清空原始数据 Range("b3:d65536").ClearContents '取出那一块的数据 Range("b3").Resize(lngNum, lngCol).Value = _ Sheets("数据").Cells(lngRow, 1).Resize(lngNum, lngCol).ValueEnd Sub
11、通过上下箭头，点击改变页码代码调用的是开发工具里的，ACTIVEX控件
13、写入代码，来控制上下箭头微调页码Private Sub ScrollBar1_Change() With Sheet4.ScrollBar1 .LinkedCell = "I2" '连接到I2单元格里 .Min = 1 '最小值是1 .Max = Range("K2").Value '最大值是K2单元格值 End With Call 单元格办法 '调用核心代码End Sub
15、详细分析一下，代码Sub SQL方法2() Dim cn As Object, rs As Object Dim sql1 As String, sql2$ Dim n As Long Dim i As Long Dim k As Long Set cn = CreateObject("Adodb.Connection") With cn .Provider = "Microsoft.Ace.Oledb.12.0;Extended Properties=Excel 12.0" .Open ThisWorkbook.FullName End With '设置参数 With Sheet1.ScrollBar1 .Min = 1 .Max = Sheet1.Range("K2").Value End With n = Sheet1.Range("M2").Value '每页条数 k = Sheet1.Range("I2").Value '页码 If k > 1 Then '页码大于1页的时候 '这个是算已经翻页的编号都有那些 sql1 = "select top " & n * (k - 1) & " 编号 from [数据$]" '这个是总表和已经翻页编号比较，合成一个新表，这个表四个字段 sql2 = "select a.编号,a.学校,a.学员,a.学费,b.编号 as tempcolum from [数据$] a left join (" _ & sql1 & ") b on a.编号 = b.编号" '通过判断第四个字段是空，来达到找到去掉已经翻页数据的目的' sql2 = "select c.编号,c.学校,c.学员,c.学费 from (" & sql2 & ") c where c.tempcolum is null" '取上面新数据前N条数据' sql2 = "select top " & n & " 编号,学校,学员,学费 from (" & sql2 & ")" Else '页码=1页的时候执行 '取每页条数的数据，就是1-N条数据 sql2 = "select top " & n * k & " 编号,学校,学员,学费 from [数据$]" End If '拿RS数据 Set rs = cn.Execute(sql2) '关闭屏幕刷新 Application.ScreenUpdating = False '清除之前结果 Range("a1:e65536").ClearContents '得到表头 For i = 0 To rs.Fields.Count - 1 Cells(2, i + 2).Value = rs.Fields(i).Name Next i '把结果复制出来到单元格里 Range("B3").CopyFromRecordset rs cn.Close: Set cn = Nothing Application.ScreenUpdating = TrueEnd Sub
17、sql2 = "select a.编号,a.学校,a.学员,a.学费,b.编号 as tempcolum from [数据$] a left join (" _ & sql1 & ") b on a.编号 = b.编号"得到的是一个五列的表，是和前面翻页编号比较的表
19、●sql2 = "select top " & n & " 编号,学校,学员,学费 from (" & sql2 & ")"取前N条数据
