[[20160819142035]] 『エクセルで送料の計算をしたい』(jaga) ページの最後に飛ぶ

[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]

 

『エクセルで送料の計算をしたい』(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個入り)

北海道| 2,800     | 2,800     | 3,000        | 3,500
青森 | 1,500    | 1,500     | 1,700        | 2,200
宮城 | 1,500    | 1,500     | 1,700        | 2,200
東京 | 1,800     | 1,800     | 2,000        | 2,500
茨城 | 1,500    | 1,500     | 1,700        | 2,200
長野 | 1,400    | 1,400     | 1,600        | 2,400
石川 | 1,300    | 1,300     | 1,700        | 2,300
愛知 | 1,300    | 1,300     | 1,700        | 2,300
大阪 |  800     |  800     | 1,000        | 1,500
奈良 |  800    |  800     | 1,000        | 1,500
広島 |  500     |  500     |  700        | 1,000
愛媛 |  800     |  800     | 1,000        | 1,500
福岡 | 1,300     | 1,300     | 1,500        | 2,000
宮崎 | 1,800    | 1,800     | 2,000        | 2,500

■計算例
 送付先:東京へ 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


マルチネスさん、ご指摘ありがとうございます。
Excel Q&Aサロンについて削除しようとしたのですが、エラーになり削除ができなかったため、取り急ぎExcel Q&Aサロンは解決にしました。Excel Q&Aサロンのコメントでも削除方法がわからないと書きました。
素人ですみませんが、削除方法を教えていただければ幸いです。
(jaga) 2016/08/20(土) 09:07

半平太さん、ありがとうございます。

>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個入り)


北海道| 3,000     | 3,000        | 3,500
青森 | 1,700     | 1,700        | 2,200
宮城 | 1,700     | 1,700        | 2,200
東京 | 2,000     | 2,000        | 2,500
茨城 | 1,700     | 1,700        | 2,200
長野 | 1,600     | 1,600        | 2,400
石川 | 1,700     | 1,700        | 2,300
愛知 | 1,700     | 1,700        | 2,300
大阪 | 1,000     | 1,000        | 1,500
奈良 | 1,000     | 1,000        | 1,500
広島 |  700     |  700        | 1,000
愛媛 | 1,000     | 1,000        | 1,500
福岡 | 1,500     | 1,500        | 2,000
宮崎 | 2,000     | 2,000        | 2,500

■計算例
 送付先:東京へ 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.