Excel 絶対・相対参照の上級編～R1C1参照～

はじめに―絶対・相対参照の更に先へ―

Excelの超基本である絶対参照・相対参照というのは大雑把に言うと、セルの位置を示す「A1」とかの表記に「$」マークを付けたり外したりして、セルの参照を固定したりしなかったりすることだ。

それは別の記事で説明しているけど、通常はこの記事に書いた程度の理解をして「$」マークの使いこなしを覚えれば問題ない。

Excel 絶対参照・相対参照 SUMIF関数を題材にして語る

2022年4月9日

しかし絶対・相対参照には、その先が、というか別のアプローチがある。

マクロ・VBAとかまでやる人でないと、そうそう目にするものではないので、ちょっと上級編的なトピックになるのだが、実用上でガチで役に立つ場面も多いし、ぜひとも理解にチャレンジしていただきたい。

例題

上図は、「前期」「当期」「差額」という項目があちこちに不規則にバラ撒かれた例の図だ。

「差額」という言葉がちょっと紛らわしくて、C3セルの差額欄には「=A3-B3」と「前期-当期」を示す計算式が入っている
→正しくは差額というのは「当期-前期」を示すので、「=B3-A3」というように式を修正しないといけないのだ。

そして、「差額」の意味を間違ったまま式を入力したのは、G3、C9、D13、・・・と、緑色にした無数のセル全部に及んでおり、それらをまとめて修正しないといけない。

セルの配置が不規則でバラバラなので、オートフィルタとかもできず、これの解決には検索・置換をするしかない。

しかし検索・置換については別の記事で説明しているが、その知識とかだけを使っても解決はできない。

Excelの検索と置換の機能について

2020年3月22日

たとえばC3セルには「=A3-B3」、G9セルには「=E9-F9」、H13セルには「=J13-K13」、・・・と式が入っていて、検索しようとしても検索ワードに設定する共通語句が見当たらないのは明らかだ。

実際の仕事とかでもこれに近い、あちこちのセルの式をまとめて修正しないといけない場面はあるはずだ。さあどうするか。

最初に言っておくが、この問題の答えは最後に書く。
はっきり言って、考え方を説明することがそのまま答えを説明することになるし、この例題に対する答え自体はどうでも良いことだ。

計算式の同一性について

さて上記の問題では、C3セルには「=A3-B3」、G9セルには「=E9-F9」、H13セルには「=J13-K13」、・・・といった計算式が入れられている。

まず、これらの計算式は＜同じ式＞なのだということは、すぐ分かるようになっていただきたい。
もちろん、同じ式だということは、コピペしていって良い式だということだ。

「前期-当期」を計算している式だから同じ式だなんてことを言っているのではない。

「自分のセルから見て2個左のセルから1個左のセルを引き算している」から同じ式だということなのだ。

自分のセルから見たときに、どの方向に幾つ離れた位置にあるセルを扱っているのかという、相対位置の問題だ。

さて、この相対位置ということなのだが、「=A3-B3」と「=J13-K13」が同じ式なのだということは、「自分のセルがどこなのか」という前提事項を示されなければ分からないことだ。

それがこの、一般的な表記方法のデメリットだ。

そのデメリットを克服する、
R[-2]C
といった表記方法がある。
別の記事で簡単な説明にとどめているが、これをR1C1表示とかいうのだけど、マクロ・VBAをやるなら、ほぼ必須の知識だ。

このR1C1表示を使うには特殊な手順・オプション設定をしなければならず面倒だけど、それについては当該記事にまとめてある。

Excel 行・列(縦横)の番号を数字で表示するマクロ 必ず個人用マクロブックに組み込もう

2020年3月21日

以下、このR1C1表示の詳細を説明していく。

R1C1の表記方法

R1C1というが、RはRow(行)、CはColumn(列)のことだ。RowとColumnは、Excelの関数としてもあるもので重要だから、必ずこれらは押さえないといけない。

そして表記例としては、
R2C5
R[2]C[5]

のように書く。

たとえばR2C5というのは、2行目の5列目ということだ。
これは通常表記では「$E$2」となり、縦・横の表記の順序が逆なのが分かるだろうか。

これが最初の、理解しにくい部分だ。私もたまに混同して間違ってしまう。

そして、なんか[]マークが付いていたりいなかったりする。これはどういうことか。

R1C1 絶対参照の記法

まず[]マークを付けずに書いた数値は、セルの絶対位置を示す。
つまり
R2C5
なら、2行目の5列目という絶対的な位置を示す。

普通の表記方法なら「$E$2」となり、完全な絶対参照のことだ。

R1C1 相対参照の記法

次に[]マークを付けて書いた数値は、セルの相対的位置を示す。
つまり
R[2]C[3]
なら、自分のセルから見て2個下、3個右側ということなのだが、この示し方が良かったり悪かったりだ。

「自分のセルから見て」と書いたが、自分のセルってのがどこなのか分からないと、結局どこのセルというの(いつも見慣れた、F5とかの番地)が分からない。

逆に言うと、自分のセルがどこなのかなんて分からなくても、相対的な位置を冷徹に示したいという場合に便利なのだ。

• Rの次の[]マーク内の数値は、マイナスなら上方向、プラスなら下方向
• Cの次の[]マーク内の数値は、マイナスなら左方向、プラスなら右方向

というのを示す。

まあそういう抽象的なのをゴチャゴチャ説明していても仕方がない。具体的(でもないかもだが)例を下に示す。

例題の解答

これでも読んだだけではピンと来ないだろうけど、必要なことは書いたつもり。
そこで、冒頭の例題に戻る。図も一応、再掲する。

繰り返しになるが、今回問題となる「差額」について、誤って共通に入力されているのは「前期-当期」の式で、言い換えると「自分のセルから見て2個左のセルから1個左のセルを引く」式だ。

これをR1C1表記にすると
=R[-2]C-R[-1]C
となる。
実際そうなるということを、以前の記事に示したオプション設定を実際に行い、確認はしてほしい。

この=R[-2]C-R[-1]Cという式は、この問題のC3セル、G9セル、H13セル、・・・と「差額」を計算するセルならどこを見ても共通になっている。

これをどう置換すれば良いかは、分かるはず。
=R[-2]C-R[-1]Cを
=R[-1]C-R[-2]Cと、全て置換すれば良いのだ。

このR1C1表示を私がしょっちゅう使うかというと、モロ今回の例題のような一括数式置換をしたいときとか、あとはマクロを書く時たまに程度しか使わない。

ただR1C1表示は、自分のセルの場所がどこかというのに囚われない考え方には、とても便利なのだ。

横方向の番号をアルファベットでなくて数値で表示できるという意味でも、R1C1表示は分かりやすい。

ただ、表記方法に分かりにくさがあることは否めないのだろう。
もっとR1C1表示は知られ普及してほしいものだ。