Excel オートフィルやコピーに強いセル指定の方法(OFFSET関数とROW・COLMUN関数の組み合わせ)

2019年12月3日

OFFSET関数とROW・COLMUN関数の

組み合わせで普通のオートフィルやコピーでは

正常に動作しないケースを解決できます。

サンプルファイル

使用例

数式と参照先で行列の向きが異なる

例えば数式は縦方向にコピーしたいが

参照先は横方向に移動する場合に有効です。

B3~5は同じ数式を単純にコピーしています。

セル色は関数と参照先の対応を示しています。

数式と参照先で行列の向きが異なる例

OFFSET関数の引数を下記のように指定します。

参照基準セルとして$D$3を指定しています。
関数の参照先の最初のセルです。
行数この例では関数の参照先は同じ行から始めるため
0で固定しています。
列数この例では関数が下に行くと、参照先が右にずれます。
ROW関数で関数セルの現在行数を出し、
始点の関数セルの高さである3を引いています。
高さこの例では常に3行を集計対象としているので3固定です。
この例では常に1列を集計対象としているので1固定です。

参照先に一定の間隔がある

この例では下段に月毎の

事業別の売上と目標と、その差額があり

上段には売上のみを全事業で合計しています。

SUMIF系が適用できるように

作れれば、その方がスマートなのですが、

そうもいかないケースでは力を発揮します。

参照先に一定の間隔がある例

OFFSET関数の引数を下記のように指定します。

参照基準セルとして$C9を指定しています。
関数の参照先の最初のセルです。
行数この例では関数の参照先は同じ行から始めるため
0で固定しています。
列数この例では関数が右に行くと、参照先が右に3セルずれます。
ROW関数で関数セルの現在行数を出し、
始点の関数セルの行数である3を引いています。
更に参照先は3づつ動くため、3を掛け算します。
高さこの例では常に3行を集計対象としているので3固定です。
この例では常に1列を集計対象としているので1固定です。
(省略してますが)

注意点

関数が複雑に絡み合うため、

数式の読みやすさは確実に落ちます。

またシートの構成変更は

普段の関数より弱いです。

投入は定期的にコピーが必要な場合や

地道に手作業でシートを作るのが

煩雑なシートの場合に絞りましょう。

関連記事

使用した機能の詳細

基準セルからの距離、高さと幅を指定してセルの内容を取得(OFFSET関数)

セルの列と行の位置を取得(ROW関数とCOLUMN関数)

セルの相対参照と絶対参照の違いと使い方

数式がずれないようにコピー(オートフィル)する方法