基本に立ち返る：エクセル編・・・「参照」という話

天気予報通り、しんしんと雪が降り続き、どうやらまた明日も降り積もった雪が
ここらあたり一面、家並みも含めて真っ白に覆いそうです。・・・風情はKこちゃんやないけど
「美しいもん」があり、心洗われるものがあるにはあるけど・・・
雪かきに「くったくた」で・・・痛めてる膝がますます痛うなるのが現実です。

こういう時は、久しぶりにパソコン開いて、思い出し思い出し
エクセルの基本について、おさらいするのもええかな・・・やっぱりやめとこか・・・どっちかです ・・
時折人の作ったエクセルのブックやシートを目にすることがありますが、
そういうときに自分なりに、その人がエクセルに慣れているかいないかの、
一つの目安にしていることがあります。
それは「相対参照」、「絶対参照」それらが混じった「複合参照」を理解しているかいないかという点です。

※エクセルの特徴として個別のセルに入力された数値を使って、「表計算」がかんたんにできて、
集計や計算がちょっとした「数式」を入れることでその答えが得られます。
たとえばA１というセルに入った数値とB1に入った数値の足し算の答えをC1というセルに求めるときは
C1というセルに数式の「＝A1＋B1」とすればいいのです。しかも数式の入力をする際には「＝（イコール）」
としたあとは、いちいちセル番地を入力せずとも、該当するセルをクリックすると自動的に
数式にセルの番地をエントリーできる仕組みです。・・・こうしたセル番地を指定することを
エクセルでは、「参照」と呼んでいます・・・・少々堅苦しい言葉ですが・・・
例を使ってさらにエクセルの基本をおさらいします。・・・・次のような表を作ったとします。

上の図の「売上」は、B列の単価×C列の数量で求められます。
従って、D2というセルに実際の数式を当てはめますと
D2には、図のように「＝B2＊C2」という数式を入力します。

D2に数式が入ったら、D2のセルをクリックして、上図のように該当する範囲までドラッグすると
その数式が自動的にコピーできます。

ここんとこまでは、多くの人たちがそういうもんやと特に意識せずともよく使っておられるとは思いますが
実は大変よくできた仕組みで、それが証拠に、D2の数式「＝B2＊C2」
をコピーしたD4というセルを試しにクリックして、
上図の上にあるように「fx」という数式バーをのぞいてみると
ちゃんと「4行目」にあうように「＝B4＊C4」に変化する形でコピーされていることがわかります。
こういう風に相対的に状況に合わして、セルの番地を変化させることを「相対参照」と呼んでいます。
ここまでは、確かに言葉使いは小難しいけども何となくわかったような気がしてるとは思います。

次の段階で、表の下（D8）に売上の合計を作ってみましょう。
合計はこの時期にお似合いの？SUM（サム）という関数を使います。・・・この関数は
多くの初心者に大変なじみのある関数で、たいていの場合ツールバーの「Σ」というボタンをクリックして
使われる方が多いと思います。

合計の欄D8をクリックして、選択したら「Σ」（サムボタン）を押すと、
上図のように集計の範囲を聞いてきますので
範囲が合っていれば、そのまま「Enter]を押すか、あるいは始点のセルと終点のセルをクリックして
集計範囲を設定して、「Enter]を押すと集計が完成します。

次はいよいよE欄の「比率」というところですが、これは全体の売上に対して個別の品物の売り上げが
何パーセントかという値を求めることになりますので
「個別の売上金額」÷「全体の売上合計」ということで答えが得られます。
そこで、まず下準備として、比率欄は、「％」で表示したいので、
該当のセル範囲をしておき、メニューの「書式」→「セル」と進み
下図のようなダイヤログが現れたたら、「表示形式」というタブで「パーセンテージ」を選び
ついでに、パーセントで表す小数点以下の桁数を、右の欄で「少数以下１桁」までしておきましょう。

で、いよいよ比率を求める「数式」をE欄に設定するのですが
下図のE2というセルに注目して、みかんの売上「D2」を合計欄「D8」で割りますと
図のように「＝D2/D8」ということになります。・・・確かに間違っていませんし、ちゃんと答えは得られます。

が、だからといってこのままさっきと同じようにE2のセルをクリックしてE7までドラッグすると
どうなるのでしょう・・・たぶん

上の図のような「エラー表示」が現れるでしょう。・・・♯DIV/0とは、
何かの値を「0(ゼロ）」で割ったという意味のエラーです・・ →「エラーコードの種類」
試しにE3をクリックして、前述のようにE3というセルの数式バーを確認すると
相対参照の仕組みから「＝D3/D9」という数式に自動的に変化しています・・それぞれのセル番地に
＋１ずつの数値が振り当てられることが確認できます。・・・これでは、実際にはD9というセルには
数値が存在しないので、「エラー」が表示されるという理屈です。

では、これを解消するのにはどうしたらいいのでしょうか・・・・下図のように
 
それぞれのE列の数式の分母をいちいちD8に変えて修正するのでしょうか・・・それでは、ずいぶん手間と
時間がかかります。効率が悪い上に、コピーができないどころか
次に使い回しのできる方法だとも思えません。
・・・実はここからが、ある程度わかってる人との違いだと感じています。
そこで、今度は下図のE2のセルの数式に注目します。
D8と指定したセルのそれぞれのアタマに「＄」（ドルマーク）がついているのがわかります。
「$D$8」となっています。こうすることによって、それぞれを固定して「絶対位置」とすることを示しています。
列の前に＄をつけて列を固定し、行を示す数字の前にも＄をつければ、行を固定します。
こうすることによって、それぞれの売上高を割る全体の売上高（$D$8)を固定する式が完成するのです。
これなら、違うセルにドラッグしても、割る方の分母が固定されたままですので
ちゃんと数式を他のセルにコピーすることができますし、一つの操作で「表」が完成します。
 
こういう具合に参照するセルを固定することが、「絶対参照」なのです。
実際の操作では、セル位置を絶対参照にするのには、ご丁寧にいちいち
セル位置をクリックしてから、それぞれのアタマに「＄」マークを追加する必要はありません。
図の場合は、数式を作る際に、該当する部分のD8をクリックしたら、キーボードの「F4」キーを押しますと
「$D$8」としてくれます。・・・もののついでに、続けてF4を押すと「D$8」という風に行だけを固定した形になり
さらにF4を押すと今度は「$D8」と言うように列だけを固定した指定になります。
こういう風に行・列のどちらかを固定して、「参照」することを「複合参照」と呼んでいるのです。

こういう使い分けができているシートを見かけると、ちょっと使い慣れてるなって感じるのです。
相対参照と絶対参照・・・言葉は小難しいのですが、ここを理解すると
エクセルの初心者からの抜け穴が見えてくる気がします。