勤務シフト表から、当日の出番を抽出するには？

質問

今回、自分ではどうしてもわからなく、ここで相談させてもらいました。

勤務表の書かれたエクセルファイルから、日ごとの勤務者を出したいと考えています。
勤務が新しく出るごとに毎回作り変えていたのでは、時間がかかってしまい大変です。

関数かマクロとかで、うまく抽出できる方法をご教授お願いします。

(状況)
勤務表.xlsと、当日勤務者.xlsのファイルは別ファイル。
勤務表.xlsは毎月更新され、今は当日勤務者.xlsを月に一度作り変えている。
マクロは完全な初心者でほとんどわからない。
勤務者は40人ほどの小さな会社。

回答No.5

tom04

No.3・4です！
何度もごめんなさい。

画像をよく見ると「勤務形態」が「休」などSheet1の項目にない場合もあるようですね！
そうなると前回のコードではエラーが発生してマクロが止まってしまいます。

エラー回避のために、どこでもよいので
On Error Resume Next
の1行を挿入しておいてください。
敢えて言えば

Range(ws1.Cells(5, 3), ws1.Cells(35, j)).ClearContents
For j = 4 To 34

の2行の間に入れて

Range(ws1.Cells(5, 3), ws1.Cells(35, j)).ClearContents
On Error Resume Next
For j = 4 To 34

といった感じがよいと思います。
これでSheet1の項目にない場合は空白となります。

親の仇のように「これでもかっ！」というくらい顔を出してごめんなさい。m(_ _)m

回答No.4

tom04

No.3です！
たびたびごめんなさい。

前回のコードはSheet1のデータをSheet2に表示させるコードでした！
アップされている画像の矢印をみると、逆の表示がご希望なのですね？

今一度コードを載せてみますので、変更してみてください。
前回アップした画像の配置通りとしています。
操作方法は前回と一緒です。

※　今回もSheet2のA列は5行ずつ結合してあるという前提です。

Sub Sheet2からSheet1へ() 'この行から
Dim i As Long, j As Long, k As Long, M As Long
Dim ws1 As Worksheet, ws2 As Worksheet
Set ws1 = Worksheets("Sheet1")
Set ws2 = Worksheets("Sheet2")
j = ws1.Cells(4, Columns.Count).End(xlToLeft).Column
Range(ws1.Cells(5, 3), ws1.Cells(35, j)).ClearContents
For j = 4 To 34
For i = 9 To ws2.Cells(Rows.Count, 1).End(xlUp).Row Step 5
If ws2.Cells(i, j) <> "" Then
k = WorksheetFunction.Match(ws2.Cells(6, j), ws1.Columns(1), False)
M = WorksheetFunction.Match(ws2.Cells(i, j), ws1.Rows(4), False)
ws1.Cells(k, M) = ws2.Cells(i, 1)
End If
Next i
Next j
End Sub 'この行まで

画像をよくよくみると、Sheet2（元データ）のA列氏名は5行の真ん中にあるような気もします。
（セルを結合しているのではなく、担当場所と氏名が2行ずれているようにも見える）
この際ですのでその場合のコードも一緒に載せておきます。

Sub Sheet2からSheet1へ() 'この行から
Dim i As Long, j As Long, k As Long, M As Long
Dim ws1 As Worksheet, ws2 As Worksheet
Set ws1 = Worksheets("Sheet1")
Set ws2 = Worksheets("Sheet2")
j = ws1.Cells(4, Columns.Count).End(xlToLeft).Column
Range(ws1.Cells(5, 3), ws1.Cells(35, j)).ClearContents
For j = 4 To 34
For i = 11 To ws2.Cells(Rows.Count, 1).End(xlUp).Row Step 5 '9→11に変更
If ws2.Cells(i - 2, j) <> "" Then 'i　→　i-2　に変更
k = WorksheetFunction.Match(ws2.Cells(6, j), ws1.Columns(1), False)
M = WorksheetFunction.Match(ws2.Cells(i - 2, j), ws1.Rows(4), False) 'i→i-2に変更
ws1.Cells(k, M) = ws2.Cells(i, 1)
End If
Next i
Next j
End Sub 'この行まで

ご希望通りになればよいのですが・・・m(_ _)m

回答No.3

tom04

こんばんは！
＞勤務表.xlsと、当日勤務者.xlsのファイルは別ファイル
とありますが、同じBookで別Sheetとしての一例です。

VBAになってしまいますが、その前に一手間かけます。

↓の画像のような配置で左側がSheet1・右側をSheet2としています。
Sheet2のA列は5行ずつ結合してあるというコトだとして・・・

余計なお世話かもしれませんが、両Sheetとも日付・曜日のセルはシリアル値にします。
Sheet1のA1セルに「西暦年」・C1セルに「月」の数値を入れるとします。

Sheet1のA5セル（セルの表示形式はユーザー定義から　d　とだけしておきます）に
=IF(MONTH(DATE($A$1,$C$1,ROW(A1)))=$C$1,DATE($A$1,$C$1,ROW(A1)),"")

B5セル（セルの表示形式はユーザー定義から　aaa　としておく）に
=IF(A5="","",A5)

という数式を入れA5・B5セルを範囲指定　→　B5セルのフィルハンドルで31日までの
35行目までオートフィルでコピーしておきます。

次にSheet2のD6セルに
=INDEX(Sheet1!$A$5:$B$35,COLUMN(A1),ROW(A1))
という数式を入れ下のD7セルまでとりあえずコピー！
D6セルの表示形式はユーザー定義から　d
D7セルの表示形式はユーザー定義から　aaa
としておき、D6・D7セルを範囲指定　→　D7セルのフィルハンドルで31日の
AH列までオートフィルでコピー！

これでSheet1のA1・C1セルに数値を入力すれば自動で日付・曜日が表示されます。
（もちろん大の月・小の月にも対応しています）

以上の下準備ができたうえで・・・

Alt＋F11キー　→　メニュー　→　挿入　→　標準モジュール　→　VBE画面に
↓のコードをコピー＆ペーストしてマクロを実行してみてください。
（Alt＋F8キー　→　マクロ　→　マクロ実行です）

Sub 表示() 'この行から
Dim i As Long, j As Long, k As Long, M As Long, ws1 As Worksheet, ws2 As Worksheet
Set ws1 = Worksheets("Sheet1") '←「Sheet1」の部分は実際のシート名に！
Set ws2 = Worksheets("Sheet2") '←「Sheet2」も実際のシート名に！
For i = 9 To ws2.Cells(Rows.Count, 1).End(xlUp).Row Step 5
Range(ws2.Cells(i, "D"), ws2.Cells(i, "AH")).ClearContents
Next i
On Error Resume Next
For i = 5 To 35
For j = 3 To ws1.Cells(4, Columns.Count).End(xlToLeft).Column
If ws1.Cells(i, j) <> "" Then
k = WorksheetFunction.Match(ws1.Cells(i, j), ws2.Columns(1), False)
M = WorksheetFunction.Match(ws1.Cells(i, 1), ws2.Rows(6), False)
ws2.Cells(k, M) = ws1.Cells(4, j)
End If
Next j
Next i
End Sub 'この行まで

※　月が変わるたびにマクロを実行してもよいのは当然ですが、
Sheet1のデータ変更があるたびにマクロを実行しても構いません。

参考になりますかね？m(_ _)m

回答No.2

tsubuyuki

これは難しいですね。

例えば、転記先の１日の行。
同じ名前が２度発生してますね。
この条件付けが不明瞭です。

転記先の勤務形態・・でしょうか。
転記元に無い「レジ＊」の扱いはどうなっているのか。

転記元、休日出勤・変更・調整欄はどう使うのか。

ぱっと見ただけでこれだけの情報が足りません。
（情報が足りたとしても、それなりに難しい処理だと思いますが）

・・というようなマクロが必要ですが、
> マクロは完全な初心者でほとんどわからない。
状況で、後に人が増えた減ったのメンテナンスはどうされますか？
その辺りも考慮するとなると、ますます凝った仕様が必要です。

そんなこんなで、表の構成から再考するのが先決と思いますよ。
お力になりたいのは山々ですが、それが本音です。

回答No.1

K Kazz（@JazzCorp）

元ネタを１人／シートにした方が仕事はハヤイのでは？？？