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

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

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

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

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

ところで

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

では

以下、サンプルファイルの表から選手の名前に応じて学年から出場回数を抽出する関数を作成していきましょう!!

18

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

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

24

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

今度は取り合えず列だけ追加してみましょう!

25

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

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

 

”違い”を理解した上で表の性質や抽出データの活用方法など、場面に応じてMATCH関数とINDEX関数の組み合わせてとVLOOKUP関数を使い分けていきましょう!

 

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

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

今回の対象は凡例項目とにかく軸ラベルになります

11

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

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

12

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

14

系列名と系列値に分けて指定を行います

GIF5

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

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

GIF8

 

2.軸ラベルを使いこなす

軸ラベルは1ほど使う機会がありませんが、知っておくと便利です

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

GIF7

今回は以上です

また次回をお楽しみに!

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

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

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

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

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

Gif1

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

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

14

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

アイコン

ABC分析 294.14 KB 41 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 44 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

今度は凡例を見やすくしましょう!

エクササイズ

今回は上のような凡例項目が多いものでなく、凡例項目が3つのデータを使います

アイコン

エクササイズ2 64.39 KB 10 downloads

...

 

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

11

そして、散布図にデータラベルを追加します

 

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

14

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

②ちょうど積み上げグラフの横に来るようにYの値を工夫します

11 12

2.散布図を追加する

グラフの追加(データ追加)、グラフ種類の変更については前回を参考にしてください

15

*前回、散布図を追加したグラフなので、”データ追加”と同時にX、Y、両方の値が指定できるようになっています

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

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

16

どうでしょう?

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

17

ではまた次回!

 

 

 

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

前回はこちらから

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

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

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

アイコン

INDIRECT関数 81.40 KB 14 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

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

 

 

 

 

スピードメーターグラフを作成しよう4~色の変更~

前回は、スピードメーターグラフの下地となるグラフの上に円グラフを追加作成しました。

今回はこの追加された円グラフを、スピードメーターの針部分のグラフにします。

読者の皆さんの中には、スピードメーターの針はどのように動かすのだろう?と不思議に思った方もいらっしゃるかと思います。

実は、とても簡単な仕組みで動いています!まずは、下のGIFをご覧ください。これが、今回作成する針部分のグラフの動きです!

3つパイがあるグラフを作成した後、真ん中のパイだけ表示するようにするので、グラフが針のように見えるのです!

針の動き

<今回解説するグラフのカスタマイズ作業>

1.3つパイがある円グラフを、下地と同じ角度で回転させる

 注)今回はドーナツグラフでなく円グラフで作成します!

2.円グラフの3つのパイの色を調整して、スピードメーターの針を作成する

まずは準備作業として、サンプルをダウンロードして、以下のように範囲を指定して円グラフを作成しましょう

値の合計が360度のところまでをグラフの範囲にするのが、実はこのスピードメーターグラフの作成ポイントです。詳細は後の説明回で解説します。

準備が終わったら、グラフのカスタマイズ作業に取り掛かりましょう!

1.3つパイがある円グラフを、下地と同じ角度で回転させる

➀下地グラフを作成した時と同じように、データ系列の書式設定を表示する

サンプルから円グラフを作成した時には、下の画像のような状態のはずです。

この状態から、円グラフを下地グラフに合わせられるように270度回転させます!

②下地グラフの時と同じ様に、データ系列の書式設定からグラフを回転させる

データ系列の書式設定を表示します!

そして、グラフの基線位置で270度を指定しましょう!

回転した後には以下の状態になっていますか?これで針の位置は下地グラフとあうようにセットできました!

キャプチャ2

2.円グラフの3つのパイの色を調整して、スピードメーターの針を作成する

➀2番目のパイが針に見えるように、2番目のパイ以外は表示されないようにする

円グラフにカーソルを置いたまま、画面上の”書式タブ”を操作して、無駄な塗りつぶしと線を除きましょう

キャプチャ3

キャプチャ4

円グラフは下の画像のようになりましたでしょうか?

②針の色を調整する

次に針の色を好きな色変えるのですが、針がなにせ細いので、指定するのはなかなか難しいです。

ですから針は一旦、大きくしましょう!

キャプチャ5

色を変えることができたら、針の大きさを戻して、針のグラフは完成です

試しに下のGIFのように、”残り1”の値を動かしてみてください。針自体の値は変わりません。残り2には数式が入っていて、3つのパイの合計が360になるように調整されるようにになっています。

<まとめ>

今回は、スピードメーターの針部分のグラフを作成しました。一見、針の作成は難しそうに見えたかもしれませんが、実は、3つのパイがある円グラフの色や線を少しカスタマイズするだけでした。見た目は複雑そうでも中身はとてもシンプルなんです!

次の5回目からは応用編を開始します!では5回目でお会いしましょう!