承接著 79. Excel教學-個人資料表格設計(一) (TEXT, DATE, MID, DATEDIF, NOW)
接下來說明會使用的函數:
◆ 入住、退住換算、生日換算: TEXT、DATE、MID 函數◆ 年紀: DATEDIF、NOW 函數- ◆ 共住天數: IFERROR 函數
- ◆ 累積天數: IF、NOT 函數
完成圖如下:
介紹【IRERROR】函數
此函數之前有寫過，請閱讀如下:
75. Excel教學 – 兩個表格,不同工作表的篩選 (VLOOKUP、IFERROR)
28. Excel: IFERROR & VLOOKUP & 資料驗證 超好學 應用/附練習檔
共住天數、累積天數應用
會分成2種的原因如下:
|
名稱 |
解釋 |
|
共住天數 |
有入住日期和結案日期 |
|
累積天數 |
有入住日期，到現在仍然持續居住中 |
前文有提到，Excel的日期格式可以互相轉換的格式只有使用「/ 斜線」方式。所以才把所有日期格式統一成yyyy/mm/dd格式，方便天數的計算。
【共住天數】
已經將日期轉換成yyyy/mm/dd格式 (如D2、F2儲存格)，所以共住天數換算非常簡單。
為了自動偵錯函數公式的設計，我會使用IFERROR函數來偵測。
|
G2儲存格: =IFERROR(F2-D2,"") |
|
|
IFERROR |
如果F2-D2有錯，顯示空白(“”) |
|
F2 |
2019/09/10 |
|
D2 |
2018/09/01 |
|
結果 |
374 |
備註: G2儲存格的格式必須設定為「通用格式」。
設定位置: 常用→數值→下拉選單→第1個通用格式。
【累積天數】
由於「郭O邦」至現在，仍繼續住宿中，所以必須使用IF、NOT函數，來計算入住日期到現在的累積天數。
介紹【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教學-資料驗證完整介紹 (下拉式選單,連動,限制…)
留言列表
SEO 教學 (2)