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

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

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

01 函數(shù)法

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

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

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

最大頁(yè)碼如果判斷?

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

就是12.5頁(yè)，我顯示13頁(yè)

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

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

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

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

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

這里最后公式：

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

用了一個(gè)IFERROR函數(shù)，容錯(cuò)也顯示1頁(yè)

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

第一個(gè)顯示的編號(hào)是：(頁(yè)碼-1)*每頁(yè)條數(shù)+1

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

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

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

這里為了方便理解，給了輔助列，沒(méi)有，把編號(hào)想象成行號(hào)來(lái)操作

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

MATCH函數(shù)

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

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

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

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

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

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

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

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

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

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

最終結(jié)果，結(jié)果部分拼一起，加一個(gè)容錯(cuò)函數(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單元格方法，會(huì)比函數(shù)還簡(jiǎn)單，數(shù)據(jù)真正多的時(shí)候，反而更快

(VBA顯示效果)

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

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

通過(guò)這個(gè)屬性，很代碼很容易就寫(xiě)出來(lái)了

Sub 單元格辦法()  
Dim rng As Range  
Dim lngPages As Long '頁(yè)數(shù)  
Dim lngNum As Long '每頁(yè)條數(shù)  
Dim lngRow As Long '第一個(gè)數(shù)值行  
Dim lngCol As Long '總數(shù)據(jù)列數(shù)  
 
'------------------下面是程序開(kāi)始部分------------- 
 
lngPages = Range("I2").Value 'I2單元格值  
lngNum = Range("M2").Value 'M2單元格值  
'函數(shù)部分學(xué)習(xí)，知道編號(hào)+1就是行號(hào) 
 
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 

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

寫(xiě)到制定工作表里

Private Sub Worksheet_Change(ByVal Target As Range)  
'判斷只有I2和M2兩個(gè)單元格改變才執(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 '打開(kāi)Worksheet_Change事件  
End If  
End Sub 

通過(guò)上下箭頭，點(diǎn)擊改變頁(yè)碼代碼

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

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

寫(xiě)入代碼，來(lái)控制上下箭頭微調(diào)頁(yè)碼

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ù)庫(kù)語(yǔ)言SQL辦法

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

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

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

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

詳細(xì)分析一下，代碼

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 '每頁(yè)條數(shù)  
k = Sheet1.Range("I2").Value '頁(yè)碼 
If k > 1 Then '頁(yè)碼大于1頁(yè)的時(shí)候 

'這個(gè)是算已經(jīng)翻頁(yè)的編號(hào)都有那些

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

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

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

'通過(guò)判斷第四個(gè)字段是空，來(lái)達(dá)到找到去掉已經(jīng)翻頁(yè)數(shù)據(jù)的目的

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

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

' sql2 = "select top " & n & " 編號(hào),學(xué)校,學(xué)員,學(xué)費(fèi) from (" & sql2 & ")" 

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

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

sql2 = "select top " & n * k & " 編號(hào),學(xué)校,學(xué)員,學(xué)費(fèi) 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ù)制出來(lái)到單元格里

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

解釋下幾個(gè)SQL語(yǔ)句

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

得到的是已經(jīng)翻頁(yè)編號(hào)數(shù)據(jù)

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

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

得到的是一個(gè)五列的表，是和前面翻頁(yè)編號(hào)比較的表

(這個(gè)語(yǔ)句后得到的表形式)

sql2 = "select c.編號(hào),c.學(xué)校,c.學(xué)員,c.學(xué)費(fèi) from (" & sql2 & ") c where c.tempcolum is null" 

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

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

sql2 = "select top " & n & " 編號(hào),學(xué)校,學(xué)員,學(xué)費(fèi) from (" & sql2 & ")" 

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

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