Excel關鍵字參照引用:Indirect、Match、Row動態抓取資料

Excel取得全部網頁資料,設置自動更新之後,很快會發現其實只需要特定內容,網頁結構也可能改變,本文介紹Indirect、Match、Row函數組合,關鍵字動態抓取資料。

上一節設置網頁自動更新,但是發現重新取得的資料會亂掉,如前所述,這是因為網站內容並非一成不變,首先呈現的內容會隨著時間刷新,這個其實不會影響,展覽活動還是在相同位置,只是活動內容不同,Excel的固定連結會一如預期把更新後的資料引用過來。然而除了內容之外,網站版面有可能也會變動,這時就不能單純使用固定連結,以下具體介紹較佳作法:

一、「台北蘇荷兒童美術館」所取得的網頁資料,其下載到Excel的資料都是在第一欄(A欄),第122列開始是「展覽資訊 > 主題展場」,第126列開始每一列是真正的展覽活動,共有7項,所以類似第一節第七步驟的彙總表,資料編輯列的引用來源是從「=蘇荷兒童美術館!A126」到「=蘇荷兒童美術館!A131」(Excel表格至多六項)。

台北蘇荷兒童美術館

二、從上個步驟分析可知,台北蘇荷兒童美術館網頁關於展覽的部份,都會在關鍵字「展覽資訊 > 主題展場」的下四列開始羅列,所以先以函數公式「=MATCH(B1,蘇荷兒童美術館!A:A,0)」取得這個關鍵字所在的列號,公式結果正是「122」,接著藉助公式「=ROW()」傳回所在列號的特性,最後設計公式:「=INDIRECT(“‘蘇荷兒童美術館’!A”&$B$4+ROW()-2)」,剛好是引用「展覽資訊 > 主題展場」下四列開始依序的儲存格內容,正是台北蘇荷兒童美術館網頁上的展覽活動。

=MATCH(B1,蘇荷兒童美術館!A:A,0)

三、接下來是「高雄歷史博物館」所取得的網頁資料, 其下載到Excel的資料分三欄,第一欄第161列(儲存格「A61」)是關鍵字「當期特展」,因為展覽活動介紹清單會從儲存格「C63」開始,也就是「當期展覽」的下兩欄下兩列,不過要注意到這裡每個活動中間因為有展期、展覽地點、空白列、展覽說明,所有會空四列。

高雄歷史博物館

四、先以函數公式「=MATCH(B1,高雄歷史博物館!A:A,0)」取得這個關鍵字所在的列號,公式結果是「61」,接著藉助公式「=ROW()」傳回所在列號的特性,因為原始資料會有下四列的問題,巧妙變換一下公式:「=(ROW()-5)5-4」,如圖所示這樣可以每下一列的數值加5,結果是「1,6,11,16,21,…」最後設計公式:「=INDIRECT(“‘高雄歷史博物館’!C”&$B$4+((ROW()-7)5-4+1))」,剛好就是引用「當期特展」下兩欄下兩列開始依序的儲存格內容,中間四列不計,這正是高雄歷史博物館網頁上的當期展覽活動。

=MATCH(B1,高雄歷史博物館!A:A,0)

五、再來一個「科學工藝博物館」所取得的網頁資料,有了前兩次網頁的基礎,這個應該不難理解其規則。

科學工藝博物館

六、關於科學工藝博物館引用展覽活動單的公式說明如下,基本概念和前面兩個網站類似,只是在決定關鍵字和每隔幾列作些微變化。

科學工藝博物館引用展覽活動單

七、綜合起來,四個網頁彙總資料的函數公式整理如下:

四個網頁彙總資料的函數公式整理

這一節介紹三個網頁引用資料的函數公式,雖然公式看起來不是那麼簡單,但仔細分析,每個公式都是使用到「Indirect」、「Match」、「Row」這三個函數,而且有著類似的架構,之所以不厭其煩地一再重覆這個過程,一方面是讓讀者熟悉這個有其實用性的函數用法,另一方面這麼一來,讀者應該能領悟到這些網頁內容不同,但似乎又有一套相同的規則在裡面,正因為如此,才能夠以類似的函數公式引用資料,掌握這一點,對於以後章節的應用相當有幫助。

每天學習,每天充電:VBA爬蟲文章合集

最新文章: