初級

Excelで
数式や計算式コピペ時の
セル番地のズレを固定
(絶対参照&相対参照)


(約文字)

2015-05-19 2018-07-25

Excelで参照セルを固定する方法を解説します。

無意識に他と比較してしまうのは疲れる生き方だなぁと思う今日この頃... この記事は、エクセルで「数式や計算式をコピーするとセル番地がズレる...」「セル番地を固定する方法は?」「「$」記号ってどう使う?」と お悩みの初心者さんに向けた「絶対参照」や「相対参照」に関する、詳しく、やさしい解説です。

1.セルがズレる原因はデフォルトが「相対参照」だから

エクセルで、セルに入力されている数式や計算式を、他のセルにコピーすると、参照先のセル番地ズレます。 これが正常・デフォルトでエクセルが壊れたり、バグが発生したり、おかしくなったわけではありません。

関数をコピーするとズレるのは「相対参照」がデフォルトだから

この自動でズレることを、「相対参照」と言います。これは関数や数式が入っているセルを基準として、参照先を"相対的"にズラす機能です。 エクセルが自動でズラしてくれるので人間がしなければならない作業を軽減してくれるのでと~っても便利なんです。この相対参照がエクセルの初期設定(デフォルト)です。

同じ数式や計算式を行方向(縦↓)や列方向(横→)に「ダーッ」とコピーしたいことありますよね。でも計算対象のセルはひとつづズレてほしい。こんな時に便利なのが「相対参照」です。 もし相対参照の機能がなかったら、数百とか数千の関数や数式をひとつひとつズラすのは、時間がかかって正直気が狂いますw

一方、「消費税」や「なんらかの係数」を一か所に設定して、各関数や数式から参照しておいて変更時(例えば、消費税を8%から10%に変更したいとき)に一括で対応できるように、参照先のセルを固定したい場合もあります。 この解決方法が「絶対参照」です(「相対」対し「絶対」)。

2.$記号で「絶対参照」にすればズレない

数式や計算式をコピーや移動しても、参照先のセルをズレなくする(固定する)方法は、数式や計算式で指定しているセル番地に「$」を付けることで、強制的にセル番地を固定させることが出来ます。

関数をコピーしても参照セルのズレを起こさない「絶対参照」

3.$記号による「絶対参照」の指定方法

絶対参照には「行のみ固定」、「列のみ固定」、「行も列も固定」と3種類あります。

$記号」を付ける位置で「固定する(絶対参照)」「固定しない(相対参照)」を指定できます。 「$」は半角です。($の色は説明の為につけているだけです。)

$の場所ですが、参照先セルが「A2」だった場合の指定方法を以下で説明します。

パターン 指定 列(この場合A列) 行(この場合2行目)
1 A2 ズレる(相対参照) ズレる(相対参照)
2 $A$2 固定(絶対参照) 固定(絶対参照)
3 $A2 固定(絶対参照) ズレる(相対参照)
4 A$2 ズレる(相対参照) 固定(絶対参照)

4.$記号の入力を簡単にする(絶対参照のショートカット)

絶対参照を設定する場合、初めのうちは「$」をどこに付けるかで試行錯誤しますが、その時「$」の場所を左右に移動するのが面倒ですが、$の位置を動かしてくれるショートカットキーがあります。

やり方は絶対参照したいセル番地にカーソル(点滅してる縦棒)を移動し、キーボードの【F4】キーを押すと$が入力され、さらにF4を押すと$の位置が移動します。

参照を切り替えるショートカットキー

「A2」→ 「$A$2」→ 「A$2」→ 「$A2」→ 「A2」と【F4】キーを4回で一周して元に戻ります。

※【F4】キーを押す前のカーソル位置は「A2」「A2」「A2」と、セル番地の「直ぐ前」、「真ん中」、「すぐ後ろ」のどこでもOKです。

5.「相対参照」と「絶対参照」で参照先を自在に変える

「相対参照」と「絶対参照」を使いこなせれば、数式や計算式を大量にコピペしても、状況により参照先を自在にコントロールできます。

また、数式内に複数セル番地がある場合、個別に「相対参照」と「絶対参照」を指定することが可能です。例えば、

=AVERAGE($A$1:F$10)

この様に、A1セルは列も行も絶対参照、F10セルは列は相対参照、行は絶対参照のような指定もも可能です。

6.「相対参照」からひとつづつ「絶対参照」へ設定するのが近道

数式を入力しながら「絶対参照」or「相対参照」を決めるのはハードルが高いので、とりあえず「相対参照($なし)」で入力を確定し、数式をコピペしながらズレてほしくない場合は「絶対参照($あり)」に指定すると、間違いなく設定が出来ます。 この手順は以下のような流れになります。

  • 手順1.セル番地を相対参照で入力
  • 手順2.関数や数式に問題がないか確認
  • 手順3.隣のセルに関数や数式をコピー
  • 手順4.元のセルに入った関数や数式で固定したいセル番地を決定
  • 手順5.セル番地を$で絶対参照に設定

エクセル20年以上使っていますが、今でも上記の流れで数式を指定しています。

この記事は以上です。最後までご覧頂き、ありがとうございました。

シェア

\ Let's Share !! /

この記事が参考になりましたら共有をお願い致します。

コラム【009】 コラム

使いこなせたらカッコいいと思うパソコンソフト-ベスト5-

  • 調査:フレッシャーズ
  • 時期:2015年5月
  • 対象:社会人男女453人
  • 方式:インターネットログイン式

第1位:Excel(96人)表計算ソフト

  • 仕事で最も使うので。使いこなせると頭がよく見える(♂/20代/電機)
  • 使いこなせている人は多くはないと感じるから(♂/30代/食品飲料)
  • メジャーソフトなので使いこなせば誰もが凄さに気づく(♂/40代/自動車)

第2位:Illustrator(86人)ベクターイメージ編集ソフト

  • 他社にイメージを伝えるのが上手くできると思う(♂/20代/機械機器)
  • 操作が複雑そうだから、使いこなせたらカッコいい(♀/20代/情報IT)
  • クリエーターっぽいから(♂/30代/電機)

第3位:Photoshop(71人)ラスターイメージ編集ソフト

  • 画像を自由自在に加工している姿はカッコいいと思う(♀/20代/食品飲料)
  • すごく立派な広告のようなチラシが作れそうだから(♀/30代/食品飲料)
  • ロゴや画像を綺麗に見せれば、仕事が上手くいくので(♂/50台以上/建設土木)

第4位:PowerPoint(67人)プレゼンソフト

  • できるビジネスマンみたいでカッコいいので(♂/20代/情報・IT)
  • パワポでプレゼンしている人を見るとスゴイと思う(♀/40代/情報IT)
  • 見栄えの良いプレゼンデータが作れたらいいので(♂/50台以上/情報IT)

第5位:Access(45人)データベースソフト

  • 分析・解析する技術は何かと重宝するので(♀/30代/アパレル繊維)
  • 上司が使いこなせていてスゴイとおもったので(♀/30代/食品飲料)
  • 高度なスキルが必要だと思うので(♀/30代/学校教育)

私の場合、Accessは必要ないので全く使いませんが、他は公私ともに必要なので、必然的に使いこなせるレベルにあると思います。でも周りから「カッコイイ!」と言われたことはありませんねw

同じカテゴリの記事