数式を飛び飛びセルへコピーで参照がずれない数式の解説

このページは、「飛び飛びセルへコピーしても参照がずれない数式」を解説する２ページ目です。

　⇒ １ページ目へはこちら

ここでは、紹介した数式の動きを解説します。

始めに、前のページで紹介した数式の形と、例に用いた表をおさらいします。

１－１．数式の形は

まず、数式の形は次の通りでした。

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

このような数式で、一データ複数行を使っている場合に「数式を飛び飛びのセルにコピーしても正しい参照先にする」ことができます。

１－２．例で用いた表と入れた数式

前のページで、表は以下のものを使いました。

左表の仕入値や売価を、右表に手間なくリンクさせる、という例でした。

そして、セルH3に入れた数式は以下のものでした。

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

この数式を元に、動きを解説します。

では、数式の動きを部分ごとに確認していきます。

数式は内側から計算されるので、同じように内側から見ていきます。

始めに、ROWS（）の部分です。

この部分は、「カッコ内で指定した範囲の行数」が返ります。

例では、C3からC3なので１行のため、「1」が返ります。

次に、ROUNDDOWN（）の部分です。

この部分は、「コピー先のセルに応じて計算された行数」が返ります。

例では、「１÷３×１」の計算結果である「0.333…」をROUNDDOWN関数で小数点以下を切り捨てした「0」が返ってきます。

最後に、OFFSET（）の部分です。

この部分は、「基準セルから行方向、列方向にそれぞれ移動したセルに入っているセルの値」が返ります。

例では、基準セルがC3で行方向に0、列方向に0移動する、つまりC3のままなので、結果「50」が返ります。

これが、数式の基本的な動きです。

そしてこの数式は、他のセルにコピーした時に能力を発揮します。

続いて、数式を他のセルにコピーするとどうなるかを見てみます。

数式をコピーした場合に変化するのは、「３つ目の『元表最上段のセル番号』」のみです。

１つ例を見てみます。

先ほどの例でセルH3に入れた数式を、セルH6にコピーした結果、数式は次のように変化します。

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

数式が変化した結果、ROWS（）は4行になるので「4」が返ってきます。

そして、「4÷3×1」が計算され、「1.333…」をROUNDDOWN関数で小数点以下切り捨てし、結果「1」が返ります。

最後に、基準セルC3から行方向にだけ下に「1」動いた先のセルの値が返ってくるので、結果セルC4の「120」が返ってくるのです。

また、例えばセルH9に数式をコピーすると、数式は次のように変化します。

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

すると、ROWS（）は7行になるので「7」が返ってきます。

そして、「7÷3×1」が計算され、「2.333…」をROUNDDOWN関数で小数点以下切り捨てし、結果「2」が返ります。

最後に、基準セルC3から行方向にだけ下に「2」動いた先のセルの値が返ってくるので、結果セルC4の「30」が返ってきます。

このように、コピー先のセルの位置に応じて基準セルからどのくらい移動するかが計算されるのです。

以上、参考になれば幸いです。

前ページの「数式の作り方」に戻りたい場合はこちら

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