[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『勤務表の集計』(健太)
以下のような1カ月の勤務表があります。
5行目が日付、6行目が曜日、それ以下は勤務者ごとの勤務状況になります。
AR列以降は、C列の基になる時間が入力されています。
また、別シートに祝祭日の一覧表も作成しています。
これらを参照して、平日、土曜日、日曜日、祝祭日の日数をカウントさせたいと思います。
勤務の形態は、以下のように、「○」「●」「■」「△」を表示していますが、これ以外に増えても可能なような作りにしたいです。
これらは全て勤務時間の異なるものを表していますが、可能ならば、平日の○、●、■、△、土曜日の○、●、■、△・・・
という詳細なカウントを出したいです。難しいようであれば、平日は何日、土曜日は何日・・・というカウントでも構いません。
祝祭日が土日に重なった場合は、祝祭日としてカウントします。
(6日は祝祭日と仮定しています)
B CDEFGHI ・・・ AR AS AT AU AV AW AX
5 1234567
6 氏名 月火水木金土日
7 山田次郎 ○ ●△■△△ 8 10 4 6 4 4
5 鈴木太郎 ●● ○ ○ 10 10 8 8
6 佐藤三郎 ■ ○ △ ○ 6 8 4 8
7 山田次郎 ○ ○ ○ ■ ・・・ 8 8 8 6
上記をカウントさせた結果は以下のようになります。
平日 土曜日 日曜日 祝祭日
○●■△ ○●■△ ○●■△ ○●■△
7 山田次郎 111 1 1
5 鈴木太郎 12 1
6 佐藤三郎 1 11 1
7 山田次郎 3 1
上記の集計を基にAR列から時間集計します。
平日 土曜日 日曜日 祝祭日
○●■△ ○●■△ ○●■△ ○●■△
7 山田次郎 81064 4 4
5 鈴木太郎 820 8
6 佐藤三郎 8 64 8
7 山田次郎 24 6
どうぞよろしくお願いします。
< 使用 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.どんな表なのか分かりませんが、それを利用して、 7行目に平・土・日・祝を区別を表示させる。(下図参照)
2.下記の数式を入力する (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.