學不完．教不停．用不盡

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

【公式設計與解析】

1. 查詢每月「期初」的值

儲存格C9:=SUMPRODUCT((A3:A6=A9)*B3:B6)

透過 SUMPRODUCT 函數在儲存格A3:A6中比對和儲存格A9是否相同,傳回 TRUE/FALSE 陣列。

(A3:A6=A9)*B3:B6:其中「*」(乘法運算),相當於執行邏輯 AND 運算。

2. 找出每月「進貨、銷貨、收發」

儲存格C10:=SUMPRODUCT((C1:K1=C8)*(C2:K2=B10)*
(OFFSET(C2,MATCH(A10,A3:A6,0),0,1,9)))

MATCH(A10,A3:A6,0):利用 MATCH 函數找出儲存格A10的內容在儲存格A3:A6中的第幾個。

OFFSET(C2,MATCH(A10,A3:A6,0),0,1,9):將上式的傳回傳代入 OFFSET 函數,用以傳回符合儲存格A10內容的資料範圍。例如:儲存格A10為「鱈魚」,則傳回儲存格C3:K3。

上式是一個動態範圍的公式寫法,應特別注意。再藉由:

(1) 條件一/C1:K1=C8:查詢符合相同月份(一月、二月、三月)者。

(2) 修件二/C2:K2=B10:查詢符合相同項目(進貨、銷貨、收發)者。

以上二式都會傳回 TRUE/FALSE 陣列。

透過 SUMPRODUCT 函數將符合「條件一、條件二」者,在 OFFSET 函數傳回的儲存格陣列中計算「乘積和」,即為這個品名的該月該項目的內容。