[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『条件付の手当を計算する方法』(まっちやん)
4月1日 4月2日 4月3日 @単価:円 Aカウント 金額(@×A) 作業A 1:00 2:00 1:00 80 1 80 作業B 1:00 2:00 0:00 60 0 作業C 1:00 0:00 0:50 90 1 90 計 3:00 4:00 1:50
上のような表(手当算出表)を作りたいです。時間を入力するだけで金額が自動的に表示できればいいのです。ただし、3つの作業時間の合計が2時間以上の場合にのみ手当をつけることができます(4月3日はどの作業にも手当はつけられません)。また、作業時間が同じ日(4月2日)は単価の一番高い作業に手当がつきます。
ここ1ヶ月ほどこの表の作成のことでずっと悩んでいます。
どなたかこの表の作り方を教えてください。よろしくお願いします。
上の表がA1から順番に入っているとします。 B6に=IF(B4>TIME(0,,),0,(IF(B5>=TIME(2,,),1,0)))*80として横へフィルします。 B7に=IF(OR(B2>TIME(0,,),B4>TIME(0,,)),0,(IF(B5>=TIME(2,,),1,0)))*60として横にフィル。 B8に=IF(B4=TIME(0,,),0,IF(B5>=TIME(2,,),1,0))*90としてフィルします。 後はG1,G2,G3にSUM関数で合計を出して下さい。もし6,7,8行が邪魔なら非表示で消します。 こんな感じではダメですか?(ケン)
ケンさんありがとうございます! あと、手当は基本的にはその日一番長い作業時間につけるということを説明していませんでした。例えば、 4月4日 単価 作業A 1:00 80円 作業B 2:00 60円 この日は作業Bに手当60円がつきます。 作業C 0:40 90円 この条件も含めるにはどうすればいいでしょうか。よろしくお願いします。
B6に=IF(B5>=TIME(2,,),IF(B2=MAX(B2:B4),IF(B4=MAX(B2:B4),0,1),0),0)*80 B7に=IF(B5>=TIME(2,,),IF(B3=MAX(B2:B4),IF(OR(B2=MAX(B2:B4),B4=MAX(B2:B4)),0,1),0))*60 B8に=IF(B5>=TIME(2,,),IF(B4=MAX(B2:B4),1,0),0)*90 こんな感じでどうですか?(ケン)
ケンさんいろいろありがとうございます。ほんとに助かりました! 最後にもうひとつだけ‥私のわがままなのですが、実は大量な件数を入力する関係上 「1:00」と入力するとき、普通は1:00と単純に入力するところを私は、100と入力すれば1:00と表示するように 表示形式をユーザー定義で「0”:”00」に設定しています。でもこれをするとB5>=TIME(2,,)の関数は機能しなくなりますね。 これを解決する方法がもしあればお教えいただけないでしょうか?無理ばかり言って本当に申し訳ありません。
100入力でユーザー定義で「0":"00」の設定では数値で、時刻を表すシリアル値ではありません。 仮にB4の数値 1:00 をシリアル値に変換する式は =(B4/100)/24+MOD(B4,100)/3600 とします。 セルの書式設定は「時刻」にします。 大量の件数故に手抜き入力が仇になりましたが、何方かのマクロ処理案内に期待したいですね (シニア)
入力時に反応させることもできますけど、最近はそういうことしないほうがいいかなと...
シニアさんの数式をそのまま組み込みました。 入力範囲や値のチェック、未入力時の処理などは組み込んでいないので ご自分でアレンジしてください。
使い方は、あらかじめ時間を入力する範囲に utime と名前を付けておいて、 入力後にマクロを実行させればよいです。 (ramrun)
Sub macro()
For Each adr In Range("utime") adr.Value = (adr.Value / 100) / 24 + (adr.Value Mod 100) / 3600 Next Range("utime").NumberFormatLocal = "h:mm"
End Sub
皆さん、おかげさまで悩みの種をすべて解決していただき、待望の手当算出表を完成させられそうです。 本当にありがとうございました!感謝します!
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.