最近有同事問我，客戶傳來的交期，和自家報表裡的交期，格式完全對不上。由於日期格式的排序不同，所以套入函數比對時，會抓不出雙方的差異。
小本碰到這種情況，大多為以下2種狀況：
(1) 一般系統轉出的報表格式，輸出的資料都會是「文字」，並非為「數值」，所以後續在維護報表比對時，會和客戶的資料比對不上。
(2) 每家公司的報表格式都不盡相同，連日期的排序也有些微的差異，就算彼此的資料都是屬於「文字格式」，但因為文字的排序不同，而無法做比對。
那我們該如何排除這個問題呢？
△ 問題：日期格式排序不同，該如何調整比對呢？
在這裡會介紹4個公式組合，他們分別負責不同的工作
● B4:B9 儲存格：將A4儲存格資料轉換成日期格式
「=DATE(RIGHT(A4,4),LEFT(A4,2),MID(A4,FIND("/",A4)+1,2))」
● C4:C9 / F4:F9儲存格：確認A4儲存格是否有轉換成功
「=VALUE(B4)」
● G4:G9 儲存格：C4:C9和F4:F9的資料相互比對是否相符
「=IF(AND(C4>0,C4=F4),TEXT(B4,"yyyy/m/d"),"Error")」
● G2儲存格：計算G4:G9有幾個Error
「=COUNTIF(G4:G9,"Error")」
一、將A4:A9儲存格資料轉換成日期格式至B4:B9
● 大主語為「DATE」，內裡的小主語分別為A、B、C。
「=DATE(RIGHT(A4,4),LEFT(A4,2),MID(A4,FIND("/",A4)+1,2))」
🡺 拆解說明：
A --- Year：RIGHT(A4,4)
操作參考「2019/09/14 Excel函數-037_right-文字」
B --- Mouth：LEFT(A4,2)
操作參考「2019/09/09 Excel 函數-035_left-文字」
C --- Day：MID(A4,FIND("/",A4)+1,2)
C-1「FIND("/",A4)」小小主語，用途是來找”/”符號，由左至右搜尋並回傳此符號在第幾個字的位置。
C-2「MID(A4,FIND("/",A4)+1,2)」從”/”位子數+1，只取2位，也就是日期的位置。
參考「2019/10/13 Excel函數-040_find-文字」
參考「2019/10/13 Excel 函數-036_mid-文字」
🡺 公式向下填充：Ctrl+C(複製)B4儲存格 >>Shift+向下鍵(反白經過的儲存格)至B9 >>Enter(貼上)
二、B4:B9儲存格的日期，轉換成數值(通用格式)。
● 日期的基數都是從1900/1/1(或1904/1/1)為起始日”1”
● 透過Value函數將日期轉化成數值，也就是格式中的通用格式。
「=VALUE(B4)」
參考「2019/08/11 閏年與Excel 1900/1904日期系統_Excel技巧-022」
🡺 公式向下填充：Ctrl+C(複製)C4儲存格 >>Shift+向下鍵(反白經過的儲存格)至C9 >>Enter(貼上)
🡺 可直接複製C4:C9儲存格，直接貼到F4:F9。
三、設定驗算公式1：確認是客戶資料、內部資料是否相符
● 公式設定完一定要另外驗算，當作一個防呆的機制。
「=IF(AND(C4>0,C4=F4),TEXT(B4,"yyyy/m/d"),"Error")」
● 主語「IF」 / 條件設定「AND」 / 格式轉換「TEXT」
🡺 TRUE：條件設定AND「AND(C4>0,C4=F4)」2個條件相符時，為TRUE，就會啟動TEXT格式轉換「TEXT(B4,"yyyy/m/d")」，將B4轉換成日期格式。
🡺 FALSE：C4<(小於)0，及C4<>(不等於)F4則是會顯示”Error”。
參考「2019/04/25 Excel函數-011_if-邏輯」
參考「2019/08/18 Excel函數-033_and-邏輯」
參考「2019/07/27 Excel函數-030_text-文字」
四、設定驗算公式2：確認Error總數
● 當資料多筆時，建議多增加一個驗算的公式，當作總覽用、方便辨識。
「=COUNTIF(G4:G9,"Error")」
操作參考「2019/10/15 Excel函數-041_countif-統計」
● 為什麼要將驗算公式2放在表格的最上頭呢？
因為當分割/凍結窗格時，驗算公式2都不會消失，會在使用者可視範圍內。
參考「2019/09/30 加總放的位置_Excel技巧-025」
KNB BLOG / KOMOTONEKOBOX / KNB BLOG / KOMOTONEKOBOX / KNB BLOG / KOMOTONEKOBOX / KNB BLOG /
KNB碎碎唸：
好久沒來碎碎唸一下，KNB常說「別人的問題，也是自己未來的問題，只是時間來的早晚」所以自己很喜歡問別人，也喜歡別人來問自己，互相交流討論才會形成一個正向的循環，大家也可以一起成長。
所以各位本友們，如果有Excel方面的問題，歡迎在底下留言，或者到粉絲團私訊提問FB#小本聚落。
延伸閱讀：
1. 2019/09/14 Excel函數-037_right-文字
2. 2019/09/09 Excel 函數-035_left-文字
3. 2019/10/13 Excel 函數-036_mid-文字
4. 2019/08/11 閏年與Excel 1900/1904日期系統_Excel技巧-022
5. 2019/10/15 Excel函數-041_countif-統計
6. 2019/04/25 Excel函數-011_if-邏輯
7. 2019/08/18 Excel函數-033_and-邏輯
8. 2019/07/27 Excel函數-030_text-文字
9. 2019/09/30 加總放的位置_Excel技巧-025
小本碰到這種情況，大多為以下2種狀況：
(1) 一般系統轉出的報表格式，輸出的資料都會是「文字」，並非為「數值」，所以後續在維護報表比對時，會和客戶的資料比對不上。
(2) 每家公司的報表格式都不盡相同，連日期的排序也有些微的差異，就算彼此的資料都是屬於「文字格式」，但因為文字的排序不同，而無法做比對。
那我們該如何排除這個問題呢？
△ 問題：日期格式排序不同，該如何調整比對呢？
在這裡會介紹4個公式組合，他們分別負責不同的工作
● B4:B9 儲存格：將A4儲存格資料轉換成日期格式
「=DATE(RIGHT(A4,4),LEFT(A4,2),MID(A4,FIND("/",A4)+1,2))」
● C4:C9 / F4:F9儲存格：確認A4儲存格是否有轉換成功
「=VALUE(B4)」
● G4:G9 儲存格：C4:C9和F4:F9的資料相互比對是否相符
「=IF(AND(C4>0,C4=F4),TEXT(B4,"yyyy/m/d"),"Error")」
● G2儲存格：計算G4:G9有幾個Error
「=COUNTIF(G4:G9,"Error")」
一、將A4:A9儲存格資料轉換成日期格式至B4:B9
● 大主語為「DATE」，內裡的小主語分別為A、B、C。
「=DATE(RIGHT(A4,4),LEFT(A4,2),MID(A4,FIND("/",A4)+1,2))」
🡺 拆解說明：
A --- Year：RIGHT(A4,4)
操作參考「2019/09/14 Excel函數-037_right-文字」
B --- Mouth：LEFT(A4,2)
操作參考「2019/09/09 Excel 函數-035_left-文字」
C --- Day：MID(A4,FIND("/",A4)+1,2)
C-1「FIND("/",A4)」小小主語，用途是來找”/”符號，由左至右搜尋並回傳此符號在第幾個字的位置。
C-2「MID(A4,FIND("/",A4)+1,2)」從”/”位子數+1，只取2位，也就是日期的位置。
參考「2019/10/13 Excel函數-040_find-文字」
參考「2019/10/13 Excel 函數-036_mid-文字」
🡺 公式向下填充：Ctrl+C(複製)B4儲存格 >>Shift+向下鍵(反白經過的儲存格)至B9 >>Enter(貼上)
二、B4:B9儲存格的日期，轉換成數值(通用格式)。
● 日期的基數都是從1900/1/1(或1904/1/1)為起始日”1”
● 透過Value函數將日期轉化成數值，也就是格式中的通用格式。
「=VALUE(B4)」
參考「2019/08/11 閏年與Excel 1900/1904日期系統_Excel技巧-022」
🡺 公式向下填充：Ctrl+C(複製)C4儲存格 >>Shift+向下鍵(反白經過的儲存格)至C9 >>Enter(貼上)
🡺 可直接複製C4:C9儲存格，直接貼到F4:F9。
三、設定驗算公式1：確認是客戶資料、內部資料是否相符
● 公式設定完一定要另外驗算，當作一個防呆的機制。
「=IF(AND(C4>0,C4=F4),TEXT(B4,"yyyy/m/d"),"Error")」
● 主語「IF」 / 條件設定「AND」 / 格式轉換「TEXT」
🡺 TRUE：條件設定AND「AND(C4>0,C4=F4)」2個條件相符時，為TRUE，就會啟動TEXT格式轉換「TEXT(B4,"yyyy/m/d")」，將B4轉換成日期格式。
🡺 FALSE：C4<(小於)0，及C4<>(不等於)F4則是會顯示”Error”。
參考「2019/04/25 Excel函數-011_if-邏輯」
參考「2019/08/18 Excel函數-033_and-邏輯」
參考「2019/07/27 Excel函數-030_text-文字」
四、設定驗算公式2：確認Error總數
● 當資料多筆時，建議多增加一個驗算的公式，當作總覽用、方便辨識。
「=COUNTIF(G4:G9,"Error")」
操作參考「2019/10/15 Excel函數-041_countif-統計」
● 為什麼要將驗算公式2放在表格的最上頭呢？
因為當分割/凍結窗格時，驗算公式2都不會消失，會在使用者可視範圍內。
參考「2019/09/30 加總放的位置_Excel技巧-025」
KNB BLOG / KOMOTONEKOBOX / KNB BLOG / KOMOTONEKOBOX / KNB BLOG / KOMOTONEKOBOX / KNB BLOG /
KNB碎碎唸：
好久沒來碎碎唸一下，KNB常說「別人的問題，也是自己未來的問題，只是時間來的早晚」所以自己很喜歡問別人，也喜歡別人來問自己，互相交流討論才會形成一個正向的循環，大家也可以一起成長。
所以各位本友們，如果有Excel方面的問題，歡迎在底下留言，或者到粉絲團私訊提問FB#小本聚落。
延伸閱讀：
1. 2019/09/14 Excel函數-037_right-文字
2. 2019/09/09 Excel 函數-035_left-文字
3. 2019/10/13 Excel 函數-036_mid-文字
4. 2019/08/11 閏年與Excel 1900/1904日期系統_Excel技巧-022
5. 2019/10/15 Excel函數-041_countif-統計
6. 2019/04/25 Excel函數-011_if-邏輯
7. 2019/08/18 Excel函數-033_and-邏輯
8. 2019/07/27 Excel函數-030_text-文字
9. 2019/09/30 加總放的位置_Excel技巧-025
留言
張貼留言