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.2

haccyan · ベストアンサー

こんばんは。

> 下記の計算式を教えてくださいm(^_^)m
　　→マルかバツかの判定と、ボーナス＝図書券の金額○○○円分の算出。
　　　ということですかね。というか、そういう意味としておこたえします。

まず、
> ４月～５月の２か月在籍のため、図書券１０００円。と
> 半分以内の在籍は対象外。は、矛盾してますね。
　　→勝手に、対象期間のうち半分未満は対象外。にしときます。

つぎに、
> ５行目については入塾と同時にAコース在籍のため、B列のコース変更年月日は空欄
　　→この場合、B列は空白ではなくA列と同じ日付をB列に入れとくべきです。
　　　"2006/3/10"が、B列にも入っているものとして考えます。

さらに、
３行目の人物が、なぜマルで、1,500なのか？
５行目の人物と同じく「2006/2/1に入塾と同時にAコース在籍」したが、
２月はコース変更月とみなされ切捨て。
2006/5/31に退塾したが、「退塾した日は在籍とみなす」ため、
３月～５月の３ヶ月間在籍したことになる。
　　→以上の理由のため、「マルで1,500」である。として考えます。

さらに、
> 図書券を「最大2000円分」、もらえる条件としては、
遅くとも2006/1/31までにコース変更（Ｂ→Ａ）していて、
なおかつ、2006/5/31までAコースに在籍していた。
（2006/5/31に退塾した場合を含む）ということになりますね。

さてさて、というかやっとできました。
Ａ列は、人物のNo.
Ｂ列は、入塾年月日
Ｃ列は、コース変更年月日
Ｄ列は、退塾年月日
Ｅ列は、資格の有無（○or×）
Ｆ列は、図書券の額
として２行目からデータの場合、
Ｅ２=IF((DATEDIF(DATE(YEAR(C2),MONTH(C2)+1,0),IF(D2="","2006/5/31",D2),"m")*IF(D2<DATEVALUE("2006/5/31"),0,1))>=2,"○","×")
Ｆ２=IF(E2="○",(DATEDIF(DATE(YEAR(C2),MONTH(C2)+1,0),IF(D2="","2006/5/31",D2),"m")*IF(D2<DATEVALUE("2006/5/31"),0,1))*500,0)
（自分で作っといていうのもなんだけど、長いな～。）
といれて、フィルコピーしてください。
いちおう簡単に確認しながら作りましたけど、
＃１さんと同じで、全パターン対応してるかどうかご自分で確認してくださいね。

お礼

haccyan様、ありがとうございます！！こんなにご丁寧に詳細に教えてくださって、感動しております。私的な事情で、締め切るのとお礼が遅くなり、大変申し訳ございませんでした。
しかし、とても複雑な式ですね…こんなに複雑な式、よくご存知ですね！！haccyanさんのような方には、朝飯前なのでしょうね。

回答No.3

marlboroman2

まず一番上に１行挿入し、Ａ１のセルに　2006/05/31　を入れて下さい。

３行目Ｄ列で○×の判定をする場合には、
=IF(IF(ISBLANK(C3)=1;$A$1;C3)<$A$1;"×";IF(DAYS($A$1;IF(ISBLANK(B3)=1;A3;B3))<61;"×";"○"))

３行目Ｅ列で金額を表示する場合には、
=IF(IF(ISBLANK(C3)=1;$A$1;C3)<$A$1;"";IF(DAYS($A$1;IF(ISBLANK(B3)=1;A3;B3))<61;"";IF(DAYS($A$1;IF(ISBLANK(B3)=1;A3;B3))<92;1000;IF(DAYS($A$1;IF(ISBLANK(B3)=1;A3;B3))<120;1500;2000))))

と、入力して下さい。

お礼

marlboroman2さん、ありがとうございます。ご回答に対するお礼が遅れて申し訳ございません。とても参考になりました。
いろんなやり方があるのですね。

回答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様、ありがとうございます。とても感謝しております。