Calcのソルバーでどこまでできるか実験

2023年1月29日2025年4月24日

現在のAmazon情報

[コールマン] シールド35 メンズレディースユニセックスリュックバックパックバッグ 35L 通勤通学部活ビジネス大容量軽量防水撥水。

35L入るので色々運べる。

15インチまでのノートパソコンが入り防水。

Amazon評価数1774件で5段階評価で4.4獲得（2025年5月20日時点）

参考価格: ￥12,980

只今セール中で-38%（2025年5月21日時点）

現在価格: ￥8,068円税込（2025年5月21日のAmazon価格）

Amazonで見る

はじめに

今回はまたソルバーネタ。

LibreOfficeのCalcにもソルバーがあるので、これがどのくらいの性能なのか検証してみようという回。

Calcは最近のアップデートでDEPSOなどの拡張機能でのソルバーがなくなってしまって、線形ソルバーと群知能非線形ソルバーの二つしか選べなくなりました。

線形ソルバーはまあいいとして、非線形ソルバーが試験的機能の群知能しかなくなってしまったので、これがどのくらいの性能なのか気になります。

非線形ソルバーで手軽なやつってあんまりないんですよね。

Excel持ってれば済む話なんですけど、持ってないならどうするかってね。

GoogleのOR-Toolsは非線形ソルバーないですし。

Calcでどこまでできるか、興味がある人はいるはず。

そう思って書いてます。

※2025/01/19時点でCalcのソルバーがかなり変更されたので後に追記します。DEPSOとSCOが復活しています。

問題

次のような問題を解きます。

酢酸6.0molとエタノール3.0mol、触媒として硫酸を少量混合し、25℃に保って振った。

平衡状態に達したとき、混合溶液中の酢酸エチルの物質量は何molか。

25℃におけるこの反応の平衡定数はK=4.0とし、反応中に液体の体積は変化しないものとする。

解：

この反応は次のように書ける。(入試レベルの完璧な書き方ではなく、概要をわかるように書いているだけですので注意)

CH₃COOH + C₂H₅OH ⇄ CH₃COOC₂H₅ + H₂O

反応前はそれぞれ、

6.0, 3.0, 0, 0 [mol]

平衡時はそれぞれ

6.0-x, 3.0-x, x, x [mol]

混合溶液中の酢酸エチルの物質量をx[mol]とする。

混合溶液の体積をVとすると、

K=([CH₃COOC₂H₅][H₂O])/([CH₃COOH][ C₂H₅OH])

=(x/V)²/(((6.0-x)/V)((3.0-x)/V))

=4.0

この式から

x≒9.5, 2.5 x<3より、x=2.5[mol]

Calcのソルバーで求める

次のように入力。

ソルバーの設定。

解決方法は群知能を選択。

結果。

オール0。

ありゃりゃ。

ちなみに群知能のオプションで差分進化でも粒子群でも結果はうまくいかず。

でもあきらめない。

中身は何らかの最小化を実施していると考えて、目標値を目的関数にするときの常套手段、絶対値に直して最小化に切り替える。

ソルバーの設定も最小化に切り替える。

結果。

2.53589827537089

が出ました！

複数変数でやってみる

以前GAをやった時と同じ問題を使ってみる。

次の問題の、-5<=x1,x2,x3<=5のときの最小値とそのときの変数の値を求めよ。

y=x1×x1+x2×x2+x3×x3

この最適解（最小の値を取るときの変数の値）は

x1,x2,x3=0 y=0 となる。

単純な放物線が三次元になっただけの問題。

シートに初期値を入力して

ソルバーの設定をして

おお！ちゃんと答えが出た。

もうちょっと負荷をかけてみる。

(x1-1.1)(x1-1.1)

としてみた。

これでも群知能の差分進化なら答えが出る。

残念ながら粒子群では答えは出なかった。

さらにこれを整数制約にしてみる。

するとx1=1が最適解になるはずだが、さあどうか。

結果は

うーん。

まだあきらめない。

オプションの非負数にチェックを入れる。

結果。

やった！答えになった。

追記【nlp(非線形最適化)ソルバーが使えるようになりました】

Calcのソルバーでnlpsolverがインストール時に自然とインストールされるようになったので、それについて追記します。2025年1月20日の情報です。

nlpsolverアドインを入れるために

普通に以下のコマンドで入るかと思ったんですが、複数のlibreofficeが混在するような環境だとその中のどれかに偶然入るような感じになってしまい使いづらいので、一度libreofficeをアンインストールしてから以下のコマンドを打つといいかもしれません。ただし、設定がまっさらになってしまうので、ご注意ください。自己責任になります。

以下Ubuntuでのやり方です。

まずまっさらにする方法。libreofficeのあとに「*」をつけてlibreoffice関係のすべてのアプリを消去します。

sudo apt remove libreoffice*

次にOSのアップデートがあるなら全部やります。じゃないと途中でlibreofficeのインストールが失敗します。

Ubuntuなら「ソフトウェアの更新」アイコンでアップデートを済ませてください。

次に以下のコマンドでインストールします。

sudo apt install libreoffice

これで自然とnlpsolverが入るはずです。日本語化もできています。

もし入らなかったときは以下のコマンドを打ちます。

sudo apt install libreoffice-nlpsolver

これで何かしらのlibreofficeにnlpsolverの拡張機能が入ります。

Calcの「ツール」→「拡張機能」を選択して上のように「非線型計画法向けソルバー」が入っていれば成功です。

ちなみにWindowsでは最新版を公式サイトからダウンロードしてインストールすると普通に非線形計画法向けソルバーがインストールされるようです。

「ツール」→「オプション」→「LibreOffice」→「詳細」→「Oracle Corporation」にチェックを入れてJavaを使えるようにしてからソルバーを使いましょう。

DEPSソルバーを中心に使う

DEPSモードはDE(Differential Evolution)とPS(Particle Swarm Optimization)の混合アルゴリズム、SCOモードはSCO(Social Cognitive Optimization)を利用することになります。

SCOは日本での使っている文献が少ない、DEのほうがPS(PSO)よりパラメータ設定が容易で最適解発見能力が高いという点から、以下の検証ではDEPSのDEとPSOの割合をすべてDEに割り振って、初期パラメータで検証を行います。

数値実験の結果をまとめると、DEのみが全ての試行で最適解を発見することができた。
〔……〕
これに対して、DEでは高次元の問題における性能の低下が見られたが、比較的ルーズに設定したパラメータでも十分な探索を行うことができた。
(1)伊藤稔, 田中雅博, 関数値最適化のためのParticle Swarm Optimization, Differential Evolution, 実数値遺伝的アルゴリズムの探索性能に関する検討, 甲南大学紀要. 理工学編 = Memoirs of Konan University. Science and engineering series 52 (1), 125-135, 2005

DEPSの設定

DEPSではすべてDEで探索させたいので「エージェントスイッチレート」を「1.0」つまり100%にして完全にDEだけにします。それ以外はすべてデフォルトのパラメータを使います。

今回の記事の問題を解いてみた

「酢酸6.0molとエタノール3.0mol、触媒として硫酸を少量混合し、25℃に保って振った。」という問題を解いてみます。上でやったとおりのセル設定にして、ソルバーの選択のときだけDEPSを利用します。

「Options」を開いて「エージェントスイッチレート」をいじって「1.0」にします。

「Solve」を押して実行すると。

しかしここでOKを押してしまうと以下となってしまう。

これが出ると結果が見れなくなって変数の値がリセットされてしまいます。

ちゃんと変数のところには答えがあるのに、このダイアログを閉じるとリセットされてしまうのです。

調べてみても解決方法が見つからないのでおそらくプログラムのバグと思われ諦めるしかなさそうです。

結局絶対値にして最小化で解くしかなく、それをやれば以下のような結果が出ます。

解は2.5358983867173となり、答えになっています。

(x1-1.1)(x1-1.1)のときの3変数でも同様に答えがでます。

(x1,x2,x3)=(1.10000438975313, -1.07732049329422E-05, -4.25224819890482E-05)でした。

またx1を整数制約にしてできるかやってみましたが、DEPSの設定ダイアログで「非負数」にチェックを入れないとやっぱり答えはでないようです。

これまでブログで扱った問題もいくつか解いてみます。

次は以下の問題。

目的関数：y=200×1+320×2→最大化
制約条件1：80×1+130×2≤7800
制約条件2：6×1+4×2≤350
制約条件3：x1,x2≥0

Linear Solverで解くと答えがちゃんと出ます。

次の問題はエクセルソルバーのエボリューショナリーで解いた問題。

アルバイトが4人いる。それぞれのアルバイトは二日連続で働いてはいけない。一日にアルバイトは二人必要。シフトを組まなければいけないのは月曜日から水曜日の3日。これを満たすシフト表を作成せよ。

各セルの値は以下。

$B$2:$D$5：0
F2：=IF(B2+C2=2, 10, 0)
F3：=IF(B3+C3=2, 10, 0)
F4：=IF(B4+C4=2, 10, 0)
F5：=IF(B5+C5=2, 10, 0)
G2：=IF(C2+D2=2, 10, 0)
G3：=IF(C3+D3=2, 10, 0)
G4：=IF(C4+D4=2, 10, 0)
G5：=IF(C5+D5=2, 10, 0)
H2：=IF(SUM(B2:B5)<>2,10,0)
I2：=IF(SUM(C2:C5)<>2, 10, 0)
J2：=IF(SUM(D2:D5)<>2, 10, 0)
K2：=SUM(F2:F5)+SUM(G2:G5)+SUM(H2:J2)

DEPSで解いてみると（DEの割合100%で非負数にチェックを入れる）

答えが得られました。LibreOfficeのソルバーでもIF関数を使った計算を行うことは可能なようです。（ただしDEPSはバイナリなどの離散変数問題は「非負数」のチェックが必須みたいですね）

次の問題に行きます。

遠足に行くときにお菓子を持っていくことになった。お菓子の条件は合計1000円までと決められている。お菓子の候補は以下となる。各お菓子には満足度が存在する。このとき条件を満たしつつ、お菓子の満足度の合計が最大になる組み合わせを求めなさい。

式や条件は上の記事を見てください。

libreofficeのLinear Solverで解くと以下となります。

Linear Solverでは「非負数」でなくても答えが出るようですね。

次が同じ問題をDEPSで解いてみた結果。(「非負数にチェックを入れています」)

答えが出ています。

次の問題に行きます

コンビニを出店することになった。候補地は2つある。周辺には町が2つある。町からコンビニまでの距離は近い方が集客できるので町からコンビニまでの距離の合計は近い方がいい。どちらの候補地を選べばよいか

上はLinear Solverの結果です。やはり非負数にしなくてもよさそうですね。

次はDEPSの結果。「非負数にチェックを入れます」

次の問題に行きます。

SPF材の切り出し問題です。

詳細は省きますが、色々やってみたところ、収束に時間がかかりすぎて解が出ませんでした。どうもエクセルのソルバーにはある「重複なし」の設定がCalcのソルバーでは無理で、重複をペナルティにしてペナルティ法で解こうとしても時間がかかりすぎるという結果でした。Calcのソルバーでは「重複なし」で20変数は難しいようです。

以上よりなんとなくCalcのnlpsolverのクセみたいなものがわかりました。

DEPSの「Value（値）」のモードではバグがあって最終的な解が見れない
DEPSで整数制約の問題を解くときは「非負数」のチェックを入れないと求解できない
線型計画法のLinear Solverはそれなりにちゃんと機能する
DEPSでも「非負数」と「バイナリ」の組み合わせが使える問題でエボリューショナリーじゃないと解けないような問題も解ける。連続変数でもIF関数などを含んだ問題も解けると思われるが、問題によっては解けないときもある。
Calcのソルバーには変数の「重複なし」を設定するダイアログがないため、離散変数問題でペナルティ的に重複を扱うと20変数くらいでは現実的な時間で解が得られない（ただし以下の「重複なし整数の壁を超える方法」である程度解決できる）

やはり使いやすいのはエクセルのソルバーです。ただCalcのソルバーでも結構色々な問題が解けるので、エクセル持ってないけど最適化をやってみたいというときに多少は機能すると思われます。

重複なし整数の壁を超える方法

上で重複なしの整数生成にペナルティ法を使ったのですが、時間がかかりすぎるので別のアプローチを取ることにしました。

以下やり方の詳細です。

まず上の記事でやったVBAがCalcでは動きません。そこでコードを書き換えてユーザー定義関数を作り直しましたが、結局ソルバーを動かすと読み込んでくれないのでユーザー定義関数とソルバーを連携させることは叶いませんでした。（後に色々試した結果引数にRangeを使うことはできました！自分のプログラミングのミスっぽいです。失礼しました。追記の「ユーザー定義関数をCalcのソルバーで使う」をご覧ください）というわけでユーザー定義関数を使用しないでセルの関数のみでやります。

これからこのシートの作り方を述べますが、まずソルバーのDEPSのダイアログで「ランダムな開始点を使用する」にチェックを入れます。じゃないと初期点がすべて0認定されてソルバーが動きません。

上のセルの作り方ですが、まずA列とB列は手入力で打ち込みます。次のC列は空白にしておきます。

次にD1セルに以下のように記述します。

D1：=RANK.EQ(C1,$C$1:$C$22)

これをオートフィルでD22セルまで引っ張ります。

次にE1セルに以下のように記述します。

E1：=OFFSET($A$1,D1-1, 1)

これをE22までオートフィルで引っ張ります。

そこから以下のように打ち込みます。

F2：=IF(E1+E2>180, E2, E1+E2)

F3：=IF(F2+E3>180, E3, F2+E3)

ここでF3をF22までオートフィルで引っ張ります。

G2：=IF(E1<F2, 0, 1)

G3：=IF(F2<F3, 0, 1)

ここでG3をオートフィルでG22まで引っ張ります。

そして

G23：=SUM(G2:G22)

H1：=180*G23+F22

これでソルバーの設定ダイアログに移ります。

DEPSの設定は「ランダムな開始点を使用する」にチェックを入れます。

Solveを押して実行すると以下となります。

ちゃんと1320が求められています。変数セルを重複なし整数に変換するところがミソですね。

ユーザー定義関数をCalcのソルバーで使う

上でユーザー定義関数が使えないのかと思っていましたが、どうやら「ユーザー定義関数の引数セルが変化するタイミングでユーザー定義関数が実行される」という仕様のようで、変数セルによって変化する列を引数にしてソルバーを回してみた結果うまくいきました。以下やり方です。

まずCalcのマクロを保存して次に開いたときに実行したいので、「ツール」→「オプション」→「LibreOffice」→「Security」→「Macro Security」から「Medium.」を選択して一時的にセキュリティレベルを下げます。ソルバーの実行が終わったら「High.」に戻しておきましょう。

次にマクロを以下のように書きます。どうやらmaterialList(i+1, 1)というRangeオブジェクトで受け取った引数は1行目が(1,1)から始まるようで、ここを(0,1)とやってしまっていたところがミスだったみたいです。

Option VBASupport 1
Function CalcLength(materialList As Range, materialNum As Integer)
    Dim i As Integer
    Dim count As Integer
    count = 0
    Dim countLength As Integer
    countLength = 0
    For i = 0 To materialNum - 1
        countLength = countLength + materialList(i+1, 1)
        If countLength > 180 Then
            count = count + 1
            countLength = materialList(i+1, 1)
        End If
    Next i
    
    CalcLength = count * 180 + countLength    
    
End Function

そうしたら上でやったF列とG列を削除してF1セルを以下のようにします。

F1：=CALCLENGTH(E1:E22,22)

この状態でDEPSの設定で「エージェントスイッチレート」を1.0にして、「ランダムな開始点を使用する」にチェックを入れて「学習周期」を20にします。ターゲットセルは「F1」に直します。

学習周期は一回のDEの計算回数みたいなパラメータで多ければ解の発見確率が上がる代わりに遅くなるというパラメータです。デフォルトの2000ではいつまでも計算が終わらなかったので、中断して20に変更したところ現実的な時間(30秒くらい)で求解できました。ここは一般には計算時間を2000でやってみて、時間がかかりすぎるならとりあえず少しずつ減らすようなパラメーターチューニングが必要ですね。

計算すると以下のようになり1320が求められています。