close

2357

如何擷取Excel表格中資料-MIDLENSEARCHOFFSET

優雅痞子:「如何自動抓取特定儲存格(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

OFFSET函數

2017.05.24

388圖文實例詳解OFFSET函數的使用方法

2019.07.12

2137Excel 自動判斷缺少部分插入空白列

2020.07.01

1062EXCEL偏移計算-OFFSET函數

2020.05.07

997Excel公式問題-OFFSET函數

2018.05.23

Q129VLOOKUP與合併儲存格(不同格數合併,5格內)

2018.12.01

1065EXCEL 注塑機問題統計表製作-日期BY機台

2019.07.13

2137Excel 自動判斷缺少部分插入12

2020.04.21

982公式參照運算-OFFSET函數

2019.07.12

2136如何複製台鐵網頁資料並貼在同一儲存格內

2020.08.14

2004Excel數值交換-OFFSET函數

2018.07.15

839增加記錄不需要重新計算-SumproductOffsetCount函數

2019.09.21

2208Excel函數用法-COUNTCOUNTIFIFIFERRORMATCHMIDVLOOKUPOFFSET

2018.11.15

1045Excel 如何用不同工作天進行加總及平均-通用版

 

點取F3儲存格輸入公式:

=MID(OFFSET($B$9,(ROW()-3)*2,0),SEARCH("速率=he",OFFSET($B$9,(ROW()-3)*2,0),1),LEN("速率=he")+5)再將公式複製到F4:F7儲存格。


arrow
arrow
    創作者介紹

    錦子老師 發表在 痞客邦 留言(0) 人氣()