EXCEL的儲存格資料有效性序列設定

來源:互聯網
上載者:User

  本文講述重點:EXCEL儲存格資料有效性序列的來源

  達到的效果:當點擊已設定資料有效性序列的儲存格,會出一個下拉式清單,供使用者選擇。

  調出資料有效性設定的步驟。

  1、 首先選中你要設定資料有效性的目標是一列 OR 一行 OR 某個儲存格 OR 某一個儲存格範圍。

  2、 功能表列--- 資料--- 有效性,開啟“設定”介面,在 “允許” 中選擇 “序列”,那麼接下來,本文的重點,就是在 “來源”框 中如何設定了。

  一、常量型的來源設定

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

  這種設定的好處,列表資訊不佔工作表資源,資訊儲存在應用程式裡面。

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

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

  設定完成後,點擊“確定”,然後返回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的值就是新的值。但是上個月輸入的值不會受什麼影響。

相關文章

Beyond APAC's No.1 Cloud

19.6% IaaS Market Share in Asia Pacific - Gartner IT Service report, 2018

Learn more >

Apsara Conference 2019

The Rise of Data Intelligence, September 25th - 27th, Hangzhou, China

Learn more >

Alibaba Cloud Free Trial

Learn and experience the power of Alibaba Cloud with a free trial worth $300-1200 USD

Learn more >

聯繫我們

該頁面正文內容均來源於網絡整理,並不代表阿里雲官方的觀點,該頁面所提到的產品和服務也與阿里云無關,如果該頁面內容對您造成了困擾,歡迎寫郵件給我們,收到郵件我們將在5個工作日內處理。

如果您發現本社區中有涉嫌抄襲的內容,歡迎發送郵件至: info-contact@alibabacloud.com 進行舉報並提供相關證據,工作人員會在 5 個工作天內聯絡您,一經查實,本站將立刻刪除涉嫌侵權內容。