excel シフト表を元に、ポスト配属表を作成II

質問

会社からいただくシフト表を元にして、
excelのシフト表（印刷用ベースのため表示変更不可）にポスト番号を打ち込むと、
個人作成した日付別のポスト配属表のようなものに
名前が自動挿入されるようにしたいのですが？
※その日のチーム編成を一目で把握したい。

上記、前回の質問にプラス、研修生を表示出来るようにする方法を
改めて、ご教授いただけないでしょうか？

以前、お教えいただいた関数
T4（黄色背景） ： =IFERROR(INDEX($A$4:$B$11,MATCH(T$3,OFFSET($D$4:$D$11,0,MATCH($P4,$E$3:$J$3,0)),0),1),"")
上記関数を「日別チーム編成表」のポストデータセル全てにオートフィルにて充てがうと
「山田」から「山本」までは表示されるのですが、
重なったポストの研修生が表示されません．．．(ToT)

緑色背景セルのように表示される関数式があれば何卒、ご教授ください。

※シフト表・ポスト配属表・・・日付欄には表示される書式は変えていますが
　　　　　　　　　　共に「2016/10/11」のようにそれぞれ入力してあります。

何分、excel初心者で只今関数を学習中。VBA、マクロはさっぱりです。(ToT；)ゝ
説明不足もあればご教授ください。・・・Excel2007 利用

回答No.4

kagakusuki · ベストアンサー

回答No.2です。

　失礼しました。前回の回答ではシフト表の方だけ行が1行ずれてしまっておりました。
　ですから、正しくは以下の様になります。

　まずT4セル(T4:V4の結合セル)に次の関数を入力して下さい。

=IFERROR(INDEX($A$4:$A$8,MATCH(T$3,INDEX($E$4:$K$8,,MATCH($P4,$E$3:$K$3,0)),0)),"")&IFERROR("　"&INDEX($A$10:$A$11,MATCH(T$3,INDEX($E$10:$K$11,,MATCH($P4,$E$3:$K$3,0)),0)),"")

　次に、T4:V4の結合セルをコピーして、W4:AE4のセル範囲に貼り付けて下さい。
　次に、T4:AE4のセル範囲をコピーして、S列～AD列の5行目以下に貼り付けて下さい。

　以上です。

お礼

ありがとうございます。

お陰様で、研修生ポストも表記された新たな表を
皆に開示し、見てもらえるようになりました。
研修生、従業員、共に喜んでくれております。
ありがとうございました。

回答No.3

bunjii

>上記、前回の質問にプラス、研修生を表示出来るようにする方法を改めて、ご教授いただけないでしょうか？
考え方としては正規のメンバーと研修生の割り付けを別の数式で算出して&演算子で文字列を連結すれば良いことになります。
T4セルに次の数式を設定して右と下へコピーすれば目的通りになります。
=INDEX($A$4:$C$8,MATCH(T$3,INDEX($F$4:$K$8,0,MATCH($P4,$F$3:$K$3,0)),0),1)&" "&IFERROR(INDEX($A$10:$C$11,MATCH(T$3,INDEX($F$10:$K$11,0,MATCH($P4,$F$3:$K$3,0)),0),1),"")
または
=INDEX($A:$C,MAX(INDEX(($F$4:$K$8=T$3)*ROW(A$4:A$8),0,MATCH($P4,$F$3:$K$3)),2),1)&" "&INDEX($A:$C,MAX(INDEX(($F$10:$K$11=T$3)*ROW(A$10:A$11),0,MATCH($P4,$F$3:$K$3)),2),1)
但し、A2セルが空欄であることが必須条件です。
添付画像はExcel 2013で検証したものですが後者の数式は他のバージョンでも再現できるはずです。

回答No.2

kagakusuki

確認したいのですが、質問者様が添付された画像の様に、研修生の表は「研修生ではないメンバー」とは別の表(研修生だけが一塊になってる表)になっていて、尚且つ、各シフトには必ず「研修生ではないメンバー」の1人が入る事になっていて、「研修生ではないメンバー」が複数人入る様な事はなく、また研修生がシフトに入る場合には研修生は必ず1人だけが「研修生ではないメンバー」の1人とチームを組まされる事になっていて、同じシフト内に研修生だけが入る様な事や、複数人の研修生が入る様な事はない、と考えれば宜しいのでしょうか?
　もしその様な決まり事になっている場合には、まずT4セル(T4:V4の結合セル)に次の関数を入力して下さい。

=IFERROR(INDEX($A$5:$A$9,MATCH(T$3,INDEX($E$5:$K$9,,MATCH($P4,$E$4:$K$4,0)),0)),"")&IFERROR("　"&INDEX($A$11:$A$12,MATCH(T$3,INDEX($E$11:$K$12,,MATCH($P4,$E$4:$K$4,0)),0)),"")

　次に、T4:V4の結合セルをコピーして、W4:AE4のセル範囲に貼り付けて下さい。
　次に、T4:AE4のセル範囲をコピーして、S列～AD列の5行目以下に貼り付けて下さい。

　以上です。

回答No.1

Prome_Lin

マクロを組みました。

添付した画像と完全に一致した形になっていないと、思った結果は得られません。

セル「A1」に何、セル「B1」に何、と正確な情報が得られましたら、マクロをそれに合わせて変更しますので、おっしゃってください。

まず、目的のエクセルファイルを開きます。

「Alt＋F11」（「Alt」（「オルト」と読みます）キーを押しながら、「F11」を押す）で、「Visual Basic」の画面があらわれます。

メニューの「挿入」から「標準モジュール」を選びます。

すると、画面右側のグレーだったところが、白くなりますので、その白くなった部分に、以下のマクロをコピー＆ペーストして、あとは「F5」で実行するだけです。

Option Explicit
Sub Test()
Dim i, j As Long
For i = 4 To 8
For j = 3 To 8
Select Case Cells(i, j).Value
Case 1
Cells(j + 1, 11).Value = Cells(i, 1).Value
Case 2
Cells(j + 1, 12).Value = Cells(i, 1).Value
Case 3
Cells(j + 1, 13).Value = Cells(i, 1).Value
Case 4
Cells(j + 1, 14).Value = Cells(i, 1).Value
Case Else
End Select
Next j
Next i
End Sub

簡単な説明をしておきますので、もし、質問者の環境にご自身で合わせられるようなら、合わせてください（もちろん、おっしゃって頂ければいくらでも修正いたします）。

Option Explicit

「正確に」とか「厳密に」マクロを書きます！と宣言しているのですが、無くても大丈夫です。

Sub Test()

サブプロシージャ（昔で言うサブルーチン）を宣言しています。

Dim i, j As Long

これからのマクロで使う変数を宣言しています。

For i = 4 To 8

変数「i」の値が、４から８まで、１つずつ変化します（結果、５回繰り返します）。

For j = 3 To 8

変数「j」の値が、３から８まで、１つずつ変化します（結果、「i」が「4」のとき、５回繰り返し、「i」が「5」のとき、５回繰り返し・・・と、繰り返します）。

Select Case Cells(i, j).Value

セル「i, j」の初期値は「Cell(4, 3)（４行目３列目でセル「C3」のこと）」で、「Value」とありますので、そのセルの値を使って（この「4, 3」は、「i, j」になっていますので、それぞれ、For Nextによって、変化します）。

Case 1

上記の値が「１」の場合、以下「２」の場合、「３」の場合、「４」の場合、それ以外の場合（結果「休」）、と続きます。

「１」の場合は、

Cells(j + 1, 11).Value = Cells(i, 1).Value

「i」が「４」のときは、「山田」、「i」が「５」のときは、「田中」・・・となります。

そして、「j + 1」が、左側の表の行、「11」が列（「Ｋ」列）を示しています。

あとは、これの繰り返しです。