EXCELで勤務表作成

質問

勤務表の作成をエクセル2003で作成しています。
　　　　１　２　３　４　５　・・
鈴木　　Ａ　Ａ　Ｂ　Ｂ　休
佐藤　　Ｂ　Ｂ　休　休　Ａ
太田　　休　休　Ａ　Ａ　Ｂ
吉田　　Ａ　Ｂ　休　Ｂ　Ａ

上記のように個人別にその日に何の担当業務をするかで作成されたものを
　　１１月１日　　　　　　　　１１月２日　　　　・・・
　Ａ　鈴木　吉田　　　　　　Ａ　鈴木
　Ｂ　佐藤　　　　　　　　　Ｂ　佐藤　吉田
　休　太田　　　　　　　　　休　太田

と日付ごとに業務別に誰が担当するのか別の表で作成する方法があったら教えて下さい。「最初からその表で作成すれば？」とお思いになるかもしれませんが、大人数の休日の管理が難しいので最初の表の作成方法は変えたくありません。ちなみに表の通り一つの業務に複数名担当することもありえますので、できればそれをふまえた上で回答願います。

回答No.3

nobita925

人数が少ない時の方法です。

まず、勤務表（Sheet1）を以下のように作成してください。
Ａ列は識別番号です。

　　Ａ　Ｂ　　　Ｃ　Ｄ　Ｅ　Ｆ　Ｇ
１　　　　　　　１　２　３　４　５　・・
２　１　鈴木　　Ａ　Ａ　Ｂ　Ｂ　休
３　２　佐藤　　Ｂ　Ｂ　休　休　Ａ
４　４　太田　　休　休　Ａ　Ａ　Ｂ
５　８　吉田　　Ａ　Ｂ　休　Ｂ　Ａ

つづいて個人別表記の表（Sheet2）を以下のように

　　Ａ　Ｂ　Ｃ　Ｄ　Ｅ　Ｆ　Ｇ
１　　　１　２　３　４　５　・・
２　Ａ　
３　Ｂ　
４　休　
各セルには
=VLOOKUP(SUMIF(Sheet1!C$3:C$6,$B3,Sheet1!$A$3:$A$6),Sheet3!$A$2:$B$17,2,FALSE)

最後にマスター表（Sheet3）を

1 鈴木
2 佐藤
3 鈴木・佐藤
4 太田
5 鈴木・太田
6 佐藤・太田
7 鈴木・佐藤・太田
8 吉田
9 鈴木・吉田
10 佐藤・吉田
11 鈴木・佐藤・吉田
12 太田・吉田
13 鈴木・太田・吉田
14 佐藤・太田・吉田
15 鈴木・佐藤・太田・吉田

シート１の識別番号は個人を足し算できる数値化しています。
Ａに鈴木・太田といれば１＋４＝５、15 鈴木・佐藤・太田といれば１＋２＋８＝１１となります。
識別番号はそれまでの合計に１足せば出来ます。

人数が４人の時は１５通りしかありませんのでマスター化するのは楽でしょう。
多人数の時は面倒で使えませんが。

お礼

回答ありがとうございます。
視点の違う考え方で目からウロコです。
総勢70名強の勤務表ですが、幅広く各種担当業務をするわけではないので、工夫すればうまくいくかもしれません。

回答No.2

imogasi

多分質問の書き振りから、関数での解決を考えているのでしょう。
答えが今だに出てないのは、関数では難しいからです。
関数はどこに表示したらよいか、データの中身により流動的で動くものは苦手なんです（表の組み換えタイプもその例です）。VBAでやるのが素直です。
仕事との関連でエクセルを使おうとすれば、エクセルVBAの習得は避けて通れないというのが、私の持論です。
やりたくても、自分に力が無ければできない。
これなども課題を丸投げしています。
何とか答えを出してみます。
VBAと関数の折衷案でユーザー定義関数というものを作り、利用してます。
ーーー
例データ　A1:F8
1 2 3 4 5
鈴木 Ａ Ａ Ｂ Ｂ 休
佐藤 Ｂ Ｂ 休 休 Ａ
太田 休 休 Ａ Ａ Ｂ
吉田 Ａ Ｂ 休 Ｂ Ａ
秋田 Ｃ Ｂ Ｃ Ａ Ｃ
田中 Ａ Ｂ 休 Ｂ Ａ
近藤 Ｂ Ａ Ｃ Ａ Ｂ
ーー
ユーザー関数を定義します。
ツールーマクローVBEの画面で、メニューの、挿入ー標準モジュールを選んで出てくる画面に
Function fnd(a, b, n)
d = Range("A18").End(xlUp).Row
m = 0
For i = 2 To d
If Cells(i, a) = b Then
m = m + 1
If m = n Then
fnd = Cells(i, "A")
Exit Function
End If
End If
Next i
fnd = ""
End Function
をコピペします。ただし本番では
Range("A18").の１８行をデータ最下行＋余白行に修正してください。
ーーー
別シートに出すでは手間なので、同一シートのデータ行の下部分に出します。
A20:a24に下記を入れます。A20には日にちの１を入れます。
１０月１日に見せるのは、表示形式で "10月"#"日" を設定。
1
Ａ 鈴木 吉田 田中
Ｂ 佐藤 近藤
Ｃ 秋田
休 太田
B21に
=fnd(MATCH($A$20,$A$1:$G$1,0),$A21,COLUMN()-1)
と入れて、E21まで式を複写します。
ｂ２１：E21を範囲指定し、＋ハンドルを出して、E24まで式を複写します。
結果
上記の通り。
ーー
2日分は　A２７に２、A28:A31にA,B,C,休をコピー張り付け。
B28は式　=fnd(MATCH($A$27,$A$1:$G$1,0),$A28,COLUMN()-1)
１日とどこが、かつなぜ変わったか理解してください。
結果
2
Ａ 鈴木 近藤
Ｂ 佐藤 吉田 秋田 田中
Ｃ
休 太田
ーーーー
A勤務などの人数が日により不定であるため、質問の結果のように、日付で横に流す表はまず不可能です。
Sheet2に出すやり方はできると思うが、VBAを理解して無いと修正できない。略。
結果として「ややこしい」ということだけの読後感に終るかも。
--
残した要改良点
別シートに出す。
日にちで式を変えなくても良いようにする。

お礼

回答ありがとうございます。おっしゃるとおり複数の端末・複数の作成者での利用を考えていたため関数での解決を考えていました。
知識の浅い自分はvlookup関数でなんとかいけないかなと試行錯誤したところです。そこでまたお答えいただければうれしいのですが、vlookup関数で解決しようとする場合、「A」勤務担当は最高でも４名なので、最初の表作成時点で「Aa」「Ab」と入力すればいけると思うのですが、入力規則のリストを利用するにはあまりにたくさんの選択項目になり、かえって非効率なような気がします。一番最初に「A」を選択した人は自動的に『Aa」次は「Ab」となるような方法はあるのでしょうか？

回答No.1

pkh4989

お早うございます。

'勤務入力シートのセル「A1」に月を入力してください。
'セル「A1」 → 11月
'勤務入力シートをActiveにする。シート2「Sheet2」へ展開します。

Sub 勤務表作成()
　　Dim wR　　　　　　　As Integer
　　Dim wC　　　　　　　As Integer
　　Dim wS2　　　　　　 As Worksheet
　　Dim wVal　　　　　　As Variant
　　Dim wIx　　　　　　 As Integer
　　Dim wIy　　　　　　 As Integer
　　Dim EditR　　　　　 As Integer
　　Dim EditC(3)　　　　As Integer
　　'
　　Application.ScreenUpdating = False
　　Set wS2 = Worksheets("Sheet2")　　　　　　　　　　　'←展開シート
　　wS2.Cells.ClearContents
　　'
　　With ActiveSheet　　　　　　　　　　　　　　　　　　'勤務入力シート
　　　　wR = .Range("A" & Rows.Count).End(xlUp).Row　　 '最大行数を求める(勤務者)
　　　　wC = .Range("B1").End(xlToRight).Column　　　　 '最大のカラムを求める(日付:1～31)
　　　　wVal = .Range(.Cells(1, 1), Cells(wR, wC))　　　'勤務入力情報をワークへ設定
　　　　'
　　　　EditR = -4
　　　　For wIx = 2 To wC　　　　　　　　　　　　　　　 '日付分回す(1～31)
　　　　　　EditR = EditR + 5
　　　　　　wS2.Cells(EditR, 1) = .Range("A1") & .Cells(1, wIx) & "日"　　　'日付編集 (セル「A1」に月を入力する)
　　　　　　wS2.Cells(EditR + 1, 1) = "Ａ"
　　　　　　wS2.Cells(EditR + 2, 1) = "Ｂ"
　　　　　　wS2.Cells(EditR + 3, 1) = "休"
　　　　　　EditC(1) = 1: EditC(2) = 1: EditC(3) = 1
　　　　　　'
　　　　　　For wIy = 2 To wR　　　　　　　　　　　　　 '勤務者分回す
　　　　　　　　Select Case wVal(wIy, wIx)
　　　　　　　　　　Case "A", "Ａ"
　　　　　　　　　　　　EditC(1) = EditC(1) + 1
　　　　　　　　　　　　wS2.Cells(EditR + 1, EditC(1)) = .Range("A" & wIy)
　　　　　　　　　　Case "B", "Ｂ"
　　　　　　　　　　　　EditC(2) = EditC(2) + 1
　　　　　　　　　　　　wS2.Cells(EditR + 2, EditC(2)) = .Range("A" & wIy)
　　　　　　　　　　Case "休"
　　　　　　　　　　　　EditC(3) = EditC(3) + 1
　　　　　　　　　　　　wS2.Cells(EditR + 3, EditC(3)) = .Range("A" & wIy)
　　　　　　　　End Select
　　　　　　Next
　　　　Next
　　End With
　　Application.ScreenUpdating = True
End Sub

お礼

早速の回答ありがとうございます。
マクロを利用しての解決は考えていなかったため（他の勤務表作成者でも利用するため）、少々とまどっていますが、早速チャレンジしてみたいと思います。