|
|
51CTO旗下网站
|
|
移动端

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

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

作者:轻松工作快乐生活来源:今日头条|2020-05-20 16:54

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

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

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

01 函数法

先来看看数据格式

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

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

分页显示大数据,原来方法这么多,快来学几个
(函数方法效果演示)

最大页码如果判断?

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

就是12.5页,我显示13页

总行数= COUNTA(数据!A:A)-1

  • COUNTA(数据!A:A) ----包换表头的总数据条数
  • COUNTA(数据!A:A)-1 ----就是去掉表头后的数据总条数

每页条数=K2单元格

向上舍入小数点,用函数:

  • ROUNDUP(数字,小数位数)

这里最后公式:

  1. =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

  1. =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(范围行数,范围列数)
  • 范围行数=每页条数
  • 范围列数=数据总列数

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

  1. Sub 单元格办法()  
  2. Dim rng As Range  
  3. Dim lngPages As Long '页数  
  4. Dim lngNum As Long '每页条数  
  5. Dim lngRow As Long '第一个数值行  
  6. Dim lngCol As Long '总数据列数  
  7.  
  8. '------------------下面是程序开始部分------------- 
  9.  
  10. lngPages = Range("I2").Value 'I2单元格值  
  11. lngNum = Range("M2").Value 'M2单元格值  
  12. '函数部分学习,知道编号+1就是行号 
  13.  
  14. lngRow = (lngPages - 1) * lngNum + 1 + 1  
  15. '数据最大列数  
  16. lngCol = Sheets("数据").Cells(1, Columns.Count).End(xlToLeft).Column  
  17. '清空原始数据 
  18.  Range("b3:d65536").ClearContents  
  19. '取出那一块的数据  
  20. Range("b3").Resize(lngNum, lngCol).Value = _  
  21. Sheets("数据").Cells(lngRow, 1).Resize(lngNum, lngCol).Value  
  22. End Sub 

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

写到制定工作表里

  1. Private Sub Worksheet_Change(ByVal Target As Range)  
  2. '判断只有I2和M2两个单元格改变才执行代码  
  3. If Target.Address(0, 0) = "I2" Or Target.Address(0, 0) = "M2" Then  
  4. Application.EnableEvents = False '关闭Worksheet_Change事件 

Call 单元格办法 '调用代码

  1. Application.EnableEvents = True '打开Worksheet_Change事件  
  2. End If  
  3. End Sub 

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

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

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

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

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

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

  1. Private Sub ScrollBar1_Change()  
  2. With Sheet4.ScrollBar1  
  3. .LinkedCell = "I2" '连接到I2单元格里  
  4. .Min = 1 '最小值是1  
  5. .Max = Range("K2").Value '最大值是K2单元格值  
  6. End With  
  7. Call 单元格办法 '调用核心代码  
  8. End Sub 

03 数据库语言SQL办法

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

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

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

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

详细分析一下,代码

  1. Sub SQL方法2()  
  2. Dim cn As Object, rs As Object  
  3. Dim sql1 As String, sql2$  
  4. Dim n As Long  
  5. Dim i As Long  
  6. Dim k As Long 
  7.  Set cn = CreateObject("Adodb.Connection" 
  8. With cn  
  9. .Provider = "Microsoft.Ace.Oledb.12.0;Extended Properties=Excel 12.0"  
  10. .Open ThisWorkbook.FullName 
  11.  End With 

'设置参数

  1. With Sheet1.ScrollBar1  
  2. .Min = 1  
  3. .Max = Sheet1.Range("K2").Value  
  4. End With  
  5. n = Sheet1.Range("M2").Value '每页条数  
  6. k = Sheet1.Range("I2").Value '页码 
  7. If k > 1 Then '页码大于1页的时候 

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

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

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

  1. sql2 = "select a.编号,a.学校,a.学员,a.学费,b.编号 as tempcolum from [数据$] a left join (" _  
  2. & sql1 & ") b on a.编号 = b.编号" 

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

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

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

  1. ' sql2 = "select top " & n & " 编号,学校,学员,学费 from (" & sql2 & ")" 

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

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

  1. sql2 = "select top " & n * k & " 编号,学校,学员,学费 from [数据$]"  
  2. End If 

'拿RS数据

  1. Set rs = cn.Execute(sql2) 

'关闭屏幕刷新

  1. Application.ScreenUpdating = False 

'清除之前结果

  1. Range("a1:e65536").ClearContents 

'得到表头

  1. For i = 0 To rs.Fields.Count - 1  
  2. Cells(2, i + 2).Value = rs.Fields(i).Name  
  3. Next i 

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

  1. Range("B3").CopyFromRecordset rs  
  2. cn.CloseSet cn = Nothing  
  3. Application.ScreenUpdating = True  
  4. End Sub 

解释下几个SQL语句

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

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

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

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

  1. sql2 = "select a.编号,a.学校,a.学员,a.学费,b.编号 as tempcolum from [数据$] a left join (" _  
  2. & sql1 & ") b on a.编号 = b.编号" 

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

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

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

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

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

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

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

  1. sql2 = "select top " & n & " 编号,学校,学员,学费 from (" & sql2 & ")" 

取前N条数据

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

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

【编辑推荐】

  1. 企业能利用大数据算法建立有效链接吗?
  2. 分析成熟度模型:阻碍数据科学团队发展的“罪魁祸首”
  3. 别再用Pandas处理大数据了!现在你拥有更好的选择
  4. Spark:大数据产品的一种测试方法与实现
  5. 未卜先知,现代化数据仓库带来强大商业智能
【责任编辑:未丽燕 TEL:(010)68476606】

点赞 0
分享:
大家都在看
猜你喜欢

订阅专栏+更多

思科交换网络安全指南

思科交换网络安全指南

安全才能无忧
共5章 | 思科小牛

76人订阅学习

云计算从入门到上瘾

云计算从入门到上瘾

传统IT工程师的转型
共26章 | 51CTO阿森

239人订阅学习

从头解锁Python运维

从头解锁Python运维

多维度详解
共19章 | 叱诧少帅

353人订阅学习

视频课程+更多

C++

C++

讲师:王健伟30641人学习过

图解轻松学Docker与Kubernetes(k8s)

图解轻松学Docker与Kubernetes(k8s)

讲师:齐毅15048人学习过

SpringBoot+SpringMvc+Spring+MyBatis项目全程实战开发(附完整源码)

SpringBoot+SpringMvc+Spring+MyBatis项目全

讲师:石头老师1624人学习过

订阅51CTO邮刊

点击这里查看样刊

订阅51CTO邮刊

51CTO服务号

51CTO官微