Excelには、校正データを表示したり、ベストフィットの直線を計算するための関数が組み込まれています。化学の実験レポートを書くときや、機器の補正係数をプログラミングするときに役立ちます。

今回は、Excelを使って、グラフの作成、直線検量線の作図、検量線の計算式の表示、そして、Excelのslope関数とintercept関数を使って、検量線を使うための簡単な計算式を作成する方法について説明します。

検量線とその方法は何ですか？

校正を行うには、機器の読み取り値（例えば温度計の表示温度）と既知の標準値（例えば水の凝固点、沸点）を比較する必要があります。これにより、一連のデータペアを作成し、それを使って検量線を作成することができます。

水の氷点と沸点を用いて温度計の2点校正を行う場合、温度計を氷水（32°Fまたは0°C）に置いた場合と沸騰水（212°Fまたは100°C）に置いた場合の2組のデータを得ることができます。この2つのデータの組を点としてプロットし、その間に線（検量線）を引くと、温度計の応答が直線的であると仮定して、温度計の表示値に対応する線上の任意の点を選び、対応する「真の」温度を求めることができる。

つまり、温度計が57.2度を示したが、その値に対応する「標準」を測定したことがない場合、実際の温度を合理的に決定できるように、基本的には既知の2点間の情報を線で埋めるのである。

Excelの機能を使えば、2つのデータの組をグラフにしたり、トレンドライン（検量線）を加えたり、検量線の式をグラフ上に表示したりすることができます。これは視覚的に表示するのに便利ですが、Excelの傾きや切片の関数を使って、直線の方程式を計算することも可能です。これらの値を簡単な計算式に入力すると、任意の測定値をもとに「真の」値を自動的に計算することができるようになります。

例を見てみましょう。

この例では、X値とY値からなる10組のデータから検量線を導きます。X値は「標準」となり、科学計測器で測定する薬液の濃度から大理石発信機を制御するプログラムの入力変数まで、あらゆるものを表すことが可能です。

Yの値が「応答」となり、各薬液を測定したときの測定器の読み、あるいは各入力値で測定したエミッタからビー玉が着地した距離を表すことになる。

検量線をプロットしたら、傾きと切片の関数を使って検量線の式を計算し、測定器の読みから「未知」の薬液の濃度を決めたり、ビー玉が発光器からある距離で落ちるようにプログラムに与えるべき入力を決めたりすることになります。

ステップ1：チャートの作成

この単純な例のスプレッドシートは、X値とY値の2つの列で構成されています。

まず、チャートにプロットするデータを選択しましょう。

まず、「X値」列のセルを選択します。

ここでCtrlキーを押しながら、Y値列のセルをクリックします。

のタブに移動します。

Chart」メニューを開き、「Scatter」ドロップダウンリストの最初のオプションを選択します。

データポイントが2列で表示されるチャートが表示されます。

青い点の一つをクリックしてシーケンスを選択します。選択されると、Excelはその点の輪郭を描きます。

ポイントの一つを右クリックし、「トレンドラインの追加」オプションを選択します。

チャート上に直線が表示されます。

画面右側に「トレンドラインの書式設定」メニューが表示されます。Show equation on chart' と 'Show R-squared value on chart' の隣にあるチェックボックスにチェックを入れます。最高のR2乗値は1.000であり、これはすべてのデータ点が直線に接していることを意味する。データ点と直線の差が大きくなると、r2乗の値は小さくなり、0.000が最低値となる。

トレンドラインの方程式とR2乗の統計量がグラフに表示されます。この例では、R2乗の値が0.988と、非常に良い相関があることに注意してください。

方程式は「Y = Mx + B」の形で、Mは直線の傾き、BはY軸の切片である。

キャリブレーションが完了したので、タイトルを編集したり、軸のタイトルを追加したりして、チャートをカスタマイズしてみましょう。

グラフのタイトルを変更するには、タイトルをクリックしてテキストを選択します。

ここで、グラフを説明する新しいタイトルを入力します。

X軸とY軸にタイトルを追加するには、まず[Chart Tools] > [Design] に移動します。

チャート要素の追加" ドロップダウンリストをクリックします。

ここで、「Axis Titles」>「Primary Horizontal」に移動します。

軸のタイトルが表示されます。

軸のタイトルを変更するには、まずテキストを選択し、新しいタイトルを入力します。

今度は、シャフトの見出し＞メジャーバーティカルに向かいます。

軸のタイトルが表示されます。

テキストを選択して新しいタイトルを入力し、このタイトルの名前を変更します。

これでチャートは完成です。

ステップ2：線形方程式とr2乗統計量を計算する

では、Excelに内蔵されている傾き、切片、相関関数を使って、直線の方程式とR2乗の統計量を計算してみましょう。

ワークシート（14行目）には、これら3つの機能の見出しを追加しました。実際の計算は、これらの見出しの下のセルで行います。

まず、傾きを計算します。セルA15を選択します。

数式」→「その他の関数」→「統計量」→「傾き」に移動します。

Known Y "フィールドで、Y値の列セルを選択または入力する。

Known_xs "フィールドで、X値列のセルを選択または入力します。SLOPE関数では、"Known_ys "と "Known_xs "フィールドの順序が重要である。

最終的にフォーミュラバーの数式は以下のようになります。

= スロープ（C3:C12, B3:B12）

セルA15のSLOPE関数が返す値が、グラフに表示されている値と一致していることに注意してください。

次に、セルB15を選択し、「数式」→「その他の関数」→「統計」→「切片」と移動します。

Known Y "フィールドのY値列のセルを選択または入力します。

Known Diem xs "フィールドのX値列セルを選択または入力します。Known_ys」と「Known_xs」フィールドの順序も、INTERCEPT関数では重要である。

最終的にフォーミュラバーの数式は以下のようになります。

= 切片 (C3:C12, B3:B12)

INTERCEPT関数が返す値は、グラフに示されたy-INTERCEPTと一致していることに注意してください。

次に、セルC15を選択し、「数式」→「その他の関数」→「統計」→「関連」と移動します。

関数パラメータ "ウィンドウがポップアップ表示されます。Array1 "フィールドの2つのセル領域のうち1つを選択または入力する。傾きや切片とは異なり、順序はCORREL関数の結果に影響しない。

Array2 "フィールドの2つのセルエリアのうち、もう一方を選択または入力します。

フォーミュラバーの数式は次のようになります。

= 関連 (B3:B12, C3:C12)

CORREL関数が返す値は、チャート上の「R二乗」の値と一致しないことに注意してください。CORREL関数は「R」を返すので、それを二乗して「R二乗」を計算する必要があるのです。"

関数バーをクリックし、CORREL関数が返す値を二乗する計算式の末尾に「^2」を追加します。これで、完全な数式は次のようになります。

= 相関関係 (B3:B12, C3:C12)^2

Enterキーを押してください。

計算式を変更した結果、「R2乗」の値がグラフに表示されている値と一致するようになりました。

ステップ3：値をすばやく計算する数式を作成する

これらの値を簡単な計算式に当てはめると、「未知」の溶液の濃度や、ビー玉が一定の距離を飛ぶようにするにはどのようなコードを入力すればよいかがわかります。

このステップでは、X値またはY値を入力し、検量線に従って対応する値を得られるように、必要な数式を設定します。

ベストフィット直線の方程式は「Y値＝傾き＊X値＋切片」の形なので、「Y値」は「X値」に「傾き」を掛けて「切片」を足せば解けます。

例えば，X値を0とすると，Y値はベストフィット直線の切片と等しくなるはずである．

Y値から切片を引き、その結果を傾きで割ることで、Y値からX値を求める。

例えば、切片をY値として使用します。返されるX値はゼロになるはずですが、返される値は3.14934E-06です。値を入力する際に誤ってインターセプト結果を切り捨ててしまったため、返される値はゼロではありません。しかし、この式の結果は0.00000314934であり、実質的にゼロであるため、この式は正しい。

最初の太枠のセルに任意のX値を入力すると、Excelが自動的に対応するY値を計算します。

2つ目の太枠のセルに任意のY値を入力すると、対応するX値が表示される。この式は、溶液の濃度を計算したり、ビー玉をある距離打ち上げるのに必要な投入量を計算したりするのに使うことができる。

この場合、測定器は "5 "と表示するので、キャリブレーションでは濃度を4.94とします。つまり、ビー玉を5ユニット移動させたいので、キャリブレーションではビー玉発射装置制御プログラムの入力変数として4.94と入力します。この例ではR2乗の値が高いので、この結果にはかなりの信頼性があると思われる。