2011年6月17日 星期五

Excel-各種資料驗證的應用

在 Excel 中的「資料驗證」功能,可以幫助使用者在輸入資料時可以只接受符合準則的輸入內容。Excel 提供了整數、實數、清單、日期、時間、文字長度和自訂公式等幾種驗證項目。

image

其實你只要將平時使用在儲存格中的公式,稍加變化即可套用在儲存格的資料驗證上,會有很多的妙用哦!

以下例舉5種應用:

(1) 不接受未來日期

日期設定小於或等於今天的日期(=TODAY())

(2) 只接受偶數

本例為儲存格C3,公式設定為「=MOD(C3,2)=0」,如果只接受奇數,則公式改為「=MOD(C3,2)=1」。也可以一次選取多個儲存格,設定時以第一個儲存格設定公式。

(3) 只接受星期一到星期五的日期

公式設定為「=WEEKDAY(C5,2)<6」。

(4) 使用儲存格範圍中的清單輸入資料

如果要使用下拉式清單方式輸入資料,可以將來源設定為資料的儲存格範圍。本例為儲存格E2:E9。

 

如果將儲存格E2:E9定義名稱為「班級」,則資料驗證準則設定來源為「=班級」

(5) 根據另一個儲存格內容顯示不同清單

例如:根據所選取的分組,顯示不同組別的姓名清單。

設定清單中的來源之公式為:「=OFFSET(H1,MATCH(Q2,分組,0),1,1,7)」

其中儲存格H2:H11被定義為名稱「分組」,利用MATCH函數取代儲存格Q2內容在分組中的第幾列。再代入OFFSET函數取得該列的名字清單(本例為儲存格I4:O4)。因此根據所選取的分組,即可顯示不同的姓名清單。

3 則留言:

  1. 請問,如果我的下拉式選單的選項為 1-55555,在使用清單選擇後只想在儲存格上出現1,要怎麼做?

    回覆刪除
  2. 您的網站對我來說實在太棒了 ^^ 讚.......

    回覆刪除
  3. 這方法真棒,但有個疑問,如果Q2先選丙,P2再選熊均哲,Q2再選乙,這時P2的值是熊均哲有點怪,請問有沒有方法讓P2自動清空嗎?感謝!

    回覆刪除

檢視其他文章

好康東東