贊助廠商

學不完.教不停.用不盡文章列表

有網友問到:最近排班時會遇到七休一的問題,如何在 Excel 中如果連續排到 7 天時即給予警示?

以下圖中的排班格式為例(排班的形式可能很多種),『V』記號表示要排班,在連續 7 天以上被排班時,給予紅色粗體字來識別。

Excel-設定連續7天以上被排班時給予警示(設定格式化的條件)

通常這類問題,都只要透過「設定格式化的條件」來處理。假設,整個日期報表是由第 2 列開始,所以從第 8 列開始設定格式化的條件。

1. 選取B8:B27。

2. 選取[常用/樣式]功能表中的「設定格式化的條件/新增規則」選項。

3. 選取規則類型:使用公式來決定要格式化哪些儲存格。

4. 輸入編輯規則:=COUNTIF(B2:B8,"V")>6

5. 設定格式:紅色粗體字。

之後,如果增加日期時,只要將已設定好格式化的條件的儲存格格式,複製到新增日期的儲存格即可,不需要再重設。

Excel-設定連續7天以上被排班時給予警示(設定格式化的條件)

vincent 發表在 痞客邦 留言(3) 人氣()

在日常生活中,有時會用到要在一個數值區間中計算含有某個數字的個數,該如何處理?

以下圖的 Excel 例子,要計算 1~500 的數值區間中,共有幾個數含有『4』?例如:4, 140, 403, ...,這些數都含有 4。

Excel-在一個數值區間中計算含有某個數字的個數(SUBSTITUTE,ROW)

 

【公式設計與解析】

儲存格C2:{=SUM(1*(SUBSTITUTE(ROW(1:500),"4","")<>ROW(1:500)&""))}

這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵,Excel 會自動產生「{}」。

(1) SUBSTITUTE(ROW(1:500),"4","")

在陣列公式中對 1~500 的數值利用 SUBSTITUTE 函數將數值中的 4 以空白取代,其傳回 500 個新的數字組成的字串陣列。

(2) ROW(1:500)&""

在陣列公式中將 1~500 的數值轉換為字串。

(3) SUBSTITUTE(ROW(1:500),"4","")<>ROW(1:500)&"")

判斷第(1)式和第(2)是否不相等,若是,代表該數含有 4;若不是,代表該數含 4。其結果傳回 TRUE/FALSE 陣列。

(4) 1*(SUBSTITUTE(ROW(1:500),"4","")<>ROW(1:500)&"")

利用『1*』運算,將第(3)式傳回的 TRUE/FALSE 陣列轉換為 1/0 陣列。

(5) SUM(1*(SUBSTITUTE(ROW(1:500),"4","")<>ROW(1:500)&""))

將第(4)式傳回的 1/0 陣列加總,其總和即為含有 4 的數值個數。

vincent 發表在 痞客邦 留言(1) 人氣()

您尚未登入,將以訪客身份留言。亦可以上方服務帳號登入留言

請輸入暱稱 ( 最多顯示 6 個中文字元 )

請輸入標題 ( 最多顯示 9 個中文字元 )

請輸入內容 ( 最多 140 個中文字元 )

請輸入左方認證碼:

看不懂,換張圖

請輸入驗證碼