贊助廠商

///本部落格所有文章列表///

搜尋本部落格文章資料

網友問到:在 Excel 工作表有一個如下圖的上方資料表,如何轉換為下方的資料表?

Excel-垂直資料轉換為矩陣形式(SUMPRODUCT,OFFSET,COLUMN)


【公式設計與解析】

選取儲存格C1:C15,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:入院日。


1. 計算每日人數

儲存格B18:=SUMPRODUCT((入院日=A18)*1)

複製儲存格B18,貼至儲存格B18:B24。

SUMPRODUCT 函數中的『*1』,用以將判斷式傳回的 TRUE/FALSE 陣列,轉換為 1/0 陣列。


2. 填入每日病歷號碼

儲存格C18:{=IFERROR(OFFSET($A$1,SMALL(IF(入院日=$A18,ROW(入院日),""),
COLUMN(A:A))-1,0),"")}

這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵,Excel 會自動加上「{}」。

複製儲存格B18,貼至儲存格B18:H24。

(1) IF(入院日=$A18,ROW(入院日),"")

在陣列公式中判斷入院日儲存格陣列是否和儲存格A18相同,若是,則傳回對應的列號,若否,則會空字串。

(2) SMALL(第(1)式,COLUMN(A:A))

根據第(1)傳回的列號,由小到大依序取出最小者。若COLUMN(A:A)往右方向複製時,則COLUMN(A:A)=1→COLUMN(B:B)=2→COLUMN(C:C)=3→...。

(3) OFFSET($A$1,第(2)式-1,0)

根據第(2)式傳回的列號,代入 OFFSET 函數取出對應的儲存格內容。

(4) IFERROR(第(3)式,"")

因為第(2)式可能傳回錯誤訊,所以令傳回值為錯誤訊息者,改顯示為空字串。

文章標籤
創作者介紹

學不完.教不停.用不盡

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


留言列表 (3)

發表留言
  • ju
  • 非常感謝您解決我工作上的問題!!
  • 悄悄話
  • 悄悄話
找更多相關文章與討論

您尚未登入,將以訪客身份留言。亦可以上方服務帳號登入留言

請輸入暱稱 ( 最多顯示 6 個中文字元 )

請輸入標題 ( 最多顯示 9 個中文字元 )

請輸入內容 ( 最多 140 個中文字元 )

請輸入左方認證碼:

看不懂,換張圖

請輸入驗證碼