最適化楽屋話#14 Excelを使った数理最適化

Numerical Optimizer 開発責任者の田辺隆人でございます。

私を含めて弊社数理最適化部隊がデモをするにつけ、受託プロジェクトなど行うにつけ、最もよくお世話になっていてなじみ深いインタフェース、それは Microsoft Excel です。なぜか。それは Excel の集計作業はそのまま最適化モデルの定義と同じ意味だから。集計結果をグラフ表示するだけで、結果の確認が一目瞭然だからです。じつは集計結果が望んだ値になるように Excel のセルに入る値を調整することが実は数理最適化そのものとも言えます。

Excel に向かって調整できるのならば、別に人がやればで十分？いえいえ、この作業が結構骨が折れる話で、Excelシート1枚でも、かなり膨大な試行錯誤になるんです、と示すのがこのパズルめいたExcelシートです（Numerical Optimizer なんて使っとらんぞ、という方も普通の式が定義されたExcelブックで試行錯誤してみることはできますのでぜひダウンロードしてお試しあれ）。解説スライドもどうぞ。

緑のセルに数字（整数に限る、とします）を入れて、黄色のセルの値（最初は2.7％となっている）を小さくする、というのがお題です。緑セルが材料 X、Y、Z の配合量になっていて、これに従って混ぜ物を作る。出来た混ぜ物に含まれる成分 a、b、c の配合量を G7、G8、G9 にある所望の値になるよう調整します。結局混ぜ物の成分は各原材料の成分と配合量から計算できる。その計算が、Excel の数式として定義されていて H7、H8、H9 に出ます（SUMPRODUCT を使っただけ）。これが隣の列の「成分ターゲット」という値にどれだけ合っているかを示した誤差（引き算した値）がピンクのセル、ピンクのセルの絶対値の合計が黄色セルの値で、これをできるだけ小さくしたい、というわけです。

緑のセルに入れる値は何でもよいのですが、ただ、すべてを合計した値（配合量）が青いセルで示した下限と上限の間になるようにするのがルール。最初の状態だと成分a が足りないので、増やすためには材料Yが足りない。X を減らして Y を増やす、a が大体揃ったところで、今度は b を揃える、そうすると a が崩れるのでまた調整・・とかやってみます。ちょっと触っているとなんとなく動かす手が見えてくるのが人間のカンのすごいところ。私が出した答えは

でした。誤差0.5%くらいだからなかなかですよね。
でももっと良い値があるんじゃないかという疑いは晴れない。ただ、緑セルの数字の入れ方のパターンを全部試すほど暇じゃない。たった一枚のExcelシートに書かれた式なんですが、組み合わせ問題の難しさの深淵を見る思いであります。

さてさて、こういうときに頼るべきは数理最適化。あっという間に

という最適解（もう黄色のセルをこれ以上小さくできないことが保証された答え）が出ます。Numerical Optimizer のライセンスをお持ちの方はExcelアドインで、最適化モデル である blend.smp（要するに Excel の計算式を別の書き方で書いたもの）をダウンロードして接続すると実行できます。

ま、自分で出してたのも0.5％くらいだったら結構よい線だった！と思って安心するのか、これじゃだめだとあくまで最適を求めるのか、はたまた答えを出す手間暇を削減することに意義を見出すのか、数理最適化を使う方々の思惑は様々ですが、とにかく実務の世界における方法論の一つとして、最適化をご理解いただけますと幸いです。

弊社 Numerical Optimizer セミナーでは、弊社最適化ソリューションを上記のような具体例を通じてご説明しています。昨年から私もレギュラー講師を務めており、より実務的な例を取り上げつつ、最近の数理最適化案件の動向やいわゆる「AI」手法の中における位置づけなどについてお知らせしています。