[公告] 痞客邦新服務上線 每日星座運勢測算[公告] 痞客邦應用市集全新改版![公告] 痞客邦「應用市集」新 App 上架-iFontCloud Professional[公告] 痞客邦後台發表文章提供插入多張圖片新功能[公告]痞客邦新服務上線 部落客商店聚集就在《痞市集》
贊助廠商

我的部落格所有文章列表

搜尋我的部落格文章資料

在 PIXNET 部落格的「所有文章列表」功能中,每一頁只顯示了20筆資料(參考下圖)。如果想要將這些資料匯入 Excel 中,並且合併成一個資料表,不用再切換多次才能看到全部內容,該怎麼做呢?

要將網頁內容匯入 Excel 的工作表,相關做法請參考另一篇文章:
從網頁上取得外部資料(http://isvincent.blogspot.com/2011/01/excel_5843.html)

本例先匯入1~12頁的內容來練習,分別放在工作表名稱1~12中。其中「人氣」欄位中的負數是因為匯入資料時將「(1)」轉成「-1」的原因。觀察這些工作表的內容格局都是一致的。

現在要將工作表名稱1~12的資料合併在一個工作表中,並且依序列出,參考下圖。

每個工作表中要取用儲存格A14:C33,而且工作表的名稱為流水號,可以使用INDIRECT函數來簡化公式的撰寫。INDIRECT函數會傳回文字串所指定的參照位址,並顯示其內容。INDIRECT 函數通常是想在公式中改變參照位址卻不想改變公式本身時使用

儲存格A2:=INDIRECT("'" & INT((ROW(2:2)-2)/20)+1 & "'!A" & MOD(ROW(2:2)-2,20)+14)

公式中的「INT((ROW(2:2)-2)/20)+1」,在向下複製時,ROW(2:2)=2 –> ROW(3:3)=3 –> …

可以產生 1,1,…,1,2,2…,2,3..的數字,即20個1、20個2、20個3、…。

公式中的「MOD(ROW(2:2)-2,20)」,在向下複製時,ROW(2:2)=2 –> ROW(3:3)=3 –> …

可以產生 1,2,…,20,1,2…,20,.. 的數字

「MOD(ROW(2:2)-2,20)+14」可以產生14,15,…,33,14,15,…,33,.. 的數字。

將儲存格A2往下複製時可以產「1!A14、1!A15、…、1!A33、2!A14、2!A15、…、2!A33、…

同理可以產生:

儲存格B2:=INDIRECT("'" & INT((ROW(2:2)-2)/20)+1 & "'!B" & MOD(ROW(2:2)-2,20)+14)

儲存格C2:=INDIRECT("'" & INT((ROW(2:2)-2)/20)+1 & "'!C" & MOD(ROW(2:2)-2,20)+14)*-1

因為來源儲存格為負數,所以C欄的內容要再乘以負1。

儲存格D2:=IFERROR(C2/(TODAY()-A2),0)

因為TODAY()-A2有可能結果為0,所以使用IFERROR函數來避免顯示錯誤訊息。

如果有新增工作表,則只要將儲存格往下複製即可。

如果你將每個工作表中的連線內容設定「檔案開啟時自動更新」,則開啟檔案時可以在一個工作表中看到完整的最新資料。

在A欄中的日期格式,可以設定數字格式為「yyyy/mm/dd」,在視覺上較為整齊。

要如何讓奇、偶數列的底色不同呢?

1. 先將儲存格設定一個底色(例如:較淺的綠色)。

2. 在「設定格式化條件」中設定「使用公式來決定要格式化哪些儲存格」的公式為「=MOD(ROW(2:2),2)<>0」。

如此可以設定奇數列為較深的綠色,偶數列則會維持原來的較淺綠色。

關於INDIRECT函數的詳細說明,請參考微軟網站:

http://office.microsoft.com/zh-tw/excel-help/HP010342609.aspx

Posted by vincent at 痞客邦 PIXNET 留言(4) 引用(0) 人氣()


留言列表 (4)

Post Comment
  • Melody
  • 您好,請問若是每一個工作表並不是固定只有20筆,請問該如何修改呢?謝謝
  • 訪客
  • 您好,
    我想再多瞭解一點工作表間儲存格資料對應及更新的資訊,
    請問可以再解說詳細一點嗎?
    另外,"連線"的功能我好像找不到(2013版)?
  • 1.在一個工作表A的儲存格A1中要使用工作表B的儲存格B1,則儲存格A1=工作表B!B1。
    2.2013版連線功能在「資料/連線」中的「連線」。

    vincent replied in 2014/05/23 23:13

  • Private Comment
  • Private Comment

You haven’t logged in yet, please use guest status to leave message. You can also log in with above service account and leave message

other options