Excel (一般機能)

Excelの一般機能に関するフォーラムです。
  • 掲示板への投稿には会員登録(無料)が必要です。会員登録がまだの方はこちら
  • 掲示板ご利用上のお願い」に反するご記入はご遠慮ください。
  • Q&A掲示板の使い方はこちらをご覧ください
トピックに返信
質問

 
(Windows 10 Pro : Excel 2010)
条件計算について
投稿日時: 20/05/25 15:42:01
投稿者: neu

随分前にこちらでお世話になり、Excelの関数も少しわかってきたところだったのですが、
複数の条件が重なるとお手上げ状態で困っています。
支給額の計算に使いたいのですが、下記のF支給額の欄を関数で計算させたいのですが、どのような式の組み方をすれば正確に計算できるのでしょうか。
教えていただけましたら幸いです。
ちなみにDの列には
=TEXT(B終了時間-A開始時間-C休憩時間,"h:mm")*1
の式を入れてます。
 
  A開始時間  B終了時間  C休憩時間  D実働時間   E基本料  F支給額
1) 9:00    10:00    0    1:00(60分)  @5000   5000
2) 9:00    15:00   0:50   5:10(310分)  @2000   5170
3) 10:00    18:00 0:50   7:10(430分)  @6000   19500
4) 13:00    17:00   0    4:00(240分)  @2000   4000
5) 13:00    14:30   0    1:30(90分)   @5000   5000
6) 9:00 18:00 1:00 8:00(480分)   @8000   25000
 
@稼働時間が90分以下の場合は基本料を払う。
A稼働時間が180分以上240分迄は基本料×2を支払う(ただし上限額10000円)
B240分を超えた時間は240分までを基本料×2(上限額10000円)に240分を超えた時間を基本料×0.5で計算し合計額を支払う。(上限額25000円)
C基本料は個人によって異なる。
D10分未満は切り上げる。
E10円未満は最後で切り上げる。

回答
投稿日時: 20/05/25 18:11:30
投稿者: 半平太

>@稼働時間が90分以下の場合は基本料を払う。
>A稼働時間が180分以上240分迄は基本料×2を支払う(ただし上限額10000円)
 
1.@とAの間、すなわち 90分超 180分未満の場合はどうなっていますか?
 
2.サンプルの「F支給額」が本当に正しいか再確認願います。

投稿日時: 20/05/25 21:11:35
投稿者: neu

半平太様
コメントありがとうございます。
>@稼働時間が90分以下の場合は基本料を払う。
>A稼働時間が180分以上240分迄は基本料×2を支払う(ただし上限額10000円)
  
1.@とAの間の場合ですが、 90分超 180分未満の場合は発生しない為、万が一発生した場合は
エラーになります。
  
2.サンプルの「F支給額」についてですが、手計算の結果は合っています。
 
1).5)は90分以内なのでEの基本料のまま。
2)は条件Bが適用され、240分までの基本料×2で4000円に超過70分分は基本料×0.5(10円未満切り上げ)1170円の合計5170円
 
4)は240分迄は基本料×2なので4000
3)は240分迄は基本料×2(上限10000)+超過190分は基本料×0.5で計算するので9500円の合計19500
6)は240分迄は基本料×2(上限10000)+超過240分は基本料×0.5で計算の16000の合計26000ですが、条件Bの上限適用で25000円になります。
 
判りにくい記載で申し訳ありません。
現在手計算で処理しているため、少しでも効率よく集計したいのですが、条件が細かいので、難儀しています。

回答
投稿日時: 20/05/25 23:37:25
投稿者: 半平太

D2セル =TEXT(CEILING(TEXT(B2-A2-C2,"h:mm"),"0:10"),"h:mm")*1
F2セル =ROUND(IF(D2<="1:30"*1,E2,IF(D2<"3:00"*1,NA(),IF(D2<="4:00"*1,MIN(10000,E2*2),MIN(25000,MIN(10000,E2*2)+(D2-"4:00")*E2*0.5*24)))),-1)

投稿日時: 20/05/26 17:56:16
投稿者: neu

半平太様
コメントありがとうございます。
記載下さった式で試してみたところ、基本料が10円単位がある場合の10円未満切り上げ部分に不具合があったので、ROUNDUPの式に置き換えて検証しています。
CEILINGやMINの関数は使ったことがなかったので勉強になりました。
本当にありがとうございます。
 
D2セル =TEXT(CEILING(TEXT(B2-A2-C2,"h:mm"),"0:10"),"h:mm")*1
F2セル =ROUNDUP(IF(D2<="1:30"*1,E2,IF(D2<"3:00"*1,NA(),IF(D2<="4:00"*1,MIN(10000,E2*2),MIN(25000,MIN(10000,E2*2)+(D2-"4:00")*E2*0.5*24)))),-1)
[/quote]

トピックに返信