二維碼 購物車
部落窩在線教育歡迎您!

Excel表10萬行數(shù)據(jù),篩選卡頓怎么辦?

?

作者:小窩來源:部落窩教育發(fā)布時間:2024-07-26 12:00:16點擊:1002

分享到:
0
收藏    收藏人氣:0人
版權(quán)說明: 原創(chuàng)作品,禁止轉(zhuǎn)載。

編按:

當(dāng)Excel工作表數(shù)據(jù)上萬行,10萬行,三四十萬行后,可能運行緩慢、卡頓。尤其是對含有大量公式的表格進行篩選操作,可能一次篩選就需要等待十分鐘以上。小窩分享兩種避免運行卡頓的大數(shù)據(jù)篩選方法。

 

客服老師轉(zhuǎn)來一份新朋友的求助:工作表有近10萬行數(shù)據(jù),每次篩選都卡得要死,怎么解決?(遺憾的是,因為有事耽擱沒有及時處理,在此,說聲抱歉。)

或許其他伙伴也可能遇到此類問題,小窩用共92993行的兩列數(shù)據(jù)在此分享一點經(jīng)驗。

 

A列是客戶編碼, B列是物料代碼,整個數(shù)據(jù)按客戶編碼做了排序?,F(xiàn)在需要篩選出每個客戶第一次出現(xiàn)時的數(shù)據(jù)。

 

 

新朋友的解決方式是:

C2中填入公式“=MATCH(A2,A:A)=ROW(A2)”并向下填充,得到一列TRUE、FALSE組成的數(shù)據(jù)。TRUE代表用戶首次出現(xiàn)。篩選C列中所有TRUE值,得到客戶首次出現(xiàn)的數(shù)據(jù)。

 

 

但是每次篩選都卡得要死,為什么如此卡呢?

篩選操作會觸發(fā)整個工作表重新計算。

當(dāng)前C2:C92993中每個單元格中的公式都引用了超100萬個數(shù)據(jù)(整個A列),合計引用超900億個數(shù)據(jù),因此計算極其耗時。小窩用自己的電腦測試了一下,C列整個計算需要186.49秒。

 

有兩種方式可以減輕運行卡頓。

第一種:不改變公式,采用手動計算加Filter函數(shù)進行篩選。

1)在“公式”選項卡的“計算”組中,設(shè)置為“計算選項”為手動,避免如增刪數(shù)據(jù)等操作觸發(fā)工作表重新計算。

2)在E2中輸入“=FILTER(A2:B92993, C2:C92993)”即可。

FILTER函數(shù)進行篩選時不會觸發(fā)工作表重新計算,F(xiàn)ILTER公式只引用了20多萬個數(shù)據(jù),經(jīng)測試耗時只有0.008秒。

 

 

第二種:修改C列公式降低單元格引用量后繼續(xù)用篩選功能進行篩選。

1)計算選項保持“自動”不變。在C2中輸入公式“=IF(A2<>A1,1,0)”并向下填充。

2)正常執(zhí)行“數(shù)據(jù)”選項卡中的“篩選”操作篩選“1”即可。

C列每個公式只引用2個數(shù)據(jù),共引用不到20萬個數(shù)據(jù)。經(jīng)測試,耗時只有0.026秒。

 

 

兩種方法,推薦大家采用第二種,從根本上減少用時。第一種方法有很多不足,只要觸發(fā)重新計算就會很耗時。

1)優(yōu)化公式,降低單元格引用量是大數(shù)據(jù)表格提高工作表速度的重要方法。

2)如果不能優(yōu)化公式,也可啟用手動計算,減少運算?;蛘咿D(zhuǎn)用其他工具如PQ進行數(shù)據(jù)處理。

 

本文配套的練習(xí)課件請?zhí)砑涌头⑿?/font>buluowojiaoyu索取。

Excel高手,快速提升工作效率,部落窩教育Excel精品好課任你選擇

掃下方二維碼關(guān)注公眾號,可隨時隨地學(xué)習(xí)Excel:

相關(guān)推薦:

當(dāng)心跨文件查詢的泄密風(fēng)險

帶近期要事提醒的工作日歷

批量修改文件名的方法

再說按指定次數(shù)復(fù)制數(shù)據(jù),不用PQ不用輔助列

版權(quán)申明:

本文作者小窩;部落窩教育享有稿件專有使用權(quán)。若需轉(zhuǎn)載請聯(lián)系部落窩教育。