贊助廠商

///本部落格所有文章列表///

搜尋本部落格文章資料

在 Excel 的工作表中,有人想要將一個經年累記錄而成的直式報表轉換成矩型的報表(參考下圖),以方便呈現(資料表不會因長而不易顯示)或是製作統計圖表等。

或許你會認為以剪貼方式也很快可以重組資料,但是如果資料更多時將會不利於處理,使用公式來處理可以一勞永逸。而本例將不以相關查表函數來處理,而是要藉用 SUMPRODUCT 函數來執行查表的結果。

【準備工作】

選取資料範圍,按一下 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:年份、月份、收益。

 

【輸入公式】

儲存格F2:=SUMPRODUCT((年份=F$1)*(月份=$E2)*收益)

選取儲存格F2,貼至儲存格F2:I13。

本題因為是以年份和月份來組成資料,所以:

(年份=F$1)*(月份=$E2):找出在年份資料中符合儲存格F1且在月份資料中符合儲存格E2的 True/False 陣列。(其中的「*」運算,相當於邏輯 AND 運算。)

再於 SUMPRODUCT 函數中,找出對應的「收益」資料陣列。(每組年份和月份項目的條件都只會對應至一個收益項目)

 

【補充資料】

詳細之函數說明,請參閱微軟網站的說明:

SUMPRODUCThttp://office.microsoft.com/zh-tw/excel-help/HP010342935.aspx

SUMPRODUCT:傳回各陣列中所有對應元素乘積的總和。

語法:SUMPRODUCT(array1, [array2], [array3], ...)

array1:要求對應元素乘積和的第一個陣列引數。

array2, array3,...:要求對應元素乘積和的第 2 個到第 255 個陣列引數。

註:各陣列必須有相同的維度(相同的列數,相同的欄數)。否則會傳回錯誤值#VALUE!。並且會將所有非數值資料的陣列元素當成0來處理。

 

創作者介紹
創作者 vincent 的頭像
vincent

學不完.教不停.用不盡

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


留言列表 (2)

發表留言
  • KK
  • 看您另一篇文章才發現範圍定義的組合鍵是 Ctrl+Shift+F3 ......
  • 謝謝您,已將文章更新。造成您的困擾,真是抱歉!

    vincent 於 2016/07/15 16:12 回覆

  • Sam Chen
  • 板大....我要怎樣才能找到我上一章的詢問問題的文章

您尚未登入,將以訪客身份留言。亦可以上方服務帳號登入留言

請輸入暱稱 ( 最多顯示 6 個中文字元 )

請輸入標題 ( 最多顯示 9 個中文字元 )

請輸入內容 ( 最多 140 個中文字元 )

請輸入左方認證碼:

看不懂,換張圖

請輸入驗證碼