A007 用 Offset 函數參考不連續的儲存格

要製作自動化的工作表時, Offset 函數和 VLookup 函數是兩個最重要的工具. 只要這兩個函數用得熟練, 可以做出許多方便的 Excel 工作表.

我們將 Yahoo!奇摩股市 的投資組合匯入到 Excel 裡. 首先我們先建立一個投資組合.

將這個投資組合的資料匯入 Excel (匯入外部資料請參考 A003 匯入Web外部資料)

匯入的資料不如我們預期的整齊, 每一檔股票佔了三列, 而且中間還有一列是廣告...
將一個空白工作表改名為 清單, 將資料的表頭複製過去

如果資訊業是一種手工業的話, 我們就將每一列用人工指向資料工作表的個別列

幸好 Excel 有提供很方便的工具給我們, 不需要花人工做重複的事情.
我們打開函數精靈, 找分類 檢視與參照 裡面的 Offset

有關 Offset 函數的詳細用法請參考函數說明. 在這裡我們只介紹一部份.
Offset 可以讓我們動態地決定我們要參考的儲存格位置. 我們先試試看, 在 A2 儲存格寫入 =OFFSET(資料!A1,1,0). 這個公式讓 Excel 從 資料!A1 這個儲存格出發, 向下位移1列 (第二個參數), 向右位移 0 行(第三個參數), 所以回應的資料是 資料!A2 的內容 1101.

因為我們的每一檔股票在 資料工作表會佔三列, 所以我們在 Offset 的第二個參數每向下移一列, 數字就要乘3. 使用 ROW() 函數會回應儲存格所在的列數, 在 C欄試著寫入 = ROW(), C6儲存格的值等於6.

現在我們可以將A2的公式改為 =OFFSET(資料!A$1,ROW()*3-5,0), 向下拉之後變成如下的結果

前三檔股票沒問題了, 可是從第四檔以後因為中間插了入一行廣告就抓不到了. 我們可以在 Offset 的第二個參數加上一個 IF 函數, 公式變成
=OFFSET(資料!A$1,ROW()*3-5+IF(ROW()>4,1,0),0)

這樣子是沒有錯, 但有一點手工業的感覺, 如果哪天沒有這行廣告, 或是廣告換位置, 或是廣告多了兩行, 我們的公式就會出錯. 所以我們要用比較通用的原則來寫公式.

回到資料工作表, 我們可以了解廣告這一列跟其他資料列不一樣的地方在 O 欄的個股資訊. 廣告這一列的 O 欄是空白的. 我們只要知道O欄在資料列前面有幾格空白, 就可以知道要跳過去幾格了.

要知道有幾格空白用 COUNTBLANK 函數, 我們將 資料!O2 一直到預計資料應該出現的位置 當作參數, COUNTBLANK 會告訴我們有幾列是廣告列. 這個時候我們就用到 Offset 函數的後面兩個選用參數 Height 和 Width.

在D欄試作公式 =COUNTBLANK(OFFSET(資料!$O$1,1,0,ROW()*3-5,1))
Offset 函數的第三、四個參數告訴 Excel 要傳回的不是單一個儲存格, 而是列數為 ROW()*3-5, 行數為1的一個範圍. D欄的公式就是我們所要跳過去的廣告列數.

將 A2的公式改為 =OFFSET(資料!C$1,ROW()*3-5+COUNTBLANK(OFFSET(資料!$O$1,1,0,ROW()*3-5,1)),0)

做 Excel 試算表最有成就感的就是寫一個公式複製到所有的儲存格. 到這裡我們已經完成了公式的設計, 將 A2 的公式複製到所有儲存格.

延伸習作

1. 在公式中的欄位移 (第三個參數) 我們的設計是 0. 試著將 Offset 第一個參數 資料!A$1 固定為 資料!$A$1, 然後在第三個參數用 COLUMN() 函數來取代
2. 範例公式如果遇到廣告超過三列會有錯誤. 試著寫一個公式可以容許無限制的廣告列.
3. 在 A欄後插入一行 名稱, 將股票名稱包含進來.