網友問到：在 Excel 的工作表中有一個資料清單(如下圖左)，想要依『日期』欄位來統計每個人員在各個月份的時數小計(如下圖右)，該如何處理？
【公式設計與解析】
選取儲存格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)))：原理同上式。
文章標籤
全站熱搜
非常感謝您的exce操作分享,讓我在工作上受用無窮,也節省了很多的計算及整理的時間~萬分感謝~ 最近又遇到excel操作的瓶頸,懇求指點迷津~ 1.如統計表格表頭有相關文字,無法使用"頂端列"進行公式設定時,應如何克服? 2.平日執行產品各項功能檢測,並設定儲存格條件以辨別是否符合,只是當鍵入N.D.或特殊符號時,會以異常方式出現,且以SUMPRODUCT計算該檢測項目之異常次數時,亦計算之.請問該如何解決這種現象~謝謝~
1.你可以新增一列放置想要的標題列，然後再將該列隱藏即可。或是你以手動方式，一個一個名稱，慢慢定義名稱。 2,不是很了解題意，如果有範例，或許可以增進理解。
問題一:已解決,謝謝~ 問題二:目前公司使用之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 個數方面無法準確的對應。根據問題二的說明，你再試試。
執行人員 日期 項目 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
請問#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"、...。