分页显示大数据，原来方法这么多，快来学几个

有没有像网页预览那样，一个页面只显示固定条数，可以翻页呢?--雨夜又遇到问题了。别着急，我给你来三个方法，搞定这个事情，从简单公式，到代码到数据库语言SQL方法，今天全部给你分析一遍

我有一个表，有几十万行数据，但是我其实只想看一部分，怎么办呢?

有没有像网页预览那样，一个页面只显示固定条数，可以翻页呢?--雨夜又遇到问题了

别着急，我给你来三个方法，搞定这个事情，从简单公式，到代码到数据库语言SQL方法，今天全部给你分析一遍

01 函数法

先来看看数据格式

我现在要格式，通过右边页码，自定义条数，来控制显示条数，效果如图

(函数方法效果演示)

最大页码如果判断?

中心思想：总行数 / 每页条数-----这个数值，如果是小数，我都想上舍入取整数

就是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页

思考规律，如何判断第一个要显示的编号是什么?

第一个显示的编号是：(页码-1)*每页条数+1

最后公式：=($G$2-1)*$K$2+1 (注意绝对引用，防止拖拽的时候改变)

这个编号，和数据的行数，是不是有什么关系?

编号+1就是实际数据的行数

这里为了方便理解，给了辅助列，没有，把编号想象成行号来操作

知道了位置，用什么函数来返回结果?

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,"")

最终结果，结果部分拼一起，加一个容错函数IFERROR

=IFERROR(INDEX(数据!$A$1:$C$1000,($G$2-1)*$K$2+1+1+IF(ROW(A1)<=$K$2,ROW(A1)-1,""),COLUMN(A1)),"")

单元格右拉和下拉，就可以完成函数部分设定

02 VBA(单元格方法)

先看效果，VBA单元格方法，会比函数还简单，数据真正多的时候，反而更快

(VBA显示效果)

分析相关参数和办法

• 通过函数方法，我们已经知道，通过编号我们直接就知道，数据单元格位置：编号+1
• 不同页码，显示第一个编号=(页码-1)*每页条数+1
• VBA有单元格RESIZE属性，表示截取一段指定数据区域
• 语法：单元格.RESIZE(范围行数，范围列数)
• 范围行数=每页条数
• 范围列数=数据总列数

通过这个属性，很代码很容易就写出来了

Sub单元格办法()
DimrngAsRange
DimlngPagesAsLong'页数
DimlngNumAsLong'每页条数
DimlngRowAsLong'第一个数值行
DimlngColAsLong'总数据列数
'------------------下面是程序开始部分-------------
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).Value
EndSub

代码部署，通过单元格值改变事件，达到改变页数和每页条数，属性数据目的

写到制定工作表里

PrivateSubWorksheet_Change(ByValTargetAsRange)
'判断只有I2和M2两个单元格改变才执行代码
IfTarget.Address(0,0)="I2"OrTarget.Address(0,0)="M2"Then
Application.EnableEvents=False'关闭Worksheet_Change事件

Call 单元格办法 '调用代码

Application.EnableEvents=True'打开Worksheet_Change事件
EndIf
EndSub

通过上下箭头，点击改变页码代码

调用的是开发工具里的，ACTIVEX控件

放入单元格位置后，在设计模式下，右键-插卡代码

写入代码，来控制上下箭头微调页码

PrivateSubScrollBar1_Change()
WithSheet4.ScrollBar1
.LinkedCell="I2"'连接到I2单元格里
.Min=1'最小值是1
.Max=Range("K2").Value'最大值是K2单元格值
EndWith
Call单元格办法'调用核心代码
EndSub

03 数据库语言SQL办法

显示效果和02部分VBA代码是一样的

这里分析下SQL部分解决思路

我是可以通过”SELECT TOP 5 * FROM [数据$]”这个SQL语句拿到前5条数据

这里我们根据变页数和条数，控制TOP后面数据，总数据去掉已经翻页的数据，再去固定每页条数TOP数据

详细分析一下，代码

SubSQL方法2()
DimcnAsObject,rsAsObject
Dimsql1AsString,sql2$
DimnAsLong
DimiAsLong
DimkAsLong
Setcn=CreateObject("Adodb.Connection")
Withcn
.Provider="Microsoft.Ace.Oledb.12.0;ExtendedProperties=Excel12.0"
.OpenThisWorkbook.FullName
EndWith

'设置参数

WithSheet1.ScrollBar1
.Min=1
.Max=Sheet1.Range("K2").Value
EndWith
n=Sheet1.Range("M2").Value'每页条数
k=Sheet1.Range("I2").Value'页码
Ifk>1Then'页码大于1页的时候

'这个是算已经翻页的编号都有那些

sql1="selecttop"&n*(k-1)&"编号from[数据$]"

'这个是总表和已经翻页编号比较，合成一个新表，这个表四个字段

sql2="selecta.编号,a.学校,a.学员,a.学费,b.编号astempcolumfrom[数据$]aleftjoin("_
&sql1&")bona.编号=b.编号"

'通过判断第四个字段是空，来达到找到去掉已经翻页数据的目的

'sql2="selectc.编号,c.学校,c.学员,c.学费from("&sql2&")cwherec.tempcolumisnull"

'取上面新数据前N条数据

'sql2="selecttop"&n&"编号,学校,学员,学费from("&sql2&")"

Else '页码=1页的时候执行

'取每页条数的数据，就是1-N条数据

sql2="selecttop"&n*k&"编号,学校,学员,学费from[数据$]"
EndIf

'拿RS数据

Setrs=cn.Execute(sql2)

'关闭屏幕刷新

Application.ScreenUpdating=False

'清除之前结果

Range("a1:e65536").ClearContents

'得到表头

Fori=0Tors.Fields.Count-1
Cells(2,i+2).Value=rs.Fields(i).Name
Nexti

'把结果复制出来到单元格里

Range("B3").CopyFromRecordsetrs
cn.Close:Setcn=Nothing
Application.ScreenUpdating=True
EndSub

解释下几个SQL语句

sql1="selecttop"&n*(k-1)&"编号from[数据$]"

得到的是已经翻页编号数据

(得到是左侧一个表，表示根据页码显示，已经翻页的编号)

sql2="selecta.编号,a.学校,a.学员,a.学费,b.编号astempcolumfrom[数据$]aleftjoin("_
&sql1&")bona.编号=b.编号"

得到的是一个五列的表，是和前面翻页编号比较的表

(这个语句后得到的表形式)

sql2="selectc.编号,c.学校,c.学员,c.学费from("&sql2&")cwherec.tempcolumisnull"

通过SQL语言，相当于筛选tempcolum这个字段，为空的数据，我只拿前四个数据，可以这么理解

(筛选未控制，只取前四列)

sql2="selecttop"&n&"编号,学校,学员,学费from("&sql2&")"

取前N条数据

在实际工作中，大数据分页往往使用在数据库管理，SQL的这个方法应用的会比较广泛。