エクセルで勤務表から出勤者を別シートに抽出したい。

質問

下記の「勤務表シート」の「弁当コーナー」、「飲料・デザートコーナー」の出勤者の名前を
「分担表シート」に日付ごとに抽出し、後は手入力でその日の分担作業名を入れて、作業指示書
を作りたいと思います。
実際には、その他にも３つのコーナーがあり、４０人程のパートさんを管理しております。
抽出する際に休んでいる人を空欄で残したいと思います。
また、日付ごとの分担表に月日も反映したいと思っています。
更に、勤務表に従って、１ヶ月分の日付の分担表のシート（２８～３１シート）を作成しておきたいと思っています。
今までは、勤務表に従って、手入力で名前を入れていましたが、入力ミスや休日変更などその都度、分担表も訂正し、手間がかかる上にミスも多く、困っています。
尚、マクロは使ったことが有りません。エクセルは２０１０です。
どうか、お手数ですが、ご教授お願い致します。

回答No.5

MackyNo1 · ベストアンサー

参考までに補足説明します。

＞休日マークは、○＝変更可能の休日　◎＝変更不可の休日　有＝有給休暇の３種類有ります。
そこで、この３種類のマーク以外の人を全て分担表に名前を出すことも可能なのでしょうか？

私の数式がうまく表示できているなら、この３種類のマークのいずれかの場合は空白表示という条件にすればよいので、配列定数を使った以下のような数式にします。

=IF(OR(VLOOKUP($A$1,勤務表!$A:$Z,ROW(A2),0)={"◎","○","有"}),"",INDEX(勤務表!$3:$3,ROW(A2)))

お礼

お忙しいところ、丁寧にお答えいただきまして、ありがとうございました。
お陰様で、完璧に思った通りのものが作成できました。

本当にありがとうございました。

回答No.4

MackyNo1

>B3セルにご指示いただきました式を入力しますと「#N/A」となり。成立しません。

私の提示した数式は元データがSheet1にある場合の数式ですが、元データが勤務表というシートなら以下の式をコピー貼り付けしたのでしょうか？

=IF(VLOOKUP($A$1,勤務表!$A:$Z,ROW(A2),0)="○","",INDEX(勤務表!$3:$3,ROW(A2)))

ちなみに、上記の数式は同じ数式ですべてのコーナーに対応できるようにした数式ですので（ブロックごとに数式を変更する必要がない）、空白行（0と表示される）を詰めたい場合は、No2で補足したように、対象セル範囲の周囲をドラッグして数式を上書き「移動」する操作で対応してください。

＞出勤する人の中には「８：００」までなどの条件で出勤する場合もたまにあり、勤務表のセルに「8」と書き込みたいのです。その時は手入力で分担表に、名前を入れればいいのですが、何か方法は無いでしょうか。

どの列のセルに「8」と表示するのでしょうか？（備考欄の列を作って表示することをお勧めします）
また、名前を表示するセルは固定されているので、わざわざ関数で取得するのではなく、条件付き書式などを使って休みの人はフォント色を白にするなどして見えないようにする方が実用的な運用のような気もします。

＞休日マークは、○＝変更可能の休日　◎＝変更不可の休日　有＝有給休暇の３種類有ります。
そこで、この３種類のマーク以外の人を全て分担表に名前を出すことも可能なのでしょうか？

もちろん可能ですが、私の提示した数式でうまく表示できない場合は、回答しても無駄になりますので、もう一度これまでの回答をよく読んで、うまく表示できるかどうか調べてみてください。

回答No.3

KURUMITO

それぞれの分担表は日にちごとにシートが有るのでしょうから、それらの分担表のシート見出しをCtrlキーを押しながらクリックすることで同じ作業グループを作ることができますね。
そこで画面上の分担表シートにお示しの表の項目などを並べます。
その後にA1セルには例えば６月２１日と入力します。
B３セルには次の式を入力してB６セルまで下方にドラッグコピーします。

=IF(OR(A$1="",COUNTIF(勤務表!A:A,A$1)=0),"",IF(INDEX(勤務表!B:E,MATCH(A$1,勤務表!A:A,0),ROW(A1))="○","",IF(INDEX(勤務表!B:E,MATCH(A$1,勤務表!A:A,0),ROW(A1))="",INDEX(勤務表!B$3:E$3,ROW(A1)),"")))

B７セルには次の式を入力してB１２セルまで下方にドラッグコピーします。

=IF(OR(A$1="",COUNTIF(勤務表!A:A,A$1)=0),"",IF(INDEX(勤務表!G:L,MATCH(A$1,勤務表!A:A,0),ROW(A1))="○","",IF(INDEX(勤務表!G:L,MATCH(A$1,勤務表!A:A,0),ROW(A1))="",INDEX(勤務表!G$3:L$3,ROW(A1)),"")))

データが表示されますがその後にA１セルの日付を空にします。
データの表示がなくなったところで勤務表を選択することで作業グループを解除します。
その後に分担表のA１セルに日付を入力すればその日の分担をそれぞれの分担表に表示させることができます。

お礼

丁寧な回答ありがとうございます。
早速、ご教授どおりやってみて、見事完成しました。
後、補足での質問についてもお教えいただけましたら、助かります。
よろしく、お願い致します。

補足

早速のご回答有難う御座いました。
お陰様で、完璧にできました。
更に、お教え頂きたいのですが、お教えいただいた式では、ブランクの人の名前だけが分担表に出て来ます。これで、十分なのですが、出勤する人の中には「８：００」までなどの条件で出勤する場合もたまにあり、勤務表のセルに「8」と書き込みたいのです。
その時は手入力で分担表に、名前を入れればいいのですが、何か方法は無いでしょうか。
実の所、休日マークは、○＝変更可能の休日　◎＝変更不可の休日　有＝有給休暇の３種類有ります。
そこで、この３種類のマーク以外の人を全て分担表に名前を出すことも可能なのでしょうか？

お忙しいところ、恐れいえいますが、教えいただけないでしょうか。

回答No.2

MackyNo1

補足と訂正です。

勤務表シートの項目名をコピーし、分担表のA3セルに貼り付ける操作ですが、右クリックして「形式を選択して貼り付け」で「行列を入れ替える」にチェックを入れ「OK」してください。

上記の操作でコーナー名をコピーするのではなく、担当表シートのコーナー間の空白行を詰めたシートが用意されていて、それをそのまま利用したいときは、B3セルにひとまず以下の式を入力し「0」と表示されている空白行の部分を詰める操作をします。

=IF(VLOOKUP($A$1,Sheet1!$A:$Z,ROW(A2),0)="○","",INDEX(Sheet1!$3:$3,ROW(A2)))

すなわち空白行の下のコーナーのB列の数式ブロックを選択してその周囲にカーソルを置いて1つ上方向にドラッグアンドドロップしてください、同様にその下のコーナーの数式部分も上にドラッグアンドドロップして位置を調整してください。

なお、提示した数式は担当者の最終行がZ列まで入力されている場合に対応するようにしてありますが、実際のデータに合わせて適宜列範囲を拡大してください。

回答No.1

MackyNo1

分担表のA1セルに日付が入力してあるなら、以下のような手順で作業グループで分担表を作成するのが効率的な操作だと思います(数式を簡略化するためにコーナー間に空白行が1つ入ります）。

まず、勤務表シートのB2セルから右側に1行項目名をコピーし、分担表の一番最初のシートを選択し、Shiftキーを押しながら最後のシートを選択してすべての分担表を作業グループにしてA3セルに貼り付けます。
次にB3セルに以下の式を入力し下方向にオートフィルコピーします。

=IF(VLOOKUP($A$1,Sheet1!$A:$Z,ROW(A2),0)="○","",INDEX(Sheet1!$3:$3,ROW(A2)))&""

最後に作業グループを解除すれば完成です。

お礼

早々のご回答有難う御座いました。
試してみましたが、私の知識不足で、うまく行きませんでした。
補足での質問にも、お答えいただけましたら、助かります。
よろしく、お願い致します。

補足

早速のご回答ありがとうございます。
あまり、詳しくないので何か勘違いしていると思うのですが、B3セルにご指示いただきました式を入力しますと「#N/A」となり。成立しません。
何が間違っているのか、見当が付きません。
初歩過ぎて恐れ入りますが、再度、ご指示いただけないでしょうか。