エクセルでの期間の重複について

質問

方法が全く思いつかないので、お手数ですが、ご回答おねがいします！
　下のようなリストがあります。

　　Ａ　　Ｂ　　　　　Ｃ　　　　　　Ｄ
１　井上　主任　2000/6/26 2003/3/20
２　井上　課長　2002/3/20　 2003/3/20

　この表で、井上さんが主任をしていた期間と、
　課長をしていた期間がかぶっています（兼任の為）
　しかし、兼任を考慮しないでカウントしてしまうと　職務期間の合計があわなくなります。
　役職上、課長の期間を優先して日数のカウントをし　たいんですが・・・
　当方、恥ずかしながらマクロは使えません。
　関数等、VBAを避けてなんとか出来ないかと考えていますが、
　手動でチェックするにもせめて重複している項目が目でみてわかるようにならないかと質問させて頂きました。
　是非、よろしくお願いします！

回答No.8

noname#30830 · ベストアンサー

お疲れ様です。
くわしく説明しようとして、遅くなってすいません。
あてはまらなかったらすまないのですが、前提条件として、
降格人事がないことと（課長と主任の）兼任終了日が同一であることを前提に不完全ながら考えてみました。

|Ａ |Ｂ |Ｃ |Ｄ | E |F |G |H |I
1|氏名|役職|就任日 |最終日 |序 |勤続|役職に |兼任|役職
列 |日数|兼任して|前の|在任
|いた日数|日数|日数
―――――――――――――――――――――――――――――
2|井上|課長|2002/3/20|2003/3/20| 3 |1004| 365| ▼|365
3|井上|主任|2000/6/26|2003/3/20| 2 | ▼| 0| 639|997
4| □ |　　| | | | ▼| 0|　▼|▼
5|井上|担当|2000/6/19|2002/6/25| 1 |　▼| 0| ▼|▼
6|佐藤|部長|2003/3/19|2003/3/20| 3 |1083| 1| ▼| 1
7|佐藤|課長|2000/6/26|2003/3/20| 2 | ▼| 0|1082|997
8| □ |　　| | |　 | ▼| 0| ▼|▼
9|佐藤|担当|2000/ 4/1|2000/6/25| 1 | ▼| 0| 0|▼

不完全ながら面倒ですいませんが、
a.まず勤続日数ほか数式をコピーするために、空白セル（□の記号が挿入した空白セルです）を必要なだけ挿入して、
　個人の行数を同一にします（例では4行ですが、揃えるためには何行でも空白セルを挿入して下さい）
b.その時の注意として、課長と主任を兼任していた行の間（例では3・4行目7・8行目）に空白セルは入れないで下さい
（これが兼任していた日数を算出する数式に必須なので）。
c.例のE列の序列欄は空白列でかまわないので挿入しておいて下さい。
　本来は必要ありませんが、主任より課長の役職が高い（優先したい）ということをはっきりさせることが必要な場合や、
　並べ替えで必要な時には作成して下さい。
　B列をコピーして貼り付け（挿入）して、置換して数字に置き換えて下さい。
ここから関数を説明します。
d.例のF列の勤続日数は、例ではF3に=D2-C5と入力してコピーして下さい
（表の▼のセルは計算結果を表示させなくていいセルですなのですが、数式が思いつかず変な値が出てしまいますので、
　まぎらわしいので消したほうがいいでしょう）。
　要は、左下が入社日、右上が計算上の最終日で、このセルの位置関係を統一しないと、トータルの日数の計算式がコピーできないので、
　空白行を挿入して、個人別に行数を同じにする必要があるのです。
e.G列の役職に兼任していた日数は、F2に=IF(D2=D3,D2-C2,0)を入力してコピーして下さい。
　兼任終了後も同じ役職でいた（仮に課長）とすれば、課長の行を2行にして、兼任最終日までのデータと、
　それ以降のデータを上の行に挿入して下さい。
（そうした時も、a.で説明した、個人の行数が同一になることに気をつけて下さい）
　それでもあてはまらない場合は、数式のD2=D3をC列に数式を変えるなど、工夫していただければ幸いです
f.兼任前の日数は、H3に=F2-G2と入力して下さい（上の行を参照しているので、H2に入力するとエラー値が出ます）。
g.役職在任日数は、I2に=D2-C2と入力
月の定義があいまいなので、日数/30などで概算を出していただければと思います。
参考になれば幸いです。

お礼

ご丁寧な回答、ありがとうございます！
出勤が月曜になりますので、早速試してみたいと思います。
とりあえず、お礼をお伝えしたかったので・・・

回答No.7

ao777

#5さんの発想を使わしてもらい、5行目に何もないとすると、
1の行を見出しだとして、D2に以下の式を入れてオートフィル
すると、今回のケースのみでの対応はできます。

=IF(ISBLANK(A3),DATEDIF(B2,C2,"M"),IF(C2<=B3,DATEDIF(B2,C2,"M"),DATEDIF(B2,B3-1,"M")))

回答No.6

ao777

単純に経過月を求めるのは
=DATEDIF(B1,C1,"M")
を使いますが、
解説
=DATEDIF(就任日,退任日,"年ならY月ならM日ならD")
ちなみに20ヶ月と、5日とかは全部20と出ます。
20ヶ月1日でも、21ヶ月と出したい場合は上記の式を少し変えます。

今回
井上　担当　2000/6/19 2000/6/25
となってますが、
井上　担当　2000/6/19 2001/6/25
の間違いではないでしょうか？

肝心の数式ですが、実際600件ほどあり、色々なとこでかぶっていたら、
関数ではできません

VBAを使うとできると思いますので、時間があれば作ってみます。

回答No.5

BLUEPIXY

まず、Ｂ列の役職で並び替えをします。
すると、担当、主任、課長の順に並びます。
（もし、役職が別にあって、並びが異なる場合は、ツール～オプション～ユーザー設定リストで並びを登録して、並び替えの時に、オプションで、標準から設定リストを選んで並び替えをします。）
その後、Ａ列の名前順で並び替えをします。
（前述の手順で並び替えを設定リストにしている場合は、標準にします）
そのようにすると、
氏名順、役職順になるので、
Ｅ列に
（１行目は項目名が入っていると仮定しています）
=IF(AND(A2=A3,C3<=D2),C3,D2)
のようにすれば、兼任している離職の日を上級職の着任の日にできます。
期間の計算にはこちらを使うようにします。

回答No.4

toshi_2000

データの内容を確認させてください。
１．井上が主任だけ、課長だけ、または、主任と課長がある、または担当、主任と課長とあるのように色々なパターンがあるのですか。
２．データの並び順はどうなっていますか。
３．２０００件ということは、６００人以上の名前があるということですか。

回答No.3

redowl

セルE1に
=IF(AND(C1<C2,D1<C2),"重複なし","重複有り")

とすれば良いように思えますが、
データが２行しかないため、ここまでドマリ。
あえて日数を入れるとすれば

E1に　　　=IF(AND(C1<C2,D1<C2),1+D1-C1,IF(AND(C1<C2,D1>C2),C2-C1,0))
E2に　　　=D2-C2+1
この場合だと、重複の表示は、　C1やD1セルの条件付き書式に　数式、　日付の大小比較式を入れて、　セルの塗りつぶしをするとか
＿＿＿＿＿＿＿＿＿＿
同じシート内に、複数名、役職名が存在した場合、
それらを、加味した上で、検索、重複の有る無しを判断し表示、且つ　任期日数も計算するのかが、質問から判断できず。
具体的なデータがもう少しあると、回答が付くはずです。

補足

早速の回答、ありがとうございます。
質問の説明不足で申し訳ございません。
「役職上、優先」というのは、
　　Ａ　　Ｂ　　　　　Ｃ　　　　　　Ｄ
１　井上　主任　2000/6/26 　2003/3/20
２　井上　課長　2002/3/20　 2003/3/20
３　井上　担当　2000/6/19 2000/6/25

　上の表で主任と課長の重複期間は2002/3/20～2003/3/20になります。しかし、課長の期間を優先したいので、主任の従事期間である2002/3/20～2003/3/2はカウントにいれたくありません。
よって、　理想の結果は、「主任21ヶ月　課長12ヶ月
担当12ヶ月」という結果を得たいんです。
データ数が少なくてすみません。同じシート内に、複数名ございます。
No,１の方の不足説明にもなるとは思うのですが・・・
お手数おかけします。

回答No.2

toshi_2000

課長の期間は、2003/3/20-2002/3/20
主任の期間は、2002/3/20-2000/6/26
でいいと思います。式を入力して書式を標準にすれば
日数が出ます。

補足

説明不足なので、申し訳ございません。
元あるデータをこのように修正できればいいのですが、データ数が２０００件ほどあるので、
手動で重複チェックをするのに時間がかかる問題から
こちらで質問させていただいた次第です。
補足説明はNO３の方のところでさせて頂きました。
よろしくお願いします。

回答No.1

noname#30830

こんにちは。
>役職上、課長の期間を優先して日数のカウントをしたいんですが・・・
「優先して」とはどういう意味なのか、よくわかりません。
詳しい説明をよろしくお願いいたします。