Excelでデータ範囲を動的に指定する。OFFSETとテーブルの使いどころ
行が増えるExcelの表を、固定範囲の修正なしで集計やプルダウンから使う。旧記事のOFFSETとCOUNTAの例で空白行の落とし穴を確認し、テーブルでの設定手順も示す。
Excelで商品一覧を増やしたのに、集計やプルダウンの候補は増えていなかった。参照先が A2:A10 のままなら、新しい11行目は範囲外である。どこまでが表かをExcelに判断させると、表を更新する人が数式まで直す手間がなくなる。
OFFSETで表の高さと幅を数える
旧記事では、データが連続している表について、左上のセルを基準に OFFSET で範囲を指定していた。以下は見出しを1行目、商品IDをA列に置く例である。試すときは表を Sheet1 に作り、集計式は別のシートに置く。旧記事「データ範囲を動的に指定する」
| セル | A列 | B列 | C列 |
|---|---|---|---|
| 1行目 | 商品ID | 商品名 | 数量 |
| 2行目 | A01 | ペン | 4 |
| 3行目 | A02 | ノート | 6 |
=SUM(OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A$1:$A$1000),COUNTA(Sheet1!$A$1:$Z$1)))
A1 から上下左右にずらさず、A列のデータ数を高さ、1行目の見出し数を幅にする。例では高さ3、幅3で A1:C3 になり、合計は 10 である。4行目に A03、定規、2 を加えると A1:C4 になり、合計は 12 になる。商品IDは文字列で、表を数える範囲に別のデータが入らないことを前提とする。MicrosoftのOFFSET関数の説明
ここで A3 のIDだけを消すと、A列で数えられるセルは、見出しの A1 とIDが入っている A2・A4 の3つになる。その結果、OFFSET が返すのは左上から3行分の A1:C3 である。IDを消した3行目の数量6は合計に残り、4行目の数量2が範囲から外れるため、合計は10に戻る。
列方向でも、途中に空白の見出しがあると末尾側の列が範囲から外れる。空文字列 "" を返す数式が入っているセルは、COUNTA に数えられる。既存ブックでこの方式を使うなら、見出しとIDを連続させ、想定する上限と空欄を確認する。表の途中の数量が空でも、IDが埋まっていれば範囲の高さは変わらない。MicrosoftのCOUNTA関数の説明
テーブルに行を追加して範囲を広げる
新しく作る表では、見出しを含む範囲を選び、[挿入]→[テーブル]でテーブルに変換する。テーブル名を Items にすると、表の外のセルから数量列を次の式で集計できる。表に行を追加すると、集計範囲も広がる。Microsoftの構造化参照の説明
=SUM(Items[数量])
商品IDが入っている行だけを合計する
A1:C4 を Items テーブルにすると、A3 が空欄のままでも、上の式は数量列の 4 + 6 + 2 を合計して12を返す。商品IDが入っている行に絞るなら、SUMIF で商品ID列が空欄以外という条件を指定する。この例では2行目と4行目の 4 + 2 で6になる。MicrosoftのSUMIF関数の説明
=SUMIF(Items[商品ID],"<>",Items[数量])
プルダウンの候補もテーブルに連動させる
商品名を別シートのプルダウンにする場合は、Excelデスクトップ版の[数式]→[名前の管理]で、ブック全体を範囲とする名前 ItemChoices を作る。参照範囲は =Items[商品名] にする。次に入力セルの[データの入力規則]を[リスト]に設定し、元の値を =ItemChoices にする。列見出しは候補に入らず、テーブルの末尾へ行を追加すれば新しい商品名が候補に加わる。候補に空欄が並ばないよう、商品名を埋めてから追加する。Microsoftのドロップダウンリスト作成手順