こんにちは、Excellent仕事術のガッツ鶴岡です

さて、あなたの周りで数字に強いと言わている人はどんな人でしょうか？

「数字が頭に入っている」

「計算が早い」

この数字が強い人の定義には、様々な意見があります

　一つだけ確実に言える事は、組織の目標達成に向けたシミュレーションができている人は最強に「数字に強い人」です

「目標達成には商品Aを最低でも○○円以上販売することが条件」

「仮に利益率が□□％以下だとした場合には・・・」

　しかも、前提条件や制約条件を「数字」をまじえて言われると凄い説得力があります

　実は、エクセルにはソルバーという強烈なシミュレーションマシンがあるのをご存知でしょうか？

意外とあまり知られていないようですが、ソルバーはとても便利なんです。

　ソルバーを使いこなせば、プレゼンや会議中に、その場で複雑なシミュレーションを実施することができます

そして、その活用機会も、給与昇給額、商品別必要生産数など様々な分野であります！

しかも操作はとても簡単なんです！

ぜひ、ソルバーを使いこなして「数字が強い」人と言われるようになりましょう！

　これから、実際にソルバーを使用してどの程度のシミュレーションができるのかを見て頂きます

　ソルバーはマクロでも動かすことができます

「実演」は、マクロで行います

実演する、シミュレーションの内容は次の通りです

①今期の売上総利益が「675」⇒来期は「1000」にしたい

②部門がA、B、Cの3部門があり、それぞれ利益率が違う

③「利益率が一番高いA部門」の売上増加率は「？」％必要か？

では目標の「1000」を上の画面の左上①に設定し、「目標・売上総利益」ボタンを押してソルバーを実行します

　上のGIFでは見えていませんが、マクロを実行した後にソルバーが起動しています

　しばらく計算に時間を要しますが、計算が終わった後はスピードメーターの針が振れているのが分かると思います

　上の例では何も制約条件を設けていませんが、実際にはA部門の増加率は”５０％まで”、などの条件を追加できます。

では、本格的なソルバーの解説に入ります！

今回は、まずソルバーをご自身のエクセルで活用できるようにしましょう！

　通常のエクセル画面では、ソルバーは使用できるようになっていませんので設定が必要になります

　ソルバーの設定は、次のショートカットキーを押すところから開始になります！

　　“Altキー＋TI”

このショートカットキーを押せば次の画面が出てきます（ちなみにIはアルファベットのIです）

＊他にはファイルタブからオプションを選択してから設定する方法もあります）

ソルバーアドインにチェックを入れてOKボタンを押しましょう

そうすれば、データタブの下から”ソルバー”が選べるようになっています

では、次回はソルバーを使って簡単なシミュレーションを行ってみましょう

にほんブログ村

こんにちは、Excellent仕事術のガッツ鶴岡です

前回はエクセル画面にソルバータブを設定しました。

ところで、ソルバーとは何でしょう？

英語では、「解決」という意味になります

今回は”ソルバーとは何か”について触れた後、簡単な操作を実際にしてみましょう！

簡単な操作をしてみた後、「ソルバー」の意味を実感して頂けると幸いです！

１．ソルバーとは何か？

まずはソルバーの画面を見てみましょう！

データタブから「ソルバー」をクリックしてみましょう

すると、次のような画面が開きます

大きく分けて３つの構成になっています

　①シミュレーションの目的設定

　②①を達成する為に変動させる「値」及び「値範囲」の設定

　③①を達成する際の制約条件の設定

つまり、③の設定条件を設定した上で、②の値や「値範囲」を動かしながら➀の値をシミュレーションしようということになります

ここまでの解説で、ソルバーについてのイメージが少し沸いてきましたでしょうか？

2．実際に操作する

では、２つのセルを乗じた値を最大化するシミュレーションを行いましょう

下の図のようにB８セル（茶色）に、B３（黄色）とB4（緑）のセルを乗じる数式（B3*B4）を入れておきます

上の図でB8セル（茶色）が１．で解説したシミュレーションの①目的になります

では、こちらを実際にソルバー画面に設定します

目標値は「最大」「最小」「指定値」とありますが、今回は最大で設定します

次に、B３：B4（黄色～緑）を１．②の変動する値範囲として「変数セルの変更」に設定します

１．①目的と②変動させる「値」「値範囲」を設定したところで、最後に制約条件も作成します

制約条件については、次の画像の箇所から設定します

追加をクリックすれば下の画像のようなダイアログボックスが開きます

今回は、変動する値範囲それぞれに「4以下」という制約条件を設定します

➀～③を設定するとソルバーの設定画面が以下のようになっているはずです

ではソルバーの解決ボタンをクリックしましょう！

変動する値の制約条件に応じた16（4ｘ4）が算出されましたでしょうか？

今回の解説は以上になります

では次回からは他の条件でソルバーを活用してみましょう！

にほんブログ村

こんにちは、Excellent仕事術のガッツ鶴岡です

前回はソルバーの基本的な操作を行いました

今回はもう少し実践的な方法に取り組んでみましょう！。

　今回は、ソルバーで解決した値が実際のビジネスで使用できるように、整数にて「最適な解」を出力する方法を解説します！

　実は、実践的にソルバーを使うにあたっては「符号」の問題もクリアする必要があります

この最重要ポイントについては、後述する事前準備・解説の②の中で行います

今回は下の図の課題に取り組みます

・購入単価が違う商品A～Cを組み合わせて購入します

・購入に当たっては、購入金額が予算を超えないようにします

　では本題に入る前にソルバーを実戦的に活用する為の必要知識として、プラスで下の①②の2点を解説します

➀SUMPRODUCT関数

この関数を使うと、本来は長くて複雑な数式の設定が必要なところを２つの引数を指定するだけで済ませることができます

下の画像の右側、購入金額の欄には、左表の商品Ａ、Ｂ、Ｃそれぞれの【価格ｘ数量】の合計を数式で表示する必要があります

この場合、本来は購入金額（価格１X数量１＋価格２X数量２＋・・・）を計算するのに、延々と数式を足す作業が必要となります

今回はSUMPRODUCT関数を使用して、図の左の価格欄と数量欄、それぞれの範囲だけを指定すれば済むようにします

②＾２

これは2乗するという意味です

今回の課題では、下の図のG４セルに【差額：予算-購入金額】の「2乗」を入力します

何故、2乗をする必要があるかという点について以下、実際の例（シナリオ１と２）で解説を行って行きます

今回の課題では差額を「目的」として設定し、差額を最小化する値範囲を設定します

・シナリオ１

*予算金額：5,000,000➡購入金額：5,000,0000➡差額：0

・シナリオ２

*予算金額：5,000,000➡購入金額：5,000,0002➡差額：-2

この場合、シナリオ１が本来は正解です

ただ、差額の符号をマイナスでも取りうることができると、シナリオ２の方が差額が小さいので、シナリオ２の差額-2が正解になってしまいます

ですので、-1x-1=1、と-2ｘ-2＝4という風に2乗することにより、「目的値を絶対値」に変換して計算できるようにしておきます

では準備の2点を説明したところで本題に入ります

１．制約無しでソルバーを動かしてみる

まずは、前述した数式を２つセットしましょう

②の（予算ー購入金額）の2乗が目的の値になります。これを最小値にする商品AからCの購入数量の組み合わせを求めます

実際のビジネスでは、数量を少数点にした値では購入しませんよね？

実際には役に立たない結果が出てしまっています

２．制約条件に整数を指定する

整数の指定の仕方はとても簡単です

まず制約条件の追加を押してください

実は次の画面でINTという指定をするだけで整数指定ができます

では下の画像のように整数指定できているのを確認できたら解決ボタンを押しましょう！

結果はきちんと整数になっています

今回はこれで終了です。今回のポイントを復習すると

Ⅰ.SUMPRODUCT関数で数式の足し算を省力化する

Ⅱ.＾２を使い差額の絶対値を目的の計算に使用できるようにする

Ⅲ．制約条件画面にて、INTの指定を通じて、ソルバーの結果の整数指定を行う

の３つになります

最後に、ソルバーを使う上での注意点を付け加えておきますが、

”ソルバーの値はあくまでシミュレーションであり、ビジネスとして絶対的に正しいわけではありません”

この点は、予め理解した上でソルバーを活用していきましょう！

ではまた次回、更に実践的な内容に取り組みます

にほんブログ村

前回はソルバーの解答を整数で指定する方法を解説しました

今回は違う指定方法を解説します

プロジェクトなどで人をアサインする場合、前回のような”整数”で指定してシミュレーションを行うとどうなるでしょうか？

シミュレーションする内容はプロジェクトを組成する際に、目安にする予算金額に応じて参加メンバーを決める場合です

詳細な条件は以下です

・目安は4百万円

・人数は5人（2人は決定済み）

・候補者の単価はそれぞれ違う

前回の整数指定で行うとこんな結果になります

鈴木さんが2人アサインされてしまっています

これではシミュレーションとして成り立ちません

では、具体的な解説に入りましょう

１．条件に応じて数式を設定する

　・人数の指定：=SUM(F5:F14)

　・人件費の計算：=SUMPRODUCT(E5:E14,F5:F14)

・差額の計算：=(G15-G16)^2

２．ソルバーに目的セルと変数セルを設定する

３．ソルバーに制約条件を設定する

まずは5人という条件を設定します

では次にどうしたらいいでしょうか？

こんな時には整数ではなくバイナリで制約条件を指定します

つまり１（アサイン有）か０（アサイン無）で解答が出るようにします

”bin”を指定すれば”バイナリ”が自動的にセットされます

では解答を計算しましょう

これできっちりとした解答が出ました！

最後に今回のポイントですが、以下の2点です

➀人件費のシミュレーションなどで有無（１か０）で解答が必要になるケースがある

②①のケースのように有無でシミュレーションの解答が必要な場合にはソルバーの制約条件をバイナリで指定する

 

今回は以上です

次回からはソルバーをマクロで自動で動かす方法を解説します

にほんブログ村

　今回はマクロ編の総仕上げとして、計算結果を示すダイアログボックスを表示させないまま、繰り返し処理の中でソルバーを動かせるようにします 　

　前回は、前々回に作成した記録マクロを修正し、ソルバーの指定範囲（目的セル、変数セル、制約条件）を変数で指定できるようにしました（TargetRange、myRange）

　繰返し処理とソルバーを組み合わせると、一度のマクロ操作で必要な値を一覧で取得できます。

　一覧で取得した値を比較することにより、 シミュレーションの精度を格段に向上させることができます

それはもう、AIを導入したかのうような感覚になりますよ！

ぜひ今回で繰り返し処理をしっかり習得しておきましょう！

今回、前回作成したマクロを引き続き使って、繰り返し処理をおこなっていくのですが、

実は今回、習得するポイントはあまり多くなく、１つだけです

！ソルバーのダイアログの表示をどう防ぐか？

ソルバーの計算が終わった後にダイアログが必ず表示されますよね？

この表示が出てしまうと繰り返し処理が止まってしまいます。

このダイアログの表示を止める方法については、後ほど解説致します。

　繰り返し処理自体のVBAコードの詳細な解説については、他の専門サイトや専門書に譲りたいと思います

目次

ソルバーの各指定範囲を変数で置換え

ソルバーのダイアログを非表示

実際に動かしてみる

＜まとめ＞

ソルバーの各指定範囲を変数で置換え

前回は２つの範囲をIF構文で切り替えて指定できるようにしました

今回は表の最終行（変数：Last)を取得した上で、カウントアップ用変数のiと連動してソルバーの対象範囲を変えていけるようにします

上の画像にあるmyRowはiと連動してカウントアップしていきます

ソルバーのダイアログを非表示

これはこの４文字を足すだけで可能です　⇒TRUE

実際に動かしてみる

下のGIF画像のように、最終行を変更しながらマクロを動かしてみましょう！

どうでしょう？うまく計算できましたでしょうか？

＜まとめ＞

　ソルバーを使うと複雑なシミュレーションも簡単に行えます。実践的に活用する為のポイントとしては以下の２つだと思っています

・制約条件を整数やバイナリ、など実用的な形で指定する

・Productsum関数や＾２（差の絶対値化）など数式の使い方を工夫する

マクロについては意外と簡単だったのではないでしょうか？もしうまく行かなかったら、記録マクロを作成してよく観察してみましょう！

　最後に、アイキャッチ画像に使っているのはマクロで作成しているスピードメーターグラフです

　こういった見る人の目を引くアイテムと「ソルバー」を組み合わせると楽しいシミュレーションボードが出来ます

ぜひトライしてみてください

⇒スピードメーターグラフ（マクロ付き）はこちらから

⇒スピードメーターグラフ（グラフ機能版）の解説はこちらから

にほんブログ村