今回は、普段なんとなく使っている絶対参照と相対参照の意味と使い方について確認しつつ、絶対参照を使って表を効率的に作る方法をご紹介します。

早速ですが、前回のブログでご紹介した表をご覧ください。

この表のセル B2 には下記の数式が入っています。

=SUMIFS(‘201907_週別仕入明細表’!$D:$D,’201907_週別仕入明細表’!$B:$B,$A2,’201907_週別仕入明細表’!$A:$A,B$1)

この数式は参照元の表（201907_週別仕入明細表）から「みかん」「りんご」「メロン」の仕入数を日付ごとに集計しています。参照元の表はこちらです。

前述 SUMIFS 関数の数式に含まれるアルファベットの前や後ろに「$」マークがあるのがおわかりかとお思います。この「$」マークが入っていることで、

・セル B2 の数式をコピーする
・B2:F4 にペーストする

という2ステップで集計することができます。試しに、「$」マークをすべて外して先ほどの2ステップを実行してみると、下の画像のようになってしまいます。

セル B2 に入っていた数式の「$」マークを外すことで相対参照になってしまい、数式を他のセルにペーストすると参照元が見つからないため、エラー（#REF!）になってしまいました。

絶対参照という言葉を聞くとちょっと抵抗があるかもしれませんが、使いこなせると絶対に便利です。3つのルールさえ覚えれば、簡単に使いこなすことができます。

【ルール 1】アルファベットの前に「$」マークをつけると、列を固定できる

【ルール 2】アルファベットの後ろに「$」マークをつけると、行を固定できる（= 行の前に「$」マークをつけると、行を固定できる）

【ルール 3】アルファベットの前と後ろの両方に「$」マークをつけると、列と行を固定できる

「絶対参照」の3つのルールを詳しくお話しする前に、まずは「相対参照」について確認します。

相対参照

数式の入っているセルをコピーして別のセルにペーストしたとき、コピー元からペースト先までの動かした分だけ、数式の列や行が増えます。スプレッドシートやエクセルの数式や値をコピーすると、「右クリック > 特殊貼り付け」などを使わない限り、基本的には相対参照のルールでペーストされます。

文章で説明するのはなかなか難しいので、画像を見ながら確認していきます。

セル A1 から C3 に文字の入った表を作ります。

セル E1 に「=A1」と入力すると、セル A1 に入力されている「あ」が表示されます。

セル E1 をコピーして、セル F1 にペーストします。すると「=B1」が入力され、セル B1 の「い」が表示されます。セル G1 にペーストすると、「=C1」が入力され「う」が表示されます。セル E1 と セル A1 とは「3列分離れていて同じ行」なので、セル F1 と セル G1 にも「3列分離れていて同じ行」のセルが参照元として入力されます。

今度は、セル E1 をコピーして、セル E2 にペーストします。そうすると、「=A2」が入力され、セル A2 の「か」が表示されます。先ほどの例と同じように、ペーストしたセルから見て「3列分離れていて同じ行」が表示されるようになります。

ある数式が入っているセルをコピーして、ひとつ右のセルにペーストすると、数式の列がひとつ増える、ひとつ下のセルにペーストすると、数式の行がひとつ増えるとも言い換えられます。ふたつ右にペーストすれば数式の列がふたつ増え、ふたつ下にペーストすれば数式の行がふたつ増えます。

NuAns

アクセサリーストッカー NuAns CADDY（キャディー）

3,036 3,036 3,036 円
選択して下さい テクスチャーブラック テクスチャーホワイト カートへ入れる

絶対参照

相対参照は、ある数式が入ったセルをコピペすると、コピー元とペースト先の関係が保たれていました（例では「3列分離れていて同じ行」という関係があった）。スプレッドシートやエクセルで数式は、基本的には相対参照のルールでコピペされます。

しかし、複数のセルに同じ参照元の値を入れたいときもあります。また、関数などで、参照元の範囲を固定させたいときもあります。そのような時は「$」マークをつけて参照したい列や行を固定させます。先ほどご紹介した絶対参照の3つのルールに沿ってまとめていきます。

【ルール 1】アルファベットの前に「$」マークをつけると、列を固定できる

セル E1 に「=$A1」と入力します。セル E1 をセル F1 にコピーしても、セル G1 にコピーしても「=$A1」が入力されます。アルファベットの前に「$」マークをつけると、参照する列が固定されるからです。

しかし、セル E1 をセル E2 にコピーすると行がひとつ増えて「=$A2」が入力されます。列は固定されていても、行は増えたり減ったりします。

【ルール 2】アルファベットの後ろに「$」マークをつけると、行を固定できる（= 行の前に「$」マークをつけると、行を固定できる）

セル E1 に「=A$1」と入力します。セル E1 を セル E2 にコピーすると「=B$1」と入力され「い」が表示され、、セル E3 にコピーすると「=C$1」が入力されて「う」が表示されます。

セル E1 を F1 にコピーすると「=B$1」が入力されます。行は固定されて、列は増えたり減ったりします。

【ルール 3】アルファベットの前と後ろの両方に「$」マークをつけると、列と行を固定できる

ルール1とルール2の合わせ技です。これは、VLOOKUP 関数で説明していきます。

• みかん：100円
• りんご：200円
• メロン：300円

という価格表があって、別のリストに単価を転記するために VLOOKUP 関数を使います。

セル F1 に「=VLOOKUP(E1,A1:B3,2,false)」と入力しました（ちなみに、数式は「セル E1 に入力されているメロンを範囲 A1:B3 の中から探して、範囲 A1:B3 の左の列から数えて2番目の列から単価を拾い出す」という意味です）。

では、この数式をコピーして、セル F2、F3 にペーストします。すると、エラーが出てしまいました。これは、参照範囲を A1:B3 と設定していたのに、ひとつ下、ふたつ下にペーストすることで、参照範囲が変わってしまったからです。セル F3 の参照範囲は A3:B5 になってしまっています。

セル F3 には、セル E3 の「りんご」を参照範囲 A3:B5 から探し出す数式が入力されていますが（「=VLOOKUP(E3,A3:B5,2,false)」）、「りんご」は参照範囲 A3:B5 には含まれていないため、エラーが出ています。

参照範囲を動かしたくないので、列も行も固定します。セル F1 に「=VLOOKUP(E1,$A$1:$B$3,2,false)」と入力して、セル F2、F3 にそれをペーストすれば、参照範囲を変更することなく、みかんとりんごの単価を価格表から拾い出すことができます。

ちょっと現実的でない例になりますが、今作った表の見出しに日付をつけて、セル F2 に入力した数式をセル G2、H2 にペーストしてみます。すると、また、エラーが出てしまいます。

セル H2 には「=VLOOKUP(G2,$A$1:$B$3,2,false)」と入力されてしまいました。ここは「=VLOOKUP(E1,$A$1:$B$3,2,false)」と入力したいところです。価格表から「メロン」（セル E1）の単価を探してほしいのに、セル G2 の「#N/A」を参照範囲 A1:B3から探しだそうとして見つけられないことがエラーの原因です。このときは、前述のルール 1を使って、列を固定します。

セル F2 に「=VLOOKUP($E1,$A$1:$B$3,2,false)」と入力すると、セル G2、H2 にペーストすると、うまく表示されるようになります。

F2:H4 に「=VLOOKUP($E1,$A$1:$B$3,2,false)」をペーストすると「メロン」「みかん」「りんご」の単価をそれぞれ表示するようになります。

NuAns

マルチ充電トレイ NuAns COLONY（コロニー）

3,544 3,544 3,544 円
選択して下さい テクスチャーブラック テクスチャーホワイト カートへ入れる

まとめ

ひとつずつ確認していくと難しいように感じますが、ルールは3つです（ルール 3はルール1と2の合わせ技なので、2つだけ覚えておけば大丈夫です）。

【ルール 1】アルファベットの前に「$」マークをつけると、列を固定できる

【ルール 2】アルファベットの後ろに「$」マークをつけると、行を固定できる（= 行の前に「$」マークをつけると、行を固定できる）

【ルール 3】アルファベットの前と後ろの両方に「$」マークをつけると、列と行を固定できる

もう一度、はじめに見た表の数式を確認してみます。参照元の表から「みかん」「りんご」「メロン」の仕入数を日付ごとに集計します。

セル B2 に数式を作って、それを B2:F4 にペーストして集計を完了させます。

参照元の表（201907_週別仕入明細表）はこちらです。

集計に使う表はこちらです。

SUMIFS 関数を使って集計します。はじめに、集計する表のセル B2 に数式を入れます。

=SUMIFS(‘201907_週別仕入明細表’!D:D,’201907_週別仕入明細表’!B:B,A2,’201907_週別仕入明細表’!A:A,B1)

このままだと、B2:F4 に数式をペーストしたときに、参照元や参照範囲が変わってしまいます。右のセルにペーストすれば列が増え、下のセルにペーストすれば行が増えてしまいます。どこを固定したいかを考えながら「$」マークを入れていきます。

• 201907_週別仕入明細表’!D:D → 201907_週別仕入明細表’!$D:$D …列を動かしたくない
• 201907_週別仕入明細表’!B:B → 201907_週別仕入明細表’!$B:$B …列を動かしたくない
• A2 → $A2 …列を動かしたくない、行は動いてほしい
• 201907_週別仕入明細表’!A:A → 201907_週別仕入明細表’!$A:$A …列を動かしたくない
• B1 → B$1 …列は動いてほしい、行は動かしたくない

セル B2 の数式は結果下記のようになります。

=SUMIFS(‘201907_週別仕入明細表’!$D:$D,’201907_週別仕入明細表’!$B:$B,$A2,’201907_週別仕入明細表’!$A:$A,B$1)

これを B2:F4 にペーストすれば完了です。

最後に

考えているとどこに「$」マークを入れたらいいのかわからなくなってしまう…。というのはよくよくあります。なので、いったんは、全部のアルファベットの前に「$」マークをつけてみる。そして、どこか変な値が返ってきたら「$」マークをつける位置を再確認する。という手順で作ってもいいのかなとも思います（実際、私はこの手順で作っていて、だからいつまで経っても絶対参照をきちんと覚えられないのだと思います）。

悩みながらも表を作り続けていると、どんな表でどんな関数を使ったときに「$」マークをどの辺に入れたらいいかがわかるようになってくると思います。いろんな表でぜひ、練習してみてください。

★

「あさすけ先生の、超役に立つGoogleスプレッドシート解説」関連記事

 
▶ 【スプレッドシート時短術】19,767個の空白セルを3秒で「0」にする小技
▶ 計算式のコピペ漏れを防ぐ！　気難しそうに見えて案外シンプルないいヤツ 〜ARRAYFORMULA関数〜
▶ VLOOKUP関数のエラー原因を探すのに2時間もかかっていた私が、いろいろな関数を使いこなせるようになったコツを考えてみた。
▶ Googleスプレッドシートに週ごとにタスクをまとめて、その週の未完了タスク・完了タスク・タスクの達成率がひと目でわかるようにする
▶ IMPORTRANGE関数で休日設定シートを転記すればすべてが丸く収まった
▶ 値ペーストと書式ペーストを使いこなすと作業が格段に速くなる – エクセルやGoogleスプレッドシート
▶ Googleスプレッドシートでデータの結合・分割の幅を広げたい。改行するなら CHAR(10) を使おう！
▶ 「$」マークを使いこなして表をパパッと作る 〜相対参照と絶対参照の復習〜（本記事）
▶ わざわざ使いたい、転置して貼り付けができるGoogleスプレッドシートの「TRANSPOSE関数」
▶ 地味にはまっている、GoogleスプレッドシートでUNIQUE関数とSORT関数で入力規則（プルダウン）用のデータリストをつくる方法
▶ Googleスプレッドシートのフィルタ表示機能が便利
▶ Googleスプレッドシートでチェックリストを5分で作ってみる
▶ 営業日の異なる日本と中国のスケジュール作成ツールをGoogleスプレッドシートで作ってみた