本節主要分享的函數是MID和FIND
今年春天大家都過了一個特殊的春天,因為疫情,全國人民都變“宅”了。
為了能夠持續推進公司的產品,很多公司都開展了直播。那麼直播結束以後,從平臺上也導出了一些數據,我們也希望通過這些數據能夠分析出直播的效果。
公司負責直播的小姐姐找到了表姐,因為她在分析這些數據的時候遇到了一些小困難。以下是導出類的原始數據:
小姐姐的訴求是以下兩點:
1、 對觀看時長進行排序,查看最長時間與最短時間
2、 平均觀看時長是多少
由於直播平臺導出的數據“觀看總時長”是文本,非標準的時間格式,所以沒有辦法進行直接的排序,那麼需要進行排序,就必須得轉化成統一的時間單位的數據方才可行。表姐決定把這些包含“時分秒”的文本數據全部轉化成秒,以實現小姐姐的訴求。
以“2小時26分鐘42秒”為例,我們需要提取文本里面的數值,需要用到的函數是MID與FIND。
MID函數用於返回文本字符串中指定位置開始的特定數目的字符,數目由用戶指定。
MID(text, start_num, num_chars)
FIND函數用於返回兩個文本串之間的值
FIND(find_text. Within_text, [start_num])
以上圖為例,要將F3“2小時26分鐘42秒”轉化為秒,那麼需要將裡面小時、分鐘以及秒的值提取出來。
步驟一:提取小時之前的值,也就是提取文本“小”所在位置之前的值。
“小”所在位置之前的字符值,公式為=FIND("小",F3)-1;
從第一個字符起,提取“小”之前的文本串,公式為:
<code> H3=MID(F3,1,FIND("小",F3)-1)/<code>
步驟二:提取“時”和“分”之間的分鐘值。
首先返回“時”和“分”之間的字符長度,公式為:
<code>I3=FIND("分",F3)-FIND("時",F3)/<code>
接著繼續用MID函數來提取文本,和小時值不同的是,這次開始的值是從“時”後面的字符開始的,也就是公式
<code>FIND("時",F3)+1/<code>
提取分鐘的值公式:
<code> =MID(F3,FIND("時",F3)+1,(FIND("分",F3)-FIND("時",F3)-1))/<code>
步驟三:提取“鍾”和“秒”之間的秒數值,原理跟提取分鐘值一樣。
<code>J3=MID(F3,FIND("鍾",F3)+1,(FIND("秒",F3)-FIND("鍾",F3)-1))/<code>
根據以上的公式,我們分別提取了小時、分鐘、和秒的值,那接下來是不是可以用公式計算出秒數值呢?答案是不可以的,因為MID函數返回的是文本串,不能直接用於計算的,上圖中顯示的提取值靠左,而不是數值默認的靠右。
現在還出現了另外一個問題,對於“31分鐘23秒”、“16秒”也就是沒有包含的小時的值,我們無法提取分鐘值,而可以提取秒的值,因為上面提取分鐘值得公式是需要找到“時”的字符才有效,因此我們這裡的返回值是錯誤的。
為了能夠有效地提取這兩種格式文本的值,我們還需要添加兩個公式來提取。
分鐘值:
<code>K3=MID(F3,1,FIND("分",F3)-1)/<code>
秒鐘值:
<code>L3=MID(F3,1,FIND("秒",F3)-1)/<code>
可是結果也不是我們想要的,那我們該如何處理呢?下一節繼續分解。