Power Queryを使えばAccessとExcelのいいところを組み合わせて利用することができます
昔、電話は電話帳をめくりながら、ダイヤルを回しましたが、今はスマホで検索して電話をしています
PowerQueryを使えば、電話帳のような大量のデータをAccessにて一括管理して、必要な時にエクセルで抽出して加工することができます
VBAのような専門知識は必要とせず、流れを覚えてさえしまえばとても簡単です
以下、概要だけ羅列します
１．PowerQueryを起動
２．抽出条件を指定します
３．抽出条件は後から削除することもできます
４．エクセルのセルで条件を指定できるようにします＿1/2
5．エクセルのセルで条件を指定できるようにします＿2/2
上記だけでは分かりずらいと思いますが、直感的にできるようになっていると思うのでぜひ試してみてください
今回からはマクロによりソルバーの設定・解決自体の自動化に取り組んでいきましょう！
前回までの4回の解説では、ソルバーの基本設定から実践的に活用する方法まで解説させて頂きました
Solverによるシミュレーション4～実践編・バイナリ設定～
ソルバーをマクロで活用できるようになると、弱気ベース、強気ベースなど様々なパターンで算出した値を瞬時に比較できるので、より実践的なシミュレーションが行えるようになります！
今回はソルバーのシナリオを２つ、記録マクロで保存してみてVBAコードを比較してみましょう！
そうするとコードの書き方がつかめてきます
その前に準備が１つあります
事前にVBE（Alt+F11で開く画面）でSolverライブラリを参照可能にしておきましょう！
このライブラリが無いと、記録マクロが動きません！
では本題に入ります
今回は１～3まで以下の表を通じて解説していきます
注）D4セルも同様の数式（X1ｘX2）が入力されています
注）D３セルのYはシナリオ１用、D４セルのYはシナリオ２用になります
１．シナリオ１記録（※X1,X2は４以下の制約にてYを最大化する）
➀以下のシナリオでマクロを記録
②VBEを開けて重複しているコードを削除し、コードを以下の画像のように３部構成にします
③表の値を１にセットし直してシナリオ１マクロを起動
もしもうまくいかなかったら、余計なコードまで消してしまったか、もしくは前述の”Solverライブラリ”にチェックが入っていない状態になっています
２．シナリオ2記録（X1,X2は2以上の制約にてYの値を４にする）
➀設定リセット
記録する際には、ソルバーの設定画面上に前回の設定が残っているのでリセット処理を行っておきましょう
②以下のシナリオでマクロを記録
③VBEを開けて重複しているコードを削除し、コードを４部構成にします
注）シナリオ１にも”SolverReset”を追加しておきましょう！
④表の値を１にセットし直してシナリオ２マクロを起動する
どうです？うまくソルバーは無事に稼働したでしょうか？
３．２つの記録マクロを比較してみる
大前提として２つのシナリオの違いをもう一度整理してみましょう！
➀目的セル、変数セル、及び制約条件を指定するセルの行位置が違う
②目標値の設定の仕方が違う；シナリオ１/最大値、シナリオ２/指定値
③制約条件の符号が違う：シナリオ１/＜＝、シナリオ２＞＝
どうでしょう？朧気ながらどのへんをVBAの変数で置き換えられそうかについてのイメージがついてきたと思います
今回は記録マクロにとどまりましたが、次回は本格的にソルバー・マクロを作成していきましょう！
今回は、前の回にて作成した２つのマクロを書き換えて、本格的にソルバーを動かすマクロを作成します
前回作成した２つのマクロは、作成条件が次の３つの項目で違っていました
➀目的セル、変数セル、及び制約方法を指定するセルの行位置
②目標値の設定の仕方；シナリオ１/「最大値」、シナリオ２/「指定値」
③制約方法の指定：シナリオ１/「＜＝」、シナリオ２「＞＝」
記録したマクロのコードを比較して、上記の違いがどう反映されているかを見てみましょう！
上がシナリオ１で下がシナリオ２です
おおよそ、黄色の目印を付けたうち、どこが「変数」で置き換えられるか見当がついたと思います
変数で置き換えられる箇所をまとめると、次の通りになります
・SolverOK➡SetCell:「目的値のセル」
➡MaxMinVal:１は最大値、２は最小値、３は指定値
➡ValueOf:指定値がある場合に指定（無い場合は０）
・ByChange➡「変動する値：値範囲」
・ SolverAdd➡CellRef: 「制約条件の範囲」
➡Relation:制約方法の指定
*下の図の順番で指定：etc 「<=」は１、「=」は2）
➡FormulaText:制約条件値の指定
今回は前回作成したシナリオ１マクロを以下の３つ、
１．SetCell（目的セル）、２．ByChange（変数セル）、３CellRef（制約条件セル）
それぞれを変数で置き換えてマクロを動かせるようにしましょう！
１．範囲を指定できるようにしておく
今回はC2セルで１を選んだら上の行を操作する（前回のシナリオ１）、２を選んだら下の行（前回のシナリオ２）を操作するようにします
次の２．ではシナリオ１の前に、IF構文を配置し、C2セルに応じて前述の１～３の範囲を動かせるようにします
If mySheet.Range(“C2”).Value = “1” Then ～ else ～
２．マクロに変数をセットしてマクロを実際に動かす
➀If構文で変数を可変にする
②ソルバーのコードに変数を設定する
各X1、X2を１に直しながら、マクロを試してみてください
＜まとめ＞
今回は、前回操作した記録マクロの範囲（以下の３つの範囲）を事前に指定できるようにしました
１．SetCell（目的セル）、２．ByChange（変数セル）、３CellRef（制約条件セル）
次回はいよいよ、ループ処理により、複数のシミュレーションを一気に走らせることができるようにします！
今回はマクロ編の総仕上げとして、計算結果を示すダイアログボックスを表示させないまま、繰り返し処理の中でソルバーを動かせるようにします
前回は、前々回に作成した記録マクロを修正し、ソルバーの指定範囲（目的セル、変数セル、制約条件）を変数で指定できるようにしました（TargetRange、myRange）
繰返し処理とソルバーを組み合わせると、一度のマクロ操作で必要な値を一覧で取得できます。
一覧で取得した値を比較することにより、 シミュレーションの精度を格段に向上させることができます
それはもう、AIを導入したかのうような感覚になりますよ！
ぜひ今回で繰り返し処理をしっかり習得しておきましょう！
今回、前回作成したマクロを引き続き使って、繰り返し処理をおこなっていくのですが、
実は今回、習得するポイントはあまり多くなく、１つだけです
！ソルバーのダイアログの表示をどう防ぐか？
ソルバーの計算が終わった後にダイアログが必ず表示されますよね？
この表示が出てしまうと繰り返し処理が止まってしまいます。
このダイアログの表示を止める方法については、後ほど解説致します。
繰り返し処理自体のVBAコードの詳細な解説については、他の専門サイトや専門書に譲りたいと思います
前回は２つの範囲をIF構文で切り替えて指定できるようにしました
今回は表の最終行（変数：Last)を取得した上で、カウントアップ用変数のiと連動してソルバーの対象範囲を変えていけるようにします
上の画像にあるmyRowはiと連動してカウントアップしていきます
これはこの４文字を足すだけで可能です ⇒TRUE
下のGIF画像のように、最終行を変更しながらマクロを動かしてみましょう！
どうでしょう？うまく計算できましたでしょうか？
ソルバーを使うと複雑なシミュレーションも簡単に行えます。実践的に活用する為のポイントとしては以下の２つだと思っています
・制約条件を整数やバイナリ、など実用的な形で指定する
・Productsum関数や＾２（差の絶対値化）など数式の使い方を工夫する
マクロについては意外と簡単だったのではないでしょうか？もしうまく行かなかったら、記録マクロを作成してよく観察してみましょう！
最後に、アイキャッチ画像に使っているのはマクロで作成しているスピードメーターグラフです
こういった見る人の目を引くアイテムと「ソルバー」を組み合わせると楽しいシミュレーションボードが出来ます
ぜひトライしてみてください
⇒スピードメーターグラフ（マクロ付き）はこちらから
⇒スピードメーターグラフ（グラフ機能版）の解説はこちらから
ピボットテーブルの表示を操作しやすくするスライサーはとても貴重な存在です。スライサーを使うだけでエクセル画面が華やかになります。
ただし、スライサーの使い方について、困ったことが起こっても日本語での解説はあまりないのが現状です（⇒スライサーについての詳細記事あります）
スライサーを使っていて困ることの一つは、スライサーで自動設定されている並び順を変えたい時です。そんな時にはリスト自体を自分で設定して独自の並び順を作成しましょう
この方法はピボットテーブルの表示順の変更にも活用できます
それでは、独自の並び順を作成する手順を3段階で説明します！
下の画面では、スライサー上では中村さんが1番上の左に来ていますので、田中さんの表示を1番上・左に移動したいとします
後で詳細を解説しますが、リスト全体を含めて並び順を設定するので、リスト全ての並び順を事前に決めおく必要があります
エクセル画面の左上、ファイルタブから⇒オプション⇒詳細設定をクリックして、下までスクロールします
全般という文字が見えたら、右下に「ユーザー設定リストの編集」のボタンが見えますのでこちらをクリックします
立ち上がったダイアログの右側に 「１．並び順を決める」 で決めた順番でリストを記入しましょう
続きを読む スライサーのリスト表示順とピボットテーブルの表示順を自由自在に変える方法エクセルのグラフ機能でスピードメーターグラフを作成すると、どうしても肝心な針部分などのデザイン性があまり良くありません。ですので、今回は針をグラフとは別途に作成し、イベントプロシージャ―を組み合わせて動く仕組みを作成しました
動く仕組み自体は至ってシンプルです
数字を指定するセルの値が変わるとイベントプロシージャーが起動するようになっています
イベントプロシージャ―が動いたら針を回転させるようになっています
プレゼン資料などに混ぜると、結構インパクトが高いのではないでしょうか？
また後日、作成の仕方の詳細について解説したいと思います！
明確な意図をもって色遣いしたグラフでも、白黒で印刷 するので、意図したものが台無しになることってありますよね。そんな時の為の工夫を３つ紹介します
１．色の濃淡でメリハリをつけて作成する
グラフを作るときから、色の濃淡をつけて作成しておけば、色黒で印刷してもあまり作成時と印象は変わりません
幸にもエクセルの最近のバージョンでは自動的に濃淡をつける機能があります
グラフをクリックすると右上に下のような筆マークが出てくるで、マークの右側の”色”をクリックします。
すると、モノクロ柄が選べるようになります
ちなみに、手作業で色の濃淡をつけようとしたら莫大な作業になってしまいます
２．凡例（系列）の違いを画像で表現する
色使いの違いの意図が凡例の違いを一目で分かるようにするものである場合、白黒で印刷するとどこか物足らないものになってしまうことがあります
そんな時には以下の画像のように”画像”の工夫を行うこともできます
実は差し込みたい画像の上でCtrl+Cを押して、グラフ上でまたCtrl+Vを押すだけで画像をグラフに反映することができます
こちらの手法については詳細を別な記事で紹介していますので、ぜひご参考にしてください
３．グラフの形を統一してシンプルにする
たもに実績説明の場でこんなグラフを見ますが、白黒だととても理解できないものになってしまいます
こんな時にはグラフの形を統一してシンプルにしましょう
下のグラフは折れ線グラフの部分をマーカーと誤差範囲を工夫してグラフの形を統一しています
マーカーや補助線は実は魅せれる形にカスタマイズすることができるのです
こちらのグラフの作成の仕方・詳細については別な記事をぜひご参照ください
では今回は前回から引き続き”データの選択”について解説します
今回の対象は凡例項目と横（項目）軸ラベルになります
上の画像の左側に赤印をした箇所を「凡例項目」、右側に赤印をした箇所を「横（項目）軸ラベル」として解説を行います
凡例項目を使いこなして、下のグラフに2017の数字を加えてみましょう
追加を押すと、どのデータを追加するのかを指定できるようになります
追加を押した後は系列名と系列値に分けて指定を行います
これで2017の数字が追加されました
そして、順番を変えるには以下の▼▲（凡例項目の右端）を操作しましょう
横（項目）軸ラベルは１ほど使う機会がありませんが、知っておくと便利です
データの範囲を指定しなくても、表示項目のチェックの入れ替えだけで調整することができます
上の画像では、「横（項目）軸ラベル」の部門Cのチェックを外すだけで、グラフから部門Cの表示が消えています
今回は以上です
また次回をお楽しみに！
今回は”系列グラフの種類の変更”の使い方について解説します
”系列グラフの種類の変更”とはグラフ上で右クリックすると表示される”機能”です
例えば、下のスピードグラフはこの機能により違う種類グラフ（円、ドーナツ）を組み合わせ、表示軸も第１軸と第2軸を組み合わせを変えることにより完成します
この機能は数字を魅せるグラフ術でも盛んに活用されます。基礎の段階でうまく使いこなせるようにしておきましょう！
下のようなデータをグラフにしようとしていたとします
売上と利益ではデータとしての種類が違いますよね？
そのまま棒グラフにすると、下の画像のような形になります
このままだと意味不明なので、まずは利益を売上とは違う種類のグラフにするため、利益グラフの上で右クリックをします
すると”系列グラフの種類の変更”の表示が出てきますのでクリックします
上の画像のように「系列グラフの種類の変更」を表示すれば、自由自在にグラフを変更できるようにできますので、ここでは折れ線グラフを選択しましょう！
これでだいぶ見やすくなりましたね
利益の種類のグラフを変更することで、だいぶ見やすくなりましたが、そもそも売上と利益では数字の質が違う為、バランスが悪いので利益を測る軸を変えます
１の時と同じように”系列グラフの種類の変更”から第２軸にチェックを入れます
すると下のグラフのようになります
売上と利益が違う基準で測られるようになった為、部門A、B、Cの違いが売上と利益の両面で分かり易くなりました
繰返しになりますが、この機能は頻繁に出てきますので早めに使いこなせるようにしておきましょう！
では、また次回！
