8/30/2016

Excel。Scatter plot。散布図の近似曲線。外れ値を除いて描きたいけどどうしたらいい?


Excel。散布図の近似曲線。外れ値を除いて描きたいけどどうしたらいい?

<散布図と近似曲線>


実験結果などをグラフ化する際に使っていく、散布図。
あまりご縁がない方も多いと思われますが、逆に、日常的にお仕事で使っている人には、通常のグラフ以上に作り方などの情報が少なくて、アレコレと現場で困っているというお話をよく耳にします。
さらに、その散布図に近似曲線を加えるということをよくするのですが、【外れ値】と呼ばれるそのグループに加えるとおかしくなるデータがあるのですが、それを含めて近似曲線を描いてしまって、困るというのを耳にしております。

そこで、今回は、散布図の近似曲線を【外れ値】を除いて描く方法をご紹介していきます。
まず、下記の表をご覧ください。

このデータを散布図にするところからやってみましょう。

B3:C9を範囲選択肢して、散布図の散布図(マーカーのみ)を選択しましょう。
これで、散布図が作成されます。

凡例は削除してあげます。
では、次に、近似曲線を描いていきましょう。

レイアウトタブの近似曲線から【線形近似曲線】をクリックしましょう。

線形近似曲線が描かれました。この近似曲線をアレンジしてきますので、線形近似曲線をダブルクリックするか、クリックしましたら、選択対象の書式設定をクリックしましょう。

近似曲線の書式設定ダイアログボックスが表示されます。

グラフに数式を表示する

グラフにR-2乗値を表示する
に、チェックマークをつけましょう。
散布図グラフはこのようになりました。

完成といえば完成なのですが、今回の問題は、【外れ値】を除いて、この近似曲線を描きたいというのがリクエストなのです。今回は、このグラフの中で6983を指し示すデータ。とびぬけていますね。これが【外れ値】にあたるとします。

単純に表からデータを削除してみましょう。

グラフも全体的に変わってしまいましたし、近似曲線も大幅に変わってしまいましたね。けど、【外れ値】はグラフに表示しておきたいわけです。

実は、データを隣に移動させた表を作ってあげればいいだけなのです。そして、グラフ
範囲を修正してあげる作業をします。

上記の表のようにD列にY-1という列をつくり、【外れ値】のデータだけを転記し、元のC列のYから削除します。今回はC8のデータを削除しております。
これで、散布図グラフは、このようになりました。

あとは、D列を追加するようにグラフのデータ範囲を修正します。
青色のカラーリファレンスの範囲を広げるのが一番簡単な修正方法ですね。

これで、近似曲線は、【外れ値】を除いて描くことができました。
最後に、【外れ値】のマークを形と色を変更して合わせてあげましょう。
【外れ値】のマーカーを右クリックして、データ系列の書式設定ダイアログボックスを表示しましょう。

マーカーのオプションのマーカーの種類を組み込みに変えて、◆に変えて、色も変更しましょう。

これで完成しました。このように別の列をつくってあげれば、【外れ値】を描きながら、近似曲線にデータを含めないで描くことができますよ。

8/29/2016

今週のFacebookページの投稿 2016/8/22-2016/8/28

今週のFacebookページの投稿 2016/8/22-2016/8/28

<Facebookページ>

Facebookページで【書いてみた】ワンポイントです。

8月22日
Excel。スパークラインは、色を変更したりすることもできますね。

8月23日
Excel。条件付き書式を設定すると、手動で設定した書式よりも優先されますね。

8月24日
Excel。条件付き書式で、書式を設定するセルと条件の対象のセルが異なる場合は、
数式を使用してルールを作ることになりますね。

8月25日
Excel。条件付き書式の数式には関数も使えますので、メッチャ、奥が深くなりますね。

8月26日
Excel。SORT。Excel2010は値以外にもフォントの色やセルの色、
条件付き書式のアイコンでも並び替えることが出来ますね。

8月27日
Excel。SORT。合計行とか除いて並び替えをするときには、
その行はのぞかないといけないので、データを範囲選択する必要がありますね。

8月28日
Excel。Excelのデータを他のアプリケーションで使用するファイル形式にすることを、
エクスポートといいます。

Excelテクニック and  MS-Office recommended by PC training

https://www.facebook.com/exceltechniqueandmsoffice/

8/27/2016

Excel。CHECK。表のチェックがわかるように一覧表に変換したい。


Excel。表のチェックがわかるように一覧表に変換したい。

<INDEX+MATCH関数>


今回は、一人ひとりに一つの○がある表がありまして、それだとわかりにくいので、
一覧表にしたいというものなのですが、
イマイチ、何をどうしたいのか?
というのがわかりにくいと思いますので、次の表をご覧ください。

健康診断日の実施日の表があります。

スタッフがいつ、健康診断を受診するのかが、わかるように、”○”が入力されています。

本当の表は、スタッフさんが多くて、
この○を確認して、スタッフがいつ受診するのか?がわかりにくいので、
右側にあるように、スタッフさんの名前と受診日が入力されている表を作りたいというのが、
今回の目標の表なのです。

確かに、一人ひとり、○があるところをチェックして、
その日を入力するのは面倒ですから、何かいい方法を考えていきましょう。

まず、考え方としては、それぞれの行に○がどこにあるのか?を探させる必要があるので、
MATCH関数を使うとよさそうですね。

そして、日付を入力したいので、日付があるのが、B4:E4。

MATCH関数で場所。
すなわち、列がわかります。日付があるのが4行目なので、
行と列がわかるので、INDEX関数を使うと、うまくいくような気がします。

では、H5をクリックして、INDEX関数ダイアログボックスを表示しましょう。

引数の選択ダイアログボックスが表示されてきます。

INDEX関数は2タイプを選んでから作る関数ですので、
今回は、配列,行番号,列番号を選択して、OKボタンをクリックしましょう。

INDEX関数ダイアログボックスが表示されてきました。

配列には、日付入力されている。
$B$4:$E$4

オートフィルで数式をこぴーしますので、絶対参照を忘れずに設定しておきましょう。

行番号ですが、
ここで、MATCH関数ダイアログボックスを表示して作成していきます。

また、列番号は、省略もできますが、0(ゼロ)でもOKですね。

では、行番号のボックスをクリックして、MATCH関数ダイアログボックスを表示していきます。

検索値には、"○"
検査範囲には、B5:E5
照合の種類は、完全一致ですので、0(ゼロ)を入力します。

そして、OKボタンをクリックして、オートフィルで数式をコピーしましょう。

これで完成しましたね。ちなみに、H5の数式は、このようになります。

=INDEX($B$4:$E$4,MATCH("○",B5:E5,0))


今回使いました、INDEX関数やMATCH関数は、色々使える関数ですので、
少しずつでもいいので、練習すると、実務でも使える関数になるかと思います。

実務で使えそうな機会がありましたら、是非使ってみてください。

8/24/2016

Excel。Line Graph。折れ線グラフを、横でなくて、縦で折れ線グラフを描く方法。


Excel。折れ線グラフを、横でなくて、縦で折れ線グラフを描く方法。

<縦方向折れ線グラフ>


新聞でよくみかける。

縦方向の折れ線グラフ。

これをどうにかExcelで表現することは出来ないかなぁ~と考えて、
まぁ折れ線グラフそのものではないんだけど、
見た目には、縦方向の折れ線グラフの作り方を紹介したいと思います。

結果から言うと、Excelには、縦方向の折れ線グラフは用意されていないので、
縦方向の折れ線グラフなのですが、使うのは、

【散布図】。


で、どのようなグラフなのかというと、下記のようなグラフです。

このデータは、
気象庁さんからの横浜市の2016年6月1日~10日までの
最高気温と最低気温を使わせていただいております。

なかなか、難易度が高いように見えますが、
それほど難易度は高くはないかと思いますので、挑戦してみてくださいね。

ということで、早速、作り方をご紹介していきましょう。

まず、下記のようなデータを用意します。

E列は、散布図にするのですが、グラフの項目名を表示するためのデータです。

これは、疑似データなので、0(ゼロ)とします。

F列は、散布図のY軸にあたる部分で、1・2・3と1つおきだと、
目盛の上にマーカーを描いてしまうので、その間にマーカーを描きたいので、
0.5おきの数値を入れております。

最初うちは、動きがよくわからないと思いますので、作ってから、
変えてみると0.5ずつなのかはわかります。

さて、A1:C11とE1:E11を範囲選択してスタートします。

手順としては、最初に、縦軸の項目名を表示するために、
ワザと横棒グラフを作成して、
そのあとに、散布図に変換していくというテクニックを使って、作成します。


なぜ、そんなことをするのかというと、散布図を最初から書いてみるとこのようなグラフになります。

このような、見た目、普通の折れ線グラフになってしまうのです。

そして、E列のデータをいれないと、最終的に、縦軸の項目名が変わってしまうのです。


では、改めて、A1:C11とE1:E11を範囲選択して、横棒グラフを作成しましょう。

グラフのサイズは大きくしてあります。また凡例は下に移動しております。

まず、最高気温を選択して、グラフの種類の変更から、
散布図の散布図(直線とマーカー)を選んでOKボタンをクリックしましょう。

すると、グラフがこのようになりました。


なんじゃ、こりゃ!と思うでしょうけど、作成を続けていきます。

今度は最低気温を散布図に変更します。

すでに、触ることが不可能になっていますので、グラフの要素から最低気温を選択して、
先程と同じように散布図の散布図(直線とマーカー)に変更します。

そもそも、縦の項目名の為だけに横棒グラフするためのデータでしたので、
散布図にグラフを変えても、データがおかしいので、データの範囲を修正していきます。

まず、最高気温を選択して、デザインタブのデータの選択をクリックします。

データソースの選択ダイアログボックスが表示されますので、
凡例項目(系列)の最高気温を選択して、編集ボタンをクリックします。

系列の編集ダイアログボックスが表示されますので、
系列Xの値を、A列からC列に変更します。
系列Yの値を、C列からF列に変更します。
OKボタンをクリックします。


今度は、最低気温を修正しますので、最低気温を選択して、編集ボタンをクリックします。

系列の編集ダイアログボックスが表示されますので、
系列Xの値を、A列からB列に変更します。
系列Yの値を、B列からF列に変更します。
OKボタンをクリックします。


データソースの選択ダイアログボックスでもOKボタンをクリックしましょう。

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

ここまでくれば、あと一息。綺麗にしていきましょう。

まずは、日付の第1縦軸をクリックして、軸の書式設定ダイアログボックスを表示します。

日付を逆順にするにチェックをつけて、反転させます。

データが逆になっていますので、
今度は、第2縦軸をクリックして、軸の書式設定ダイアログボックスを表示しましょう。

軸を反転するにチェックをいれます。

また、グラフのサイズを変えた際に、最小値・最大値・目盛間隔が変わってしまうと、
グラフがおかしくなってしまうので、固定で止めておきましょう。

そして、今回は、この第2縦軸は不要なので、表示しないようにしますので、

目盛の種類をなし。軸ラベルもなしに変更して、閉じるボタンをクリックしましょう。

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

最後に、凡例からxを削除して、横軸の最小値を14に変更します。

データラベルも追加するといいでしょう。

これで、完成しました。

散布図を使うと、面白いグラフを作ることも可能になりますので、
何か機会がありましたら、作ってみてはいかがでしょうか?

8/23/2016

今週のFacebookページの投稿 2016/8/15-2016/8/21

今週のFacebookページの投稿 2016/8/15-2016/8/21

<Facebookページ>

Facebookページで【書いてみた】ワンポイントです。

8月15日
Excel。
セルに名前を定義しておくと、目的のセルを素早く選択したり、
数式に使用することが出来ますね。

8月16日
Excel。
セル範囲に名前を定義しておくと、目的のセル範囲を素早く選択したり、
数式に使用することが出来ますね。

8月17日
Excel。
名前の定義をすると、既定ではシート名とセル番地が絶対参照で定義されますね。

8月18日
Excel。
3-D集計・串刺し集計は集計対象の表及びセルが共通でないと計算できませんね。

8月19日
Excel。
3-D集計・串刺し集計は
SUM/AVERAGE/COUNT/COUNTA/MAX/MINでできますよ

8月20日
Excel。
スパークラインは、表のデータを見ながら、
傾向を視覚的に把握確認できるツールですね。

8月21日
Excel。
スパークラインは、マーカーを表示して最大や最小を強調することが出来ますね

Excelテクニック and  MS-Office recommended by PC training

https://www.facebook.com/exceltechniqueandmsoffice/

8/21/2016

Excel。Number。日付付きの通し番号を作って管理したいけど、どうしたらいいの?

Excel。日付付きの通し番号を作って管理したいけど、どうしたらいいの?

<TEXT関数とCOUNTIF関数>


Excelの表示形式というのは、なかなか難しいというか、苦手な人も多いようで、
日付を使った通し番号を作りたいのだけど、
なかなかうまくいかなくて、いいアイディアはないですかね?

ということでしたので、「どんなことしたいの?」ということで、次のようなものした。

A列の管理番号は、日付にその日の件数を加えたコードで管理したいんですね。

このような管理番号を作りたい場合には、
一度に数式を作るよりも作業列を使ってみるほうが、いいように思えますね。

ベテランさんならば、一発で数式を作れちゃうかもしれませんが、
慌てずに、一つずつ作っていきましょう。

E列に、7月1日を0701という表示で算出したいので、
まずは、TEXT関数を使って数式を作っていきましょう。

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

当然、手入力で数式を作っても構いません。

値には、B2
表示形式には、"mmdd"
あとは、OKボタンをクリックして、オートフィルでコピーしましょう。
数式は、

=TEXT(B2,"mmdd")


そして、次はF列にその日ごとの連番を作る必要があります。

1・2と入力して、オートフィルでコピーしても、
このような連番を作ると単なる通し番号になってしまいます。

そこで、COUNTIF関数を使って、連番を算出していきます。

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

範囲には、$B$2:B2
検索条件には、B2

このように範囲の始点を固定しておいてあげますと、
日付が変わった時点でカウントがクリアされる形になりますので、
日付ごとに連番を振りなおすことが出来るようになります。

あとは、OKボタンをクリックしてオートフィルでコピーしましょう。

算出できた、E列とF列を結合させて、A列を作成してきます。

ここでポイントになるのは、ただ、結合してしまうと、
01・02というように表示できませんので、この結合の時にも、
TEXT関数を使って、結合してあげる必要があります。

では、A2をクリックして、数式を作成してきます。

手入力でも十分なぐらい簡単な数式ですので、数式を直接作成してみましょう。

=E2&TEXT(F2,"00")


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

これで、日付+通し番号で管理番号を作ることができましたね。

ちなみに、いっぺんに、数式を作るとしたら、

=TEXT(B2,"mmdd")&TEXT(COUNTIF($B$2:B2,B2),"00")

というようになります。
ちょっと長い数式というか、単に2つの数式を結合しただけですので、
無理に一発で算出しないで、
今回のように作業列を使って作成する方法でもいいと思いますよ。

8/18/2016

Excel。Graph。時間経過の折れ線グラフ。実は散布図で作るとより綺麗に描けるのです。


Excel。時間経過の折れ線グラフ。実は散布図で作るとより綺麗に描けるのです。

<時間経過折れ線グラフ>


色んなグラフをご紹介しておりますが、今回ご紹介するのは、
折れ線グラフ。別に普通につくってもいいのですが、
時間経過に関する折れ線グラフの場合には、
ただ折れ線グラフを作るのもいいのですが、散布図で作ってみるというのもいいですよ。

ということで、まずは、下記の表を使って、通常のマーカー付き折れ線グラフを作ってきます。

A2:B21を範囲選択して、折れ線の中から、マーカー付き折れ線グラフを選んで、
グラフを作成してみましょう。

マーカー付きグラフが表示されましたら、綺麗に整えます。

グラフタイトルを削除して、凡例を下側に移動して、サイズを大きくしております。

別にこれで問題はないのですが、あえて、細かいことになるのですが、
0:00~6:00の時間帯が、
急に上がっているのと、横軸。時間ごとにプロットされていて、細かいというか、
わかりにくくなってしまっています。

そして、交点0(ゼロ)から始まっていませんね。

そこを改善するには、【散布図】を使って作る必要が出てくるのです。

では、同じ範囲で、今度は、【散布図】でグラフを作成してみましょう。

A2:B21を範囲選択して、散布図から、散布図(直線とマーカー)グラフを選んで、
グラフを作成してみましょう。

先程と同じように、グラフタイトルを削除して、
凡例を下側に移動して、サイズを調整しましょう。

変なグラフが出来たじゃないか?と思った方も多いと思いますが、
横軸をアレンジするだけで、完成します。

散布図を使うと、交点0(ゼロ)ですでに始まっているグラフになっていますよね。

では、横(値)軸をダブルクリック。または、横(値)軸をクリックして、
レイアウトタブの選択対象の書式設定をクリックして、
軸の書式設定ダイアログボックスを表示しましょう。

変更する場所は、
最小値を固定で0(ゼロ)にします。
最大値を固定で、1.0にします。
目盛間隔を固定で、0.25にします。

0.25の理由は、Excelでは、1日が1。

つまりシリアル値が1なので、
6時間置きに目盛りを表示させる場合、
1÷24で、0.04167が1時間なので、この0.04167を6倍すると、0.25になります。

目盛間隔を固定で、0.04167にします。

0.04167の理由は、目盛間隔は、1時間ごとの目盛間隔にしたいので、
先程書きましたように、1÷24で、0.04167が1時間なので、0.04167を入力します。

あと、
目盛の種類を内向き
補助目盛の種類を内向き
に変更しましょう。

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

0:00から6:00の間も急坂ではなくなりましたし、
横軸もわかりやすくなり、さらに、交点0(ゼロ)というグラフが作れましたね。

このように、状況によっては、折れ線グラフにこだわらず、
散布図というアイディアもありますので、何かの際には、使ってみてはいかがでしょうか?

8/17/2016

今週のFacebookページの投稿 2016/8/8-2016/8/14

今週のFacebookページの投稿 2016/8/8-2016/8/14

<Facebookページ>


Facebookページで【書いてみた】ワンポイントです。

8月8日
Excel。表示形式を設定していないセルにはG/標準が設定されていますね。

8月9日
Excel。
表示形式のG/標準「G」とは
General(一般の、普遍的な、全般の)頭文字の「G」の略です。
英語版のExcelでは「G/標準」は「General」となっています。

8月10日
Excel。
ミニツールバーが邪魔な時は、
ファイルタブ→オブション→基本設定→選択時にミニツールバーを表示する
→OKボタンで非表示にできますね。

8月11日
Excel。ジャンプ機能を使うと数値データのセルをまとめて選択することが出来ますね。

8月12日
Excel。シート名を変更するには、
シート名の上でダブルクリックすると修正可能になりますね。

8月13日
Excel。シート名を変更するには、シート名の上で右クリックをして、
メニューから名前の変更で修正可能ですね。

8月14日
Excel。シート見出しの色は、設定した後、
薄いなぁ~と感じた時は、別のシートに移動してみましょう。
きっと指定した色がついているのが確認できますよ。

Excelテクニック and  MS-Office recommended by PC training

https://www.facebook.com/exceltechniqueandmsoffice/

8/15/2016

Excel。EDATEとEOMONTH。何か月後は求められるのに、何年後の日付を求めるには?


Excel。EDATEとEOMONTH。何か月後は求められるのに、何年後の日付を求めるには?

<EDATE関数とEOMONTH関数>


Excelセミナーのネタをアレコレ考えているときに、アレレ。
簡単にできないということがありまして、
いざ現場でやろうとしたときに、出来ない!となると、作業効率が悪化してしまいます。

そこで、今回は、EDATE関数とEOMONTH関数の変なクセ?について書いてみようかと。

まずは、EDATE関数とEOMONTH関数は、ご存知でしょうか?

EDATE関数は、何か月後の日付を算出してくれる関数ですね。
例えば、今から6か月後といって、いちいち、数えなくてもすぐに算出してくれる関数です。

そして、
EOMONTH関数は、何か月後の末日を算出してくれる関数ですね。
よく、末締めの翌末払いということで、使われる関数ですね。

下記の表が、そのEDATE関数とEOMONTH関数を使って、作った表です。

補足説明をしておきましょう。

B列とE列には、A列とD列をセル参照させて、
表示形式をユーザー定義で、”aaa”に変更しております。

日付を曜日の表示形式に変える設定を行っております。

D列には、それぞれの関数を使った数式が設定されています。
D3には、

=EDATE(A3,C3)

D9には、

=EOMONTH(A9,C9)

これらの関数は、引数(カッコの中)が少ないので、手入力で作成してもいい関数ですね。
Excel関数になれて来たら、少しずつ、ダイアログボックスも大切ですが、
手入力に慣れていくといいですよね。

では、次の表をご覧ください。

契約月から契約年に変えてみました。

さて、このように、日付を求めてくれる関数たちなのですが、この関数。

”何か月後”という、月単位でないと算出してくれない関数なのです。

例えば、D3の数式をダイアログボックスで見てみましょう。

EDATE関数ダイアログボックス。月になっていますよね。

ですので、何年後というのを算出しようとすると、

×12(12倍)する必要があるのです。


これを忘れてしまうと、年での算出ということは出来ないのです。

では、D3を改めて作成してみましょう。

EDATE関数ダイアログボックスを表示します。

開始日は、A3
月は、C3*12

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

D列のリース終了日の書式が日付でなくなってしまった場合には、
表示形式を日付に戻すようにしましょう。

書式のコピー/貼り付けボタンを使ってもいいですね。

同じように、EOMONTH関数のD9:D11も算出してみましょう。

このように、Excelの日付に関数の中には、月。

で設定するものも多くありますので、
12でかけたり、12で割ったりする必要が発生することがありますので、
注意する点の一つですね。

なお、1年6カ月なんて場合には、素直に、18カ月というように、”月”で管理しておくのが、
EDATE関数・EOMONTH関数とも楽だと思います。