エクセルで出勤シフト表中の数字から別シートで名前入りの出勤表を作成したい

Question

エクセル初心者です。
勤務は午前、午後、いずれも2名体制ですが、各自の都合があるので毎月出勤シフト表を組んでいます。午前と午後で勤務時間も違い、時折午前休みもあります。月の出勤日数（午前、午後の回数）や勤務時間の合計を出しやすいことからシート１のように、出勤する人には「１」を入力した月別勤務表を作成。ここからが本題です。
シート１で出勤することになった人(「１」を入力した人）を別シート2で名前を出したいです。シート２でプルダウンメニューを使えば名前が入ることは知っていますが、シート１の「１」の入力からシート２の名前を導き出す数式はありますか？

tom04 · Accepted Answer

数式を拝見しました。

おそらく最後のCOLUMN関数でB列を指定しているためだと思います。
このCOLUMN関数はSMALL関数「順位」（何番目か？）を求めている関数なので
B列を指定してしまうと「2番目」から表示されます。
（B1、B100、B1000でも列番号ですのですべて「2」となります）

表示したいのは配列数式が「TRUE」の行の小さい順から1番目と2番目ですので、
かならずA列から指定してやる必要があります。
お示しの数式ではおそらく2番目・3番目が表示されるのでは？

数式内の
＞COLUMN(B2)
の部分を
＞COLUMN(A1)
（もちろんCOLUMN(A2)でも問題ありません）
に変更したらどうなりますか？m(_ _)m

tom04 · Answer

こんばんは！

一例です。
↓の画像のような配置になっているとします。
尚、同じ日付の「AM」と「PM」は3人以上に「1」が入力されない！という前提です。
（1日あたり「AM」・「PM」とも最大二人まで）

画像ではSheet2のB2セルに
=IFERROR(INDEX(Sheet1!$A$2:$A$11,SMALL(IF((Sheet1!$B$2:$B$10=$B$1)*(OFFSET(Sheet1!$B$2:$B$10,,MATCH($A2,Sheet1!$1:$1,0)-2,,1))=1,ROW($A$1:$A$9)),COLUMN(A1))),"")

配列数式になりますので、Ctrl＋Shift＋Enterで確定！
この画面からコピー＆ペーストする場合は
上記数式をドラッグ＆コピー　→　B2セルを選択　→　数式バー内に貼り付け　→　そのまま（編集可能なまま）
Ctrl＋Shiftキーを押しながらEnterキーで確定！
B2セルのフィルハンドルで隣りのC2セルまでコピー！

次にD2セルに
=IFERROR(INDEX(Sheet1!$A$2:$A$11,SMALL(IF((Sheet1!$B$3:$B$11=$D$1)*(OFFSET(Sheet1!$B$3:$B$11,,MATCH($A2,Sheet1!$1:$1,0)-2,,1))=1,ROW($A$1:$A$9)),COLUMN(A1))),"")

これも配列数式ですので、Ctrl＋Shift＋Enterで確定！
D2セルのフィルハンドルで隣りのE2セルまでコピー！
最後にB2～E2セルを範囲指定　→　E2セルのフィルハンドルで下へコピーすると
画像のような感じになります。

※　質問文ではSheet1のA列が結合され、人数は5人になっていますので、
5人分だけの数式になります。

※　B2セルの数式とD2セルの数式が微妙に異なります。
IF関数等の範囲が1行ずれているコトに気を付けてください。

こんな感じではどうでしょうか？m(_ _)m

tom04 · Answer

No.1です。

補足を拝見しました。
セル結合してあると何かと厄介です。
特に、今回は一人当たり3行の結合で、「PM」と「PM」のみの区別が必要になってしまいます。

（1）について・・・
はい、全く異なった数式になってしまいます。

（2）について・・・
人数が増えた場合といってもやたらに広い範囲を指定してももっと厄介になりますので
今回は10人まで対応できるようにしてみました。

一案ですが、↓の画像のように作業用の列を設けてみてはどうでしょうか？
今回はB列を挿入し、作業用の列としています。すなわち元のB列以降が右へ1列ずつずれるコトになります。
作業列B2セルに
=IF(A2="",B1,A2)
という数式を入れ10人分のB31セルまでフィル＆コピーしておきます。
このときA列にデータがなくても最終の人が表示されますが、表に「1」が入ることはないと思いますので、無視してください。

そしてSheet2のB2セルに
=IFERROR(INDEX(Sheet1!$B$2:$B$31,SMALL(IF((Sheet1!$C$2:$C$31=$B$1)*(OFFSET(Sheet1!$C$2:$C$31,,MATCH($A2,Sheet1!$1:$1,0)-3,,1)=1),ROW($A$1:$A$30)),COLUMN(A1))),"")

前回同様配列数式ですので、Ctrl＋Shift＋Enterで確定！　→　隣りのC2セルまでフィル＆コピー！

D2セルに
=IFERROR(INDEX(Sheet1!$B$2:$B$31,SMALL(IF((LEFT(Sheet1!$C$2:$C$31,2)=$D$1)*(OFFSET(Sheet1!$C$2:$C$31,,MATCH($A2,Sheet1!$1:$1,0)-3,,1)=1),ROW($A$1:$A$30)),COLUMN(A1))),"")

としCtrl＋Shift＋Enter　→　隣りのE2セルまでフィル＆コピー！
最後にB2～E2セルを範囲指定　→　E2セルのフィルハンドルで下へコピー！
これで画像のような感じになります。

※　作業列が目障りであれば非表示にしておいてください。

※　余計なお世話かもしれませんが、「AM」・「PM」とも最大二人というコトですが
間違ってSheet1の同一日に3個以上の「1」を入力した場合注意を促す意味で
セルの塗りつぶしの設定をしてみてはどうでしょうか？

Sheet1のD2セル以降「1」が入る可能性があるセルを範囲指定　→　条件付き書式　→　新しいルール　→　数式を使用して・・・　→　数式欄に
=IF(AND($C2="AM",D2=1),COUNTIFS($C$2:$C$31,"AM",D$2:D$31,1)>2)
という数式を入れ　→　書式　→　塗りつぶしから「赤」（←好みの色で構いません）を選択しOK

同じセル範囲に二つ目の条件付き書式を設定します。
・・・中略・・・　→　数式欄に
=IF(AND(LEFT($C2,2)="PM",D2=1),SUMPRODUCT((LEFT($C$2:$C$31,2)="PM")*(D$2:D$31=1))>2)
という数式を入れ　→　書式　→　塗りつぶしから「赤」（←好みの色）を選択しOK

これで1列内に「AM」に3個以上の「1」が入力されれば「AM」の「1」すべてのセルが塗りつぶされ
同じく「PM」または「PMのみ」の行のセルに「1」が3個以上入力されれば
そのセルが塗りつぶされます。

こんな感じではどうでしょうか？m(_ _)m

tom04 · Answer

何度もごめんなさい。

大勢に影響はないと思いますが
No.2の作業列、B2セルの数式を↓の数式に変更してください。
=OFFSET(A2,-MOD(ROW(A3),3),,1)&""

これでA列にデータがない場合はなにも表示されないはずです。

※　間違ってA列にデータがない行に「1」を入力してしまうと
前回の数式では最後の人が表示されます。m(_ _)m

tom04 · Answer

続けてお邪魔します。

ん～～～
前回アップした配置の表で今こちらでこの画面上から数式をコピー＆ペーストして確認してみるとちゃんと表示されました。

今一度確認してみてください。
①　お手元のSheetは↓の画像と同じ配置になっているか？
②　両シートの日付セルにはちゃんと同じデータが入っているか？
③　Sheet1のC列は「AM」「PMのみ」「PM」の半角アルファベットになっているか？
④　Sheet2のB1・D1(結合セル）もちゃんと半角アルファベットになっているか？

全くなにも表示されないという原因としては
参照セルが別物と判断されている可能性があります。

こちらで考えられる原因としてはこの程度でしょうかね。m(_ _)m

tom04 · Answer

こんばんは！

Excel2003でも対応できる数式ですか！
かなり長い数式になってしまいますが・・・

Sheetの配置は今までアップした画像通りとします。
Sheet2のB2セルに
=IF(SUMPRODUCT((Sheet1!$C$2:$C$31=$B$1)*(OFFSET(Sheet1!$C$2:$C$31,,MATCH($A2,Sheet1!$1:$1,0)-3,,1)=1))<COLUMN(A1),"",INDEX(Sheet1!$B$2:$B$31,SMALL(IF((Sheet1!$C$2:$C$31=$B$1)*(OFFSET(Sheet1!$C$2:$C$31,,MATCH($A2,Sheet1!$1:$1,0)-3,,1)=1),ROW($A$1:$A$30)),COLUMN(A1))))

D2セルに
=IF(SUMPRODUCT((LEFT(Sheet1!$C$2:$C$31,2)="PM")*(OFFSET(Sheet1!$C$2:$C$31,,MATCH($A2,Sheet1!$1:$1,0)-3,,1)=1))<COLUMN(A1),"",INDEX(Sheet1!$B$2:$B$31,SMALL(IF((LEFT(Sheet1!$C$2:$C$31,2)=$D$1)*(OFFSET(Sheet1!$C$2:$C$31,,MATCH($A2,Sheet1!$1:$1,0)-3,,1)=1),ROW($A$1:$A$30)),COLUMN(A1))))

どちらも配列数式ですので、Ctrl＋Shift＋Enterで確定してください。

こんな感じではどうでしょうか？m(_ _)m

tom04 · Answer

だんだん難しくなっていくような気がします。

個人的にはあまり長い数式は好みではありません。
実際に自分が投稿した数式を変更する場合どこをどう修正していいのか判らなくなってしまい、
結局最初から数式を考えた方が間違いが少ない！ってコトの方が多いです。

今回はSheet1が↓のような配置になった場合の数式を載せてみます。
Excel2003に対応させようとすると長い数式になりますので、Excel2007以降に対応できる数式とします。
（今回も10人分としています。）

まずSheet1の作業列C3セルに
=OFFSET(B3,-MOD(ROW(A3),3),,1)&""
という数式を入れフィルハンドルで下へコピーしておきます。

Sheet2のは配置はそのままだとすると
Sheet2のB2セル（配列数式）は
=IFERROR(INDEX(Sheet1!$C$3:$C$32,SMALL(IF((Sheet1!$D$3:$D$32=$B$1)*(OFFSET(Sheet1!$D$3:$D$32,,MATCH($A2,Sheet1!$2:$2,0)-4,,1)=1),ROW($A$1:$A$30)),COLUMN(A1))),"")

D2セル（配列数式）は
=IFERROR(INDEX(Sheet1!$C$3:$C$32,SMALL(IF((LEFT(Sheet1!$D$3:$D$32,2)=$D$1)*(OFFSET(Sheet1!$D$3:$D$32,,MATCH($A2,Sheet1!$2:$2,0)-4,,1)=1),ROW($A$1:$A$30)),COLUMN(A1))),"")

といった感じになります。

今までの数式との違いは判るでしょうか？

試しにSheet1の「1日」の列の「1」の人を列方向に列挙する数式を載せておきます。
使っていないSheet3のどこのセルでもいいので

=INDEX(Sheet1!$C$3:$C$32,SMALL(IF(Sheet1!$E$3:$E$32=1,ROW($A$1:$A$30)),COLUMN(A1)))
（配列数式です）として、フィルハンドルで右へコピーしてみてください。
Sheet1の「1日」の列に「1」が入力されている人が羅列されそれ以降はエラーになると思います。
基本はこの配列数式になっています。
INDEX関数の「配列」の部分の範囲指定は問題ないと思います。
「行番号」の部分が配列数式になり、「1」が入力されている行の小さい順に表示します。
「列番号」はOFFSET関数関数とMATCH関数の併用で列を決めています。

最終的にはエラー処理とINDEX関数の行・列取得のための枝葉がついて
あのような長い数式になっています。

※　本来であれば別シートにでも作業用の列を何列でも作成し自身が目的のセルを抽出しやすいような表にした方が良いと思います。
もちろん作成方法は色々あると思いますが、作り方によっては配列数式にしなくても
単純な関数だけで表示させることも可能だと思います。
そうすればメンテナンス面からみてもそちらをおススメします。m(_ _)m

エクセルで出勤シフト表中の数字から別シートで名前入りの出勤表を作成したい

こんばんは！

No.1です。

何度もごめんなさい。

続けてお邪魔します。

こんばんは！

だんだん難しくなっていくような気がします。

数式を拝見しました。

似たような質問が見つかりました

このQ&Aを見た人はこんなQ&Aも見ています

関連するカテゴリからQ&Aを探す

このQ&Aを見た人がよく見るQ&A

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング