Excel。相関図の縦軸横軸をいれかえたい【Relationship Chart】

Excel。相関図の縦軸横軸をいれかえたい

＜相関図＞

次の表があります。

B列が販売金額でC列が来店客数のデータが入力されています。

この販売金額と来店客数との間に、関係性があるのか（相関）を知りたい。

そこで、散布図を作って視覚的に関係性があるのかないのかを確認してみます。

散布図の作り方にポイントがあります。

見出しは含めません。

範囲選択は、B2:C8で、散布図をつくります。

B2:C8を範囲選択して、挿入タブの散布図を選択します。

散布図が表示されました。

これでOKなのですが、横軸は販売金額で、縦軸が来店客数になっています。

これを逆にしたい場合、どうしたらいいのでしょうか。

縦棒グラフや円グラフならば、グラフのデザインタブにある「行/列の切り替え」をクリックすれば解決します。

ただし、グラフをよく見てみると、おかしなグラフになっています。

散布図は、「行/列の切り替え」で対応していません。

まずは、散布図の仕組みを確認します。

範囲選択の左側が横軸。

X軸になります。

右側が縦軸。

Y軸になるように設定されています。

ということは、元表の列を入れ替えればいいわけですね。

といきたいところですが、作り直すのは大変ですし、面倒です。

そこで、散布図は、次の方法で、入れ替えを行います。

グラフのデザインタブにある「データの選択」をつかいます。

データソースの選択ダイアログボックスが表示されました。

凡例項目の編集をクリックします。

系列の編集ダイアログボックスが表示されます。

系列Xの値には横軸になるデータを選択します。

系列Yの値には縦軸になるデータを選択します。

OKボタンをクリックします。

これで、縦軸と横軸を入れ替えることができました。

Excel。相関係数=1というのは、グラフにしてみるとよくわかります。【correlation】

Excel。相関係数=1というのは、グラフにしてみるとよくわかります。

＜散布図＞

相関係数とは、2つの変数の間にどれくらい強い関係があるかを数値で表したものです。

値は通常-1から1までの範囲をとり、1に近いほど強い正の関係、-1に近いほど強い負の関係、0に近いほどほとんど関係がないことを示します。

と説明には書かれているのですが、どういうことをいっているのでしょうか。

まずは、次の表を用意しました。

この表は、客数と販売金額が相関係数1の関係がある表を用意しました。

まぁ、このようなことは発生しませんが…

見てもらうとわかるように、客数が10人増えれば、10000円販売金額が増えるという表です。それが10まで続いています。

Excelには相関係数を簡単に求めることができる関数が用意されています。

それが、CORREL関数です。

F1に数式をつくってみました。

=CORREL(B2:B11,C2:C11)

算出結果は1になっています。よって相関係数は1ということが関数からもわかりました。

よって、1に近いほど強い正の関係があることが数式からも確認できました。

ただ、そういわれても、いまいちピンときません。

そこで、散布図にしてみましょう。

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

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

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

グラフのサイズやフォントサイズを見やすいように加工しました。

右上に一直線でマーカーがプロットされていることがわかります。

もし客数が1000になれば、1000000になることが想像できます。

相関係数を確認するには、散布図で確認してみると、いいことがわかります。

では、販売金額をRANDBETWEEN関数をつかって、変えてみましょう。

C2には、

=RANDBETWEEN(100,100000)

と同じ範囲でランダムの数値を算出させてみました。

相関係数は0.4165

1よりも離れましたので、客数と販売金額の関係性は薄れたことがわかります。

そして、散布図も見てみましょう。

先ほどと異なり、一直線ではありません。

どこに、近似直線を引けばいいかわかりませんね。

数値に関係性があるかどうか、強いのか弱いのかを求めるだけではなくて、散布図をつかってみると視覚的にわかりやすくなります。

Excel。散布図の横軸を日付でつくると、横軸の表示がおかしい【Scatter Plot】

Excel。散布図の横軸を日付でつくると、横軸の表示がおかしい

＜散布図＞

Excelの散布図で、折れ線グラフを描くと、縦と横の補助線が自動的に表示されます。

そのため、通常の折れ線グラフよりも重宝することがあります。

ただ、厄介な問題を含んでいますので、その対応方法もご紹介します。

次のデータをつかって、散布図を挿入します。

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

挿入タブの散布図にある「散布図（直線とマーカー）」を選択します。

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

散布図をつかった折れ線グラフは、縦横の補助線が描かれています。

わかりやすいグラフなので、通常の折れ線グラフよりも、資料としてわかりやすいグラフになっています。

ただ、横軸をみると、重なっています。

そしてただ、重なっているだけではないようです。

同じ日付が複数あるように見えます。

グラフを横に大きくしてみます。

やはり、日付が複数あることが確認できました。

これではこまってしまうわけです。

逆に縮めてみましょう。

縮めてみたら、横軸の日付は重複表示されていません。

散布図でつくった折れ線グラフは大きくすることはできないのでしょうか。

それでは、横軸を修正しますので、横軸をクリックしたら、書式タブにあるグラフ要素が横（値）軸になっていることを確認したら、選択対象の書式設定をクリックします。

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

単位を0.5から1.0に変更します。これで重複しなくなります。

横軸が12月1日からのデータなのに、11月30日からはじまっています。

終わりも、12月6日と12月5日までにします。

境界値の最小値と最大値はシリアル値で表示されてしまっています。

最小値は+1。

最大値は-1すれば、12月1日。

12月5日にすることができます。

それではグラフを確認してみましょう。

横軸を表示合わせることができました。

あとは、グラフの見栄えをよくして完成です。

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」にするだけで、二次関数グラフもマイナス側で表示できます。

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