Excel。パレート図の作成にはグラフノウハウてんこ盛り！ その3 パレート図の折れ線グラフを0/0交点から

Excel。パレート図の作成にはグラフノウハウてんこ盛り！
 その3

パレート図の折れ線グラフを0/0交点から

前回ご紹介したABC分析（パレート図）の続きの作業をご紹介していきましょう。
今回は、いよいよメインディッシュ？！

マーカー付折れ線グラフをX軸Y軸のそれぞれ0/0交点からスタート

するように修正していきましょう。

なお、このテクニックは以前ご紹介したことがありますが、
今回は作業の流れということで、再登場させました。

さて、今は下記のようなグラフになっていますね。

まず、マーカー付折れ線グラフのデータ範囲に0%を加えてあげる必要があります。
そこで、よくやる方法として、表に0%の行を追加して、
その行をデータに加える方法もあるのですが、
それだと、表を結構加工しないといけませんですので、
ちょっと、Excelの特性を生かした方法を取るといいかなぁ～と思います。

とりあえず、修正をしますので、グラフをクリックして、
デザインタブのデータの選択をクリックしましょう。

そうすると、データソースの選択ダイアログボックスが表示されてきます。

このダイアログボックスは、データソースを修正するダイアログボックスなのですが、
今回は累計のデータの範囲を変更したいので、累計を選択して、
編集ボタンをクリックしましょう。

すると、系列の編集ダイアログボックスが表示されてきます。

系列値を修正していきますので、一度、全部削除して、累計の見出しの「累計」から
100%の郡山までを範囲選択します。

=ABC!$D$1:$D$20

このような式になります。
ポイントは、D1の累計という見出しを含める事なんですね。
文字は0と判断されますので、こうすることによって、
0%という行を追加しなくても大丈夫なんですね。

あとは、OKボタンをクリックしましょう。
データソースの選択ダイアログボックスに戻りますので、ここもOKボタンをクリックしましょう。

グラフを確認すると、マーカー付折れ線グラフは、
X軸は0になりましたが、Y軸は0になっていませんね。完成までもう一息です。

さて、このあと、どうしていけばいいでしょうか？
X軸すなわち、項目軸からスタートしていますので、
項目軸にヒントがあるのではと思う方も多いと思います。

では、項目軸をダブルクリック。
あるいは、クリックして、選択対象の書式設定をクリックすると、
軸の書式設定ダイアログボックスが表示されてきます。

軸のオプションの一番下にある、軸位置を目盛の間になっているので、
Y軸に着いていない訳なので、ここを目盛に変えるといいんじゃないかと思う訳ですね。

しかし、結果はどうなったかというと、見た目、成功！と思えるのですが、

よく見ると、大阪まで移動してしまっていて、最後の郡山までずれちゃっている。
これでは、せっかくX軸Y軸ともに0となったのに、項目軸自体がずれてしまいました。

アイディアはいいのですが、ここでポイントがあるのです。

おっと、その前に、元に戻すボタンで戻しておきましょう。

このマーカー付折れ線グラフって、そもそもデータが見えないので、
第2軸を使って見えるようにしたわけですよね。

実は第2軸って縦軸だけじゃないんですね。

第2軸にした段階で、横軸も第2軸になっている訳です。
そこで、次のような処理をしてから、先程の処理をしてあげればいいのです。

グラフをクリックして、レイアウトタブの軸の▼をクリックして、
第2横軸に合わせて、ラベルなしで軸を表示をクリックしましょう。

次は、ダブルクリックではうまくいきませんので、

レイアウトタブの現在の選択範囲の第2軸横（項目）軸に合わせて、
選択対象の書式設定をクリックして、軸の書式設定ダイアログボックスが表示しましょう。

軸のオプションの一番下にある、軸位置を目盛に変えて閉じるボタンをクリックしましょう。

これで、パレート図が完成しましたね。

Excel。パレート図の作成にはグラフノウハウてんこ盛り！ その2 パレート図をクリンアップ

Excel。パレート図の作成にはグラフノウハウてんこ盛り！ 
その2

パレート図をクリンアップ

前回ご紹介したABC分析（パレート図）の続きの作業をご紹介していきましょう。
今回は、グラフをクリンアップしていきましょう。

ここからのテクニックはグラフを作っていく上でのノウハウが散りばめられていますので、
初心者さんの講座でも、手間暇かかりますが、
ゆっくりご紹介しておりますし、

プレゼンなどの資料作りでも必須のスキル

なので、
仕事で使えるExcel講座でもご紹介しております。

これで、一応。パレート図としては完成しましたが、
カッコ悪いので、まず左右の軸から調整していきましょう。

左側の第1軸は値が大きいので表示単位を万円に変更するのと、
右側の第2軸は、最高値が100%なので、最高値を100%に変更していきます。

左側の第1軸をダブルクリックあるいは、クリックして、
デザインタブの選択対象の書式設定をクリックすると、
軸の書式ダイアログボックスが表示されてきます。

軸のオプションの表示単位▼から万を選択しますと単位表示が万に変わります。

しかしよく見ると、万という表示が横を向いちゃっていますね。
今度は、この万を横向きに変更していきましょう。
万をダブルクリック、または、クリックして、レイアウトタブのして、
選択対象の書式設定ダイアログボックスをクリックすると、
表単位ラベルの書式設定ダイアログボックスが表示されてきます。

配置の文字列の方向を横書きにして閉じるボタンをクリックすると、万が横書きになりましたね。

次は、反対側の右側の第2軸を修正していきましょう。第2軸をダブルクリック、
またはクリックして、レイアウトタブの選択対象の書式設定をクリックしましょう。

軸のダイアログボックスが表示されてきますね。

軸のオプションの最大値を固定で1に変更します。
100%が最大値ですから、1ですね。

最近、集中講座で数学が苦手だったという方が多い時は、
1が100%ということを確認しておきませんと、ここで、ハテナが頭の上に出ちゃいますので、

ちゃんと、説明をしてあげております。あとは、閉じるボタンをクリックしましょう。

ちゃんと、最大値が100%になったのが確認できましたね。
続いて、棒グラフが細いので、きしめんのように太くしてみましょう。

そのほうが、分かりやすくなりますね。ヒストグラムを作るときのテクニックですね。
まず、棒グラフをダブルクリック、

または、クリックして、選択対象の書式設定をクリックしましょう。

系列のオプションの要素の間隔をなしにすると、
棒グラフの幅がきしめんみたいに太くなりましたね。

あとは、ABCで色分けしましたので、棒グラフの色も変えていきましょう。

これは、まとめてできませんので、一つずつ棒グラフをクリックして色を設定していきましょう。

なお、この一つずつ選ぶときには、棒グラフをまず、クリックして、
そのあと、もう一度クリックすると、一つずつ選ぶことが出来ますよね。

さぁ、あとは、

マーカー付折れ線グラフを0/0交点から始めるようにしたいですね。

それは、また次回にご紹介することにしましょう。

Excel。パレート図の作成にはグラフノウハウてんこ盛り！ その１

Excel。パレート図の作成にはグラフノウハウてんこ盛り！ 
その１

パレート図

前回ご紹介したABC分析（パレート図）の続きの作業をご紹介していきましょう。
今回は、グラフ化。
つまり

パレート図

を作ってみようという訳です。
このパレート図を作ることはないなぁ～という方も多いと思いますが、
この作成手順の中に、グラフに関するノウハウが多く詰まっていますので、
実は、作れるようになると、ググッとExcelグラフのテクニックが上昇しちゃうわけですね。

ですので、手間暇かかりますが、仕事で使えるExcel講座でもご紹介しております。

それでは、前回は条件付き書式を使って、A/B/Cのそれぞれで、
行で色分けできる設定をしてきた表を使っていきます。

まずは、範囲選択をしていきましょう。A列の店舗名とB列の金額と、D列の累計を選択します。

挿入タブの縦棒グラフの集合縦棒をクリックして、集合縦棒を作成しましょう。

まず、グラフが小さいので、グラフを大きくしてみましょう。
または、グラフだけを新しいシートに移動してもいいですね。

今回は、グラフだけを別のシートに移動してみます。
グラフを選択して、デザインタブのグラフの移動をクリックしましょう。

そうすると、グラフの移動ダイアログボックスを表示されます。

新しいシートにチェックをして、シート名をパレート図としてOKをクリックすると、
パレート図という新しいシートが出来て、グラフだけが移動しましたね。

凡例を下に移動しましょう。凡例を選択して、
凡例を下に配置をクリックしましょう。凡例が下に移動します。

累計の数値が小さすぎるので、棒グラフが全く見えない状態にありますね。
このような時には、右側に第2軸を表示させることによって、表示させることが出来ます。
それでは、やっていきましょう。

ポイントなのは、累計が小さすぎて触れないので、レイアウトタブか書式タブのどちらかにある、
グラフの要素ボックスの▼をクリックして、系列累計をクリックします。

そして、

選択対象の書式設定のボタンをクリックすると、
データ系列の書式設定ダイアログボックスが表示されてきます。

系列のオプションの使用する軸の第2軸にチェックを付けて閉じるボタンをクリックしましょう。
そうすると、今まで、見えなかった、累計の棒グラフが見えるようになってきましたね。今触っている状態ですので、このままにして、グラフを棒グラフからマーカー付折れ線グラフに変更していきます。

デザインタブのグラフの種類の変更をクリックすると、グラフの種類の変更ダイアログボックスが表示されます。

マーカー付折れ線グラフを選択してOKボタンをクリックしましょう。
棒グラフだった累計がマーカー付折れ線グラフに変わりましたね。

これで、まずは、パレート図は完成しましたが、見た目がカッコ悪いので、
次回はこの続きを紹介していきます。

Excel。条件付き書式を行に設定するのには複合参照が必須です。条件付き書式と複合参照

Excel。条件付き書式を行に設定するのには
複合参照が必須です。

条件付き書式と複合参照

前回ご紹介したABC分析（パレート図）の先の作業にあたるのですが、
Excelのスキルの中で、簡単そうなんだけど、ちょっと面倒くさいというスキルに、

条件付き書式を行に設定する

というのがあります。
どういうことかというと、下記の表で、E列のランクがAだったら、その行、つまり、

そのレコードに対して条件式書式を設定していきたいわけです。
E2がAなので、そのE2だけに条件付き書式を付けるのは、簡単ですが、
今回は、E2がAだったら、A2:D2にも条件付き書式がアクションしてくれるようにしたい訳です。

こんな感じになるという事ですね。

実は、これがなかなか大変なんですね。
条件付き書式のテクニックだけではダメで、複合参照のスキルも必要になってくるんですね。

今回ご紹介するテクニックは、企業研修でも質問が出ますし、
仕事でつかえるExcel講座でも、ご紹介しております。

ただ、初心者さんの講座でもご質問をいただきますが、
絶対参照と条件付き書式の基本がわかっていないと判断するときには、
即答を避けて、しっかりスキルが定着してからご紹介するようにしております。

パターンとして覚えていても、Excel力がアップしなければ結局、現場では使えませんのでね。

では、早速紹介していきます。

まずは、A2:D20を範囲選択しましょう。ホームタブの条件付き書式をクリックして、
新しいルールをクリックしましょう。

そうすると、新しい書式ルールダイアログボックスが表示されてきます。

この中の一番下にある【数式を使用して、書式設定するセルを決定】をクリックします。

この【数式を使用して、書式設定するセルを決定】を使うことによって、
行全部を該当する条件で書式を反映することが出来るようになりますし、
あと、【数式を使用して、書式設定するセルを決定】を使うことによって、
高度な条件付き書式を設定することも可能になります。

この

【数式を使用して、書式設定するセルを決定】

を使いこなせると、いいですよね。

本来なら、書式ボタンは、あとで設定するのですが、
今回は、数式の作り方が大切なので、この書式ボタンから書式を設定する方法のご紹介を

先にしちゃいましょう。
書式ボタンをクリックします。

今回は塗りつぶしを設定しますので、
塗りつぶしタブをクリックしてお好みの色を選択してOKボタンをクリックすると、
先程の新しい書式ルールダイアログボックスへ戻ります。

さて、次の数式を満たす場合に値を書式設定のボックスに数式を作っていきましょう。

数式を作っていくわけですが、ポイントとなる点があります。
それは、通常関数などの数式を作ったあとに、オートフィルなどで数式をコピーするわけですよね。ところが、条件付き書式は、先に、範囲選択をして数式を作っていくことになりますので、

それぞれの行が事前に条件を満たすように考える必要があるということです。

つまり、オートフィルするイメージで数式を作る必要があるわけです。

例えば、E2がAだったら、塗りつぶしをするというルールを作っていくわけですね。
そこで、E2=”A”という式を入れてみると、どうなるでしょうか？やってみましょう。
まず、E2をクリックします。そして、=”A”と入力しましょう。Aは文字なので、””で囲いましょう。

数式は、=$E$2=”A”となりますね。

OKボタンをクリックすると、

ありゃま、全部赤！お気づきの方もいると思いますが、先程の数式。
=$E$2=”A”。E2が絶対参照になっているので、
範囲のセル全部がE2がAだったらという条件が成立してしまったので、
書式が反映されたわけですね。けど、これじゃ、全くダメなわけですね。

それでは、元に戻して、考えてみましょう。

それぞれの行が、E列を参照してくれればいいわけですよね。
E列がAですか？というようにしたい訳ですね。

ということは、E列が止まっていて欲しいわけですね。

で、行番号はオートフィルした時に変わるイメージなわけです。

そこで、絶対参照ではなく、複合参照を使うと、希望する数式が作れるわけですね。

=$E2=”A”

$マークがついているほうが、参照が固定されるわけです。

それでは、OKボタンをクリックしてみましょう。

ランクがAの行だけに塗りつぶしの書式が反映されてましたね。
同じ方法で、数式を、それぞれ、
=$E2=”B”
と
=$E2=”C”
として、条件付き書式を設定すると、

ABCで色分けできましたね。

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でいいと判断されましたが、これはいかがなものでしょうか？
最後にエボリューショナリーだと、

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