[數據處理] Excel 日期時間換算成秒

這次獻慶碰到Excel的數據處理問題。

儀器匯出的Excel檔的時間軸是用 "日期跟時間"來表示，而不是用秒來表示。這樣會造成繪圖軟體無法判斷時間，而無法將數據進行繪圖。

或者說很多繪圖軟體會把 "日期跟時間"這種資料格式視作 "字串"，而不是同單位的時間 "數字"。造成繪圖失效。

為了要處理這個問題。一個單純的想法，就是將包含日期跟時間的文字換成單一時間單位。這個時間單位，依據數據分析的需求可以是年、月、日、時、分、秒等。

案例1: 2020/1/15 12:59:35 $\rightarrow$ OOO 秒

以獻慶這次的狀況將包含日期跟時間的文字換成秒，是合理處置。

如何將包含日期跟時間的文字換成秒?

2020/1/15 12:59:35 --> OOO秒

實際上，為了畫圖，我們要知道的是數據點間的相對時間，或者說時間差。

這個問題頗好解決、獻慶運氣不錯，很快就找到了網友提供的方式。可用下列函數達成。

Excel-時間相減後轉成秒數
=TEXT(A2-A1,"[ss]")

可以強制把時間轉換為秒數。若ss改為mm 則轉換為分鐘，不足數無條件捨去。

參考資料: Excel-時間相減後轉成秒數

這個函數可用，但得小改一下。要把A1改成第一筆資料的時間。

例如:

=TEXT(A2-"2020/1/15 12:59:34","[ss]")

這招有用，表示Excel有把某些特定日期跟時間的字串換成秒的功能。這樣就不用像以前使用Fortran等程式時的做法，要用 "天*86400+小時*3600+分*60+秒"這種數學換算。

案例2: 2020/2/14 下午 06:00:49 $\rightarrow$ OOO 秒

乍看之下，這組時間字串只是多了兩個字(上午/下午)而已。

多了兩個中文字而已，同樣的函數應該有用。

如果案例1的招式可以萬用的話，就不會有案例2了。呵呵!

總之就是"掛了"!

那就要來找新的解決方案。

先講結論。函數長這樣:

= DATEVALUE(LEFT(A2,FIND(" ",A2)-1))
+ IF(COUNT(FIND("下午", A2)),TIMEVALUE(RIGHT(A2,8))+0.5,0)
+ IF(COUNT(FIND("上午 12",A2)),TIMEVALUE(RIGHT(A2,8))-0.5,0)
+ IF(COUNT(FIND("上午 01",A2)),TIMEVALUE(RIGHT(A2,8)),0)
+ IF(COUNT(FIND("上午 02",A2)),TIMEVALUE(RIGHT(A2,8)),0)
+ IF(COUNT(FIND("上午 03",A2)),TIMEVALUE(RIGHT(A2,8)),0)
+ IF(COUNT(FIND("上午 04",A2)),TIMEVALUE(RIGHT(A2,8)),0)
+ IF(COUNT(FIND("上午 05",A2)),TIMEVALUE(RIGHT(A2,8)),0)
+ IF(COUNT(FIND("上午 06",A2)),TIMEVALUE(RIGHT(A2,8)),0)
+ IF(COUNT(FIND("上午 07",A2)),TIMEVALUE(RIGHT(A2,8)),0)
+ IF(COUNT(FIND("上午 08",A2)),TIMEVALUE(RIGHT(A2,8)),0)
+ IF(COUNT(FIND("上午 09",A2)),TIMEVALUE(RIGHT(A2,8)),0)
+ IF(COUNT(FIND("上午 10",A2)),TIMEVALUE(RIGHT(A2,8)),0)
+ IF(COUNT(FIND("上午 11",A2)),TIMEVALUE(RIGHT(A2,8)),0)

跟案例1也差太多了...

這種處理方式就真的是在處理字串，主要就是要把 "上午/下午"拿掉，並把12時制換成24時制。

以下簡單講踢鐵板程序

Step 1 使用現成函數，試試看

使用網友提供的函數:
= DATEVALUE(LEFT(A2,FIND(" ",A2)-1))
+ IF(FIND("下午",A2),TIMEVALUE(RIGHT(A2,8))+0.5, TIMEVALUE(RIGHT(A2,8)))
參考資料: Excel-轉換含中文字的日期時間為數值格式

狀況: 可以處理下午，但不能處理上午。上午的數據會變成#VALUE!

Step 2 尋找相關資訊、消化吸收、分析可能問題點

為了解決#VALUE!，找了一堆方法
參考資料:

儘管都是過了，但無效。還是出現#VALUE!

Step 3 發現關鍵問題

會出現#VALUE!，主要來自於FIND這個函數。對於 "FIND不到"的狀況時，會回傳#VALUE!

解決方案，在FIND函數前面使用COUNT函數

IF(COUNT(FIND("下午",A2)),TIMEVALUE(RIGHT(A2,8))+0.5,0)

註: 這裡的0.5是指12小時。微軟在Excel中將一天訂成數值1，而下午的時間要由12時制變成24時制，得加上12小時，也就是要加上0.5。

參考資料: excel函数count的常见用法

Step 4 時間格式對齊

原始數據的 "下午11:59"增加1分鐘到隔天時，會變成 "上午12:00"，其實應該是"上午00:00"，否則會跟中午的12點衝突。

解決方案，將"上午12:00"的數據挑出來，減掉12小時(減0.5)

IF(COUNT(FIND("上午 12",A2)),TIMEVALUE(RIGHT(A2,8))-0.5,0)

其他的上午時間則不變

+ IF(COUNT(FIND("上午 01",A2)),TIMEVALUE(RIGHT(A2,8)),0)
+ IF(COUNT(FIND("上午 02",A2)),TIMEVALUE(RIGHT(A2,8)),0)
+ IF(COUNT(FIND("上午 03",A2)),TIMEVALUE(RIGHT(A2,8)),0)
+ IF(COUNT(FIND("上午 04",A2)),TIMEVALUE(RIGHT(A2,8)),0)
+ IF(COUNT(FIND("上午 05",A2)),TIMEVALUE(RIGHT(A2,8)),0)
+ IF(COUNT(FIND("上午 06",A2)),TIMEVALUE(RIGHT(A2,8)),0)
+ IF(COUNT(FIND("上午 07",A2)),TIMEVALUE(RIGHT(A2,8)),0)
+ IF(COUNT(FIND("上午 08",A2)),TIMEVALUE(RIGHT(A2,8)),0)
+ IF(COUNT(FIND("上午 09",A2)),TIMEVALUE(RIGHT(A2,8)),0)
+ IF(COUNT(FIND("上午 10",A2)),TIMEVALUE(RIGHT(A2,8)),0)
+ IF(COUNT(FIND("上午 11",A2)),TIMEVALUE(RIGHT(A2,8)),0)

註: 其實"上午 01"~"上午 11"這段應該有更好的寫法。例如，資料範圍判斷法，但是獻慶這裡就不多耗時間研究了。整體功能性先出來，可以解決現階段問題就好。

Step 5 回到案例1方式處理

把 "上午/下午"拿掉，並把12時制換成24時制後，就可以依照案例1的方式處理。完工!

以下兩招讓案例1的數據處理招式更好用。免掉複製第一點的時間字串麻煩，特別是整個實驗數據存在許多不同張工作表時。

1. 為了要鎖定同張工作表中的特定儲存格數據。
函數範例: A2 --> \$A\$2
參考資料:

2. 為了要鎖定某張工作表中的特定儲存格數據。
函數範例: A2 --> address1!\$A\$2
參考資料: Excel-取用不同工作表中相同儲存格的值

感想

十幾二十年前，大部分的數據匯出都是全英文，還沒進到多國語言，日期時間轉換的問題還不是那麼嚴重。

就算不會轉換，有時運氣不錯。有的數據很規律，就是每秒量測一個點，那也不用換算，直接使用 "行數"當作秒數就行。

後來，慢慢碰到有些機台會有夾雜中文的資料，有時候用"行數"當作秒數，可以勉強處理掉，但若碰到數據有部分跳掉幾秒鐘、或跳掉幾分鐘、或是幾小時，那就難以處理了。

如果是獻慶自己的程式，那就好處理，在數據輸出時，就附上電腦中的秒數，沒有問題。

但總是會碰到別人給的數據、或是儀器直接匯出的數據，這些都難以請對方或廠商進行修改了。

很多時候，如果數據重要性沒那麼高，那也就算了。

但這次算是踢到鐵板了，閃不掉，那就要認真面對。

這次處理完成，也一起解決十多年前就掛在心裡的問題! 收穫很多!

GOOD! ^_^

感恩

眾多網友提供相關資料，還有函數範例，供參考。才讓此問題可以順利解決。

(FB link: https://www.facebook.com/hsienching.chung/posts/3165177583495189 )

搜尋此網誌

夢想與創造