我有一个表,有几十万行数据,但是我其实只想看一部分,怎么办呢?
有没有像网页预览那样,一个页面只显示固定条数,可以翻页呢?--雨夜又遇到问题了
别着急,分页预览怎么添加第二页,我给你来三个方法,搞定这个事情,从简单公式,到代码到数据库语言SQL方法,今天全部给你分析一遍
01 函数法
先来看看数据格式
我现在要格式,通过右边页码,自定义条数,来控制显示条数,效果如图
▍最大页码如果判断?
中心思想:总行数 / 每页条数-----这个数值,如果是小数,我都想上舍入取整数
就是12.5页,我显示13页
总行数= COUNTA(数据!A:A)-1
1、点击主菜单上的【打印预览】图标(红框内的图标)2、然后,出现下列预览界面,在此界面,可以选择【打印机】,选择【纸张类型】,选择【纸张方向】,选择【打印份数】,显示共几页,可以【分页预览】,【页眉页脚】设置。
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,&34;)
▍最终结果,结果部分拼一起,加一个容错函数IFERROR
单元格右拉和下拉,就可以完成函数部分设定
02 VBA(单元格方法)
先看效果,VBA单元格方法,会比函数还简单,数据真正多的时候,反而更快
▍分析相关参数和办法
通过函数方法,我们已经知道,通过编号我们直接就知道,数据单元格位置:编号+1
不同页码,显示第一个编号=(页码-1)*每页条数+1
VBA有单元格RESIZE属性,表示截取一段指定数据区域
语法:单元格.RESIZE(范围行数,范围列数)
范围行数=每页条数
范围列数=数据总列数
▍通过这个属性,很代码很容易就写出来了
Sub 单元格办法()
Dim rng As Range
Dim lngPages As Long '页数
Dim lngNum As Long '每页条数
Dim lngRow As Long '第一个数值行
Dim lngCol As Long '总数据列数
'------------------下面是程序开始部分-------------
lngPages = Range(&34;).Value 'I2单元格值
lngNum = Range(&34;).Value 'M2单元格值
'函数部分学习,知道编号+1就是行号
lngRow = (lngPages - 1) * lngNum + 1 + 1
'数据最大列数
'清空原始数据
Range(&34;).ClearContents
'取出那一块的数据
Range(&34;).Resize(lngNum,lngCol).Value = _
End Sub
▍代码部署,通过单元格值改变事件,达到改变页数和每页条数,属性数据目的
●写到制定工作表里
Private Sub Worksheet_Change(ByVal Target As Range)
'判断只有I2和M2两个单元格改变才执行代码
If Target.Address(0,0) = &34; Or Target.Address(0,0) = &34; Then
Application.EnableEvents = False '关闭Worksheet_Change事件
Call 单元格办法 '调用代码
End If
End Sub
●通过上下箭头,点击改变页码代码
调用的是开发工具里的,ACTIVEX控件
放入单元格位置后,在设计模式下,右键-插卡代码
1、 如图所示我想将此表分三页打印,每个班占据一页纸,首先需要点击“视图”。2、 然后需要点击分页“预览”。3、 然后会进入“分页预览”界面。4、 鼠标选中此单元格,单击右键,然后点击插入“分页符”。这时将在两个。
写入代码,来控制上下箭头微调页码
Private Sub ScrollBar1_Change()
With Sheet4.ScrollBar1
1、首先,在电脑桌面打开需要编辑的excel表格,进入到编辑首页中。2、然后在编辑页面中,一起点击表格右下角的分页预览按键。3、其次,来对自己需要数量的页码进行调整。4、最后,会看见中间有条虚线,这是到一页的提示。
.LinkedCell = &34; '连接到I2单元格里
.Min = 1 '最小值是1
.Max = Range(&34;).Value '最大值是K2单元格值
End With
Call 单元格办法 '调用核心代码
End Sub
03 数据库语言SQL办法
显示效果和02部分VBA代码是一样的
▍这里分析下SQL部分解决思路
我是可以通过”SELECT TOP 5 * FROM [数据$]”这个SQL语句拿到前5条数据
这里我们根据变页数和条数,控制TOP后面数据,总数据去掉已经翻页的数据,再去固定每页条数TOP数据
▍详细分析一下,代码
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(&34;)
With cn
.Provider = &34;
.Open ThisWorkbook.FullName
End With
'设置参数
With Sheet1.ScrollBar1
.Min = 1
.Max = Sheet1.Range(&34;).Value
End With
n = Sheet1.Range(&34;).Value '每页条数
k = Sheet1.Range(&34;).Value '页码
If k > 1 Then '页码大于1页的时候
'这个是算已经翻页的编号都有那些
sql1 = &34; & n * (k - 1) & &34;
'这个是总表和已经翻页编号比较,合成一个新表,这个表四个字段
sql2 = &34; _
& sql1 & &34;
'通过判断第四个字段是空,来达到找到去掉已经翻页数据的目的
&34;select c.编号,c.学校,c.学员,c.学费 from (&34;) c where c.tempcolum is null"
'取上面新数据前N条数据
具体步骤:1、首先点击excel表格任务栏中的“视图”2、然后点击工具栏中的分页预览,系统会默认的将你的编辑内容进行分页。你也可以自己通过拖动分页线来调整当页的内容。打印时就会将当页的内容打印到一张纸上。3、如果要。
&34;select top &34; 编号,学校,学员,学费 from (&34;)"
Else '页码=1页的时候执行
'取每页条数的数据,就是1-N条数据
sql2 = &34; & n * k & &34;
End If
'拿RS数据
Set rs = cn.Execute(sql2)
'关闭屏幕刷新
Application.ScreenUpdating = False
'清除之前结果
Range(&34;).ClearContents
'得到表头
For i = 0 To rs.Fields.Count - 1
Cells(2,i + 2).Value = rs.Fields(i).Name
Next i
'把结果复制出来到单元格里
Range(&34;).CopyFromRecordset rs
cn.Close: Set cn = Nothing
Application.ScreenUpdating = True
End Sub
▍解释下几个SQL语句
● sql1 = &34; & n * (k - 1) & &34;
得到的是已经翻页编号数据
● sql2 = &34; _
& sql1 & &34;
得到的是一个五列的表,是和前面翻页编号比较的表
● sql2 = &34; & sql2 & &34;
通过SQL语言,相当于筛选tempcolum这个字段,为空的数据,我只拿前四个数据,可以这么理解
● sql2 = &34; & n & &34; & sql2 & &34;
取前N条数据
在实际工作中,大数据分页往往使用在数据库管理,SQL的这个方法应用的会比较广泛
喜欢就关注我吧,每天分享职场知识,办公技巧!