OFFSET 函數 x COUNTA 函數 x 動態清單

by Unknown 00:50:00 3 comments dynamic range Excel excel functions

 一般在使用 Excel 的時候作帳或者在某些欄位需要限定輸入的項目時，這時我們會使用Excel的資料驗證→清單這個功能。
以前使用這個功能，想要增加或移除項目的時候都需要再重新指定範圍。後來學習到用 OFFSET 的方式，就可以將動態地去指定需要的項目清單。




一開始，我們必須先知道如何限定儲存格只能有幾個我們指定的值。

[1] 先選擇放清單項目的工作表位置
[2] 選擇指定的清單的儲存格，依此例來說就是將 B3 到 B7 選取起來。
[3] 選擇索引標籤【公式】
[4] 點選【定義名稱】，會跳出【新名稱】對話視窗。
[5] 適當地命名你的清單，依此例就是將他命名為 expense
[6] 當我們有選取儲存格範圍時，這裡會自動地填上我們選擇的範圍。若此範圍是錯的，可自行輸入或直接點選儲存格做選擇。
[7] 按下【確定】鈕之後，這個範圍的儲存格就被我們命名為 expense 了。



下一步，我們必須將剛剛命名完範圍，放在我們平常會限定輸入的地方。

[1] 選擇限定輸入的儲存格

[2] 選擇索引標籤【資料】

[3] 按下【資料驗證】，會出現【資料驗證】對話視窗。

[4] 在這個下拉選單中，選擇【清單】。這樣待會設定完之後，在儲存格準備輸入值的時候，才有下拉選單可以選擇我們限定輸入的值。

[5] 此時，【來源】這裡為空白，請用滑鼠點一下，並按【F3】，會出現【貼上名稱】對話窗格。若不想用【F3】選擇範圍，也可以自己輸入，但切記一定要以【=】開頭。

[6] 選擇剛剛建立的清單

[7] 按下【確定】

[8] 此時【來源】就會自行出現 【=expense】的字樣。

[9] 按下【確定】之後，D3 這個儲存格就可以像第一張圖一樣，擁有限定輸入值的下拉選單。

人是懶惰的XD
總是希望工具可以聰明一點，知道什麼時候我的值移除或新增了，而在限定輸入的儲存格中也一併修改。

 上圖在支出項目的最後，新增了一筆「測試」。而我們的目標，就是希望 D3 這個被限定輸入值的儲存格的下拉選單中，也能如下圖般自行出現「測試」這個新的項目，而不用再透過上面的步驟重新設定。

接下來就要使用 OFFSET 函數進行我們的小小自動化大工程!!!!

 其實設定的步驟大同小異，主要是差在設定清單範圍那裡。
[1] 一樣選擇清單座落的工作表
[2] 索引標籤【公式】
[3] 選擇【名稱管理員】，因為我們在上面已經將這個清單命名過了，所以這裡我們就直接拿舊的清單做修改。
[4] 選擇 expense
[5] 點選【編輯】，此時會跳出【編輯名稱】對話視窗，如下圖。

 

此時，我們將【參照到】的【=someList!$B$3:$B$7】修改如下：

=OFFSET(someLists!$B$2, 1, 0, COUNTA(someLists!$B:$B)-1, 1)

OFFSET 共有 5 個參數，後面兩個是選擇性的參數。

● 第一個參數 (此例為 someLists!$B$2)

offset 故名思義，就是移動。但是要移動的時候，我們也要跟 Excel 說要從哪個儲存格為頭頭，開始移動。

而第一個參數，就是那個頭，他必須是不會被刪掉的儲存格，通常設定為標題。

● 第二個參數 (此例為 1)

這個參數是告訴 Excel，以做頭的那個儲存格為標準，我要往下或往上移動幾格，正數為往下，負數為往上。

● 第三個參數 (此例為 0)

這個參數是告訴 Excel，以做頭的那個儲存格為標準，我要往左或往右移動幾格，正數為往右，負數為往左。

● 第四個參數 (此例為 COUNTA(someLists!$B:$B)-1)

這個參數是告訴 Excel，依照上面三個參數，我移到指定的儲存格後，我要往下抓幾個儲存格當我的範圍。

而這個例子是用 COUNTA(someLists!$B:$B)-1，COUNTA用來數指定的範圍中(在此題是B欄)，有幾個有內容物的儲存格，減1是因為不需要把標題當成我的範圍。

這個參數是最重要的精華，因為這個函數在新增新的值的時候，就會變動。

● 第五個參數 (此例為 1)

這個參數是告訴 Excel，依照最前面三個參數，我移到指定的儲存格後，我要往右抓幾個儲存格當我的範圍。

而這個例子是用 1，是因為我只需要當欄那一欄就夠。

完成設定後，即可按【確定】回到【名稱管理員】。

 

此時，若將遊標停在紅色矩形框起來的【參照到】，就可以看到綠色虛線框框閃爍在我們指定好的範圍。
若想測試是否為動態，可以在增加新的值，再回到【名稱管理員】點選該名稱的【參照到】，觀看綠色虛線框是否閃爍在對的位置。


範例檔下載連結，裡面還有人員的清單還沒製作，若有興趣可以下載檔案嘗試 ~\(^　^\)
若有問題歡迎留言或者來信，有其他問題也可以來信唷 O_<