2017年3月26日 星期日

Excel-替代陣列公式以在大筆資料中加快速度(MATCH,OFFSET)

延伸前一篇文章:利用Excel製作能快速查詢多個字詞之注音的系統,如果要查詢列出同音字,該如何處理?
先來探討一個 Excel 的問題:原始資料約有17萬筆,若使用陣列公式({}),速度變慢且容易當機。若不使用陣列公式,則必須增設輔助欄位,以二階段方式來撰寫公式。雖然高達17萬筆的資料,可以明顯的感覺加快了查詢速度。
在下圖中,想要在儲存格F1中輸入一個字或詞後,想要列出所有同音的字或詞。在H欄中增設了輔助欄位,用以加速查詢的速度。
Excel-替代陣列公式以在大筆資料中加快速度(MATCH,OFFSET)

【公式設計與解析】
先來處理輔助欄位:
1. 找出第一筆符合的儲存格列號
儲存格H4:=IFERROR(MATCH($F$1,$B$2:$B$170000,0),"")
(1) MATCH($F$1,$B$2:$B$170000,0)
利用MATCH函數找出儲存格F1在全部資料中符合的儲存格是第幾筆。
利用IFERROR函數將傳回的錯誤訊息轉換為空字串。
2. 找出第二筆以後符合的儲存格列號
儲存格H5:=IFERROR(MATCH($F$1,OFFSET($B$2,H4,0,170000,1),0)+H4,"")
(1) OFFSET($B$2,H4,0,170000,1),0)
找出第一筆符合的儲存格之後的儲存格範圍。
(2) MATCH($F$1,OFFSET($B$2,H4,0,170000,1),0)
利用第(1)式的傳回值,再藉由MATCH函數找出儲存格F1在全部資料中符合的儲存格是第幾筆。
(3) MATCH($F$1,OFFSET($B$2,H4,0,170000,1),0)+H4
將第(2)式的傳回值再加上前一筆傳回值,才是第二筆在整個儲存格的實際所在位置。
最後,利用IFERROR函數將傳回的錯誤訊息轉換為空字串。
複製儲存格H5,貼至儲存格H5:H12。
接著處理查詢欄位:
儲存格E4:=IFERROR(OFFSET($B$1,H4,0),"")
儲存格F4:=IFERROR(OFFSET($C$1,H4,0),"")
複製儲存格E4:F4,貼至儲存格E4:F12。

沒有留言:

張貼留言

好康東東