「数式をコピーしたら計算結果がなんかおかしくなった」
「この場合、相対？絶対？」
「列と行、どっちを絶対参照にするの？」
と言ってエクセルが苦手になってしまった人、いませんか？
セル参照を正しく設定しないと、正しい計算が行われず、まともな表が作れなくなります。
でも安心してください。ポイントさえ押さえれば、そんなに難しい概念ではありません。
相対参照(そうたいさんしょう)とは、「数式を入力したセルから見た」位置関係でセルを参照すること。
絶対参照(ぜったいさんしょう)とは、「どのセルから見ても」同じセルを参照すること。
記事内では、具体的な例をあげながら、わかりやすく解説しています。
この記事は
- セル参照とは
- 相対参照
- 絶対参照
- 複合参照
- 他のシートのセルを参照
について説明しています。
エクセルのもっとも基本的な概念であるセル参照をマスターすれば、数式も関数もこわくありません。
セル参照とは
セル参照って何？
数値の入った表があります。
E2セルに
=A1+C2
と、入力してみましょう。計算結果として9が表示されます。
A1セルの値「１」と C2セルの値「8」を数式に取り込んだわけです。
このように数式や関数の引数に、あるセルの値を取り込むことを「セルを参照する」「セル参照」と言います。
「セル参照」とは、数式や関数の引数にセルの値を取り込むこと
数式バーには入力した数式が表示され、どのセルを参照しているかがわかります。
セルを範囲で指定してみましょう。
E3セルに
=SUM(A1:C3)
と、SUM関数を入力。
「A1:C3」とは、A1セルから C3セルまでの連続したセルの範囲を意味します。
このように、セル範囲を指定するときは左上のセル番地と右下のセル番地を: (コロン)で連結します。
セル範囲 [セル番地] : [セル番地]
セル指定の方法
数式内でセル参照を指定するとき、
- 直接、セル番地をキーボードで入力
- マウスでクリックまたはドラッグ
の二つの方法があります。
参照しているセルは色付きの枠(わく)で囲われます。
この枠は、マウスで移動したり拡大・縮小することが可能です。
入力を確定すると、この枠は消えます。
もう一度表示させたいときは、数式の入ったセルをダブルクリック、もしくは F2 キーを押して編集モードにしてください。
セル参照には「相対参照」と「絶対参照」があります。
以降でこの違いを解説します。
相対参照
相対参照とは
E2セルに
=A1+C2
という数式が入力されています。
これをすぐ下の E3セルにコピーしてみましょう。
E2セルをクリック。わくの右下にマウスカーソルを合わせて、十字型になったらそのまま下にドラッグ。オートフィルでコピーできました。
同じ数式をコピーしたつもりなのに、E2セルには9、E3セルには11と表示されています。数式バーで確認してみましょう。
E2セルは =A1+C2
E3セルは =A2+C3
となっています。勝手にセル参照が変更されています。なぜでしょう。
説明を簡単にするために数式をもっと単純にします。
E2セルに
=A1
と入力。その下の E３セルにもコピーします。今度は右側の F2セルにもコピーしてみましょう。
各セルを数式バーで確認。
E2セルは =A1
E3セルは =A2
F2セルは =B1
おわかりでしょうか。
実は E2セルが参照している A1セルというのは、A1セルそのものではなく、相対的な位置関係を表しています。
” E2セルを基準にして左に 4つ、上に 1つのセルを参照するよ ”
という意味なのです。
E3、F2に入力されたセル参照も、「左に4つ、上に1つのセルを参照する」という、
同じ数式がコピーされているわけです。
このように、相対的な位置関係でセルを参照することを相対参照と言います。
左 (右) に○○、上 (下) に○○の位置のセルを参照
”A1” “A1:C3” などセル番地を指定して参照した場合、もしくはマウスでセルをクリック・ドラッグした場合、相対参照になります。
それがエクセルの仕様です。
なぜでしょう。その方が便利だからです。
相対参照が便利な理由
A列に商品名、B列にそれぞれの単価、C列にそれぞれの売上個数を入力した表があります。
D列にそれぞれの売上合計 (単価 X 個数) を表す数式を入れようとしています。
D2セルに
=B2*C2
と入力。あとは、6行目までコピーするだけで終了です。
[ 2つ左、同じ行のセル ] * [ 1つ左、同じ行のセル ]
という位置関係は変わらないからです。
実務で大量のデータの表をあつかう場合、ひとつひとつのセルにそれぞれセル参照を入力するのはたいへん面倒です。
相対参照を利用して数式をコピーしたほうが、はるかに楽です。
相対参照の便利さがお分かりいただけましたか。
関数の引数で使われるセル参照も、理屈は同じです。
オートフィルによる数式のコピー
「絶対参照」を解説する前にオートフィルでのコピーについて説明します。
オートフィルは連続データをつくることもできますが、数値や数式のオートフィルではコピーになります。
前述の例のように、同列の数式のコピーにもっとも使われるのはオートフィルです。
絶対参照
数式を入力するセルの位置にかかわらず、つねに同じセルを参照したい場合があります。
先ほどの表のE列に、消費税額を表示する数式を追加したいです。
H1セルに消費税率の10%が入力されています。E2セルに
=D2*H1
と入力。
「10%」という数値はかけ算ではそのまま「0.10」として計算されるので、「*0.10(かける0.10)」という意味になります。
6行目までオートフィル。
E3 ～ E6セルが明らかにおかしいですね。数式バーで確認してみましょう。
相対参照によって、消費税率の参照が H1→H2→H3… と移動してしまっています。
こんなときは、消費税率の参照をH1セルに固定したいですね。
E2セルをダブルクリック。E2セルの数式が編集できるようになりました。
「H1」のところにカーソルをあわせた状態 (前後、真ん中どこでもいい) で F4 キーを 1回押します。
「$H$1」と表記が変わりました。
Enter キーで確定したら E6セルまでコピーします。今度は正しく消費税額が計算されました。
このように、列と行の先頭に $ (ドルマーク)がついた状態を絶対参照と言います。
どこのセルから見ても、つねに同じ H1セルを参照するという意味です。
どこから見ても同じ位置のセルを参照
「相対参照」と「絶対参照」のちがい、お分かりいただけましたか？
例をあげれば、
- 「ここから東へ5km、北へ2km行ったところ」という指定は相対参照。
- 「○○県△△市××町何番地」という指定は絶対参照です。
複合参照
相対参照と絶対参照を組み合わせた参照もあります。複合参照と言います。
前項の絶対参照の例で、H1にカーソルがある状態で F4 キーを 1回押しました。実はさらに数回 F4 キーを押すと参照の形が変化します。
A1セル参照を変化させます。
行と列、相対参照と絶対参照を組みあわせた形です。
| A1 | 行も列も相対参照 |
| $A$1 | 行も列も絶対参照 |
| A$1 | 列Aは相対参照、行1は絶対参照 |
| $A1 | 列Aは絶対参照、行1は相対参照 |
- 「A$1」は「行を 1 に固定する」
- 「$A1」は「列を A に固定する」
と考えるとわかりやすいでしょう。
それぞれの参照を、行方向・列方向にコピーした場合を確認してみましょう。
「相対参照」
行も列も変化する
「絶対参照」
行も列も固定
「複合参照」
列は変化
行は 1 で固定
「複合参照」
列は A で固定
行は変化
行か列を固定する参照
複合参照使用例
5種の商品の、単価と年度別売上個数をしめした表があります。
F列からH列に年度別売上金額を計算する数式を入れたいです。
相対参照で各列ごとに入力していく方法もありますが、ここでは複合参照を使って効率的に入力します。
まず、F3セル。
1年度の売上金額は単価に1年度の売上個数をかければいいですね。
=B3*C3
と入力。次にこの数式をG3、H3にコピーしていきたいんですが、相対参照によって売上個数はうまくずれてくれますが、単価の参照はずれて欲しくありません。
こんな時はF3セルの数式をつぎにようにします。
=$B3*C3
これを G列、H列にコピーしましょう。単価は列B が固定されているので(行はすべて 3行)、つねに B3セルを参照しています。
あとはこの3つの数式をそのまま 7行までコピーしましょう。完成です。
商品B～商品E についても相対参照で単価の参照が移動しています。
このように、相対参照と絶対参照を行と列で組み合わせるのが、複合参照です。
複合参照といえば…
複合参照の説明でかならず出てくる”ネタ”があります。
「かけざん九九の表を作ろう！」です。
「いんいちがいち、いんにがに、・・・」の表を作ろうということです。
実はこの表を完成させるには一つの数式のコピペで済みます。
B2セルに
=$A2*B$1
と入力し、あとはすべてのセルにコピーすれば完成です。なぜこれでいいのか。
パズルを解くノリで考えてみてください。
解説を。
説明のため、*(かける)の前を「前の数字」、後ろを「後の数字」とします。A列に縦に並んでいる数字を前の数字、行1に横に並んでいる数字を後の数字とします。
B2セルに入力された数式で、「前の数字」は「$A2」です。「前の数字」はつねに A列を参照するので列を絶対参照にします。
2行の「前の数字」はすべて「$A2」、3行の「前の数字」はすべて「$A3」、4行の「前の数字」はすべて「$A4」… となります。
次に「後の数字」はB$1です。「後の数字」はつねに1行を参照するので行を絶対参照にします。
B列の「後の数字」はすべて「B$1」、C列の「後の数字」はすべて「C$1」、D列の「後の数字」はすべて「D$1」… となります。
完成！
他のシートのセルを参照
他のシートのセルを参照するにはどうすればいいでしょう。
例えば、「シートB」の A3 セルを参照する場合。
シートB!A3
とします。”シート名”の後に、! (エクスクラメーションマーク)、その後にセル番地でOKです。
まとめ
まずは「相対参照」と「絶対参照」を完全に理解しましょう。
「複合参照」はその次でもいいです。
たとえば、複合参照の項で説明したこの表。
F3セルに「=B3*C3」として、F7セルまでコピー(相対参照)
G3セルに「=B3*D3」として、G7セルまでコピー(相対参照)
H3セルに「=B3*E3」として、H7セルまでコピー(相対参照)
でも数式は完成します。
複合参照の方が効率的というだけです。