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完全マスター: 関数・テクニック・新機能を網羅した決定版ガイド
本書は、Excelの基礎から応用まで、包括的に学べるガイドブックです。当サイトの豊富な情報を、電子書籍向けに最適化し、読みやすく再構成しました。
【本書の特徴】
- 情報量：Word-A4サイズ換算で1,400ページ相当
- 基本操作から高度なテクニックまで、段階的に学習可能
- 140種類の関数を詳細に解説
- 109種類の実用的なテクニックと具体例を紹介
- 最新機能（XLOOKUP関数、LET関数、シートビュー、LAMBDA関数、スピル等）を徹底解説
【対象読者】
- Excel初心者からプロフェッショナルまで
- 体系的にExcelスキルを向上させたい方
- 業務効率化を目指すビジネスパーソン
- データ分析や可視化のスキルを磨きたい方
【本書の強み】
- 実務に即した例題と解説
- 視覚的な図表やスクリーンショットで理解を促進
- 最新のExcelバージョンに対応した内容
- 著者の長年の経験に基づく、実践的なTipsを多数収録
【更新とサポート】
- 常に最新の情報を反映するため、定期的に内容を更新
- 最新版の入手方法：Amazonカスタマーサポートへお問い合わせください
【入手方法】
- 買い切り：950円
- Kindle Unlimited：読み放題プランで利用可能
Excelマスターを目指す方、業務効率を劇的に向上させたい方に、自信を持っておすすめできる一冊です。この1冊で、Excelの可能性を最大限に引き出す力が身につきます。