エクセルの使い方(最小二乗法)

目標

測定データから次のようなグラフを作成する

  1. 実験データを図示する
  2. 最小二乗法で線を引く
  3. タイトルや軸の物理量など、グラフの体裁を整える

「出来る」という人は→上級者問題へ

1. エクセルの起動

  • Mac(OS-X)の場合
    • 画面下のメニューからエクセルボタン(緑色のXのような形)を押す
    • 新規作成で[エクセル ブック]を選ぶ
    • メニューから[表示]-[標準]を選んだほうが、解析はやりやすいでしょう。
  • Windowsの場合
    • [スタート]ボタンから[全てのプログラム]-[Microsoft Excel] を選ぶ ([Microsoft]というフォルダの中に入っている場合あり)

2. データの入力

  • 適当な数値をセルに入力する
  • 文字列(物理量の名前や、単位)をセルに入力する
  • 数値は右寄せ、文字列は左寄せで表示される。
  • 数値を文字列としたいときは、先頭に ‘ (アポストロフィ)を入れる
  • マッキントッシュでは、日本語-英語の入力は[りんごボタン]+[スペース] で切り替え
  • Windowsでは、日本語-英語の入力は[半角/全角]ボタン で切り替え

3. コピー、カット、ペースト

  • セルのコピー、カット、ペーストが行えるようにする
  • 複数のセルをペーストするときは、左上のセルを指定する
  • 消去するときは、セルを選択して Delete キーを押す
  • 範囲を選択するときに、Shiftキーを押しながらカーソルキーを操作すると、マウスを使わなくても範囲指定することが出来る。その際、Endキーを押した後カーソルキーを押すと、一気にデータの終わりまで移動できる。データが複数ページにわたる場合に便利。
  • Ctrl+C(同時押し), Ctrl+X, Ctrl+Vでコピー、カット、ペーストが行えます。(ショートカットキーと呼ぶ)

4. 計算式入力

  • エクセル上で計算が行えるようにする
    セルの先頭に = (イコール)をつけると、そのセルは計算式として扱われる
    (たとえば、空いているセルに =2+5 と入力してみよ)
  • 表示と実体(上のほうの表示)の違いに注意
    Macでは、excelのメニューから[表示]-[数式バー]を選択し、セルの実体が表示されるようにする
  • 使える記号
    +, -, *, / (四則演算)
    (, ) 括弧 通常の計算と同じ優先順位で計算が行われる。いくつでも使えるが、セルの中で左右の括弧の数が違っているとエラー
    ^ (ハット) (累乗 2の3乗 → =2^3)
    log(), ln(), exp(), sqrt() 常用対数、自然対数、exp, ルート 括弧の中に数値を入れる
    pi() 円周率(括弧の中には数字を入れない)
    sin(), cos() 括弧の中には角度を入れる(ラジアン単位であることに注意)
    など (メニュー中の[挿入]-[関数]で一覧が出てきます。)
    radians() 度をラジアンに変換
    degrees() ラジアンを度に変換
  • 計算式は全て半角文字でなくてはならない

問題1

次の計算をせよ

  1. 3 ~ 7 の整数の和の 4 倍
  2. 2 の 16 乗
  3. ルート 5
  4. sin 30°

5. 他のセルの参照

  • 他のセルの参照が使えるようにする
    計算式(先頭に=(イコール))の中で、A9やB1という形式で他のセルの値を参照できる。

問題2

  1. 空いたセルに数値をいれ、その隣のセルに最初のセルの値の2倍、その隣のセルにさらに2倍した値が表示されるようにせよ。最初の値を変更すると、他のセルの値が連動して変わることを確かめよ

6. 実験データの入力

  • 実験データを入力する

実験データの例は別ページにあります。最初は1回目の実験結果だけを入力してください。

7. 参照を含んだコピー

  • 参照を含んだセルをコピーするときの振る舞いを理解する。
    参照を含んだセルをコピーすると、参照位置が自動的に移動してコピーされる。

問題3

  1. 最初に入力したデータの隣の列に、ベックマン温度計の読みを2倍したものを表示せよ
  2. さらにその隣の列に、最初の温度を 0 とした温度差(温度の変化)を表示せよ
  • $マークをつけておくと、コピーしても位置が移動しない。
    $A1 → コピーしても列は固定
    A$1 → コピーしても行は固定
    $A$1 → 両方固定
    ([F4]キー(キーボードの一番上)を押すと、上記を切り替えることができます。)

問題4

  1. 「2倍」と固定するのではなく、列の一番上のセルに「何倍するか」の数値を指定できるようにせよ。
    (列の一番上のセルに、例えば3 と入力すると、その列にベックマン温度計の読みの3倍が表示されるようにする)
  • 値が連動して欲しくない場合がある。その場合、数式を含むセルをコピーし、それをペーストする際に右クリックし、[形式を選択して貼り付け]を選び、[値]をえらぶ。
    こうすると、貼り付けたセルは、表示と実体(上のほうの表示)が両方とも数値になっていることを確かめよ

発展問題

1 mol 中で速度が v と v+dv の間にある分子の割合は次式(Maxwell-Boltzmann式)で与えられる。

 \displaystyle \frac{{\rm d}N/N_{\rm A}}{{\rm d}v} = 4 \pi v^2 \left( \frac{m}{2 \pi k T} \right)^{\frac{3}{2}} \exp \left( - \frac{m v^2}{2 k T} \right)

k; ボルツマン定数 (1.381 × 10-23 J K-1)
m; 分子の質量 (kg)
T; 温度(K)

3000 m/s までの v について、(dN/NA)/dv を、298 K の He (分子量4.00), Ne (20.18), Ar (39.95), Kr (83.80) について求めよ。

8. 有効数字のそろえ方

  • 最初の状態では0.240 の最後の 0 が省略されてしまい、0.24 と表示されている。これを調整できるようにする。
    →セルを選択して右クリック、[セルの書式設定]-[表示形式]で[数値]の桁数を指定する
  • Macで右クリックができない　という場合
    メニューのりんごマークから[システム設定]-[マウス] で右ボタンのところに[副メニュー]を設定

9. 罫線

  • 罫線(セルの周りの線)を変更できるようにし、レポートの「表｣として提出できるように体裁を整える
    セルを選択して右クリック、[セルの書式設定]-[罫線]から設定
  • セルを選択する際に、罫線を引きたい領域を選んでおく必要がある。
  • レポート提出の際は、表は罫線はやたらと引かず、見やすくするための最小限のものを引くようにする。出版されている論文を参照のこと。
  • 表には必ずタイトルをつけなくてはならない。表のタイトルは、表の｢上｣につける。

10. グラフ作成

  • グラフを作成する
    グラフにするデータを選択し、メニューから[挿入]-[グラフ]で[散布図]を選択
    ! [折れ線]を選ばないよう注意 ! ([折れ線]は横軸が固定されてしまうので)
  • タイトルや軸の名前など
    →右クリック、「グラフのオプション」-[タイトルとラベル]から設定

    • 図には必ずタイトルをつけなくてはならない。エクセルは図の上にタイトルをつけてしまうが、自然科学分野では図のタイトルは、図の「下」につける。位置を修正すること
    • グラフの軸には「必ず｣物理量と、単位をつけなくてはならない。
  • グラフの中の横線
    →右クリック、「グラフのオプション」-[目盛線]から設定
    不必要なものは消す
  • 線や点の有無、色、種類
    →凡例(はんれい)をクリック
    データ点は必ず示さなくてはならない。
    データ点を折れ線でつないではいけない
    点は「得られた実験結果」を、線は「その解釈」を示す。
    直線でない解釈をする場合、線は印刷した後、手で書き加えてもよい。
  • 領域、軸の色
    →右クリック、「プロットエリアの書式設定」から設定
    無意味に色をつけない
  • 凡例中の文字列
    →右クリック、[元のデータ]-[系列]-[名前]を修正
  • 表示範囲
    グラフ中の軸をクリックし、[軸の書式設定]-[目盛]の[最大値]、[最小値]を設定。
    「見せたい部分」が大きくなるよう工夫しなければならない。

問題 5

このページの一番上の図を参考に、実験データのグラフをタイトルや軸の物理量をつけて作成せよ。

11. 最小2乗解析

  • 最小2乗法により直線フィッティングを行う。
    傾きをslope()、切片をintercept()命令を使って算出する
  • 算出した傾き、切片を使って、直線を作成する
  • slope()の使い方
    書式 =slope(C6:C12, B6:B12)
    C6:C12はYの値、B6:B12はXの値を範囲で指定関数ウィザードを使うなら
    空いたセルを選択しておいて
    メニューから[fx]と書かれたボタンを押し、(またはメニューから[挿入]-[関数])
    [統計]-[SLOPE]を選択
    [既知のY]、[既知のX]と書かれている部分の三角マークを押して、直線フィッティングを行う領域を指定する。
  • intercept() もほぼ同様。
  • 傾き(a)、切片(b)がわかれば、横軸(t)の値を使って、データ列の隣に、直線上の値を計算することが出来る 参考
  • slope()やintercept()は、最小2乗法によって得られる結果を直接算出する。最小2乗法は、測定点における直線との差の2乗を最小にするような直線(ax+b)の傾き(a)と切片(b)を求める方法である。原理は各自理解しておくこと。(参考文献: 「物理化学実験法」、鮫島実三郎著、裳華房、p. 12)
  • 解析を行うのに便利な関数として他に、sum() (合計を出す)、count() (データの個数を数える)、average() (平均を求める)等がある
  • 範囲を指定する場合は A1:A20 のようにする (開始セル コロン 終了セル)
  • 直線を作成する際は7.で述べた$マークを使うとよい
  • 評価値(決定係数) R2 を求めたい場合は次の式により計算する
    \displaystyle R^2 = 1 - \frac{\sum\limits_{i=1}^n (y_i-z_i)^2}{\sum\limits_{i=1}^n (y_i-\bar{y})^2}

    yi は測定値、zi はその測定値に対応した予測値、\bar{y}は全測定値の平均

参考画面

問題 6

1回目の実験データの中間部分について、最小2乗法を用いて近似直線を書き加えよ。

別のやり方↓

  1. グラフを作成した後に、グラフを選択した状態で、上のメニューから[グラフ]-[近似曲線の追加]を選ぶ
  2. [種類]から[線形近似]を選ぶ
  3. 傾きや切片を知りたい場合は[オプション]-[数式を表示]を選ぶ
  4. 傾きや切片の値を細かく知りたい場合は別ページを参照
  • この方法の場合、フィッティングする範囲を選べない。フィッティングしたい部分だけを抜き出して列にしておくなどの工夫が必要
  • 実験データとよく合うからといって、意味なく(理論的な裏付けなく)、複雑な多項式近似等を用いてはならない

12. データのセーブ、グラフの利用

  • 作成したグラフは、エクセルのファイル(ワークシート)としてセーブする
  • 作成したグラフはコピー＆ペーストでワード等に貼り付けることが出来る
  • エクセル上のセルも(罫線つきで) ワードに貼り付けることが出来る

13. 複数データの処理

  • 複数のデータのうち、一緒に示したほうがわかりやすいものや比較するべきものは、「必ず」同じグラフ内に示すようにする
    最初のデータの2回目、3回目を入力し、同一のグラフ内に示せ
  • 3回目のデータのように、xの値が異なっている場合、グラフ内に重ねる方法は以下の2つ
  • グラフを右クリックし、[元のデータ]-[系列]-[追加]で、新しいデータのXとYの範囲を指定する。
  • この画面のように(参考画面)、xの値を一番左の列に入力し、各データ(yの値)を各列に並べて入力してグラフを作成する。

問題 7

1回目のデータの最初の部分、最後の部分に近似直線を加えよ。
可能であれば、同様の解析を2回目、3回目のデータについても行う。
(このように同様の解析を行う際には、コピー&ペーストを活用して手間が減らせるように、シートが工夫できているとよい。)

14. その他、レポート作成時のTips

別ページにまとめました。