2017年8月5日 星期六

Excel-重組表格資訊(SUMPRODUCT,OFFSET,MATCH)

網友問到:在下圖中,如何在 Excel 中將左表轉換右表?
下圖中,同一日期中,A組、B組、C組的人不會重覆。
Excel-重組表格資訊(SUMPRODUCT,OFFSET,MATCH)
【公式設計與解析】
選取日期中有資料儲存格,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:日期。
選取資料的所有儲存格(本例:儲存格C2:E32),定義名稱:資料。
儲存格H3:=IF(SUMPRODUCT((日期=H$1)*(資料=$G3)),"V","")
因為同一日期中,A組、B組、C組的人不會重覆,所以在 SUMPRODUCT 函數中利用雙條件:(日期=H$1)*(資料=$G3),結果只會傳回 1/0。在 IF 函數中將 1/0 對應顯示V/空白
(完整結果如下圖)
Excel-重組表格資訊(SUMPRODUCT,OFFSET,MATCH)

以下提供第二種不同公式寫法,當作練習公式運用:
Excel-重組表格資訊(SUMPRODUCT,OFFSET,MATCH)
選取日期中有資料儲存格,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:日期。
儲存格F3:
=IFERROR(IF(MATCH($G3,OFFSET($A$1,MATCH(H$1,日期,0),2,1,3),0),"V",),"")
複製儲存格F3,貼至儲存格F3:AL14。
(1) MATCH(H$1,日期,0)
在 MATCH 函數中判斷儲存格H1位於日期陣列的位置,傳回一個數值。
(2) OFFSET($A$1,第(1)式,2,1,3)
將第(1)式的傳回值代入 OFFSET 函數,本例傳回儲存格C2:E2。
(3) MATCH($G3,第(2)式,0)
在 MATCH 函數中判斷儲存格G3位於第(2)式取得的儲存格範圍中的位置,傳回一個數值。(傳回值:1、2、3和錯誤訊息)
(4) IF(第(3)式,"V",)
判斷第(3)式的傳回值,若是 1,2,3,則顯示「V」。
(5) IFERROR(第(4)式,"")
利用 IFERROR 函數將錯誤訊息顯示為空白。

沒有留言:

張貼留言

檢視其他文章

好康東東