我有一個表，有幾十萬行數據，但是我其實只想看一部分，怎么辦呢?

有沒有像網頁預覽那樣，一個頁面只顯示固定條數，可以翻頁呢?--雨夜又遇到問題了

別著急，我給你來三個方法，搞定這個事情，從簡單公式，到代碼到數據庫語言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 單元格辦法()  
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 

代碼部署，通過單元格值改變事件，達到改變頁數和每頁條數，屬性數據目的

寫到制定工作表里

 

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 

通過上下箭頭，點擊改變頁碼代碼

調用的是開發工具里的，ACTIVEX控件

 

放入單元格位置后，在設計模式下，右鍵-插卡代碼

 

寫入代碼，來控制上下箭頭微調頁碼

 

Private Sub ScrollBar1_Change()  
With Sheet4.ScrollBar1  
.LinkedCell = "I2" '連接到I2單元格里  
.Min = 1 '最小值是1  
.Max = Range("K2").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("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 

解釋下幾個SQL語句

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" 

通過SQL語言，相當于篩選tempcolum這個字段，為空的數據，我只拿前四個數據，可以這么理解

 

(篩選未控制，只取前四列)

sql2 = "select top " & n & " 編號,學校,學員,學費 from (" & sql2 & ")" 

取前N條數據

 

在實際工作中，大數據分頁往往使用在數據庫管理，SQL的這個方法應用的會比較廣泛。