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

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

別著急，我給你來三個方法，搞定這個事情，從簡單公式，到代碼到數(shù)據(jù)庫語言SQL方法，今天全部給你分析一遍

01 函數(shù)法

先來看看數(shù)據(jù)格式

我現(xiàn)在要格式，通過右邊頁碼，自定義條數(shù)，來控制顯示條數(shù)，效果如圖

(函數(shù)方法效果演示)

最大頁碼如果判斷?

中心思想：總行數(shù) / 每頁條數(shù)-----這個數(shù)值，如果是小數(shù)，我都想上舍入取整數(shù)

就是12.5頁，我顯示13頁

總行數(shù)= COUNTA(數(shù)據(jù)!A:A)-1

• COUNTA(數(shù)據(jù)!A:A) ----包換表頭的總數(shù)據(jù)條數(shù)
• COUNTA(數(shù)據(jù)!A:A)-1 ----就是去掉表頭后的數(shù)據(jù)總條數(shù)

每頁條數(shù)=K2單元格

向上舍入小數(shù)點，用函數(shù)：

• ROUNDUP(數(shù)字，小數(shù)位數(shù))

這里最后公式：

=IFERROR(ROUNDUP((COUNTA(數(shù)據(jù)!A:A)-1)/K2,0),1) 

用了一個IFERROR函數(shù)，容錯也顯示1頁

思考規(guī)律，如何判斷第一個要顯示的編號是什么?

第一個顯示的編號是：(頁碼-1)*每頁條數(shù)+1

最后公式：=($G$2-1)*$K$2+1 (注意絕對引用，防止拖拽的時候改變)

這個編號，和數(shù)據(jù)的行數(shù)，是不是有什么關(guān)系?

編號+1就是實際數(shù)據(jù)的行數(shù)

這里為了方便理解，給了輔助列，沒有，把編號想象成行號來操作

知道了位置，用什么函數(shù)來返回結(jié)果?

MATCH函數(shù)

語法：INDEX(數(shù)組或范圍，在數(shù)組和范圍里行的位置，在數(shù)組和范圍里列的位置)

• 一參數(shù)范圍：實際數(shù)據(jù)范圍，注意絕對鎖定
• 二參數(shù)，行號，就是編號+1
• 三參數(shù)列，就是從1開始到3的數(shù)字

過程函數(shù)：INDEX(數(shù)據(jù)!$A$1:$C$1000,編號+1,COLUMN(A1))

結(jié)果：INDEX(數(shù)據(jù)!$A$1:$C$1000, ($G$2-1)*$K$2+1+1,COLUMN(A1))

這個公式，是可以得到第一條數(shù)據(jù)結(jié)構(gòu)，我需要根據(jù)下拉，得到正確結(jié)果

只要下拉行數(shù)編號，要使用ROW函數(shù)

語法：ROW(單元格) 返回的是單元格行數(shù)

我這里寫入ROW(A1)，通過下拉，是里面A1變化為B1，C1，D1，E1，得到1，2，3，4結(jié)果

還要限制條數(shù)，這里用IF函數(shù)來判斷，只要超過條數(shù)，就顯示空，讓函數(shù)出錯

最終這部分函數(shù)：IF(ROW(A1)<=$K$2,ROW(A1)-1,"")

最終結(jié)果，結(jié)果部分拼一起，加一個容錯函數(shù)IFERROR

=IFERROR(INDEX(數(shù)據(jù)!$A$1:$C$1000,($G$2-1)*$K$2+1+1+IF(ROW(A1)<=$K$2,ROW(A1)-1,""),COLUMN(A1)),"") 

單元格右拉和下拉，就可以完成函數(shù)部分設(shè)定

02 VBA(單元格方法)

先看效果，VBA單元格方法，會比函數(shù)還簡單，數(shù)據(jù)真正多的時候，反而更快

(VBA顯示效果)

分析相關(guān)參數(shù)和辦法

• 通過函數(shù)方法，我們已經(jīng)知道，通過編號我們直接就知道，數(shù)據(jù)單元格位置：編號+1
• 不同頁碼，顯示第一個編號=(頁碼-1)*每頁條數(shù)+1
• VBA有單元格RESIZE屬性，表示截取一段指定數(shù)據(jù)區(qū)域
• 語法：單元格.RESIZE(范圍行數(shù)，范圍列數(shù))
• 范圍行數(shù)=每頁條數(shù)
• 范圍列數(shù)=數(shù)據(jù)總列數(shù)

通過這個屬性，很代碼很容易就寫出來了

Sub 單元格辦法()  
Dim rng As Range  
Dim lngPages As Long '頁數(shù)  
Dim lngNum As Long '每頁條數(shù)  
Dim lngRow As Long '第一個數(shù)值行  
Dim lngCol As Long '總數(shù)據(jù)列數(shù)  
 
'------------------下面是程序開始部分------------- 
 
lngPages = Range("I2").Value 'I2單元格值  
lngNum = Range("M2").Value 'M2單元格值  
'函數(shù)部分學(xué)習(xí)，知道編號+1就是行號 
 
lngRow = (lngPages - 1) * lngNum + 1 + 1  
'數(shù)據(jù)最大列數(shù)  
lngCol = Sheets("數(shù)據(jù)").Cells(1, Columns.Count).End(xlToLeft).Column  
'清空原始數(shù)據(jù) 
 Range("b3:d65536").ClearContents  
'取出那一塊的數(shù)據(jù)  
Range("b3").Resize(lngNum, lngCol).Value = _  
Sheets("數(shù)據(jù)").Cells(lngRow, 1).Resize(lngNum, lngCol).Value  
End Sub 

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

寫到制定工作表里

Private Sub Worksheet_Change(ByVal Target As Range)  
'判斷只有I2和M2兩個單元格改變才執(zhí)行代碼  
If Target.Address(0, 0) = "I2" Or Target.Address(0, 0) = "M2" Then  
Application.EnableEvents = False '關(guān)閉Worksheet_Change事件 

Call 單元格辦法 '調(diào)用代碼

Application.EnableEvents = True '打開Worksheet_Change事件  
End If  
End Sub 

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

調(diào)用的是開發(fā)工具里的，ACTIVEX控件

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

寫入代碼，來控制上下箭頭微調(diào)頁碼

Private Sub ScrollBar1_Change()  
With Sheet4.ScrollBar1  
.LinkedCell = "I2" '連接到I2單元格里  
.Min = 1 '最小值是1  
.Max = Range("K2").Value '最大值是K2單元格值  
End With  
Call 單元格辦法 '調(diào)用核心代碼  
End Sub 

03 數(shù)據(jù)庫語言SQL辦法

顯示效果和02部分VBA代碼是一樣的

這里分析下SQL部分解決思路

我是可以通過”SELECT TOP 5 * FROM [數(shù)據(jù)$]”這個SQL語句拿到前5條數(shù)據(jù)

這里我們根據(jù)變頁數(shù)和條數(shù)，控制TOP后面數(shù)據(jù)，總數(shù)據(jù)去掉已經(jīng)翻頁的數(shù)據(jù)，再去固定每頁條數(shù)TOP數(shù)據(jù)

詳細分析一下，代碼

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 

'設(shè)置參數(shù)

With Sheet1.ScrollBar1  
.Min = 1  
.Max = Sheet1.Range("K2").Value  
End With  
n = Sheet1.Range("M2").Value '每頁條數(shù)  
k = Sheet1.Range("I2").Value '頁碼 
If k > 1 Then '頁碼大于1頁的時候 

'這個是算已經(jīng)翻頁的編號都有那些

sql1 = "select top " & n * (k - 1) & " 編號 from [數(shù)據(jù)$]" 

'這個是總表和已經(jīng)翻頁編號比較，合成一個新表，這個表四個字段

sql2 = "select a.編號,a.學(xué)校,a.學(xué)員,a.學(xué)費,b.編號 as tempcolum from [數(shù)據(jù)$] a left join (" _  
& sql1 & ") b on a.編號 = b.編號" 

'通過判斷第四個字段是空，來達到找到去掉已經(jīng)翻頁數(shù)據(jù)的目的

' sql2 = "select c.編號,c.學(xué)校,c.學(xué)員,c.學(xué)費 from (" & sql2 & ") c where c.tempcolum is null" 

'取上面新數(shù)據(jù)前N條數(shù)據(jù)

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

Else '頁碼=1頁的時候執(zhí)行

'取每頁條數(shù)的數(shù)據(jù)，就是1-N條數(shù)據(jù)

sql2 = "select top " & n * k & " 編號,學(xué)校,學(xué)員,學(xué)費 from [數(shù)據(jù)$]"  
End If 

'拿RS數(shù)據(jù)

Set rs = cn.Execute(sql2) 

'關(guān)閉屏幕刷新

Application.ScreenUpdating = False 

'清除之前結(jié)果

Range("a1:e65536").ClearContents 

'得到表頭

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

'把結(jié)果復(fù)制出來到單元格里

Range("B3").CopyFromRecordset rs  
cn.Close: Set cn = Nothing  
Application.ScreenUpdating = True  
End Sub 

解釋下幾個SQL語句

sql1 = "select top " & n * (k - 1) & " 編號 from [數(shù)據(jù)$]" 

得到的是已經(jīng)翻頁編號數(shù)據(jù)

(得到是左側(cè)一個表，表示根據(jù)頁碼顯示，已經(jīng)翻頁的編號)

sql2 = "select a.編號,a.學(xué)校,a.學(xué)員,a.學(xué)費,b.編號 as tempcolum from [數(shù)據(jù)$] a left join (" _  
& sql1 & ") b on a.編號 = b.編號" 

得到的是一個五列的表，是和前面翻頁編號比較的表

(這個語句后得到的表形式)

sql2 = "select c.編號,c.學(xué)校,c.學(xué)員,c.學(xué)費 from (" & sql2 & ") c where c.tempcolum is null" 

通過SQL語言，相當(dāng)于篩選tempcolum這個字段，為空的數(shù)據(jù)，我只拿前四個數(shù)據(jù)，可以這么理解

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

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

取前N條數(shù)據(jù)

在實際工作中，大數(shù)據(jù)分頁往往使用在數(shù)據(jù)庫管理，SQL的這個方法應(yīng)用的會比較廣泛。