プロが教える店舗&オフィスのセキュリティ対策術

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

「エクセルで出勤シフト表中の数字から別シー」の質問画像

質問者からの補足コメント

  • 実際は午後だけの勤務日は出勤時間が違うためsheet1では、PMを2列にして「1」の入力場所を変えてます。(AM、PMのみ、PM)の3列にしてます。sheet2の形式に変更はありません。
    修正した画像を添付しました。
    (1)回答例の数式の一部変更すれば良いでしょうか。それとも全く別の数式になりますか?(2人体制に変更はありません)
    2)回答例では5人分だけの数式とありましたが、仮に人数が増えた場合は、数式のどこを手直すればよいのでしょうか?また名前のところのセルは結合しなければ数式はより簡単になりますか?

    初めてで、補足質問が多くてすみません。

    「エクセルで出勤シフト表中の数字から別シー」の補足画像1
      補足日時:2016/11/28 13:57
  • 補足説明にすぐに応じていただいてありがとうございます。
    回答No.2にありました
    作業列B2セルに
    =IF(A2="",B1,A2)を入れたのち(回答NO.3 の数式も入れてみましたが・・・)

    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))),"")
    を配列数式で入れてみたのですが、名前が全く出てきません。
    上記数式であってますか?
    D2も同様です。
    お手数をおかけしますが、ご回答よろしくお願いします。

    No.2の回答に寄せられた補足コメントです。 補足日時:2016/11/29 12:55
  • ご迷惑をおかけしました。確認いたしましたらご指摘④結合したセル内のAM,PMが全角でした。直したところ、出来ました!!
    教えて頂いた数式の関数を少しは理解したい(希望ですが・・・)と思ってネットで調べてましたら、IFERROR関数はエクセル2007から新設とありました。
    ビスタを使っている人がいるので、2003の場合の数式がお分かりだったらお教えいただけないでしょうか?

    No.4の回答に寄せられた補足コメントです。 補足日時:2016/11/29 15:16
  • 今回お教えいただいたシート1、シート2では、いずれも「A1」からスタートしてますが、表題を付けたりして実際は「A1」のスタートとなりません。
    数式中にある行、列のことがよく理解できていません。
    (1)シート1で行をかえたり、列を変えたりした場合、シート2の数式のどこが違ってくるのでしょうか?
    (シート1の行を挿入(表はA2スタート)してもシート2は従来通り、シート1で列挿入するとシート2は変化)
    (2)シート2で行を変えたり、列を変えたりしたら、数式のどこを変更したらよいのでしょうか?
    (シート1はそのままでシート2で行を挿入したらもう変化)
    (3)(1)、(2)の疑問が理解できたら、シート2のAmとPmの間に列を増やして、出勤時間が違うところに印☆(手入力を予定してます)でも入れようかと(1)(2)が解決出来たら可能ですよね。
    たびたびの質問でお手数をおかけして本当にすみません。

    No.5の回答に寄せられた補足コメントです。 補足日時:2016/11/30 11:27
  • 回答6、たいへんありがとうございま~す。
    回答にあった数式例を参照にしたら幾分応用も可能となりました。*_*
    応用する際、数式中のMATCH($A2,Sheet1!$1:$1,0)-3,,1)=1の変更に少し苦しみましたが・・・

    回答6にあったsheet1(変更したもの)を基にsheet2の変更を自分なりに試みて以下の数式にしましたがどうもうまく出来ません。どこが悪いのでしょうか?

    =IFERROR(INDEX(Sheet1!$C$3:$C$32,SMALL(IF((Sheet1!$D$3:$D$32=$C$2)*(OFFSET(Sheet1!$D$3:$D$32,,MATCH($B2,Sheet1!$2:$2,0)-4,,1)=1),ROW($B$1:$B$30)),COLUMN(B2))),"")

    「エクセルで出勤シフト表中の数字から別シー」の補足画像5
      補足日時:2016/12/01 13:47

A 回答 (7件)

数式を拝見しました。



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

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

数式内の
>COLUMN(B2)
の部分を
>COLUMN(A1)
(もちろんCOLUMN(A2)でも問題ありません)
に変更したらどうなりますか?m(_ _)m
    • good
    • 1
この回答へのお礼

助かりました

出来ました!!
数式の意味がわからないので、sheet2の数式を入力した際の指示がある色枠を原型から類推して試みた次第です。
sheet2ももう少し移動したケースも試みてみます!
これで何とか目安がつきそうです。
拙い質問ににお答えいただき、どの回答もベストアンサーです
ありがとうございました

お礼日時:2016/12/01 15:17

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



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

今回は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
「エクセルで出勤シフト表中の数字から別シー」の回答画像6
    • good
    • 1
この回答へのお礼

何度も詳しく教えて頂きまして本当もありがとうございます。
変更した場合の数式の変更箇所がよくわかりました。

お礼日時:2016/12/01 15:17

こんばんは!



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
この回答への補足あり
    • good
    • 1
この回答へのお礼

何度も詳しく教えて頂きまして本当もありがとうございます。

お礼日時:2016/12/01 15:16

続けてお邪魔します。



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

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

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

こちらで考えられる原因としてはこの程度でしょうかね。m(_ _)m
この回答への補足あり
    • good
    • 1
この回答へのお礼

ご指摘があったとおり、一部が全角でした。修正したらできました。

お礼日時:2016/12/01 15:16

何度もごめんなさい。



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

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

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

何度も詳しく教えて頂きまして本当もありがとうございます。

お礼日時:2016/12/01 15:16

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
「エクセルで出勤シフト表中の数字から別シー」の回答画像2
この回答への補足あり
    • good
    • 1
この回答へのお礼

詳しく書いていただいて、ありがとうございます。

お礼日時:2016/12/01 15:16

こんばんは!



一例です。
↓の画像のような配置になっているとします。
尚、同じ日付の「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
「エクセルで出勤シフト表中の数字から別シー」の回答画像1
    • good
    • 1
この回答へのお礼

tom04さん、ご回答いただきありがとうございます。
エクセル初心者なので今までも月勤務シフト表作成に至るまでも試行錯誤の連続でした。超難関だったのが今回お尋ねした別シートに名前を出すことでした。詳しい説明、シート添付のおかげで超難関が解決できました。もう感謝感激です。

実は今回初めての質問でしたので質問の一部を簡略化してしまいました(後悔してま~す)

実際は午後だけの勤務日は出勤時間が違うためsheet1では、PMを2列にして「1」の入力場所を変えてます。(AM、PM、PMのみ)の3列にしてます。sheet2の形式に変更はありません。

今回の回答例の数式の一部変更すれば良いでしょか。それとも全く別の数式になりますか?(2人体制に変更はありません)
お手数をおかけしたしますが、お教えいただきましたら幸いです。
 追加質問をここに書いて良いのかわからず書きました。

お礼日時:2016/11/28 11:16

お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!

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


人気Q&Aランキング