贊助廠商

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

搜尋本部落格文章資料

網友問到:在 Excel 的工作表中有一個資料清單(如下圖左),想要依『日期』欄位來統計每個人員在各個月份的時數小計(如下圖右),該如何處理?

Excel-依日期中的月份對個人小計(SUMPRODUCT)


【公式設計與解析】

選取儲存格A1:C26,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:人員、日期、時數。

表<1>

儲存格F2:
=SUMPRODUCT((人員=$E2)*(VALUE(LEFT(日期,2))=COLUMN(A:A))*時數)

LEFT(日期,2):取出『日期』陣列每個儲存格左邊 2 個字(表示月份),取出的資料為『文字』型態。

VALUE(LEFT(日期,2)):利用 VALUE 函數將上式的文字轉換為『數字』型態。

COLUMN(A:A):向右複製時,COLUMN(A:A)=1→COLUMN(B:B)=2→COLUMN(C:CA)=3→...。

表<2>

儲存格F15:
=SUMPRODUCT((人員=F$14)*(VALUE(LEFT(日期,2))=ROW(1:1))*時數)

ROW(1:1):向下複製時,ROW(1:1)=1→ROW(2:2)=2→ROW(3:3)=3→...。


【延伸學習】

公式=SUMPRODUCT((人員=F$14)*(VALUE(LEFT(日期,2))=ROW(1:1))*時數)

公式中的運算子『*』,會在運算過程中將 TRUE/FALSE 陣列藉由運算轉換為 1/0 陣列。

相當於:

公式=SUMPRODUCT(--(人員=F$14),--((VALUE(LEFT(日期,2))=ROW(1:1))),時數)

--(人員=F$14):將 TRUE/FALSE 陣列藉由運算轉換為 1/0 陣列。

--((VALUE(LEFT(日期,2))=ROW(1:1))):原理同上式。

創作者介紹

學不完.教不停.用不盡

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


留言列表 (4)

發表留言
  • Bomo
  • 非常感謝您的exce操作分享,讓我在工作上受用無窮,也節省了很多的計算及整理的時間~萬分感謝~
    最近又遇到excel操作的瓶頸,懇求指點迷津~

    1.如統計表格表頭有相關文字,無法使用"頂端列"進行公式設定時,應如何克服?
    2.平日執行產品各項功能檢測,並設定儲存格條件以辨別是否符合,只是當鍵入N.D.或特殊符號時,會以異常方式出現,且以SUMPRODUCT計算該檢測項目之異常次數時,亦計算之.請問該如何解決這種現象~謝謝~
  • 1.你可以新增一列放置想要的標題列,然後再將該列隱藏即可。或是你以手動方式,一個一個名稱,慢慢定義名稱。
    2,不是很了解題意,如果有範例,或許可以增進理解。

    vincent 於 2016/02/01 21:19 回覆

  • Bomo
  • 問題一:已解決,謝謝~
    問題二:目前公司使用之Excel為2003版,表格主要設計為
    月份 產品編號 類別 受測單位 As B Cd Cr Cu Ni
    1月 1 海 A ND - - - N/D ※
    其中各分析元素之分析結果,依其類別及標準設定格式條件,如OR(AND($C2="海",$E2>3)AND($C2="空",$E2>0.5)),條件成立則以紅色粗體表示,但經測試輸入值如為ND或N.D.或N/D或其他符號,均會以紅色粗體表示.不符預期.
    問題三:為統計上述表格中,每一產品於每一月份其異常數,故另設計一工作表為
    類別 編號 受測單位 1月
    Total As B Cd Cr Cu Ni
    陸 1 A 6 1 1 1 1 1 1
    各分析元素之公式為SUMPRODUCT(( (檢測總表!$E$2:$E$493>3))*(檢測總表!$B$2:$C$493=$B3)*(檢測總表!$A$2:$A$493=$D$1)) ,A欄為月份,B欄為受測單位之編號,經測試,問題二工作表之元素分析結果如以ND或N.D.或N/D或其他符號輸入,則上述公式之計算亦成立,不符預期
  • 關於問題二:
    因為你的判斷式中並沒有Cu這項(目前為N/D),而E2內容為ND,可是判斷式中卻是數字,顯然是造成錯誤。其中"DN"必然大於數字(利用ASCII碼比對),所以"ND">1、"ND">"MD"、"ND"<"OD"、...。
    問題三:你呈現的資料,第一列有5個,第二列有7個,第三列有10個,
    類別 編號 受測單位 1月
    Total As B Cd Cr Cu Ni
    陸 1 A 6 1 1 1 1 1 1
    個數方面無法準確的對應。根據問題二的說明,你再試試。

    vincent 於 2016/02/18 15:42 回覆

  • Bomo

  • 執行人員 日期 項目
    A 01/05 甲
    A 01/06 丙
    B 01/06 甲
    A 02/03 甲
    C 02/03 丙
    請問如依上述資料統計人員每月執行各項目(約8種)之次數,使用SUMPRODUCT((執行人員=$T$5)*(VALUE(LEFT(日期,2))=$S7)*(項目=T$6))或SUMPRODUCT((執行人員=$T$5)*(MONTH(認定日期)=$S7)*(項目=$S7))兩種方式都無法成功,不知原因為何?懇請解惑,謝謝!!
    PS:T5=人員,S7=月份,T6=各執行項目
  • 請參考:http://isvincent.pixnet.net/blog/post/45871570

    vincent 於 2016/02/16 20:07 回覆

  • Bomo
  • 請問#2的問題是否無法克服~
    目前我們是以0代替ND或N.D.或N/D,但交給客戶時又需再改回ND或N.D.或N/D
    較會發生人為誤差情形,懇請協助解惑~~
    謝謝
  • 關於問題二:
    因為你的判斷式中並沒有Cu這項(目前為N/D),而E2內容為ND,可是判斷式中卻是數字,顯然是造成錯誤。其中"DN"必然大於數字(利用ASCII碼比對),所以"ND">1、"ND">"MD"、"ND"<"OD"、...。

    vincent 於 2016/02/18 15:41 回覆

找更多相關文章與討論

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

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

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

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

請輸入左方認證碼:

看不懂,換張圖

請輸入驗證碼