Excel。交点ゼロからのグラフは、折れ線グラフで描くよりも散布図がオススメです。【Scatter plot】

Excel。交点ゼロからのグラフは、折れ線グラフで描くよりも散布図がオススメです。

＜散布図＞

Y=2Xのようなグラフなど、交点ゼロから折れ線グラフを描く場合には、通常の折れ線グラフだと、うまく描くことができません。

次の表をつかって説明します。

Y=2Xのデータなので、B4には=A4*2　という数式が設定してあります。

オートフィルで数式をコピーしています。

では、A3:B13を範囲選択して、折れ線グラフを描いてみます。

挿入タブのグラフにある、「折れ線」を選択します。

すると、折れ線グラフは描かれたのですが、ちょっと変なグラフが描かれています。

 

原因は、XのA列のデータを
横軸として判断されず、データとして判断されてしまったので、プロットエリア内に折れ線グラフとして描かれてしまっています。

そのため、グラフのデザインタブの「データの選択」をつかって、修正する必要があります。

グラフのデザインタブの「データの選択を」クリックして、データソースの選択ダイアログボックスを表示します。

凡例項目（系列）にある「x」が不要なので、削除して、「y」のみにします。

A列の「x」のデータは消えて、「y」だけの折れ線グラフに変えることができました。

ただ、折れ線グラフのスタートが「0」から描かれていません。

データは、x=0 y=0　から始まっていますが、反映されていません。

さらに、グラフの修正が必要になります。

こうなると、グラフを作るのが、少し面倒になってきます。

そこで、折れ線グラフをつかって、描くのではなく、散布図をつかうことで、一発で交点ゼロから始まるグラフを描くことができます。

見出しを除いた、A4:D13を範囲選択します。

挿入タブのグラフにある「散布図（平滑線）」をクリックします。

これだけで、交点ゼロから始まる折れ線グラフを描くことができました。

先程の折れ線グラフのように、横軸がおかしいということもありません。

折れ線グラフで描くのもいいですが、散布図を使ってみるという方法もあります。 

Excel。相関係数の意味は散布図をつくると理解しやすくなります。【Scatter plot】

Excel。相関係数の意味は散布図をつくると理解しやすくなります。

＜CORREL関数と散布図＞

データとデータの相関（関係）が強いのか弱いのかというのを数値にしたのが、相関係数です。

この相関係数が「1」に近いと強いといわれています。

どのようなことなのか理解するには、データを「散布図」をつかって見える化してみるといいかもしれません。

次のデータを使ってみます。

データAとデータBの相関係数は、B10に算出しています。

B10の数式は、

=CORREL(B2:B8,C2:C8)

CORREL関数をつかうと、手早く相関係数を算出することができます。

その結果は、「1」。

このデータAとデータBをつかって、散布図をつくっていきます。

A1:B8を範囲選択します。

挿入タブの「散布図」を選択します。

散布図のグラフが挿入されました。

グラフを大きく表示したいので、ラベルは削除した状態で、少しグラフを大きくしています。

縦軸・横軸ともフォントサイズを大きくしています。

データAの数値が大きくなれば比例して、データBの数値も大きくなっている。

これが確実といっていいレベルだということが見えます。

Y=10Xという一次方程式で表すことができます。

それでは、データBをいい加減な数値にしてみると、散布図がどうなるのか、確認してみましょう。

相関係数は、約0.8です。散布図を確認すると一目瞭然ですね。

プロットされているマーカーがバラバラで、規則性があるんだか、ないんだか。

このようなことから、相関係数が「1」に近いのか、遠いのかによって、データとデータとの間に相関（関係）が強いのか、弱いのを散布図をつかうことで、視覚的に知ることができます。

Excel。散布図。横軸の日付は同じ日付が2つ表示されるのでどうにかしたい【Scatter plot】

Excel。散布図。横軸の日付は同じ日付が2つ表示されるのでどうにかしたい

＜散布図＞

折れ線グラフで描くよりも、散布図をつかって折れ線グラフを描くと、縦と横の補助線が自動的に表示されるなど、都合のいい場合もあります。

散布図で折れ線グラフを描くこと自体は、簡単ですが、横軸に日付をつかうと、不思議なグラフが描かれます。

次の表をつかって、散布図を描いてみます。

A1:A6とC1:C6を範囲選択して、挿入タブの散布図にある「散布図（直線）」をクリックします。

散布図を使った、折れ線グラフが表示されたのですが、注目したいのは、横軸です。

なお、グラフタイトルはグラフ全体を大きく表示したいので、削除しています。

あと、サイズも大きくしています。

日付が2つ重複して表示されています。元の表には、そのようになっていません。

見づらいので、どうにかしたいわけですね。

原因と修正方法を確認していきましょう。

横軸をクリックして、書式タブのグラフ要素が「横（値）軸」になっているのを確認したら、「選択対象の書式設定」をクリックします。

右側に、軸の書式設定作業ウィンドウが表示されます。

境界値の最小値「44712.5」と最大値「44717.5」と、わけのわからない状態になっています。

これは、シリアル値で表示されているだけです。

ただ、0.5は不要ですので、小数点はカットした値で入力し直すといいでしょう。

ついでなので、折れ線グラフの始点と終点を縦軸に重ねたグラフに変更しようと思いますので、次のように修正します。

最小値は「44713」

最大値は「44717」

さて、本題に戻って、横軸の日付重複表示をやめたいので、単位の「主」を「0.5」から「1」に変更します。

重複で表示された原因は、0.5になっていたのが原因です。

ざっくりいうと、午前と午後になっていたわけです。

散布図の折れ線グラフは、このように横軸も重複されず、始点と終点も縦軸から描かれたグラフに修正することができました。

あとは、見やすくデコレーションすれば、完成です。

散布図をつかって、折れ線グラフを描くことができるなど、散布図を散布図としてつかうのではない方法もありますので、色々試してみると、今まで描きたかったグラフを描くことができるかもしれませんね。

Excel。二次関数のグラフを描くにはどうしたらいいの？【Graphing quadratics】

Excel。二次関数のグラフを描くにはどうしたらいいの？

＜散布図：二次関数グラフ＞

Excelのグラフは、アイディアで色々なグラフを描くことができます。

例えば、学生時代に学んだことがある「二次関数」。

その二次関数グラフを描くということも、簡単に描くことができます。

ということで、今回は、次のような二次関数グラフを作っていきます。

放物線のグラフですが、このようなグラフを描くためには、通常の折れ線グラフでは描くことができません。

「散布図」を使うことで、二次関数グラフなどを描くことが出来るようになっています。

今回は、「y=x^2+2x+3」という二次関数をグラフ化します。

グラフなので、当然ですが、表がないと描くことができません。

最初は、表を作っていきます。

二次関数は、「y=ax^2+bx+c」ですから、「y=x^2+2x+3」のグラフを作りたいので、a・b・cに対応した数値を入力しているのが、A5:C5です。

頂点のX座標を算出していきます。

Xの頂点座標を算出する公式は、「-b/2a」ですから、B7の数式は、

=-B5/(2*A5)

同じように、頂点のY座標も算出します。

Yの頂点座標を算出する公式は「-(b^2-4ac)/4a」です。

これに合わせた数式を、B8に設定します。

B8の数式は、

=-(B5^2-4*A5*C5)/(4*A5)

次に、二次関数グラフを描くために「X」と「Y」を数式で算出していきます。

F7の数式は、

=$B$7+E7

先程算出した、頂点座標のXが「-1」なので、左右に5個ずつプロットするとした場合、-5の時は、-6と算出されます。

G7の数式は、

=$A$5*F7^2+$B$5*F7+$C$5

これは、解の公式をつかってY値を算出します。

「y=x^2+2x+3」に沿って作った数式というわけですね。

あとは、オートフィルで数式をコピーします。

これで、グラフを作るための表が完成しました。

ここからは、散布図をつかって、二次関数グラフを作っていきます。

F6:G17を範囲選択して、挿入タブの散布図から「散布図（平滑線）」で散布図を描きます。

 

散布図が挿入されました。

ちなみに、「マーカー」が付いたグラフにしたい時は、「散布図（平滑線とマーカー）」を選ぶことで、描くことができます。

綺麗な放物線の二次関数グラフができました。

あとは、線を太くしたり、プロットエリアを塗りつぶしたりして、見やすいようにアレンジすれば完成ですね。

ポイントは、どんなグラフでも共通ですが、グラフを作るための「表」をどうやって、作るのかということです。

きちんと、設定しておけば、例えば、「y=-x^2+2x+3」とa値を「-1」にするだけで、二次関数グラフもマイナス側で表示できます。

二次関数グラフは、学生さんや学校の先生向けのグラフなのかもしれませんが、機会があれば作ってみるとなかなか面白いですよ。

Excel。相関関係は散布図をつかったデータの並び方であるなしがわかります。【Correlation】

Excel。相関関係は散布図をつかったデータの並び方であるなしがわかります。

＜CORREL関数・散布図＞

二つの数値には、関係性があるのか、ないのか？知りたい場合があります。

たとえば、気温が上がると、冷たい飲み物が多く売れるのだろうか？

「相関係数」を算出することができるCORREL関数を使えば、数値としては算出することができます。

ただ、算出結果が、「1」に近ければ強い相関があり、「0」に近ければほぼ相関はないといわれています。

といわれても、なかなかわかりにくいので、グラフにしてみると、幾分わかりやすくなるかと思います。

今回は、次のデータをつかって、相関係数を算出したうえで、散布図はどのようになるのかを確認していきましょう。

B列の最高気温とC列の販売数には関係性があるのか？

その相関係数を算出しているのが、F1。

F1の数式は、

=CORREL(B2:B11,C2:C11)

とても簡単に設定できる関数なのですが、効果は絶大です。

このパターンでの相関係数は「1」。

1に近いので、0.8以上だと強い相関があるいえるそうですが、散布図にすることでよりイメージがわきやすいかと思います。

B1:C11を範囲選択します。

挿入タブの「散布図」から散布図をクリックします。

散布図が挿入されました。

散布図は何も加工していないプレーンの状態です。

相関係数が「1」ということは、気温が上がれば販売数も比例して増えていくということが、確実なわけです。

このグラフは、y=10xというグラフといえますね。

ただ、現実世界ではこのようなことは、まずありませんので、気温はそのままで、販売数を少し現実的に変えて、「0.6」ぐらいの相関係数をみてみましょう。

相関係数は、「0.653854」なので、弱い相関があるといえます。

この場合の散布図をみてみましょう。

先程の相関係数「1」の時とは異なり、マーカーが散らばっていることがわかります。

関係性が弱くなっていることがみてわかります。

さらに、相関関係を弱くしてみましょう。

見た感じですでに、データはバラバラで関係性は無いように見えます。

 

相関係数も「0.09291」とほぼゼロ。

すなわち、ほぼ相関がないといえます。

散布図はどうなったのでしょうか？

 

マーカーはバラバラで、まとまっていなことがわかりますね。

散布図をつかうと、２つのデータの間に関係性があるのか？ないのか？を視覚的判断することも出来ます。

CORREL関数で相関係数を算出するのは簡単ですが、イメージとして散布図を作って資料にプラスするなどしてみると、いいのかもしれませんね。

Excel。標準正規分布グラフの左右の棄却域を塗りつぶすにはどうすればいいの？【Abandon domain】

Excel。標準正規分布グラフの左右の棄却域を塗りつぶすにはどうすればいいの？

＜正規分布＞

正規分布グラフの左右の棄却域と呼ばれているところを、塗りつぶすのですが、Excelのグラフではどのようにしたらいいのでしょうか？

次のようなグラフを作りたいわけです。

 

標準正規分布のグラフですが、左右端が塗りつぶされていますよね。

この塗りつぶされているところを、「棄却域」と呼ばれています。

今回は説明のために、左右それぞれ別々の色で塗りつぶしをしています。

このグラフを作るのには、細かいデータの表を準備する必要があります。

 

横軸は、-4～4までを0.1ずつで用意しています。

正規分布の、B5には、

=NORM.DIST($A5,B$1,B$2,FALSE)

という数式を設定してあります。

NORM.DIST関数の説明は、棄却域を塗りつぶすのが今回の目的なので、割愛します。

左棄却域（左側）ですが、-4～-2を正規分布のデータをセル参照で、数値をコピーしております。この値が塗りつぶすための数値になります。

同じように、右棄却域（右側）は、2～4を正規分布のデータをセル参照で、数値をコピーしております。この値が塗りつぶすための数値になります。

普通、正規分布グラフを作成するには、散布図をつかって描くのですが、散布図では塗りつぶすことができません。

そこで、面グラフをつかって描く必要があります。

なぜ、0.1刻みの表を作ったのかというと、面グラフは、線を滑らかにすることができないので、データ量を増やすことで、放物線を綺麗に描くことができるというわけです。

では、グラフをつくっていきましょう。

A4:D85を範囲選択して、挿入タブの「すべてのグラフを表示」をクリックします。

 

グラフの挿入ダイアログボックスが表示されます。

 

おすすめグラフタブに「折れ線」が表示されていれば、そのままOKボタンをクリックします。一度、「折れ線」にしてから修正するほうが、簡単です。

もし、折れ線の放物線がなければ、すべてのグラフタブの折れ線から、放物線になっているものを選択します。

 

このグラフを面グラフに変更していきます。

グラフのデザインタブの「グラフの種類の変更」をクリックします。

 

再度、グラフの種類の変更ダイアログボックスが表示されますので、すべてのグラフタブの「組み合わせ」をクリックします。

 

すべて、グラフの種類を面に変更したら、OKボタンをクリックします。

グラフはこのようになりました。

ところで、なんで最初から「面グラフ」にしないのかというと、次のグラフのように、塗り分けの境界線が斜めになってしまうからなのです。

 

これを直すのがとても、面倒なので、折れ線で一度確定してから、面グラフに変更しています。

凡例を削除して、グラフタイトルを標準正規分布に変更します。

青色の部分をクリックして、塗りつぶしを「なし」。

図形の枠線を設定します。

 

最後に、横軸の表示を修正していきます。

横（項目）軸をクリックして、書式タブの選択対象の書式設定をクリックします。

 

軸の書式設定作業ウィンドウが表示されます。

 

軸のオプションのラベルにある「間隔の単位」を5に変更します。

 

これで、完成です。

正規分布グラフは、線グラフのイメージがあるので、なかなか面グラフをつかうという発想が浮かびにくいですが、線グラフでは塗りつぶすことができないので、塗りつぶしたい場合は、塗りつぶしができるグラフから考えてみると、いいかもしれませんね。

Excel。散布図のデータラベルに項目名を表示するのが楽になっています。【Scatter label】

Excel。散布図のデータラベルに項目名を表示するのが楽になっています。

＜散布図＞

Excelもバージョンがアップするごとに、微妙に改善されている機能がたくさんあります。

グラフにも様々な改善があり、便利になっていることがあります。

散布図のデータラベルに項目名を表示することも、改善された一つだといえます。

次の表を用意しました。

 

このデータを使って、散布図を作っていきます。

散布図を作るときのポイントは、店舗名のフィールドは含めないで作成します。

通常、横軸に該当する列を含めないと、「系列1」という項目名になってしまいます。

ここが散布図を作成する時のポイントですね。

では、B1:C7を範囲選択して、挿入タブの「散布図（X,Y）またはバブルチャートの挿入」から散布図を選択します。

 

散布図が表示されます。

今回は、説明の為大きくしたいので、グラフタイトルは削除しております。

 

散布図自体は、簡単に作成できるのですが、

横軸縦軸とも何を意味する数値なのか？

そして、

データ自体どこの店舗のものなのか？

何も表示されていないために、色々修正しないと資料としては、イマイチ使えない状態です。

最初は、データラベルに店舗名を表示する方法から確認していきます。

 

グラフを触っている状態で、グラフのデザインタブの「グラフ要素の追加」にデータラベルがあります。

その中にある「その他のデータラベルオプション」をクリックします。

データラベル書式設定作業ウィンドウが表示されます。

 

ラベルオプションのラベルの内容にある「セルの値」にチェックマークをいれます。

 

データラベル範囲ダイアログボックスが表示されますので、店舗名を表示したいので、A2:A7を範囲選択して、OKボタンをクリックします。

その後、不要になった、「Y値」のチェックマークをはずします。

細かいことかもしれませんが、先に「Y値」のチェックマークを外してしまうと、データラベル自体が消えてしまい、作業ウィンドウも変わってしまい、結果やり直しになるので、注意するといいかもしれません。

あとは、必要に応じて、データラベルの表示場所を変更します。

今回は、「上」に表示します。

グラフはこのように変わりました。

 

項目名をつかって、データラベルを表示することができました。

Excelの昔のバージョンでは、表示するのが大変だったのですが、今は、このように楽に表示することができるように変わりました。

 

データラベルは完成しましたが、あとは、縦軸ラベル・横軸ラベルを表示したり、横軸の目盛りを修正したりすれば完成ですね。