Mr.Lazy愛偷懶先生

網友來信提到，如果自己電腦裡沒有 Ms Excel 軟體的話，該怎麼辦? 我認為這是個好問題，於是在Google試算表內，設計一個完全線上版本的「自動化排班表」。

此次教學，會學到:

功能 : 設計下拉式選單、條件式格式設定的方法。

函數 : 

《第 178 篇連結 : 第178篇.Google 試算表連結。》

完成圖 :

在進入教學步驟之前，建議先將《第178篇練習檔》連結建立副本。

《如何建立副本，參閱 : 179.Google試算表-將 Google 線上 Excel 試算表，建立副本自己使用》

教學步驟

步驟 1 : 設計好排班表內容，和對應數值的位置

步驟 2 : 先建立下拉式的清單列表

1. 新增一個新工作表，並命名「列表清單」，新增所需的清單。

2. 需要的清單 : 年份、月份、國定假日日期名稱、班別。

步驟 3 : 先建立年份、月份之下拉式清單

1. 框選欲顯示年份之範圍。

2. 點選上方之【資料】→【資料驗證】，就會開啟下圖示。

3. 此時，再次確認【儲存格範圍】是否為框選範圍。在條件處。選擇【範圍內的清單】後，框選在「列表清單」中的年份範圍。

4. 確認後，按下【儲存】。

5. 同樣的方法，建立一組「月份」的下拉式清單。

步驟 4  : 設計「日期/星期」的格式

此部分有分兩部分，一個是 C3 儲存格；一個是 D3~AG3 儲存格:

1. C3儲存格公式 : =DATE($C$1,$F$1,1) ；=DATE(年份,月份,日期)，結果為 2/1。

2. D3儲存格公式 : =IFERROR(IF(MONTH(C3+1)=$F$1,C3+1,""),"")，公式較複雜，請透過下表了解:

步驟 5 : 設定日期轉成週別之文字格式

1. C4 儲存格公式 :  =right(text(C3,"ddd"),1)

說 明 : 將C3儲存格顯示之「2/1」數值格式，轉成「週二」之文字格式。並擷取右方開始第一個字元，結果為「二」。

之後，將公式向右拉，即可完成週別的設定。

接下來說的話，我認為很重要!!

《MS Excel 的文字日期格式 和 Google 試算表的文字日期格式是不一樣的。》

我在 Google 試算表的格式上，花費了一些時間查找才發現。Google 試算表中，TEXT 函數正確使用，以及格式設定參閱網址: 《TEXT 函數 文件編輯器說明》

步驟 6 : 當遇到周末時，顯示顏色

當週別顯示為「六」或「日」時，儲存格自動顯示顏色。

1. 先框選範圍 C5 ~ AG15 儲存格。

2. 點選上方之【格式】後，選擇【條件式格式】。點選【+新增其他規則】

3. 確認、完成以下設定:

• 套用範圍 : C5 ~ AG15
• 格式規則 : 選擇「自訂公式 : 」，並輸入公式 : =or(C$4="六",C$4="日")。
• 格式設定樣式 : 設定其中一種顏色。

注意 : 公式中，只將「列」上鎖，這樣子才能達到效果。

關於 OR 函數使用，可閱讀 :

 11. EXCEL 教學：IF 函數應用可以更好 (下)｜IF函數 / AND / OR / NOT / 複製格式

步驟 7 : 如遇「特定假日」，顯示顏色

會使用到先前建立好的「國定假日 日期」部分。

另外，我嘗試許多次，仍無法在【自訂公式】內，用跨工作表來框選範圍。因此，我在「排班表」工作表內，建立「國定假日 日期」的連結。AJ3儲存格欄位。

1. 先框選範圍 C5 ~ AG15 儲存格。

2. 點選開啟【條件格式設定】，選擇【+新增其他規則】。

2. 和「步驟 7」一樣操作；同樣的「條件式格式規則」設定。

3. 在格式規則，選擇「自訂公式」並輸入 :

• =match(C$3,$AJ$1:$AJ$100,0)。
• =match(判斷數值,判斷範圍,搜尋類型)

步驟 8 : 新增「輪班」之下拉式選單

1. 先框選 C5 ~AG15 儲存格。

1. 點選上方之【資料】之【資料驗證】。

2. 和「步驟3」一樣的操作模式。

步驟 9 : 休假時，顯示顏色

操作方式，和「遇周末時，顯示顏色」、遇「國定假期時，顯示顏色」設計方式一樣。