Excelで数式を下方向へコピーする際、通常の相対参照では1行ずつ参照先が変化します。しかし、特定のパターンで4行おきに参照セルを移動したいケースも少なくありません。例えば「AA27とAA30」を参照した次の行では「AA31とAA34」、さらに次は「AA35とAA38」のように4行単位で参照位置をずらしたい場合です。この記事では、このような規則的なセル参照を効率よく実現する方法を解説します。
通常のコピーでは希望どおりにならない理由
元の数式をそのままコピーすると、AA27→AA28→AA29と1行ずつ増加してしまいます。
今回のように4行単位で参照先を移動する場合は、ROW関数などを利用して参照行番号を計算する必要があります。
4行おきに参照セルを変更する数式
例えば数式を入力するセルがA1から始まる場合は、次のような数式が利用できます。
=IFERROR(CONCATENATE(INDIRECT(“Sheet1!AA”&(27+(ROW(A1)-1)*4)),CHAR(10),INDIRECT(“Sheet1!AA”&(30+(ROW(A1)-1)*4)))&””,””)
この数式を下方向へコピーすると、参照先が自動的に変化します。
| 配置行 | 参照セル1 | 参照セル2 |
|---|---|---|
| 1行目 | AA27 | AA30 |
| 2行目 | AA31 | AA34 |
| 3行目 | AA35 | AA38 |
| 4行目 | AA39 | AA42 |
ROW関数の仕組みを理解する
ROW(A1)は1を返します。数式を1行下へコピーするとROW(A2)となり2を返します。
そこで「(ROW(A1)-1)*4」を利用すると、0、4、8、12…という増加値を作ることができます。
これを開始行である27や30に加算することで、4行おきの参照先が実現できます。
50行コピーしても自動で対応できる
この方法のメリットは、50行でも100行でもそのままオートフィルでコピーできることです。
参照先を手作業で修正する必要がなく、入力ミスも防げます。
データ量が多い帳票や定型レポート作成では特に便利なテクニックです。
INDIRECT関数を使わない代替案
Excelの処理速度を重視する場合、INDIRECT関数は再計算負荷が高くなることがあります。
大量データを扱う場合はINDEX関数を利用した方法も検討できます。ただし設定は少し複雑になります。
50行程度の利用であれば、INDIRECT関数でも十分実用的です。
まとめ
AA27・AA30を起点に、コピーするたびに4行ずつ参照先を移動したい場合は、ROW関数とINDIRECT関数を組み合わせる方法が最も簡単です。数式を一度作成すれば50行以上コピーしても自動でAA31・AA34、AA35・AA38というように規則的に参照先が変化するため、作業効率を大幅に向上できます。
コメント