この記事ではエクセルで計算式コピーでずれる（数式）原因と対策を解説していきます。
エクセルの計算式コピーで数式がずれる主な原因として、「相対参照」と「絶対参照」が正しく使われていないエースが多いです。
コピーした際に参照先が変わらないのが、「絶対参照」でコピーに合わせて参照先が変わるのが「相対参照」になります。
例えば、数式が「=A1」である時に。これを下のセルにコピーをすると「=A2」のように変わりますね。これを「絶対参照」に形式変更するには「=$A$1」のように変更します。これを下のセルにコピーしても「=$A$1」のままで参照先が変わることはありません。
それではサンプルを用いて計算式コピーで数式がずれる原因と対策を解説していきます。
エクセルで計算式コピーでずれる（数式）原因とそのままにす対策1
VLOOKUP関数を使うと効率的な作業ができる便利な関数ですが、「#N/A」エラーなどが出て思い通りの値が表示されないなどトラブルが多いのではないでしょうか。
ここではVLOOKUP関数の計算式をコピーした際に発生するエラー原因と対策をサンプルで解説します。
B3セルにA3セルの数字に対応した文字列を抽出し表示させる数式を設定します。
=VLOOKUP(A3,E3:F12,2)
B3セルの数式をB4〜B7セルコピーします。
セルコピーをしたところ、B4〜B7セルの数式の範囲が変わっていますね。上図のサンプルをみると、B5セルの数式の範囲が変わっていて、値が入っていないところを参照しています。
上のサンプルでは、A列に数字を入れてもB5〜B7セルは正しき表示できずにエラーとなっています。
エラーとなる要因ですが、B3セルの数式では「範囲」が「E3:F12」と「相対参照」形式で設定されています。これが原因でセルコピーする際に範囲がカウントアップされてしまいました。
但し、検索条件はB5セルでは「A5」となっていて、ここはセルの値が変わっても問題ありませんね。
このエラーに対する対策は、「範囲」を「相対参照」ではなく、「絶対参照」形式で設定してみましょう。B3セルの数式の範囲を「絶対参照」形式の場合は「$E$3:$F$12」のように設定します。
=VLOOKUP(A3,$E$3:$F$12,2)
B3セルの数式をB4〜B7セルコピーします。
「相対参照」形式では範囲がずれていたB5セルも「絶対参照」形式に変わり範囲が正しく設定されました。
A列に数字を入れるとB5〜B7セルは正しくA列の数字に対応した文字列が正しく表示されましたね。
エクセルで計算式コピーでずれる（数式）原因とそのままにする対策2
「相対参照」と「絶対参照」はよく理解して使い分けをしないと、計算式をコピーした際にエラーに遭遇して修正に時間がかかりますね。
参照するシートが別シートを参照し、「相対参照」と「絶対参照」を使い分けるサンプルを使い解説します。
上図のサンプルでは、別シートの「仕入価格」より、商品の仕入価格と売値を表示するシートを作成します。
D3セルに「仕入単価」シートからデータを抽出する数式を入れて見ましょう。
=VLOOKUP(B3,仕入価格!B3:D12,3)
別シートの範囲からデータを抽出しますので、範囲の前に「仕入価格!」という記述を入れます。
D3セルの値をD4〜D12にセルコピーします。
コピーした結果を見てみると、D9~D12のセルが”0”となっていて正しく表示されていませんね。
原因はD3セルに入れた数式で、参照範囲を仕入単価シートの「B 3:D12」と相対参照形式で設定したことでセルコピーした際にD9セルの数式が、
=VLOOKUP(B9,仕入価格!B9:D18,3)
と参照範囲が変わっていることです。
では参照範囲を「$B$3:$D12」のように絶対参照形式に変えてみましょう。
絶対参照に変えたところ正しく表示されましたね。
エクセルで計算式コピーでずれる（数式）原因とそのままにする対策3
では次に先ほどのサンプルを使って売値を計算する際に相対参照と絶対参照が正しく設定されずに発生するエラーについて解説します。
E3セルに「仕入単価」シートの割引率から売値を計算する数式を入れてみましょう。
=D3-(D3*仕入価格!B15)
E3セルには正しく「720」円と売値が表示されていますね。
では、E3セルをセルコピーでE4〜E12セルにコピーしてみます。
E4〜E12の売値は仕入単価と同じで割引になっていませんね。E4の数式の参照を見てみると、”B16”なってしまっていますね。「仕入価格」シートのB16には値がありません、なので売値に割引価格が引かれてなかったのですね。
数式の参照先を「相対参照」で指定したことが原因ですね。
では、数式を「相対参照」ではなく、絶対参照で設定して見ましょう。
=D3-($D$3*仕入価格!$B$15)
E3セルをE4〜E12にコピーしました。よく見てみるとE3の値は正しく10％引きの80円が割引された売値が表示されましたが、E4〜E7の売値は全て80円引きになっていますね。
この原因もやはり「絶対参照」の設定誤りになります。
計算式の仕入価格の参照先は「仕入価格!$B$15」と正しく設定されていますが、「D3」も「$D$3」と絶対参照形式にしたことが原因で正しく表示されませんでした。
E3セルの数式を正しく、
=D3-(D3*仕入価格!$B$15)
と設定して、E4セルの数式をE4〜E12セルにコピーしました。
今回は割引（10％）された売値が正しく表示されましたね。
このように「相対参照」と「絶対参照」を正しく使い分けしないと、思い通りの結果にはなりませんね。
これまでの説明では別シートを参照先にする方法を解説しましたが、別ファイルを参照先にする方法も補足しておきます。
別シートを参照する際の数式を、別ファイルを参照先にする場合は以下のように設定します。
=D3-(D3*[work52_3.xlsx]仕入価格!$B$15)
これも覚えておくと便利だと思います。なお別ファイルから参照する場合も「相対参照」「絶対参照」の使い方は同じです。
まとめ エクセルで計算式コピーでずれる（数式）原因とそのままにする対策【別シート：別ファイルなど：連続】
この記事ではエクセルで計算式コピーで（数式）がずれる原因と対策をサンプルで解説しました。
エクセルのさまざまな処理を理解し、業務に役立てていきましょう。
