2015年10月5日 星期一

Excel-資料表格式轉換及查詢(OFFSET,SUMPRODUCT)

享: 7Headlines facebook PLURK twitter 
 

網友問到:在 Excel 中有一個如下圖(上)的資料表,要轉換成下圖(下)的格式,該如何處理?參考下圖,上下兩個表格,是要把「進貨、銷貨、收發」由橫向轉換為直向。現在來看看要如何處理。
Excel-資料表格式轉換及查詢(OFFSET,SUMPRODUCT)

【公式設計與解析】
1. 查詢每月「期初」的值
儲存格C9:=SUMPRODUCT(($A$3:$A$6=$A9)*$B$3:$B$6)
透過 SUMPRODUCT 函數在儲存格A3:A6中比對和儲存格A9是否相同,傳回 TRUE/FALSE 陣列。
($A$3:$A$6=$A9)*$B$3:$B$6:其中「*」(乘法運算),相當於執行邏輯 AND 運算。

2. 找出每月「進貨、銷貨、收發」
儲存格C10:=SUMPRODUCT(($C$1:$K$1=C$8)*($C$2:$K$2=$B10)*
(OFFSET($C$2,MATCH($A10,$A$3:$A$6,0),0,1,9)))
MATCH($A10,$A$3:$A$6,0):利用 MATCH 函數找出儲存格A10的內容在儲存格A3:A6中的第幾個。
OFFSET($C$2,MATCH($A10,$A$3:$A$6,0),0,1,9):將上式的傳回傳代入 OFFSET 函數,用以傳回符合儲存格A10內容的資料範圍。例如:儲存格A10為「鱈魚」,則傳回儲存格C3:K3。
上式是一個動態範圍的公式寫法,應特別注意。再藉由:
(1) 條件一/$C$1:$K$1=C$8:查詢符合相同月份(一月、二月、三月)者。
(2) 修件二/$C$2:$K$2=$B10:查詢符合相同項目(進貨、銷貨、收發)者。
以上二式都會傳回 TRUE/FALSE 陣列。
透過 SUMPRODUCT 函數將符合「條件一、條件二」者,在 OFFSET 函數傳回的儲存格陣列中計算「乘積和」,即為這個品名的該月該項目的內容。

沒有留言:

張貼留言

檢視其他文章

好康東東