- ホーム >
- 基本操作 >
- エクセル（Excel）編 >
数式の複写（1）～ 絶対参照と相対参照 ～
Excelの基本機能の中で最も便利で多様すると思われる機能が「複写」になると思います。セルの複写・結合・挿入 等でも解説しましたが、ドラッグで簡単にセルを複写することができます。
もちろん、前項で作成したグラフも簡単に複写できますし、数式を複写することで値も自動的に計算してくれます。
すこし、おさらいしてみましょう。下図のようにセルの右下の「＋」マークをマウスでドラッグ&ドロップするとセルを複写することができます。
セルに入力した数式は、隣のセルに複写されても、数式が参照するセルは複写にともなって移動するため、値も再計算されます。
つまり、上図のC列「1,500+980+780」の数式「SUM（C3:C5）」は、右列のD列に複写されると、「2,300+750+790」の数式「SUM（D3:D5）」に自動的に調整されるわけです。これは大変便利な機能で、Excelの代名詞でもあります。
このように、複写にともなって参照先の列や行が変化する参照の仕方を、
相対参照（そうたいさんしょう）
と言います。相対参照によって、例えば上図のように月ごとの合計を表にあらわす場合など、合計を求める式を一度記述すれば、それを月分だけマウスで複写すれば、自動的に合計値が計算されて表が完成します。また、中の値を再入力すれば、ともなって合計値も再計算されるので、大変便利です。
また、セルの複写・結合・挿入 でも解説のとおり、数字の「1」を複写すると、自動的に「2、3、4・・・」と増加したり、「月」という文字を複写すると、自動的に「火、水、木・・・」と曜日を調整して複写してくれるという機能も備えています。この機能を、
オートフィル
と言います。連番や連続日、曜日を入力する場合などは、オートフィル機能によって簡単に複写して入力することができます。（逆にオートフィル機能が不要なときは「Ctrl」キー押したままドラッグすると、その値のまま複写されます）
さて、このように複写にともなって自動的に参照先や値が調整されるというのは非常に便利な機能ですが、逆に参照先を固定したまま、数式を変化させたくない場合もあります。
例えば、ある合計値の構成比を複写して計算するような場合などがありますが、このような場合には、参照先のセルが変化してしまうと正しく計算することができません。そのため、相対参照ではなく参照する行や列を固定する必要があります。
相対参照に対し、セルもしくは行、列を固定する参照の仕方を、
絶対参照（ぜったいさんしょう）
と言います。絶対参照によって、いくら複写しても参照先を変化させないようにすることができます。絶対参照は、3つの区分に分けることができます。
まず、行と列ともにその「セル」自体を固定してしまう「絶対参照」、「行」のみを固定する「絶対行参照」、「列」のみを固定する「絶対列参照」の3つです。
絶対行参照と絶対列参照は、行か列の一方が絶対参照で、もう一方が相対参照なので、「複合参照」と言う場合もあります。
それでは、言葉だけではイメージしにくいので、同様に例題を使って、それぞれの参照方法の違いを学習していきましょう。
| 例題4 | 「例題4の1」と「例題4の2」の2シートがあります。 |
|---|
まず、「例題4の1」シートを開いてください。下図のように数式を記述してありますので、「B5」セルに隣の列の「A5」と同じ「=B3+B4」の式を記述します。
そしてEnterで、B5セルには「11」の値が表示されます。この数式はこれまでと同様に「相対参照」の式になります。右列に複写すると、ともなって参照セルも変化します。つまり、上2つのセルの合計となっています。
それでは、次に「B6」セルに絶対参照の数式を記述してみましょう。絶対参照とは前述のとおり「固定」するということです。絶対参照では、固定されていることを表すために「$」マークを記述します。この「$」マークが付いている行または列は、複写しても固定されたまま動きません。
例題のように「B3」セルを絶対参照する場合、列は「B」で行は「3」になります。つまり、「B」と「3」の両方、もしくはどちらか一方に対して絶対参照（固定する）ことができ、3パターンの参照方法を記述することができます。
1）$B$3、2）$B3、3）B$3
の3パターンです。絶対参照の「$」マークを記述するには、直接記述してもかまいませんが、該当のセルをクリックした後、「F4」キーで記述することができます。
例えば、式の「B3」の部分にカーソルを合わせて、「F4」キーを一度押すと「$B$3」、もう一度押すと「B$3」、もう一度押すと「$B3」、さらにもう一度押すと「B3」となります。
つまり、「$B$3（絶対参照）→ B$3（絶対行参照）→ $B3（絶対列参照）→ B3（相対参照）」の順に記述することができるわけです。
では、3パターンの違いをみていきましょう。1）は「B」に対しても「3」に対しても「$」マークにより固定されています。つまり、縦に複写しても横に複写しても「B3」から参照が外れることはありません。
では、「B6」セルに「A6」と同様に「＝$B$3+B4」と記述して、同様に横に複写してみましょう。この式は、「B3」セルは固定したまま、「4」行の値を相対参照させる式になります。下図のように「5」行目とは異なった計算結果が表示されます。
つまり、B3セルの「1」が固定されたまま、「4」行の数字のみが相対的に変化して足し算されているわけです。（1+10、1+11、1+12、1+13・・・）
次に、2）と3）の場合はどちらか一方が相対参照となっており、ある方向に複写した場合は相対参照となり、ともなって行または列が変化していきます。
2）の「$B3」場合、「B」が「$」マークによって固定されているため、「列」が固定されたことになります。つまり、列が固定されたということは、横方向に複写した場合は列が固定されているため、セルは変化しません。
では、「B7」セルに「A7」と同様に「＝$B3+B4」と記述して、同様に横に複写してみましょう。この式では、「B3」セルの列を固定したまま、「4」行の値を相対参照させる式になります。すなわち、列が固定されているため、値は上行の「6」行と同じ値になります。
最後に、3）の「B$3」場合、「3」が「$」マークによって固定されているため、「行」が固定されたことになります。つまり、行が固定されたということは、縦方向に複写した場合は行が固定されているため、セルは変化しません。
では、「B8」セルに「A8」と同様に「＝B$3+B4」と記述して、同様に横に複写してみましょう。この式では、「B3」セルの行を固定したまま、「4」行の値を相対参照させる式になります。
すなわち、行は固定されているものの、横方向に複写するため列は相対的に変化します。したがって、値は上行の「5」行と同じ値になります。
では、次に縦方向の複写を行ってみましょう。下図のとおり、「N4」セルに上の行の「N3」と同じ「=$M$3+L3」の式を記述して、縦方向に複写します。
この式は、「M3」セルは固定したまま、「L」列の値を相対参照させる式になります。つまり、M3セルの「120」が固定されたまま、「L」列の数字のみが相対的に変化して足し算されているわけです。（120+110、120+20、120+130、120+21・・・）
では、「O4」セルに「O3」と同様に「＝M$3+L3」と記述して、同様に縦に複写してみましょう。この式では、「M3」セルの行を固定したまま、「L」列の値を相対参照させる式になります。縦方向に複写した場合は行が固定されているため、セルは変化しません。すなわち、値はN列と同じになります。
次に、「P4」セルに「P3」と同様に「＝$M3+L3」と記述して、同様に縦に複写してみましょう。この式では、「M3」セルの列を固定したまま、「L」列の値を相対参照させる式になります。
すなわち、列は固定されているものの、縦方向に複写するため行は相対的に変化します。したがって、相対参照による式と同じ値になります。（120+110、21+20、141+130、22+21・・・）
では、これらを踏まえて、「例題4の2」シートの表を完成させてみましょう。このシートを埋められる程度に理解できていれば、相対参照と絶対参照についてはほぼ完璧に理解し、使い分けることができていると考えてよいと思います。
まずは、これまでのおさらいとして「H列」に合計値を求める式を入力し、縦方向に複写してH列に合計値を算出してみましょう。
入力する式は、「H2」セルの合計値は「原価×数量」ですので「=B2*G2」となります。そして、セルを相対参照で、縦方向に複写します。
上図のとおり値が算出されたでしょうか？この合計値は値の多い順に上から並んでいます。隣の「I列」に累計、つまり、該当する行までの合計値を算出していくために、このようにあえて並び替えています。
累計の計算式は、「I2」セルから順に、「=H2、H2+H3、H2+H3+H4、H2+H3+H4+H5・・・」と足し算するセルが増えていきます。全てのセルにひとつずつ「=H2+H3+H4」のように直接記述しても計算できますが、大変面倒です。簡単に複写して値を算出するには、どのような式を記述すればよいでしょうか？
まず、もうおわかりのとおり、相対参照の式を記述してしまうと、複写のたびにセルがずれてしまうのでダメです。絶対参照によって「H2」セルを固定しておかなければなりません。
それを踏まえて、ここでは、SUM関数とAVERAGE関数 で解説した「SUM関数」を使って計算式を記述してみましょう。「I2」セルには、隣の「H2」セルの値「1,287,500」がそのまま入りますので、「I2」セルには「=H2」もしくは「=SUM（H2）」を記述します。
次に「I3」セルには「=SUM（H2:H3）」を記述します。ただし、「I3」セルはこのままで問題ありませんが、「I4」セルに複写した場合、「=SUM（H3:H4）」と参照セルが変化してしまいます。「H2」セルは固定しなければなりませんので、絶対参照の式を記述します。
下図のように、「H2」の部分にカーソルを持っていき、「F4」キーで「$」マークを記述します。この場合、行も列も絶対参照で固定してもかまいませんし、「H$2」のように「行」のみの絶対行参照でもかまいません。
この式を縦方向に複写します。すると、「H2」セルからその行までの合計が累計として算出されます。この列の値は、「I12」セルの値がH列の値の合計値となるわけです。
次に、「J列」に構成比を算出します。構成比は、隣のセル（例えば「J2」セルなら「I2」セル）の値が、合計値に占める割合がどのくらいなのかをあらわします。つまり、式は「該当セルの値÷合計値」で計算することができます。
したがって、「J2」セルには「=I2/I12」を記述します。ただし、累計と同様に「J2」セルはこのままで問題ありませんが、「J3」セルに複写した場合、「=I3/I13」と参照セルが変化してしまい、何もないセルを参照してしまいます。「I12」セルは固定しなければなりませんので、絶対参照の式を記述します。
同様に式の「I12」の部分にカーソルを持っていき、「F4」キーで「$」マークを記述します。この場合も、行も列も絶対参照で固定してもかまいませんし、「I$12」のように「行」のみの絶対行参照でもかまいません。
この式を縦方向に複写します。すると、その行の累計値が全体に占める割合が構成比として算出されます。つまりこの表では、商品にかかる金額を多い順に並べ、それらの商品は全体のどれくらいの割合を占めているのかをあらわしています。（無添加ローションだけで全体の61％を占めています）
最後に、「C、D、E、F」列を埋めてみましょう。、ここに記述する式はどういうものかというと、「B」列の原価に「C1～F1」の数字をかけた式を記述します。
つまり、「C2」であれば「原価×1.03」で「=B2*C1」となり、「D2」セルであれば「=B2*D1」となります。要するに3％、5％、10％、15％増しの金額を算出したいわけです。
では、この「C2」セルを一気に「F12」まで複写した時に、正しく計算されるようにするにはどのように記述すればよいでしょうか？
この場合は、縦の複写と横の複写の両方を考慮する必要があります。まずは、「C2」セルを横方向に複写した場合を考えてみましょう。
原価の「B2」セルは固定し、かける方の「C1、D1、E1、F1」セルは相対参照させなければなりません。その場合は、「=$B$2*C1」と記述すれば、横方向は複写しても正しく計算されます。
しかし、縦方向に複写した場合を考えると、「=$B$2*C1」の式では、「C3」セルに複写した場合、「=$B$2*C2」の式になってしまい、「51,500×53,045」になってしまいます。「C3」セルでは「=B3*C1」の式でなければなりません。
したがって、「B2」セルは完全に絶対参照してしまうとダメで、「列」のみの絶対列参照でなければなりません。そして、かける方の「C1、D1、E1、F1」セルについても「行が」ずれてはダメなので、絶対行参照にする必要があります。
すなわち、「C2」セルに記述する式は「=絶対列参照×絶対行参照」の式となります。下図のとおり「=$B2*C$1」と記述すると、縦・横どちらに複写しても正しく計算される式となります。
どうでしょうか？簡単に言ってしまうと、
横に複写する時は「絶対列参照」、縦に複写する時は「絶対行参照」
という公式がある程度当てはまると思います。一度覚えて理解してしまうともう迷うことはありません。何度か練習してみてください。
では、下記の練習課題より、絶対参照と相対参照、複合参照について復習してみましょう。
| 課題3 | 絶対参照（複合参照）の練習 |
|---|
本項の練習課題についても、特に解説はありませんので、これができたら次項へすすみましょう。
更新履歴
- 2011年4月21日
- ページを公開。
- 2017年12月29日
- ページをSSL化によりHTTPSに対応。
参考文献・ウェブサイト
当ページの作成にあたり、以下の文献およびウェブサイトを参考にさせていただきました。
- 文献
- なし
- ウェブサイト
- なし
- 管理人のつぶやき
- Twitterのフォローお願いします！