Indirect、Match、Row這三個Excel函數組合,竟然足以搜尋任何關鍵字重組報表

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

一、台北蘇荷兒童美術館(http://www.artart.com.tw/index.php/Museum/museum_info/maid/24/bid/71)所取得的網頁資料,其下載到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)

三、接下來是高雄歷史博物館(http://khm.org.tw/home02.aspx?ID=$2002&IDK=2&EXEC=L)所取得的網頁資料, 其下載到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)

五、再來一個科學工藝博物館(https://www.nstm.gov.tw/ExhibitionList.aspx?appname=Exhibition)所取得的網頁資料,有了前兩次網頁的基礎,這個應該不難理解其規則。

科學工藝博物館

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

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

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

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

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

本文內容取自《人人做得到的網路資料整理術》,最好學習方法仍然是書本,今天就買本書吧!

金石堂網路書店網址:https://www.kingstone.com.tw/basic/2014941521928


商業數據力實戰|一次學會財報分析與Excel自動化應用:
【填寫課前問卷抽免費課程】



贊贊小屋講課:


20.03.14(六) 高雄場
《職場Excel常用指令、函數及樞紐分析表應用實務講座》

20.03.15(日) 台北場
《財會部門各職能管理報表及Excel應用班》

20.03.16(一) 台北場
《成本會計實務流程及管理分析講座-以存貨成本角度分析企業財務表現》

20.03.28(六)-29(日) 台北場
《會計人的Excel小教室:高效率工作心法!》