Excel 在籍期間に応じてボーナスの額を算出 条件式 計算式

質問

こんにちは。下記の計算式を教えてくださいm(^_^)m

【期間に関する条件式】
　　　　A　　　　　　B　　　　　　　C　　　　D　　　E
1　2006/2/1　　　2006/3/10　　2006/5/15　　（バツ）　　　　　　　　
2　2006/1/10　　2006/3/15　　　　　　　　　（マル）1,000
3　2006/2/1　　　　　　　　　2006/5/31　（マル）　1,500
4　2006/4/1　　2006/4/15　　2006/6/15　　（バツ）　　　　　　　　
5　2006/3/10　　　　　　　　　　　　　　（マル）　1,000

A列；入塾年月日（５行目については入塾と同時にAコース在籍）
B列；BコースからAコースへのコース変更年月日（５行目については入塾と同時にAコース在籍のため、B列のコース変更年月日は空欄）
C列；退塾の年月日（退塾する人だけ入力）
D列；図書券を貰う資格の有無（ある人；マル印、無い人；バツ印）
E列；図書券を貰う資格のある人の図書券の額

上記の説明です。ある学習塾で、Aコースに2006/2/1から2006/5/31まで在籍している人に、在籍期間に応じて図書券を「最大2000円分」プレゼントすることになりました。

C列の資格の有無の条件とD列の図書券の額の算出方法；
2006/2/1～2006/5/31までの期間を対象に下記を考慮して計算。［a］期間中半分以上（この場合対象期間が４か月間あるので、２ヶ月以上の在籍が「半分以上」となる）在籍していること。半分以内の在籍は対象外。
［b］B列（５行目についてはA列）において、コース変更月（５行目については入塾月）は期間として換算せず切り捨てる。
［c］対象期間末（2006/5/31）に在籍していない人は、たとえ期間中半分以上在籍していても対象外。

〈例〉１行目の人物；［c］に該当せず図書券受領資格なし。
２行目の人物；資格有り。３月は移動月で切り捨て、４月～５月の２か月在籍のため、図書券１０００円。

回答No.1

papayuka

一度に出すと複雑になりそうなので、例えば作業列としてG列～L列を使って

入会年月日　G2　=IF(B2="",A2,B2)
入会翌月日　H2　=DATE(YEAR(G2),MONTH(G2)+1,1)
対象先頭月　I2　=IF(H2<DATEVALUE("2006/2/1"),DATEVALUE("2006/2/1"),H2)
対象終了月　J2　=IF(OR(C2>=DATEVALUE("2006/5/31"),C2=""),DATEVALUE("2006/5/31"),"")
対象月判定　K2　=DATEDIF(I2,J2,"ym")
結果の金額　L2　=CHOOSE(K2,1000,1500,2000)

のような感じで下にコピーするとそれっぽくなりました。
全パターンに対応出来てるかはご自分で検証して下さい。

＊1行目はタイトル行で2行目からデータの場合です。
＊G～J列が数字にしか見えない場合はセルの書式設定で 日付 yyyy/m/d にして下さい。

お礼

ごかいとうをありがとうございました。お礼を遅れた事をお詫び致します。
とてもご丁寧に、分かりやすいご回答でした。papayuka様、ありがとうございます。とても感謝しております。