網友問到:在下圖中,如何在 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 函數將錯誤訊息顯示為空白。

arrow
arrow
    文章標籤
    Excel SUMPRODUCT OFFSET MATCH
    全站熱搜

    vincent 發表在 痞客邦 留言(3) 人氣()