學不完．教不停．用不盡

網友想要由一個 Excel 資料表(下圖左)中查詢資料，但顯示時欄位內容時，想要由橫式轉為直式顯示(如下圖右)，該如何處理？

【公式設計與解析】

儲存格S5：=OFFSET($C$4,SUMPRODUCT(($A$4:$A$111=$S$3)*
($B$4:$B$111=$S$4)*ROW($A$4:$A$111))-4,ROW(1:1)-1,,)

($A$4:$A$111=$S$3)*($B$4:$B$111=$S$4)*ROW($A$4:$A$111)：
在 SUMPRODUCT 函數中查詢條件：『在店別中符合儲存格S3』和『在月份中符合儲存格S4』，兩個條件皆成立時，所對應儲存格A4:A111的『列號』。其中「*」為執行邏輯 AND 運算。

OFFSET($C$4,上式傳回的列號-4,ROW(1:1)-1,,)：
利用上式傳回的列號代入 OFFSET 函數，其中「-4」是因為第一筆資料在第 4 列。
而 ROW(1:1)=1 在往下複製公式時，會產生 ROW(1:1)=1→ROW(2:2)=2→ROW(3:3)=3→...，在 OFFSET 函數中取得第n欄的資料。這個技巧可以將原本資料以橫式呈現者，轉換為以直式顯示。

複製儲存格S5，貼至儲存格S5:12。

【思考一下】

如果不是每個欄位依序全部顯示時，原來公式將會查到錯誤資料，該如何修正？

儲存格S5：=OFFSET($C$4,SUMPRODUCT(($A$4:$A$111=$S$3)*
($B$4:$B$111=$S$4)*ROW($A$4:$A$111))-4,MATCH(R5,$C$3:$P$3,0)-1,,)

比較前後二個公式差異，在於將『ROW(1:1)-1』改成『MATCH(R5,$C$3:$P$3,0)-1』，即利用 MATCH 函數來查詢儲存格R5位在儲存格C3:P3中的第幾個。(所以，可以使用這個公式來取代原來的公式)

【延伸學習】

如果想要以 INDEX 函數來取代 OFFSET 函數，該如何修正？

儲存格S5：==INDEX($C$4:$P$111,SUMPRODUCT(($A$4:$A$111=$S$3)*
($B$4:$B$111=$S$4)*ROW($A$4:$A$111))-3,MATCH(R5,$C$3:$P$3,0))

複製儲存格S5，貼至儲存格S5:12。

【再動動腦】

如果想要在選取店別和月份後，該筆資料會自動以紅色粗體顯示(如下圖)，該如何處理？

1. 先選取所有資料的儲存格，本例為儲存格A4:P133。

2. 設定格式化的條件，如下：

規則類型：使用公式來決定要格式化哪些儲存格。

編輯規則：=ROW(A4)=SUMPRODUCT(($A$4:$A$111=$S$3)*
($B$4:$B$111=$S$4)*ROW($A$4:$A$111))

(該規則直接由儲存格S5中的公式裡取得即可)

設定格式：文字為紅色、粗體。