契約更新回数を算出したい

質問

人事部で業務を行っております。
パートタイマーの離職時にこれまでの契約更新回数を離職票に記入しなければなりませんが
今は頭で数えて記入しているような状態です。
そこで契約更新回数を算出できる関数をぜひご教示いただきたく・・・。

契約更新は年に2回あり、6月1日～11月30日、12月1日～5月30日が
それぞれの契約期間です。

例えば2014年3月1日に契約開始したパートタイマーの場合、
2014年6月1日に1回目の契約更新を行い、2014年12月1日に2回目の契約更新を行います。

氏名　　　　契約日　　　　　　更新回数
Aさん　　　2014年3月1日　　　 〇回
Bさん　　　2014年4月21日　　　×回
Cさん　　　2015年8月25日　　　△回
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
人別に契約日が入力されたデータはありますので、
あるセルに退職日を入力するとこれまでの契約更新回数が
表示されるような関数を組みたいです。

ぜひ教えてください！

回答No.7

kagakusuki · ベストアンサー

各契約更新日が丁度6ヶ月ごとにやって来る様になっている事から、次の様な方法を使う事が出来ます。

　今仮に、A3セルに「氏名」、B3セルに「契約日」、C3セルに「更新回数」と入力されていて、B1セルに退職日を入力するものとします。
　その場合、まずC4セルに次の関数を入力して下さい。

=IF(OR($B$1="",$B4=""),"",IF(OR(SUM($B$1)<SUM($B4),SUM($B4)<"1904/6/1"+0),"(入力値無効)",INT(DATEDIF(DATE(YEAR($B4),LOOKUP(MONTH($B4),{0,6,12}),1),$B$1,"m")/6)))

　そして、C4セルをコピーして、C5以下に貼り付けて下さい。

　以上です。

お礼

まさに求めていた形でした。
早速管理しているデータに使わせていただきました。
ありがとうございました。

回答No.8

keithin

ご相談で掲示されたサンプルとちょっと違い，

＞人別に契約日が入力されたデータはありますので、
＞あるセルに退職日を入力すると
＞これまでの契約更新回数が表示されるような

氏名，契約日とデータが並んでいるところに加えて，退職日を記入すると更新回数が表示できるよう，合計４列の表になりますね。

添付図：
Ａ，Ｂ列は既に用意されているデータですね。
Ｃ列に退職日を記入すると
Ｄ２：
=IF(COUNT(B2:C2)=2,(YEAR(C2)-YEAR(B2))*2+(MONTH(B2)<6)+(MONTH(B2)<12)-(MONTH(C2)<6)-(MONTH(C2)<12),"")
以下コピー

ぐらいで出来ると思います。

お礼

ご教示いただきありがとうございました。
離職日以外のデータ管理にも参考にさせていただきます。

回答No.6

bunjii

回答No.5に数式の誤りがありました。
D2=IF(C2="","",COUNT(D2：Z2))
　　　　↓
D2=IF(C2="","",COUNT(E2:Z2))

お礼

ご教示いただきありがとうございました。
離職日以外のデータ管理にも参考にさせていただきます。

回答No.5

bunjii

>パートタイマーの離職時にこれまでの契約更新回数を離職票に記入しなければなりませんが今は頭で数えて記入しているような状態です。
それが一番簡単です。

>例えば2014年3月1日に契約開始したパートタイマーの場合、2014年6月1日に1回目の契約更新を行い、2014年12月1日に2回目の契約更新を行います。
契約開始（採用日付）が6月1日または12月1日のときは次の更新日が第1回目の更新日ですよね？
また、退職日が6月1日または12月1日のときは契約更新されたことになるのでしょうか？
これらの条件を提示して頂けないので具体的な数式を提示できません。

>人別に契約日が入力されたデータはありますので、あるセルに退職日を入力するとこれまでの契約更新回数が表示されるような関数を組みたいです。
実際に更新された日付を入力したセルが有るのなら、そのセルの数をCOUNT関数で数えれば良いでしょう。
退職日が入力されたか否かはIF関数で条件分岐すれば良いことになります。
例えばAさんの名前がA2セルとし、契約日がB2セルで、退職日がC2、更新回数がD2、契約更新日がE2からZ2セルまでに順次入力されるものとすれば次のような数式でカウントできるはずです。
D2=IF(C2="","",COUNT(D2：Z2))
D2からZ2に更新予定日を将来まで入力されている場合はCOUNTIFS関数を使わなければならないでしょう。
Excel 2003以前のバージョンではCOUNTIFS関数が組み込まれていませんのでCONTIF関数を2組使って減算しなければなりません。
関数の使い方はヘルプを参照すれば分かるはずです。
ヘルプの例題を読んで理解できない場合は入門書で学習してください。

回答No.4

imogasi

＃３です。参考までに
例データ　Ａ，Ｂ列が元データ　Ｄ，Ｅ，Ｆ列は関数で算出列
Ａ，Ｂ、Ｄ、Ｅ、Ｆ列
2013/2/11 2016/2/28 1 7 6
2014/2/5 2016/4/29 3 8 5
2014/10/1 2016/4/29 5 8 3
ーー
ＶＬＯＯＫＵＰの「ＴＲＵＥ」型を使う。
Ｄ１　の式　=VLOOKUP(A1,$H$1:$I$13,2,TRUE)
Ｅ１　の式　=E1-D1
ーーー
検索に参照する表　使う人がシートに作る必要があり。
区分に（１日程度。以上、以下未満などの点で）注意する必要があり。
Ｈ，Ｉ列
0 0
2013/1/1 1
2013/3/1 2
2013/9/1 3
2014/3/1 4
2014/9/1 5
2015/3/1 6
2015/9/1 7
2016/3/1 8
2016/9/1 9
2017/3/1 10
2017/9/1 11
2018/3/1 12
ーー
参照の仕組みは
ＡとＢの間の値の場合ーー＞Ｃを引く
Ｂ　→ Ｃ　　　ある行
↑
A 　　　　　　次の行
ーーー
深く丁寧にチェックしてないので、＋１日や１日づらす、という必要がある場合があるかもしれないが、よく検討して、この方式が使えるかを考えて、生かしてください。
使えない場合は後免。
もし関数でやると、関数式が長く、関数を組み合わせて、している内容が理解しづらい可能性が大きいので。
テーブル参照方式は、使うのが適当な場合は、非常に簡潔で優れた方式になる場合が多い。

お礼

ご教示いただきありがとうございました。
離職日以外のデータ管理にも参考にさせていただきます。

回答No.3

imogasi

（１）更新日付そのもののデータ行が、人ごと（たとえばＡさん）にまとまっているなら、
Ａさんの範囲行の中での回数＝データ出現数ーー＞関数Ｃｏｕｎｔ　　で
（２）Ａ，Ｂ、Ｃさん・・・分が１つのシートの、行的に、いろんな行に混在（出現）するなら、ＣＯＵＮＴＩＦＳなどで
　その際、カウント条件は
・Ａさんである行
・期間（日付範囲）
で、出現回数を関数でかぞえる。
で数えられるだろう。
（３）入社契約日～現在日までに、６月１日、１２月１日が何回通過したか数えて済む（それで正確）なら、そういう関数式を考えればよいが。これはややむつかしそうだが。
参考　似た質問例が
http://detail.chiebukuro.yahoo.co.jp/qa/question_detail/q10120599760
に出ている。
ーー
ほしい結果は例示されているが（こんなのは想像はつく）、各パートのエクセルシートの「元データの状況（あり様）」が質問に説明されていないのでは。
もしそうなら、質問の仕方が不完全ではないか。
シート的にどうか、列的にどうか、行的にはどうか。
パートを区別するＩＤは（例氏名か番号か）など。

お礼

ご教示いただきありがとうございました。
離職日以外のデータ管理にも参考にさせていただきます。

回答No.2

f272

A1に契約日があって，B1に退職日があれば
=(YEAR(B1)-YEAR(A1))*2+(MONTH(A1)<6)+(MONTH(A1)<12)-(MONTH(B1)<6)-(MONTH(B1)<12)

お礼

ご教示いただきありがとうございました。
離職日以外のデータ管理にも参考にさせていただきます。