2013年7月13日 星期六

Excel-計算轉置矩陣後的平均(OFFSET)

在 Excel 中,常常在記錄資料時的方向,可能會和最後的統計報表方向不一樣(橫列相反),如果再透過轉置的動作再來進行統計運算,或許有些不方便。通常透過 OFFSET 函數即可解決這種轉置的運算。

以下圖中的 A 區和 B 區二個資料為例,如果想要計算甲、乙的平均,並且將兩個表合併為一個表,列在下方的統圖表中,公式如何處理?

1. 計算 A 區各月平均

儲存格B13:=AVERAGE(OFFSET($C$2,COLUMN(A1)-1,,,2))

COLUMN(A1)=1,往右複製時,公式自動會轉換為COLUMN(B1)=2、COLUMN(C1)=3、…

OFFSET($C$2,COLUMN(A1)-1,,,2):產生儲存格C2:D2、儲存格C3:D3、儲存格C4:D4、…

再透過 AVERAGE 函數將儲存格範圍予以平均。

  

複製儲存格B13,貼至儲存格B13:I13。

2. 計算 B 區各月平均

參考計算 A 區各月平均的說明,自行練習:

儲存格B14:=AVERAGE(OFFSET($G$2,COLUMN(A1)-1,,,2))

 

【輔助技巧】

如果你看不懂以上的工式,可以選取[公式/公式稽核]功能表中的「評估值公式」,每按一次[評估值]按鈕,則可以在「評估」區中看到公式的運算結果,以幫助理解。

 

【補充資料】

詳細函數說明,請參考微軟網站:

OFFSEThttp://office.microsoft.com/zh-tw/excel-help/HP010342739.aspx

OFFSET 函數:傳回根據所指定的儲存格位址、列距及欄距而算出的參照位址。

語法:OFFSET(reference, rows, cols, [height], [width])

Reference:用以計算位移的起始參照位址。

Rows:左上角儲存格要往上或往下參照的列數。Rows可以是正數(表示在起始參照位址下方)或負數(表示在起始參照位址上方)

Cols:左上角儲存格要往左或往右參照的欄數。Cols 可以是正數(表示在起始參照位址右方)或負數(表示在起始參照位址左方)

Height:所傳回參照位址的高度 (以列數為單位)Height 必須是正數。

Width:所傳回參照位址的寬度 (以欄數為單位)Width 必須是正數。

沒有留言:

張貼留言

檢視其他文章

好康東東