[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『エクセルで送料の計算をしたい』(jaga)
エクセルで送料の計算(計算式など)をしたいと思っていますが、エクセル初心者のため教えてください。
A箱((M商品1個かR商品2個)が入る)、B箱((M商品2個かR商品4個)が入る)、C箱(R商品1個が入る)の箱があったとします。
またこれらの各箱に、M商品、R商品を入れて安く送付する場合の送料を計算したいです。
以下に例を記載しますので、ご教授のほどよろしくお願いします。
| A箱(M商品1個入り | B箱(M商品2個入り | C箱(R商品1個
| /R商品2個入り) | /R商品4個入り) | 入り)
北海道| 3,000 | 3,500 | 2,800
東京 | 2,000 | 2,500 | 1,800
大阪 | 1,000 | 1,500 | 800
広島 | 500 | 1,000 | 300
愛媛 | 1,000 | 1,500 | 800
福岡 | 1,500 | 2,000 | 1,300
宮崎 | 2,000 | 2,500 | 1,800
■計算例
送付先:東京へ M商品:5個 R商品:7個 を送付する場合、
A箱、B箱の複数箱に入れて送付する。
この場合、上記の表から、
M商品:5個=A箱(2,000)×1箱+B箱(2,500)×2箱=7,000
R商品:7個=A箱(2,000)×1箱+B箱(2,500)×1箱+C箱(1,800)×1箱=6,300
送料合計=M商品:5個+R商品:7個=13,300
※上記のように、各商品で送付する個数によって、箱に入れることができる数が変わるため、
利用する箱も替えることで送料も変わってきます。
上記のような計算をエクセルで行う方法を教えてください。
初心者のため、わかりやすく解説いただけますとありがたいです。
よろしくお願いします。
< 使用 Excel:Excel2016、使用 OS:Windows7 >
1.ご提示の料金表類は実データですか? それとも、あくまでサンプル程度のものですか?
※こういうものは、実データを出して貰わないと解決の道を迷走することになります。
>R商品:7個=A箱(2,000)×1箱+B箱(2,500)×1箱+C箱(1,800)×1箱=6,300
2.安くするということは、こんな風にして、1個分スペースが余ってもいいという考え方ですか?
↓
R商品:7個=B箱(2,500)×2箱 =5,000
> | A箱(M商品1個入り | B箱(M商品2個入り | C箱(R商品1個
> | /R商品2個入り) | /R商品4個入り) | 入り)
3.何故この表は、一番大きな箱が真ん中にあるんですか?
普通は大きい順か、小さい順に並べるものですけど・・・(なんか落ち着かないんですけど)
4.MはM、RはRで別々に詰めるんですか?
それとも、B箱にM商品1個とR商品2個詰めたり出来るんですか?
(半平太) 2016/08/19(金) 19:19
あちらはマルチポスト禁止ですので、あちらは閉じるようにしてください。 (マルチネス) 2016/08/19(金) 19:46
>1.ご提示の料金表類は実データですか? それとも、あくまでサンプル程度のものですか?
> ※こういうものは、実データを出して貰わないと解決の道を迷走することになります。
サンプル程度のもにになります。
ご指摘ありがとうございます。
>>R商品:7個=A箱(2,000)×1箱+B箱(2,500)×1箱+C箱(1,800)×1箱=6,300
>2.安くするということは、こんな風にして、1個分スペースが余ってもいいという考え方ですか?
> ↓
> R商品:7個=B箱(2,500)×2箱 =5,000
スペースなしということでお願いします。
>> | A箱(M商品1個入り | B箱(M商品2個入り | C箱(R商品1個
>> | /R商品2個入り) | /R商品4個入り) | 入り)
>3.何故この表は、一番大きな箱が真ん中にあるんですか?
> 普通は大きい順か、小さい順に並べるものですけど・・・(なんか落ち着かないんですけど)
理由はないです。C箱のことが後づけになっただけです。すみません。
>4.MはM、RはRで別々に詰めるんですか?
>それとも、B箱にM商品1個とR商品2個詰めたり出来るんですか?
M商品とR商品一緒に詰めることもできますので、
B箱にM商品1個とR商品2個詰めたりできます。
実データに近いものを出しますので、よろしくお願いします。
| A箱(M商品1個| B箱(R商品1個 | C箱(M商品1個入り | D箱(M商品2個入り
| 入り) | 入り) | /R商品2個入り) | /R商品4個入り)
■計算例
送付先:東京へ M商品:5個 R商品:7個 を送付する場合、
A箱、B箱の複数箱に入れて送付する。
※A箱、B箱では、それぞれの商品を1個ずつしか入らない。
M商品:5個(1個ずつ入る箱)=A箱(1,800)×5箱= 9,000
R商品:7個(1個ずつ入る箱)=B箱(1,800)×7箱=12,600
------------------------------------------------------
送料合計(1個ずつ)=M商品:5個+R商品:7個 =21,600
上記のように1個ずつ入る箱に入れるよりも、2個入りや4個入りの箱を利用した方が
送料が安くなります。
2個入りの箱や4個入りの箱を利用した場合、
M商品:5個=C箱(2,000)×1箱+D箱(2,500)×2箱=7,000
R商品:7個=B箱(1,800)×1箱+C箱(2,000)×1箱+D箱(2,500)×1箱=6,300
---------------------------------------------------------
送料合計(複数個入る)=M商品:5個+R商品:7個 =13,300
上記のように、1個しか入らない箱を利用するよりも、複数個入る箱を利用する方が
送料は安くすみます。
このように安くするときの送料を計算したいです。
■M商品の場合
M商品:5個(1個ずつ入る箱)=A箱(1,800)×5箱 =9,000
M商品:5個=C箱(2,000)×1箱+D箱(2,500)×2箱=7,000
-------------------------------------------------------
割安(差し引き) 2,000
※1個入りの箱を利用するよりも2,000円割安になります。
■R商品の場合
R商品:7個(1個ずつ入る箱)=B箱(1,800)×7箱 =12,600
R商品:7個=B箱(1,800)×1箱+C箱(2,000)×1箱+D箱(2,500)×1箱= 6,300
---------------------------------------------------------------------------
割安(差し引き) 6,300
最終的には、1個入りの箱に対する割安送料(複数個入る箱を利用)を算出する方法をご教授ください。
よろしくお願いします。
(jaga) 2016/08/19(金) 22:43
表がリーズナブルじゃないように思えます。
M商品を1個処理する時、C箱を選ぶはずないですよね?(A箱の方が常に安いのですから)
なのに何故「C箱(M商品1個入り/R商品2個入り)」などとするのですか? 「C箱 (R商品2個入り)」で十分じゃないですか?
A箱の方が常に安いと言うのは、単にサンプルだからですか? それとも、将来、C箱の方が安くなる時が到来することも想定したいと、言うことですか?
(半平太) 2016/08/20(土) 07:05
>A箱の方が常に安いと言うのは、単にサンプルだからですか?
>それとも、将来、C箱の方が安くなる時が到来することも想定したいと、言うことですか?
すみません。A箱の方が安いのはサンプルのためです。
実際には、A箱とC箱は同じものです。
(サンプルを作るため、金額部分を直せていませんでした。)
また、「C箱(M商品1個入り/R商品2個入り)」というのは、
R商品は、M商品の1/2の大きさのため、M商品が1個入る箱には、R商品が2個入るという考えです。
D箱についても同じこと(R商品は、M商品の1/2の大きさ)が言えます。
※B箱は、R商品1個入ります。C箱では、R商品2個入ります。C箱の方がR商品が倍入りますが、
金額はどちらも同じです。なるべくC箱に入れた方が安くできるのでそうしたいです。
わかりにくいため、表も直しますので、何度もすみませんが、よろしくお願いします。
(A箱を削除し、B箱の金額を変更しています。)
| B箱(R商品1個 | C箱(M商品1個入り | D箱(M商品2個入り
| 入り) | /R商品2個入り) | /R商品4個入り)
■計算例
送付先:東京へ M商品:5個 R商品:7個 を送付する場合、
1個ずつ入る箱を利用すると、
M商品:5個(1個ずつ入る箱)=C箱(2,000)×5箱 =10,000
R商品:7個(1個ずつ入る箱)=B箱(2,000)×7箱 =14,000
---------------------------------------------------------------------------
送料合計(1個ずつ)=M商品:5個+R商品:7個 =24,000
上記のように1個ずつ入る箱に入れるよりも、2個入りや4個入りの箱を利用した方が
送料が安くなります。
2個入りの箱や4個入りの箱を利用した場合、
M商品:5個=C箱(2,000)×1箱+D箱(2,500)×2箱 = 7,000
R商品:7個=B箱(2,000)×1箱+C箱(2,000)×1箱+D箱(2,500)×1箱= 6,500
---------------------------------------------------------------------------
送料合計(複数個入る)=M商品:5個+R商品:7個 =13,500
上でも書きましたが、B箱は、R商品1個入ります。C箱では、R商品2個入ります。
C箱の方がR商品が倍入りますが、金額はどちらも同じです。
なるべくC箱に入れた方が安くできるのでそうしたいです。
(D箱も同じようなことです。)
よろしくお願いします。
(jaga) 2016/08/20(土) 09:12
頭が混乱するので、 (1) B箱、C箱、D箱 → それぞれ、小箱、中箱、大箱 (2) R商品 → S商品 とします。
M商品はサイズMなのでそのままです。
1.まず、Mが1個とSが2個の同梱を作るかどうか決めます。 Mの個数が1個半端で、かつSの個数を4で割った余りが2個になる時は同梱となります。
(1) J6セル =(MOD(D6,2)=1)*(MOD(D7,4)=2)
2.あとは、出来るだけ大きい方の箱を使うことにします。 下図に従って、所要データを埋めた後、下式を入力してください。
※いちいち手入力するのが面倒な場合は、後述のマクロを利用すると簡単です。
(1) D5セル =MATCH(B5,L:L,0) (2) E5セル =INDEX(O:O,$D5) (3) F5セル =INDEX(N:N,$D5) (4) G5セル =INDEX(M:M,$D5) (5) E6セル =INT((D6-J6)/E$2)+J6 (6) F6セル =$D6-$E$2*E6+J6 (7) I6セル =SUMPRODUCT(E$5:G$5,E6:G6) (8) I7セル =SUMPRODUCT(E$5:G$5,E7:G7) (9) E7セル =INT((D7-J6*2)/E$3) (10) F7セル =INT(($D7-E$3*E7)/F$3)-J6 (11) G7セル =D7-SUMPRODUCT(E7:F7,E$3:F$3)-J6*2 (12) I8セル =SUM(I6:I7)
<結果図> 行 ___A___ __B__ __C__ __D__ ____E____ ___F___ ___G___ __H__ __I__ ______________J______________ _K_ ___L___ _____M_____ ________N________ ________O________ 1 (M/S) 大(2/4) 中(1/2) 小(0/1) 小箱(S1個) 中箱(M1個/S2個) 大箱(M2個/S4個) 2 M 2 1 0 北海道 3,000 3,000 3,500 3 S 4 2 1 青森 1,700 1,700 2,200 4 1は「M1/S2の同梱→大箱使用」 宮城 1,700 1,700 2,200 5 送付先 東京 TBL行 5 2,500 2,000 2,000 東京 2,000 2,000 2,500 6 M 5 3 0 小計 7,500 1 茨城 1,700 1,700 2,200 7 S 2 0 0 0 小計 0 長野 1,600 1,600 2,400 8 合計 7,500 石川 1,700 1,700 2,300 9 愛知 1,700 1,700 2,300 10 大阪 1,000 1,000 1,500 11 奈良 1,000 1,000 1,500 12 広島 700 700 1,000 13 愛媛 1,000 1,000 1,500 14 福岡 1,500 1,500 2,000 15 宮崎 2,000 2,000 2,500
<数式およびサンプルデータの転記が面倒と感じる場合> 全部手作業でやる場合は、ここから下は読む必要ありません。
後記マクロコードを新規シートの「シートモジュール(※)」に貼り付けたあと、 F5キーを押下してください(マクロ「onlyOnce」が実行されます)
すると自動的にデータと数式が入力されます。 実行は1回だけですので、終わったらマクロコードは削除して下さい
※「シートモジュール」の表示方法が分からない場合 新規シートの「シート見出し」を右クリックして、「コードの表示」を選ぶと 画面中央に白いエリアが表れますので、そこにマクロコードをコピぺしてF5キーを押下してください。
’貼り付けるマクロ Private Sub onlyOnce() Rem 生データのセルをまとめて処理 Range("D1").Value = "(M/S)" Range("E1").Value = "大(2/4)" Range("F1").Value = "中(1/2)" Range("G1").Value = "小(0/1)" Range("M1").Value = "小箱(S1個)" Range("N1").Value = "中箱(M1個/S2個)" Range("O1").Value = "大箱(M2個/S4個)" Range("D2,C6").Value = "M" Range("E2,F3,D7").Value = 2 Range("F2,G3").Value = 1 Range("G2").Value = 0 Range("L2").Value = "北海道" Range("M2:N2").Value = 3000 Range("O2").Value = 3500 Range("D3,C7").Value = "S" Range("E3").Value = 4 Range("L3").Value = "青森" Range("M3:N4,M6:N6,M8:N9").Value = 1700 Range("O3:O4,O6").Value = 2200 Range("J4").Value = "1は「M1/S2の同梱→大箱使用」" Range("L4").Value = "宮城" Range("A5").Value = "送付先" Range("B5,L5").Value = "東京" Range("C5").Value = "TBL行" Range("M5:N5,O14,M15:N15").Value = 2000 Range("O5,O15").Value = 2500 Range("D6").Value = 5 Range("H6:H7").Value = "小計" Range("L6").Value = "茨城" Range("L7").Value = "長野" Range("M7:N7").Value = 1600 Range("O7").Value = 2400 Range("H8").Value = "合計" Range("L8").Value = "石川" Range("O8:O9").Value = 2300 Range("L9").Value = "愛知" Range("L10").Value = "大阪" Range("M10:N11,O12,M13:N13").Value = 1000 Range("O10:O11,O13,M14:N14").Value = 1500 Range("L11").Value = "奈良" Range("L12").Value = "広島" Range("M12:N12").Value = 700 Range("L13").Value = "愛媛" Range("L14").Value = "福岡" Range("L15").Value = "宮崎"
Rem 数式セルをまとめて処理 Range("D5").FormulaR1C1Local = "=MATCH(RC[-2],C[8],0)" Range("E5").FormulaR1C1Local = "=INDEX(C[10],RC4)" Range("F5").FormulaR1C1Local = "=INDEX(C[8],RC4)" Range("G5").FormulaR1C1Local = "=INDEX(C[6],RC4)" Range("E6").FormulaR1C1Local = "=INT((RC[-1]-RC[5])/R2C)+RC[5]" Range("F6").FormulaR1C1Local = "=RC4-R2C5*RC[-1]+RC[4]" Range("I6:I7").FormulaR1C1Local = "=SUMPRODUCT(R5C[-4]:R5C[-2],RC[-4]:RC[-2])" Range("J6").FormulaR1C1Local = "=(MOD(RC[-6],2)=1)*(MOD(R[1]C[-6],4)=2)" Range("E7").FormulaR1C1Local = "=INT((RC[-1]-R[-1]C[5]*2)/R3C)" Range("F7").FormulaR1C1Local = "=INT((RC4-R3C[-1]*RC[-1])/R3C)-R[-1]C[4]" Range("G7").FormulaR1C1Local = "=RC[-3]-SUMPRODUCT(RC[-2]:RC[-1],R3C[-2]:R3C[-1])-R[-1]C[3]*2" Range("I8").FormulaR1C1Local = "=SUM(R[-2]C:R[-1]C)"
Rem 標準外書式セルをまとめて処理 Range("E1:G1,H4,J4,E5:G5,F6:G7,E9:G9,F10:G11,E13:G13,F14:G15").NumberFormatLocal = "#,##0;[赤]-#,##0" Range("M2:O11,I3:I15,O12,M13:O15").NumberFormatLocal = "#,##0"
Rem 塗りつぶしセルをまとめて処理 Range("D5:G5,E6:F6,I6:J6,E7:G7,I7:I8").Interior.ColorIndex = 40 End Sub
(半平太) 2016/08/20(土) 11:52
(jaga) 2016/08/20(土) 19:11
大変恐縮ですが、さらに以下の点をご教授いただけないでしょうか。
■例、東京に送付する場合(東京以外でも同様)
(以下の≪例2≫を≪例1≫のように計算したいです。)
≪例1≫
大(2/4) 中(1/2) 小(0/1) 1は「M1/S2の同梱→大箱使用」
2500 2000 2000
M商品(5個) 3 0 0 小計 7500 1
S商品(2個) 0 0 0 小計 0
合計 7500
≪例2≫
大(2/4) 中(1/2) 小(0/1) 1は「M1/S2の同梱→大箱使用」
2500 2000 2000
M商品(5個) 2 1 0 小計 7000 0
S商品(3個) 0 1 1 小計 4000
合計 11000
※≪例2≫では、S商品が3個の場合、
”1は「M1/S2の同梱→大箱使用」”として計算がされていないようです。
S商品が3個の場合でも、2個の場合と同じように、”1は「M1/S2の同梱→大箱使用」”を
使用した方が安くなりますので、以下のような計算はできないでしょうか?
大(2/4) 中(1/2) 小(0/1) 1は「M1/S2の同梱→大箱使用」
2500 2000 2000
M商品(5個) 3 0 0 小計 7500 1
S商品(3個) 0 0 1 小計 2000
合計 9500
何度もお手数ですが、よろしくお願いします。
(jaga) 2016/08/21(日) 12:18
> S商品が3個の場合でも、2個の場合と同じように、”1は「M1/S2の同梱→大箱使用」”を > 使用した方が安くなりますので、以下のような計算はできないでしょうか?
それはルールとして出来ないハズです。
以前、このような回答がありました。 ↓ >>>R商品:7個=A箱(2,000)×1箱+B箱(2,500)×1箱+C箱(1,800)×1箱=6,300 >>2.安くするということは、こんな風にして、1個分スペースが余ってもいいという考え方ですか? >> ↓ >> R商品:7個=B箱(2,500)×2箱 =5,000 >スペースなしということでお願いします。
(半平太) 2016/08/21(日) 13:12
確かに、
>>>R商品:7個=A箱(2,000)×1箱+B箱(2,500)×1箱+C箱(1,800)×1箱=6,300
>>2.安くするということは、こんな風にして、1個分スペースが余ってもいいという考え方ですか?
>> ↓
>> R商品:7個=B箱(2,500)×2箱 =5,000
の場合、箱の中に1個分のスペースが入りますので、そのスペースは入らないようにお願いしました。
ただ、M商品:5個、S商品:3個ではスペースはできないと思います。
(大(2/4)には、M商品は2個入ります。S商品は4個入ります。M商品とS商品を組み合わせて入れる場合、
M商品:1個とS商品:2個でも大(2/4)箱に入ると思います。
実際に、≪例1≫のように計算すると、このようになります。
M商品:1個とS商品:3個でも、大(2/4)箱に1個とS商品:2個入ります。S商品:1個分は残りますので、
小(0/1)に入れることができて、大(2/4)箱にはスペースはできないと思いますが、無理でしょうか?
何度もお手数ですが、ご教授いただけますでしょうか。
よろしくお願いします。
(jaga) 2016/08/21(日) 14:55
≪例1≫と、≪例2≫では、商品の数が違っていたんですねぇ。ちょっと勘違いしてしまいました。m(__)m
> J6セル =(MOD(D6,2)=1)*(MOD(D7,4)=2)
↓不等号(左大なり)を「=」の前に挿入してください
J6セル =(MOD(D6,2)=1)*(MOD(D7,4)>=2)
(半平太) 2016/08/21(日) 16:00
(jaga) 2016/08/21(日) 16:33
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.