Introduction
Ver. all 2013 2016 2019 2021 365
Excelで作成した関数を他のシートで使用するとセル参照がずれてしまう・・・。
例えば下の図の「D2」に入力されている数式はすぐ左隣の「試験」列のセルを参照しています。
その式を他のシートにコピーすると、そのままのセル番地がコピーされるのではなく、すぐ左のセルを参照するように自動設定されます。
「試験」列のセルを参照したいので、数式のセル参照をずらす必要がありますね。
そんな時の対処法を３つ紹介します。
セル番地を1つ1つ修正するのは面倒だにゃ
置換機能を利用する
置換機能は、数式内で同じセル番地を一括で他のセル番地に変更したい時に便利です。
※複数のセル番地を組み合わせた数式の場合は、その数だけ置換機能を繰り返す必要があるので時間がかかります。次に紹介する方法を参考にしてください。
下図、「D2」には「C2」の「試験」の点数を参照した
=IF(C2>=85,5,IF(C2>=65,4,IF(C2>=50,3,IF(C2>=30,2,1))))
という式が入っています。
これを別シートの表にコピーしたかったとします。
右の表には「D列」「E列」が間に入っているためそのままでは「試験」ではなく、すぐ左の「小ﾃｽﾄ2」を参照してしまいます。これを置換機能で修正してみます。
では、操作を始めます。
まず、コピー元の式が入力されている「D2」を選択してコピー。
シートを移動して、「F2」に貼り付けます。
「試験」の点数「C2」ではなく、左隣の「小ﾃｽﾄ2」のデータ「E2」を参照してしまいます。
そのため、下にコピーしてみると全員「1」の成績に・・・。
では、この場所のセル参照「E2」を「C2」に置換します。
「F2」を選択して、
［検索と選択］をクリック。
一覧から［置換］を選びます。
※ショートカットキー【Ctrl】+【H】がおすすめ。
「E2」を「C2」に変更したいので、
- ［検索する文字列］・・・「E2」
- ［置換後の文字列］・・・「C2」
を指定して、［すべて置換］を押します。
そうしないと、関数名にそのアルファベットが使用されていた場合、関数名の文字が置き換わってしまいます。
下のように置換した数が表示されるので、置換されるべき数に間違いがないか確認して［OK］を押しましょう。
「E2」が「C2」に置き換わりました。
後は下にコピーすれば完了です。
セルではなく数式そのものをコピーする
「1学期」のワークシートの数式を、「2学期」のワークシートにコピーしたかったとします。
「1学期」シートの「成績」の計算式はすぐ左の「試験」のデータを参照しています。
その「D2」セル選択をしてコピーして「2学期」シートに貼り付けると、
すぐ手前のセルを参照するようにセル参照が自動修正されてしまいます。
今回の場合は、「C2」が「E2」へ変更されます。
そのまま「C2」で良かったのに・・・。
そんな時は、数式そのものを選択してからコピーしましょう。
コピーしたい数式が入っているセルを選択したら、
そのままコピーせずに、数式バーを確認。
この式を直接コピーしましょう。ショートカットキーを利用するとスムーズです。
【Ctrl】+【A】で選択 → 【Ctrl】+【C】でコピー。
※ドラッグして選択 → 右クリックのメニューからコピーもできます
後は、コピー先のシートに貼り付けましょう。
貼り付ける際は、数式バーではなくそのままセルに貼り付けて大丈夫です。
セル番地が移動せずに貼り付けられます。
数式をずらしてからコピーする
１つか２つの式をそのままコピーしたい場合は、手前で説明したように直接コピーするのが良いでしょう。
しかし、連続した列の複数の式をコピーしたい場合は、ここで説明する方法がおすすめです。
先ほどと同じ2つの表を使用して説明します。
※ここではコピーする数式の列は1つですが、本来は複数の連続した列に数式が入っているときに便利です
左の「D2」の式を、右の「F2」にコピーすると、「試験」の列ではなく「小ﾃｽﾄ」の列を参照してしまいます。
「試験」「成績」の２つ分の列をずらして参照するように設定すれば解決します。
まずは、元の表のシートをコピーしてから作業しましょう。
コピーする際は、シート見出しを【Ctrl】を押しながら右に引っ張ります。
コピーできました。
この表で、コピー先と同じセル番地になるように列を加減します。
今回の場合は「試験」と「成績」の間に2列追加すれば、コピー先と同じになります。
列を追加するには追加したい場所の後ろから追加する数だけ列番号を選択します。
今回の場合は「試験」の後ろから2列を選択することになります。
選択した列番号の上で右クリック → 【挿入】をクリック。
2列挿入されました。
これで数式を貼り付けたい表と同じ形になったので数式をそのまま使えます。
「F2」の数式をコピーして貼り付ければOKですね。
最後に作業用にコピーしたワークシートを削除しておきましょう。
シート見出しの上で右クリック → 一覧から［削除］を選びます。
コピーした作業用のシートを利用することで、自由にセル参照の移動を試せますよ。
まとめ
複数シートで作業していると今回のように数式をコピーして利用したい場面があります。
そのまま使用できれば良いのですが、データの形が微妙に異なる場合は今回の例が参考になるのでは。
ポイントを３つ挙げておきます。
- 置換機能を使用する際は、セル番地まで指定してから置換する
- 1つ2つの式ならば数式バーの式を直接コピーする
- 行の挿入で対応する際は、シートごとコピーしてから試す
これらのポイントを押さえて正しいセル参照でコピーしましょう。
セル番地のずれは慌てず慎重に修正するにゃ