2014年6月7日 星期六

Excel-直式資料清單轉表格(OFFSET)

最近同仁從網路書店搜集到一些購書的資料放在 Excel 的工作表中(參考下圖左),資料是直式的清單,不知如何轉換為表格形式來呈現(參考下圖右)?

這個例子,要使用 OFFSET 函數試試看!

【輸入公式】

(1) 儲存格C2:

=SUBSTITUTE(OFFSET($A$1,(ROW(1:1)-1)*5+COLUMN(A:A)-1,0),"","")

(ROW(1:1)-1)*5:在第 1 列時為 0,第 2 列時為 5,第 3 列時為 10,…。

(ROW(1:1)-1)*5+COLUMN(A:A)-1:在 C 欄的第 1 列為 0,往右複製到 D 欄時為 1,往右複製到 E 欄時為 2,往右複製到 F 欄時為 3。

使用 SUBSTITUTE 函數的用意是要將傳回結果為 0 者(在清單中沒有的資料的儲存格)改以空白顯示。

(2) 儲存格D2:

=SUBSTITUTE(OFFSET($A$1,(ROW(1:1)-1)*5+COLUMN(B:B)-1,0),"作者:","")

使用 SUBSTITUTE 函數將「作者:」置換為空字串。

(3) 儲存格E2:

=SUBSTITUTE(OFFSET($A$1,(ROW(1:1)-1)*5+COLUMN(C:C)-1,0),"出版社:","")

使用 SUBSTITUTE 函數將「出版社:」置換為空字串。

(4) 儲存格F2:

=SUBSTITUTE(SUBSTITUTE(OFFSET($A$1,(ROW(1:1)-1)*5+COLUMN(D:D)-1,0),"定價:",""),"元","")

使用 SUBSTITUTE 函數將「定價:」和「元」置換為空字串。

沒有留言:

張貼留言

檢視其他文章

好康東東