カテゴリー別アーカイブ: テンプレート

弾丸チャート作成マクロ

【今回は、プレゼンの場などで注目されること間違いなしの「弾丸チャート」を自動作成するマクロを紹介します】

読者の皆さんは、会議でこんなグラフを見かけたことはありませんでしょうか?

こんなグラフを出されると、どこから見始めていいかすら悩んでしまいます

本来、グラフは一目で必要な数字を分かるようなするためのものです

ですので、グラフは無駄な情報はなるべく省き、とことんシンプルにすべきです

「そうはいっても、表示すべき情報は5種類あるんですけど・・・」

という方には、ぜひこちらの「弾丸グラフ」をお勧めします

この弾丸チャートには、シンプルに魅せるための3つの工夫があります

①折れ線グラフは表示せず、グラフの形を統一する

②色種類を統一し、データの違いを濃淡で表現する

③テキスト表示を組み合わせる

この3つの工夫により、データ種類が5つあっても「一目で」伝わりやすくなります

今回は、この弾丸チャートを自動作成するマクロを提供します

ぜひ、プレゼンなどの場で有効活用してください!

使い方

データの入力

ファイルの中に「グラフデータ入力」というシートがあります

こちらのシートで数字データを入力します

上の画像で、赤い点を付けた箇所が「数字データ」を入力する範囲です

グラフの横軸に表示する内容(上の画像の東京等)は、A列の5行目から9行目までに入力してください

尚、横軸の内容の行数と数字データの行数に相違があると、グラフの表示などが狂うので注意してください

グラフの凡例の表記やデータラベル/テキストボックスの表記を変える場合には、下の画像の黄色のセルを変更してください

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

グラフ作成

実際のグラフ作成は「弾丸チャート作成」シートで行います

まずは、基調となる色を3つから選びます

 その後、「グラフ作成ボタン」を押すと「基調とする色」と「データの最大値」が反映されたグラフが作成されます

ここでいうデータの最大値とは、数字データの最大値です

こちらの最大値が軸の最大値になるような仕掛けになっています

次に、グラフの右にあるテキストボックスを各グラフの上に配置します

 もし、テキストボックスを配置するスペースが狭いとおもったら、グラフエリアを下のGIF画像のように上限に調整してください

 仮にテキストボックスの小数点の表示数を変えたい場合には「グラフデータ入力」シートの下の画像の箇所を変更してください

 例えば、小数点の第二位まで表示したい時には、TEXT関数の中の第二引数を「#,##0%」から「#,##0.00%」に変えます

<まとめ>

記事の冒頭でも解説しましたが、グラフはシンプルで見やすいのが一番です

今回紹介した弾丸チャートは、予実対比のようなデータ種類が多い場合には最適です

実は、記事の冒頭で紹介させて頂いた事以外にも、グラフの見た目をシンプルにするための工夫をしています

軸や目盛り線を表示せず、一目で数字が分かるように数字を横軸に表示したりしています!

残念なのは、今回のマクロだとデータラベルを手動で配置しなくてはならない点です

本来であれば、下のGIF画像のように自動で配置されます

データラベルも自動配置したい場合には、ぜひ過去の記事をご参照ください!

こちらの記事では、4つのグラフを複合して、弾丸チャートにする方法を解説しています

グラフが3つでなく4つなのがポイントです

4つ目のグラフは透明にしてあります

3つの系列の最大値(実績100、前年80、予算50の場合は実績)を透明なグラフにしてデータラベルだけ表示します

最大値を取ることで、データラベルが他のグラフに重ねないように表示できます

弾丸チャートは一見、難しそうですが、実は上記のような簡単な仕組みです

ぜひ記事を参照してチャレンジしてみてください

にほんブログ村 IT技術ブログ VBAへ


にほんブログ村

【グラフテンプレート】パワーポイントで動かすスピードメーターグラフ

 このブログではこれまで何度か、エクセルのスピードメーターグラフの作成の仕方を紹介してきました

 前からこのグラフのメーターの針を、パワーポイントのスライドショーの中で動かせたらインパクトのあるプレゼンが出来ると思っていました

 今回、パワーポイントでスピードメーターグラフの針を動かすテンプレートを作成しましたので、ご提供させて頂きます!

注意点

スピードメーターグラフには、様々な角度の種類があります

例えば、下の画像は針の範囲が180度動き、最初は270度の箇所から動きます

こちらは、針が240度動き、最初は240度から動きます

今回は、こちらのグラフのテンプレートになります

テンプレートの種類

ご提供させて頂くテンプレートは2種類になります

1つ目は計算用のエクセルファイル

2つ目はパワーポイント自体となります

何故、1つ目の計算用のエクセルファイルが必要になるかというと、理由が2つあります

上の画像はエクセルファイルで作成したスピードメーターグラフです

画面右に黄色く印をつけた箇所が2つあります

これはスピードメーターグラフは下地となるグラフと、針グラフの2つが重なっていることを示しています

2つのグラフともに、各種360度に変換する計算が発生するため、計算はエクセルで行います

例えば、最小値が0で最大値が100のグラフで入力値が50の場合があるとします

この場合、入力値を針の角度に直すと、計算式が(360-120)x50/100で120となります

ちなみに何故、360から120を引くかというと、下の画像のようにスピードメーターグラフの下地は見えるところと、見えないところに分かれているからです

テンプレートの使用手順

計算

計算用のエクセルファイルでまずは、3つの項目の設定が必要になります

・入力値➡針の値とする値(注:針の角度ではない)

・最小値➡下地グラフにて、時計回りで針が動く範囲の始点

・最大値➡ 下地グラフにて、時計回りで針が動く範囲の終点

上の3つを設定すると、自動的に2つの計算項目が算出されます

1つ目は下地グラフの各種値(こちらは後で、パワーポイントにコピーして頂きます)

2つ目は針の角度です

この2つの値は後程、使用方法を詳細に解説します

パワーポイント・グラフデータの編集

パワーポイントのテンプレートのグラフにて、右クリックし「データの編集」をクリックします

すると、下の図の画面が開きます

こちらは、前述の計算用のテンプレートで計算した下地グラフの各値をコピーして置き換えます

パワーポイント・針のアニメーション

下の図は、あくまでイメージですが、パワーポイントで動くメーターの針は三角形の図形を組み合わせ、片方は透明にしたものが動きます

アニメーションで動かす時には、「スピン」を使います

スピンする角度は360度ですが、下の画像の画面から修正することが可能です

対象のアニメーションの上で右クリックし、効果のオプションが表示されたら、こちらをクリックします

すると、ユーザー設定の箇所で角度を登録できるようになります

角度は、計算用のテンプレートからコピーしましょう

更に、グラフを分かり易くする為にテキストボックスを作成し、スライドインさせましょう!

<まとめ>

今回提供するテンプレートは、前述のように2種類あります

そちらは以下に添付します

こちらの2つのテンプレートは、前述の内容に従い、エクセルで各種計算➡パワーポイントに各種計算した値の設定、の手順で行ってください

それでは、ぜひ、スピードメーターグラフを活用した「臨場感」のあるプレゼンを楽しんでください

最後まで記事を読んで頂き誠にありがとうございました

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

にほんブログ村 IT技術ブログ VBAへ

にほんブログ村

温度計グラフテンプレート

今回は、温度計グラフのテンプレートを紹介します

自分でも作成して見たいという方のために、概要だけ紹介します

まず、2つデータを用意します

1つはダミーの値(100%)になります

 2つのデータから棒グラフを作成するのですが、次の画像の箇所で2つ目のデータを追加する形になります

次に、2つの棒グラフデータは過去の記事で紹介した方法で、重ねます

(この時、値のグラフは第二軸に変更しておきます)

系列のオプションで「系列の重なり」を100%にします

その後、ダミーのグラフの色は白にします

軸は第一軸と第二軸を2つとも表示します

目盛は補助目盛を温度計っぽく調整します

今回は以上です

ぜひ、プレゼンなんかにも活用してみてください

 他にもグラフテンプレートはありますので、そちらもぜひのぞいてみてください!

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

にほんブログ村

スピードメーターグラフ_テンプレート(散布図使用)

 これまで2回、スピードメーターグラフの添付レートを紹介してきました
1度目は円グラフを組み合わせたもの、2度目はVBAを使用したものです

今回紹介するのは、メーターの針を散布図で作成したものです

ですので、円グラフで針を作成するよりリアルなメーターになっています

最小値と最大値も調整ができるので、数字をメリハリをつけて示すことができます

下の図では値は35%のままですが、最小値と最大値を変えているのでメーターの針の角度が変わっています

ちなみにメーターは最小値と最大値の間の外には動かないようになっていますのでその点は注意して使用してください

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

XxXLookup関数で集計上手になる!

読者の皆さんがこれまで使用したエクセル関数の中で、一番使用頻度が高かったのはどの関数でしょうか?

多くの皆さんはVLOOKUP関数と答えるのではないでしょうか?

ところが、XLOOKUP関数の登場により、関数の使用シーンがこれから大きく変わっていきそうです

まだ使用できるエクセルバージョンは少ないかもしれませんが、それだけXLOOKUP関数の登場はインパクトがあります

特にXLOOKUP関数と他の関数を組み合わせることにより、データ集計の効率を格段に向上させることができそうです

今回は、XLOOKUP関数同士を組み合わせた例を2つ紹介します

どちらの例も、表を2次元で処理できるので、利用シーンが多そうです

①マトリクス表から2つの条件に合致するものを抽出する

条件を2つ(条件①部門x条件➁四半期間)をリストで指定し、2つの条件に一致する値を抽出する

➁マトリクス表でランク付けをする

事業の売上額と成長率、在庫商品の在庫額と回転率、などなど二次元でランク付けすることはよくあります

ところが、これまでは単に関数を使用するだけでは、ランク付けは自動ではできませんでした

尚、これからの解説は既にXLOOKUP関数を使用したことがある人を主として想定しています

但し、そうでない方もサンプルファイルをダウンロードすることで、そのままサンプルファイルの使用例を他の目的に利用していくことも想定しています!

1.そもそもXLOOKUP関数とは?

2つの事例を紹介する前に少しXLOOKUP関数について簡単におさらいをしておきましょう

サンプルファイルでは1シート目の内容です

サンプルの内容は部門毎に対応する部門長を別表から抽出するものです

XLOOKUP関数も、VLOOKUP関数と同じように検索値を基にして表からデータを抽出します(事例では北海道を検索値として、部門長の木村を抽出)

但し、VLOOKUP関数で行っていたような

「○○から右に何番目」

といった列数の指定が無く、検索する範囲(サンプルの部門)、戻り範囲(部門長)を指定しするだけです

つまり、 「○○から右に何番目」 の縛りがなく、検索値が参照表の左に無くてもいいし、列方向でなく行方向でもデータを抽出できます

引数は以下のような形で指定します

=XLOOKUP(検索値、検索値範囲、戻り範囲、【省略可】)

2.マトリクス表から2つの条件に合致するものを抽出する
XLOOUP関数の3番目の引数、戻り範囲にXLOOKUP関数を入れるのがポイントになります

仮に戻り範囲にXLOOKUP関数を入れなければ、いわゆるスピる(値があふれ出す)状態になります

以下が検索の軸を部門にし、戻り範囲を表の全範囲にした場合です

列方向でスピります

・=XLOOKUP(I3,B3:B9,C3:F9)*3番目の引数を全範囲

以下が期間を軸に、戻り範囲を表の全範囲にした場合です

行方向でスピります

・=XLOOKUP(I4,C2:F2,C3:F9)*3番目の引数を全範囲

この2つのスピりの交点が求まれば正解の値です

ですので、以下のように3段階でXLOOKUP関数を組み合わせます

A.部門の検索を行うXLOOKUP関数を作成

B.但し、3番目の引数に期間を検索するXLOOKUP関数を入れる

C.3番目の引数になるXLOOKUP関数は”期間の選択”に備えて戻り範囲=全範囲

最終的にはこのようになります

=XLOOKUP(I3,B3:B9,XLOOKUP(I4,C2:F2,C3:F9))

数式の内容はサンプルファイルにて、実際に入力されている内容を確認してくみてください

3.マトリクス表でランク付けをする

2.で行ったXLOOKUPによる関数では、4番目以降の引数を省略していました

ここで、5番目の引数を登場させます

VLOOKUP関数でも4番目の引数で検索方法を指定しています

FALSE=完全一致

TRUE≒完全一致

XLOOKUP関数では以下のように検索方法を指定できます

0⇒完全一致

-1⇒検索値の次に小さい値

1⇒検索値の次に大きい値

今回使用するの-1、検索値の次に小さい値です

以下の表で言えば、もし売上が30なら30より小さい値の0が該当し、前期比が15%であれば、10%が該当します

つまり、2.で行ったXLOOKUP関数の組み合わせに、検索方法-1を組み合わせれば2次元でのランク付けは、簡単に自動化できるのです

式は以下のようになります

=XLOOKUP(C5,$H$4:$H$6,XLOOKUP(D5,$I$3:$K$3,$I$4:$K$6,,-1),,-1)

企業ではABC分析の延長で、2つの変数(例:売上、利益率)で事業や商品を評価する機会が多いので、このXLOOKUP関数の組み合わせを行う機会は多いと思います

<まとめ>

今回は大きく分けて2つの事を解説しました

ⅰ)XLOOKUP関数を行列、双方向で組み合わせる(スピるの組み合わせ)

ⅱ)XLOOKUP関数の検索方法を工夫し、自動でランク付けをする

このⅰ)ⅱ)ができれば、データ集計がとても楽に行えます

ぜひ、有効活用していきましょう!

このブログではFILTER関数など、XLOOKUP関数と同じ新たなタイプの関数の解説も行っていますので、そちらもぜひ参照してみてください

尚、XLOOKUP関数が使えるエクセルバージョン(2020年9月19日時点)は限られており、OFFICE365ユーザー向けとなっています

2019バージョンユーザーなどは使用できないので、その点はまだ不便ではあります

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

にほんブログ村

The Fastest Way to learn Excel -VLOOKUP FUNCTION-

The fastest way to learn the Excel function is to see how humans operate and feel how it works.

This is because the Excel function outputs the result in an instant while multiple parts work together.

So I created a learning macro that you can study by yourself with Ninja

In this time, let’s try to learn VLOOKUP FUNTION!

↓DOWNLOAD↓

https://www.youtube.com/watch?v=ZZvZrIYjNgk

If this mark appear, please push enable button!

Thank you!

アイコンを使おう

 資料の中に表やグラフをいくら詰め込んでも、どこか資料に”インパクト”がないと感じる時がありませんか?

 そんな時は、資料の中でキーとなる数字にアイコンを使って資料にインパクトをつけてみましょう! 

 実は、アイコンは条件付き書式の中の機能です

とても簡単に作成することができます!

書式ルールの編集で”書式スタイル”をアイコンセットにするだけです

今回はアイコン作成のポイントを2つに絞って解説します

1.種類、値の指定

 値に応じて表示されるアイコンに変化がつくようにしましょう!

アイコンスタイルを選ぶと、表示されるアイコンに対応した値と種類が選択できるようになります

 書式ルールの編集画面を読み込んだ際に、種類はパーセントがデフォルトでセットされていますが、数値にしておいた方が値が設定し易いです

2.アイコンのみ表示

 書式ルールの編集画面を読み込んだ 際にはセルの値も自動表示されるようになっています

セルの値も表示すべきかどうかはケースによりますが、アイコンのみ表示のチェックを外すと、セルの値は表示されません

 最後にアイコンの使用例を紹介します

これはピボットテーブルに信号のアイコンを組み込んだ例です!

興味のある方はぜひこちらの記事を参考にして下さい

後、こちらはスマホのダッシュボードです

全部エクセルで出来ています!

こちらも解説記事があります。ぜひご参考にしてください!

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

にほんブログ村

2つの指標も楽々比較~双方グラフ~

 読者の皆さんの中でも、”売り上げと利益”などの複数の指標を組み合わせたグラフを作成している方も多いかと思います

 この複数の指標をグラフ化する場合、よく見られるのは上のような棒グラフと折れ線グラフを組み合わせたグラフだと思います。

 このグラフだと作成は確かに簡単ですが、見る側は指標にあわせて第一軸と第二軸を追いかけて数値を確認せねばならず、あまり見やすいとは言えません

 比べて、下の双方向グラフだと”グラフの形”が統一されているので、とても見やすくなります 

 この双方向の形だと、とてもシンプルなので、上の方へのプレゼンの際にも説明が行い易いと思います!

ぜひ、以下のテンプレートをご活用ください!

作成の仕方はまだ解説ページを作成していませんが、今度、作成した際に通知を致します!

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

にほんブログ村

複数のグラフを一括表示

読者の皆さんの中には、資料の中にグラフが多すぎて辟易した経験がある方も多くいらっしゃると思います

今回、提供するテンプレートでは、HyperLInkとマクロ、並びに、条件付き書式を使い、1つのグラフで複数のグラフを提供できるようにします

このテンプレートが多くの人の業務の効率化に役立つことを願っております

ちなみに詳細な仕組みを知りたい方は、ぜひこの記事をご参照ください!

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