数式を飛び飛びセルへコピーすると参照がずれる時の回避法

ここでは、数式を飛び飛び（数行おき）のセルへコピーした時に起こる参照のずれを回避する方法について解説します。

参照のデータは連続しているけど数式は数行おき、またはその反対でも対応できる方法です。

始めに、やりたいことを例を含めて確認します。

１－１．数式を飛び飛びのセルにコピーしても正しい参照先にする

やりたいことは、「数式を飛び飛びのセルにコピーしても正しい参照先にする」です。

そもそも、エクセルは数式をコピーすると参照先も相対的に変わります。

例えば、一つ下にコピーすると参照先も一つ下がります。

これが参照してほしいセルであればいいのですが、「一データに複数行」使っていたりすると、参照先がずれてしまいます。

ここでは、このような「一データ複数行」のケースでも参照先をずらさない数式を紹介します。

１－２．例で確認

ひとつ例を見てみましょう。

上の例の左側は商品ごとの仕入値と売価の表で、右側は利益額の一覧表です。

そして、「左側にある仕入値と売価を右側の表に表示させたい」とします。

しかし、問題点は「単純に『=セル番号』では手間がかかりすぎる」ことです。

例えば、ねぎの仕入値を左の表から「=C4」で持ってきたとします。

ただし、これを下のセルにコピーしても当然正しい値は入りません。

左表のかぼちゃの仕入値が、右表のねぎの売価に入ってしまいました。

これは、数式のコピー先に応じて参照先も同じ分だけ移動するからです。

すると、一つ一つ「＝セル番号」と入れていかなければならないのか？となります。

そんなことはありません。ちゃんと方法はあります。

数式を入れるのは赤枠部分だけにして、下にコピーすればそれぞれ正しい値が表示させるようにできます。

では、数式の形と各引数の入れ方を見ていきます。

２－１．数式の形は

数式の形は、次の通りです。

指定するのは、「元表最上段のセル番号」と「コピー先の行間隔」、「元表の行間隔」の３つです。

２－２．引数の指定の仕方

次に、引数の指定の仕方を、例を参考にしながら説明します。

以下、例の表です。

（１） 元表最上段のセル番号

「元表最上段のセル番号」には、参照元の表のうち、一番上のセル番号（タイトル行は除く）を入れます。

例えば、「仕入値を入れたい」といった時には、元表（左の仕入値表）の仕入値が入っているC3～C5のうち「C3」が「最上段のセル番号」となり、これを全部で「3ヶ所」に入れます。

ただし、1・2か所目は「絶対参照」で、3か所目は「相対参照」で入れてください。

絶対参照とは、「$C$3」のように「$」を２つ付けた状態のことで、相対参照とは、「C3」のように「$」を付けない状態のことです。

すると、例の場合セルH3に入れる数式は、この時点で次のようになります。

=OFFSET($C$3,ROUNDDOWN(ROWS($C$3:C3)/コピー先の行間隔*元表の行間隔+1,0),0)

（２）コピー先の行間隔

「コピー先の行間隔」には、「数式をコピーするセルが何行おきか？」を数値で指定します。

例では、右表の仕入値が３行おきになっているので「３」と入れます。

すると、この時点で数式は次のようになります。

=OFFSET($C$3,ROUNDDOWN(ROWS($C$3:C3)/3*元表の行間隔+1,0),0)

（３）元表の行間隔＋１

「元表の行間隔」には、「元表の値が何行おきか？」に「１」を加えた値を指定します。

例では、左表の仕入値がC3、C4、C5と１行も空かずに入っているので、０＋１で「１」と入力します。

すると、数式は次のようになって完成です。

=OFFSET($C$3,ROUNDDOWN(ROWS($C$3:C3)/3*1,0),0)

ちなみに、セルH3に入れた数式をセルH6とH9にコピーすると、ちゃんと正しい結果が返ってきました。

続いて、数式の作り方を見てみます。

以下、手順です。

（１）以下の数式をコピーし、セルに貼り付ける

【コピーする数式】

（２）１つ目の「元表最上段のセル」の文字を消し、該当するセル番号を入力後、F4キーを１回押す

※ F4キーとは「キーボード最上段の『F4』と書かれたキー」のことです

（３）２つ目の「元表最上段のセル」も（２）と同じように行う

（４）３つ目の「元表最上段のセル」は、セル番号入力後にF4キーを押さない

（５）「コピー先の行間隔」の文字を消し、数値で指定する

（６）「元表の行間隔+1」の文字を消し、数値で指定後にEnterキーを押せば完了

最後に、複数行まとめてコピーする方法です。

例をもとに、手順を確認してみます。

（１）セルH4（売価）にも同様の手順で数式を入れる

（２）利益額は「＝売価－仕入値」で求める

（３）数式を入れた３行を範囲選択する

（４）範囲の右下にカーソルを当て、「+」マークになったら下に左ドラッグして離せば完了

これで、複数行をまとめてコピーできます。簡単ですね。

なお、なぜこの数式でできるのか？

それは長くなるので、次のページで解説します。

次のページにいくにはコチラ

関数の問題解決記事一覧へはこちら