贊助廠商

///本部落格所有文章列表///

搜尋本部落格文章資料

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

在 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) 人氣()


留言列表 (1)

發表留言
  • 訪客
  • 您好:
    如果A上班時間為11:30-15:30,
    B為15:30-21:30。
    兩者無重疊時段,
    但以此表格會出現15:00-15:30區間顯示有兩個人同時上班。
    我與您製作的表格類似,但這個問題依然無法克服。
  • 公式改成:=($A4>=B$2)*($A4<B$3)*($A4>=C$2)*($A4<C$3)

    vincent 於 2018/11/03 23:19 回覆

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

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

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

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

請輸入左方認證碼:

看不懂,換張圖

請輸入驗證碼