學不完．教不停．用不盡

延伸前一篇文章：利用Excel製作能快速查詢多個字詞之注音的系統，如果要查詢列出同音字，該如何處理？

先來探討一個 Excel 的問題：原始資料約有17萬筆，若使用陣列公式（｛｝），速度變慢且容易當機。若不使用陣列公式，則必須增設輔助欄位，以二階段方式來撰寫公式。雖然高達17萬筆的資料，可以明顯的感覺加快了查詢速度。

在下圖中，想要在儲存格F1中輸入一個字或詞後，想要列出所有同音的字或詞。在H欄中增設了輔助欄位，用以加速查詢的速度。

 

【公式設計與解析】

先來處理輔助欄位：

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。