こんにちは。下記の計算式を教えてください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列;入塾年月日(5行目については入塾と同時にAコース在籍)
B列;BコースからAコースへのコース変更年月日(5行目については入塾と同時にAコース在籍のため、B列のコース変更年月日は空欄)
C列;退塾の年月日(退塾する人だけ入力)
D列;図書券を貰う資格の有無(ある人;マル印、無い人;バツ印)
E列;図書券を貰う資格のある人の図書券の額
上記の説明です。ある学習塾で、Aコースに2006/2/1から2006/5/31まで在籍している人に、在籍期間に応じて図書券を「最大2000円分」プレゼントすることになりました。
C列の資格の有無の条件とD列の図書券の額の算出方法;
2006/2/1~2006/5/31までの期間を対象に下記を考慮して計算。[a]期間中半分以上(この場合対象期間が4か月間あるので、2ヶ月以上の在籍が「半分以上」となる)在籍していること。半分以内の在籍は対象外。
[b]B列(5行目についてはA列)において、コース変更月(5行目については入塾月)は期間として換算せず切り捨てる。
[c]対象期間末(2006/5/31)に在籍していない人は、たとえ期間中半分以上在籍していても対象外。
〈例〉1行目の人物;[c]に該当せず図書券受領資格なし。
2行目の人物;資格有り。3月は移動月で切り捨て、4月~5月の2か月在籍のため、図書券1000円。
No.2ベストアンサー
- 回答日時:
こんばんは。
> 下記の計算式を教えてくださいm(^_^)m
→マルかバツかの判定と、ボーナス=図書券の金額○○○円分の算出。
ということですかね。というか、そういう意味としておこたえします。
まず、
> 4月~5月の2か月在籍のため、図書券1000円。と
> 半分以内の在籍は対象外。は、矛盾してますね。
→勝手に、対象期間のうち半分未満は対象外。にしときます。
つぎに、
> 5行目については入塾と同時にAコース在籍のため、B列のコース変更年月日は空欄
→この場合、B列は空白ではなくA列と同じ日付をB列に入れとくべきです。
"2006/3/10"が、B列にも入っているものとして考えます。
さらに、
3行目の人物が、なぜマルで、1,500なのか?
5行目の人物と同じく「2006/2/1に入塾と同時にAコース在籍」したが、
2月はコース変更月とみなされ切捨て。
2006/5/31に退塾したが、「退塾した日は在籍とみなす」ため、
3月~5月の3ヶ月間在籍したことになる。
→以上の理由のため、「マルで1,500」である。として考えます。
さらに、
> 図書券を「最大2000円分」、もらえる条件としては、
遅くとも2006/1/31までにコース変更(B→A)していて、
なおかつ、2006/5/31までAコースに在籍していた。
(2006/5/31に退塾した場合を含む)ということになりますね。
さてさて、というかやっとできました。
A列は、人物のNo.
B列は、入塾年月日
C列は、コース変更年月日
D列は、退塾年月日
E列は、資格の有無(○or×)
F列は、図書券の額
として2行目からデータの場合、
E2=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,"○","×")
F2=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)
(自分で作っといていうのもなんだけど、長いな~。)
といれて、フィルコピーしてください。
いちおう簡単に確認しながら作りましたけど、
#1さんと同じで、全パターン対応してるかどうかご自分で確認してくださいね。
haccyan様、ありがとうございます!!こんなにご丁寧に詳細に教えてくださって、感動しております。私的な事情で、締め切るのとお礼が遅くなり、大変申し訳ございませんでした。
しかし、とても複雑な式ですね…こんなに複雑な式、よくご存知ですね!!haccyanさんのような方には、朝飯前なのでしょうね。
No.3
- 回答日時:
まず一番上に1行挿入し、A1のセルに 2006/05/31 を入れて下さい。
3行目D列で○×の判定をする場合には、
=IF(IF(ISBLANK(C3)=1;$A$1;C3)<$A$1;"×";IF(DAYS($A$1;IF(ISBLANK(B3)=1;A3;B3))<61;"×";"○"))
3行目E列で金額を表示する場合には、
=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
- 回答日時:
一度に出すと複雑になりそうなので、例えば作業列として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様、ありがとうございます。とても感謝しております。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- 簿記検定・漢字検定・秘書検定 満期保有目的債券の償却原価法の計算について(簿記2級) 1 2022/06/18 15:40
- 転職 賞与について詳しい経理の方?教えて下さい。 賞与が年2回支給されます。 支給対象期間6月は(前年10 8 2023/04/17 19:23
- 憲法・法令通則 令和4年(2022年)4月1日に,国籍の選択をすべき期限が変更されます。 成年年齢の引き下げ等を内容 2 2023/03/12 12:11
- 大学・短大 必修単位を意図的に落とす 5 2022/07/15 00:40
- 戸籍・住民票・身分証明書 離婚後の妻側の籍や扶養などについて。 2 2022/03/22 15:45
- 健康保険 傷病手当金について 傷病手当金の在職中の分の申請は、退職後でも可能ですか? 調べてみると、退職後は健 2 2023/01/10 21:35
- Excel(エクセル) 列を自動で追加したい 3 2022/07/11 12:58
- 就職・退職 失業保険についての質問です。 ①前々職を適応障害で退職しました。在籍期間としては2018年4月〜20 2 2022/06/06 00:46
- 所得・給料・お小遣い 雇用契約書に「夏季賞与は前年の11月16日から当年5月15日までに在籍し支給日まで引き続き勤務する者 6 2022/06/25 11:39
- 弁護士・行政書士・司法書士・社会保険労務士 遺産分割協議書作成で、故人3名・健在者5名の必要書類集めの期間について 6 2022/04/01 14:21
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
IFとIFS関数
-
Excelを無料で使うには? パソ...
-
セルの数を求めたい
-
エクセルの数式バーのフォント...
-
Excelの表示についての質問
-
再質問です。マクロの修正箇所...
-
データチェックを行うエクセル...
-
Excelに詳しい方! B列が「日...
-
西暦や和暦の表示をyyyymmdd表...
-
Excelで50個のセルに同じ文字を...
-
Excelの数式について教えてくだ...
-
Excel VBAで全ての矢印を赤色に...
-
エクセル関数を使って
-
祝日と土曜、日曜の合計をカウ...
-
xlsxファイルを保存する際にPDF...
-
Excelについて
-
エクセルVBA、別ブックへ転記す...
-
エクセルで「ページレイアウト...
-
2列に入っているデータを1列...
-
【ExcelVBA】名前を付けて保存→...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
半角カタカナをヘボン式ローマ...
-
(マクロ)vlookupの元データを同...
-
エクセルで上位バイトのセルと...
-
exselの質問です
-
Excel 大小比較演算子による「...
-
Excel VBについての質問です。
-
エクセルの問題です。絶対値の...
-
非表示列の再表示に失敗
-
職場の人から聞かれており、こ...
-
Excel関数-文字列で自動作成さ...
-
Excelデータをコピペして、ペー...
-
ユーザー定義関数をアドイン登...
-
【マクロ】for next構文について
-
エクセルの日付を編集する
-
【マクロ】VLOOKUPにて参照元に...
-
exselで最小数で並び替える関数
-
libre 表計算ソフトの計算がう...
-
エクセルで表
-
エクセルの表で1年間の曜日を...
-
西暦和暦
おすすめ情報