Excel：分页显示大数据，原来方法这么多

1、01，函数法

先来看看数据格式

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

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页

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

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

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

编号+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,"")

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

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

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

7、02，VBA（单元格方法）

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

8、分析相关参数和办法

通过函数方法，我们已经知道，通过编号我们直接就知道，数据单元格位置：编号+1    

不同页码，显示第一个编号=（页码-1）*每页条数+1

VBA有单元格RESIZE属性，表示截取一段制定数据区域

语法：单元格.RESIZE(范围行数，范围列数)

范围行数=每页条数

范围列数=数据总列数

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).Value

End Sub

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

●写到制定工作表里

Private Sub Worksheet_Change(ByVal Target As Range)

   '判断只有I2和M2两个单元格改变才执行代码

   If Target.Address(0, 0) = "I2" Or Target.Address(0, 0) = "M2" Then

       Application.EnableEvents = False '关闭Worksheet_Change事件

       Call 单元格办法  '调用代码

       Application.EnableEvents = True  '打开Worksheet_Change事件

   End If

End Sub

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

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

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

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

Private Sub ScrollBar1_Change()

   With Sheet4.ScrollBar1

       .LinkedCell = "I2" '连接到I2单元格里

       .Min = 1            '最小值是1

       .Max = Range("K2").Value '最大值是K2单元格值

   End With

   Call 单元格办法    '调用核心代码

End Sub

14、03，数据库语言SQL办法

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

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

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

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

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 = True

End Sub

16、解释下几个SQL语句

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

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

17、 sql2 = "select a.编号,a.学校,a.学员,a.学费,b.编号 as tempcolum from [数据$] a left join (" _

           & sql1 & ") b on a.编号 = b.编号"

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

18、sql2 = "select c.编号,c.学校,c.学员,c.学费 from (" & sql2 & ") c where c.tempcolum is null"

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

19、● sql2 = "select top " & n & " 编号,学校,学员,学费 from (" & sql2 & ")"

取前N条数据

20、个人建议

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