仕事効率化

【エクセル】セルの参照を固定(列のみ・行のみ)する方法とは?

エクセルで数式をコピーしたら計算がおかしい…そんな経験はありませんか?

下図のC2セルには、静岡店の売上構成比を求めるために「=B2/B6」という数式が入力されています。
セルに式が入力されている
他の店舗の売上構成比も確認したいので、C2セルの数式をC5セルまでコピーしました。

ナル
ナル
静岡店の構成比はしっかりと計算ができているのに、C3セルからC5セルはエラー表示(#DIV/0!)になってしまいました


コピーした数式がエラー表示
これは、コピーした数式のセルの参照がズレてしまったことが原因

そこで本記事では、セルの参照を固定した状態で数式をコピーする方法と、相対参照・絶対参照の違いを初心者目線でお伝えします。

セルの参照は絶対参照で固定できる

セルの参照を絶対参照にすると数式を他のセルにコピーしても参照が絶対にズレなくなり、正しく計算ができるようになります。

また、絶対参照は行だけを固定する、または列だけを固定するという応用もできます。(後述致します)

VLOOKUPなどのエクセル関数では、セルの参照をコントロールすることが必要不可欠になりますので、この機会にぜひエクセルの参照を理解しましょう。

【エクセル】VLOOKUP関数の使い方とすぐにできる6つの応用技VLOOKUP関数はビジネスで大活躍の関数。しかし、初心者にはとっくきにくいと苦手意識を持っている方も多いです。そこでエクセル歴20年の筆者が分かりやすく解説。さらに新エクセル関数XLOOKUPについても紹介。...

スポンサーリンク


エクセルの2つのセル参照「相対参照」と「絶対参照」とは?

セルの参照には「相対参照」と「絶対参照」があります。

セルの参照①相対参照

エクセルでは、数式を他のセルにコピーすると、自動的に参照するセルが変化します。

これを「相対参照」と呼びます。

先ほどとは違う表で説明します。

E2セルに7月から9月の合計をSUM関数で求めました。
SUM関数
数式をE5セルまでコピーすると、それぞれの店舗の合計金額がE列に表示されました。
正しく計算できた
E列に入力されている数式を確認してみます。
数式を確認
数式のコピー元であるE2セル(静岡店)は「=SUM(B2:D2)」ですが、コピー先のE3セル(横浜店)は「=SUM(B3:D3)」、E4セル(新宿店)は「=SUM(B4:D4)」、というように、入力されたセルに合わせて参照が変化してます。

相対参照のおかげで1つ1つのセルにSUM関数を入力する必要はなく、E2セルの数式をコピーするだけで計算ができました。

相対参照はこのような場面では非常に便利

しかし、冒頭にお話ししたような表の場合、相対参照では正しく計算ができませんでした。

C2セルに入力されている「=B2/B6」をC3セルにコピーすると、
相対参照
相対参照で「=B3/B7」となってしまいます。
参照がズレる
C3に入る数式は、「=B3/B6」でなければなりません

これが、数式をコピーしたときに計算がおかしくなる理由です。

スポンサーリンク



セルの参照②絶対参照

相対参照に対して、絶対参照と呼ばれるものがあります。

絶対参照では、数式を他のセルにコピーしても参照が固定されてズレることがありません。

絶対参照はセルの参照に「$」が付くのが特徴です。
絶対参照は$が付く
イメージとしては、「$」がカギマークだと思うと分かりやすいかもしれません。

僕はセルの参照に「$」が付いていると、カギマーク=ロックされている、と考えるようにしています。
ロック
むずかしく考えなくても、「$」カギマークが付いているときは絶対参照

だから数式をコピーしても参照がズレないと覚えてくれればOKです。

相対参照と絶対参照を分かりやすく比較してみます。

A1セルに入力した文字をB列に表示しています。
相対参照の例
相対参照の場合、B1セルを他のセルにコピーすると当然参照がズレます。
相対参照をコピー
相対参照の数式
次に絶対参照で同じことをやってみます。

B1セルに入力された参照のA1を「$A$1」に変更します。
絶対参照の例
B1セルを他のセルにコピーしてみました。
絶対参照をコピー
絶対参照の数式
どのセルにコピーしても、参照先はA1セルになっています。

これが絶対参照。
セルの参照を完全に固定することが可能ですね。

スポンサーリンク


セルの参照を固定した数式をコピーする方法

本題に戻ります。
冒頭で例にした表で、絶対参照を使ってみます。

C2セルを選択して、数式を絶対参照に変更します。
相対参照のセルを選択
直接「B6」→「$B$6」と手入力で修正しても良いのですが、実はもっと簡単な方法があります。

セルの参照を固定したいB6の部分を選択して、キーボードのF4キーを1度押します。
F4キーを押すと絶対参照になる
これだけで、セルの参照が相対参照から絶対参照へ変更されます。
絶対参照に変更された

相対参照を絶対参照に変更したいときはキーボードのF4キーを押す

B2セルをB5セルまでコピーします。
絶対参照の式をコピー
相対参照では数式をコピーしてもエラー表示がされていましたが、今回はB6セルが絶対参照になっているためコピーした数式で問題なく計算できています。

C2セルからC5セルに入力された数式を確認してみます。
絶対参照の数式を確認
割り算の分子となるB2セルはコピーしたセルに応じてB3、B4…と変化をしていますが、分母となるB6セルはどのセルも同じで固定されているのが分かると思います。

このように、相対参照と絶対参照を使い分けることで、数式や関数を使ったときに正確に求めたい結果を計算することができます。
スポンサーリンク



セル参照で列のみ・行のみを固定したいときは複合参照

実際の作業の中では、完全にセルを固定したいわけではなく、列だけ固定したい、行のみを固定したいという場面もありますよね。

列のみ・行のみのセルの参照を固定したいケース

レンタル料金と日数の掛け算によってレンタル料金が計算される一覧表を例にします。
レンタル料金一覧表
まずはD3セルに「C3*D2」と入力してみました。
D3セルに式を入力
数式を相対参照のまま他のセルにコピーしてみます。
相対参照のままコピー
どう見ても正しい計算はできていませんよね。

では、D3セルの参照を絶対参照にして固定してみましょう。

数式を「=$C$3*$D$2」にしてみました。
絶対参照にしてコピー
すべてのセルに同じ料金が表示されてしまいました。

ナル
ナル
セル参照を完全に固定してるから当然ですよね…

このように、相対参照でダメだからと言って絶対参照にしても、それだけは上手くいかないこともあります。

この問題で重要なのは、列または行のみを固定する方法です。

D3セルに入力した「=C3*D2」という式を他のセルにコピーするとき、どんな風にセルを参照すれば正しく計算できるのかを考えてみましょう。

D3セルの「=C3*D2」を縦(列)方向にコピーするとしたら、C3セルは相対参照で変化させて、D2セルは絶対参照で固定すれば良いですよね。
列方向にコピーするなら
D3セルの「=C3*D2」を横(行)方向にコピーするとしたら、C3セルを絶対参照で固定して、D2セルは相対参照で変化させれば問題ありません。
行方向にコピーするなら
つまり、D3セルに入力された数式の「C3*D2」を次のようにすれば良いことになります。

C3セルは「C(列)は絶対参照」で「3(行)は相対参照」。

D2セルは「D(列)は相対参照」で「2(行)は絶対参照」。

スポンサーリンク


列のみ・行のみをセル参照する超簡単な方法

たとえば、A1セルを参照した状態でキーボードのF4キーを押すと、「A1」→「$A$1」となり絶対参照になります。

実は、ここからさらにF4キーを押すことで参照が変更されます。
F4キーを押すごとに変化する
慣れないとセル参照が今どの状態なのかが把握しにくいと思いますが、先に述べましたように「」マークを意識してみてください。

A1(相対参照)

$A$1(絶対参照)

A$1(行のみ固定)

$A1(列のみ固定)

このように、相対参照と絶対参照を組み合わせることを複合参照と呼びます。

複合参照により、列のみ、または行のみのセルの参照を固定することができます。

では、複合参照を使ってレンタル料金の一覧表を完成させてみましょう。

D3セルに「=$C3(列参照のみ固定)*D$2(行参照のみ固定)」と入力して、他のセルにコピー。
複合参照の入力
すべての箇所に正しく計算結果が表示できました。
複合参照をコピー
参考に、各セルに入力された計算式を載せておきますね。
複合参照の数式を確認
相対参照で変化しているところと、絶対参照で固定されているセル参照がありますね。

$」の位置に注意しながら眺めてみてください。

スポンサーリンク



まとめ

エクセル関数を使いこなすためには、相対参照絶対参照、そして複合参照の使い分けが必要不可欠です。

最初はどうすれば良いのか判断を迷うこともあると思いますが、ゆっくりと時間をかけて覚えていけば良いです。

大切なのはキーボードのF4キー。

エクセルのセルの参照で困ったときには、この記事を参考に考えてみてくださいね。
スポンサーリンク