エクセルでのデータ出力について

質問

エクセル初心者です。どなたかアドバイスをお願いします。
職員の休暇・休職管理をしたいと考えています。

シート1には以下のようなデータが入っています。

職員番号、氏名、所属、事項、開始日、終了日、備考
00001、山田花子、総務課、産後休暇、2009/8/1、2009/10/31
00002、佐々木太郎、営業部、病気休職、2008/3/1、2008/4/30
00002、佐々木太郎、営業部、病気休職、2008/5/1、2008/7/31
00001、山田花子、総務課、育児休業、2009/11/1、2010/7/31
00003、岡本史郎、経理課、病気休暇、2009/6/15、2009/6/30
00004、佐藤次郎、制作部、病気休職、2009/7/10、2009/11/30
00002、佐々木太郎、営業部、病気休職、2008/8/1、2008/9/30
00001、山田花子、総務課、病気休暇、2009/3/15、2009/3/28

※現在、この様式でデータを保管しています。

シート２では以下のようにデータを出力したいと思います。

　職員番号："00001"　←ここを入力すると、以下の項目が出力される
　氏名：山田花子
　所属：総務課
　　事項　　開始日　　　終了日　　　備考
　病気休暇　2009/3/15　2009/3/28
　産後休暇　2009/8/1　2009/10/31　
　育児休業　2009/11/1　2010/7/31

※2つ以上の事項がある場合は、開始日の昇順で自動的にソートされる。

1人につき一回だけの休暇であればVLOOKUPを使って抽出できると思いますが、
上記例の山田花子のように、同じ人物が複数回休暇を取得している場合、
その全てを一覧表示させたいと思います。
また、それを開始日の昇順で自動的ソートさせたいと思います。

また、シート３では以下のようにデータを出力したいと思います。

期間　"2009/4/1"～"2009/8/31"　←ここを入力すると、以下の項目が出力される

職員番号、氏名、所属、事項、開始日、終了日、備考
00001、山田花子、総務課、産後休暇、2009/8/1、2009/10/31
00002、佐々木太郎、営業部、病気休職、2008/3/1、2008/4/30
00002、佐々木太郎、営業部、病気休職、2008/5/1、2008/7/31
00002、佐々木太郎、営業部、病気休職、2008/8/1、2008/9/30
00003、岡本史郎、経理課、病気休暇、2009/6/15、2009/6/30
00004、佐藤次郎、制作部、病気休職、2009/7/10、2009/11/30

入力して指定した期間の中で休職している人を一覧表示させる。
同じ職員は連続して、かつ開始日の昇順でソートさせたいと思います。

この場合の、シート２とシート３での作りこみの仕方について
アドバイスいただけませんでしょうか。

そもそもエクセルではムリとか、シート１のデータ保存形式がおかしいなど、問題があればご指摘ください。

どうぞよろしくお願いします。

回答No.1

tom04 · ベストアンサー

こんばんは！
参考になるかどうか判りませんし、大きく外している可能性もあります。
二つ目のSheet3への質問が表示どおりにならないのですが・・・
結局ある期間～ある期間までの間に休暇（休職）がある人を表示させれば良いわけですよね？
一応そういう事だとしての回答になります。
↓の画像をアップしてみました（小さくて見にくいかもしれません）

Sheet2・Sheet3用の作業用の列を使わせてもらっています。
Sheet2用の作業列H2セルに
=IF(A2=Sheet2!$B$1,RANK(E2,$E$2:$E$100,1),"")
Sheet3用の作業列I2セルに
=IF(A2="","",IF(AND(E2<=Sheet3!$C$2,F2>=Sheet3!$B$2),RANK(A2,$A$2:$E$2:$A$100,1),""))
という数式を入れ、H2・I2セルを範囲指定し、I2セルのフィルハンドルで下へずぃ～～～！っとコピーします。

まず、Sheet2のB2セルを
=IF($B$1="","",VLOOKUP($B$1,Sheet1!$A$2:$C$100,ROW(A2),0))
とし、B3セルまでコピーします。

そして、A5セルに
=IF(COUNT(Sheet1!$H$2:$H$100)<ROW(A1),"",INDEX(Sheet1!D$2:D$100,MATCH(SMALL(Sheet1!$H$2:$H$100,ROW(A1)),Sheet1!$H$2:$H$100,0)))
という数式を入れ、列方向と行方向にコピーします。

続いて、Sheet3のほうですが、
A5セルに
=IF(COUNT(Sheet1!$I$2:$I$100)<ROW(A1),"",INDEX(Sheet1!A$2:A$100,MATCH(SMALL(Sheet1!$I$2:$I$100,ROW(A1)),Sheet1!$I$2:$I$100,0)))
という数式で列方向と行方向にコピーすると画像のような感じになります。
尚、Sheet2・3の日付セルの表示形式は当然「日付」で！
そして、メニュー　→　ツール　→　オプション　→　「表示タブ」の
「ゼロ値」のチェックを外します。

数式はとりあえず100行まで対応できるようにしていますが、
データ量によって範囲指定の領域はアレンジしてください。

Sheet3の表示順は職員番号の昇順にしています（日付の昇順ではありません）ので
同じ職員であれば、上位の行にあるものから表示されます。

以上、当方使用のExcel2003での回答です。
長々と書きましたけど、お役に立つかどうか判りません。
とりあえず投稿してみました。m(__)m

お礼

ありがとうございました！！できました！！！
これでエクセルをいちいち印刷して、手作業せずに管理ができます。
本当に助かりました！非常にうれしいです。

範囲に関しては、結構な数がいますので100だと足りなくなると思います。
その際、全ての数式の、$100というところを、$1000などに変更すればよろしいのでしょうか？

回答No.5

tom04

No.1～3です！

またまた・・・おじゃましました。
何とか希望に近い形になったようで安心しました。

お礼欄に
>範囲に関しては、結構な数がいますので100だと足りなくなると思います。
その際、全ての数式の、$100というところを、$1000などに変更すればよろしいのでしょうか？

とありますが、
そうですねぇ～！
データ量が多いということであれば、500とか1000とか統一していた方が良いかもしれませんね！
（配列数式ではないので、たぶん範囲指定の領域がバラバラでもちゃんと表示できると思いますが・・・）

尚、作業列のオートフィルでのコピーもかなり下までコピーしておいた方が良いと思います。
（データがなくても構いませんので、今後データが増えても良いように、数式の範囲指定した行くらいまでコピーしても問題ありません）

以上、何度もお邪魔してごめんなさいね。m(__)m

お礼

何度もありがとうございます！
一年間でかなりの人数が出ますので、1000に統一させていただきました。

ありがとうございました！

回答No.4

KURUMITO

開始日の昇順で並べるなどの操作が入りますのでシート1では作業列が多くなります。目障りでしたら入力が終了後には列を非表示にしてもよいでしょう。
シート1ではお示しの表がＡ1セルからＧ9セルにあるとします。もちろん下行は多くても問題はありません。
シート1のＨ2セルには次の式を入力します。
=IF(A2="","",A2+E2/1000000)
Ｉ2セルには次の式を入力します。
=IF(H2="","",IF(INT(H2)<>Sheet2!B$1,"",RANK(H2,INDIRECT("H2:H"&COUNT(H:H)+1),1)))
Ｊ2セルには次の式を入力します。
=IF(H2="","",IF(OR(AND(E2>=Sheet3!B$1,E2<=Sheet3!C$1),AND(F2>=Sheet3!B$1,F2<=Sheet3!C$1),AND(E2<=Sheet3!B$1,F2>Sheet3!C$1)),RANK(H2,INDIRECT("H2:H"&COUNT(H:H)+1),1),""))
Ｋ2セルには次の式を入力します。
=IF(J2="","",RANK(J2,INDIRECT("J2:J"&COUNT(H:H)+1),1))
Ｈ2セルからＫ2セルまでを選択して下方にオートフィルドラッグします。
シート2では次のようにします。
Ａ1セルに職員番号と文字を入力し、Ｂ1セルにお求めの職員番号を入力します。
Ａ2セルには氏名と入力し、Ｂ2セルには次の式を入力します。
=IF(B1="","",VLOOKUP(B1,Sheet1!A:B,2,FALSE))
Ａ3セルには所属と入力し、Ｂ3セルには次の式を入力します。
=IF(B1="","",VLOOKUP(B1,Sheet1!A:C,3,FALSE))
Ａ4セルには事項、Ｂ4セルには開始日、Ｃ4セルには終了日、Ｄ4セルには備考と入力します。
Ａ5セルには次の式を入力したのちにＤ5セルまでオートフィルドラッグしたのちに下方にもオートフィルドラッグします。
=IF(COUNTIF(Sheet1!$I:$I,SMALL(Sheet1!$I:$I,ROW(A1)))=0,"",INDEX(Sheet1!$A:$G,MATCH(SMALL(Sheet1!$I:$I,ROW(A1)),Sheet1!$I:$I,0),COLUMN(D1)))
Ｂ5セルおよびＣ5セルから下の行についてはセルの表示形式を日付にします。
また、Ｄ5セルから下の行についてはセルの表示形式をユーザー定義で#と入力して0が表示されないようにします。
シート3ではＡ1セルに期間と入力してＢ1セルには開始の日付をＣ1セルには終了の日付を入力します。
Ａ2セルからＧ2セルまでは職員番号、氏名などの項目名を入力します。
Ａ3セルには次の式を入力したのちにＧ3セルまでオートフィルドラッグしたのちに下方にもオートフィルドラッグします。
=IF(COUNTIF(Sheet1!$K:$K,SMALL(Sheet1!$K:$K,ROW(A1)))=0,"",INDEX(Sheet1!$A:$G,MATCH(SMALL(Sheet1!$K:$K,ROW(A1)),Sheet1!$K:$K,0),COLUMN(A1)))
開始日や終了日および備考などについての表示形式はシート2と同様に設定します。

お礼

こちらの方法でもできました！！ありがとうございます。
本当に助かりました！！

教えていただいた数式を紐解いて、勉強したいと思います。

回答No.3

tom04

No.1・2です！
ほんとぉ～～～！に何度もごめんなさいね！

もう一度訂正です。
Sheet1の作業列I2セルは最終的に

=IF(A2="","",IF(AND(Sheet3!$B$2<=F2,Sheet3!$C$2>=E2),A2*100+RANK(E2,$E$2:$E$100,1),""))
にしてください。
検証せずに何度も投稿してごめんなさい。

親の仇のように「これでもかっ！」というくらい顔を出してしまいました。
失礼しました。m(__)m

回答No.2

tom04

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

前回の回答の中で、Sheet3用の作業列の数式が少しまずかったです！

Sheet1作業列、I2セルの数式を
=IF(A2="","",IF(AND(Sheet3!$B$2<=F2,Sheet3!$C$2>=E2),ROW(A1)*100+RANK(E2,$E$2:$E$100,1),""))
に訂正してみてください。

前回の数式では同職員の場合正確に表示されないと思います。
どうも何度も失礼しました。m(__)m