Tips: どうやってコピー時に Excelは計算式の中身を調整しているのか？

Excel計算式コピーの不思議

Excelで計算式をコピーすると、計算式の中で参照しているセルが自動的に変化して、正しい参照関係になっている。これはちょっと不思議。この性質があるため、同じ計算式を何千個も入力することなく、コピー一発で済むわけだがその中身はどうなっているのかちょっと調べてみよう。

R1C1参照形式で考える

Excelでのセル番地は、通常 AE86 とか FD4 とかいった形式で「列番号（アルファベット）」＋「行番号（数値）」で表現されている。しかし、Excelには実はもう一つのセル番地表現形式があって、 R1C1参照形式 と呼ばれている。この形式を使いたい場合は、オフィスボタンから[Excelのオプション]-[数式]を選んで「R1C1参照形式を利用する」にチェックを入れる必要がある。R1C1参照形式では、行も列も全て番号で表すので、混乱しないようにR（Row: 行）とC（Column: 列）というアルファベットを数字につけて、R2C3 といった形でセル番地として使う。ちなみに、R2C3は列が 3列目、行が2行目なので普通のセル番地で書くと C2　である。行と列の順番が逆になるので注意。

さて、このことを理解した上で計算式が入っているセルの内容を R1C1参照形式で表示してみよう。たとえば、通常のセル番地で以下の例を見ると、セル E6には =C6*D6 という計算式、すなわち「セル C6 の値とセルD6 の値を掛けたものを セル E6の値としろ」と書いてある。

では、この同じワークシートの同じセルを、R1C1参照形式で見てみるとどうなるだろうか？　素直に考えれば、C6は R6C3, D6は R6C4であるから =R6C3*R6C4 と書いてあるだろう。論より証拠、上の図と全く同じワークシートを R1C1参照形式で表示してみると下図のようになる。

予想では =R6C3*R6C4 だったのだが、実際には =RC[-2]*RC[-1] と全然違うことが書いてある。ついでなので、ほぼ同じ内容が入っているはずのセル E5（1行上のセル）についても両方の表示形式で示してみよう。

面白いことに、R1C1参照形式ではセル E5 にも　セル E6　にも（というか、R5C5 にも R6C5 にもと書くべきなのかもしれない）全く同じ

=RC[-2]*RC[-1]

という計算式が入っている。この計算式の中に入っている[-2]とか[-1]という表現は、「この計算式が入っているセルを基点として右（下）がプラス、左（上）がマイナスにとって、何列（行）分離れたセルを参照している」ということを表している。上下左右の差が0の時には特に R[0]とかC[0]とか書かないで[0]は省略するから、=RC[-2]*RC[-1]という計算式は、=R[0]C[-2]*R[0]C[-1]と同じである。

R[0]C[-2]は R(行方向）は計算式が入っているセルと同じ行、C（列方向）は計算式が入っているセルからマイナス2（つまり左に2つ）離れたセルを意味し、R[0]C[-1] はR(行方向）は計算式が入っているセルと同じ行、C（列方向）は計算式が入っているセルからマイナス1（つまり左に1つ）離れたセルを意味している。要するに、「この計算式が入っているセルと同じ行で、左に2つ行ったセルの内容と左に1つ行ったセルの内容を乗じたものをこの計算式が入っているセルの値として計算しなさい」ということになる。

R1C1参照形式で考えれば、計算式をコピーしたときに正しく計算できるのは不思議でもなんでもない。ここでの例で分かるように、R1C1参照形式で見れば計算式自体は全く変わっていないのだから、文字列のコピーと同じことである。

種明かしをすれば、Excelに計算式を入力した際、普通に入力すれば計算式の中で参照されているセル番地の内部的な表現は全て上に示した[ ] つきのR1C1参照形式と同じように「計算式が入っているセルからの番地の差」となっている。これを Excelの用語では「相対参照」と呼ぶ。A1形式で表示した際に、実際に参照しているセル番地として表示されるのは人間向けのサービスに過ぎない。計算式をコピーしたときには、計算の内容を Excelが理解して書き換えてくれているわけでもなんでもないので、R1C1参照形式で見ても中身が変わってしまうようなコピーを行うともちろんうまくいかないことに注意。

相対参照と絶対参照

相対という言葉の意味は、「計算式が入っているセルを基点として、上下左右どれくらいズレたところを見ているか」ということだった。それでは「絶対参照」というのは何をするものだろう。

絶対参照は、ワークシートの全体の座標系のなかで、セルを一意に指定するものである。いままで、A1 っていうセル番地は ワークシートの左上のセルをあらわしているんですよ。などと簡単に言ってきたが、計算式の中で A1 と単純に書いた場合には上述のとおり相対参照になってしまう。相対参照ということは、計算式がどのセルに書き込まれているかで実際に使うセルが違ってしまうわけで、どこのセルに書いても参照するセルを一意に指定することはできない。

これがどういうときに困ったことを引き起こすかを、以前つかった美術館での買い物リストで見てみよう。例題で扱った表は、円換算額を合計額についてのみ計算していたが、各品物について円換算額を計算するように表を作り変えてみる。

まず、最初の品物について円での金額を計算するには、セル E6 で計算している米ドルでの金額に 、セル D3 で与えてある為替レートを乗じてやればよい。素直に書けば、セル F6 に =E6*D3 と書き込めば OKなはず。数値の表示形式を円に直すとこんなふうになる。

13.95×116=1618.2 なので小数点以下を表示しなければあっているようだ。それでは、残りの2品については同じ計算式を入力するのが面倒なので、前にやったように計算式をコピーしてみよう。

結果が明らかに変だ。70×116が0のはずはないし、135×116が #VALUE! などという変な文字列になるのもおかしい。とりあえずセル F7　とセル F8 に入っている計算式を確認しておこう。

なにやら変なところを参照している。これは、セル F6 に入れた計算式が相対参照を使っていたから。また R1C1 参照形式でチェックすると、RC[-1]*R[-3]C[-2] という計算式が入っている。これは、R[0]C[-1] * R[-3]C[-2] と同じ意味であるから、同じ行（R[0]）で 1列左（C[-1]）のセルの内容と、3行上（R[-3]）で2行左（C[-2]）を乗じた結果を求めろと書いてあることになる。

これでは、計算式が入っているセルが 1行下になったときに、R[0]C[-1]　は正しく次の行の金額（ドル表示）を参照するが、 R[-3]C[-2] も1行下のセルを参照してしまう。結果的になにも入っていないセルを参照して計算結果が0になり、2行下になったときには　数量　という文字列が入ったセルを参照していたため掛け算に失敗して #VALUE! というエラーになってしまうのも無理はない。

これを解決するためには、為替レートが入っているセルを相対参照ではなく、ワークシートの中で一意に（つまり絶対参照で）指定すればよい。R1C1参照形式では、[ ] をつけずに番号を指定すれば絶対参照になるので、為替レートが入っているセルは R1C1参照形式の絶対参照では R3C4 となる。つまり計算式としては =RC[-1]*R3C4 と書けばいいはずである。実際に、このように入力して、計算式をコピーすると以下のようになる。

確かに正しく計算されているようである。念のため計算式もチェックしておくと

確かに、適切にコピーされていることがわかる。

さて、R1C1参照形式ではどうやって絶対参照を使うかわかったが、普段使っている A1形式のセル番地では絶対参照はどう記述するのだろうか？　上のワークシートの表示をA1形式にすると、こんなかんじ。

セル F8　に入っている計算式は

=E8*$D$3

となっている。この $ が A1形式での絶対参照を表すマークになる。絶対参照と相対参照の利用例については、第7課で詳しく学ぶ。

このドキュメントでは、ワークシートの中で一意に定まるセル番地をあらわす際には枠線つきのセル番地表記（例: A5 ）を、計算式の中での相対参照を表す際には枠線なしのセル番地表記（例 A5 ）と使い分けている。当然、計算式の中であってもワークシートの中で一意に定まるセル番地を参照している場合（絶対参照）については枠線つきのセル番地表記（例: $A$5 ）を用いている（*1）

（*1）筆者が枠線をつけ忘れていて、いろいろな表記が混在しているわけではない