- ベストアンサー
自動計算式：複数の条件を満たす数値の計算方法
- 商品発注数の自動計算式に関する質問です。A列には一か月分の商品の発注や在庫に関する情報が入っています。商品は6種類あり、各商品の発注数は合計で504になる必要があり、発注単位は6です。
- また、各商品の3ヶ月先までの在庫保有率を示す指数があり、出来るだけ1.00に近づけるのが目標です。この指数は発注数を入力すると自動的に計算され、最低でも0.70は必要です。
- 上記の条件を満たすための自動計算式を関数で作成したいです。
- d_heim
- お礼率33% (1/3)
- その他MS Office製品
- 回答数3
- ありがとう数1
- みんなの回答 （3）
- 専門家の回答
質問者が選んだベストアンサー
Excel2002でソルバーを使うなら以下のようにするのが設定が簡単です。 A2:A7セルを変化するセルとし（初めは空白セルで良い）、セルの値のB2セルに「=A2*6」と入力し、その6倍の数字をB列に表示させます（この値がそれぞれA12、A20、A28、A36、A44、A52になります）。 その下のB8セルに「=SUM(B2:B7)」と入力して合計を表示しておきます。 比率のC2セルには「=A2/(分子の計算式)」の数字を入力します（添付画像ではD2セルに表示させていますので「=A2/D2」の数式を入力していますが、このD列は必要ありません）。 このように設定しておいて（かつアドインでソルバーを使えるようにしておいて）「ツール」「ソルバー」で目的セルを「B8」、変化させるセルを「A2:A7」、制約条件を「追加」しA2:A7の範囲を「区間」で「整数」にします。 さらに「追加」でC2:C7の範囲を「>=」で「0.7」として「実行」してください。 これですべての条件が合致する解が得られます。 他の解を求めたいなら、A列の初期値を適宜変更して、同じソルバーを再度実行してみてください。
その他の回答 (2)
- MackyNo1
- ベストアンサー率53% (1521/2850)
ご希望の条件を満たす数値は、何種類もある可能性がありますので（あるいは全くない場合もあります）、基本的に関数で求めることはできません。 このようなケースで条件に合致する１つの解答例を求めるには、ソルバーの機能を利用します。 エクセルのバージョンが記載されていませんので具体的な説明はしませんが、例えば比率が0.7以上という条件は、簡便な関数での制約条件を利用すると最低条件（０．７以上）を満たす基準となりますが（1に近いという条件にならない）、すべての条件が合致する1つの解答例を求めるだけで良いのでしょうか？
補足
ご回答ありがとうございます。言葉足らずで申し訳ありません。エクセルのバージョンは2002（自宅）と2007（職場）で、現在職場で使用するため2002を使用しながら上記の計算式を作ろうとしています。 0.70以上になれば、1.00に近いという条件は必ずしも満たす必要はなく、また全ての条件が合致する1種類の解答さえ求められれば、あとは手動で微調整しようと考えております。 ソルバーは別のサイトでも見かけたので、インストールしてみますね。
お礼
お礼が遅れてすみません。本日職場で問題の数式を上司に見せなければならなかったのですが、職場に着いた時点で投稿を拝読し、無事ご提案いただいた方法を職場のエクセルシートに当てはめて計算することができました！！何度も詳細にご説明いただき本当にありがとうございました。 本日、合計発注数は504だけでなくその倍数でもいいという説明を受けたので、今後504では解が見つからない場合、ソルバーの目標値を自動的に倍数（1008、1512など）にする方法をを模索したいと思います。 これまであまりエクセルで高度な計算をすることがなかったのですが、今後は自分自身でも本を買って将来的にはマクロを組むことを目標に勉強しようと思います。ありがとうございました。