Excel。アドインしてソルバーを使ってみよう！すごいぞ ソルバー

Excel。アドインしてソルバーを使ってみよう！
すごいぞ ソルバー

ソルバーとアドイン

先日ご紹介した、ゴールシーク。
結構好評でして、仕事で使える！と仕事で使えるExcel講座などで言っていただいたのですが、
１つでなくて、複数のデータの場合は出来ますか？との質問が出まして…
そりゃ～確かに、セル1つだけじゃねぇ～。

ゴールシークを使って、根性を入れれば求められないこともないですしね。

実は、ソルバーという機能を使うと、このリクエストに答えることが出来るん訳なんです。
このソルバーの基本的な使い方は、非常に簡単で、ゴールシークじゃなくて、
こっちを標準装備にしてくれればいいじゃないの？と思っちゃったりするんですが、このソルバー。アドインしないと、使えないんですね。

それでは、まずは、アドインの方法をご紹介しましょう。
Excelのバージョンは2010です。
まず、前提として、開発タブが表示されているものとして紹介していきます。
開発タブが表示されていないとアドインすることが出来ませんので、注意が必要です。

アドインは、開発タブのアドインをクリックすると、アドインダイアログボックスが表示されてきます。

ソルバーアドインにチェックをつけて、OKボタンをクリックしましょう。
これで、下準備は完了です。
何が変わったのでしょうか？Excelに劇的な変化が起こっているようにみえませんね。
変わった場所を、ご紹介しましょう。
それは、データタブに移動しましょう。

データタブの中に分析というのが登場して、そこにソルバーが登場しましたね。
コレで確認できましたね。

さて、準備は出来ましたので、ソルバーを紹介していきましょう。
下記のような表があります。

売上を10万円に到達するには、
A～S定食をそれぞれ何個売ればいいのか？
というのをソルバーを使うとあっさり、簡単に求めることが出来るんですね。

答えを求めたいところは、C7:C9。

準備としては、D7:D9に価格×数量の数式が、D10には、
総合計を求める数式が設定されています。

もう一つ準備がありまして、条件を加えることが出来ます。
今回は、このような条件を付けたいと思います。
個数なので、整数にする。
S定食は50個以上売る。
達成金額は最低10万円
という条件で求めたいと思います。
本来ならば、S定食は50個以上売るという条件は無いほうがいいと思いますが、
今回は、データの作り方という事で、いれております。

それでは、データタブのソルバーをクリックしましょう。

ソルバーのパラメーターダイアログボックスが表示されます。

目的セルの設定ですが、
これは、目標とする合計金額のセルになりますのでD10を範囲選択しましょう。

目標値は、目標とする合計金額に最も近くなるようにしますので、今回は「最大値」を選びます。
変数セルの変更は、数量にありたいますから、C7:C9を範囲選択しましょう。
制約条件の対象は、先程、準備しました、条件を作っていきましょう。
追加ボタンをクリックしましょう。

最初は、「個数なので、整数にする。」ですので、

セル参照は、C7:C9を範囲選択して、条件にはintを選ぶと制約条件に整数が表示されます。
続けて「S定食は50個以上売る。」の条件を作っていきますので、追加ボタンをクリックしましょう。

セル参照は、S定食の数量のC9を選択して、
条件は>=で制約条件には50個以上売りたいので、50と入力します。

さらに、「達成金額は最低10万円」という条件も追加しますので、追加ボタンをクリックしましょう。

合計金額が最低10万円なので、セル参照はD10を選択します、
条件は最低ということですので<=を選択して、制約条件は、10万と直接入力してもいいのですが、B4に10万という数字が用意してありますので、このセルを使いたいと思います。

このようにセル参照も出来ます。条件は今回3つですので、ここでOKボタンをクリックしましょう。
先程のソルバーのパラメーターダイアログボックスに戻ってきます。

あとは、解決ボタンをクリックしてみましょう。
すると、ソルバーの結果ダイアログボックスが表示されます。

あとは、OKボタンをクリックすると完成ですが、ここで、レポートの解答をクリックすると、
別シートに詳細解答を表示してくれます。

結果は、

A定食13個。B定食16個。S定食68個となりました。
このソルバーを知るとより複雑な条件でも最適値を求めることが可能になりますので、
アドインのソルバー。覚えておいて損はないと思いますね。

なお、条件の変更や追加がある場合は、改めて、データタブのソルバーをクリックすれば、
ソルバーのパラメーターダイアログボックスが表示されます。

まぁ、折角なので、解決方法に、滑らかな非線形のGRG非線形と、線形には、LPシンプレックスと、滑らかでない非線形のエボリューショナリーがありますので、この3つの結果を比べてみましょう。

先程、算出したのが、GRG非線形

では、シンプレックスLPだと、どうなるでしょうか？

B定食0でいいと判断されましたが、これはいかがなものでしょうか？
最後にエボリューショナリーだと、

ソルバーの結果で、条件が足らないので、そのままでは算出することが出来ませんと表示されました。このエボリューショナリーの場合は、すべての変数に上限と下限が必要になりますので、もっと詳細な条件を考えないといけませんね。

Excel。ABC分析（パレート図）のデータを作ってみよう？

Excel。ABC分析（パレート図）のデータを作ってみよう？

ABC分析（パレート図）

分析関係でお馴染みになってきた、ABC分析（パレート図）。
折れ線グラフを縦軸0横軸0の交点で始める方法は、以前ご紹介しましたが、
仕事で使えるExcel講座や企業研修さんにおいて、グラフの作成の前段階。
つまりABCに分ける表の作り方について、ご質問が、ちょこちょこございますので、
今回は、データというか表の作成をご紹介していきましょう。

表自体の作成方法は、初心者の方でもいい練習になりますので、挑戦してみましょう。
さて、下記の表があるとします。

まず、パレート図を作成するにあたり手順を確認しておきましょう。
1.頻度の順（今回は金額）に項目を並べる
2.頻度の合計（金額の合計）を100とした構成比を求めます
3.頻度の順に構成比の累計を算出する
4.構成比率の累計によってグループ分けをしていく
～80%をA
～90%をB
～100%をC
とグループ分けします。

それでは、B列の金額を金額の大きい順すなわち、降順にしましょう。

ここで、注意が必要なのは、データタブの降順ボタンをポンと押すと21行目の総計まで含めて並び変わってしまいますね。

この場合は、どうしたらいいかというと、まずA1:B20までを範囲選択、
つまり総計行を除いて範囲選択をします。
データタブの並び替えボタンをクリックしましょう。

クリックすると、並び替えのダイアログボックスが表示されます。
このダイアログボックスを使用します。

最優先されるキーを”金額”として、並び替えのキーは、値で、順序は”降順”として、
OKボタンをクリックすると、金額の降順でデータが並び変わりますね。

当然範囲に含めていない総計行は除外されていますね。

これで、1.頻度の順（今回は金額）に項目を並べる。が出来ましたね。
次は、2.頻度の合計（金額の合計）を100とした構成比を求めていきましょう。
C1に構成比と入力をして、C2に大阪の構成比を算出していきましょう。

使う値は、大阪の金額と総計の金額ですね。で、比率なので、四則演算は、除算ですね。

問題は、どっちからどっちを割ることになるのですが、とりあえず、割ってみましょう。
100%を越えたら、おかしいわけですから、そのポイントだけ頭においておけばよいでしょう。

今回は、大阪の金額を総計で割ってみることにしましょう。

=B2/$B$21

B21の総計は常に参照。
つまり、絶対参照にしないとオートフィルで数式をコピーした時に#DIV/0というエラーが
表示されてしまいますので、F4キーを押して、絶対参照の設定を忘れないようにしましょうね。

数式をオートフィルでコピーして、％スタイルと小数第2位まで表示するようにすると、
このようになりますね。

これで、2.頻度の合計（金額の合計）を100とした構成比を求めることができましたね。
続いて、3.頻度の順に構成比の累計を算出していきましょう。
D1に累計と入力して、D2をクリックしましょう。
ここで、SUM関数のテクニックを使って、累計を求めていきましょう。
数式を手入力したほうが楽ですので、直接入力で数式を作っていきます。

=SUM($C$2:C2)

この数式はどんな意味なのかというと、最初のC2を絶対参照を設定することにより、
常にC2～指定のセルまでの範囲という意味になります。

絶対参照も使い方をちょこっとアレンジするだけで、累計も簡単に算出することが出来ます。

この数式をオートフィルをしようして、コピーしていきましょう。

そして、4番目の、4.構成比率の累計によってグループ分けをしていく
～80%をA　～90%をB　～100%をC　とグループ分けするので、
ここは、IF+IF関数を使うと求めることが出来ますね。

E1にランクと入力をして、E2をクリックしましょう。

IF関数のダイアログボックスを表示しましょう。
論理式に、D2<80%
真の場合には、”A”と入力しましょう。

偽の場合には、再度質問をしていくことになりますので、名前ボックスの▼をクリックしましょう。

もう一度IF関数のダイアログボックスを表示していきましょう。
どうも、このネストの時に、前のダイアログボックスが見えなくなるのが、
関数に慣れてない人からは、わからなくなるということをよく聞きますね。

論理式に、D2<90%
真の場合には、”B”と入力しましょう。
そして、
偽の場合には、”C”と入力しましょう。
これで、OKボタンをクリックすると、Aと算出されましたね。
この数式をオートフィルを使ってコピーしていきましょう。

これで、ABC分析（パレート図）を作成するデータ。表が完成しましたね。
これを基にして、グラフにしていくわけですね。

Excel。棒グラフを絵にして表現してみませんか？

Excel。棒グラフを絵にして表現してみませんか？

絵グラフ

初心者さん向けの講座でも、仕事でつかえるExcel講座なんかでも、グラフの重要性をお話して、グラフの作り方やそれぞれのグラフの見せ方もお話しするのですが、そこで、単なる棒グラフよりも、棒を絵にしたほうが、表現としていい場合があります。作り方は非常に簡単なので、今回は、棒グラフを絵に変更するテクニックをご紹介しましょう。

まずは下記の表がありますので、集合横棒を作っていきましょう。

使うデータは、A3:A7とH3:H7を使いますので、範囲選択をして挿入のグラフにある

横棒の集合横棒を選択しましょう。
下記のような横棒グラフが出来ましたね。

グラフを少しアレンジしてみましょう。グラフタイトルを非表示で凡例を下にしましょう。
まずは、グラフタイトルを非常時にしてみます。グラフタイトルをクリックして、DELキーでOKですね。一応メニューでのやり方も紹介しておきましょう。グラフをクリックして、レイアウトタブのグラフタイトルをクリックして、なしを選択してもOKですね。

続いて、凡例を下に移動させましょう。レイアウトタブの凡例をクリックしましょう。

すると、凡例が下に移動しましたね。

さて、いよいよ今回の目的である、この棒グラフを絵グラフに変更していきましょう。
まず、プリンの棒グラフだけを選択します。一度棒グラフをクリックすると棒グラフ全部が選択されますので、再度プリンの棒グラフをクリックしますと、その棒グラフだけを選択することが出来ますね。そうしましたら、レイアウトタブの現在の選択範囲にある選択対象の書式設定をクリックしましょう。

そうすると、データ系列の書式設定ダイアログボックスが表示されます。

塗りつぶしの塗りつぶし（図またはテクスチャ）を選択し、クリップアートをクリックします。今回の絵はクリップアートを使用しますので、クリップアートを選択しますが、もし画像ファイルがあるようでしたら、ファイルボタンをクリックして該当ファイルを選びましょう。

クリップアートをクリックしますと、図の選択ダイアログボックスが表示されますので、検索したい文字を入力して検索ボタンをクリックしましょう。
今回はプリンなので、プリント入力してみます。なお、Office.comのコンテンツを含めるにチェックを付けておきませんと、Office.comを検索しないので、ピックアップされてくる画像数が激減します。なお、インターネットに接続できていないといけません。
さて、検索して画像が見つかりましたら、OKボタンをクリックしましょう。
そうしますと、棒グラフが選択した画像に置き換わりましたが…

プリンの画像が間延びしちゃっていますね。これではイマイチですので、再びデータ要素の書式設定ダイアログボックスを修正していきます。
図の挿入の下に、拡大縮小と積み重ねがありますので、チェックを付けましょう。また、単位は今回500としてみます。

この500は絵一つを500ということで置き換えてくれますので、グラフによって調整しましょう。そうすると、グラフはこうなりますね。

プリンの棒グラフがプリンのクリップアートの絵グラフに変わりましたね。このやり方をケーキからアイスまでやっていきますと、アピールしやすいプレゼンしやすいグラフになりますね。

ただ、ここまでやったんだったら、もうちょっと絵を大きくしたいですよね。再度、棒グラフをクリックして、データ系列の書式設定ダイアログボックスを表示しましょう。

要素の間隔をなしにして、OKボタンをクリックしましょう。
そうしますと、もっと見栄えのいいグラフになりましたね。

必要に応じて、絵グラフに変えてみてはいかがでしょうか？

Excel。適当なデータをランダムで作る方法と値のコピー RANDBETWEEN関数

Excel。適当なデータをランダムで作る方法と値のコピー

RANDBETWEEN関数

仕事柄といいますか、このような文章を作ったりするときに、
表に適当な数字を入力して作ることがあります。
下記のような表とかがそうなのですが、

結構入力するのが面倒くさいんですね。皆さんの仕事でも、
つかえることがあるかもしれないので、
今回は、このようなランダムのデータを作る方法をご紹介しましょう。
この内容は、仕事でつかえるExcel講座では、ご紹介することはないのですが、
そこで使用するデータで、よく使っている方法なんですよ。

さて、早速ご紹介をしていきましょう。
今回は、RANDBETWEEN関数を使うと出来るのです。
一度、B2:F10までのデータを削除しておきます。
まず、B2をクリックして、RANDBETWEEN関数のダイアログボックスを表示してみましょう。

使い方は、いたって簡単。
最小値と最大値を入力するだけなんですね。
今回はテストの点数をランダムで作りたいので、
まぁ、40点～満点の100点までの数で作っていくとしたら、

最小値は40
最大値は100

と入力して、OKボタンをクリックしましょう。

そうすると、今回は、58と表示されましたね。

この数式をオートフィルを使って、下方向にコピーしていきましょう。

最終的に、F10まで数式をコピーしていきましょう。
さて、ランダムデータが出来ましたが、B2の値が？？？
58から84に変わっちゃいましたね。オートフィルでコピーしたら、変わっちゃいましたね。
まぁ別に困りませんので、いいんですが、ビックリしますよね。
ただ、このままでもいいのですが、ちょっとした時にデータが変わってしまうと、
このような文章とか資料やExcel講座では、困ってしまうので、
このデータで固定したいと思います。

数式を値に変えるには、コピーして値で貼り付ける。をすればいいわけですね。
では、こちらもやってみましょう。
まず、B2:F10を範囲選択しましょう。

コピーボタンをクリックして、そのまま、すぐに、貼り付けボタンの▼をクリックして、
アイコンから選択するのもいいのですが、分かりにくいので、

【形式を選択して貼り付け】を選択すると、
形式を選択して貼り付けダイアログボックスが表示されてきます。

貼り付けの値をチェックして、OKボタンをクリックしましょう。
では、確認してみましょう。B2をクリックして数式バーを確認してみると、
そのままの数が入っていますよね。

こうすれば、数式の結果が、直接数値にかわりますので、
数式の結果に左右されることなく固定することができますね。

コピー＆値の貼り付けは、ビジネスシーンでは有効なテクニックですので、
覚えておくといいですよね。