妤璇

帖子: 28
主題: 4
精華: 0
積分: 35
點名: 0
作業系統: windows
軟體版本: office2010
閱讀權限: 10
性別: 女
註冊時間: 2014-3-13
最後登錄: 2014-4-18

1^# 跳轉到 » 倒序看帖

打印

字體大小: tT

發表於 2014-3-27 12:35 | 只看該作者

[發問] 想作類似樞紐分析的表格，函數要怎麼寫呢?

點工清單.zip (21.43 KB)

依「點工清單」為依據，照月份將每天的數據及工作內容自動填到「點工工作表」及「工作分類統計表」

請各位好心人幫幫我，謝謝!!

樓主熱帖

ML089

版主 Rank: 7 Rank: 7 Rank: 7

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

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

2^#

發表於 2014-3-27 21:54 | 只看該作者

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))
下拉

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

TOP

妤璇

小學生 Rank: 1

帖子: 28
主題: 4
精華: 0
積分: 35
點名: 0
作業系統: windows
軟體版本: office2010
閱讀權限: 10
性別: 女
註冊時間: 2014-3-13
最後登錄: 2014-4-18

3^#

發表於 2014-3-28 10:15 | 只看該作者

謝謝你的幫忙，你寫的都可以使用，正在努力的理解中，一看到多層函數，腦筋就打結了= =
我在想工作分類統計表中，也可以依你的方式來作，只是將姓名改成工作內容，在年份下新增一列姓名，下午來試看看。
非常非常的感謝喔!!

TOP

妤璇

小學生 Rank: 1

帖子: 28
主題: 4
精華: 0
積分: 35
點名: 0
作業系統: windows
軟體版本: office2010
閱讀權限: 10
性別: 女
註冊時間: 2014-3-13
最後登錄: 2014-4-18

4^#

發表於 2014-3-29 23:46 | 只看該作者

回復 2# ML089

請問我想用點工清單統計表的方式做員工個人的表格，在c2新增姓名，是以下拉式選單做的，將點工清單中的工作事項貼在b5:21，往後可以輸入年、月，再點選姓名，就可以知道某個員工這個月做了那一些工作，我有試著用你的原公式加入姓名…但失敗了，是否能再麻煩你呢??
謝謝!!

TOP

ML089

版主 Rank: 7 Rank: 7 Rank: 7

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

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

5^#

發表於 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 三鍵輸入公式

TOP

ML089

版主 Rank: 7 Rank: 7 Rank: 7

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

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

6^#

發表於 2014-3-30 21:37 | 只看該作者

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

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

TOP

ML089

版主 Rank: 7 Rank: 7 Rank: 7

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

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

7^#

發表於 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 三鍵輸入公式

TOP

妤璇

小學生 Rank: 1

帖子: 28
主題: 4
精華: 0
積分: 35
點名: 0
作業系統: windows
軟體版本: office2010
閱讀權限: 10
性別: 女
註冊時間: 2014-3-13
最後登錄: 2014-4-18

8^#

發表於 2014-3-31 15:42 | 只看該作者

回復 7# ML089

步驟二，你指使用巨集的方式來完成，我蠻有興趣的。
其實當初會使用這一些表格的用意，是希望能以點工清單為依據，使後面的點工清單統計表(日)、工資表及工資統計表(月)能一氣呵成，不用再輸入文字，只要點選年、月、姓名，就能得到各表格的資料了。
我excel的程度只有輸入文字及簡單合計加總，是這個月，常來這裡，才知道原來excel還可以這樣使用，目前只能多看多問，希望未來能成為像你及其他前輩一樣。

另外想請問，若我想作一張每個月的工資統計表，年、月一樣是用輸入的，輸入後就可以知道某個月所有員工的上工總天數，只要有姓名、工作天數就好，不知道要用那一個函數，我用vlookup抓取資料沒辦法顯示，可以再麻煩你嗎??

TOP

妤璇