EXCEL的單元格數據有效性序列設置

　　本文講述重點：EXCEL單元格數據有效性序列的來源

　　達到的效果：當點擊已設置數據有效性序列的單元格，會出一個下拉列表，供使用者選擇。

　　調出數據有效性設置的步驟。

　　1、 首先選中你要設置數據有效性的目標是一列 OR 一行 OR 某個單元格 OR 某一個單元格區域。

　　2、 菜單欄--- 數據--- 有效性，打開“設置”界面，在 “允許” 中選擇 “序列”，那麼接下來，本文的重點，就是在 “來源”框 中如何設置了。

　　一、常量型的來源設置

　　簡單點講，就是設好之後，列表不會隨意變更，適用於不會經常變來變去的列表。比如：銷售部，客服部，財務部，人事部等，公司不會隨意變更這幾個部門。

　　這種設置的好處，列表信息不占工作表資源，信息存儲在應用程序裡面。

　　設置方法：將列表名單輸入“來源”下的框中。

　　特別注意：不同的名單中間以“,”號隔開，這個符號是在英文輸入法狀態下輸入的，不要搞混了。(我的做法是，先用五筆把文字打出來，然後再切換到英文狀態，輸入“,”號)

　　設置完成後，點擊“確定”，然後返回EXCEL表中，查看效果

　　二、變量型來源的設置，也稱為引用型的來源設置。

　　這種設置就是所你可以自己指定一個單元格區域，來做為列表的來源，它可以是本工作表的，也可以是本工作簿其它工作表的。當然，這個區域內，你想以什麼文字做為列表都可以，允許內容隨時更改的。當然了，更改後數據有效性的序列也隨之更新了。

　　1、普通引用型的“來源”設置

　　(1，在本表中直接指定。

　　比如設定A1：A4為來源，方法如下：直接在“來源”框中點一下鼠標激活，然後鼠標再點住A1單元格不放，直接向下拖動到A4單元格。默認狀態下是絕對引用，如果是手工在“來源”框中輸入，請記得按F4 鍵，或Shift+ 4 鍵，以便輸入 “$” 符號

　　本方法適用於在當前Sheet表裡做。如果你的源數據列表來源於本工作簿其它Sheet表，那麼在指定“來源”時將不能指定，需要名稱公式。

　　(2，在本工作簿其它表中指定。

　　比如，數據列表在Sheet 1工作表的 A1:A4單元格，現在希望為Sheet 2 工作表的B5單元格設置數據有效性序列，序列的來源於Sheet 1工作表的 A1:A4 區域。

　　方法如下：

　　第1個步驟。

　　將工作窗口切換至Sheet 2 工作表，打開 菜單欄—“插入”—“名稱”，選擇“定義”，打開定義名稱設置窗口。

　　在引用位置中，將當前的填寫內容刪掉，然後點 Sheet 1工作表標簽，用鼠標選中A1：A4區域。默認狀態下，你的最後結果應該是 =Sheet1!$A$1:$A$4 注意檢查一下噢，呵呵…… 然後在“在當前工作簿中的名稱”下面的框框中，輸入你為這個公式定義的名稱，比如取個名字叫“部門列表”，然後依次點右側的按鈕“添加”、“確定”。

　　如下圖所示

　　第2個步驟。

　　為Sheet 2 工作表的 B5 單元格設置 數據有效性序列。 菜單打開的先後順序還記得嗎? 菜單欄---“數據”-----“有效性”，在 設置 界面，將 “允許”設置為“序列”。

　　那麼接下來“來源”怎麼寫呢? 請輸入引號裡面的“=部門列表”

　　第3步，查驗一下效果，呵呵，成了，那麼，如果你Sheet 1工作表中的“人事部”改為“行政部”呢? 結果怎麼樣? 呵呵，是不是很好玩。

　　那如果你在A1和A4單元格再插入一行，輸入“總經辦”，現在再返回Sheet 2工作表，查看B 5單元格，告訴我你發現了什麼? 是不是類似於下圖。呵呵…..

　　上述方法，適用於一時半會兒不能確定列表內容的情況。如果有增加，允許在第1個單元格和最後1個單格之間插入新的單元格數據。

　　注意：我們的示例中最後一個單元格是A4，如果現在要在A5單元格填入新數據，那麼數據有效性序列將不能顯示這一條。因為我們的名稱公式，只定義了A1：A4。

　　如果你不能確定未來的最後一個單元格是多少，那麼，你將需要下面這種較復雜的方法。

　　在上一篇中，使用常量的數據有效性序列設置，類似於打固定靶;使用普通引用型的設置方法，類似於打單方向的移動靶。接下來，我們講解打雙方向的移動靶，你不能確定數據源的首尾單元格的位置時，應該怎麼設置。

　　內容如下：

　　2，查找偏移引用型的“來源”設置

　　前提設定：列表數據來自Sheet 1 工作表A列，列表個數不確定;列表首尾前後是否要添加數據不確定。 現在又在Sheet 2 工作表的B 5單元格 設置數據有效性序列。序列來源於Sheet列

　　方法：因為數據使用的是另一個Sheet表，因此，還像之前那樣，我們首選在Sheet 2工作窗口，設置名稱公式。設置名稱公式的步驟你還會嗎? 不會的話，一起來做吧。

　　<1、定義名稱

　　點開 Sheet 2 工作表窗口，菜單欄---“插入”---“名稱”—“定義”

　　將引用位置框填入

　　=INDEX(Sheet1!$A:$A,1,1):OFFSET(INDEX(Sheet1!$A:$A,1,1),COUNTA(Sheet1!$A:$A)-1,0,,)

　　定義名稱為"我的列表"， 然後依次點 “添加”，“確定”。

　　<2、設置有效性

　　依然在Sheet 2 工作表窗口，點一下 B 5單元格,然後再從 菜單欄—“數據”—“有效性” 設置 允許 值為序列，在來源框中輸入 =我的列表 確定。 OK了，呵呵。

　　那麼現在試一下成果，你在Sheet 1的A列第1行新加入一行，然後填上數據後，在Sheet 2 的B5單元格，看一下效果，或者在Sheet 列緊接著最後一行，再填寫一個數據看看。呵呵，是不是靈活性很大了。

　　<3、公式解析

　　=INDEX(Sheet1!$A:$A,1,1)

　　這一段代表定位源數據的開頭始終為第1個單元格，

　　INDEX函數，指定返回到Sheet 1 工作表的A列第1行與第1列的交叉單元格。

　　OFFSET(INDEX(Sheet1!$A:$A,1,1),COUNTA(Sheet1!$A:$A)-1,0,,)

　　這一段代表定位源數據的結尾單元格，

　　OFFSET函數是一個偏移函數，在本公式中代表，以(INDEX(Sheet1!$A:$A,1,1)為參照點，向下偏移COUNTA(Sheet1!$A:$A)-1行，向右偏移0列。

　　COUNTA函數統計在Sheet1!$A中數據的行數。減1是因為這一次統計是統計一共有多少行，而OFFSET函數偏移時是不計算參照物那一行的，因此需要減去1行。

　　<4、相關說明

　　上述公式統計的區域是A列，因為這一列不能用做其它用途，如果不需要統計1列，那麼可以將COUNTA函數中指定為類似於

　　COUNTA(INDEX(Sheet1!$A:$A,1,1): Sheet1!$A30)

　　不能對源數據表的有數據行進行刪除操作，否則會引起錯誤，如果需要更改，可以使用復制粘貼的形式，使上1行與下1行保持不空行。

　　<5、關於EXCEL 單元格數據有效性序列設置其它運用

　　限於篇幅和難度，本次僅簡單介紹

　　運用一：二級引用運用。

　　舉例，書寫工具 可以分為鋼筆、鉛筆、水性筆，而鋼筆又有 英雄鋼筆、派克鋼筆、萬寶龍鋼筆等。 只要將源數據的分類列好。可以使用有效性序列，根據大類的名稱，自己顯示出小類的列表。

　　設計思路：1、使用INDEX找到大類別名稱的位置;

　　2、使用OFFSET以大類別名稱為參照點，進行雙向移動靶的首尾確定

　　運用二：針對於經常變更的數字設置有效性序列。

　　比如A5單元格是當前銷售價格，在A17設定數據有效性序列為 =A5, 那麼接來的輸入就是這個價格了，但如果下個月銷售價格有變動， A5的值變更了，那麼下個月A17的值就是新的值。但是上個月輸入的值不會受什麼影響。