2015年1月20日 星期二

Excel-手動資料轉置(OFFSET,ROW,COLUMN)

在 Excel 中有一個二欄構成的資料表(如下圖左),有網友想要將其轉換為下圖右的二種呈現方式,該如何處理?

在 Excel 中其實有提供「轉置」的功能,也就是複製原始資料後,在貼上資料時選擇「轉置」即可。但是手動處理,對於資料筆數多時,形成很大的困擾,所以來試試如何建構公式來自動化處理。

 

【公式設計】

(1) 二欄直接轉置

儲存格D1:=OFFSET($A$1,(ROW(1:1)-1)/2*6+COLUMN(A:A)-1,,)

(ROW(1:1)-1)/2*6:因為A欄的資料會每間隔二列出現,所以在向下複製公式後得到以下結果。

第 1 列時公式為 ROW(1:1)-1/2*6,時傳回 0;

第 3 列時公式為 ROW(3:3)-1/2*6,時傳回 6;

第 5 列時公式為 ROW(5:5)-1/2*6,時傳回 12;

依此類推,取得儲存格B1、儲存格B7、儲存格B13、… 的資料。

COLUMN(A:A)-1:當向右複製公式時,結果如下。

在D欄公式為COLUMN(A:A)-1,傳回 0;

在E欄公式為COLUMN(B:B)-1,傳回 1;

在F欄公式為COLUMN(C:C)-1,傳回 2;

依此類推,取得儲存格B1:B6的資料。

透過 OFFSET 函數置入以上的參數值,即可取得對應的儲存格內容。

同理:

儲存格D2:=OFFSET($B$1,(ROW(1:1)-1)/2*6+COLUMN(A:A)-1,,)

複製儲存格D1:I2,貼至儲存格D1:I8。

 

(2)取出資料變項轉置

也可以簡化(1)的資料呈現方式,將標題固定於一列。

儲存格D12:=OFFSET($B$1,(ROW(1:1)-1)*6+COLUMN(A:A)-1,,)

公式原理和(1)雷同。

複製儲存格D12:I12,貼至儲存格D12:I15。

沒有留言:

張貼留言

檢視其他文章

好康東東