相対参照、絶対参照、複合参照/まとめて一括式設定、$固定ショートカット

if-768x332 基礎コース

相対参照

相対参照とは・・・・

数式をコピーした場合に、コピー元から移動した分だけ、数式中で参照されているセルがコピー先のセル番地に合わせて変更される機能(参照方法)です。

では、実際に相対参照の使い方例について数式のコピーを用いて理解していきましょう。

使い方例)コピー・貼り付け

下の表は、とあるネットショップの売上一覧表です。
この表を利用して、相対参照がどのようなものか、どのように便利かを確認していきましょう。

セルG4には、商品『テーブル(木目調)』の送料を表示するため、①「金額(セルF4)が5000円以上の場合は送料が無料、5,000未満の場合は500円」と表示される以下のIF関数が入力されています。(IF関数の利用方法はこちら)

=IF(F4>=5000,0,500)

if-768x332

 

 

この表のその他の商品についても、セルG5・G6に同様に送料を表示する数式を入力すると、それぞれ以下の式を入力する必要があります。
=IF(F5>=5000,0,500)
=IF(F6>=5000,0,500)

それぞれの商品の金額が入力されているセルF5F6をひとつずつずらして式を入力する必要がありますが、これは大変手間のかかる作業です。

しかしながら、Excelには元から、式をコピーすると、その式に利用されている位置を表す情報は、コピー元からコピー先までの移動分だけ、自動的にずれるという『相対参照』の機能をもっています。

実際にどういうことか、以下でセルG4に入力された数式をコピーして、セルG5に貼り付けてみましょう。
セルのコピー・貼り付け方法は以下の手順で行います。

(1)セルのコピー
セルをコピーする方法はいくつかありますが、以下の方法Ⅰ~Ⅲのいずれかで、
対象のセルをコピーします。

方法Ⅰ.コピー元のセルを選択し、マウスを右クリックして「コピー」を選択。
方法Ⅱ.コピー元のセルを選択し、キーボードの「ctrlキー」(コントロールキー)と「C」を同時に押す。
(ctrl + C はコピーのショートカットです。)
方法Ⅲ.以下の方法で、メニューからコピーする。

→ コピーしたいセル(=G4)をクリックして選択。

img7

 

→ 画面上部の「ホーム」をクリックして表示の切り替え。

img8

→ ボタンの中から左上にある「コピー」ボタンをクリックすると、セルの内容が記憶されます。

img9

(2)セルの貼り付け
次にセルの貼り付けです。セルの貼り付け方法も、以下の方法Ⅰ~Ⅲのいずれかで、
対象のセルを貼り付けできます。

方法Ⅰ.貼り付け先のセルを選択し、マウスを右クリックして「貼り付け」を選択。
方法Ⅱ.コピー元のセルを選択し、キーボードの「ctrlキー」(コントロールキー)と「v」を同時に押す。
(ctrl + v は貼り付けのショートカットです。)
方法Ⅲ.以下の方法で、メニューから貼り付けする。

→貼り付けたいセル(=G5)を選択

img10

→画面上部の「貼り付け」ボタンをクリックすると、貼り付け完了です。
※注意:このとき、文字ではなく、絵の方をクリックするようにしてください。

img11

(3)セルのコピー・貼り付け完了!

これで、セルのコピー・貼り付けが完了しました。(以下はコピー・貼り付け後の表です。)

img12

 

(4)コピーされた式が相対参照になっているか見てみよう!

では、実際の数式の変化(=相対参照)を確認してみましょう。

img13

 

数式を見てみると、コピー元の式ではセル「F4」が参照されていたものが、1行下にある貼り付け先では、セル「F5」に自動で変化しています。

セルG4(コピー元): =IF(F4>=5000,0,500)
セルG5(コピー先): =IF(F5>=5000,0,500)

これによって、「クローゼットの金額は5000円以上となるので送料無料」になり、相対参照されていることが分かります。

 

まとめて一括で式を反映させる方法

さて、複数のセルにて、同様の計算を行う場合、ひとつひとつコピー・貼り付けを行うのは少し面倒です。せっかく式の利用によって便利になっても、大量のデータを一つ一つコピー&ペーストするのでは、業務が効率的になったとは言えません。

そこで、次に学ぶ一括して反映する方法=『オートフィル』について勉強していきましょう。

オートフィルとは・・・

値や式が入力されたセルを選択し、表示される『フィルハンドル』をドラッグすることによって、連続したコピーが出来る機能です。この機能によって、同じような式を複数のセルにそれぞれコピー・貼り付けをしたり、入力したりするよりも、簡単に一括で設定が出来るようになります。

フィルハンドルってなに?フィルハンドルとは、セルを選択した時に表示される、右下の四角のことです。

img14

このオートフィルフィルハンドルを利用して、作成した関数式を、複数のセルにまとめて一括で反映させる方法を見ていきましょう。

使い方例)オートフィル

オートフィルによる一括での式の反映方法は以下の手順で行います。

(1)一括設定したいコピー元のセルを選択する

→ コピーしたいセル(=G4)をクリックして選択します。

img15

 

マウスカーソルをフィルハンドルの上に重ねます。きちんと重なっていれば、図のようにカーソルが黒い十字の状態になります。

img16

 

(2)フィルハンドルをコピーしたい方向にドラッグ

コピーしたい方向へドラッグする(~G6まで)と、連続したデータ(または、式)が、カーソルに合わせて反映されていきます。

img17

 

(3)まとめて一括で式が反映されているか確認

コピー・貼り付けした時と同様に、セルの内容は相対参照され、数式が変化していることを確認しましょう。以下はオートフィル後の表です。

img18

 

数式を見てみると、コピー&貼り付けと同様に、コピー元ではセル「F4」が参照されていたものが、オートフィル先ではそれぞれ、セル「F5」・「F6」と、ドラッグした方向(行方向・下)に、移動した分だけ変化しています。

コピー元(G4): =IF(F4>=5000,0,500)
コピー先
1つ下の行(G5): =IF(F5>=5000,0,500)
2つ下の行(G6): =IF(F6>=5000,0,500)

これによって、式をまとめて一括反映させることができました。

 

POINT!

オートフィル機能は、縦にコピーするだけではなく、横にもコピーできるので、表の内容や条件によって
使い分けるようにしましょう。

以下は、売上一覧表において『数量』『金額』『送料』の合計を求めるために、『単価』の合計を表す関数式を横にオートフィルした場合の一例です。

img19

 

この例では、式の4行目~6行目を合計するSUM関数が使用されていますが、それぞれ、オートフィルによって行の指定は変更されず、列の指定のみが自動的に変更されています。

元の式(セルD7)

=SUM(D4:D6)

コピー先(セルG7)

=SUM(G4:G6)

 

D列からG列まで、3列横にオートフィルしたところ、参照先も3列分移動していることがわかります。オートフィルによる相対参照は、コピーする方向によって、縦なら行が、横なら列が自動的に変更されるという事がお分かりいただけたでしょうか。

 

絶対参照

絶対参照とは・・・

コピーやオートフィルによって、コピー先の移動の分だけ参照先が変化する相対参照とは違い、数式中の参照しているセルが、コピー先に関わらず固定され、変化しないようにする参照方法です。
参照先を固定するには、固定したい列や行の前に「$」を表記します。

例)「A1」のセルを固定したい場合 → $A$1

絶対参照を学ぶ前に、まずは相対参照では不都合な例を見てみましょう。

相対参照の不都合な例)

下記の表は、売上一覧表の『商品コード』にしたがって、商品コード一覧表から商品コードと一致した商品名を自動的に表示するように設定した、売上データ入力用マスターです。
セル「C7」には、①『すでに入力されているB列の商品コードを元に、右側の表「I4:K9」を参照して同じ商品コードである商品名を自動的に表示する式』がVLOOKUP関数を用いて設定されています。(VLOOKUP関数の使い方はこちら

このセル「C7」について、セル「C8」~「C9」にも同様の数式が設定されるよう、相対参照の時と同様に、セル「C8」~「C9」にオートフィルしてみると、以下のような結果になります。

オートフィル結果

結果、セル「C8」は一見して問題ないように見えますが、セル「C9」では、本来「テーブル(アルミ)」という商品名を表示したいところ、『#N/A』の文字が表示されてしまいました。
『#N/A』は、本来セルに設定した数式がエラーとなった際に出る文字ですので、この式は正しくない式になります。

セル「C9」の数式が一体どうなっているのかを見直してみましょう。

なんで『#N/A』が表示されてしまったの?

さて、見直してみると、コピー元の式、コピー先の式は、以下の通りになっていました。

コピー元の式(セルC7)

=VLOOKUP(B7,I4:K9,2,0)

コピー先の式(セルC9)

=VLOOKUP(B9,I6:K11,2,0)

(確認1)コピー元の式の参照先である、商品コード「B7」は、下にオートフィルする事によって相対参照され、下の商品コードであるセル「B9」が参照されていましたので、こちらは問題ありません。

(2)次の参照先である右側の商品コード一覧表の表範囲「I4:K9」は、下にオートフィルすることによって相対参照され、設定範囲が「I6:K11」と下に動いている事が分かります。

つまり、セル「C9」の数式では、本来動いてはいけない表範囲の設定が、相対参照によって変化していた為、商品コードの文字『A02』(=セル「I5」)を、エクセルが見つけられずエラーとなった・・・という結果が見えてきました。
一括でその他のセルにも式を設定したいけど、式に使っている一部の参照セルは固定しておきたい・・・
こういった場合に有効となるのが、次に学ぶ「絶対参照」の設定です。

使い方例)「$」記号の入力/参照先を固定する

では、どうやって参照先が移動しないように固定するか、その方法を見ていきましょう。
下の表は、実際に絶対参照の設定をした式を入力した例です。

vlookuup2

 

絶対参照の設定方法
参照先を固定するには、固定したい列や行の前に「$」を表記します。

(1)セルC7にて、『 =VLOOKUP(B7, I4:K9 ,2,0)』の式を入力
(2)固定したい参照先である商品コード一覧表の範囲を示す「I4:K9」の行と列の前に、固定を表す「$」を入力する。
→ 「$I$4:$K$9」
(※「I4:K9」のアルファベット及び数字の前に「$」記号を追加する)

=VLOOKUP(B7,$I$4:$K$9,2,0)

 

オートフィル結果を確認する

では、書き直した式について、最初と同じように、オートフィルを利用しコピーしてみましょう。

vlookup16

 

セル「C9」の数式は、

=VLOOKUP(B9,$I$4:$K$9,2,0)

となっており、商品コード一覧を示すセル範囲「I4:K9」は固定されていて、動いていない事が分かります。
このように、「絶対参照」を利用することで、数式のコピーやオートフィル等による、参照しているセルや範囲の変化をさせず、固定させることが出来るのです。

 

範囲を固定する方法$について

「$」記号の意味・・・

セルの場所を表す列番号(A・B・C・・・)、あるいは、行番号(1・2・3)の前に追加することで対象の列や行が、コピーなどにより変化させない為の記号です。

img6

 

$固定のショートカット

参照するセルを固定する「$」記号を、効率よく数式に追加するために、キーボード上部の[F4]キーを活用してみましょう。

img3

 

では、実際に上で使用した同じ表を用いて、[F4]キーを活用した絶対参照の設定方法をしましょう。

vlookuup1

 

使い方例)F4キーを用いた参照設定①

方法① 式の入力中に設定する

式を入力中にファンクションキー(キーボード上部)を利用して設定します。
今回の場合、

(1)セルC7にて、『 =VLOOKUP(B7,I4:K9,2,0) 』と式を入力
(2)商品コード一覧表のセル範囲「I4:K9」をマウスで範囲選択
(3)[F4]キーを1回押す
(4)残りの式を入力する

以上で設定完了となります。
設定後、(2)で指定した範囲に「$」記号が追加されていることを数式バーから確認してください。

=VLOOKUP(B7,$I$4:$K$9,2,0)

方法② すでにある式を数式バーから修正する

すでに式が入力されている場合は、数式バー(画面上部)から修正が可能です。

数式バーとは・・・

エクセルの画面上部にある細長い欄で、選択したセルに入力されている数値や数式を表示する役割を持っています。
また、表示されているデータを修正する事も出来ます。

img4

今回の場合、

(1)数式の入力されているセルC7を選択する。
(2)数式バーにて「I4:K9」の文字をマウスで範囲選択。
(3)[F4]キーを1回押す。
(4)ENTERを押して式の修正を決定する。

以上で設定完了となります。
こちらでも方法①と同様に、数式に「$」記号が追加され、絶対参照が設定出来ます。

このように、オートフィルにより、式の参照セルや範囲が動いてはいけない時には絶対参照を利用するようにいたしましょう。

 

複合参照

複合参照とは・・・

「相対参照」と「絶対参照」を組み合わせた参照方式の事で、

列方向(横)には絶対参照=固定して、行方向(縦)には相対参照=固定しない
行方向(縦)には絶対参照=固定して、列方向(横)には相対参照=固定しない

といった、2種類の方法があります。
少し複雑な参照方法ですが、覚えておくとエクセルを使いこなす上ではとても便利な参照方法です。

ではどういった場合に、この「複合参照」が必要となるのか具体例を上げてみます。

複合参照の必要な例)

では、複合参照が必要になる事例を見てみましょう。下の表は、とある会社の社員給与一覧及び、交通費の合計を求める表です。
セルD11には、①「部署(B4:B7)の中から、営業(セルC11)の社員の給与(D4:D7)の合計」を求めるSUMIF関数が入力されています。(SUMIF関数の使い方はこちら
この式をオートフィルするだけで、『部署別合計』の表に部署ごとの給与、交通費が出力されるようにするにはどうしたらいいでしょうか。

まず、その際、部署が入力されている「B4:B7」の範囲と、給与合計の対象範囲である「D4:D7」は絶対参照で固定してみました。

=SUMIF($B$4:$B$7,C11,$D$4:$D$7)

sumif

オートフィル結果を確認する
このセルD11を下、及び右のセルにオートフィルした際、どのようになるか確認してみましょう。

sumif2

 

① 下にオートフィルした場合

セルD12の式は、

=SUMIF($B$4:$B$7,C12,$D$4:$D$7)

となり、正常にセルC12(企画)の社員のみの給与合計が表示されました。

②右にオートフィルした場合

セルE11の式は、

=SUMIF($B$4:$B$7,D11,$D$4:$D$7)

となって、表示された合計は、実際の合計とは違った結果となりました。

この時②の式の問題点を見てみましょう。

(1)どの部署の合計かを示すセル参照が、元の「C11」から右に動き「D11」となっています。
(2)オートフィル後の合計対象であるセル範囲は、交通費=「E4:E7」となるはずが、元のセル範囲である、給与=「D4:D7」が絶対参照により固定されている為、セルの参照範囲が変更されていません。

分かりやすくまとめると・・・

(1)部署の参照先は、行方向(縦)のみ変化出来るようにしなければいけない。
(2)合計の範囲は、列方向(横)のみ変化出来るようにしなければいけない。

以上の2点が必要なことが分かるでしょうか。

では、「複合参照」を利用して、この数式を修正するようにいたしましょう。

使い方例)F4キーを用いた参照設定②

sumif3

 

 

複合参照の設定方法

① 部署の参照先「C11」を、列方向(横)のみ固定とする→「$c11」

(1)数式バーから「C11」の文字を範囲選択
(2)[F4]キーを3回押す

=SUMIF($B$4:$B$7,$C11,$D$4:$D$7)

② 合計範囲の参照先「$D$4:$D$7」を、行方向(縦)のみ固定とする→「D$4:D$7」

(1)数式バーから「$D$4:$D$7」の文字を範囲選択
(2)[F4]キーを1回押す

=SUMIF($B$4:$B$7,$C11,D$4:D$7)

※[F4]キーを押す毎に、「$」記号の位置が変化した事に注意をしてみてください。

 

では、オートフィル結果を確認してみましょう。

オートフィル結果)

sumif4

 

右にオートフィルした場合は、「部署が営業の、交通費合計」、
下にオートフィルした場合は、「部署が企画の、給与合計」が、それぞれ表示されました。

 

POINT!

相対参照・絶対参照・複合参照は、[F4]キーを押す毎に切り替え出来るようになっています。

最後に、参照方法の切り替えと[F4]キーの関係について、以下の図でまとめてみましょう。

img5

今後、より複雑な表や数式を覚えていく中で、とても重要な役割を持つことになる為、何度か練習してみるといいかもしれませんね。

Rate article