エクセルで数式入力の基本的なセル参照と確認「セルの位置関係を表す相対参照と絶対参照」

エクセルのセルを使った計算

売上金額を集計したり商品一覧表の作成にはじまり、先日のサポートのレジジャーナルの1か月分の集計を行ったりと何かと便利なエクセルは表計算ソフトです。

その特徴は単純に一覧表を作るだけだはなく、文字・数値（金額・人数・個数）といった情報をもとに合計や平均、割合等の計算ができることだと思います。

この表計算ソフトはできることが多すぎて、「難しくてよくわからず関数なんか使えません」といった声をいただくことがありますが、WebDream自身も使っていな機能が山ほどあって、まだまだエクセルの学習中です。

セルに入力された値を使って計算するわけですが、その計算をするための【数式】を入力するときのエクセルのセルの参照の仕方についてまとめておきたいと思います。

先日のジャーナル集計サポートの中で気になった部分と、なかなか理解しづらい部分など少しでもわかっていただければという、サポート後記みたいな意味合いで残しています。

∑を使った計算

エクセルを使い始めると、合計を求めることができる便利なボタンを教えてもらい、自分で使い始めたりする【オートSUM】。確かに便利なのですが、エクセルの特性を知ったうえで使うことが大事です。

WebDreamがサポートする場合は、「数式は手入力をしましょう」などと、ボタンを使わずに説明することが多いのですが、便利なボタンとしてリボンに存在しているので使うこと自体全然OKです。

さて、この図のような【支店別売上表】を作成する状況を考えます。

4支店分の4月から6月の3か月の売り上げ金額が入力してあり、その情報から合計や平均の他、達成率などの値を計算するといったものです。

まずはA支店の合計を求めてみましょう。

確認用データとしてダウンロードできるようにしております。実際に確認されたい場合はダウンロードしてご確認ください。

オートサムボタン使用

A支店の合計を【G4】セルで求めるので、【G4】セルをクリックして、オートサムボタンをクリックすると、合計するセルの範囲を自動的にエクセルが判断して選んでくれます。

この選択範囲大丈夫でしょうか？

計算すべきセル範囲をよく考えていないと、このままEnterキーを押し、合計が求められます。そして支店別の合計を求めるために式のコピーを行ってしまいます。

セルに入力されてる【予算、4月分、5月分、6月分】の金額はすべて数値なので、合計できる値であることは間違いないのですが、【予算】と【売上】の金額は意味合いが違います。

予算は支店がそれぞれ目標として掲げる金額であり、売上は実際に月ごとに売り上げた金額なので、性質の違うものを合計するわけにはいきません。

オートSUMをクリックして、エクセルが自動的に選んだセルの範囲が正しいかについては、使う人自身が判断して、範囲の修正が必要であれば合計したいセルの範囲を選びなおしてEnterキーを押さければなりません。

自動的に選んだセルの範囲とは

オートサムをクリックしたときにエクセルが自動的に選んだセルの範囲【C4：F4】について考えてみましょう。

計算の結果を求めたいG4セルの左側に数値が入力されています。ひとつずつ見ていくとすぐ左は6月の売り上げ、次に5月、4月と続き、さらに予算金額として3,000があり、さらに左側にはA支店という文字列があります。

数値が入力されているセルの範囲を合計しようとエクセルは判断するので、オートサムをクリックしたときに自動的にセルの範囲として【C4：F4】を選んでいます。

1か所合計を求めたら、その数式をコピーというのが普通の流れですが、ここでは合計を求めるセルについて、オートサムを使って支店ごとに計算式を入力してみるとどうなるかを試してみます。
ここでは予算のセルが含まれた数式になっていますが、エクセルの特性を見るために選びなおさずに処理を進めています。

B支店の場合は、合計を求めるセルの左側の数値が入力されたセルの範囲を選びました。

次にC支店の場合も試してみます。今回は左側のセルではなく先ほど求めたA支店とB支店の合計範囲を選択し計算を実行しようとしました。

オートサムは本来合計したかったセルの左側を選ばずに、セルの上側にある合計部分【G4：G5】を選んでしまいました。

合計する数値が左側にあるか、上側にも存在するかによって、オートサムが合計しようと選択するセルの範囲が異なってしまいました。「エクセルが選択する範囲は使う人の考える場所とは限らない」これがエクセルの特性で、自動的に選ばれセルの範囲をしっかりと確認する必要がある理由でもあります。

エクセルがリボンの関数を利用する時にセルの範囲を選んでくれるのは、とても便利ですがエクセル任せにしないことが大事です。

確認をして範囲が間違っていなければそのまま範囲を使うし、範囲が異なれば正しい範囲に修正を行うように気を付けてください。

今回はあえて合計するセル１つずつ選んで、オートサムボタンをクリックしていますが、通常であればA支店の合計を求めて、あとは数式のコピーを行うというのが普通だと思います。

合計する範囲を【D4：F4】と選び直しA支店の合計を求め、その数式をB支店、C支店、D支店に数式のコピーを行ってそれぞれの合計を求めました。

エラーインジケーター

正しく範囲を選択して合計を求めたにも関わらず、合計を求めたセル内の左上に緑の三角形【エラーインジケーター】が表示されています。

計算間違い？

それぞれの合計の計算は間違っていないはずです。4月から6月のセル売上の範囲を選び計算しました。しかしエクセルはエラーインジケータを表示しています。

エラーインジケータ色々な場面で、状況を教えてくれるものです。今回の表示は【計算が間違ってます】と言っているのではなく、計算に使用したセルの範囲に間違いはないでしょうか？と尋ねてきているものです。

A支店の合計を求める際、最初エクセルが選んだ範囲は予算の金額を含めた範囲【C4：F4】でした。しかし合計には予算は含めないので【D4：F4】と範囲を選びなおしました。

エクセルが選んだ範囲との相違

エクセルが選んだ範囲と、選びなおしたセルの範囲が異なるために、エクセルとしては数値が入力されたセルがあるのに選び漏れではないでしょうかと、確認の意味で表示しているものだと思っていただければよいと思います。

エラーインジケータが表示されたセルを選択すると、左側にビックリマークが表示され、ビックリマークにマウスポインタを合わせてみます

【このセルにある数式は、隣接したセル以外の範囲を参照します。】と表示されますが、これは読んでも意味が解りづらい文章です。サポートしていると「なにこれ、全然わかりません・・・」と言われてしいます。

このような時「エクセルが合計しようと思った範囲と人間が指定した範囲にズレがあることをエクセルが言ってます」と、WebDreamは伝えてます。

このエラーインジケーターは非表示にすることもできますが、WebDreamは表示したままをおすすめしています。何かしらの情報を教えてくれているのだからあえて消す必要はないとの思いです。

なぜその表示出るのかを再確認する意味もあり、また選んだセルの範囲が妥当なのかなどのチェックにも役立てることができると考えているからです。

達成率を求める

達成率は予算に対する合計の割合を求めるものなので、計算としては今求めた支店の売り上げ合計を予算金額で除算するということです。

A支店の達成率を求める数式は　【G4/C4】となります。

後は支店毎に計算をするので、計算結果が出たH4セルを選びなおし、フィルハンドルをドラッグすることで数式コピーを行います。

構成比を求める

構成比は支店の合計売上が、支店全体の売り上げ金額に対する割合を求めるもので、4支店分の総合計を求めておかなければなりません。

8行目の合計をそれぞれ求めておきます。まずは予算の合計を求めます。

A支店の構成比はA支店の合計をを支店の総合計で除算するので、数式は【G4/G8】となります。

数式のコピーができない？

A支店の構成比を求めることができたので、フィルハンドルをドラッグし数式をコピーしてみます

今までは数式をコピーして問題がなかったのに、今回は計算の結果が表示されず【 #DIV/0! 】という【へんなもの】が表示されました。

エクセルを使っていて、フィルハンドルで数式をコピーしたときに、今までと違うなにか妙なものがセル内に表示されると【へんなもの】と呼ばれて、サポートしていると「なんかおかしくなりました・・・」と言われます。

エクセルに慣れている人にとっては、このエラー表示を認識していて、「さて、どう処理をしようか」と思うものなのですが、慣れていない人にとっては、ある意味恐怖というか、なんか間違った？エクセルを壊してしまった？などという思いが頭の中をぐるぐる回ってしまうようです。

上図のように、いわゆる【へんなもの】が表示されたら、その表示を見たくないのですぐさま削除するか、元に戻すを押して、それ自体をなかったことにしてしまう。
慣れていない場合このような行動をしてしまうのが一般的でしょう。

【へんなもの】表示されたら不安です、さらに知らないことがあればあるほどその不安は増していきます

ここではそのへんなもの？の正体と、表示された理由、そして解決策につついて説明していきます。

エラーの正体

この【へんなもの】の正体は、【０で除算しています】というエラー表示です。

エクセルの数式の処理において０で除算をしてはいけないというルールを持っています。そのため０で除算が行われた場合にエラーとして表示されるものです。

表示されている【 #DIV/0! 】は、divided by zero の意味で、ゼロで除算を行ったためのエクセルのエラー表示です。

表示された理由

表示された理由を調べるためには、【へんなもの】が表示されたらすぐ消しまうと理由を調べらることができません。

表示されたことには必ず理由があります。エクセルを使っているとこの【ゼロ除算】以外のエラーも表示されることがあります。

エラーが表示されたときは即削除してしまうのではなく、理由を調べてから削除・修正・訂正を行う習慣づけることがエクセルを使う上で必要な姿勢です。

A支店の計算では問題なく計算できていますが、数式をコピーしたB支店、C支店、D支店でエラーが表示されてしまいました。

数式を入力したセルをダブルクリックして、計算式に使用したセルがどこを参照しているのか確認してみましょう。ダブルクリックでなくセルをクリックし、F2キーを押すことでも数式の確認ができます。

A支店の構成比を計算する【I4】のセルは【 G4/G8 】で、1,550割る13,800となっています。

B支店の構成比を計算する【I5】のセルは【 G5/G9 】で、5,6000割る数値が入力されていないG9のセルになっています。

C支店の構成比を計算する【I6】のセルは【 G6/G10 】で、1,550割る数値が入力されていないG10のセルになっています。

D支店の構成比を計算する【I7】のセルは【 G7/G11 】で、5,100割る数値が入力されていないG11のセルになっています。

割られる数は問題ないのですが、割る数（除数）のセルがB支店、C支店、D支店では支店売り上げの総合計のセルG8セルと異なっていることが原因で、計算する際にそのセルに値が入力されていなければ、その値は【０】として計算されてしまうからです。

原因は数式のセルのズレということが判明し、修正していかなければなりません。
でも、計算に使用されているセルの位置が思った部分とは違う？と感じた方が多いと思います。修正も大事ですが、今回なぜ数式のコピーでうまくいかなかったのかを先に確認します

正しく計算された、A支店の【 G4/G8 】ですがセルの番地G4セルやG8と言うのではなく、位置関係の表し方を変えてみましょう。

計算の結果を表示する【I4】のセルを基準に考え、その基準のセルの位置から、左・右・上・下にといった「上下左右方向」にどれだけ位置が移動しているかで表してみます。

A支店の構成比の計算に使用したG4セルは、I4セルから左方向に2つ進んだセルとなります。
G8セルはI4セルから左方向に2つ進んで、下に4つ進んだセルと表すことができます。ほかの支店でも計算したセルの位置を表していきます。

B支店で計算したG5セルは、I5セルから左方向に2つ進んだセル、G9はI5セルから左に2つ進んで、下に４進んだセル

C支店で計算したG6セルは、I6セルから左方向に2つ進んだセル、G10はI6セルから左に2つ進んで、下に4つ進んだセル

D支店で計算したG7セルは、I7セルから左方向に2つ進んだセル、G11はI7セルから左に2つ進んで、下に4つ進んだセル

長々とセルの位置関係を書いてみましたが、セルの番地を計算するセルの位置を基準にし、上下左右にどれだけ移動したかの位置関係で説明すると、すべて同じで【左に2つ進んだセルの値を、左に2つ、下に4つ進んだセルの値で除算する】という同じ結果となります。

相対参照

このようにセルの位置関係を基準となるセルの位置から、上下左右にいくつか移動したかでセルの位置を表す相対的な位置をあらわすという意味で【相対参照】と言います。

エクセルで数式を入力したときには、基本的にこの【相対参照】という参照方法を使って計算しています。

合計や達成率では数式のコピーを行い、問題なく正しく結果が得られているのはこの相対参照で処理されているからです。

合計の計算式を相対参照の言い方に変えてみましょう。

A支店の合計G4セルでの計算式【 = SUM (C4:F4) 】は
左に3つ進んだセルから左に1つ進んだセルまでを合計するとなっています。

この表し方はB支店、C支店、D支店共に同じ言い方になります。

達成率の計算【 =G4/C4 】は
左に1つ進んだセルの値を、左に5つ進んだセルの値で除算するとなります。

このように相対的な位置関係でセルの位置を説明したときに、同じ位置関係となることから合計と達成率は式のコピーで問題なく計算ができました。縦方向の計算の合計部分や平均も同じ理由です。

このようにエクセルでは数式入力時に何も指定しない場合は、相対参照という相対的な位置関係を使って計算を行うようになっています。

一方、構成比の計算の場合は、相対参照では除数となるセルの位置がずれてしまします。そのため必ず各支店の合計金額であるG8のセル値で除算するように指定することができれば正しく計算できます。

絶対参照

列位置と行位置のセルを表す際に$マークを付けることで解決できます。エクセルの参考書などではキーボードのF4キーを押してくださいと書かれています。手入力でも構いませんがF4キーを押すのが早いかと思います。

数式を入力する際に、G8セルを入力したときにF4キーを押すことで処理ができます。

【 =G4/$G$8 】「絶対参照とは計算に使用するセルの位置を固定する」と、ご理解ください。

ただし、F4キーを押すと$G$8となり、さらにF4キーを押すとG$8と行につき、さらにF4キーを押すと$G8と列につきさらにF4キーを押すとG8とループするので、押しすぎには気を付けてください。
8に$がつくのは行固定、Gに$がつくのは列固定という複合参照となるのですがここでは説明は省きます。

別途複合参照についても説明の機会は設けたいと思います

除数として必ず【G8】のセルを使う式を入力でき、結果が得られるはずです。結果が出たセルを選びなおしフィルハンドルをドラッグします。

支店毎の合計金額を必ず全合計のセル【G8】の値で除算するという式の完成です。

達成率とか構成比といった「率・比」を求める割り算の結果表示では、%表示を行うと思います。説明ではすでに％表示になっていますが、正しく計算ができた後でセルの表示形式で％表示の設定を行うことをお勧めします。
また、小数点何位まで表示するかなど細かく指定すれば出来上がりです。

まとめ

エクセルは何も指定していなければセルの数式は相対参照で処理を行います。式のコピーを行う場合に多くの場面では相対参照で問題なく計算が可能ですが、そうでない場合はセルの参照方法を変更しなければならないことがあることを知っていただければと思います。

今回の構成比を求める場合では、必ず総合計であるセルを計算に使用しなければならないので、計算式の入力時にセルの絶対参照を行って計算しました。

この絶対参照の説明はなかなかとっつきにくい部分でもあり、エクセルに慣れない方にとっては計算式をコピーして使っていく上でのハードルとなるようです。

この絶対参照を知らない場合に、一つずつ数式を入力するなどの苦労をして作表をする人もいたりします。
今回の支店数のように4か所だけ計算するのであれば、一つずつ入力もまあ良いかぁ～ともなるかもしれませんが、極端な話支店数が100あったら？支店数が1000あったら？そのような場合に100か所計算を入れるのですか？1000か所計算式を入れるのですか？となってしまいます。

これでは時間がかかってしまいます。なによりそれだけの箇所に対して入力しようとすれば指定するセル位置を間違えてしまい正しい計算が行われないことにもつながります。

時間をかけた結果正しくない結果となってしまって何にもなりません。
エクセルの計算にはセルの参照方式がいくつかあるということを学び、理解してエクセルで計算式を入力するときに時間をかけずに済むようになっていただければと思っています。

何かの結果を求めたい場合に計算式は1か所入力し、その計算式をコピーするというのが基本的なエクセルでの操作となります。

1つずつ数式を入力するといった時間のかけ方ではなく、1か所入力する数式に考える時間をかけるようにしてほしいとWebDreamは考えます。