Excel オートフィルやコピーに強いセル指定の方法(OFFSET関数とROW・COLMUN関数の組み合わせ)
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固定です。 （例では省略していますが） |
注意点
関数が複雑に絡み合うため、数式の読みやすさは確実に落ちます。
またシートの構成変更には普段の関数より弱いです。
この方法は定期的にコピーが必要な場合や地道に手作業でシートを作るのが
煩雑なシートの場合に絞りましょう。
Excelを効率的に習得したい方へ
当サイトの情報を電子書籍用に読み易く整理したコンテンツを
買い切り950円またはKindle Unlimited (読み放題) で提供中です。
Word-A4サイズ：1,400ページの情報量で
（実際のページ数はデバイスで変わります）
基本的な使い方、関数の解説(140種類)、
頻出テクニックと実用例(109種類)、
XLOOKUP関数、LET関数、シートビュー、LAMBDA関数、スピルなど便利な新機能を紹介。
体系的に学びたい方は是非ご検討ください。
アップデートなどの更新事項があれば随時反映しています。
なお購入後に最新版をダウンロードするには
Amazonへの問い合わせが必要です。