エクセルで0もしくは0に近い負の値を計算させる方法

画像のような表で
単価×数量＝累計の合計が
基準値に近くなるよう、数量を自動計算させたいのです。
数量が減るのは問題ないですが、増えるのはダメです。
また、3行目、5行目は0.5単位で調整できますが、ほかは整数のみです。

知恵をお貸しくださいm(_ _)m

質問者からの補足コメント

• 回答ありがとうございます
  画像の数値でいくと
  -17550なので単価が17500の数量をマイナス1し、差が-50で終わりです
  
  なので、数量は
  99→99
  72→72
  3→2
  1.5→1.5
  1→1
  
  になります

  No.2の回答に寄せられた補足コメントです。 補足日時：2021/08/01 15:48

• すみません、別に数量はどうあれ、0に近ければそれでいいので、私が出した-50が正解というわけではないです。
  単純に計算して-50だったのでそう言いました。
  
  人間で計算するよりも、Excelにさせたほうが0に近い数字を出してくれるんじゃないか？という考えで質問しています。

  No.3の回答に寄せられた補足コメントです。 補足日時：2021/08/02 18:34

• 回答ありがとうございます
  -50が最適というのは単純に単価に17,500円があり、基準値との差が-17,550なので、単価17,500の数量を減らしただけです。
  
  これ以外に０に近い数字に出来るならば、それが最適になります。
  
  人間の手作業ではパッと見-50が最適となりますが、エクセルで計算させたらその最適が覆るんじゃないかと思って質問しています。

  No.4の回答に寄せられた補足コメントです。 補足日時：2021/08/02 18:38

No.8 ベストアンサー 回答者： goomania 回答日時： 2021/08/03 23:31

大変失礼ではありますが、

No.5さんの回答で「エクセルのソルバーを使えば簡単にできます」は
そのとおりだと思うのですが、条件の設定が間違っている気がします。
ご質問者さんは
>数量が減るのは問題ないですが、増えるのはダメです。
といっているので、
14000は99以下
2300は72以下
17500は3以下
2900は1.5以下
9100は1以下
という制限があると解釈しています。
従って、No.5さんの回答では
17500、2900および9100で制限をオーバーしてしまいます。
また、No.7さんの回答では
9100で制限をオーバーしてしまいます。

ソルバーの有効化はNo.6さんが解説しているので割愛しますが、添付画像のような表を作成してソルバー機能で「解決」ボタンを押せば回答が得られます。
添付画像①をご覧ください。
ご質問者のお示しになった表に登場しないのはB列の「仮想数量」です。
ソルバーでは「整数」という制限条件を付けられますが、私の知る限り、0.5刻みの条件が付けられません。そこで、B列に「仮想数量」を配置し、これを変数とします。
C列では3列目と5列目以外は「=B2」のようにB列の値をそのまま転記する関数を入れ、C3とC5には「=B3/2」のように、B列の値を2分の1とする関数を入れます。こうすることでB列の変数を整数条件にすれば、自動的に3列目と5列目は0.5刻みになるというわけです。
D列は単価×数量の計算式を、D7に合計の計算式を入れてあります。E5に基準値を入力してあります。また、E7には「=E5-D7」を入れてあります。
次にソルバーの条件設定ですが、添付画像➁をご覧ください。
>数量が減るのは問題ないですが、増えるのはダメです。
ということなので、それぞれの上限値を設定していますが、0.5刻みも可能にするために「仮想数量」を設定していますので、3行目と5行目はご質問者が提示した数量の2倍を上限値とし、0以上の整数条件を設定します。
さらに、合計値であるD7は基準値以上でなくてはいけません。
目的セルの設定では基準値との合計を計算している「$D$7」セルを指定し、目標値は「最小値」を選択します。
変数セルの変更欄に「$B$2:$B$6」を入れます。
整数解を求める問題なので、「制約の無い変数を非負数とする」にはチェックしておきます。
解決方法の選択では「シンプレックスLP」を選択し、解決ボタンを押します。
すると添付画像③のようになり、C列の「数量」に最適解が表示されます。

この回答へのお礼

回答ありがとうございました！
まさにこのソルバーで私がやりたかったことが解決しました！
他の方も知恵を貸して頂いたのですが、ご指摘の通り、数量が増えるのはダメの条件で、0.5の対策まで回答いただけたのでベストアンサーに致します。

お礼日時：2021/08/04 21:24

No.7 回答者： トモクンアヤチャン 回答日時： 2021/08/03 18:55

ソルバーの制約条件はB2:B6＝整数（int）として解決したところ、

単価・・・・・数量・・・・累計
14000・・・89・・・1246000
2300・・・・55・・・126500
17500・・・・0・・・・・0
2900・・・・・0・・・・・0・・・基準値
9100・・・・・25・・・227500・1600000
・・・・・合計・・・1600000・・・・0
でした。正解はいろいろあるようです。
0.5刻みにするには、単価を半分にすればいいと思います（数量は半分にする）

No.6 回答者： mike_g 回答日時： 2021/08/02 21:53

この場を拝借して、

googoo900さんにお尋ねいたします。

私は Excel 2019 を使用しています。
[ファイル]⇒[その他…]⇒[アドイン]⇒[管理]ボックスで“Excelアドイン”を選択⇒[設定]⇒[有効なアドイン]窓で“ソルバーアドイン”にチェック入れ⇒[OK]
の操作で、「ソルバーアドインを有効化」できました。

そこで…
[データ]⇒[分析 <ソルバー>]で、添付図に示す[ソルバーのパラメーター]設定メニューが表示されました。
左下の[ヘルプ]を眺めましたが、私の読解力が貧弱なため理解できません。とにかく、…
１．[目的セルの設定]ボックスにマウスカーソルを放り込んで、式
￣￣=D6-C7 を入力されて、現在 -17,550 が表示されているセル D7
￣￣をクリックした結果、「$D$7」が表示されました。
￣￣ちなみに、この時点では範囲 B2:B6 には上から、それぞれ数値
￣￣99、72、3、1.5、1 が入力されていました。
２．[目標値]右側の選択肢群の一つ“指定値”に目玉入れ(この時点で
￣￣右端ボックス内は 0 が表示されていました)
３．[変更セルの変更]ボックス内にマウスカーソルを放り込んで、
￣￣範囲 B1:B6 を撫で撫でした結果、「$B$2:$B$6」が表示され
￣￣ました。

此処まで実行しましたが、この先はどう進めばよろしいのでしょうか？

No.5 回答者： googoo900 回答日時： 2021/08/02 20:10

エクセルのソルバーを使えば簡単にできます。

メニュー ⇒ データ ⇒ ソルバー　とします。
目的セル、目標値、変化させるセルを入力します。
制約条件を設定します。(整数など)
実行すれば自動的に解が求められます。

メニューにソルバーが表示されていなければ、オプション ⇒ アドイン　でソルバーアドインを有効化しておく必要があります。

ちなみに、差はゼロで値は下記でした。
14000>>>37
2300>>>23
17500>>>45
2900>>>8
9100>>>24

No.4 回答者： goomania 回答日時： 2021/08/02 18:28

ご質問者は

>エクセルで0もしくは0に近い負の値
とおっしゃってしますので、基準値との差が「0」でもよいということになります。
すると

99→99
72→65
3→3
1.5→1
1→1

という回答が基準値との差が「0」になるので、ご質問者の求める最適解ということになると思うのですが、ご質問者は

99→99
72→72
3→2
1.5→1.5
1→1

で基準値との差が「-50」が最適解だとおっしゃっています。
その理由をご説明いただかないと、回答のしようがありません。

No.3 回答者： mike_g 回答日時： 2021/08/02 15:37

[No2補足]へのコメント、

_99→_99
_72→_72
＿3→＿_2
1.5→_1.5
＿1→＿_1
が正解でしたかぁ、私の予想は、愚直な試行錯誤の結果の
_99→＿99
_72→70.5
＿3→＿_3
1.5→_1.5
＿1→＿_1
でしたが、見事に(?)外れ！
つまり、私には手に負えないことが判明してスッキリポッキリしました。

此処はヒトツ、Excelで「線形計画法と言う手法を使う」と宣われた方の懇切丁寧なご指導を仰ぎたいですねェ、

t_fumiakiさん、どうか、よろしく。m(_._)m

No.2 回答者： mike_g 回答日時： 2021/08/01 15:03

「線形計画法」なんてな小難しい理論はさておき、

貴方自身は、当然のことながら、正解値(例えば、下記)
をご存じですよね？我々の検証・検算のために、ソレを教えておいてくださいナ！
　 数量
98　→ 98
72　→ 70.5
3　→　3
1.5→　1.5
　1 →　1

No.1 回答者： t_fumiaki 回答日時： 2021/08/01 10:15

線形計画法と言う手法を使う。

やり方は検索する様に。