excelで関数を使用していると、参照元エリアが可変（自動で拡縮）になればいいのにと思うことがある。
例えば参照元エリアが以下のようなＢ列の１〜３だとすると、
$B$1:$B$3
という感じで表記することになるが、データ増加とともに参照元の行数や列数が増えていくケースは多い。
データが追加されて、Ｂ列の１〜５までが参照元になった場合、いちいち関数を修正していくのは気が遠くなる。
=sheet1!$B$1:$B$3
今回は参照元の領域を、自動で増減してくれるよう設定してから、同じく名前の定義に登録してみた。
「行数はデータがある分だけ」「列数もデータがある分だけ」
という発想で領域を定義する。
言いかえるならば、データの存在する行数と列数をカウントすることで領域を指定し、それに名前の定義を行う。
OFFSET関数の記述方法
範囲指定に使用したのはOFFSET関数。
まず基準点となるセルを指定し、そこから参照元エリアまでの行数、列数を記述。
さらに参照元の始点のセルからさらに行数、列数を記述することで範囲を指定する。
上記のクリーム色の部分の範囲を指定する場合は、以下のような記述になる。
※別シートから参照することを前提とするため、今回はシート名も記載。以下はシート名が「sheet1」の場合。
=offset(sheet1!$A$1,3,3,6,5)
OFFSET関数で「レコードがあるエリア」を指定してみる
さらに範囲を可変とするよう記述してみる。
以下のような宿泊参加者表をサンプルとして作成。行数が増えていくオレンジのエリアを対象範囲に指定したい。項目も増やす予定なので、列も可変としたい。
データの件数は「COUNTA関数」を使う。
データが存在した場合、カウントしてくれる関数です。
対象としてB行と４の列を選出した。
B行は氏名が入力されており、データが増加すれば必ず記入されているという前提で選出。
4の列は一覧のインデックスであり、項目列が増加した場合、必ず記入されるため選出。
B行のCOUNTA関数の表記は以下の通り。
=COUNTA(sheet1!$B:$B)-1インデックスの「名前」という文字はデータではないため、最後にマイマス１を追記した。
4の列のCOUNTA関数の表記は以下の通り。
=COUNTA(sheet1!$4:$4)-1最初の「No」という列は行数のカウントであり、データを入れないため最後にマイマス１を追記。
以上のOFFSETとCOUNTAを組合わせ、データが入力されている始点を「B7」とした場合、以下のような関数となる。
関数が長いので、テキストコピー用のサンプルを作ってみました。
=OFFSET(sheet1!$B$7,0,0,COUNTA(sheet1!$B:$B)-1,COUNTA(sheet1!$4:$4)-1)
OFFSETには差引勘定という意味もあるようだ。
始点の座標を指定し、そこから行数・列数指定してある範囲だけを特定する一連の流れを「差引き」と捉えているということらしい。
■名前の定義に指定してみる
上記のOFFSETでの範囲指定を名前の定義に登録する。
数式タブから「名前の管理」をクリック。
「名前の管理」で新規作成をクリック。
「名前の編集」の「参照範囲」に作成したOFFSET関数を入力します。
=offset(sheet1!$B$7,0,0,COUNTA(sheet1!$B:$B)-1,COUNTA(sheet1!$4:$4)-1)
今回は『氏名』という名前のエリアとして登録しました。
index関数の検索対象領域としてもよし、一列だけの可変領域を登録すれば、ドロップダウンリストの元の値としても活用できる。
データが増えても関数を変えなくていいので、楽になりました。
