在 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))

 

【輔助技巧】

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

 

【補充資料】

詳細函數說明，請參考微軟網站：

OFFSET：http://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 必須是正數。

arrow
arrow
    全站熱搜

    vincent 發表在 痞客邦 留言(1) 人氣()