ExcelでSUMPRODUCTやSUMIF、INDIRECTを組み合わせた数式を作成すると、横方向へコピーした際に参照列だけを自動的に変更したい場面があります。しかし、INDIRECT関数内に直接「C:C」などの文字列で列を指定している場合、通常のオートフィルでは列が自動的に移動しません。
この記事では、COLUMN関数やADDRESS関数を利用して、横へコピーするだけでC列、D列、E列のように参照先を自動変更する方法を詳しく解説します。
INDIRECT関数の列指定が横コピーで変わらない理由
Excelの通常のセル参照では、例えば「=SUM(C1:C10)」という数式を右へコピーすると、自動的に「=SUM(D1:D10)」のように参照範囲が移動します。
しかし、INDIRECT関数では参照先を文字列として指定しています。例えば「INDIRECT(“Sheet1!C:C”)」の場合、ExcelはC列という文字をそのまま参照先として扱うため、右へコピーしてもD列には変化しません。
そのため、COLUMN関数などを利用して、コピーした位置に応じて列番号を計算し、INDIRECTへ渡す文字列を作成する必要があります。
COLUMN関数を使って参照列を自動変更する方法
横方向へコピーする場合は、COLUMN関数で現在の列番号を取得し、その番号を使って参照列を作成します。
例えば、最初の数式をC列から開始したい場合は、以下のように変更できます。
=SUMPRODUCT(SUMIF(INDIRECT(シート名&"!$B:$B"),$B10,INDIRECT(シート名&"!"&ADDRESS(1,COLUMN(C:C),4)&":"&ADDRESS(1,COLUMN(C:C),4))))
ただし、このままだとADDRESS関数がセル形式を返すため、列全体を指定する場合にはSUBSTITUTE関数などで行番号を削除する方法が使いやすくなります。
列番号から列名を作成してINDIRECTへ渡す方法
より実用的には、ADDRESS関数とSUBSTITUTE関数を組み合わせて列記号だけを取得します。
例えば以下の数式では、コピー位置に応じてC列、D列、E列へ自動的に変化します。
=SUMPRODUCT(SUMIF(INDIRECT(シート名&"!$B:$B"),$B10,INDIRECT(シート名&"!"&SUBSTITUTE(ADDRESS(1,COLUMN(C:C),4),"1","")&":"&SUBSTITUTE(ADDRESS(1,COLUMN(C:C),4),"1",""))))
この式を右へコピーすると、COLUMN(C:C)の部分がCOLUMN(D:D)、COLUMN(E:E)へ変化するため、INDIRECTで参照する列も自動的に移動します。
結果として、最初はC列を参照し、右へコピーするとD列、さらに右へコピーするとE列を参照する数式になります。
より簡単な方法：COLUMN関数とINDEX関数を使う
INDIRECTは便利な関数ですが、文字列を参照するため計算負荷が高くなる場合があります。大量のデータを扱う場合はINDEX関数を使った方法もおすすめです。
例えば、列番号を動的に変更したい場合は以下のような考え方ができます。
=SUMPRODUCT(SUMIF(シート名!$B:$B,$B10,INDEX(シート名!$C:$Z,0,COLUMN(A1))))
この場合、COLUMN(A1)が右へコピーすることで1、2、3と変化し、INDEX関数が取得する列も順番に移動します。
INDIRECTを使う必要がない場合は、INDEX関数の方が高速で安定した数式になることがあります。
横コピー対応の数式を作るときの注意点
動的に列を変更する数式を作る場合、どのセルを基準にCOLUMN関数を使用するかが重要です。
例えばC列から開始したい場合、COLUMN(C:C)を使えば最初からC列になりますが、A列を基準にしてCOLUMN(A1)を利用する場合は1から数える必要があります。
また、INDIRECTは参照先のシート名や範囲が間違っているとエラーになりやすいため、シート名にスペースが含まれる場合は「’シート名’!」のようにシングルクォーテーションで囲む必要があります。
まとめ
SUMPRODUCTやSUMIFとINDIRECTを組み合わせた数式で、横コピー時に参照列を自動変更したい場合は、COLUMN関数を利用して列番号を取得し、その値から参照文字列を作成する方法が有効です。
ADDRESS関数やSUBSTITUTE関数を組み合わせれば、C列からD列、E列へとオートフィルだけで参照先を移動できます。
ただし、データ量が多い場合はINDIRECTよりINDEX関数を利用した方が処理速度や管理面で優れる場合があります。用途に応じて適切な方法を選ぶことで、より扱いやすいExcel数式を作成できます。
コメント