セルの相対参照・絶対参照・複合参照は３つのパターンの暗記で充分です（関数が苦手ならセルの参照から）
シラトリさん、私どうにも関数が苦手なんです。
・・・苦手なのは関数？それともセルの参照？どっち？
え、どっちと言われても・・・。１個はなんとかできるんですけど、コピーして貼り付けがよくわからなくて。私がやるといつもメチャメチャになっちゃうんです。
なんて抽象的な言い方・・・。きっと、それはセルの参照が苦手なんだね。
セルの参照・・・ですか？
そう。セルの相対参照、絶対参照、複合参照。ちゃんとわかっている？
セル参照の時の$の意味わかっている？
そんなに追い込むような言い方しないでくださいよー。確かによくわかってないですけど・・・。
●このエントリの内容
全部で4パターンしかないのに何でそんなに難しく考えるの？
でもさ、A1のセルを参照するなら、A1、$A$1、$A1、A$1の4つだけでしょ。セルの参照なんて4パターンしかないんだから、極端なハナシ4回試せば絶対できるんだから、難しく考え過ぎなんだと思うよ。とりあえず成功するまでやってみれば良いのに。４回だし。
なんと身も蓋もない言い方を。ちゃんと理解してから使いたいじゃないですか。失敗したらイヤですし・・・。
逆なんだよね。使わないから理解できないんだよ。それじゃ、いつまで経っても使えるようにはならないよ。
それに、たかだかエクセルの数式入力すら失敗したくないって・・・。なぜそんなに失敗を怖がるのか理由がわからないよ。
なんか壊しちゃいそうで怖いんですよ。でもやっぱりやるしか無いんですよね。
相対参照は何が相対的なの？
そもそも相対参照とか絶対参照って何なんですか？絶対参照は固定で絶対そこを参照するってわかるんですけど、相対参照がイマイチわからないんです。
相対参照は数式を入力しているセルから見て相対的な位置関係で参照する事を言うんだ。
相対的な位置関係？
例えば、下図の=C26/$C$32という数式は、一つ左隣りのセル÷C32のセルという意味になるんだ。
C26のセルは、数式を入力しているD26から見て一つ左隣だよね。$C$32は絶対にC32を参照するという指定だよね。
こうやって指定すると、下方向にコピーした時にも、一つ左隣りのセル÷C32のセルという数式を一気に作る事ができるんだ。
なるほどー。
相対参照は、数式をコピーした時にも位置関係を崩さない指定方法なんですね。絶対参照は絶対そのセルを参照する。
なんかC26ってなっていると、必ずC26を参照するようなイメージだったから勘違いしていました。数式を入力しているセルから見た相対的な位置関係なんですね。んー、でもちょっと混乱しちゃいますね。
後は経験値をあげるしかないね。
次の例の=C26/C$32という数式は、
一つ左隣りのセル ÷ 一つ左隣りの32行のセル という意味になるんだ。
こういう参照方法を、行だけ絶対参照とか複合参照と言う。
さっきは分母が$C$32だったけど、今度はC$32でCの前の$を消したんですね。
一つ左隣りの32行ですか・・・。これはどういう場合に使うんですか？
こう指定しておくと、例えば、同じ構成の表が横にある場合に、構成比の数式をそのままコピーできるから楽なんだ。
へー、なるほどです。
じゃあ、これは意味わかるかな？
一つ左隣りのセル ÷ 一つ左隣りの、６つ下のセル という意味で良いですか？
そうだね。エクセルで普通に数式を入れるとこうなるよね。
で、このまま下にコピーすると、分母はC32を参照して欲しいのに、一つ左隣りの、６つ下という位置関係で参照しちゃうから、何も入力されていないC33行目を参照しちゃって、#DIV/0ってエラーになる。
あー、よくやっちゃいます。ところで$はどうやって入力するんですか？SHIFT+4で入力するんですか？
わざわざSHIFT+4で$を入力しなくても良いよ。数式入力中に[F4]キーを押すと$を入力できるよ。押す回数によって参照方法が切り替えられるから、下の表を参考にしてね。初期値は相対参照ね。
| 参照方法 | セル表記 | F4を押す回数 |
|---|---|---|
| ●絶対参照 | $A$1 | 1回 |
| ●行のみ絶対参照 | A$1 | 2回 |
| ●列のみ絶対参照 | $A1 | 3回 |
| ●相対参照 | A1 | 4回 |
行のみ絶対参照と列のみ絶対参照が覚えられるかなぁ？間違えちゃいそう。
きっちり覚える必要もないけどね。僕だって毎回F4キーを押してから、$がどこについたかを確認しながら入力しているし。勘違いでよく間違うしね。
でもね、セルの参照なんて最初は暗記で全然構わないんだよ。
こういう場合は、こう指定するって言うパターンがあるんだ。まずはそこから始めたらどうかな？
ぜひそれでお願いしますー。
同じ行で違う列のセルを参照するパターンを覚える
セルの参照に気を使うのは数式をコピーする時だよね。
下図ではD21に数式を入力して、それを右方向にも下方向にもコピーする例で説明するね。
数式を入力するセルから見てどこを参照するか？それによってパターンが決まるんだ。
まず数式を入力するセルと同じ行で、違う列を参照するパターン。
えーと、D21に数式を入力する時に、C21を参照するって事ですね。行は同じ21行で列がDとCで異なる。
この場合は、F4を3回押して列のみ絶対参照にする。$C21とするんだ。数式を入力するセルと同じ行のC列を参照するって意味になる。
もちろんこの数式を横方向にコピーしないのであれば、C21と相対参照で構わないよ。
つまり、数式を入力するセルと同じ行で、違う列を参照する場合は、$C21かC21の2択になるんだ。
列だけ絶対参照か相対参照かの２択ですか。それも横方向にコピーするなら列だけ絶対参照、しないなら相対参照って判断できるなら私でもすぐに覚えられそうです。
同じ列で違う行のセルを参照するパターンを覚える
次は、同じ列で違う行のセルを参照するパターン。さっきは１つのセルを参照したけど、今度は範囲を参照してみよう。
セル範囲って事は、始点と終点があるんですよね。また難しくなりそうですね。
１つのセルを参照するのも、セル範囲を参照するのも基本は同じだよ。
D21に数式を入力し、D11:D20の範囲を参照する場合になる。
同じD列で、違う行の範囲を参照するんですね。
この場合は、F4を2回押して行のみ絶対参照にする。D$11:D$20とするんだ。数式を入力するセルと同じ列の11行～20行を参照するという意味。
始点と終点の両方とも行だけ絶対参照にすれば良いんですね。
そうだね。範囲の始点と終点で、相対参照と絶対参照が変わる例、例えばD11:$D$20のような指定になる事はあまり無いよ。
なるへそです。数式を入力するセルと、同じ列で違う行の範囲を指定する場合は、行だけ絶対参照にすれば良く、コピーしない時はもちろん相対参照で構わないって事ですね。
そのとおり。
行も列も違うセルを参照するパターンを覚える
最後は、数式を入力するセルと、行も列も違うセル範囲を参照する場合になるんだけど、これだけ少し選択肢が増えるから注意してね。
D21に数式を入力し、C11:C20の範囲を参照する場合になる。行も列も変わっているよね。
この場合は、F4を1回押して絶対参照にする。$C$11:$C$20とするんだ。9割くらいは絶対参照で大丈夫。
9割ですか？
残りの1割くらいは、F4を2回押して行のみ絶対参照、C$11:C$20にするパターンになるかな。
さっきの、=C26/$C$32と=C26/C$32みたいなパターンですね。確かにどちらも数式を入力するセルと行も列も違うセルを参照していますね。
じゃあ、数式を入力するセルから見て、行も列も違うセルを参照する場合は、色々考えずまず絶対参照にしてみても9割は正しいって事ですね。だめなら行のみ絶対参照にする。
もちろん例外もあるけどね。最初のうちはこの３パターンを暗記しておけば良いと思うよ。
C:Cみたいに列全体を指定した場合はどうなるんですか？
行も列も違うセルを参照するパターンで良いよ。$C:$Cのように絶対参照にする。ダメなら相対参照で。
まとめるとこんな感じだね。もちろん数式をコピーしないのであれば相対参照のままで構わないよ。
| 数式を入力するセルから見て | セル表記 | F4を押す回数 |
|---|---|---|
| ●同じ行で違う列のセルを参照する | 列のみ絶対参照（$A1） | 3回 |
| ●同じ列で違う行のセルを参照する | 行のみ絶対参照（A$1） | 2回 |
| ●行も列も違うセルを参照する | 絶対参照（$A$1）または行のみ絶対参照（A$1) | 1回または3回 |
SUMIF関数での使用例
それじゃあ、SUMIF関数を使う場合で、どのように引数を指定していくか解説するね。SUMIF関数は、SUMIF(範囲,検索条件,合計範囲）という書式で、範囲・検索条件・合計範囲の３つの引数を指定する。今回は、あえてややこしい表の下に数量と金額の計を入れるパターンでやってみよう。
第一引数の「範囲」は数式を入力するセルから見て、行も列も違う範囲を指定するから絶対参照で、$C$11:$C$20というように参照する。
第二引数の「検索条件」は、数式を入力するセルから見て、同じ行で列が違うセルを指定するから、列のみ絶対参照（列固定）で$C21というように参照する。
第三引数の「合計範囲」は、数式を入力するセルから見て、同じ列で行が違う範囲を指定するから、行のみ絶対参照（行固定）でD$11:D$20って参照するんですね。
なるほどー。確かにパターン通りですね。
VLOOKUP関数での使用例
次はVLOOKUP関数で使うパターンで見てみよう。
こんな感じで、C18に数式を入力し、横（D18）と下(C19～C23）にコピーする時に、引数をどう指定するか。
VLOOKUP関数の書式は、=VLOOKUP(検索値,範囲,列番号,検索方法)で、セルを参照して指定する引数は検索値と範囲になるよね。
まず、第一引数の検索値は、数式を入力するセルから見て、同じ行の違う列を参照するから、列だけ絶対参照で、$B18というように参照する。
同じ行のB列を参照するって事で良いですかね？D18に数式をコピーしても、同じ行のB列を参照したいので$B18になるんですね。
そうだね。
第二引数の範囲は、J列からL列までを指定するんだけど、これは数式を入力するセルから見て、行も列も違うパターンになるので、絶対参照にする。まあVLOOKUP関数の範囲は必ず絶対参照にするというクセをつけた方が間違えがない。
おー、これもパターン通りですね。
でしょ。もちろん例外はあるから、このパターンでやってみて出来なかったらF4で参照を変えてみると良いよ。
最後に
最後までお読みいただきありがとうございました。
関数が苦手な人は、実はセルの参照が苦手なのではないでしょうか？
数式を入力する時は、必ずセルを参照します。このセル参照ができないと、すなわち数式が入力できないという事になります。
相対参照、絶対参照をしっかり使えると、数式を１つ入力して後はコピー＆貼付けだけで表を完成できるようになるので、ぜひぜひ習得して頂きたいのですが、やはり慣れないうちは難しい。その対応方法として整理してみましたので、騙されたと思ってこのパターンを暗記し使ってみて下さい。そして使っていくウチにこういうものなのか・・・という事が理解できるようになります。
数式を入力する行までの累計や件数を求めるような場合に、範囲の始点と終点で、相対参照と絶対参照が変わる指定をする事がありますが、それ意外は9割方このパターンで行けると思います。
