エクセルでの超過分の算出方法

質問

Ｄ　　Ｅ　　F G H I J K L M
積載重量 15Ｋ箱 15 10Ｋ箱 10 5Ｋ箱 5 5Ｋ箱以下 3
個数 103 個数 70 個数 42 個数 30 割増額
3,650 100 10300 110 7700 120 5040 150 4500 1500

一定の重量を超えた場合に、超えた重量分を割増額として加算したいのですが、但し条件があります。
大きい箱の単重より規定重量内に入れながら、超えた重量分は各箱数に単価を掛けたのを算出する方法
この例題ならは、超えた重量は１５０Ｋとなり、５Ｋ以下は全てと５Ｋ以下の１００個が規定重量内に収まり、超過分は５Ｋ以下の５０個となり、５０個×３０円＝割増額は１５００円になります
１行目は単重で２行目は単価になります
３行目は個数を入力した場合、隣に小計金額が表示されます
一定の重量は、３５００Ｋ

割増額の１５００円のところに、一挙に計算式を入れて算出できませんか？

回答No.6

masah43 · ベストアンサー

ANo.4 gyouda1114 さんの回答について
基本的な考え方は正しいと思いますが超過重量の求め方が正しくないように思います。
gyouda1114 さんの式では１５ｋの個数が奇数個だと
１０ｋの超過重量に５ｋ単位の端数がでますが
１０ｋ（単重）単位になるのが正しいのではないでしょうか。
その前提で以下のような改良案を考えました。
－－－－－－－－－－－－－－－－－－－－－－－－－－
（８行目に超過個数を追加）
　　　＝１～５行目は省略（変更なし）＝
6　超過重量　　0　 50　 200　 240　　0 －
7　超過料金　　0　 350　 1,680　 2,400　　0　　4,430
8　超過個数　　0　 5　 40　 80　　0 －
B8=IF(SUM($B$4:B4)>=3500,ROUNDUP((SUM($B$4:B4)-SUM(3500))/B1,0),0)
C8=IF(SUM($B$4:C4)>=3500,ROUNDUP((SUM($B$4:C4)-SUM(3500,$B$6:B6))/C1,0),0)
C8をD8からF8にオートフィル
（６行目超過重量の計算式）
B6=B8*B1　　C6からF6にオートフィル

超過個数を８行目にしたのは説明上わかりやすくするためで、見易さを考えれば超過重量の前行がいいでしょう。

いかがでしょうか？

お礼

みさなん有難うございます

何とか解決のメドがつきました

又、わからないことがあると思いますので宜しくおねがいします

回答No.5

某HN クロメート(Chromate)（@CoalTar）

結果的にはgyouda1114さんの回答にはなると思いますが

> 単重の小さいものを超過にすることにより、
> 単価を掛けた場合に金額が大きくなるためです。
> つまり超過した個数を増やすようにすれば、金額が増えるため
と
> この算出の考え方として、単重の大きい順より
> 規定重量（３５００Ｋ）に入れること
が結びつかないです。

どうせならK単価の高い3K(1Kあたり10)で算出すれば一番高くつきます

#サンプルは最低でも3つ以上提示しないと説明不足を補えませんよ

回答No.4

gyouda1114

表を作り変えて
　　Ａ　　　　 Ｂ　　　Ｃ　　　Ｄ　　　Ｅ　　　Ｆ　　　Ｇ
1　単　重　　　15　　　10　　　5　　　3　　　0.8 　　合計
2　単　価　　 103　　　70　　 42　　 30　　　 7　　　－
3　個　数　　　10　 　340　 40　　 80　　　 0　　　－
4　総重量　　 150　 3,400　　200　　240 0　　　－
5　金　額　 1,030　 23,800　1,680　2,400　　　 0　　　－
6　超過重量　　 0　　　50　　200　　240 　0　　　－
7　超過料金 　 0　　 350　1,680　2,400　　　 0　 4,430

超過重量　B6=IF(SUM($B$4:B4)>=3500,SUM($B$4:B4)-SUM(3500),0)
　　　　　C6=IF(SUM($B$4:C4)>=3500,SUM($B$4:C4)-SUM(3500,$B$6:B6),0)
　　　　　C6をD6からF6にオートフィル
超過料金　B7=B6/B1*B2　C7からF6にオートフィル
超過料金合計　=SUM(B7:F7)

回答No.3

noname#204879

[ANo.2この回答へのお礼]に対するコメント、

》 単重の大きい順より規定重量（３５００Ｋ）に入れること
》 １５Ｋ＊１０個＝１５０Ｋ
》 １０Ｋ＊３３５個＝３３５０Ｋ
》 これで規定重量となり…

この部分が理解できません。なぜ、それだけになるのか説明ください。
　 １５Ｋ＊２３３個＝３４９５Ｋ
　 　５Ｋ＊　　１個＝　　　５Ｋ
でも規定重量となりませんか？
「単重の大きい順より規定重量（３５００Ｋ）に入れる」とは、具体的にどういうことですか？

補足

確かに規定重量にはなりますが、最終目標の超過額に影響がでてしまうからです

単重の小さいものを超過にすることにより、単価を掛けた場合に金額が大きくなるためです。

つまり超過した個数を増やすようにすれば、金額が増えるため

説明下手で申し訳ないです

回答No.2

某HN クロメート(Chromate)（@CoalTar）

D3セルに 3650が入っている
サンプルが不足しているため条件が違う可能性がある
例.
3650 → 1500
3951 → 3822
4100 → 5040
4101 → 4270
となるとして

[Ctrl]+[F3]名前の定義
名前 まっち
参照範囲 =MATCH($D$3-3500,{1500;1100;600;450},-1)
=IF(D3<=3500,0,
CEILING((D3-3500)*
INDEX({10300;7700;5040;4500},まっち)/
INDEX({1500;1100;600;450},まっち),
INDEX({103;70;42;30},まっち)))

お礼

私の説明不足にも関わらず有難うございます
折角、回答を頂いてるのですが、私には少し理解できてません＾＾；

詳しく説明させてもらいます。（エクセルのあてはめてください）
Ｆ９に１５（単重）
Ｆ１０に１０３（単価）
Ｈ９に１０（単重）
Ｈ１０に７０（単価）
Ｊ９に５（単重）
Ｊ１０に４２（単価）
Ｌ９に３（単重）
Ｌ１０に３０（単価）
Ｎ９に０．８（単重）
Ｎ１０に７（単価）
Ｄ１１合計重量（=(E11*F9)+(G11*H9)+(I11*J9)+(K11*L9)+(M11*N9)）
Ｅ１１には個数（今回は１０）
Ｆ１１に小計額（=F10*E11）
Ｇ１１には個数（今回は３４０）
Ｈ１１に小計額（=H10*G11）
Ｉ１１には個数（今回は４０）
Ｊ１１に小計額（=J10*I11）
Ｋ１１には個数（今回は８０）
Ｌ１１に小計額（=L10*K11）
Ｍ１１には個数（今回は０）
Ｎ１１に小計額（=N10*M11）

Ｐ１１に加算額が表示されるようにしたい
（今回の場合は、４４３０円）
この算出の考え方として、単重の大きい順より規定重量（３５００Ｋ）に入れること
１５Ｋ＊１０個＝１５０Ｋ
１０Ｋ＊３３５個＝３３５０Ｋ
これで規定重量となり、超過分は
１０Ｋ＊５個＝５０Ｋ　　Ａ（５個＊７０円）
５Ｋ＊４０個＝２００Ｋ　Ｂ（４０個＊４２円）
３Ｋ＊８０個＝２４０Ｋ　Ｃ（８０個＊３０円）
Ａ＋Ｂ＋Ｃ＝４４３０円

こんな説明しかできませんが、宜しくお願いします。

回答No.1

gyouda1114

読む人に理解できるように書かないと誰も回答できません。

お礼

すみません＾＾；