先週は式の入力というもっとも初歩的な内容について、あえておさらいをした。今週はそれに引き続き、式のコピーについて振り返りたい。
相対参照と絶対参照
これまでの８回とくらべ、突然難解な用語が出てきたとおどろかれる読者もいるかもしれない。しかし、日常業務においては、ごく自然に使っているはずである。
以前、データ入力の中で「オートフィル」を説明した。連続したセルに規則性をもったデータを入力し、そのデータ全体のセルを範囲指定してマウスでドラッグすると、規則性をキープしたまま、データが入力されるという、あれである。
相対参照
実はこのオートフィル機能、式のコピーについても同じように装備されている。下図のように商品Aの単価と数量の積、総額を求めるよう、セルE3には「=C3*D3」と入力されている。
これを商品B、商品Cについても同じように総額を求めるには、商品Aについての総額と同じ計算式をそれぞれセルE4、セルE5にも入力すればいい。そこでわざわざ入力せずともポインタをセルE3の右下に合わせドラッグハンドルにし、セルE5までドラッグすることで式がコピーされ、式に使用するセルも同じように下方向に一つずつズレるのである。
この式コピーのオートフィル機能は、名前など知らなくとも自然と使っているはずである。この式をコピーした際に式に組み込まれているセルの番地を、コピー元とコピー先の差と同じだけズラす機能を「相対参照」と呼ぶ。
要するに「式が入力されているセル」と「計算式に組み込まれ『データの参照先』のセル」の位置関係を、式が入力されているセルを基準として相対的に保持しているのである。
この機能はExcelにはなくてはならない機能で、これがなかった場合、式をコピーしてもすべての式について、その参照先のセルの番地を修正しなければならず、表計算アプリとしてはとても使いものにならない。
絶対参照
たいていの作表計算では相対参照で事足りるが、Excelの使い方がわかってくると、これだけではうまくいかないケースに出くわすことになる。
たとえば、下の表は各商品ごとに割引率を設定して、割引後価格を計算している表であるが、この割引率を全商品一定にし、計算された割引後価格を見ながら、割引率を決めたい、というニーズがあったとしよう。
簡単に思いつくのは、セルF3からセルF5に同じ割引率を入力するか、セルF4とセルF5に一つ上のセルを参照する計算式を入力するか、もしくはいちいちコピーするか、方法はいくつかある。
しかし、恒常的にこのような作業を行い、かつ値を変えたいセルが多数あるような場合、セルの「絶対参照」を式に組み込むことにより、簡単に式をコピーして使えるのである。
セルを絶対参照にするには、いつもどおり式を入力する際の、参照先のセルをクリックして、式にセルの番地が入ったところで、[F4]キーを押す。そうするとたとえば「E9」というセルの番地が「$E$9」というように番地のアルファベットと数字の前に「$」マークが付く。
この「$」マークがついたセルの番地は式をコピーしてもオートフィルの対象から外れてそのままの番地をキープするのである。これで同じ参照先の値を複数のセルに１回の入力で反映できるのである。
要するに計算式に組み込まれている参照先のセル番地を「E列の9行目」というシート上の絶対値として保持しているのだ。
ちなにみこの「$」マークは、番地のアルファベットと数字の両方だけでなく、「$E9」や「E$9」のように片方だけにつけることもでき、「$」マークがついた番地だけが絶対参照になる。
そうすると式のコピーでオートフィルが機能しても「$」マークがついた番地だけがそのままズレないでコピーされるのである。
「$」マークのつけ方は、先ほどと同じように式入力の際、参照先のセルをクリックしたときに[F4]キーを押すのだが、式入力モードで押し直すと以下のように「$」マークの付き方が変わってくる。
「E9」→「$E$9」→「E$9」→「$E9」→「E9」→以降くりかえし
単純な作表計算ではあまり活用の機会はないが、複雑な式を入力して定型業務に活用したり、システムからCSVで吐き出したデータを加工して、シミュレーションするときになどには必須のテクである。人事屋さんを目指すのであればぜひ、基礎から身につけていただきたい。
