エクセル初心者です。どなたかアドバイスをお願いします。
職員の休暇・休職管理をしたいと考えています。
シート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
※現在、この様式でデータを保管しています。
シート2では以下のようにデータを出力したいと思います。
職員番号:"00001" ←ここを入力すると、以下の項目が出力される
氏名:山田花子
所属:総務課
事項 開始日 終了日 備考
病気休暇 2009/3/15 2009/3/28
産後休暇 2009/8/1 2009/10/31
育児休業 2009/11/1 2010/7/31
※2つ以上の事項がある場合は、開始日の昇順で自動的にソートされる。
1人につき一回だけの休暇であればVLOOKUPを使って抽出できると思いますが、
上記例の山田花子のように、同じ人物が複数回休暇を取得している場合、
その全てを一覧表示させたいと思います。
また、それを開始日の昇順で自動的ソートさせたいと思います。
また、シート3では以下のようにデータを出力したいと思います。
期間 "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
入力して指定した期間の中で休職している人を一覧表示させる。
同じ職員は連続して、かつ開始日の昇順でソートさせたいと思います。
この場合の、シート2とシート3での作りこみの仕方について
アドバイスいただけませんでしょうか。
そもそもエクセルではムリとか、シート1のデータ保存形式がおかしいなど、問題があればご指摘ください。
どうぞよろしくお願いします。
No.5
- 回答日時:
No.1~3です!
またまた・・・おじゃましました。
何とか希望に近い形になったようで安心しました。
お礼欄に
>範囲に関しては、結構な数がいますので100だと足りなくなると思います。
その際、全ての数式の、$100というところを、$1000などに変更すればよろしいのでしょうか?
とありますが、
そうですねぇ~!
データ量が多いということであれば、500とか1000とか統一していた方が良いかもしれませんね!
(配列数式ではないので、たぶん範囲指定の領域がバラバラでもちゃんと表示できると思いますが・・・)
尚、作業列のオートフィルでのコピーもかなり下までコピーしておいた方が良いと思います。
(データがなくても構いませんので、今後データが増えても良いように、数式の範囲指定した行くらいまでコピーしても問題ありません)
以上、何度もお邪魔してごめんなさいね。m(__)m
No.4
- 回答日時:
開始日の昇順で並べるなどの操作が入りますのでシート1では作業列が多くなります。
目障りでしたら入力が終了後には列を非表示にしてもよいでしょう。シート1ではお示しの表がA1セルからG9セルにあるとします。もちろん下行は多くても問題はありません。
シート1のH2セルには次の式を入力します。
=IF(A2="","",A2+E2/1000000)
I2セルには次の式を入力します。
=IF(H2="","",IF(INT(H2)<>Sheet2!B$1,"",RANK(H2,INDIRECT("H2:H"&COUNT(H:H)+1),1)))
J2セルには次の式を入力します。
=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),""))
K2セルには次の式を入力します。
=IF(J2="","",RANK(J2,INDIRECT("J2:J"&COUNT(H:H)+1),1))
H2セルからK2セルまでを選択して下方にオートフィルドラッグします。
シート2では次のようにします。
A1セルに職員番号と文字を入力し、B1セルにお求めの職員番号を入力します。
A2セルには氏名と入力し、B2セルには次の式を入力します。
=IF(B1="","",VLOOKUP(B1,Sheet1!A:B,2,FALSE))
A3セルには所属と入力し、B3セルには次の式を入力します。
=IF(B1="","",VLOOKUP(B1,Sheet1!A:C,3,FALSE))
A4セルには事項、B4セルには開始日、C4セルには終了日、D4セルには備考と入力します。
A5セルには次の式を入力したのちにD5セルまでオートフィルドラッグしたのちに下方にもオートフィルドラッグします。
=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)))
B5セルおよびC5セルから下の行についてはセルの表示形式を日付にします。
また、D5セルから下の行についてはセルの表示形式をユーザー定義で#と入力して0が表示されないようにします。
シート3ではA1セルに期間と入力してB1セルには開始の日付をC1セルには終了の日付を入力します。
A2セルからG2セルまでは職員番号、氏名などの項目名を入力します。
A3セルには次の式を入力したのちにG3セルまでオートフィルドラッグしたのちに下方にもオートフィルドラッグします。
=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
- 回答日時:
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
- 回答日時:
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
No.1ベストアンサー
- 回答日時:
こんばんは!
参考になるかどうか判りませんし、大きく外している可能性もあります。
二つ目の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などに変更すればよろしいのでしょうか?
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- 転職 下記のような募集は、良い会社でしょうか?どう思いますか?入ってみたら良くない事が無いでしょうか? 4 2023/03/03 16:40
- 出産 回答お願いします。 出産日 7月25日 現在産前産後休暇中です。 休暇後育児休暇予定で、出産後、出産 2 2022/09/06 22:44
- 転職 長く続けられる好条件の求人でしょうか? 3 2023/07/12 18:45
- その他(就職・転職・働き方) ドラマを観てて思った事だけど…。例えば 西村京太郎サスペンスの中で高橋英樹演じる十津川警部がある回で 4 2022/06/13 14:47
- その他(ビジネス・キャリア) グーグルの障害者訓練プログラム募集あるがどうだろ?6時間勤務で月収22万!! 1 2023/02/17 20:36
- 妊娠・出産 育児休業給付金について 2 2023/08/14 13:47
- Excel(エクセル) Excelマクロ 差分抽出の方法が知りたいです。 2 2023/03/07 13:25
- C言語・C++・C# c言語の問題です 2 2023/07/21 10:51
- Excel(エクセル) エクセルで休憩時間を引く時と、引かない時の数式 3 2022/11/05 11:48
- 求人情報・採用情報 この求人はブラックだと思いますか? 月給26万5,000円 ~ 35万円 交通費支給あり <想定年収 9 2023/01/07 13:43
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
【スプレドシート】IMPORTRANGE...
-
Excel 日付を比較したら、同じ...
-
Outlookを立ち上げたらGoogleロ...
-
outlookのメールが固まってしま...
-
会社PCのメールが更新されない
-
【スプレッドシート】指定の日...
-
ウィンドウィズ メモ帳で日付だ...
-
英数字のみ全角から半角に変換
-
Excelで空白以外の値がある列の...
-
Microsoft Formsの「個人情報や...
-
microsoft office
-
MicrosoftOfficeについて質問で...
-
マイクロソフト 一時使用コード...
-
outlookで宛先が異なるメールを...
-
Outlookでの時間指定送信機能に...
-
【スプレドシート】目標達成の...
-
自分の専門分野の仕事。初見で...
-
Microsoft Officeを2台目のPCに...
-
【Excel VBA】PDFを作成して,...
-
エクセルでXLOOKUP関数...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
【スプレドシート】IMPORTRANGE...
-
【スプレッドシート】指定の日...
-
英数字のみ全角から半角に変換
-
会社PCのメールが更新されない
-
マイクロソフト 一時使用コード...
-
Office 2021 Professional Plus...
-
エクセルで例えば、関数を使っ...
-
Microsoft Formsの「個人情報や...
-
1つのPCに「Excel 2010」「Exc...
-
エクセルで例えば、A1に㈱ベ...
-
理由を教えてください。
-
エクセルでXLOOKUP関数...
-
マイクロソフト オフィスについて
-
VLOOKUP関数について
-
teams設定教えて下さい。 ①ビデ...
-
Googleのスプレッドシートでシ...
-
【Excel VBA】PDFを作成して,...
-
Microsoft365で写真をアルバム...
-
Outlook で宛先が複数の場合の人数
-
Excel テーブル内の空白行の削除
おすすめ情報