松原駅前商店会 PC研修会 On the Web アクセスGETキャンペーン アップロード店 作品発表

今月の力試し エクセル（Excel）編 4

『Excelで データの分析』をしてみましょう！
今回は数字のデータから、分析とグラフの作成に挑戦です。

NHKの「計るだけダイエット」を実践された方の2ヶ月分のデータをいただきました。
起床時・夕食後・就寝前に1日3回測定した体重がそれぞれ記載されています。
ほとんどもれなく計量されており、扱いやすい形になっています。
このデータを提供された方を仮に「I氏」と名付けて問題を進めていきます。
今回は「解説とヒント」を見て模範解答を作成できる方は中級を名乗っていただいてOKと思いますよ。チャレンジ下さい。

作成出来た方は12月中頃に、稲田研修員まで添付で提出下さい。
12月の研修員、「ガラガラ真っ最中」大忙しです。でもタテヨコピッを買いました。
　問題 　解説と ヒント 　模範解答
　今回の禁じ手：『数字・書式の直接入力』

★ データの取り込み
最初に下記のデータをエクセルに貼り付けます。
今回は表内で右クリックしてみましょう。
ウインドウに「Microsoft Excelにエクスポート」があればクリックします。
このようにしてWebデータを取り込むことも出来ます。

起床時(kg) 夕食後(kg) 就寝前(kg)
9/1 76.6 77.8 77.4
9/2 76.8 77.6 77.2
9/3 76.4 77.8 78.6
9/4 78.2 78.1 78.0
9/5 77.2 76.8 未測定
9/6 77.0 78.0 77.2
9/7 76.6 77.2 76.6
9/8 76.0 77.0 76.4
9/9 75.8 76.8 76.4
9/10 75.8 77.4 77.8
9/11 77.4 77.8 77.2
9/12 76.4 77.6 未測定
9/13 76.8 77.4 76.6
9/14 76.0 76.2 76.0
9/15 75.4 76.6 76.6
9/16 75.8 76.8 76.4
9/17 75.8 76.8 77.6
9/18 76.8 77.4 77.2
9/19 76.2 77.2 76.8
9/20 76.0 76.6 76.0
9/21 75.4 76.0 75.8
9/22 75.0 76.0 75.8
9/23 75.2 76.2 75.6
9/24 75.0 76.0 76.6
9/25 75.8 76.2 75.8
9/26 75.0 75.8 75.4
9/27 75.0 75.8 75.2
9/28 74.6 75.6 75.6
9/29 74.8 75.6 75.6
9/30 74.8 75.6 75.4
10/1 74.8 75.2 未測定
10/2 75.0 75.2 74.8
10/3 74.2 75.2 75.0
10/4 74.4 74.8 74.6
10/5 74.0 74.4 75.4
10/6 75.0 74.8 74.6
10/7 74.0 74.4 74.4
10/8 73.8 74.6 74.8
10/9 74.0 74.6 74.4
10/10 73.6 74.4 74.4
10/11 73.6 74.2 74.2
10/12 73.4 73.8 73.8
10/13 73.0 74.2 74.0
10/14 73.6 74.2 74.2
10/15 73.2 73.8 74.0
10/16 73.2 74.0 73.6
10/17 73.0 73.6 73.0
10/18 72.6 73.8 74.0
10/19 73.2 73.2 74.0
10/20 73.0 73.4 73.4
10/21 72.6 73.4 73.4
10/22 72.8 73.6 74.2
10/23 73.6 74.2 74.0
10/24 73.2 73.4 73.2
10/25 72.6 73.2 73.0
10/26 72.4 73.4 72.8
10/27 73.2 74.2 73.2
10/28 73.6 74.0 72.8
10/29 72.2 73.0 73.2
10/30 72.6 73.2 73.0
10/31 72.2 74.0 73.2
　問題 1
データを提供いただいたI氏の身長は175cmです。
身長と体重よりBMI値が計算できます。
就寝前(kg)の右にBMI値の列を作成して、その日の最軽量体重より計算した値を表示させて下さい。但し、BMI値は小数点以下1桁までの表示とします。
※ BMI値の計算は 健康的にダイエット を参考にして下さい。
　問題 ２
I氏は前日の同じ時間に計った体重より減少していると非常にうれしいそうです。
縦の列で見て、前日よりも増加している値は赤色文字表示にして警告サインを出して下さい。
※ 例えば9/2起床時は前日76.6・当日76.8、増えているので赤色文字です。
　問題 ３
このダイエット法の良いところの一つに言い訳欄がある事です。
体重が減らなかった言い訳が書けるのです。
Ｉ氏の言い訳欄には次のようは記入があります。
これを、「コメントの挿入」を使って記入して下さい。
  • 9月3日；PC研修会でお楽しみの生ビール
  • 9月6日；好物のカレーの食べすぎ
  • 9月10日；ラーメン+PC研修会
  • 9月15日；スパゲティーで腹いっぱい
  • 9月17日；PC研修会でお楽しみの生ビール
  • 9月24日；PC研修会でお楽しみの生ビール
  • 9月28日；お好み焼き+ビール うまい
  • 10月1日；PC研修会でお楽しみの生ビール
  • 10月5日；ＩＴ委員会with阿部っち
  • 10月14日；キャラメルコーン等おやつほおばる
  • 10月15日；PC研修会でお楽しみの焼酎お湯割り
  • 10月18日；焼肉+ビール うまい満腹だ〜
  • 10月19日；台風+スナックでカラオケ
  • 10月22日；PC研修会でお楽しみのビール+牡蠣フライ
  • 10月29日；PC研修会にPCなしで参加のあげく、楽しみのズリ作り+お湯割りをいただく
※ここまでをシート名「計るだけダイエット」で保存してください。
   このシートが解答1になります。
　問題 ４
グラフを作成してみます。Excelを使うと、さまざまなグラフが作成できます。
I氏の体重とBMI値の変化をグラフに表示します。
縦軸・横軸等自由で結構です。
最適な種類のグラフを選んで作成して下さい。
このグラフのシート名を「測定値グラフ」にして下さい。
※このシートが解答2になります
　問題 ５
I氏の今後の体重の変化を予測してみましょう。
この数字のまま推移していくと、大晦日には何kgになっているのでしょうか？
下記を参考に3種類の方法で予測して下さい。

大晦日の体重を予測する？
ヘルプの「値を予測する」には、下記の3種の方法が記されていました。
新しいシート「大晦日予測値1」、「大晦日予測値2」、「大晦日予測値3」を作成して、起床時・夕食後・就寝前・BMI値の予測値をそれぞれの方法で出してみましょう。
<ヒント> どの範囲の値を参考にして予測するかちょっと考えてみましょうね。
大晦日予測値1

値を予測するセルのフィル ハンドルをドラッグすると…
大晦日予測値2  グラフに近似曲線を追加して、…
大晦日予測値3  値を予測するFORECAST関数を使って、…

※この3種のシートが解答3〜5になります。
体重変化の落ち着いてきた、10月または10月後半を対象に予測すると、
より正確な値が導き出せると思われます。
予測値が的中しても景品はございません。悪しからず。
これで完了。稲田研修員に送信です。

解説と ヒント バージョンを考慮せずに作成しており、表示・操作の違う場合もございます
簡単に今回利用する機能を解説（簡単に書きましたが、ちょっと長いです）
BMI値 BMI値＝体重(kg)÷身長(m)÷身長(m)
四則計算をするので最初に答えを表示するセルを選択します。
続いてエクセルの数式バーに「＝」を入力し、体重(kg)÷身長(m)÷身長(m)の計算をさせます。÷は「/」です。
条件付書式 条件よって文字を赤色にする時は、条件付書式を使います。最初の1個を考えて、残りは同じ条件でなので書式のコピーをしましょう。
まず、書式を設定するB3セルを選択し書式⇒条件付書式をクリックします。セルの値が・次の値よりも大きい・B2 で、赤色文字の書式を選びます。
コメントの挿入  オンマウスで、文字を表示させる機能がコメントです。
コメントを表示させたいセルを右クリックして「コメントの挿入」を選び、文字を入力します。
グラフの作成 グラフを作成する範囲内のセルを1つ選択して（又はグラフを作成する範囲をドラグして選択して）挿入⇒グラフをクリックします。するとグラフウイザードが表示されます。
ウィザード1/4；グラフの種類で今回は折れ線グラフを選びました
ウィザード2/4；元データの範囲が違っている時はここで修正します
ウィザード3/4；グラフタイトルと、X軸Y軸の単位を入れます
ウィザード4/4；新しいシートを選び、シート名を入れます
これでグラフが作成されます。が、数値Y軸が0〜90で表示されており変化がわかりません。従ってBMI値を第2軸（右軸）に移します。
最初に、BMI値のグラフをクリックし選択します。そのまま右クリックし「データ系列の書式設定」を選びます。軸タブで第2軸（上/右側）にチェックを入れ変えますBMI値の変化がわかるグラフになりました。次に第1軸の数字を右クリックして「軸の書式設定」の目盛タブで最大値・最小値を変えて体重の変化がわかるグラフにします。続いてY軸（横軸）同様に、月日の表示形式を見やすい横書きに変えて下さい。続いて、グラフのプロットエリア（グラフのフで折れ線の無い部分）で右クリックして「グラフのオプション」を選び、タイトルとラベルタブの、Y/第2数値軸にBMI値と入力します。
最後に、凡例・文字方向・折れ線色等を整形して完成です。
フィルハンドル 「値を予測するセルのフィル ハンドルをドラッグすると…」⇒オートフィルのことです。シート名「計るだけダイエット」をコピーしてシート「大晦日予測値1」を作成します。予測する元データの範囲を選択して12月31日までオートフィルして下さい。一番簡単な予測方法です。
近似曲線 シート名「計るだけダイエット」をコピーし月日を12月31日までオートフィルして、予測する範囲の元データ（不要な数値を削除）を作成します。
予測値の元データを作成し問題4同様のグラフを作成し、シート「大晦日予測値2」とします。折れ線を右クリックし、「近似曲線の追加」を選択します。11月・12月の空白部に予測グラフが作成されます。
FORECAST関数 シート名「計るだけダイエット」をコピーしてシート「大晦日予測値3」を作成します。10月30日の下部のセルA63に12月31日を作成します。答えを表示するセルB63を選択して、関数の挿入からFORECAST関数を選びます。
FORECAST(x,既知のy,既知のx) の3種の引数から予測値が求まります。今回の場合、x は12月31日（セルA63）・既知のy は予測対象とする体重範囲・既知のx は予測対象とした体重範囲の月日です。
答えが出たら、xと既知のxを絶対参照（F4キー使用）にして セルC63・D63にオートフィルして完成です。

模範解答
問題が難しいので作成できるか不安ですが、このような模範解答でいかがでしょうか？

 ;筆者の一言
今回は表計算ソフトExcelで、数字の表から問題を作りました。 基本的な計算、四則計算（加減乗除）。加は +、減は -、乗は *、除は / のマークで計算します。（ ）でくくると先に計算されます。算数と同じですね。続いて条件により書式（文字色・背景色等）を変える条件付書式です。最初に「セルの値が」で右側より条件を選びます。また「数式が」にすると右側に、＝関数で関数を使用して条件の設定が出来ます。and関数・or関数を使い複数の条件の設定も可能ですよ。グラフも簡単に描けますが、縦軸・横軸・第2軸の修正が必要ですね。わかりやすいグラフを選ぶのもテクニックの一つでしょう。さらに予測値は少し難しいですが、3種の方法を学習して楽しかったです。私もヘルプより予測方法をマスターし、勉強になりました。
尚、このテキストは松原駅前商店会 PC研修会で使用したテキストです  【 事業部 】 2004/11/5
Copyright(C)  2000.-2004. Isono.biz AllRights Reserved