2011年1月22日 星期六

Excel-將多個工作表資料組合成一個資料表

在 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

1 則留言:

  1. 看到您這篇發表,我非常想請問您,
    如果要隨機抽取2-6個工作表中的各2個題目到第一個工作表中,應該怎麼做會比較好呢?

    回覆刪除

檢視其他文章

好康東東