タグ別アーカイブ: エクセルダッシュボード

グラフを効率的に作成する3

では今回は前回から引き続き”データの選択”について解説します

今回の対象は凡例項目横(項目)軸ラベルになります

11

上の画像の左側に赤印をした箇所を「凡例項目」、右側に赤印をした箇所を「横(項目)軸ラベル」として解説を行います

1.凡例項目を使いこなす

凡例項目を使いこなして、下のグラフに2017の数字を加えてみましょう

12

追加を押すと、どのデータを追加するのかを指定できるようになります

14

追加を押した後は系列名系列値に分けて指定を行います

GIF5

これで2017の数字が追加されました

そして、順番を変えるには以下の▼▲(凡例項目の右端)を操作しましょう

GIF8

2.横(項目)軸ラベルを使いこなす

横(項目)軸ラベルほど使う機会がありませんが、知っておくと便利です

データの範囲を指定しなくても、表示項目のチェックの入れ替えだけで調整することができます

GIF7

上の画像では、「横(項目)軸ラベル」の部門Cのチェックを外すだけで、グラフから部門Cの表示が消えています

今回は以上です

また次回をお楽しみに!

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

関数をマスターする6~MATCH関数&INDEX関数2~

今回は前回に引き続き、MATCH関数とINDEX関数に取り組みます

今回は、名前管理の機能を使ってより実践的な活用方法に取り組んでみましょう!

もし、分かりづらい部分があれば下記のサンプルファイルをダウンロードして自分で触って動かしてみましょう!

関数を理解するための一番の近道は”手を動かす”ことです

ところで、

読者の中には「MATCH関数とINDEX関数の組み合わせはVLOOKUP関数と似ている」と感じた方もいらっしゃると思います

MATCH関数とINDEX関数の組み合わせがどう”VLOOKUP関数”と違うかという点も合わせて解説していきます(VLOOKUP関数は行方向に検索しますが、列方向には検索できませんので、その点は省略させて頂きます)

では、

 下の画像にある表から、選手の名前に応じて学年から出場回数を抽出する関数を作成していきましょう!!

18

INDEX関数とMATCH関数は下の画像の黄色の印を付けた箇所に入力をします

1.まずは表の見出しに対応した名前をつけよう!

選手~出場回数までの各列に、エクセル画面上の名前ボックスにて、そのまま見出し/列名を名前につけましょう!

GIF9

各列に名前を付ける理由について、1つ解説します

もし、名前を付けない場合は、選手の学年を抽出する関数はどんな風になるかというと以下のようになります

=INDEX(C$2:C$11,MATCH(C$13,B$2:B$11,0))

一方、名前をつけると下記のようにセル範囲の部分が置き換わります

=INDEX(学年,MATCH($C$13,選手,0))

名前を付けたほうが一目見ただけで意味が分かりますよね

”Cの列は何だっけ?”などと表を参照する必要はありません

では、以下の画像の表の空欄”=INDEX(学年,MATCH($C$13,選手,0))”と記入して、赤字の選手名に対応して「選手の学年」を関数で抽出できるようにしましょう

20

2.関数を下まで(出場回数まで)コピーして、関数の名前を修正

関数をコピーした後、関数の名前を抽出対象にあわせて修正しましょう

21

22

もし、1.の段階で名前を付けていなかったら、セルの列や行位置などを修正する必要があった(もしくは1.の段階で絶対参照を設定しておく必要があった)ことを考えると、名前を付けることが省力化につながることが良く分かると思います

3.表に列や行を追加してみましょう!

VLOOKUP関数でも同じように名前をつけて表から抽出することができます

23

ここで、表に行データ/5行目を追加してみましょう!

24

VLOOKUP関数でも行追加に対応できています

今度は列を追加してみましょう!

25

するとVLOOKUP関数の列参照が狂ってしまいます・・・

なぜなら、VLOOKUP関数の列参照は整数で設定しているからです

対して、INDEX関数とMATCH関数は、あくまで参照する値に応じて検索が行えます

この点はMATCH関数とINDEX関数の組み合わせとVLOOKUP関数の大きな違いです

この”違い”を理解した上で、場面に応じてMATCH関数とINDEX関数の組み合わせてとVLOOKUP関数を使い分けていきましょう!

次回OFFSET関数について解説します


にほんブログ村

イラストのようなフォントを使いこなす

今回は無味乾燥な表にメリハリとパンチを付け加える小技を解説します

下のGIF画像のGoodマークBadマーク、実はイラストでなくてフォント、つまり文字なんです

フォント

こちらは特殊なフォンをエクセルダッシュボードに組み込んでいるのですが、これは下の画像の表から抽出する仕組みになっています

キャプチャ1

数式バーにDDと入っていて、おや?と思われる方もいらっしゃると思いますが

これWingdingsっていう、れっきとしたフォントなんです

どこから引っ張るかというと、”挿入タブ”から記号と特殊文字を選んでください

次の画面が出たら”フォント”のところから選べます

キャプチャ2

どれもとてもキャッチ―なので、無味乾燥な表にメリハリを付けるのにとても便利です

ちなみに下のお天気マークは”Webdings”っていうフォントから引っ張りました!

キャプチャ3

ちなみにWingdingsには2と3もあります

イラストのようなフォント!、一味違う資料作りをするのに、ぜひ活用してください!

次回はタイムラインについて解説します


にほんブログ村

ABC分析をエクセル力で改善する9

今回は今まで解説してきた内容を基にしてダッシュボードを作成しましょう

これまで解説してきた内容は大きく分けて2つです。
①ABCランクの区分を自由自在に設定する

ABC分析をエクセル力で改善する3

Gif1

②ABCランクの質と量が見やすいグラフを作成する

ABC分析をエクセル力で改善する6

14

今回は上の2つの内容をダッシュボードで示せるようにしましょう(右半分は今回は割愛します)

アイコン

ABC分析 294.14 KB 43 downloads

...

29

 

1.ランク設定の内容を分かり易くする

ABCランクの設定条件を示しつつ(下の画像の上の枠)、詳細が見たくなったら直接、該当シートに飛べるようにします(下の画像の下の枠)

30

(下の画像の上の枠)

ABCランクを設定したシートからテキストボックスを使って参照します

(セルの参照はテキストボックスの中でなく数式バーでおこなってください)

31

ABC設定のシートではダッシュボードで参照できるようにTEXT関数を使ってデータを成形してあります

(TEXT関数を使わないと , も含めてうまく表示できません)

32

(下の画像の下の枠)

挿入タブからリンクをクリックし該当のシート、セルを指定してください

33

2.ピボットテーブルでABCランクの量と質を表示する

ぜひ以下の記事(特集)を参照してください。ピボットテーブルの見た目を変えるテクニックを紹介しています

ピボットテーブルの役割を変えよう

3.グラフを配置する

ABC、各グラフの位置が分かり易いように凡例の位置を上にして文字を大きくしましょう

35

以上です、また次回お会いしましょう!

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

ABC分析をエクセル力で改善する8

前回は双方向グラフ作成についての解説をしました

つまり、以下の3つのプロセスで作成を行えます

①第二軸を作成する

②両軸の範囲を揃える(-100%~100%)

③軸を反転する

今回はサンプルの”ABC分析・双方向グラフ”シートのようにピボットテーブルをデータ元にして実際に作成してみましょう

アイコン

ABC分析 258.23 KB 46 downloads

...

ちなみにピボットテーブルのデータ元は”Data”シートになります

21

1.ピボットテーブルを作成したらピボットグラフを作成しましょう!

ピボットテーブルをデータ元としてグラフを作成しましょう!

23

データの指定の仕方は普通のグラフを一緒です(詳細

ただ、グラフではなく”ピボットグラフ”をクリックしましょう!

20

棒グラフを指定したら、分析タブからフィールドボタンを押して見栄えを変え、普通のグラフのようにしましょう!

22

2.第二軸を指定する

商品数を表すグラフ上で右クリックして第二軸を指定しましょう!

これは前回通りです

18

3.両軸の範囲を揃える(-100%~100%)

上下、双方向に伸びるグラフを同じ基準で作成しましょう

それぞれ軸の上で右クリックし、軸の書式設定で最小値と最大値を指定しましょう

25

24]

 

4.第二軸を反転させしましょう

3と同じく軸の上で右クリックして「軸を反転する」にチェックを入れましょう

Gif11

5.軸を非表示にする

最後に軸を非表示にしてグラフの体裁を整えましょう!

26

今回は以上です

次回は最終回です。ダッシュボード化について解説します

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

意外なエクセル術~配色&クイックアクセスツールバー~

資料やグラフは色種類を数多く使えば使うほど、分かり易くなるというのは、錯覚です

白黒の他に2つ位の種類が丁度いいと思います

 

あまり多くの色種類を使うと、一見カラフルに見えるかもしれませんが、情報が多くなりすぎて、かえって分かりにくくなります

アイコン

基調となる種類を選んだら、濃淡で表現していくのが良いです

アイコン2

逆に基調となる色使いを決めておかないと、”テーマの色”もスペースの制限があるので色が選びにくくなります

41

 

資料を作成する前に、ページレイアウトタブの”配色”から、資料のコンセプトにぴったりの基調を決めておきましょう!

GIF8

 

”配色”の下にある”色のカスタマイズ”では自分なりに”色の組み合わせ”をカスタマイズできます

40

最後に追加ですが、”配色”はクイックアクセスツールバーに設定しておくと便利です

42

 

グラフを魅せるエクササイズ_2~凡例の表示~

グラフは一目で内容が理解できるのが理想です

 積上げ棒グラフは内訳が見れるので便利なのですが、表示内容が多いだけに見づらくなるケースがあります

前回は積み上げグラフの合計額を、軸や目盛を確認しなくてもデータラベルで見れるようにしました

2

 今度は凡例を見やすくして、グラフの表示が「何か?」を明確にできるように工夫をしていきましょう!

 積み上げ棒グラフだと、グラフ内での表示位置と凡例の表示位置の間に「ズレ」があるので、上の画像のように「グラフの内訳の内容」が分かりにくいケースがあります

今回も前回と同じ様に散布図グラフを追加します

そして、散布図データラベルを追加しつつ、マーカーを非表示にすることで、散布図を下のGIF画像のように「動く凡例」にします

1.散布図用のデータを追加する

今回使用するデータは上のGIF画像とは違い、前回と同様のデータを使います

アイコン

エクササイズ2 64.39 KB 10 downloads

...

まずは「動く凡例」にする散布図ようのデータをXとYそれぞれに追加します

ちなみに、前回はX値は指定していませんので、その点は前回と違いがあります

14

ポイント

①今回は項目が3つ(年度)あるのでXの値をより若干大きくします

②散布図のマーカーが積み上げグラフの横に来るようにYの値を工夫します

例えば、1つ目の内訳の値が1,620であれば、2で割った810が散布図のY値になるようにします

2つ目の内訳の値が2,204であれば「1,620 + 2,204/2」が散布図のY値になるようにします

11

12

2.散布図を追加する

 グラフの追加(データ追加)、グラフ種類の変更については前回と同様の方法で行います

①グラフの追加

グラフの上で右クリックをして「データの選択」をクリックして下の画面を表示します

上の画像の「追加」をクリックし、まずはX値の範囲をデータ追加します

②グラフ種類の変更

①の作業を行うと、散布図のX値が積上げ棒グラフに追加されていますので、X値の箇所を選択して「系列グラフの種類の変更」をクリックします

その後、下の画面でX値のグラフの箇所を「散布図」に変更します

③グラフにY値の追加

再び、データの選択をクリックして、追加した系列4(散布図)を編集します

編集は次の画像のように系列名は「凡例」となるように指定してください

その後にYの値を指定してください

15

3.データラベルを追加する

今回のデータラベルはセルで指定します

まずは、散布図を選択したまま下の画像の「データラベル」をクリックし、次に「その他のオプション」をクリックしてください

次に開いた「ラベルオプション」の箇所でセルの値で凡例の内容を指定してください

16

データラベルを指定すると下の画像のような状態になっているはずです

最後は、マーカーを非表示にします

これで終了です

<まとめ>

今回は動く凡例を作成して、積上げ棒グラフの内訳の中味を分かり易くしました

前回と同様に主に次の2つのことを行いました

A.散布図を組み合わせること

B.散布図はデータラベルだけ表示してマーカーが非表示/透明にすること

この2つの工夫を行えると、他のグラフでも応用が効きます

ぜひ、習得しておきましょう


にほんブログ村

ExcellentなDashboard作成演習#3 ~ボダン活用 5回目~

前回はこちらから

今回からオプションボタンから出力される番号に従って画像を変える処理をするのですが、

まずはINDIRECT関数の使い方を押さえておきましょう!

INDIRECT関数を覚えると関数の使い方が2次元的になります。覚えるコツは”習うより慣れろ”です。サンプルファイルをダウンロードして手を動かしみましょう!

アイコン

INDIRECT関数 81.40 KB 15 downloads

...

1.セルを間接的に参照する

C2セルに”=indirect(B2)”と入力しましょう

11

B2セルに入力されているD2のセルの内容が”間接”的に反映されましたか?試しに他の”こんにちは”などにD2セルを変えて見てください。即座に変更内容が反映されるはずです

つまり、B2セル⇒(を通じて)D2セルという参照の流れになっています

2.名前を付けた範囲を間接的に参照する

今度はD2セルに”挨拶”という名前を付けましょう

名前の定義をクリックしましょう!

12

名前のところに”挨拶”、参照範囲にD2を指定しましょう!

13

そして、C3セルに”=indirect(B3)”と入力しましょう

B3セル⇒(を通じて)挨拶⇒(を通じて)D2セル

という参照の流れが起きます

14

3.名前管理の中で間接的な参照を完結する

まず”あいさつ”という名前にINDIRECT関数を埋め込みましょう(”挨拶”が埋め込まれているB3セルを指定します)

15

そしてD3セルに”=あいさつ”を入力してください

すると

あいさつ(D3セル)⇒(を通じて)⇒B3セル⇒(を通じて)挨拶⇒(を通じて)D2セル

という参照の流れが起こります

16

ちなみに、Greetingという名前(D4セル参照)を作成したとします

17

そしてD4セルに”Hello!”を入力し、さらに、B2セルをGreetingに変更します

するとD3セルは”Hello!”と表示されます!

18

ややこしいですが、とにかく手を動かしてみましょう!

では6回目でお会いしましょう!
にほんブログ村 資格ブログ ビジネススキルへ
にほんブログ村
にほんブログ村 IT技術ブログ VBAへ

ExcellentなDashboard作成演習#3 ~ボダン活用 1回目~

ダッシュボード2

今回はオプションボタンで動くダッシュボードを作成します

特徴は以下の2つです

1.今、どの会社の数字が示されているのかが、画像で分かり易く示されている

2.オプションボタンで同じフォーマットを動かす為、3者間の数字の比較が行い易く、印刷の手間もない

作成にあたってのポイントは”オプションボタンと各関数との連動”です

後のスピートメーターや温度計のようなグラフはこれまでの演習内容を参照して頂きます

演習2回目は⇒こちらから
にほんブログ村 資格ブログ ビジネススキルへ
にほんブログ村
にほんブログ村 IT技術ブログ VBAへ

ExcellentなDashboard作成演習#2 ~カラフルメーター編 2回目~

今回と3回目はグラフを作成しましょう!

アイコン画像

 

 

では、上記のサンプルファイルから計算シート(演習用)をあけてください

1

 

今回は右の赤丸にある表を埋めて積み上げ棒グラフをの部分を作成しましょう!

 

1.表を埋めましょう!

要注意ゾーンから超優良ゾーンまでの値を合計したら100%になるようにしましょう

ついでに累計値を入れて100%になっていることを確かめられるようにしましょう!

 

最後に入力する超優良ゾーンは下の画像のように上の合計と1との差分を入力しましょう

3

2.積みあげ縦棒グラフを作成しましょう

E2セルからF7セルまでを指定して、積み上げ縦棒グラフを選択しましょう!

4

こんな感じになりましたでしょうか?

次にグラフ上で右クリックしてデータの選択を選びましょう

5

そしたら、行と列を入れ替えましょう!

 

6

凡例を追加したら、こんな感じになっているはずです!

 

7

3.積み上げ縦棒グラフの体裁を体裁を整えましょう!

各ゾーンの色をなるべくカラフルにしましょう!

 

カラフルにできたら、各ゾーンを立体的にしましょう

 

まず、各ゾーンにカーソル(クリック)を置いたまま書式タブから”図形の効果”をクリックしましょう!

8

すると色んなパターンが出てきますが、”面取り”から”丸タイプ”を選びましょう!

 

9

 

こんな感じになりましたでしょうか?

10

それでは、次回、グラフを完成しましょう!