學不完．教不停．用不盡

在 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