[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『勤務表の集計』（健太）
以下のような１カ月の勤務表があります。
５行目が日付、６行目が曜日、それ以下は勤務者ごとの勤務状況になります。
AR列以降は、Ｃ列の基になる時間が入力されています。
また、別シートに祝祭日の一覧表も作成しています。
これらを参照して、平日、土曜日、日曜日、祝祭日の日数をカウントさせたいと思います。
勤務の形態は、以下のように、「○」「●」「■」「△」を表示していますが、これ以外に増えても可能なような作りにしたいです。
これらは全て勤務時間の異なるものを表していますが、可能ならば、平日の○、●、■、△、土曜日の○、●、■、△・・・
という詳細なカウントを出したいです。難しいようであれば、平日は何日、土曜日は何日・・・というカウントでも構いません。
祝祭日が土日に重なった場合は、祝祭日としてカウントします。
（６日は祝祭日と仮定しています）
Ｂ ＣＤＥＦＧＨＩ ・・・ AR AS AT AU AV AW AX
５ １２３４５６７
６ 氏名 月火水木金土日
７ 山田次郎 ○ ●△■△△ ８ 10 ４ ６ ４ ４
５ 鈴木太郎 ●● ○ ○ 10 10 ８ ８
６ 佐藤三郎 ■ ○ △ ○ ６ ８ ４ ８
７ 山田次郎 ○ ○ ○ ■ ・・・ ８ ８ ８ ６
上記をカウントさせた結果は以下のようになります。
平日 土曜日 日曜日 祝祭日
○●■△ ○●■△ ○●■△ ○●■△
７ 山田次郎 １１１ １ １
５ 鈴木太郎 １２ １
６ 佐藤三郎 １ １１ １
７ 山田次郎 ３ １
上記の集計を基にAR列から時間集計します。
平日 土曜日 日曜日 祝祭日
○●■△ ○●■△ ○●■△ ○●■△
７ 山田次郎 ８10６４ ４ ４
５ 鈴木太郎 ８20 ８
６ 佐藤三郎 ８ ６４ ８
７ 山田次郎 24 ６
どうぞよろしくお願いします。
＜ 使用 Excel：Excel2013、使用 OS：Windows7 ＞
6日が「祝日」ってExcelに理解させないといけないよ。 （GobGob） 2016/05/27(金) 16:29
一応。カウントだけ。
A B C D E F G H I J K L M N O P 1 1 2 3 4 5 6 7 1 2 3 4 5 6 7 2 月 火 水 木 金 土 日 月 火 水 木 金 土 日 3 祝日 4 山田次郎 ○ ● △ ■ △ △ 8 10 4 6 4 4 5 鈴木太郎 ● ● ○ ○ 10 10 8 8 6 佐藤三郎 ■ ○ △ ○ 6 8 4 8 7 山田次郎 ○ ○ ○ ■ 8 8 8 6 8 9 10 11 12 平日 土曜日 日曜日 祝祭日 13 ○●■△ ○●■△ ○●■△ ○●■△ 14 山田次郎 01010101000000000000000100000001 15 鈴木太郎 01020000000000000100000000000000 16 佐藤三郎 01000101000000000100000000000000 17 山田次郎 03000000000000000000010000000000 18
B14 =TEXT(SUMPRODUCT((MID(B$13,ROW($1:$4),1)=REPT($B4:$H4,($B$2:$H$2<>"土")*($B$2:$H$2<>"日")*($B$3:$H$3="")))*10^(8-ROW($1:$4)*2)),"00000000") C14 =TEXT(SUMPRODUCT((MID(C$13,ROW($1:$4),1)=REPT($B4:$H4,($B$2:$H$2="土")*($B$3:$H$3="")))*10^(8-ROW($1:$4)*2)),"00000000") D14 =TEXT(SUMPRODUCT((MID(D$13,ROW($1:$4),1)=REPT($B4:$H4,($B$2:$H$2="日")*($B$3:$H$3="")))*10^(8-ROW($1:$4)*2)),"00000000") E14 =TEXT(SUMPRODUCT((MID(E$13,ROW($1:$4),1)=REPT($B4:$H4,($B$3:$H$3<> "")))*10^(8-ROW($1:$4)*2)),"00000000")
B14:E14 下へコピー。
※ 3行目に「祝日」情報
※ 全角、半角めんどうなので「二桁数値」で表現
※ 合計はMIDの結果に数値情報を掛け算して、べき乗を調整かな？
・・・8時間MAX×稼動20日として3桁になりそうなんで
面倒なのでやってまへん。
（GobGob） 2016/05/27(金) 16:37
＞別シートに祝祭日の一覧表も作成しています。
１．どんな表なのか分かりませんが、それを利用して、 ７行目に平・土・日・祝を区別を表示させる。(下図参照)
２．下記の数式を入力する (1) C15セル =COUNTIFS($C8:$AG8,C$14,$C$7:$AG$7,C$13) (2) C22セル =SUMIFS($AR8:$BV8,$C$7:$AG$7,C$20,$C8:$AG8,C$21)
右へ、下へコピー
<結果図> 行 ____B____ _C_ _D_ _E_ _F_ _G_ _H_ _I_ _J_ _K_ _L_ _M_ _N_ _O_ _P_ _Q_ _R_ 5 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 6 月 火 水 木 金 土 日 月 火 水 木 金 土 日 月 火 7 氏名 平 平 平 平 平 祝 日 平 平 平 平 平 土 日 平 平 8 山田次郎 ○ ● △ ■ △ △ 9 鈴木太郎 ● ● ○ ○ 10 佐藤三郎 ■ ○ △ ○ 11 山田次郎 ○ ○ ○ ■ 12 13 日数 平 平 平 平 土 土 土 土 日 日 日 日 祝 祝 祝 祝 14 ○ ● ■ △ ○ ● ■ △ ○ ● ■ △ ○ ● ■ △ 15 山田次郎 1 1 1 1 1 1 16 鈴木太郎 1 2 1 17 佐藤三郎 1 1 1 1 18 山田次郎 3 1 19 20 時間 平 平 平 平 土 土 土 土 日 日 日 日 祝 祝 祝 祝 21 ○ ● ■ △ ○ ● ■ △ ○ ● ■ △ ○ ● ■ △ 22 山田次郎 8 10 6 5 4 4 23 鈴木太郎 8 20 8 24 佐藤三郎 8 6 4 8 25 山田次郎 24 6
見栄えは適当に塩梅する。
（半平太） 2016/05/27(金) 16:42
Dim dic, c As Range, rg As Range, y As String
Sheets("output").Cells.ClearContents
Set dic = CreateObject("Scripting.Dictionary")
For Each c In Sheets("勤務表").Range("C3:AG" & Sheets("勤務表").Cells(Rows.Count, 2).End(xlUp).Row).Cells
If c.Value <> "" Then dic(c.Value) = True
Next c
Set rg = Sheets("output").Range("c2")
For Each k In dic.keys
rg.Value = k: rg.Offset(, dic.Count) = k: rg.Offset(, dic.Count * 2) = k: rg.Offset(, dic.Count * 3) = k
rg.Offset(-1).Value = "平": rg.Offset(-1).Offset(, dic.Count) = "土": rg.Offset(-1).Offset(, dic.Count * 2) = "日": rg.Offset(-1).Offset(, dic.Count * 3) = "祝"
Set rg = rg.Offset(, 1)
Next k
Sheets("勤務表").Range("B3:B" & Sheets("勤務表").Cells(Rows.Count, 2).End(xlUp).Row).Copy Sheets("output").Range("B3")
For Each c In Sheets("勤務表").Range("C3:AG" & Sheets("勤務表").Cells(Rows.Count, 2).End(xlUp).Row).Cells
If c.Value <> "" Then
y = Array("", "平", "平", "平", "平", "平", "土", "日")(InStr(1, "月火水木金土日", Sheets("勤務表").Cells(2, c.Column).Value, 1))
dic(Sheets("勤務表").Range("B" & c.Row).Value & c.Value & y) = _
dic(Sheets("勤務表").Range("B" & c.Row).Value & c.Value & y) + 1
End If
Next c
For Each c In Intersect(Sheets("output").Range("c1").CurrentRegion, Sheets("output").Range("c3", Sheets("output").Cells(Rows.Count, Columns.Count)))
c.Value = dic(Sheets("output").Range("B" & c.Row).Value & Sheets("output").Cells(2, c.Column).Value & Sheets("output").Cells(1, c.Column).Value)
Next c
End Sub
（mm） 2016/05/27(金) 17:28
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.