[算表] Indirect、Match、Row三個Excel函數組合

看板Office作者moodyblue時間6年前 (2020/01/23 09:42)推噓1(1推 0噓 11→)

留言12則, 2人參與討論串1/1

Indirect、Match、Row這三個Excel函數組合，竟然足以搜尋任何關鍵字重組報表網誌圖文版： https://www.b88104069.com/archives/4411 上一節設置網頁自動更新，但是發現重新取得的資料會亂掉，如前所述，這是因為網站內容並非一成不變，首先呈現的內容會隨著時間刷新，這個其實不會影響，,展覽活動還是在相同位置，只是活動內容不同，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)」，剛好是引用「展覽資訊 > 主題展場」下四列開始依序的儲存格內容，正是台北蘇荷兒童美術館網頁上的展覽活動。三、接下來是高雄歷史博物館（ 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))」，剛好就是引用「當期特展」下兩欄下兩列開始依序的儲存格內容，中間四列不計，這正是高雄歷史博物館網頁上的當期展覽活動。五、再來一個科學工藝博物館（ https://www.nstm.gov.tw/ExhibitionList.aspx?appname=Exhibition）所取得的網頁資料，有了前兩次網頁的基礎，這個應該不難理解其規則。六、關於科學工藝博物館引用展覽活動單的公式說明如下，基本概念和前面兩個網站類似，只是在決定關鍵字和每隔幾列作些微變化。七、綜合起來，四個網頁彙總資料的函數公式整理如下：這一節介紹三個網頁引用資料的函數公式，雖然公式看起來不是那麼簡單，但仔細分析，每個公式都是使用到「Indirect」、「Match」、「Row」這三個函數，而且有著類似的架構，之所以不厭其煩地一再重覆這個過程，一方面是讓讀者熟悉這個有其實用性的函數用法，另一方面這麼一來，讀者應該能領悟到這些網頁內容不同，但似乎又有一套相同的規則在裡面，正因為如此，才能夠以類似的函數公式引用資料，掌握這一點，對於以後章節的應用相當有幫助。本文內容取自《人人做得到的網路資料整理術》，金石堂網路書店網址： https://www.kingstone.com.tw/basic/2014941521928。延伸閱讀： Excel取得網頁資料（二）：現有連線整理 https://www.b88104069.com/archives/4400 Excel取得藝文活動網路資料 https://www.b88104069.com/archives/4395 VBA如何設置迴圈將多餘資料刪除 https://www.b88104069.com/archives/4376 -- 周末，我們繼續Excel：精華區=>21.心得=>5.其他=>3.office 會計人的Excel小教室： https://www.facebook.com/acctexcel -- ※ 發信站: 批踢踢實業坊(ptt.cc), 來自: 112.22.232.200 (中國) ※ 文章網址: https://www.ptt.cc/bbs/Office/M.1579743758.A.8CD.html