想作類似樞紐分析的表格，函數要怎麼寫呢? - Excelㄧ般區

帖子: 2025
主題: 13
精華: 0
積分: 2053
點名: 0
作業系統: WIN7
軟體版本: Office2007
閱讀權限: 100
性別: 男
來自: 台北市
註冊時間: 2011-3-2
最後登錄: 2024-3-14

論壇榮譽勳章論壇贊助勳章熱烈參與勳章 2014馬年全勤獎章 2015羊年全勤獎章 2016猴年全勤獎章

2^#

發表於 2014-3-30 21:10 | 顯示全部帖子

本帖最後由 ML089 於 2014-3-30 21:26 編輯

回復 4# 妤璇

步驟一 :
建立下拉式選單的基本資料如下

可以使用公式來設定資料動態範圍
[公式]-[定義名稱]-輸入名稱及參照到

名稱參照到
年份 =OFFSET(設定!$A$2,,,COUNTA(設定!$A:$A)-1)
月份 =OFFSET(設定!$B$2,,,COUNTA(設定!$B:$B)-1)
人員姓名 =OFFSET(設定!$C$2,,,COUNTA(設定!$C:$C)-1)
工別 =OFFSET(設定!$D$2,,,COUNTA(設定!$D:$D)-1)
工作事項 =OFFSET(設定!$E$2,,,COUNTA(設定!$E:$E)-1)
工作樓層 =OFFSET(設定!$F$2,,,COUNTA(設定!$F:$F)-1)

複製代碼

步驟二 :
下拉式選單的基本資料來源可以使用點工清單資料頁來製作，不用寫公式直接用 [資料] - [進階篩選] (勾選不選重複的記錄)，再用人工複製就可以。
此項不建議用公式來處理，當資料時多影響電腦速度很大，其實可以用巨集錄製方式來完成，有興趣後續再說。

{...} 表示需要用 CTRL+SHIFT+ENTER 三鍵輸入公式

帖子: 2025
主題: 13
精華: 0
積分: 2053
點名: 0
作業系統: WIN7
軟體版本: Office2007
閱讀權限: 100
性別: 男
來自: 台北市
註冊時間: 2011-3-2
最後登錄: 2024-3-14

論壇榮譽勳章論壇贊助勳章熱烈參與勳章 2014馬年全勤獎章 2015羊年全勤獎章 2016猴年全勤獎章

3^#

發表於 2014-3-30 21:37 | 顯示全部帖子

步驟三
選單設定，使用 [資料]-[資料驗證]-勾選(清單) 及來源(=定義名稱)來設定
年份、月份、工別、工作事項、工作樓層等都可以依照[人員姓名]方式設定清單

{...} 表示需要用 CTRL+SHIFT+ENTER 三鍵輸入公式

帖子: 2025
主題: 13
精華: 0
積分: 2053
點名: 0
作業系統: WIN7
軟體版本: Office2007
閱讀權限: 100
性別: 男
來自: 台北市
註冊時間: 2011-3-2
最後登錄: 2024-3-14

論壇榮譽勳章論壇贊助勳章熱烈參與勳章 2014馬年全勤獎章 2015羊年全勤獎章 2016猴年全勤獎章

4^#

發表於 2014-3-30 21:45 | 顯示全部帖子

回復 4# 妤璇

步驟四
工作分類統計表的公式設定

B2姓名

C2年份，輸入103，顯示103年(儲存格格式 0年)

E2月份，輸入3，顯示3月份年(儲存格格式 0月份)

顯示該月份日期
C3 =IF(MONTH(DATE($C$2+1911,$E$2,COLUMN(A1)))<>$E$2,"",COLUMN(A1))
右拉

顯示短星期
C4 =IF(C3="","",TEXT(DATE($C2+1911,$E2,C3),"[$-804]aaa"))
右拉

統計工時
C5 =IF(OR(C$3="",$B5=""),"",SUMIFS(點工清單!$F:$F,點工清單!$B:$B,$B$2,點工清單!$A:$A,DATE($C$2+1911,$E$2,C$3),點工清單!$D:$D,$B5))
右拉下拉

統計該月工時
AH5 =IF(B5="","",SUM(C5:AG5))
下拉

{...} 表示需要用 CTRL+SHIFT+ENTER 三鍵輸入公式

帖子: 2025
主題: 13
精華: 0
積分: 2053
點名: 0
作業系統: WIN7
軟體版本: Office2007
閱讀權限: 100
性別: 男
來自: 台北市
註冊時間: 2011-3-2
最後登錄: 2024-3-14

論壇榮譽勳章論壇贊助勳章熱烈參與勳章 2014馬年全勤獎章 2015羊年全勤獎章 2016猴年全勤獎章

5^#

發表於 2014-3-31 15:53 | 顯示全部帖子

回復 8# 妤璇

2樓資料表就每人每月的統計資料，直接用VLOOKUP去抓此表的資料

{...} 表示需要用 CTRL+SHIFT+ENTER 三鍵輸入公式

帖子: 2025
主題: 13
精華: 0
積分: 2053
點名: 0
作業系統: WIN7
軟體版本: Office2007
閱讀權限: 100
性別: 男
來自: 台北市
註冊時間: 2011-3-2
最後登錄: 2024-3-14

論壇榮譽勳章論壇贊助勳章熱烈參與勳章 2014馬年全勤獎章 2015羊年全勤獎章 2016猴年全勤獎章

6^#

發表於 2014-3-31 17:06 | 顯示全部帖子

本帖最後由 ML089 於 2014-3-31 22:13 編輯

回復 9# 妤璇

> 用SUMIF函數，沒辦法對照月份，要怎麼寫函數才可以在輸入月份之後，會將工作人員在某個月的工作天數顯示出來呢?

方法一  要使用2個日期篩選
SUMIF(.....,日期, ">=" &  DATE(年,月,1), 日期,  "<=" & DATE(年,月+1,0))

SUMIFS(點工清單!$F:$F,
  點工清單!$B:$B,姓名,
  點工清單!$A:$A,">="&DATE(年+1911,月,1),
  點工清單!$A:$A,"<="&DATE(年+1911,月+1,0)
)

方法二在資料位置增加輔助欄  年月(yyyymm)

{...} 表示需要用 CTRL+SHIFT+ENTER 三鍵輸入公式