[EXCEL問題] 製作每日當班人員清冊?

a8325811
個人積分：3分
文章編號：60782291

3分

樓主

2016-07-01 1:19

小弟想做一個當日值班人員清冊的系統，概念大概如下。

1.母資料有全部員工的名字與相關職務與一堆我想加上的訊息。

2.母資料員工右邊有日期表格，該表格右上角一個欄位負責定義第一天的日期，之後靠系統自動判斷產出。(遇到大小月與2月的問題要思索下)

3.把每個員工的當班日期輸入，這邊使用簡單的1來當作有上班。

4.再產生當日值班清冊的工作表列出所有人員與相關資料。

5.清冊的表格只有一份，靠右上角的欄位設定讀取星期數，自動表列出每天人員。

6.每天當班人員不一樣多，該表格能跟著人員數量多寡跟著延伸或縮短。

示意圖片:
母資料表格，右上角黃色日期用來定義第一天禮拜三的初始日期，之後自動生成剩下的。
[EXCEL問題] 製作每日當班人員清冊?

禮拜四的人員名單，可透過最右上角的數字來定義出現的星期數，這邊輸入4所以表現出禮拜四的表格。
[EXCEL問題] 製作每日當班人員清冊?

右上角改成5，生成禮拜五的人員名單，且人數跟禮拜四不一樣，表格最下端會自動貼到最後一筆資料後。
[EXCEL問題] 製作每日當班人員清冊?

大概問題是這樣，希望有大神能幫小弟解決>< 謝謝各位

2016-07-01 1:19 發佈

文章關鍵字 excel 問題當班人員

Mystique Hsiao

Mystique Hsiao
個人積分：7623分
文章編號：60783167

7623分

2樓

2016-07-01 7:33

本人功力有限，
查詢工作表中的日期只能做到放在上側標題下方，
好處是免除額外的設定及進階的 VBA 程式語言！

說明：

母資料表：
E1, E2 右側欄位（F1~K2）直接向右複製即可！

查詢資料表：
為解決星期位置問題並縮短欄位公式長度，
新增 H1 欄位公式（完成後Ｈ欄可隱藏）

A4~C10 欄位為陣列公式（公式最外層為 {}），
A4 欄位輸入時不用加上 {}，
但輸入完畢需以 Ctrl+Shift+Enter 結束，
然後再複製至其它欄位！

雖然您提到母資料表中的第一個日期會是星期三，
但考量日後可能出現第一天不是星期三的狀況，
故於公式中增加判斷式以適用於各種不同需求（否則公式可更精簡）！

My Interior Knowledge is Extraordinaire

YS2000

YS2000
個人積分：63分
文章編號：60798754

63分

3樓

2016-07-02 11:02

a8325811 wrote:
小弟想做一個當日值...(恕刪)

要動態改變表單大小要用到VBA

1.人員名單

儲存格 E1=RIGHT( TEXT($O$1+COLUMN(A:A)-1,"aaa"),1)
儲存格 E2=TEXT($O$1-1+COLUMN(A:A),"d")
選取 E1 E2 把公式向右拉到 K1 K2

2.定義名稱

如上設置定義

3.值班單

名稱填 =IFERROR(INDEX(名單,序列,1),"")
職位填 =IFERROR(VLOOKUP($A6,名單,2,0),"")
電話填 =IFERROR(VLOOKUP($A6,名單,3,0),"")
日期(A8)填 =IFERROR(TEXT(人員名單!O1+CHOOSE(值班單!M1,5,6,0,1,2,3,4),"yyyy年m月d日 aaaa"),"")

4.VBA 程式碼

在VBA設計下 "值班單"工作表裡貼上下面程式碼


Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Application.Intersect(Range("M1"), Range(Target.Address)) Is Nothing Then
        Dim DateEnd As Integer, i As Integer
        DateEnd = Evaluate("日期位置")(1) - 1
        If DateEnd > 7 Then
          Range("A8:J" & DateEnd).Delete Shift:=xlUp
        End If
        i = Evaluate("出勤人數")
        Do Until i < 2
            Range("A6:J7").Copy
            Range("A6:J7").Insert Shift:=xlDown
            i = i - 1
        Loop
        Application.CutCopyMode = False
    End If
End Sub

5.另存為使用巨集工作簿
公式有使用到 "IFERROR()"，EXCEL 版本太就會無法使用

a8325811

a8325811
個人積分：3分
文章編號：60811996

3分

樓主

2016-07-03 18:03

感謝各位大大，小弟我去試試看!

a8325811 wrote:
小弟想做一個當日值...(恕刪)

a8325811

a8325811
個人積分：3分
文章編號：60813541

3分

樓主

2016-07-03 21:13

YS2000 wrote:
要動態改變表單大小要...(恕刪)

大大你好，我發現我用完後，有些資料會重複出現。

類似是禮拜四有王大明陳一陳三出勤

輸出的結果變成王大明陳一陳一

請問是哪邊出錯嗎？

YS2000

YS2000
個人積分：63分
文章編號：60815601

63分

6樓

2016-07-04 0:48

a8325811 wrote:
大大你好，我發現我...(恕刪)

原檔不在身邊,所以重做一遍,發現一個地方有問題

總人數=ROWS(名單)
這個地方原本測試的時候是放一個極大值,後來整理的時候改成這樣,結果定義得不夠大,導致有誤

改成極大值=ROWS(名單)*2 ,應該就可以正常了

定義名稱

日期位置=ROW(值班單!$A$8)
紀錄值班單日期儲存格的位置(會隨著人數變動,初始值A8)，資料變動時，會先用VBA把第一格到日期之間的表格刪掉

出勤人數=IFERROR(SUM(INDEX(出勤表,0,選擇)),0)
人數大於1要用VBA新增表格

出勤表=OFFSET(人員名單!$E$3,0,0,COUNTA(人員名單!$A:$A)-1,7)
名單=OFFSET(人員名單!$A$3,0,0,COUNTA(人員名單!$A:$A)-1,3)
上面兩個是依照A欄非空白儲存格數量動態選取資料範圍，多了一個"名稱" 數量要減1

序列=(極大值-LARGE(INDEX(出勤表,0,選擇)*(極大值-ROW(名單)),INT(ROW()-4)/2))-2
出勤名單排序用

極大值=ROWS(名單)*2
設任一遠大於總人數的值

選擇=CHOOSE(值班單!$M$1,6,7,1,2,3,4,5)
把輸入的星期轉換成出勤表中星期的順序
值班單!$M$1 =>輸入星期的儲存格

附上檔案你比對看看好了
附加壓縮檔: 201607/mobile01-7fc31db3c20417b9e0bdc81e87dddf15.zip

a8325811

a8325811
個人積分：3分
文章編號：60816473

3分

樓主

2016-07-04 7:17

YS2000 wrote:
原檔不在身邊,所以重...(恕刪)

好的，我試試看，謝謝大大^^

a8325811

a8325811
個人積分：3分
文章編號：60822563

3分

樓主

2016-07-04 16:19

YS2000 wrote:
原檔不在身邊,所以重...(恕刪)

大大你好，我用你的方法做了一次，很成功。

我自己改造了格式使表格更匹配我要的。
但有點狀況。

這是我的人員資料，把各部門分開。

名稱設定，基本都照你的，但配合我的版本有更動。

VBA的撰寫，也有更動欄位與數值。

最後輸入3查看禮拜三，但頭兩格空白，後面的資料會每個人重複兩次。

我查看過，人數讀取沒問題，當日幾個人上班都算正確，後面班表複製格數也正確，但不知為啥頭一定會空白。然後資料會重複，我蠻懷疑是我［序列］那部分有錯，大大可以幫我糾正嗎？

謝謝。

（如果可以的話，可以跟我講講序列那一串的思路嗎？其他的我都看懂了，但只有序列不是很明白）

orz

YS2000

YS2000
個人積分：63分
文章編號：60825377

63分

9樓

2016-07-04 20:48

多個部門資料放在相同的欄位裡，建議用格式化表格，或是第一欄放部門名稱，再用 COUNTIF(A:A,"=第一部門") 去抓數量，才不會把其他部門的人也算進去。

序列=(極大值-LARGE(INDEX(出勤表,0,選擇)*(極大值-ROW(名單)),INT(ROW()-4)/2))-2

假設名單為 A3:C10 ， ROW(名單) 會變成 {3,4,5,6,7,8,9,10}

用INDEX(出勤表,0,選擇)
=>第二個參數 ROW 設0時，會抓出 column 為選擇那欄所有的資料

假設選擇那天只有 A3 跟 A10 出勤 =>資料會顯示 {1,0,0,0,0,0,0,1}

所以 INDEX(出勤表,0,選擇)*ROW(名單) => 陣列公式
答案={3,0,0,0,0,0,0,10}

再用 LARGE 去抓，第二個參數設1時顯示第一大的，設2時顯示第二大的...

INT(ROW()-4)/2 =>這個是用來算這欄要顯示第幾大的
這個公式是看顯示表單的規則去修改。
ROW() 會顯寫入公式儲存格本身的 ROW 值
-4是因為資料從 A6 格開始 (ROW(A6)-4)/2 =1
第二個人為 A8格 (ROW(A8)-4)/2= 2
因為原來的值日單的欄位是合併的，多1個人會跳兩個ROW
看你VBA 多1人個只跳1個ROW
所以序列的修正就不用除以2

新表單資料從 A4 開始多1個人 ROW只增加1

所以只要寫 ROW()-3 就好
人員名單從A3開始，所算出來第1人是10 第二人是3，還要再把答案 -2 變成 8跟1 表示答案是表單中的第8筆跟第1筆

序列=LARGE(INDEX(出勤表,0,選擇)*ROW(名單),ROW()-3)-2
這時資料是顛倒顯示的
如果要正常排列要再加上極大值來修正
序列=(極大值-LARGE(INDEX(出勤表,0,選擇)*(極大值-ROW(名單)),ROW()-3))-2

我沒有實際測試試看看應該沒錯

a8325811

a8325811
個人積分：3分
文章編號：60825738

3分

樓主

2016-07-04 21:23

YS2000 wrote:
多個部門資料放在相同...(恕刪)

謝謝大大那麼迅速的解答，小弟等下試試。