image

承接著  79. Excel教學-個人資料表格設計(一) (TEXT, DATE, MID, DATEDIF, NOW)

接下來說明會使用的函數:

  • ◆ 入住、退住換算、生日換算: TEXTDATEMID 函數
  • ◆ 年紀: DATEDIFNOW 函數
  • ◆ 共住天數: IFERROR 函數
  • ◆ 累積天數: IFNOT 函數

 

完成圖如下:

image

 

  介紹【IRERROR】函數  

此函數之前有寫過,請閱讀如下:

75. Excel教學兩個表格,不同工作表的篩選 (VLOOKUPIFERROR)

28. Excel: IFERROR & VLOOKUP & 資料驗證 超好學 應用/附練習檔

 

  共住天數、累積天數應用  

會分成2種的原因如下:

名稱

解釋

共住天數

有入住日期和結案日期

累積天數

有入住日期,到現在仍然持續居住中

 

前文有提到,Excel的日期格式可以互相轉換的格式只有使用「/ 斜線」方式。所以才把所有日期格式統一成yyyy/mm/dd格式,方便天數的計算。

image

  【共住天數】  

image

已經將日期轉換成yyyy/mm/dd格式 (D2F2儲存格),所以共住天數換算非常簡單。

為了自動偵錯函數公式的設計,我會使用IFERROR函數來偵測。

G2儲存格: =IFERROR(F2-D2,"")

IFERROR

如果F2-D2有錯,顯示空白(“”)

F2

2019/09/10

D2

2018/09/01

結果

374

 

備註: G2儲存格的格式必須設定為「通用格式」。

設定位置: 常用→數值→下拉選單→第1個通用格式。

 

 【累積天數】 

image

由於「郭O邦」至現在,仍繼續住宿中,所以必須使用IFNOT函數,來計算入住日期到現在的累積天數。

 

  介紹【IF、NOT】函數  

此函數之前有寫過,請閱讀如下:

10. EXCEL 教學:IF 函數應用可以更好 ()IF函數 / 新增規則

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

 

H4儲存格: =IF(NOT(G4=""),G4,$K$1-D4)

IFERROR

如果G4不等於空白(””),顯示G4,否則顯示K1-D4的數值

G4

因為還沒有結案日期,所以是空白

K1

=NOW函數(現在時間)

D4

結案時間(目前是空白)

結果

769

計算方式解釋:

名稱

解釋

共住天數

結案時間 入住時間

累積天數

現在時間 入住時間

 

延伸閱讀:

79. Excel教學-個人資料表格設計(一) (TEXT, DATE, MID, DATEDIF, NOW)

78. Excel教學-資料驗證完整介紹 (下拉式選單,連動,限制…)

 


    愛偷懶先生 發表在 痞客邦 留言(0) 人氣()