エクセル(Excel)の関数をコピーして誤った値が表示されることがありませんか？今回は関数をコピーすると関数が崩れてしまったり誤った値が表示する原因について紹介します。
chick関数をコピーすると全然違う値が表示されてしまって…毎回自分で関数を入力する必要があるのかな？
rabbitたぶんそれは絶対参照になっていないからだよ
chick絶対参照？
rabbitエクセルには絶対参照と相対参照があるんだ～使い方簡単だから図解で説明するね❗
エクセルの関数をコピーして便利に使うためには絶対参照・相対参照を理解する必要があります。関数の操作例を含めて解説します。
- 相対参照：コピー先に合わせて参照元を変更する
- 絶対参照：コピー先に合わせて参照元を変更しない
既定では、セル参照は相対参照です。 たとえば、セル C2 からセル A2 を参照する場合、実際には、2 列左 (C – A) で同じ行 (2) にあるセルを参照しています。 相対セル参照が含まれる数式は、セルからセルにコピーすると変更されます。 たとえば、数式 =A2+B2 をセル C2 から C3 へコピーすると、C3 の数式の参照は、1 行下に調整され =A3+B3 になります。
相対参照と絶対参照を切り替える｜Microsoft
Youtubeでも相対参照と絶対参照について解説しています。よろしければご覧ください。
関数をコピーする時に注意しよう｜相対参照
関数をコピーする時に覚えておきたいのが、相対参照と絶対参照です。相対参照はデフォルトで設定されているため規則性のある値の数式をコピーしても正しい値が表示されます。絶対参照は例えば消費税の計算などで固定の値があってそのセルの値は変更したくない場合にセルの固定をする必要があります。
それではデフォルトで設定されている相対参照について見ていきましょう。
相対参照
- 規則性のある単純な関数のコピーなどは相対参照で問題ありません。
- ※(B3,C3,D3)から(B4,C4,D4)へと計算式が並んでいる場合など
- セルのコピーはセルの端に表示される「+」をドラッグしながらのコピーで問題ありません。
- 関数をコピーしても数式がずれていないことを確認します。
- 横へのコピーも同様でセル端の「+」をドラッグしながらコピーします。
- セルのコピーで関数が正しくコピーされたことを確認します。
rabbit相対参照はデフォルトの設定だよ～難しくないよ！
関数をコピーする時に注意しよう｜絶対参照
絶対参照の使用例はセルの「A1」の「1」をコピー時に変更しないようにするには「A$1」とします。また「A」を変更したくない場合は「$A1」とします。変更したくない値の前に「$」を設置します。また「$」は「F4」キーを押すことで変更可能なため下記の図を参考に変更を試してみてください。
| 参照の型 | 固定方法 | 説明 |
|---|---|---|
| $A$1 | [F4]キーを1回押す | 「行」と「列」の絶対参照 |
| A$1 | [F4]キーを2回押す | 「行」の絶対参照 |
| $A1 | [F4]キーを3回押す | 「列」の絶対参照 |
| A1 | [F4]キーを4回押して解除 | [$]なし相対参照 |
それでは図解で説明します。
絶対参照
- 単純な合計値ではなく、固定セルに記載の消費税を求める計算式を作成しました。
- 個数と価格の計算式で消費税を求める計算式です。ROUNDは四捨五入で使用します。PRODUCTは掛け算の時に使用します。
- ※ROUNDとPRODUCTについては掛け算の時にも使用してますので下記のブログを参考ください。
- セルの端にある「+」を使ってコピーします。
- 関数がコピーされましたが、価格表示がおかしくなってます。
- セル「E11」の数式を確認すると「=ROUND(PRODUCT(C11,D11,F8),0)」となってます。消費税計算のため「F8」は「F2」とする必要があります。
- 関数の計算式がずれてしまったのでコピー元を「=ROUND(PRODUCT(C5,D5,F$2),0)」として消費税の値が動かないように行の絶対参照「F$2」としています。
- セル「E11」の数式を確認すると「=ROUND(PRODUCT(C11,D11,F$2),0)」となり正常に計算されました。
- セル「F11」の消費税も「=ROUND(PRODUCT(C11,D11,E$2),0)」として正しく表示されてます。
- セル「I7」の横へコピーを行う場合は「=ROUND(PRODUCT(I5,I6,$F2),0)」とします。
- 「$F2」で列の絶対参照とします。
- 絶対参照で正常に計算されました。
相対参照と絶対参照の違いは「参照元を変更する」設定にするか「参照元を変更しない」設定にするかの違いになります。
- 相対参照：コピー先に合わせて参照元を変更する
- 絶対参照：コピー先に合わせて参照元を変更しない
rabbitイメージだけでは難しいと思うので実際に関数を組んで操作してみることをおすすめします！
コメント