2015年11月1日 星期日

Excel-查表後欄位由橫轉直顯示(SUMPRODUCT,OFFSET)

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

【公式設計與解析】
儲存格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中的第幾個。(所以,可以使用這個公式來取代原來的公式)
Excel-查表後欄位由橫轉直顯示(SUMPRODUCT,OFFSET)

【延伸學習】
如果想要以 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。

【再動動腦】
如果想要在選取店別和月份後,該筆資料會自動以紅色粗體顯示(如下圖),該如何處理?
Excel-查表後欄位由橫轉直顯示(SUMPRODUCT,OFFSET)
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中的公式裡取得即可)
設定格式:文字為紅色、粗體。
image

沒有留言:

張貼留言

好康東東