analytic-vba のすべての投稿

エクセルで弾丸チャートを作成しよう 2回目

今回はグラフを見やすくする為の秘訣のひとつ、”色をシンプルにする”を中心に解説します

下のグラフには何色の色種類が使われていますか?実績、前年・グラフで使用する数字種類にあわせて実に5種類もの色種類が使われています。これでは情報が多すぎて、見る方は混乱します。

アイコン

理想は黒白で1種類、他に2種類ぐらいだと思います

種類を絞ったら色の濃淡で調整するのがいいでしょう!

<今回行うこと>

サンプルデータをもとにして棒グラフを作成したら、色種類を絞り込む便利技を解説します

1.棒グラフを作成する

2.色種類を絞り込む

では、演習に入りましょう

1.棒グラフを作成する

サンプルをダウンロードしたら、演習のデータをもとに棒グラフを作成してみてください

キャプチャ1

そしたら、以下のようになりませんか?

キャプチャ2

自働的に色種類が3種類できていますよね?

2.色種類を絞り込む

1で作成した棒グラフの色種類を一気に絞り込みます!

  グラフをクリックしながらデザインタブ⇒色の変更とすると”モノクロ”を選択できるようになります

これで、色の濃淡で数字の種類を表せるようにできます

<まとめ>

色の種類を多く使えば、使うほどグラフが分かり易くなると思っていた方もいらっしゃったかもしれませんが、実は、色種類は絞り込んだ方が見やすくなるんです。せっかくエクセルには色種類を絞り込む機能が備わっているので有効に活用しましょう!

では3回目では目盛り線に頼らない裏技を解説していきます
にほんブログ村 資格ブログ ビジネススキルへ
にほんブログ村
にほんブログ村 IT技術ブログ VBAへ

エクセルで弾丸チャートを作成しよう 1回目

 会議で業績を確認する資料として、無造作に前年実績・前年比・予算設定額・・・などの各種数字が並んだグラフをよく目にします

アイコン

 見やすいように色を変えたり、折れ線グラフを取り入れたりしますが、色やグラフ種類が多すぎて、かえって見ずらいグラフになったりします。

 そんな時に便利なのが弾丸チャートです

 複数の種類の数字を詰め込んでもシンプルさを保てるエクセルグラフです!

GIF1

左のグラフとの違いが分かりますでしょうか?

 単純に形が違うというだけでなく、右のグラフには”グラフを見やすくする工夫”が詰め込まれています

 今回のシリーズでは、単に弾丸チャートの作成の仕方を解説するだけでなく、以下のようなグラフそのものを見やすくする工夫を学ぶ機会になれば幸いです

Ⅰ.色をシンプルにする ⇒ 2回目

Ⅱ.目盛線をなくす ⇒ 3回目

Ⅲ.グラフの形を統一して重ねる ⇒ 4回目

Ⅳ.グラフの上にメッセージを表示する ⇒ 7回目

では、

次の回から、上記の工夫を解説しながら弾丸チャートの作成方法を解説していきます

 

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

ワッフルチャートを作成しよう 3回目

前回で仕組みは分かったと思います

ワッフルチャートを作成するのにすることは2つだけ

1.数字を均等に、且つ、順番に散りばめること

2.条件付き書式で1で作成した、数字が入ったセルの書式を変えることです

今回はワッフルらしく、且つ、グラフらしくする仕組みを解説します

実はポイントは3つだけです。

1つは同じ数字を全部で4つ作成しておきます。そして、行と列を狭くするのです
4

そうすれば、数字が引き立って見えてきます

そして2つ目、

5

罫線はセルの片側の色を濃くし、セルを立体的にしましょう!

この2つを実践すればワッフルチャートは”らしく”なります

余裕のある方はサンプルで上記の2つのポイントを確認してみてください

では!また次回

 

ワッフルチャートを作成しよう 2回目

今回は以下のような簡単なチャートを作成しましょう

GIF2

仕組みは簡単です。条件付き書式を使うだけです

条件付き書式とは、選択した範囲が条件通り、もしくは条件通りでなければ”書式”を変えるというものです

ですから、条件と書式の両方を指定します

まず、以下のようにホームタブから⇒条件付き書式、そして新しいルールをクリックしましょう

1

次に条件を指定しましょう

上記の例だと黄色の枠の中(E2セル)以下の値の場合は書式を変えるようにしましょう

2

そして、書式を選択して、好きな書式を指定しましょう

3

これで終了です

今回は演習用のデータとサンプルもぜひご利用ください

次回がワッフルチャートの演習の最終回となります

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

ワッフルチャートを作成しよう 1回目

エクセルではグラフだけが”グラフ”ではありません

GIF1

このワッフルチャートはエクセルのグラフメニューを使わずに作成できます

本来、グラフの目的は数字の意味合いを分かり易く伝えることです

数字の意味合いは多様です

同じ”1”でも数字の意味合いは様々です

利益率が90%の企業にとっての1%と10%の企業の1%とは違います

同じ1年でも創業2年目の1年と創業100年の企業の1年とでは意味が違います

当然、グラフの種類も意味合いに応じて多様で構わないのです

次回から本格的に作成の仕方を解説します

スピードメーターグラフを作成しよう8~データラベル~

今回(8回目)は前回のメーターグラフの色を変えつつ、針に入力値が表示されるようにしましょう

GIF1

色を変えるだけなら、とても簡単です

サンプルをダウンロードして、グラフの第2軸を変えましょう

1

そうすると”値1のグラフ”の各パイの色を変えることができるようになります

問題はどうやって針の表示を変えるかです

お気づきの方もいらっしゃるでしょうが、針の値は固定になっており、実質は他の値により動いています

ですから、残り1の値、針の値、残り2の値の表示順番を変えることが必要です。

言葉だけでは分かりにくいと思いますので実際に手を動かしてみましょう!

まず”凡例項目”の値2をクリックしながら横(項目)軸ラベルの編集をクリックしましょう

3

そして、”Ctrlキー”を押しながら以下のように分類の数字を順番を変えながら指定しましょう

2番目に指定する値は残り1の入力値にするのです

GIF2

反映された値には”,”がついているはずです。針に反映せれている値には”残り1”の入力値(15%)のセルが指定されています

2

ここまで来たらデータラベルを”分類”で表示しましょう!

4

隠れたコツは”針”のみをはっきりとクリックしてから処理しましょう!

そうすれば、針だけデータラベルが表示されます!

今回、スピードメーターグラフを作成しようは最終回となります

また次回、別のテーマでお会いしましょう!

スピードメーターグラフを作成しよう7~グラフの完成~

前回で以下、2つの調整値を算出しました

1.調整値1

初値(10%)が入力された場合に0度に変換する

2.調整値2

終値が(20%)が入力された場合に調整値1を基にして10%に変換された値を240度(グラフの大きさ)に変換する

上記の1と2を図にすると以下のような形になります

では実際に調整値1と2を針の動きに反映していくのですが、本題に入る前に、まずは元データとグラフの連携について、概要を解説しておきます

ちなみにサンプルは前回と同じものを使用します

<元データとグラフの連携・概要>

1.残り1の入力値(セルB9)を調整して値2(セルC9)に反映

 ⇒残り1と残り2の値に合わせてグラフの針が動くようにします

  *値2の3つの合計は常に360度になるようにします

2.残り1の値2には調整1と調整2を組み込んでおきます

元データとグラフの連携について解説したので、本題に入りましょう!

Ⅰ.調整1と調整値2の反映

残り1の値2に(セルC9)に以下の数式を入力します

(残り1の入力値+調整値1)X調整値2

⇒=(B9+F2)*H4

Ⅱ.グラフの動きのチェック

 正しく値2が設定できたら、入力値(セルB9)を動かしてみましょう!。入力値の変化とともに、グラフの針が以下のGIFのように動くはずです

GIF1

では最終回の8回目では、おまけの機能を紹介します!

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


にほんブログ村

エクセル関数って本当に奥深いね

こんなブログを書いてるぐらいだから、当然エクセル関数は詳しいんはずだけど、まだ色々と知らないことあるね

人の作ったエクセルをいじる時にイラつくのは、行と列の違いね

いちいち行だったものを列に、列だったものを行にするんだけど

Transpose関数はエクセル関数の中でもあまり知られていませんがとても便利です

Transpose関数にCtrl+shift+Enterで配列関数にすれば簡単に行と列を変換できます

とても勉強になりました

 

 

スピードメーターグラフを作成しよう6~元データの工夫~

前回に引き続き、応用編を続けます

自由にメーターの範囲、単位(-100~100、10%~20%など)を自由に設定できるようにする為に、

入力値を角度(最大360度)に変換しながら、”2つの調整値”を活用できるようにします

<今回解説するグラフ元データの工夫>

1.初期値の調整値を算出する

初期値が”0度”になるように調整します

2.終了値の調整を算出する

終了値がスピードメーターの針の、最大角度になるように調整します

今回の事例では、時計回りに240度、スピードメーターの針を最大で240度回転させるスピードメータグラフにて、10%(初期値)から20%(終了値)までを入力範囲に設定します

図でまとめると以下になります

3

これからの手順は以下です。とにかく円グラフは最終的に360度になるということを意識しましょう

➀ 10%(初期値)を0度に変換する調整値を設定する(調整値1反映:-10%)

② 20%(終了値)を調整1で調整した値を算出する(調整値1反映:10%)

③ ➁で算出した値を240度(グラフ全体の大きさ)に変換する調整値2(調性値2反映:2400)を設定する

今回は以下のサンプルを使用します

ちなみに、入力値に対してスピードメーターの針の角度がどうなっているべきなのか?を分かり易くするために、”目安”をサンプルに書きました。

”ピンク部分””が120度(入力値:0%~15%)⇒累計120度、”赤部分”(入力値:15%~20%)が120度⇒累計360度、そして自動的に”無色の部分””が120度⇒累計360度になり、合計して360度になります

1.初期値の調整値を算出する

まず手順➀からですが、以下の式(C2-B2)をサンプルに入力してください

値1(0%)を引くことで-10%が算出されます

これが調整値1です

入力値に調整値1を加算するようにしておきえば、10%を入力した場合には0に調整されます。つまり0度から針がスタートするようにします

次に手順➁です!

サンプルファイルのG列にて、20%(終了値)を調整値1で変換しましょう

そうすると10%に変換されているはずです。

最後に終了値(入力値では20%⇒調整1で調整後は10%)を240度に変換する調整値2を算出しましょう!

調整値2は、240度を10%で割って算出します

すると2400という調整2が算出されます

これで調整1と調整値2が求まりました

次回(7回目)では調整値1,2を活用して実際に針の動きに反映させましょう!

スピードメーターグラフを作成しよう5~より伝わるグラフにする~

今回から応用編を開始します。

 基本編では、スピードメーターグラフの作成の仕方自体を学びました。

 応用編では、スピードメーターの形を最大限に活かして、伝えたい数字の意味合いを、より臨場感を伴って表現できるようにします。

 この記事の読者の皆さんは、数字をグラフにして表現する機会が多いのではないかと思います。

 会議などで、伝えたい数字の意味合いが伝わらず、はがゆい思いをされた方も、中にはいらっしゃると思います。

 実は、数字には温度があります。伝えるのが難しいケースの1つは、数字の持つ温度を適切に表現する必要がある時です。

 例えば、夏に外から、部屋の温度が25℃に設定されたオフィスに戻ってくると涼しいと感じます。ところが、冬は同じ25℃が温かく感じます。このギャップは、温度を比較する基準が夏と冬とで違うためです。

 実際のビジネスでも、温度の問題は沢山あります。典型的な例が利益率の問題です。利益率が90%の企業と20%の企業を比較してこの温度の問題を考えてみましょう!

 利益率が90%の企業の場合、利益率1%の重みは、1/90です。利益率が1%下がってもあまり深刻な事態になりません。一方、利益率が2%の企業が1%下げた場合を考えてみましょう。利益率1%の重みは1/2となります。90%の企業と比べて、90÷2倍の重みがあります。

 よって同じ1%の違いでも、比較する基準が違うため、利益率が90%と2%の企業では天と地ほど意味合いが違うのです。

 では、グラフでこの温度の違いを表現するにはどうしたらよいでしょうか?

 実は、この応用編では、温度の違いを表現する方法を2つ用意しています!

 読者の中には、”グラフの軸の最小値と最大値を調整すれば温度の違いを表現できる”と考える方もいらっしゃると思います。

 確かに棒グラフであれば、軸の最小値と最大値を変えれば、棒グラフに対する比較基準を変えることができます。

 では、スピードメーターグラフではどのようにしたらよいでしょうか?スピードメーターグラフは円グラフの組み合わせで出来ていて、軸の基準変更は行えません。

 但し、針の振れ幅の最小値と最大値を調整できれば、温度の違いを表現するのに、スピードメーターグラフはぴったりな感じがします!

 実際に、実例で見てみましょう!

 下の画像は、営業利益率が前年の5%から、4%に下がったことを表したグラフです。

 左も右も、何ら違いがあるように見えません。何故なら、グラフの最大値が100%に設定されているからです。

 では、次の画像を見てください!

グラフの最大値が100%でなく、10%で設定されているので、前年との違いが”明確”に分かるはずです。

 このグラフの最大値、もしくは、最小値を変えるのが、温度の違いを表現する1つ目の方法です。

 では、2つ目は何かというと、通常のグラフで言えば、目盛線の役割をするものです。

    例えば、上記の画像内のグラフは3つのパイからできています。これが、通常のグラフで言えば目盛線の役割をするものです。スピードメーター内で言えば、ゾーンと言えるものです。

 安全ゾーン、危険ゾーン・・・その他、ゾーンを活用すれば、色々なストーリーをグラフを通じて語れそうです。

 例えば、上の画像なら、

”今年は営業利益率が4%まで下がりました。但し、来年は○△✕の施策により7.5%まで上げることが可能です。そして、再来年は10%まで上げましょう”

などのような形で、これからの施策と絡めてストーリーを語れます。

 ではここで、これから応用編で解説する内容を少し、前倒しで解説しておきます。

 実は、スピードメーターグラフの最小値と最大値を変更するのは、そんなに難しくないんです。

 下のGIFの動きを見てください!スピードメーターグラフの最小値と最大値を以下のように設定しています。

  最小値:入力値10% ⇒ グラフは0度回転

  最大値:入力値20% ⇒ グラフは240度回転

まるで、グラフの軸を調整したかのように動いているはずです。

GIF1

要は、グラフの参照元のデータを工夫すれば、温度の違いを表現するのは難しくないんです。

 では、次回から応用編の解説を本格的に開始して、数字の持つ温度が熱を持って伝わるようなグラフ作成をしていきましょう!

では6回目でお会いしましょう!

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