學不完．教不停．用不盡

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

【公式設計與解析】

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 函數傳回的儲存格陣列中計算「乘積和」，即為這個品名的該月該項目的內容。