(1)PowerQueryを使って、一旦縦持ちデータに変換したうえで、ピボットテーブルで集計を行う方法
(2)Dictionaryなどを使ってマクロで作成する方法
などがあると思います。
(1)は、どなたかから回答があればよいとおもいます。
(2)について、若干のコメントをしておきます。
重複を除いた勤務形態の作成だけマクロで行い、
基本的に数式処理は手動対応する方法が考えられます。
<<ワークシート>> レイアウトは以下のとおりです。
A列 B列 C列 D列
1行目 4月1日 4月2日 4月3日
2 AAA A2 休 A2
3 BBB A2 休 A2
4 CCC Y2 A2 Y2
5 DDD Y2 A2 Y2
6 EEE 休 ・ 休
7 FFF 休 ・ 休
8 GGG ・ Y2 ・
9 ZZZ ・ Y2 ・
10
11
12 形態別回数 A2 Y2 ・ ■B12：D12にマクロで勤務形態を
13 AAA 2 0 0 書き込む
14 BBB 1 2 0 ・B13 以下には、=COUNTIF($B3:$AF3,B$12)
15 CCC 1 2 0 のような式を予め設定
16 DDD 0 0 1
17 EEE 0 0 1
18 FFF 0 1 2
19 GGG 0 1 2
20 ZZZ 0 0 0
21
22
23 8 9 10 ・B23には、=VLOOKUP(B24,対応表,2,FALSE)
24 形態別時間 A2 Y2 ・ などを設定
25 AAA 16 0 0 ■B11：D11にマクロで勤務形態を書き込む
26 BBB 8 18 0 ・B25 以下には、=B13*B$23 を予め設定
27 CCC 8 18 0
28 DDD 0 0 10
29 EEE 0 0 10
30 FFF 0 9 20
31 GGG 0 9 20
32 ZZZ 0 0 0
(a)マクロで、
・回数のカウント
・時間への換算
・横計、縦計などの計算
をすべて設定することもできるでしょう。
(b)メンテナンス負荷も考慮して、
左上隅の一つのセルにだけ予め数式を埋め込んでおいて、
あとはマクロで必要な数だけコピーしたり、
縦計、横計の計算式をマクロで入れる方法もあるでしょう。
メインテンス負荷、とか仕様がどの程度変わり得るか、あなたのコード作成スキルとか考慮して、
(a)(b)どちらのタイプがよいか、そちらで検討してみて下さい。
各課によって異なる勤務形態を重複を除いてとりだし、
・形態別回数の行と
・形態別時間の行に
書き込むためのマクロは以下のとおりです。
参考にしてください。
Sub test()
Dim dic As Object
Dim rng As Range
Dim body As Range
Dim r As Range
Dim rng1 As Range
Dim rng2 As Range
Dim s As String
Set dic = CreateObject("Scripting.Dictionary")
Set rng = [A1].CurrentRegion
Set body = Intersect(rng, rng.Offset(1, 1))
For Each r In body
s = r.Value
If s <> "休" Then
dic(s) = Empty
End If
Next
'形態別回数の行に、勤務形態を書き込み、ソート
Set rng1 = Columns("A").Find("形態別回数", LookAt:=xlWhole)
Set rng1 = rng1.Offset(0, 1).Resize(1, dic.Count)
rng1.Value = dic.keys ' 勤務形態を書き込む
rng1.Sort key1:=rng1(1), order1:=xlAscending, Orientation:=xlSortColumns
'形態別時間の行に、勤務形態を書き込み、ソート
Set rng2 = Columns("A").Find("形態別時間", LookAt:=xlWhole)
Set rng2 = rng2.Offset(0, 1).Resize(1, dic.Count)
rng2.Value = dic.keys
rng2.Sort key1:=rng2(1), order1:=xlAscending, Orientation:=xlSortColumns
End Sub
