標題:
[發問]
想作類似樞紐分析的表格，函數要怎麼寫呢?
[打印本頁]
作者:
妤璇
時間:
2014-3-27 12:35
標題:
想作類似樞紐分析的表格，函數要怎麼寫呢?
[attach]17886[/attach]
依「點工清單」為依據，照月份將每天的數據及工作內容自動填到「點工工作表」及「工作分類統計表」
請各位好心人幫幫我，謝謝!!
作者:
ML089
時間:
2014-3-27 21:54
[attach]17890[/attach]
A1年份，輸入103，顯示103年(儲存格格式 0年)
C1月份，輸入2，顯示2月份年(儲存格格式 0月份)
顯示該月份日期
C2 =IF(MONTH(DATE($A$1+1911,$C$1,COLUMN(A1)))<>$C$1,"",COLUMN(A1))
右拉
顯示短星期
C3 =IF(C2="","",TEXT(DATE($A$1+1911,$C$1,C$2),"[$-804]aaa"))
右拉
統計工時
C4 =IF(OR(C$3="",B4=""),"",SUMIFS(點工清單!$F:$F, 點工清單!$B:$B,$B4, 點工清單!$A:$A,DATE($A$1+1911,$C$1,C$2)))
右拉下拉
統計該月工時
AH4 =IF(B4="","",SUM(C4:AG4))
下拉
作者:
妤璇
時間:
2014-3-28 10:15
謝謝你的幫忙，你寫的都可以使用，正在努力的理解中，一看到多層函數，腦筋就打結了= =
我在想工作分類統計表中，也可以依你的方式來作，只是將姓名改成工作內容，在年份下新增一列姓名，下午來試看看。
非常非常的感謝喔!!
作者:
妤璇
時間:
2014-3-29 23:46
回復
2#
ML089
請問我想用點工清單統計表的方式做員工個人的表格，在c2新增姓名，是以下拉式選單做的，將點工清單中的工作事項貼在b5:21，往後可以輸入年、月，再點選姓名，就可以知道某個員工這個月做了那一些工作，我有試著用你的原公式加入姓名…但失敗了，是否能再麻煩你呢??
謝謝!!
作者:
ML089
時間:
2014-3-30 21:10
本帖最後由 ML089 於 2014-3-30 21:26 編輯
回復
4#
妤璇
步驟一 :
建立下拉式選單的基本資料如下
[attach]17909[/attach]
可以使用公式來設定資料動態範圍
[公式]-[定義名稱]-輸入名稱及參照到
名稱 參照到
年份 =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)
複製代碼
步驟二 :
下拉式選單的基本資料來源可以使用 點工清單 資料頁來製作，不用寫公式直接用 [資料] - [進階篩選] (勾選 不選重複的記錄)，再用人工複製就可以。
此項不建議用公式來處理，當資料時多影響電腦速度很大，其實可以用巨集錄製方式來完成，有興趣後續再說。
[attach]17910[/attach]
作者:
ML089
時間:
2014-3-30 21:37
步驟三
選單設定，使用 [資料]-[資料驗證]-勾選(清單) 及來源(=定義名稱)來設定
年份、月份、工別、工作事項、工作樓層等都可以依照[人員姓名]方式設定清單
[attach]17911[/attach]
作者:
ML089
時間:
2014-3-30 21:45
回復
4#
妤璇
步驟四
工作分類統計表的公式設定
[attach]17912[/attach]
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))
下拉
作者:
妤璇
時間:
2014-3-31 15:42
回復
7#
ML089
步驟二，你指使用巨集的方式來完成，我蠻有興趣的。
其實當初會使用這一些表格的用意，是希望能以點工清單為依據，使後面的點工清單統計表(日)、工資表及工資統計表(月)能一氣呵成，不用再輸入文字，只要點選年、月、姓名，就能得到各表格的資料了。
我excel的程度只有輸入文字及簡單合計加總，是這個月，常來這裡，才知道原來excel還可以這樣使用，目前只能多看多問，希望未來能成為像你及其他前輩一樣。
另外想請問，若我想作一張每個月的工資統計表，年、月一樣是用輸入的，輸入後就可以知道某個月所有員工的上工總天數，只要有姓名、工作天數就好，不知道要用那一個函數，我用vlookup抓取資料沒辦法顯示，可以再麻煩你嗎??
作者:
妤璇
時間:
2014-3-31 15:51
回復
7#
ML089
打錯了，我是用SUMIF函數，沒辦法對照月份，要怎麼寫函數才可以在輸入月份之後，會將工作人員在某個月的工作天數顯示出來呢?
作者:
ML089
時間:
2014-3-31 15:53
回復
8#
妤璇
2樓資料表就每人每月的統計資料，直接用VLOOKUP去抓此表的資料
作者:
ML089
時間:
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)
作者:
妤璇
時間:
2014-3-31 17:07
回復
10#
ML089
沒想到可以直接從那裡抓取，感恩呢!!
歡迎光臨 麻辣家族討論版版 (http://forum.twbts.com/)
