錦子老師

優雅痞子：「如何自動抓取特定儲存格(F2:H7)內特定字串並回傳，由於有千列資料之多，故省略如下?」

錦子老師：「其實這是可以用多個函數組合完成的。

點取G2儲存格輸入公式：

=MID(B9,SEARCH("通道=36",B9,1),LEN("通道=36"))

點取H2儲存格輸入公式：

=MID(B17,SEARCH("通道=48",B17,1),LEN("通道=48"))

【公式解說】

LEN(字串) 傳回字串長度。

LEN("通道=36") 傳回通道=36的長度。

SEARCH(尋找字串,字串位址,開始位元) 從字串位址第幾個字元開始尋找字串，傳回其位址。

SEARCH("通道=36",B9,1) 傳回B9儲存格從第1個字元開始尋找通道=36字串，其位於第幾個字元。

MID(字串，開始位元，抓取字元數) 傳回於字串中從指定位址開始抓取多少個字元的內容。

MID(B17,SEARCH("通道=36",B9,1),LEN("通道=36")) 傳回B9儲存格從SEARCH("通道=36",B9,1)傳回的字元位址開始抓取LEN("通道=36")傳回的字元數。

點取F3儲存格輸入公式：

=MID(OFFSET($B$1,SUMPRODUCT(LARGE(($A$9:$A$47>1)*(ROW($A$9:$A$47)),11-ROW()))-1,0),SEARCH("速率=he",OFFSET($B$1,SUMPRODUCT(LARGE(($A$9:$A$47>1)* (ROW($A$9:$A$47)),11-ROW()))-1,0),1),LEN("速率=he")+5)

再將公式複製到F4:F7儲存格。

【公式解說】

=MID(OFFSET($B$1,SUMPRODUCT(LARGE(($A$9:$A$47>1)*(ROW($A$9:$A$47)),11-ROW()))-1,0),SEARCH("速率=he",OFFSET($B$1,SUMPRODUCT(LARGE(($A$9:$A$47>1)* (ROW($A$9:$A$47)),11-ROW()))-1,0),1),LEN("速率=he")+5)

LEN("速率=he")+5) 傳回速率=he的長度再加5。

11-ROW() 傳回11減列號的值。

LARGE(數值資料，數值) 傳回數值資料中第幾大的值。

LARGE(($A$9:$A$47>1)*(ROW($A$9:$A$47)),11-ROW()) 傳回($A$9:$A$47>1)乘上(ROW($A$9:$A$47))後，第11-ROW()大的數值。

SUMPRODUCT(LARGE(($A$9:$A$47>1)* (ROW($A$9:$A$47)),11-ROW())) 統計LARGE(($A$9:$A$47>1)*(ROW($A$9:$A$47)),11-ROW())傳回的數值總和。

OFFSET(開始儲存格位址，移動列數，移動欄數，包含列數，包含欄數) 傳回從開始儲存格位址移動幾列(幾欄)，包含幾列(幾欄)的儲存格內容。

OFFSET($B$1,SUMPRODUCT(LARGE(($A$9:$A$47>1)* (ROW($A$9:$A$47)),11-ROW()))-1,0) 傳回從B1儲存格開始向下移動SUMPRODUCT(LARGE(($A$9:$A$47>1)* (ROW($A$9:$A$47)),11-ROW()))傳回的列數減1，移動0欄的儲存格。

SEARCH("速率=he",OFFSET($B$1,SUMPRODUCT(LARGE(($A$9:$A$47>1)*(ROW ($A$9: $A$47)),11-ROW()))-1,0),1) 從OFFSET($B$1,SUMPRODUCT(LARGE(($A$9:$A$47>1)*(ROW($ A$9:$A$47)),11-ROW()))-1,0)傳回的儲存格中第1個字元開始尋找"速率=he"字串其所在位元。

MID(OFFSET($B$1,SUMPRODUCT(LARGE(($A$9:$A$47>1)*(ROW($A$9:$A$47)),11-ROW()))-1,0),SEARCH("速率=he",OFFSET($B$1,SUMPRODUCT(LARGE(($A$9:$A$47>1)* (ROW($A$9:$A$47)),11-ROW()))-1,0),1),LEN("速率=he")+5)

從OFFSET($B$1,SUMPRODUCT(LARGE(($A$9:$A$47>1)*(ROW($A$9:$A$47)),11-ROW()))-1,0)傳回的儲存格，從SEARCH("速率=he",OFFSET($B$1,SUMPRODUCT(LARGE(($A$9: $A$47> 1)*(ROW($A$9:$A$47)),11-ROW()))-1,0),1)傳回的字元開始抓取LEN("速率=he")+5)傳回的字元數。

部落格相關範例