エクセルの関数を固定してコピーしよう|相対参照と絶対参照

エクセル(Excel)の関数をコピーして誤った値が表示されることがありませんか?今回は関数をコピーすると関数が崩れてしまったり誤った値が表示する原因について紹介します。

chick

関数をコピーすると全然違う値が表示されてしまって…毎回自分で関数を入力する必要があるのかな?

rabbit

たぶんそれは絶対参照になっていないからだよ

chick

絶対参照?

rabbit

エクセルには絶対参照と相対参照があるんだ~使い方簡単だから図解で説明するね❗

エクセルの関数をコピーして便利に使うためには絶対参照相対参照を理解する必要があります。関数の操作例を含めて解説します。

  • 相対参照:コピー先に合わせて参照元を変更する
  • 絶対参照:コピー先に合わせて参照元を変更しない

既定では、セル参照は相対参照です。 たとえば、セル C2 からセル A2 を参照する場合、実際には、2 列左 (C – A) で同じ行 (2) にあるセルを参照しています。 相対セル参照が含まれる数式は、セルからセルにコピーすると変更されます。 たとえば、数式 =A2+B2 をセル C2 から C3 へコピーすると、C3 の数式の参照は、1 行下に調整され =A3+B3 になります。

相対参照と絶対参照を切り替える|Microsoft

Youtubeでも相対参照と絶対参照について解説しています。よろしければご覧ください。

目次

関数をコピーする時に注意しよう|相対参照

関数をコピーする時に覚えておきたいのが、相対参照と絶対参照です。相対参照はデフォルトで設定されているため規則性のある値の数式をコピーしても正しい値が表示されます。絶対参照は例えば消費税の計算などで固定の値があってそのセルの値は変更したくない場合にセルの固定をする必要があります。

それではデフォルトで設定されている相対参照について見ていきましょう。

相対参照

STEP
単純な関数
  1. 規則性のある単純な関数のコピーなどは相対参照で問題ありません。
  2. ※(B3,C3,D3)から(B4,C4,D4)へと計算式が並んでいる場合など
STEP
セルのコピー
  1. セルのコピーはセルの端に表示される「+」をドラッグしながらのコピーで問題ありません。
STEP
スポンサーリンク
STEP
1.セルのコピーの確認
  1. 関数をコピーしても数式がずれていないことを確認します。
STEP
横にセルのコピー
  1. 横へのコピーも同様でセル端の「+」をドラッグしながらコピーします。
STEP
2.セルのコピーの確認
  1. セルのコピーで関数が正しくコピーされたことを確認します。
rabbit

相対参照はデフォルトの設定だよ~難しくないよ!

関数をコピーする時に注意しよう|絶対参照

絶対参照の使用例はセルの「A1」の「1」をコピー時に変更しないようにするには「A$1」とします。また「A」を変更したくない場合は「$A1」とします。変更したくない値の前に「$」を設置します。また「$」は「F4」キーを押すことで変更可能なため下記の図を参考に変更を試してみてください。

スクロールできます
参照の型固定方法説明
$A$1[F4]キーを1回押す」と「」の絶対参照
A$1[F4]キーを2回押す」の絶対参照
$A1[F4]キーを3回押す」の絶対参照
A1[F4]キーを4回押して解除[$]なし相対参照

それでは図解で説明します。

絶対参照

STEP
複雑な関数
  1. 単純な合計値ではなく、固定セルに記載の消費税を求める計算式を作成しました。
STEP
関数の意味
  1. 個数と価格の計算式で消費税を求める計算式です。ROUNDは四捨五入で使用します。PRODUCTは掛け算の時に使用します。
  2. ※ROUNDとPRODUCTについては掛け算の時にも使用してますので下記のブログを参考ください。
STEP
関数のコピー
  1. セルの端にある「+」を使ってコピーします。
STEP
関数の確認
  1. 関数がコピーされましたが、価格表示がおかしくなってます。
STEP
スポンサーリンク
STEP
コピーした関数の確認
  1. セル「E11」の数式を確認すると「=ROUND(PRODUCT(C11,D11,F8),0)」となってます。消費税計算のため「F8」は「F2」とする必要があります。
STEP
絶対参照
  1. 関数の計算式がずれてしまったのでコピー元を「=ROUND(PRODUCT(C5,D5,F$2),0)」として消費税の値が動かないように行の絶対参照「F$2」としています。
STEP
コピーした絶対参照の確認
  1. セル「E11」の数式を確認すると「=ROUND(PRODUCT(C11,D11,F$2),0)」となり正常に計算されました。
STEP
消費税の絶対参照
  1. セル「F11」の消費税も「=ROUND(PRODUCT(C11,D11,E$2),0)」として正しく表示されてます。
STEP
絶対参照の横へのコピー
  1. セル「I7」の横へコピーを行う場合は「=ROUND(PRODUCT(I5,I6,$F2),0)」とします。
  2. 「$F2」で列の絶対参照とします。
STEP
横へのコピーの確認
  1. 絶対参照で正常に計算されました。

相対参照と絶対参照の違いは「参照元を変更する」設定にするか「参照元を変更しない」設定にするかの違いになります。

  • 相対参照:コピー先に合わせて参照元を変更する
  • 絶対参照:コピー先に合わせて参照元を変更しない
rabbit

イメージだけでは難しいと思うので実際に関数を組んで操作してみることをおすすめします!

MOS Excel 365&2019 Expert対策テキスト&問題集 (よくわかるマスター)

MOS Excel 365&2019 Expert対策テキスト&問題集 (よくわかるマスター)

富士通エフ・オー・エム
3,410円(02/04 19:07時点)
Amazonの情報を掲載しています
  • URLをコピーしました!
目次