Excel。積み上げ縦棒グラフの合計値を表示する方法

Excel。積み上げ縦棒グラフの合計値を表示する方法

積み上げ縦棒グラフの合計値を表示する方法

グラフというのは、本当に細かくて、いろんなテクニックが潜んでいるというか、
知っていたほうがいいものとか、結構ありまして、簡単なものは、
初心者さん向けの講座でも可能な限りご紹介していますし、
仕事でつかえるExcel講座なんかでは、実践に即して、テクニックをご紹介しています。
今回はその中でも、比較的簡単なんだけども、
知っていて損は無いというテクニックをご紹介しましょう。

今回は、積み上げ縦棒の合計値を表示する方法です。
積み上げ縦棒のそれぞれのデータを表示することは簡単にできるのですが、
なぜか、合計値が表示されないんですね。
で、いちいち、表示されている数値を暗算で合計するっていうのも如何なものかと、思いますね。

そこで、どうやったら、いいのか？ということを紹介していきます。

まず、下記の表を使って、2-Dの積み上げ縦棒を作ってみましょう。

グラフで重要なのは、最初の最初。範囲選択ですね。範囲はA3:D7ですね。
範囲選択をしたら、挿入タブの棒グラフにある、積み上げ縦棒を作っていきましょう。

すると、積み上げ棒グラフが作成できますね。説明の為ちょっと、大きくしておきます。

棒グラフだけでは、よくわかりませんので、それぞれのデータの数値を表示させてみましょう。

レイアウトタブのデータラベルをクリックして、今回は中央を選択しましょう。
すると、各データの中の中央に数値が表示されてきましたね。

各数値が表示されたまではいいのですが、なぜか、合計の数値が表示されない訳なんですね。
合計の数値も表示してくれれば、いいのですがね。

それでは、合計の数値を表示させちゃいましょう。

では、どうやって表示させるのかという事、テキストボックスを使用します。
テキストボックス？と思う人もいるかもしれませんが、テキストボックスを使います。

それも、直接数値を入力するわけではありません。

まず、グラフをクリックして、レイアウトタブの挿入ブロックにあるテキストボックスを
クリックしましょう。

そうしたら、アイスの棒グラフの一番上でクリックをしましょう。

先程も書きましたが、ここで直接合計の数値を入力しては芸がないというか、
数式を使うことによって、数値が変わっても連動して数値を変えることができますので、
やってみましょう。

テキストボックスの中にカーソルがある状態なのを確認して、
そのまま数式バーをクリックして数式を入力していきます。

数式バーには、まず＝（イコール）を入力しましょう。

そして、合計のセルであるE4をクリックしましょう。
そうすると、数式バーには、=縦棒合計!$E$4と表示されますので、Enterキーを押すと、

テキストボックスの中に、2,710が表示されますね。

あとは、テキストボックスの大きさを文字のサイズに調整して、
それぞれの棒グラフの上に同じように作成すれば完成です。

また、自分で調整するよりも、Excelに調整させる方法もありますので、合わせてご紹介しましょう。

テキストボックスの上で、右クリックをして、オブジェクトの書式設定をクリックしましょう。
図形の書式設定ダイアログボックスが表示されます。

テキストボックスの自動調整にある、テキストに合わせて図形のサイズを調整するにチェックを
つけると、文字の大きさに合わせて、テキストボックスが調整されますね。

で、ひとつ注意する必要があるのは、この作成したテキストボックスをCtrl +ドラッグで、
コピーすると、図形のコピーになってしまって、中の数式まではコピーしてくれません。

面倒ですが、ひとつずつ、テキストボックスを作成する必要があります。

データがたくさんあるときには、折れ線グラフを第2軸として表示させる方法もありますよ。
http://infoyandssblog.blogspot.jp/2015/06/excelverticalbargraph.html

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で色分けできましたね。