Excel 2007 の相対参照と絶対参照をマスタする

　「C支店」の累計を計算してみよう。

相対参照の場合の数式

　「C支店」の累積を計算するために、C3セルには「=B3」と入力して、C4セルには、上のセルと左のセル加算する数式（「＝C3+B4」）を入力する。この方法だと「C4セル」をオートフィル機能を使ってコピーできる。確認をするために、「「数式」をセルに表示させる」に切り替える（図１）。また、「R1C1参照形式」に切り替えると、次のようにすべて同じ情報がコピーされていることが分かる（図２）。

=R[-1]C+RC[-1]

図1●「C支店」の累計を計算する １．「「数式」をセルに表示させる」に切り替える。 ２．C3セルに「=B3」を入力する。 ３．C4セルに「=C3+B4」する。

４．C4セルをオートフィルを使ってコピーができる。

図2●「R1C1参照形式」に切り替えると、２行目以降は同じ情報がコピーされていることがわかる（R[-1]とC[-1]の組み合わせに注目）。

SUM関数を使って累計を計算する

　今度は、SUM関数を使って累計を計算してみょう。このやり方だと数式は１つでよい。C3セルに「=SUM($B$3:B3)」と入力する。「$」の付いたセル座標は絶対参照と呼ばれるもので、「コピーしても座標を置き変えない」ことを表している。入力の仕方は、「●「絶対参照」の記入方法」を参照して下さい。
　あとは、「C3セル」をオートフィルでコピーすればよい。確認をするために、「「数式」をセルに表示させる」に切り替える（図３）。絶対参照で記入した部分は、変化していない。また、「R1C1参照形式」に切り替えると、次のようにすべて同じ情報がコピーされていることが分かる（図4）。

=SUM(R3C2:RC[-1]

図3●「C支店」の累計をSUM関数を使って計算する １．C3セルに「=SUM($B$3:B3)」を入力する。 ２．「C3セル」をオートフィルでコピーする。

３．「「数式」をセルに表示させる」に切り替える。 ４．絶対参照で記入された部分は座標が変化していない。

図4●「R1C1参照形式」に切り替えると、すべて同じ数式であることが分かる。

「絶対参照」の記入方法

　「＄」が付いたしてセル座標は「絶対参照」と呼ばれるもので、「コピーしても座標を置き変えない」ことを表している。「絶対参照」の記入方法は、セル参照の部分にセルポインタを置いて、F4キーを押す（図5、図6）。別なやり方としては、$記号をキーボードから入力しても構わない（図7）。ここで、注意することは、F4キーを押す度に「$」が切り替わることです。
「絶対参照→行のみ絶対参照→列のみ絶対参照→相対参照→絶対参照→・・・・」といった順番のトグルになっている（図8）。

図５●F4キーを使う方法 １．C3セルに「=SUM(B3」と入力したところで、F4キーを押す。 ２．「$」が付く。続いて「：1B3)」と入力をする。

図６●数式バーで、F4キーを使う方法 １．C3セルに「=SUM(B3：B3)」と入力して、数式バーで、「$」を付けたい セル参照部分をカーソルをポイントして、F4キーを押す。 ２．「$」が付く。

図７●キボードの「$」キーを使う １．C3セルに「=SUM($B$3：B3)」とキーボードから直接入力する。

図８●「F4」キーについて F4キーは押す度に「$」付く部分が変わってくるので、注意が必要です。

　　　　（リンク元に戻る）　　　　Top へ

複合参照と応用問題

　F4キーを何回か押す度に、「$」の付く部分が変わってきた。と言うことは、「絶対参照」には、「行方向のみ絶対参照」や「列方向のみ絶対参照」というセル参照も作成できる。例えば「A$1」,「$A1」のように記入する。これを「複合参照」と呼ぶ。

複合参照の活用

　３つの支店について、４月からの累計を計算する。G43セルに数式「=SUM(B$4:B4)」を入力する。範囲の先頭セルは「行方向のみ絶対参照」となっているので、下方向にコピーしても、右方向にコピーしても、適切なセルを参照する数式を作成することができる。　下方向と右方向をまとめてコピーする場合は、G4セルを選択して、「コピー」の操作をする。次に、範囲選択（G5セルからI15セル）をして、「貼り付け」の操作をする（図1）。「絶対参照」の記入方法は、こちらを参照願います。

図1●３つの支店の累計を計算する １．G4セルに数式「=SUM(B$4:B4)」を入力する。 ２．数式をコピーする場合は、C3セルを選択して、「コピー」の操作をする。 ３．範囲選択（G5セルからI15セル）をして、「貼り付け」の操作をする。

数式の入力と同時にコピーをする方法

　最初に範囲選択（G4セルからI15セル）をして、数式バーに数式「=SUM(B$4:B4)」を入力する。そして、「Ctrl」＋「Enter」キーを押して入力を完了すればよい（図2)。

図２●数式の入力と同時にコピーをする方法 １．先に範囲選択（G4セルからI15セル)をする。 ２．数式バーに数式「=SUM(B$4:B4)」を入力する。

３．「Ctrl」+「Enter」キーを押して、入力を完了させると、同時にコピーされる。

応用問題

　さて、最後に応用問題として、借入金の月返済額の一覧表を作成する。図3の左上にローンの返済額を計算している領域がある。300万円を年4％の金利で５年間借りたときの月返済額は、PTM関数で計算して55,250円である。その右下には、作りかけの表がある。列方向は「３年」から「10年」までの返済期間が、行方向は「1.00％」から「7.00％」までの金利が、項目名として設定されている。これらの組み合わせの月返済額を計算して、一覧表を作成しているところである。ここで、D9セルに数式を設定し、その数式をコピーして全セルに適切な結果が表示されるようにしたい。さて、どんな数式を作成したらよいだろうか。なお、借入金額は、C2セルに入力されているデータを参照する。
　相対参照と絶対参照をきちんと理解していれば、作業はとても簡単です。D9セルに次の様な数式を設定すればよい。「絶対参照」の記入方法は、こちらを参照願います。

=PMT($C9/12,D$8*12,-$C$2)

図３●借入金の月返済額の一覧を作成する １．C2〜C4セルに借入金額、借乳期間、金利が設定されている。 ２．C5セルに月返済額を求める場合は、数式「=PMT(C4/12,C3*12,-C2)」となる。月返済額を計算するために、金利と期間は月単位に変換している。

３．D9:K21を範囲選択する。 ４．数式を作成する。金利は、C9:C21で「列のみ絶対参照」、期間はD8:K8で「行のみ絶対参照」、借入金額は、C2で固定なので「絶対参照」で設定する。 数式は「PMT($C9/12,D$8*12,-$C$2)」となる。 ５．数式ができたならば、数式バーに入力して、「Ctrl+Enter」キーで入力を完了させる。 ６．各セルに適切な数式が設定されて、月返済額の一覧表が作成できる。

　　　　　　　　　　　このページのTop へ