（対面）表計算ソフトの活用（１）

主な到達目標
Excelによる実践的な実験データ処理を行えるようになる
（仮想の吸光度測定データを処理し結合定数（＝解離定数^-1）や吸光係数を求める）

　水溶液中では無色（＝光の吸収無し）の物質AとBが結合することにより有色物質ABとなる。　これら物質AとBの混合水溶液中での結合定数をk とする。

　結合定数の定義から等式が一つ決まりる。さらに、混合溶液を作る時の物質AとBそれぞれの調整濃度C_AとC_Bは遊離の物質濃度[A]、[B]と結合体濃度[AB]の総和であることから等式が二つ決まる。

このとき、結合定数k は物質間特有の値なので定数、C_AとC_Bも混合溶液を調整するときに決められる定数である。一方、[A]、[B]および[AB]は与えられた定数C_A、C_B およびk によって混合溶液中で定まってくる未知数である。

　未知数が三つに対し式が三つ用意できたのでこの連立方程式をまず[A]について解くと、 [A]についての二次方程式が得られます。

　この二次方程式から得られる二つの解のうち、
k ＞0、C_A≧0より
a ＞0、c ≦0となる結果
(b ²-4ac )^(1/2)≧|b |という条件より
値の小さい解では分子が-b - (b ²-4ac )^(1/2)≦0常に負
値の大きい解では分子が-b + (b ²-4ac )^(1/2)≧0常に正
どちらの解でも分母が2a＞0常に正なので常に正の値をとる解を選びます。

　[A]が求まれば元の方程式に順次代入していくことにより[B]および[AB]が求まります。

Excelによる結合モデル作成
まず、上記の結合モデルで定数の任意の値に対して未知数が決まる様子を体験します。

このリンクからExcelファイルをダウンロードして開きます。

結合定数k = 0.5 mM^-1、物質ＡとＢの調製濃度がC_A= 10 mM、 C_B= 10 mMの場合の混合溶液中の各物質濃度を求めます。
上記３つの数値としてI1セル(k)に0.5、I2セル(C_A)に10、I3セル(C_B)に10と直接入力。

２次方程式の定数を決めるためにI4には数式“=I1”、I5には数式“=I1*(I3-I2)+1”、I6には数式“=-I2”を入力。

[A]を求めるためにI7に２次方程式の解の数式“=(SQRT(I5^2-4*I4*I6)-I5)/I4/2”、
[B]を求めるためにI8に数式“=I3/(1+I1*I7)”、[AB]を求めるためにI9に数式“=I1*I7*I8”を入力します。

ここで、SQRT(x )はx の平方根を返す関数で、 a^bはa^b を計算する演算子です。
k = 0.5 mM^-1、C_A= 10 mM、C_B= 10 mMのときの [A]、[B]および[AB]の濃度がI7、I8、I9セルにそれぞれ求まります。

結合モデルの動作確認
[A]、[B]、[AB]の濃度を棒グラフ化し量的関係を捉えやすくします。
セル範囲(H7:I9)を選択し、末端に付いてくる「クイック分析」から、

[グラフ]→[集合縦棒]を選択します。

k = 0.5 mM^-1、C_A= 10 mM、C_B= 10 mMのときの [A]、[B]および[AB]の濃度がグラフ化できました。

３つの定数k 、C_A、C_Bの値をいろいろ変化させてみて、それに伴い[A]、[B]、[AB]の量的関係が変化する様子を確認します。

C_Bの変化に伴う[A]、[B]、[AB]の変化

３つの定数k 、C_A、C_Bの値が与えられると、 [A]、[B]、[AB]が決まるワークシートが出来たので、
k 、C_Aの値は固定、C_Bの値が系統的に変化する混合溶液の物質濃度を求められるように拡張します。

棒グラフは削除し、計算式再利用のためセル範囲G1:I9をG11:I19へコピーします。

k = 0.5 mM^-1、C_A= 5 mM、C_B= 0を初濃度とします。
C_B= 0（I13セル）を「お手本」とし、S列までオートフィル。「オートフィル　オプション」から『連続データ』を選択します。

３つの定数k 、C_A、C_Bの値が準備できたので、
C_B= 0のときの計算を行っているセル範囲（I14:I19）を「お手本」にしてC_B= 10（S列）までオートフィルしてみます。

エラーだらけになります。

計算がうまく列（I列）とうまくいかない列（J列）を見比べてみると「オートフィル」の相対参照の影響で、
動いてはいけないセル参照が動いてしまっていることが分かります。

下図のように修正すればよいのですが、オートフィルした全てのセルに対してこのような確認と修正をしたのでは、
何のための便利機能かわかりません。

そこで、オートフィルされた後のセルを修正するのではなく、オートフィルの「お手本」をオートフィルしても大丈夫なように書き換えます
具体的にはお手本のセル範囲（I14:I19）からのセル参照のうち動いてはいけないI11とI12を「絶対参照」に変更します。
ます、I14セルを修正のためダブルクリックで編集モードにし、「相対参照」状態の”I11”の３文字の間に入力カーソル「｜」を点滅させ、
キーボードの[F4]キーを押すと、”I11”→”$I$11”と絶対参照の式になり、オートフィルしてもI11セルを参照し続けるようになります。

修正が済んだら[Enter]キーを押してI14セルの内容を確定し、次はI15セルをダブルクリックで確認します。I11とI12を絶対参照にします。

修正が済んだら[Enter]キーを押してI15セルを確定し、次はI16セルをダブルクリックで確認します。I12を絶対参照にします。

修正が済んだら[Enter]でI16セルを確定し、次はI17セルを確認します。I11とI12は参照されておらず、修正の必要無し。

そのまま[Enter]でI1７セルを確定し、次はI18セルを確認します。I11を絶対参照にします。

修正が済んだら[Enter]でI18セルを確定し、次はI19セルを確認します。I11を絶対参照にします。

修正が済んだら[Enter]でI19セルを確定し。セル範囲（I14:I19）を「お手本」にしてS列までオートフィルしてみます。

エラー無し。

C_Bの変化に対して[A]、[B]、[AB]の変化をグラフ化します。
まず、横軸のデータのセル範囲（H13:S13）を選択します。

次に、[Ctrl]キーをおしながら縦軸のデータ系列のセル範囲（H17:S19）を選択します。（複数セル範囲選択）

複数セル範囲を選択すると「データ分析」が出てこないので、[挿入]→[グラフ]→[散布図]→[散布図（平滑線）]を選択。

k 、C_A、とくにk の値を変化させて、グラフの形がどのような変化を受けるかを確認します。

C_Bの変化に伴う吸光度の変化の計算値

吸光度は光吸収物質の濃度に比例する測定値で、物質固有の比例係数がモル吸光係数です。

ワークシートに結合体ABのモル吸光係数ε_ABを用意し、C_Bの変化に伴う吸光度の変化を扱えるようにします。
グラフを削除し、F列が先頭に表示されるようにスクロールし、必要項目を入力します。
H20セルは”イプシロン”を変換で全角”ε”に続き半角英数”AB”を入力ご”AB”のみ文字選択し「フォントの設定」ダイアログを開き、

[下付き]をチェックし[OK]。

J20セルのように特殊文字”/”が先頭に来る場合、更にその前に[Shift]+[や]で”'”（アポストロフィ）を付けることで入力。

仮に結合体ABのモル吸光係数がε_AB=123(/cm/M)とすると、
測定されるであろう吸光度の計算値はAbs./cm=ε_AB×[AB]/1000となりますが、

後のオートフィルに備えてI20セルを絶対参照にしてから[Enter]で確定します。

表示列の先頭をH列に戻し、先程入力したC_B= 0のときの吸光度I21セルをお手本にS列までオートフィルします。

吸光度の変化をグラフ化するため、横軸セル範囲（H13:S13）と縦軸系列セル範囲（H21:S21）を選択します。

[挿入]→[グラフ]→[散布図]→[散布図（平滑線）]を選択。

k 、C_A、ε_ABの値を変化させて、グラフの形がどのような変化を受けるかを確認します。

吸光度の変化の測定値と計算値との比較

吸光度の計算値と比較するために測定値を入力する行を用意します。
表示列の先頭をF列にして、セル範囲（G21:H21）を下方向に１行オートフィルでコピーし、H22の先頭に”o”（observedの意味）を入力。

表示列の先頭をH列にして、各C_Bに対して↓に示すような測定値を22行目に入力します。

グラフをクリックして選択状態し、グラフの系列データの青いセル範囲の左下隅のフィルハンドルに似たつまみをドラッグし、

下方向に１行広げると、22行目の測定値データがグラフに２つめの系列として赤い平滑線で追加されます。

追加された赤い平滑線上でクリックすると測定値データの系列のみが選択されるので、
[グラフツール]→[デザイン]→[グラフの種類の変更]を選択。

「グラフの種類の変更」ダイアログが出たら、系列名”oAbs./cm”のグラフの種類を”散布図（平滑線）”から”散布図”に変更し[OK]。

連続的な計算値データを平滑線、離散的な測定値データを点で表示できました。

グラフの書式を整えます。まず、グラフ全体が選択された状態で、フォントサイズを12 ptにします。

グラフ要素で[軸]、[軸ラベル]、[目盛線]をチェック状態にし、

軸ラベルを編集します。

配置をセル範囲（I26:O45）上に。

吸光度の測定値と計算値を比較できるようになりましたが、グラフ上では両者に大きなずれがあります。
これは計算値を出す際に用いているkとε_ABが仮に定めた値であるためです。
逆に言えばグラフ上で両者がピタリと重なるkとε_ABの値を見つけられれば、
実験によってkとε_ABを決めることができたことになります。

kとε_ABの値を書き換えてグラフの２系列がなるべく重なるように試みます。
最初は大きく値を変えてグラフの計算値系列の動きの傾向を探り、徐々に値の変化量を細かくしていきます。

見た目では系列が重なったように見えてきたとき、実際どの程度重なっているか客観的な指標が欲しいところです。
そこで、各測定濃度（C_B）での、測定値と計算値の”差”を23行目で算出してみます。

各測定濃度（C_B）での”ずれ”は見ることが出来ましたが、系列全体での”ずれ”を総合的に判断できません。
では、測定値と計算値の”差”を全て足して、その総和が0に近ければいいかといえば、そうはいきません。
なぜなら、差は±があるため、実際は大きくずれているにもかかわらず、+に外れている量と-に外れている量が同じくらいだと、
総和が0に近くなってしまうためです。
そこで、各C_Bでの差の２乗の値をはずれ具合の量として用います。
この値なら、差が+であっても-であっても外れていれば外れているほど正の値で大きくなります。