網友問到:(如下圖)如何標示兩個員工工作時間重疊區域?

在 Excel 的工作表中記錄了 A 和 B 員工的起迄工作時間,如何依據這個時間繪出二人重疊的時間區域?

Excel-根據兩個員工的起迄工作時間繪出兩人重疊的時間區域

 

【公式設計與解析】

1.產生每隔30分鐘的時間數列

儲存格A4:=TIME(8+INT((ROW(1:1)-1)/2),MOD((ROW(1:1)-1),2)*30,0)

複製儲存格A4,貼至儲存格A4:A40。

產生 08:00 AM→08:30 AM→09:00 AM→09:30 AM→10:00 AM→... 數列。

 

2. 產生 A 和 B 工作時間範圍圖示

儲存格B4:=($A4>=B$2)*($A4<B$3)

產生時間在工作時間內者產生「1」,否則產生「0」。

其中「*」運算子的作用相當於執行邏輯 AND 運算。

複製儲存格B4,貼至儲存格B4:B40。

再設定格式化的條件:將「>=1」者以綠燈圖示表示,其餘不顯示圖示。

Excel-根據兩個員工的起迄工作時間繪出兩人重疊的時間區域

接著,複製儲存格B4,貼至儲存格C4:C40。

其中,儲存格C4:=($A4>=C$2)*($A4<C$3),原理同上。

 

3. 產生 A 和 B 工作時間範圍重疊圖示

儲存格D4:=($A4>=B$2)*($A4<B$3)*($A4>=C$2)*($A4<C$3)

複製儲存格D4,貼至儲存格D4:D40。

再設定格式化的條件:將「>=1」者以紅燈圖示表示,其餘不顯示圖示。

Excel-根據兩個員工的起迄工作時間繪出兩人重疊的時間區域

 

創作者介紹
創作者 vincent 的頭像
vincent

學不完.教不停.用不盡

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