カテゴリー別アーカイブ: Excellentな分析術

ソルバーによるシミュレーション~設定~

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

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

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

「計算が早い」

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

シミュレーション実施前のエクセル画面

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

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

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

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

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

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

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

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

  “Altキー+TI”

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

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

ソルバー設定

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

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

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

にほんブログ村 資格ブログ ビジネススキルへ
にほんブログ村 IT技術ブログ VBAへ


にほんブログ村

ソルバーによるシミュレーション2~基本操作~

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

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

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

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

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

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

1.ソルバーとは何か?

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

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

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

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

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

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

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

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

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

2.実際に操作する

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

下の図のようにB8セル(茶色)に、B3(黄色)とB4(緑)のセルを乗じる数式(B3*B4)を入れておきます

上の図でB8セル(茶色)が1.で解説したシミュレーションの①目的になります

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

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

次に、B3:B4(黄色~緑)を1.②の変動する値範囲として「変数セルの変更」に設定します

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

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

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

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

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

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

変動する値の制約条件に応じた16(4x4)が算出されましたでしょうか?

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

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

にほんブログ村 資格ブログ ビジネススキルへ
にほんブログ村 IT技術ブログ VBAへ


にほんブログ村

ソルバーによるシミュレーション3~実践編・整数設定~

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

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

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

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

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

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

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

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

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

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

➀SUMPRODUCT関数

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

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

この場合、本来は購入金額(価格1X数量1+価格2X数量2+・・・)を計算するのに、延々と数式を足す作業が必要となります

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

②^2

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

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

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

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

・シナリオ1

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

・シナリオ2

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

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

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

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

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

1.制約無しでソルバーを動かしてみる

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

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

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

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

2.制約条件に整数を指定する

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

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

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

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

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

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

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

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

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

の3つになります

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

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

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

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

にほんブログ村 資格ブログ ビジネススキルへ
にほんブログ村 IT技術ブログ VBAへ


にほんブログ村

ソルバーによるシミュレーション4~実践編・バイナリ設定(0,1)~

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

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

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

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

詳細な条件は以下です

・目安は4百万円

・人数は5人(2人は決定済み)

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

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

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

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

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

1.条件に応じて数式を設定する

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

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

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

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

3.ソルバーに制約条件を設定する

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

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

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

つまり1(アサイン有)か0(アサイン無)で解答が出るようにします

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

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

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

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

➀人件費のシミュレーションなどで有無(1か0)で解答が必要になるケースがある

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

 

今回は以上です

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

にほんブログ村 資格ブログ ビジネススキルへ
にほんブログ村 IT技術ブログ VBAへ


にほんブログ村

Solverによるシミュレーション5~マクロ編・設定~

 今回からはマクロによりソルバーの設定・解決自体の自動化に取り組んでいきましょう!

 前回までの4回の解説では、ソルバーの基本設定から実践的に活用する方法まで解説させて頂きました

 Solverによるシミュレーション

  Solverによるシミュレーション2

 Solverによるシミュレーション3~実践編・整数設定~

 Solverによるシミュレーション4~実践編・バイナリ設定~ 

 ソルバーをマクロで活用できるようになると、弱気ベース、強気ベースなど様々なパターンで算出した値を瞬時に比較できるので、より実践的なシミュレーションが行えるようになります!

 今回はソルバーのシナリオを2つ、記録マクロで保存してみてVBAコードを比較してみましょう!

そうするとコードの書き方がつかめてきます

その前に準備が1つあります

 事前にVBE(Alt+F11で開く画面)でSolverライブラリを参照可能にしておきましょう!

このライブラリが無いと、記録マクロが動きません!

では本題に入ります

今回は1~3まで以下の表を通じて解説していきます

 注)D4セルも同様の数式(X1xX2)が入力されています

注)D3セルのYはシナリオ1用、D4セルのYはシナリオ2用になります

1.シナリオ1記録(※X1,X2は4以下の制約にてYを最大化する)

➀以下のシナリオでマクロを記録

②VBEを開けて重複しているコードを削除し、コードを以下の画像のように3部構成にします

③表の値を1にセットし直してシナリオ1マクロを起動

もしもうまくいかなかったら、余計なコードまで消してしまったか、もしくは前述の”Solverライブラリ”にチェックが入っていない状態になっています


2.シナリオ2記録(X1,X2は2以上の制約にてYの値を4にする)

➀設定リセット

記録する際には、ソルバーの設定画面上に前回の設定が残っているのでリセット処理を行っておきましょう

②以下のシナリオでマクロを記録

③VBEを開けて重複しているコードを削除し、コードを4部構成にします

注)シナリオ1にも”SolverReset”を追加しておきましょう!

④表の値を1にセットし直してシナリオ2マクロを起動する

どうです?うまくソルバーは無事に稼働したでしょうか?

3.2つの記録マクロを比較してみる

大前提として2つのシナリオの違いをもう一度整理してみましょう!

➀目的セル、変数セル、及び制約条件を指定するセルの行位置が違う

②目標値の設定の仕方が違う;シナリオ1/最大値、シナリオ2/指定値

③制約条件の符号が違う:シナリオ1/<=、シナリオ2>=

どうでしょう?朧気ながらどのへんをVBAの変数で置き換えられそうかについてのイメージがついてきたと思います

 今回は記録マクロにとどまりましたが、次回は本格的にソルバー・マクロを作成していきましょう!

にほんブログ村 資格ブログ ビジネススキルへ
にほんブログ村 IT技術ブログ VBAへ


にほんブログ村

ソルバーによるシミュレーション6~マクロ編・操作~

 今回は、前の回にて作成した2つのマクロを書き換えて、本格的にソルバーを動かすマクロを作成します

前回作成した2つのマクロは、作成条件が次の3つの項目で違っていました

➀目的セル、変数セル、及び制約方法を指定するセルの行位置

目標値の設定の仕方;シナリオ1/「最大値」、シナリオ2/「指定値」

制約方法の指定:シナリオ1/「<=」、シナリオ2「>=」

記録したマクロのコードを比較して、上記の違いがどう反映されているかを見てみましょう!

シナリオ1
シナリオ2

上がシナリオ1で下がシナリオ2です

おおよそ、黄色の目印を付けたうち、どこが「変数」で置き換えられるか見当がついたと思います

変数で置き換えられる箇所をまとめると、次の通りになります

・SolverOK➡SetCell:「目的値のセル」

      ➡MaxMinVal:1は最大値、2は最小値、3は指定値

      ➡ValueOf:指定値がある場合に指定(無い場合は0)

・ByChange➡「変動する値:値範囲」

・ SolverAdd➡CellRef: 「制約条件の範囲」

      ➡Relation:制約方法の指定

       *下の図の順番で指定:etc 「<=」は1、「=」は2)


      ➡FormulaText:制約条件の指定           

今回は前回作成したシナリオ1マクロを以下の3つ、

1.SetCell(目的セル)、2.ByChange(変数セル)、3CellRef(制約条件セル)

それぞれを変数で置き換えてマクロを動かせるようにしましょう!

1.範囲を指定できるようにしておく

今回はC2セルで1を選んだら上の行を操作する(前回のシナリオ1)、2を選んだら下の行(前回のシナリオ2)を操作するようにします

次の2.ではシナリオ1の前に、IF構文を配置し、C2セルに応じて前述の1~3の範囲を動かせるようにします

If mySheet.Range(“C2”).Value = “1” Then ~ else ~

2.マクロに変数をセットしてマクロを実際に動かす

➀If構文で変数を可変にする

②ソルバーのコードに変数を設定する

各X1、X2を1に直しながら、マクロを試してみてください

<まとめ>

 今回は、前回操作した記録マクロの範囲(以下の3つの範囲)を事前に指定できるようにしました

1.SetCell(目的セル)、2.ByChange(変数セル)、3CellRef(制約条件セル)

 次回はいよいよ、ループ処理により、複数のシミュレーションを一気に走らせることができるようにします!

にほんブログ村 資格ブログ ビジネススキルへ
にほんブログ村 IT技術ブログ VBAへ


にほんブログ村

ソルバーによるシミュレーション7~マクロ編・ループ処理~

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

画像に alt 属性が指定されていません。ファイル名: 36.jpg

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

前回作成マクロ

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

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

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

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

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

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

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

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

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

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

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

目次

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

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

実際に動かしてみる

<まとめ>

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

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

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

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

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

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

実際に動かしてみる

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

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

<まとめ>

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

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

・Productsum関数や^2(差の絶対値化)など数式の使い方を工夫する

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

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

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

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

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

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

にほんブログ村 資格ブログ ビジネススキルへ
にほんブログ村 IT技術ブログ VBAへ


にほんブログ村

ABC分析をエクセル力で改善する9

今回は今まで解説してきた内容を基にしてダッシュボードを作成しましょう

これまで解説してきた内容は大きく分けて2つです。
①ABCランクの区分を自由自在に設定する

ABC分析をエクセル力で改善する3

Gif1

②ABCランクの質と量が見やすいグラフを作成する

ABC分析をエクセル力で改善する6

14

今回は上の2つの内容をダッシュボードで示せるようにしましょう(右半分は今回は割愛します)

アイコン

ABC分析 294.14 KB 43 downloads

...

29

 

1.ランク設定の内容を分かり易くする

ABCランクの設定条件を示しつつ(下の画像の上の枠)、詳細が見たくなったら直接、該当シートに飛べるようにします(下の画像の下の枠)

30

(下の画像の上の枠)

ABCランクを設定したシートからテキストボックスを使って参照します

(セルの参照はテキストボックスの中でなく数式バーでおこなってください)

31

ABC設定のシートではダッシュボードで参照できるようにTEXT関数を使ってデータを成形してあります

(TEXT関数を使わないと , も含めてうまく表示できません)

32

(下の画像の下の枠)

挿入タブからリンクをクリックし該当のシート、セルを指定してください

33

2.ピボットテーブルでABCランクの量と質を表示する

ぜひ以下の記事(特集)を参照してください。ピボットテーブルの見た目を変えるテクニックを紹介しています

ピボットテーブルの役割を変えよう

3.グラフを配置する

ABC、各グラフの位置が分かり易いように凡例の位置を上にして文字を大きくしましょう

35

以上です、また次回お会いしましょう!

にほんブログ村 資格ブログ ビジネススキルへ
にほんブログ村
にほんブログ村 IT技術ブログ VBAへ

ABC分析をエクセル力で改善する8

前回は双方向グラフ作成についての解説をしました

つまり、以下の3つのプロセスで作成を行えます

①第二軸を作成する

②両軸の範囲を揃える(-100%~100%)

③軸を反転する

今回はサンプルの”ABC分析・双方向グラフ”シートのようにピボットテーブルをデータ元にして実際に作成してみましょう

アイコン

ABC分析 258.23 KB 46 downloads

...

ちなみにピボットテーブルのデータ元は”Data”シートになります

21

1.ピボットテーブルを作成したらピボットグラフを作成しましょう!

ピボットテーブルをデータ元としてグラフを作成しましょう!

23

データの指定の仕方は普通のグラフを一緒です(詳細

ただ、グラフではなく”ピボットグラフ”をクリックしましょう!

20

棒グラフを指定したら、分析タブからフィールドボタンを押して見栄えを変え、普通のグラフのようにしましょう!

22

2.第二軸を指定する

商品数を表すグラフ上で右クリックして第二軸を指定しましょう!

これは前回通りです

18

3.両軸の範囲を揃える(-100%~100%)

上下、双方向に伸びるグラフを同じ基準で作成しましょう

それぞれ軸の上で右クリックし、軸の書式設定で最小値と最大値を指定しましょう

25

24]

 

4.第二軸を反転させしましょう

3と同じく軸の上で右クリックして「軸を反転する」にチェックを入れましょう

Gif11

5.軸を非表示にする

最後に軸を非表示にしてグラフの体裁を整えましょう!

26

今回は以上です

次回は最終回です。ダッシュボード化について解説します

にほんブログ村 資格ブログ ビジネススキルへ
にほんブログ村
にほんブログ村 IT技術ブログ VBAへ

ABC分析をエクセル力で改善する7

前回の最後に予告したように、今回は、ABC分析のランク付けが量と質、双方の観点からどのようになっているのかが分かり易いグラフを作成します

15

最初に、今回のグラフ作成にあたっての最大のポイントを解説します

下のGIFの右下に注目してください

Gif11

軸を反転させているのです!

16

今回は反転に関して、追加で2つポイントを解説して終了します

1.第二軸を設定する

まずは棒グラフを作成します

17

下の画像のように第二軸を指定します

18

2.軸の範囲を第一軸と第二軸で揃える

第一軸、第二軸ともに-100%~100%で揃えましょう!

19

今回は以上です!

次回からダッシュボード作成も絡めて実際にサンプルデータを使用してグラフを作成しましょう!
にほんブログ村 資格ブログ ビジネススキルへ
にほんブログ村
にほんブログ村 IT技術ブログ VBAへ