有老師問到:如下圖左所示的配課資料,如何整理成下圖右的資料表?

下圖中,原始的配課資料是由上而下的表格式資料呈現,要改成以矩陣形式的資料呈現方式。

Excel-將表格資料改以矩陣形式呈現(以課表為例)

 

【公式設計與解析】

先將配課資料定義名稱,選取儲存格A1:D25,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:班級、節次、教師、科目。

Excel-將表格資料改以矩陣形式呈現(以課表為例)

Excel-將表格資料改以矩陣形式呈現(以課表為例)

假設配課資料放在「資料」工作表中。

儲存格B2:=OFFSET(資料!$D$1,SUMPRODUCT((教師=$A2)*(節次=B$1)*
ROW(科目))-1,0)&CHAR(10)&OFFSET(資料!$A$1,SUMPRODUCT((教師=$A2)*
(節次=B$1)*ROW(科目))-1,0)

複製儲存格B2,貼至儲存格B2:E7。

(1) OFFSET(資料!$D$1,SUMPRODUCT((教師=$A2)*(節次=B$1)*ROW(科目))-1,0)

該式可以取得符合條件者對應的「科目」。

SUMPRODUCT 函數中利用二個條件:(教師=$A2)*(節次=B$1),找出二個條件都符合者,其中運算子「*」,相當於執行邏輯 AND 運算。

SUMPRODUCT((教師=$A2)*(節次=B$1)*ROW(科目))會傳回符合二個條件者的科目之列號。

將上式傳回的列號代入 OFFSET 函數取得該列號對應的儲存格內容。

(2) CHAR(10)

如果要在一個儲存格中要分多列顯示,可以藉助 Alt+Enter 鍵。如果要使用公式來將儲存格內容分列顯示,則可以使用 CHAR(10)

(3) OFFSET(資料!$A$1,SUMPRODUCT((教師=$A2)*(節次=B$1)*ROW(科目))-1,0)

該式可以取得符合條件者對應的「班級」。

Excel-將表格資料改以矩陣形式呈現(以課表為例)

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

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