新社会人のためのExcelデータ分析|ソルバー編

新社会人のスキル向上
スポンサーリンク

前回に続いてExcelデータ分析についてまとめていきます。

前回はゴールシークと言って、必要な値を逆算するときに使える機能についてご紹介しました。

ゴールシークは、「100円で買ったものの消費税はいくら?」というような、条件が一つの場合に逆算をすることができます。

言い換えると、逆算したい条件が複数ある場合はゴールシークは使えないのです。

そこで今回ご紹介するのがソルバー

求めたい数値が複数ある場合や特定のーーーに使うことができます。

今回はこのソルバーについて、基本的な使い方と、実践例をみていきましょう。

ソルバーとは

ソルバーとは

ソルバーは、数式の目標値を設定し、その結果を得るために複数の制約条件を指定し、任意のセルを変化させて必要な条件を求める機能です。

さまざまな条件に合う、最適な数値を素早く導き出すことができるのです。

※もちろんゴールシークの例でみたような条件が一つのものも実行可能です。

ソルバーの使い道の例

具体的にはどのような場面で使えるのでしょうか。

たとえば、

・予算制約のもとでの最適な予算配分はどのようなものか?

・目標の売上を達成するには複数の商品をいくつずつ仕入れたらいいか?

などといったことを素早く計算することができます。




使い方

初回のみ|アドインを有効にする

初期設定では、ソルバーの機能が無効となっているので、有効化する必要があります。

初めてソルバーを使用するときに設定しましょう。

ファイルオプションアドイン

から設定をクリックして

ソルバーアドインを有効にします。

OKを押すと、ソルバーが有効化され、バーにソルバーが表示されるようになります。

条件を示す

ソルバーが有効化されたら、さっそくソルバーを使っていきましょう。

まずは条件を文字と表にしていきます。

『Excel最強の教科書』で著者は、わからなくならないように制約条件を書いておくことを勧めています。

ここではそれに倣い、上に条件を書いておくことにしましょう。

今回の例では以下のような条件を出し、商品Aと商品Bをそれぞれいくつずつ買えばいいかを求めてみましょう。

二つの商品を予算内でできるだけ多く購入したい。

商品Aは1200円、商品Bは2700円で、予算は60000円である。

また、AとBの個数の差は5つ以下としたい。

次にその条件を表に表していきます。

このようになります。

表にするときのポイントは以下の通りです。

値段の行はベタ打ちではなく数式を入力

合計の列はベタ打ちではなく数式を入力

ばらつきの計算式は、MAX(C9-C10)-MIN(C9-C10)

数式を入力すべきセルは、ベタ打ちにするとソルバーが実行できなくなってしまうので注意が必要です。

ソルバーの実行

条件が書けたらソルバーを実行していきます。

データソルバー

からソルバーをクリックするとこのような画面が出てきます。

目的セルとは希望の数値が書かれたセルのことです。

求めたい数値(推定値)にする条件を求めることも、その値を最大、最小にするような条件を求めることもできます。

ここでは推定値を60000に設定します。

変数セルとは数値を変更していくセルのことです。

ここでは商品の個数を設定します。

また、それ以外の条件は「追加」から追加できます。

次のような条件を入れることができます。

・セルを任意の数以上、以下にする

・とある複数のセルの合計が、別のセルの合計を下回るようにする

・セルを整数にする(int)

・セルの値を0か1にする(bin)

ここでは、個数を整数にする条件、商品AとBの個数の差を5以内にする条件を追加しました。

全ての条件を入力し終えたら、解決をクリックします。

ばらつきが2の、60000円以内の商品の買い方を求めることができました!

商品Aを14個、商品Bを16個買えばいいとわかりましたね。

まとめ

今回は、複数の条件をもとに逆算できるツール、ソルバーについてまとめてきました。

ソルバーはゴールシークと比べると難しく感じますが、売上目標や予算を算定するときなど、ビジネスの場で使えると便利な機能です。

前回の繰り返しになりますが、一度自分の手を動かして使い方を確認してみることをお勧めします。

見るのと実際にやるのでは全然違うからです。

私も実際にやってみて、理解しているようで理解できていなかったことに気づくことができました。

ぜひ、簡単な例で実践してみてください。

※私がExcelについて参考にした本はこちら。

実際のビジネスシーンでExcelを使えるようにすることを目指した「実用」に特化した本です。

Excelを用いた業務をする新社会人は必ずもっておきたい一冊です。

コメント

  1. […] 新社会人のためのExcelデータ分析|ソルバー編前回に続いてExcelデータ分析… […]

タイトルとURLをコピーしました