[[20190903205445]]　『ソルバーアドインの使い方について。（線形計画法』（がっきー）　ページの最後に飛ぶ

『ソルバーアドインの使い方について。（線形計画法）』（がっきー）

初めまして。質問させて下さい。

以下の問題をエクセルに解いてもらうことは出来るでしょうか？

「ソルバー」というアドインがキーっぽく感じているのですがどうにもこうにも分かりません。

 賢人方のお知恵をお貸し下さい。

 ＜問題＞
ーーーーーーーーー
小麦粉を３０ｇ～４００ｇまで使うお菓子がランダムにあります。
それを1日に何個も作ります

小麦粉の購入は３０ｇ単位39円 100g単位124円で購入しているとします。

 各お菓子は30ｇの小麦粉と100ｇの小麦粉のみでできあがるとして、
 小麦粉の余った分は廃棄します。

1日あたり、100グラム単位の小麦粉を何個、３０グラムの小麦粉を何個仕入れれば
1日の制作費は最も安くなるでしょうか？
ーーーーーーーーーーー
例）
○月☆日

 １ うさぎくっきー ３０ｍｇ
 ２ ライオンクッキー１１０ｍｇ
 ３ ぞうさんくっきー２１０ｍｇ
 ４ いぬくっきー ６０ｍｇ

 計410ｇ 
 必要な仕入れは１００ｇ×2袋＋３０ｇ×７袋 ←この計算をしたい

単純に割り算ではなく出来るだけ優先して100ｇを買いたいです（安いので）
また、あまりは出来るだけ少ないほうが安くできます。
たとえば１１０ｇなら
１００ｇ入りと３０ｇ入りを買って
２０ｇ余らすより
３０ｇ入り４つ買った方が安いです

＜使用 Excel：Excel2013、使用 OS：Windows10 ＞

 ＞必要な仕入れは１００ｇ×2袋＋３０ｇ×７袋 ←この計算をしたい

１００ｇ×4袋＋３０ｇ×1袋のほうが安くないですか

（マナ） 2019/09/03(火) 21:06

失礼しました。
ソルバーで試してみたら、１００ｇ×2袋＋３０ｇ×７袋になりました。

（マナ） 2019/09/03(火) 21:15

わたしは、こんな感じで実行しました。

どこか適当なセルに、個数から、金額と重量を求める数式を入力

目的セル：金額
目標値：最小値を選択
変数セルの変更：個数
制約条件：個数が整数であること、重量が410ｇ以上であること

（マナ） 2019/09/03(火) 22:17

使い方は、こことか
http://mt-soft.sakura.ne.jp/kyozai/excel_mid/160_simulation/20_solver_1.htm

（マナ） 2019/09/03(火) 22:21

○月☆日のデータが何百件とある場合、ソルバーの計算はオートフィルのように
まとめて行うことはできないでしょうか？
（がっきー） 2019/09/03(火) 23:40

１件はできたのですか。

＞オートフィルのように

できないと思います。
マクロが使えるならトライしてみてはどうでしょうか。

（マナ） 2019/09/04(水) 00:08

１度投稿しましたが、取り下げます。

（マナ） 2019/09/04(水) 19:24

↑は、Dictinaryのほうが簡単だったので書き直しました。

マクロ案です。提示された例に限定で、汎用性はありません。
もしかして普通に数式でもできるかもと、書いてて思いましたが
わたしには無理なのでマクロ使用します。
ソルバーのマクロは興味がないので、ユーザー定義関数にしました。

 使い方は
 １）B3:C3を選んで　=配分($A3,$B$1,$B$2,$C$1,$C$2)
 ２）ctrl + shift + enter
 ３）下方向にフィルコピー

	-A-	-B-	-C-
 1	サイズ	100	30
 2	価格	124	39
 3	430	4	1
 4	410	2	7
 5	160	1	2
 6	20	0	1
 7	80	0	3
 8	100	1	0
 9	120	0	4

 Option Explicit

 Function 配分(必要量 As Long, _
            サイズ1 As Long, 価格1 As Long, _
            サイズ2 As Long, 価格2 As Long)
    Dim dic As Object
    Dim n As Long, n2 As Long
    Dim v As Long, v2 As Long

    If サイズ1 <= サイズ2 Then Exit Function

    Set dic = CreateObject("scripting.dictionary")

    Do
        dic(サイズ1) = dic(サイズ1) + 1
        If dic(サイズ1) * サイズ1 >= 必要量 Then Exit Do
    Loop

    n = dic(サイズ1)
    v = dic(サイズ1) * 価格1

    Do
        If dic(サイズ1) > 0 Then dic(サイズ1) = dic(サイズ1) - 1
        Do
             dic(サイズ2) = dic(サイズ2) + 1

            If dic(サイズ1) * サイズ1 + dic(サイズ2) * サイズ2 >= 必要量 Then Exit Do
        Loop

        v2 = dic(サイズ1) * 価格1 + dic(サイズ2) * 価格2

        If v2 >= v Then Exit Do
        v = v2
        n = dic(サイズ1)
        n2 = dic(サイズ2)
    Loop

    配分 = Array(n, n2)

 End Function

（マナ） 2019/09/04(水) 19:56

 横から済みません。m(__)m

 問題がかなり単純化されているような気がするので、一つ教えてください。

 実際、小麦の袋は何種類あるんでしょうか？

（半平太） 2019/09/04(水) 20:20

コメント返信：

[ 一覧(最新更新順) ]

[[20190903205445]] 『ソルバーアドインの使い方について。（線形計画法』（がっきー） ページの最後に飛ぶ

[[20190903205445]]　『ソルバーアドインの使い方について。（線形計画法』（がっきー）　ページの最後に飛ぶ