花園・パソコン研究会

アクセスカウンタ

zoom RSS Excelの関数とグラフを使う・バイオリズムグラフの作成

<<   作成日時 : 2013/07/01 18:09   >>

ブログ気持玉 0 / トラックバック 0 / コメント 0

バイオリズムは、最近は流行りませんが、仕事や車の運転などのミスや事故を防ぐために、今も特定の職場では活用されています。
身体は23日周期・感情は28日周期・知性は33日周期で好・不調がサインカーブで繰り返され、0を通過するときに事故が起きるとされています。
生年月日を入れて、今日をはさむ1週間のバイオリズムを計算し、これをグラフに表してみましょう。エクセルの関数やグラフの、良い練習教材になります。

bio01

■定数を入れるフォームを作る

bio02

A3に生年月日を入れます。
B3には、=TODAY() と入れて、今日の日付が自動ではいる関数を入れます。

TODAY関数は、()に入れる引数はないのですが、関数なので=と()は省略できません。

A4から縦に、23・28・33の定数を入れ、そのタイトルとして、B4から、身体・感情・知性の項目を入れます。

これは、後でグラフの項目名になるもので、グラフの範囲に入れたいので、このように定数の右に入れます。

 

■C3から、日にちを入れる式を入れる

といっても、C3には、=B3 - 3 を、D3には、=C3 +  1 を入れます。自動で書式は日付になりますが、こちらは年は不要なので、セルの書式を○月○日に変えておきます。C3には、3日前の日付が入り、その後は1日ごとに進みます。

bio03

D3に書いた関数は、そのまま右へドラッグして、「以下同様」の関数を入れます。
関数を使うポイントは、このようにドラッグで書き加えていくことです。そのために、書き方を工夫します。ドラッグでコピー・貼り付けしたときに、うまく座標が変わるように工夫します。

bio04

D3の =C3 + 1 の式は、となり+1なので、そのまま+十字をI3までドラッグするだけです。

■バイオリズム関数を作る準備 周期の位置を求める

bio05生まれた日から、その日までの日数を周期で割った余りを求めます。このような目的で使うのが、MOD関数です。


=MOD(数,除数) であまりが求められます。 例えば =MOD(10,3)は、10を3で割ったあまりなので、1になります。

ここでは、C4に式を書きます。
C3-A3が、割られる数で、A4が割る数です。

=MOD (C3−A3,A4) という式で、6月28日の数値が求められます。この関係は、横も縦も同じように計算します。従って、この式をコピー貼り付けで切るように作ります。
式を入れた後は、自動的に書式が日付になっているので、標準に直してください。

■引数の座標を、コピーできるように、固定する

bio06

C4の式を工夫します。

生年月日が入っているA3は、縦横に移動した式でも同じものを使います。

従って、列も行も固定する必要があるので、
$A$3 のように、列のAにも、行の3にも$を付けて固定します。

C3は、横に移動したときはD3に、下に移動したときは、3列目に日付があることは変わらないので、C3のままになります。

ということは、行の3は固定されるが、列のCはDEF・・と代わって欲しいので、3だけ固定します。 C$3 となります。
同様に、周期の数字A4は、下ではA5・A6になりますが、横へ移動してもAは変わりません。 $A3 となります。

C4に、 =MOD(C$3-$A$3,$A4) という式を入れれば、右にも下にもコピーできます。

ここが一番のポイントです。実際に、C4の式を右や下にドラッグしてエラーがないことを確認します。この式はまだ完成したいるわけではないので、すべてに貼り付ける必要はありません。

■複数の関数を使う (引数の中に関数)

MOD関数で求められた数字は、それぞれの周期の中の位置を表すだけです。これを三角関数で使える角度に直して、これのSIN(サイン)の値が、最終的な目的の数字です。

第一段階は、このMODの数値を、$A4 にある周期で割ります。式の後ろに、 /$A4 を追加します。数式バーの中で書き込んで、エンターキィで抜けます。式を書いているときは、マウスで出ることは出来ません。必ずエンターで抜けます。
このとき、再び書式が日付に直されるので、標準に直します。引数の中に日付があるので、自動的に日付になってしまいます。

bio07

次に、この数値に3.14の2倍を掛け算します。昔習った、2π(2パイ)を掛け算して、三角関数で使える角度にします。

6.28を掛けても良いのですが、ここでは正確にPI()という関数で3.14159265358979という円周率を入れます。
同じように、数式バーで、イコールのすぐ後に 2*PI()* を追加します。

bio08

最後に、この値をSINの中に入れます。=の後ろに =SIN ( まで入れて、一番後ろに閉じ括弧 ) を入れて挟みます。

bio09

これで完成です!一度に書くには大変面倒ですが、式に式を追加して完成させていきます。

分かりにくい人は、下の式をコピーして、C4の数式バーに貼り付けて、エンターキィで抜けてください。

=SIN(2*PI()*MOD(C$3-$A$3,$A4)/$A4

この式は、右にも下にもドラッグでコピー貼り付けできます。そのように作れば、式は一つ作るだけで済むのです。

bio10

■グラフを作る

グラフを作る範囲は、B3からI6です。B3には、=TODAY()という関数が入っていますが、ここが項目の交差点なので、グラフ範囲のスタートになります。これで、日付と身体・感情・知性の3本の折れ線グラフを作ります。

挿入の、折れ線グラフを選び、マーカーつき折れ線を選びます。

bio11

あっという間に、グラフが出来ます。

bio12

■データとグラフを追加する

バイオリズムは、それぞれの周期で-1から+1までを変化するサインカーブです。それぞれの線が、0を通過するときが要注意の日です。それが分かりやすいように、オリジナルの危険指数グラフを追加してみました。

B7に 危険指数 という項目を書いて、 C7に以下の関数を書いて、これをI列までコピーします。

=1-2*SQTR(ABS(C4*C5*C6))  見やすいように太字にしました。$で固定するものはありません。

3つの値を掛け算して、その値の絶対値を取り、平方根を求めて、0に近い数値が危険指数を大きくするように作った関数です。危険指数が0.8を超える日は、事故やミスに注意してください。

bio13

グラフ範囲を選択し、この表範囲を右下のコーナーをドラッグして広げます。

bio14

これで、グラフが自動的に追加されます。

bio15

■グラフの編集

グラフや、項目をクリックして選択し、右クリックメニューで書式を設定することで、編集できます。

線の色などは、自動を外して、単色を選んで変更します。基本的には、色は自動で決まります。

bio16


月別リンク

ブログ気持玉

クリックして気持ちを伝えよう!
ログインしてクリックすれば、自分のブログへのリンクが付きます。
→ログインへ

コメント(0件)

内 容 ニックネーム/日時

コメントする help

ニックネーム
本 文
Excelの関数とグラフを使う・バイオリズムグラフの作成 花園・パソコン研究会/BIGLOBEウェブリブログ
文字サイズ:       閉じる