Excel。二重ドーナツグラフをつくってみよう！

Excel。二重ドーナツグラフをつくってみよう！

二重ドーナツグラフ

仕事で使えるExcel講座で、円グラフに続いて、だいたい、ドーナツグラフを紹介するのですが、
このドーナツグラフ。
二重ドーナツグラフの作り方を教えてほしいとか、
うまく作れないとかという話をよくいただきます。

そこで、今回は、ドーナツグラフの発展形として、

【二重ドーナツグラフ】

をご紹介したいと思います。

まず、下記の表があります。

分類がリアルとネットで、その詳細の販売方法が、B列。
そして今回は、その詳細のデータであるI列と分類金額のJ列。
を使って二重ドーナツグラフを作ってみたいと思います。

まず、問題になるのが範囲選択です。
外側のドーナツに分類金額のデータを表示させたいので、A列の分類である、
リアルとネットは、のぞいてあげる必要があります。

すなわち、外側のドーナツのデータだけを選ぶのがポイントとなります。
また、2行目の見出し行ものぞいて範囲選択をしていきます。

すなわち、今回は、B3:B6とI3:J4が、範囲選択となります。
では、範囲選択をしたら、挿入タブのその他のグラフの▼をクリックして、

ドーナツグラフをクリックすると、二重ドーナツグラフが挿入されます。

これで、完成！ってわけには当然いきませんよね。
まずは、パーセントを表示していきましょう。
今回のように項目名と構成比を表示するには、レイアウトを使うと、

簡単に表示することが出来ますので、そちらを使っていきましょう。

まず、グラフをクリックして、グラフツールのデザインタブ→グラフのレイアウトにある、
レイアウト1をクリックしましょう。

そうすると、二重ドーナツグラフのレイアウトが変更されます。

グラフタイトルを、販売内容に変更します。
次に、パーセントを小数点第2位までの表示に変更していきましょう。
まず、外側のデータラベルをダブルクリックあるいは、クリックして、
グラフツールのレイアウトタブの現在の選択範囲にある選択対象の書式設定をクリックすると、データラベルの書式設定ダイアログボックスが表示されてきます。

表示形式の分類にあるパーセンテージを選択して、小数点以下の桁数を2にして、
閉じるボタンをクリックすると、表示が小数点第2位に変わったがわかりますね。

同じように内側のドーナツのデータラベルをダブルクリックあるいは、
クリックして、グラフツールのレイアウトタブの現在の選択範囲にある選択対象の書式設定をクリックすると、データラベルの書式設定ダイアログボックスが表示されてきます。

表示形式の分類にあるパーセンテージを選択して、小数点以下の桁数を2にして、
閉じるボタンをクリックすると、先程と同じように表示が小数点第2位に変わったがわかりますね。

そして、よく見てみると、外側のドーナツのラベルがリアルとネットとなっていませんね。
折角、パーセントが小数点第2位になったのですから、修正していきましょう。

まず、外側のドーナツのデータラベルをクリックします。
そして、もう一度クリックすると、そのデータラベルだけが選択できますので、
さらに、Shopという文字の上でダブルクリックをすると、文字を修正することができますので、
Shopをリアル。facebookをネットと修正しましょう。

今度は、ドーナツの穴を小さくしていきましょう。

ドーナツグラフをクリックして、
グラフツールのレイアウトタブの現在の選択範囲にある選択対象の書式設定をクリックすると、データ系列の書式設定ダイアログボックスが表示されてきます。

系列のオプションのドーナツの穴の大きさを30%にしてみましょう。閉じるボタンをクリックすると、
穴が小さくなりましたね。

次は、リアルとネットの色を変えてみましょう。リアルをクリックして、もう一度クリックしますと、
リアルだけを選択できますので、好きな色に変えていきましょう。

ネットの方も、同じように、色を変えてみましょう。これで、完成しました。

Excel。基本中の基本 IF関数をおさらいしてみよう。

Excel。基本中の基本 IF関数をおさらいしてみよう。

IF関数

新年度になると、企業研修でも新人研修が増えてきたり、
職業訓練やらパソコンスクールさんなどでも、Excelを新しく、
あるいは、ブラッシュアップということで、講座を行わせていただくことがありますが、
そこのなかで、100%ご紹介する関数に、基本中の基本。
IF関数があります。当然、仕事で使えるExcel講座でも、ご紹介しております。

このIF関数。実際に使う人は、日常的に使うのですが、
覚えた後、とんと使っていないと、

どう使いましたっけ？

などという質問も結構あるのです。

そこで、今回は、IF関数を改めて、ご紹介しようと思います。

下記の表があります。

F3に合計の数値が150以上ならば合格。
そうでなければ、空白という判定をしたいとします。
一人ぐらいなら、目視で勝負！でもいいのですが、
件数が増えれば相当面倒な処理になりますよね。

そこで、このような判断をする場合に使うのが、IF関数なのです。

Excelのベテランさんならば、サッサと作れると思いますが、
今回は、初めての方向けに何時も講義でやっている感じで説明をしていきたいと思います。

まず、IF関数の前に、どの関数を使ったらいいのか？
という訳ですが、

今回の様に、○×クイズのような二者択一の問題を作りたい場合は、
IF関数を使っていくというイメージがいいかと思います。

今回だと、鈴木さんの合計点は150点以上ですか？と質問して、

○か×かを答えるというクイズ。

を作るイメージです。このようなことをExcelにさせたいので、
今回はIF関数を使っていきます。となります。

IF関数は慣れるまでは、
次のような図を書いてみるとわかりやすいので、作ってみてもいいかもしれませんね。
 

では、答えを出す、F3をクリックして、数式バーのfxボタンをクリックして、
IF関数のダイアログボックスを表示してみましょう。

fxボタンを関数の挿入ボタンといいます。クリックしますと、
関数挿入ダイアログボックスが表示されます。

まず、関数の分類が、最近使用した関数になっていれば、その下の関数名のボックスの中に、
おおむねIF関数はあると思いますので、
その中からIF関数を選択して、OKボタンをクリックしましょう。

すると、今度は、IF関数のダイアログボックスが表示されてきます。

論理式ですが、これは、

質問文

と置き換えるとわかりやすいかと思います。
今回は鈴木さんの合計点は150点以上ですか？
という質問なので、これを”Excel語”にしていきます。

まず、ネックになるのが、150点以上。という表現。

以上とか以下とか文字で書いても、Excelは何の事だかわかりません。
そこで、以上や以下を不等号記号を使って表現する必要があります。


以上は、数学では　≧ですが、Excelでは、　＞＝　と表記します。
より大きいは、数学では＞で、Excelでも、　＞　と表記します。
以下は、数学では　≦ですが、Excelでは、　＜＝　と表記します。
より小さいは、数学では　＜で、Excelでも、　＜　と表記します。
未満は、数学では　＜で、Excelでも、　＜　と表記します。
等しいは、数学では　＝で、Excelでも、＝　と表記します。


となっています。今回は以上ですので>=を使います。残念ながら、Excelは≧を認識しませんので、>=という記号二つで以上と言う意味になります。

なお、=>のように、先にイコールを入力するとエラーになってしまいますのでご注意ください。書き順は、先に>ですから、書き順を順守していると思っていただくといいかと思います。

さて、長くなりましたが、鈴木さんの合計点は150点以上ですか？を
Excel語に置き換えてみましょう。

鈴木さんのセル番地は、E3。
以上は>=。
数値は150　なので、
論理式には、
E3>=150
という数式が入ります。なお、>=は、半角入力で入力するようにしましょう、
全角でも認識しますが、半角で入力するようにしましょう。

つづいて、真の場合を作っていきましょう。
真の場合のボックスをクリックする。あるいは、論理式のボックスでTabキーを押すと、移動します。関数のダイアログボックスを使用するときは、Enterキーを使うのは、控えた方がいいでしょう。

Enterキーを使うと、関数を作成途中にOKボタンをクリックする処理と同じことをしてしまって、
エラーになって最初から関数を作り直すことになってしまいますので、ご注意ください。

真の場合には、何が入るのかというと、これは、質問に対して、YESだったらということです。
すなわち、○と入力します。

○と入力して、Tabキーを使って、次の偽の場合のボックスに移動すると、
自動的に”○”と置き換わったのが確認できますね。
この”（タブルコーテーション）に囲まれたものは、Excelで文字という認識になります。
自分で入力でも構いませんが、ミスを防ぐ意味からも、
ExcelがやることはExcelにやらせるほうがいいでしょう。

最後は、偽の場合。Noの場合と言うことですが、空白をいれるます。
さて、何も入力しないと、エラーになってしまいます。
IF関数は、真の場合と偽の場合の両方とも入力されていないとエラーになってしまします。
では、空白とはどうすればいいのでしょうか？

スペースキーで空白を入力する。というのは×です。
ここには、空白を意味する、””（タブルコーテーションを連続入力）を入力します。

スペースキーの空白とは、パソコン上異なっております。

そして、Tabキーを押して、確定させたら、OKボタンをクリックして、完成。

IF関数は、Excelを上達するための第一歩です。しっかり押さえておきたいスキルですね。

Excel。判断分岐多いならIf関数よりChoose関数が楽

Excel。判断分岐多いならIf関数よりChoose関数が楽

Choose関数で４つの判断分岐

以前企業研修のご質問だったと思うんだけど、IF+IF関数を使って、
ABCに判断分岐する方法をご紹介した時に、

判断分岐が多くなると、IF+IF+IF…と大変なので、効率な方法はありませんか？
とありまして、アイディアによっては、Choose関数のほうがIF+IF関数のネストよりも、【楽】かもね。

とお答えしたことがありましたが、

今回は、そのChoose関数を使って、４つの判断分岐をやってみたいと思います。

仕事で使えるExcel講座でも、お話しすることもあるネタですね。

今回は下記の表があります。

F列の評価に次の条件で判断した文字を表示したい訳です。

300点なら、満点
200～299点なら、良
100～199点なら、可
0～99点なら、不可

というルールで、判断するとします。

まずは、IF+IF関数のネストで作ってみましょう。

苦労をすることによって、もっといい方法はないかな？と考えるわけですからね。

F2をクリックして、IF関数のダイアログボックスを表示しましょう。

まず、F2=300という論理式を作って、真の場合は、”満点”。
偽の場合は、また質問を作っていきますので、IF関数を作っていきます。

名前ボックスの▼をクリックして、一覧からIF関数があれば、クリックしましょう。
ネストのIF関数を作っていきますので、
論理式には、E2>=200
真の場合には、”良”

負の場合には、これまた、ネストのIF関数を挿入していきますので、
名前ボックスの▼をクリックして、一覧からIF関数があれば、クリックしましょう。

ネストのIF関数を作っていきますので、
論理式がE2>=100
真の場合には”可”
偽の場合には”不可”

としてOKボタンをクリックすると完成しますので、オートフィルで連続コピーしましょう。
ちなみに、数式は、

=IF(E2=300,"満点",IF(E2>=200,"良",IF(E2>=100,"可","不可")))
なんだか、やっぱり長い数式になりましたね。
ご質問があったように、IF+IF関数のネストだと、
多岐にわたる分岐判断だと複雑化していきますね。

なお、結果は、

これを煩雑化した数式にしないで算出する方法に、
今回紹介するChoose関数を使う方法があります。

それでは、ご紹介していきましょう。
F2をクリックして、Choose関数のダイアログボックスを表示させましょう。
このChoose関数は、1なら、２なら、３なら…という風に判断してくれます。

ですので、ココはアイディアが必要になってくるのですが、今回の分岐の条件が、

300点なら、満点
200～299点なら、良
100～199点なら、可
0～99点なら、不可

ですので、例えば、99点は不可と判定させるには、合計点を100で割った値に＋１します。
今回はなぜ＋１をするのかというと、このChoose関数。

小数点以下は切り捨てちゃうんですね。

ですので、割っただけだと、0になってしまう訳ですね。

99÷100=0.99+1=1.99　で1として判断。
このように、どうしたら、
１に２に３に…と出来るのかを考えれば多岐にわたる分岐判断は
IF+IF関数よりも簡単になると思います。

Choose関数のダイアログボックスを作っていきましょう。
インデックスには、E2/100+1
値1には、”不可”
値2には、”可”
値3には、”良”
値4には、”満点”

あとは、OKボタンをクリックしましょう。

そうしたら、オートフィルハンドルを使って連続コピーしましょう。

ちなみに、数式は、
=CHOOSE(E2/100+1,"不可","可","良","満点")
確かに、IF+IF関数のネストよりも煩雑じゃないですよね。

ちょっと複数の判断分岐になるときには、Choose関数。使ってみてはどうでしょうか？

Excel。ドーナツグラフの合計値を表示する方法

Excel。ドーナツグラフの合計値を表示する方法

ドーナツグラフの合計値を表示する方法

積み上げ縦棒グラフの上に合計値を表示させる方法をご紹介しましたが、
それに類似するテクニックで、ドーナツグラフの穴の中に合計値を表示させる方法を
今回はご紹介しましょう。
これも、結構リクエストやら質問やらがあるんですよ。積み上げ縦棒もそうですが、
このあたりも自動的に表示させてくれるようになるといいですよね。

今回も、このテクニックで登場するのはテキストボックスなのですがその前に、
まずは、ドーナツグラフの作り方から、ご紹介していきましょう。

下記のデータがあります。

この状を使用して、ドーナツグラフを作っていきましょう。
なお、作成するドーナツグラフはシングルタイプです。
ダブルタイプのドーナツグラフの作り方は、別の時に改めてご紹介したいと思います。
さて、今回の範囲は、A3:A7の販売方法～プリンとE3:E7の合計からプリンの合計までを使って、
作成していきます。
範囲選択をしましたら、挿入タブのグラフからその他のグラフからドーナツを選択します。

クリックすると、ドーナツグラフが表示されます。説明の都合上、サイズを大きくしました。

レイアウトを変更して、パーセントを表示させてみましょう。
デザインタブのレイアウトからレイアウト1を選択しましょう。

そうすると、グラフのレイアウトが変更されましたね。

まず、グラフタイトルを合計から第一四半期合計に変更しておきます。
その後、パーセント表示。小数点が表示されていませんので、
小数点第2位まで表示させていきましょう。

データラベルをダブルクリックするか、レイアウトのデータラベルにある、
その他のデータラベルオプションをクリックすると、
データラベルの書式設定ダイアログボックスが表示されてきます。
表示形式に移動して、分類をパーセントに合わせます。
右上にある、小数点以下の桁数の欄に2と入力しましょう。
こうすると小数点第2位まで表示することが出来ます。

閉じるボタンをクリックしましょう。

次に、データラベルの文字が小さいので大きくしましょう。やり方は簡単。

データラベルをクリックして、ホームタブのフォントサイズを適度に大きくします。

ちなみに、このままでもいいのですが、プロットサイズを大きくして、もうちょっと、
ドーナツグラフ全体を大きくしてみましょう。
グラフで注意しないといけないのは、プロットエリアを拡大しないと、
グラフそのものは大きくならないという事です。

グラフエリアを大きくしても、全体が大きくなるだけです。

さて、いよいよ、ドーナツの穴の中に、合計値を表示させてみましょう。
入力する文字は、合計金額　8,330円　とします。

レイアウトタブにあるテキストボックスをクリックしましょう。
そして、グラフの上でクリックすると、テキストボックスに入力できるようになります。

ここで、悩ましいのは、前回積み上げ縦棒の時にご紹介した、
数式バーに＝（イコール）を入れて、合計値をクリックする方法をご紹介しましたが、
これをやると、今回は、ちょっと問題があるのですが、

それは、合計金額という文字と金額の後にある円という文字。

通常のセルの場合だと、
="合計金額"&E8&"円"
という数式を作れば、問題はないのですが、テキストボックスの中は、＝（イコール）のあと、

受け付けてくれないので、合計値のセルをクリックするしかないのです。

ですので、今回は、手入力をしたほうが、楽だと思います。

テキストボックスの中に文字を入力しましょう。
入力後、テキストボックスを右クリックして、図形の書式設定ダイアログボックスを表示します。

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

このあと、穴の真ん中に移動して、文字のフォントサイズを大きくすれば、完成ですね。

Excel。値段・利益で使われる、主な計算式をまとめてみました。

Excel。値段・利益で使われる主な計算式をまとめてみました。

計算式

以前紹介した、ビジネスで使う計算式が好評のようだったので、今回は、その第4弾。
Excelは使えるけど、こういう式がわからないと、数式を作ることができないわけですね。
仕事で使えるExcel講座では、こういうビジネスで使われる、計算式もご紹介しております。

まずは、この間この計算式を使ったExcelファイルを作ってあげたので、こちらから。

人件費関係

従業員1人あたりの生産性を算出するときに使う計算式を２つ紹介。

労働生産性

従業員1人あたりの粗利益高　＝　粗利益　÷　従業員数

人時生産性

労働時間1hあたりの粗利益高　＝　粗利益　÷　総労働時間

けど従業員を1人にしたら、そのまま粗利益がアップするのかは別ですけどね。

つづいて利益関係。
売上総利益　＝　総売上高　－　売上原価　－　ロス
売上総利益を増加させるためには、売上原価とロスを抑制する必要があります。
この売上総利益から調整可能費用を引くと、店舗調整可能利益を求めることができます。

店舗調整可能利益　＝　売上総利益　－　調整可能費用
調整可能費用とは、
人件費・販売促進費・備品費・包装費・在庫金利・水道光熱費の合算金額です。

なお、在庫金利の算出方法は、
在庫金利　＝　（実際在庫日数　－　標準在庫日数）×平均日版×原価率×1%（月当たり）
在庫金利とは、売れるまでの間に必要な商品在庫の費用のことですね。

さらに、店舗調整可能利益から営業費用を差し引くと、店舗営業利益を求めることが出来ますね。
店舗営業利益　＝　店舗調整可能利益　－　営業費用
営業費用とは、店舗家賃と本部費用の合算金額です。

さらに、さらに、店舗営業利益から、チェーン店さんなんかは、
本部スタッフ費用などの負担がありますので、そのような金額を差し引くと、
店舗純利益を算出することが出来ます。

店舗純利益　＝　店舗営業利益　－　その他

このように、煩雑複雑、そして日常的に計算をしないといけないものは、
ソロバンや電卓レベルでやっていては、ちょっと大変な感じもしますね。
そこで、Excelなどの表計算ソフトを使用することによって、瞬時に、
しかも、計算式を作成しておけば、データを入力するだけで、
正確に求めることが出来るわけですね。

初心者さん向けのテキストですと、どうしても、ベタな四則演算で練習するものが多くありますが、
ビジネスマン向けとか、小売店などの方向けの場合は、このような具体的なケースを用いて、
四則演算を教えてみてもいいかもしれませんね。

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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