2014年6月13日 星期五

Excel-選取月份/日期/時間(多層下拉式清單)

網友提問了一個實用的問題:參考下圖,想要在一個下拉式清單中選取某一月份時,在第二個下拉式清單中只出現該月份清單中的日期;並且在輸入時間後,比對日期對應的起姳/終止時間,如果不在範圍內則以不同色彩標示出來。

這個問題看起來有點複雜,可能對某些讀者而言會有些難度,以下就盡量詳細說明解釋:

【準備工作】

選取儲存格C1:C17,按一下 Ctrl+Shfit+F3 鍵,勾選「頂端列」,定義名稱:一月。

選取儲存格D1:D15,按一下 Ctrl+Shfit+F3 鍵,勾選「頂端列」,定義名稱:二月。

選取儲存格E1:E16,按一下 Ctrl+Shfit+F3 鍵,勾選「頂端列」,定義名稱:三月。

選取儲存格F1:F17,按一下 Ctrl+Shfit+F3 鍵,勾選「頂端列」,定義名稱:四月。

【操作步驟】

1. 選取儲存格A2。

2. 選取功能表[資料/資料工具/資料驗證]選項。

3. 在[資料驗證]對話框中的[設定]標籤中設定:

儲存格內允許:清單/來源:=$C$1:$F$1。

當選取儲存格A2時,即可使用下拉式清單,其清單項目即為:一月、二月、三月、四月。

4. 選取儲存格A5。

5. 選取功能表[資料/資料工具/資料驗證]選項。

6. 在[資料驗證]對話框中的[設定]標籤中設定:

儲存格內允許:清單/來源:=INDIRECT(A2)。

使用 INDIRCET 函數將代入的參數:一月、二月、三月、四月(先前已經定義為名稱),轉換為儲存格範圍,例如:二月為儲存格D2:D15。

7. 選取儲存格A8。

8. 選取功能表[常用/樣式/設定格式化的條件]選項。

9. 選取「新增規則」選項。

10. 新增一條規則:便用公式來決定要格式化哪些儲存格

(1) 輸入公式:=NOT((A8>=OFFSET(I$1,A5,0))*(A8<=OFFSET(J$1,A5,0)))

OFFSET(I$1,A5,0):根據儲存格A5的內容,對照由儲存格I1為時間起始值。

OFFSET(J$1,A5,0):根據儲存格A5的內容,對照由儲存格J1為時間終止值。

(A8>=OFFSET(I$1,A5,0))*(A8<=OFFSET(J$1,A5,0)):其中的「*」相當於執行 AND 邏輯運算,表示判斷儲存格A8是否介於起始值和終止值之間。

再以 NOT 運算子表示判斷儲存格A8是否「」介於起始值和終止值之間。

(2) 設定格式:紅色字。

本例的日期為二月4日時間為「17」,不是在 8 至 16 之間,所以顯示紅色字。

沒有留言:

張貼留言

好康東東