贊助廠商

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

搜尋本部落格文章資料

廣告贊助

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

文章標籤
創作者介紹

學不完.教不停.用不盡

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


留言列表 (3)

發表留言
  • 悄悄話
  • 永宜
  • 最近有弄一個公式
    總共使用2個分頁
    第一個分頁想將第二分頁的日期+英文用公式套到第一分頁去

    顯示結果為 日期+負數+英文

    例如: 13-AA
    但如果分頁二顯示英文某欄是空白的話則會顯示13-
    所以想請問有方法可將多餘的 "-" 負數在沒英文顯示時讓她消失嗎
    而有英文出現時則讓她出現正常的13-AA

    公式為
    =DAY(INDEX(RR!A:I,MATCH(C:C,RR!G:G,0),2))&"-"&INDEX(RR!A:I,MATCH(C:C,RR!G:G,0),9)

    請大師求救
  • 悄悄話
找更多相關文章與討論

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

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

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

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

請輸入左方認證碼:

看不懂,換張圖

請輸入驗證碼