Excel-用OFFSET和COUNTA實作動态增加下拉清單

今天在用Excel實作一個需求管理的小工具。Excel的下拉框大家一定都知道，是用資料有效性中的序列來完成的。但是他有個很明顯的弊端，就是無法動态的去加入下拉清單中内容，一旦下拉清單内容需要做調整的時候，就需要一個個的重新在資料有效性中的序列中去增加，這是一個相當複雜和容易出錯的工作。

如何實作這一需求？

先介紹兩個Excel中很有用的兩個函數：OFFSET和COUNTA。OFFSET是用來傳回一個引用，引用可以是單元格或者一個單元格區域的；而COUNTA是用來傳回參數清單中非空值的單元格個數。具體的文法我就不在這裡描述了，大家有興趣可以檢視Excel的幫助文檔。

這裡我想實作這麼一個具體的功能：我的Excel有一列叫“PM”，并且我想随時在下拉清單裡增加“PM人選(如圖一)。見圖一，目前PM這列有四個人選，我想動态增加一個PM人選“巨元”，如何做？

(圖一) 

步驟一：借助另外一個Sheet，用某一列來定義PM人選（如圖二）

（圖二）

步驟二：Excel有一個叫名稱管理器-定義名稱，這個功能類似與我們開發語言中的全部變量，在這裡我們定義一個變量名稱：PM，并設定引用位置：=OFFSET(CONFIG!$C$2,1,0,COUNTA(CONFIG!$C$2:CONFIG!$C$256)-1,1)；這個公式是核心，簡單解釋一下：

1）COUNTA(CONFIG!$C$2:CONFIG!$C$256)-1；傳回值：4

2）OFFSET(CONFIG!$C$2,1,0,4,1)；傳回值：C3:C6的數組

步驟三：在圖一中的PM這列使用資料有效性的序列，來源：=PM(就是名稱管理器中定義的變量)；

步驟四：現在我們可以在圖二中PM這列中任意增加、删除、修改内容（增加“巨元”），都會立刻動态同步到圖一中的PM下拉清單（顯示包括“巨元”在内的五個PM人選的下拉清單）。

大功告成。

本文轉自 神相 51CTO部落格，原文連結：http://blog.51cto.com/shenxiang/269217，如需轉載請自行聯系原作者