2357 |
如何擷取Excel表格中資料-MID、LEN、SEARCH、OFFSET |
優雅痞子:「如何自動抓取特定儲存格(F2:H7)內特定字串並回傳,由於有千列資料之多,故省略如下?」
錦子老師:「其實這是可以用多個函數組合完成的。
點取G2儲存格輸入公式:
=MID(B9,SEARCH("通道=36",B9,1),LEN("通道=36"))
點取H2儲存格輸入公式:
=MID(B17,SEARCH("通道=48",B17,1),LEN("通道=48"))
【公式解說】
LEN(字串) 傳回字串長度。
LEN("通道=48") 傳回通道=48的長度。
SEARCH(尋找字串,字串位址,開始位元) 從字串位址第幾個字元開始尋找字串,傳回其位址。
SEARCH("通道=48",B17,1) 傳回B17儲存格從第1個字元開始尋找通道=48字串,其位於第幾個字元。
MID(字串,開始位元,抓取字元數) 傳回於字串中從指定位址開始抓取多少個字元的內容。
MID(B17,SEARCH("通道=48",B17,1),LEN("通道=48")) 傳回B17儲存格從SEARCH("通道=48",B17,1)傳回的字元位址開始抓取LEN("通道=48")傳回的字元數。
點取F3儲存格輸入公式:
=MID(OFFSET($B$9,(ROW()-3)*2,0),SEARCH("速率=he",OFFSET($B$9,(ROW()-3)*2,0),1),LEN("速率=he")+5)
再將公式複製到F4:F7儲存格。
【公式解說】
LEN("速率=he")+5) 傳回速率=he的長度再加5。
(ROW()-3)*2 傳回列編號減3後的值乘以2的數值。
OFFSET(開始儲存格位址,移動列數,移動欄數,包含列數,包含欄數)
OFFSET($B$9,(ROW()-3)*2,0) 傳回從B9儲存格開始向下移動(ROW()-3)*2傳回的列數,移動0欄的儲存格。
SEARCH("速率=he",OFFSET($B$9,(ROW()-3)*2,0),1) 從OFFSET($B$9,(ROW()-3)*2,0)傳回的儲存格中第1個字元開始尋找"速率=he"字串其所在位元。
MID(OFFSET($B$9,(ROW()-3)*2,0),SEARCH("速率=he",OFFSET($B$9,(ROW()-3)*2,0),1),LEN("速率=he")+5) 從OFFSET($B$9,(ROW()-3)*2,0)傳回的儲存格,從SEARCH("速率=he",OFFSET($B$9,(ROW()-3)*2,0),1)傳回的字元開始抓取LEN("速率=he")+5)傳回的字元數。
部落格相關範例
2019.01.10 |
|
2017.05.24 |
|
2019.07.12 |
|
2020.07.01 |
|
2020.05.07 |
|
2018.05.23 |
|
2018.12.01 |
|
2019.07.13 |
|
2020.04.21 |
|
2019.07.12 |
|
2020.08.14 |
|
2018.07.15 |
|
2019.09.21 |
2208Excel函數用法-COUNT、COUNTIF、IF、IFERROR、MATCH、MID、VLOOKUP、OFFSET |
2018.11.15 |
點取F3儲存格輸入公式:
=MID(OFFSET($B$9,(ROW()-3)*2,0),SEARCH("速率=he",OFFSET($B$9,(ROW()-3)*2,0),1),LEN("速率=he")+5)再將公式複製到F4:F7儲存格。
留言列表