食費を抑えたいのに、栄養も気になる
物の値段が上がって、毎日の食費が気になる人は多いと思います。安くしたい。でも、体のことを考えると、栄養も削りたくない。
献立を考えたことがある人なら、こんな「あるある」に覚えがあるはずです。
- 安いからと買った特売品で、なぜか毎日同じものを食べている
- 「野菜も食べなきゃ」と高い野菜を買い足して、結局いつもより高くつく
- 肉や魚を買えばたんぱく質は足りる、と思い込んでいる
- 栄養の表示を見ても、1日分を足し算するのが面倒で途中でやめる
- 「この献立で本当に安くできているのかな」と思っても、確かめようがない
食べ物1つだけなら「一番安いものを選ぶ」で済みます。でも、1つの食べ物にいろいろな栄養が少しずつ入っているので、何をいくつ食べるかを1つ変えると、全部の栄養の足し算が変わります。この「全体で見てどうか」は、手作業ではなかなか確かめられません。
この記事では、この献立選びを、Excelに最初から入っている機能だけで自動で決めてみます。
この記事でわかること
- Excelに最初から入っている「ソルバー」という機能の基本の使い方（初めての人向けに、ゼロから説明します）
- それを使って**「必要な栄養をとりつつ、1日の食費が一番安くなる食べ方」を決める**方法
- 安さだけを追いかけると、とても食べたくない献立になる理由
- 「同じものは1日3つまで」のような自分の好みの入れ方
VBAもPythonも使いません。Windows版のExcelがあれば試せます。
※ この記事の値段と栄養の数字は、説明のための例です。栄養は食品の成分表の値を丸めた目安で、健康のための食事の指導ではありません。
1. ソルバーってなに？ ── 3つの箱に「ゴール・決めること・ルール」を入れるだけ
身近な例でいうと
2泊3日の旅行の荷造りを考えてみます。何を持っていくか決めるとき、頭の中ではいつも次の3つを考えています。
| 考えること | 荷造りの例 | ソルバーでの呼び名 |
|---|---|---|
| 何を一番よくしたいか（ゴール） | かばんをできるだけ軽くしたい | 目的（目的セル） |
| 自分たちで決められること | 何をいくつ持っていくか | 変数（変数セル） |
| 守らないといけないこと | 着替えは3日分／雨具は必ず1つ | 制約条件 |
持ち物が少なければ、頭の中で試せます。でも、選べるものが増えると組み合わせは一気に増えて、全部を試すのは無理になります。そこで、この3つをExcelに伝えて、一番いい組み合わせを探してもらうのがソルバーです。
ポイントは、ソルバーに「どうやって解くか」を教える必要はないことです。「何をしたいか」「何を決めたいか」「何を守るか」を正しく伝えれば、探すのはExcelの仕事です。
ソルバーを使えるようにする（最初の1回だけ）
ソルバーはExcelに最初から入っていますが、はじめは隠れています。
- ［ファイル］→［オプション］→［アドイン］を開く
- 画面下の「管理」で「Excel アドイン」を選んで［設定］
- 「ソルバー アドイン」にチェックを入れて［OK］
これで［データ］タブの右端に「ソルバー」が出てきます。
ソルバーの画面は「3つの箱」
［データ］→［ソルバー］を押すと設定画面が開きます。たくさん項目がありますが、大事なのは3つの箱だけです。
- 目的セルの設定：ゴールの数字が入っているセルを1つ選び、「最大値」か「最小値」かを選ぶ
- 変数セルの変更：ソルバーに自由に書き換えてもらうセル（＝決めたいこと）を選ぶ
- 制約条件の対象：［追加］を押して、「このセルは○○以上」「このセルは○○以下」のようにルールを1つずつ入れる
献立の場合、ソルバーに書き換えてもらうのは「その食べ物を1日にいくつ食べるか」という数です。食べ物ごとに1つずつ、0、1、2……と数を入れていく。それだけで「何をいくつ食べるか」が表せます。
2. 例：スーパーで買える10種類の食べ物で、1日分を組む
近所のスーパーで買える、ふだんよく食べるものを10種類選びました。値段は「1つ分」にかかるお金です（どれも例の数字です）。
食べ物の表（1つあたり）
| 食べ物 | 1つの量 | 値段(円) | エネルギー(kcal) | たんぱく質(g) | カルシウム(mg) | ビタミンC(mg) | 食物繊維(g) |
|---|---|---|---|---|---|---|---|
| ご飯 | 茶わん1杯(150g) | 54 | 234 | 3.8 | 5 | 0 | 2.3 |
| 食パン | 6枚切り1枚 | 21 | 149 | 5.3 | 13 | 0 | 2.5 |
| 卵 | 1個 | 35 | 71 | 6.1 | 23 | 0 | 0 |
| 納豆 | 1パック | 39 | 86 | 7.4 | 41 | 0 | 3.0 |
| 牛乳 | コップ1杯(200ml) | 52 | 134 | 6.6 | 220 | 2 | 0 |
| 鶏むね肉 | 100g | 96 | 105 | 23.3 | 4 | 3 | 0 |
| 木綿豆腐 | 半丁(150g) | 40 | 110 | 10.5 | 140 | 0 | 0.6 |
| キャベツ | 100g | 26 | 21 | 1.3 | 43 | 41 | 1.8 |
| バナナ | 1本 | 40 | 93 | 1.1 | 6 | 16 | 1.1 |
| ブロッコリー | 100g | 62 | 37 | 5.4 | 50 | 140 | 5.1 |
1日にとりたい量（例）
| エネルギー | たんぱく質 | カルシウム | ビタミンC | 食物繊維 |
|---|---|---|---|---|
| 2000kcal以上 | 65g以上 | 700mg以上 | 100mg以上 | 20g以上 |
ゴールは、この5つを全部とったうえで、1日の食費をできるだけ安くすることです。
3. なんとなく組むと、どうなる？
まずは、ふつうに献立を考えてみます。「主食とたんぱく質と野菜を毎食そろえる」という、よくある考え方です。
| 食べるもの | |
|---|---|
| 朝 | 食パン2枚・卵1個・牛乳1杯・バナナ1本 |
| 昼 | ご飯2杯・納豆1パック・キャベツ100g |
| 夜 | ご飯1杯・鶏むね肉100g・豆腐半丁・ブロッコリー100g・牛乳1杯 |
| 夜食 | トースト1枚・バナナ1本 |
足し算すると、エネルギー2033kcal、たんぱく質96.7g、カルシウム807mg、ビタミンC220mg、食物繊維27.1g。5つとも足りています。
値段は 707円 でした。
ちゃんとした献立に見えます。でも、たんぱく質は65gでいいところを97gもとっていて、ビタミンCも必要な量の2倍以上です。足りないのは困りますが、とりすぎた分にもお金を払っているわけです。では、5つを全部とったうえで、一番安い食べ方はどれでしょうか。ここからはソルバーに探してもらいます。
4. ソルバーに伝える3つ
さきほどの3つに当てはめると、こうなります。
- 決めること（変数）：それぞれの食べ物を、1日にいくつ食べるか
- ルール（制約条件）：エネルギー・たんぱく質・カルシウム・ビタミンC・食物繊維の5つが、どれも1日にとりたい量以上
- ゴール（目的）：1日の食費の合計を最小にする
「とれた栄養の合計」は、「1つあたりの栄養」と「食べる数」を掛けて足すだけで出せます。食べない物は0を掛けるので、勝手に消えてくれます。食費の合計も同じ考え方です。
どの式も「掛けて足す」だけでできているので、Excelは最後まできっちり探しきってくれます。
5. シートの作り方
次の配置で作ります（セル番地は一例です）。
入力欄（自分で入れるところ）
| 場所 | 入れるもの |
|---|---|
| A2:J2 | 見出し（食べ物、1つの量、値段、エネルギー、たんぱく質、カルシウム、ビタミンC、食物繊維、1日の上限、食べる数） |
| A3:H12 | 上の食べ物の表（ご飯〜ブロッコリーの順） |
| I3:I12 | 1日の上限（全部 3。「7. 結果」の後半で使います） |
| D14:H14 | 1日にとりたい量（2000, 65, 700, 100, 20） |
ソルバーが決める欄と計算欄
| 場所 | 入れるもの |
|---|---|
| J3:J12 | ソルバーが決める欄：食べる数（最初は全部0でOK） |
| C16 | 1日の食費の合計 =SUMPRODUCT(C3:C12,$J$3:$J$12) → H16までコピー |
C16を右にコピーすると、D16〜H16がそれぞれ「とれたエネルギー」「とれたたんぱく質」……の合計になります。
SUMPRODUCT は「同じ位置どうしを掛けて、全部足す」関数です。C16なら「ご飯の値段 × ご飯を食べる数」「食パンの値段 × 食パンを食べる数」……を10個分足しているので、食べる物の値段だけが合計されます。$J$3:$J$12 のように $ を付けておくと、右にコピーしても「食べる数」の列がずれません。
使っている関数は SUMPRODUCT だけです。IF などは使いません（これらを使うと、Excelが一番いい答えを確実に探しきれなくなることがあります）。
6. ソルバーの設定
［データ］→［ソルバー］で、3つの箱を次のように埋めます。最後に、画面の項目をいくつか選んで完成です。
① 目的セルの設定：C16 → 最小値
② 変数セルの変更：J3:J12
③ 制約条件の対象（［追加］で1行入れるだけです）
| 左のセル | 記号 | 右 | 意味 |
|---|---|---|---|
| D16:H16 | >= | D14:H14 | 5つの栄養が、どれもとりたい量以上 |
④ 最後に、画面の項目を選ぶ（画面に出ている文字のとおりに選べばOKです）
- 制約条件の［追加］で、左に
J3:J12、真ん中のリストで「int」を選ぶ（右側に「整数」と表示されます） → 食べる数が 0、1、2……だけになります（「卵0.37個」のような半端な答えが出なくなります）。 - 「制約のない変数を非負数にする」にチェックを入れる → 「マイナス1個食べる」のような答えが出ないようにします。
- 「解決方法の選択」で「シンプレックス LP」を選ぶ → 今回のような「掛けて足す」だけの表を、最後まで確実に解ききる解き方です。
- ［オプション］の「整数の最適性(%)」を 0 にする → 途中で妥協せず、一番いい答えまで探します。
［解決］を押して「解が見つかりました」と出たらOKです。
ソルバーが決める欄は食べ物10種類で 10個。Excel標準のソルバーは200個までなので、食べ物を何十種類に増やしても、この形のまま収まります。
7. 結果
まずは、安さだけで選ばせると
1日の食費の最小は 434円 でした。別の計算ツールで、ほかに434円になる食べ方がないかも全部調べましたが、この1通りだけでした（次に安いのは437円）。
| 食べ物 | 食べる数 | 値段 |
|---|---|---|
| 食パン | 12枚 | 252円 |
| 牛乳 | 2杯 | 104円 |
| キャベツ | 300g | 78円 |
| 合計 | 434円 |
とれた量は、エネルギー2119kcal、たんぱく質80.7g、カルシウム725mg、ビタミンC127mg、食物繊維35.4g。5つとも足りています ✔
なんとなく組んだ707円より、273円も安くなりました。でも、1日に食パン12枚（6枚切り2袋）と、牛乳と、キャベツだけ。数字の上では正しくても、とても続けられる献立ではありません。
実はこれ、昔から知られている話です。1940年代にアメリカの経済学者が、同じように「必要な栄養をとれる、一番安い1年分の食事」を計算しました。出てきた答えは、小麦粉・エバミルク（缶入りの濃い牛乳）・キャベツ・ほうれん草・乾燥いんげん豆の5つだけ。今回の答えとそっくりです。
ソルバーは、伝えられたことしか守りません。「おいしく食べたい」「いろいろなものを食べたい」は、伝えていないので気にしてくれないのです。
「同じものは1日3つまで」を足すと
そこで、③の制約条件に1行足します。
| 左のセル | 記号 | 右 | 意味 |
|---|---|---|---|
| J3:J12 | <= | I3:I12 | 同じものは1日3つまで |
これで解き直すと、1日の食費の最小は 593円 になりました。これも全部調べて、593円になるのはこの1通りだけと確認しています（次に安いのは602円）。
| 食べ物 | 食べる数 | 値段 |
|---|---|---|
| ご飯 | 3杯 | 162円 |
| 食パン | 3枚 | 63円 |
| 牛乳 | 3杯 | 156円 |
| 木綿豆腐 | 3つ（半丁×3） | 120円 |
| キャベツ | 200g | 52円 |
| バナナ | 1本 | 40円 |
| 合計 | 593円 |
とれた量は、エネルギー2016kcal、たんぱく質82.3g、カルシウム1226mg、ビタミンC104mg、食物繊維20.9g ✔
たとえば、朝はトースト2枚・牛乳・バナナ、昼はご飯1杯・冷ややっこ・千切りキャベツ・牛乳、夜はご飯2杯・湯豆腐（豆腐2つ分）・キャベツ・牛乳、夜食にトースト1枚、といった形にできます。
| なんとなく組んだ献立 | 安さだけ | 同じものは3つまで | |
|---|---|---|---|
| 1日の食費 | 707円 | 434円 | 593円 |
| 5つの栄養 | ✔ | ✔ | ✔ |
| 続けられそうか | ✔ | 食パン12枚 ✘ | ✔ |
なんとなく組んだ献立より、1日114円安くなりました。30日なら3420円です。
どこが違ったのか
なんとなく組んだ献立とくらべると、鶏むね肉とブロッコリーと卵と納豆がなくなり、豆腐と牛乳が増えたのが大きな違いです。
- 鶏むね肉は、たんぱく質はたっぷりですが、ほかの栄養はほとんどありません。しかも、たんぱく質はほかの食べ物から足りる量がとれていました。
- 豆腐は、40円で、たんぱく質とカルシウムの両方がとれます。牛乳も、カルシウムとエネルギーの両方に効きます。
- ブロッコリーはビタミンCの量はすごいですが、必要なのは100mgまで。キャベツ200gとバナナ1本、それに牛乳の少しの分で104mgになり、ちょうど足ります。
答えは、「1つの栄養がすごい食べ物」より「いくつもの栄養に少しずつ効く、安い食べ物」を選ぶことでした。手で考えると「たんぱく質なら肉」「ビタミンCならブロッコリー」と、栄養ごとに一番よさそうなものを選びがちです。でも、5つの栄養と10種類の食べ物を同時に見ると、組み合わせは一気に増えます。そこを全部調べてくれるのがソルバーです。
「この食べ物は必ず1つ入れたい」ときは、その行の J のセルに「>= 1」のルールを1行足せばOKです。「卵は毎朝食べたい」のような事情も、そのまま入れられます。
8. つまずきやすいところ
-
「線形の条件を満たしていない」というメッセージが出る：計算欄に
IF、ABS、^、ソルバーが決める欄どうしの掛け算が入っていないか確認します（「値段 × 食べる数」のような、入力欄との掛け算は大丈夫です）。 - 「解が見つかりません」と出る：上限を厳しくしすぎると、どう食べても栄養が足りなくなります。この例では「同じものは1日1つまで」にすると、10種類を全部1つずつ食べてもエネルギーが1040kcalにしかならず、答えが出ません（「2つまで」なら806円で答えが出ます）。とりたい量の入れ間違い（2000を20000にしている、など）がないかも確認します。
- 食べる数が 2.37 や 0.9999 のようになる：④の「int」の追加を忘れていないか確認します。
- 毎回結果が違う・終わらない：「解決方法の選択」が「シンプレックス LP」になっているか確認します。
- Web版のExcel、Googleスプレッドシート：ソルバーの仕組みが違うため、この設定はそのままでは使えません。
まとめ
- ソルバーは「ゴール（目的セル）・決めること（変数セル）・ルール（制約条件）」の3つを入れるだけ。献立なら、決めることは「それぞれの食べ物をいくつ食べるか」。
- 栄養が足りるかどうかは、「1つあたりの栄養 × 食べる数」を足すだけで出せる。ルールは「とれた量 ≧ とりたい量」の1行。
- 安さだけをゴールにすると、食パン12枚のような数字の上では正しいけれど食べたくない答えが出る（今回の例では434円）。「同じものは3つまで」のような好みをルールで足すと、続けられて、しかも一番安い献立が出せる（今回の例では593円。なんとなく組んだ献立より1日114円安い）。
ソルバーの基本（3つの箱の考え方、限られた予算の中で何をいくつ選ぶかの決め方など）は、拙著『遠足のおやつ選びから始める数理最適化入門』でも身近な例を使って一から解説しています。