テーブルのセル参照 : 構造化参照とエラー回避策

テーブル内のデータをテーブル外から参照する際のポイントを紹介します。データ入力用にテーブルを使い、別の表からそれを参照する場合を例に説明します。提出用の定型書式が先に決まっていて、データ入力を効率よく行いたいといった場面でよく用いる策です。

ポイント1 : 「構造化参照」形式で参照する

テーブルのセルを参照する数式には、「=SheetA!A4」のような通常の相対参照、「=入力テーブル[@品番]」のような構造化参照の2種類があります。

構造化参照にするには

「=」を入力して参照先のセルをクリックしたとき、相対参照になる場合と構造化参照になる場合があります。どちらにするかをユーザーが選択することはできません。この違いは、行の位置です。

構造化参照にする条件は、参照元と参照先の行番号を同じにする です。参照先がシート内でも他シートでも、です。行番号が異なっていると相対参照になります。

先頭行番号を 6 で統一

構造化参照が必要な理由

構造化参照の数式のほうがわかりやすいというのも一つですが、より重要な理由があります。エラー回避です。

テーブルの行を削除すると、そこを参照している相対参照は #REF! エラー（リンク切れ）になります。さらに、その数式が =SheetA!#REF! に書き換わってしまい、どこを参照していたのかもわからなくなってしまうのです!

エラーになるだけでなく数式も変わってしまった

テーブルで無効なデータ行を削除するのはよく行う操作ですが、それができなくなってしまうということです。

構造化参照であれば、エラーになりません。6行目が削除されても新たな6行目の値を参照します。

ポイント2 : テーブルの状態に対応する

テーブルの行数（データの個数）は、定型書式の行数と同じとは限りません。テーブルが変化しても定型書式の外観に問題が生じないように調整します。

#VALUE! エラーと 0 を対策

構造化参照の数式のみの場合、データ数が少なく参照先の行がないと#VALUE! エラーになります。これにはIFERROR関数で対応できます。

また、データ行が削除されずに空行として残っていると、エラーにはなりませんが 0 が表示されます（これは相対参照の場合と同じです）。これを避けるにはISBLANK関数などを使います。

両方に対応する場合は、少し長くなりますが次のような参照数式になります。

データ個数の超過を対策

テーブルのデータは増減自在なので、定型書式の行数を超えてしまうこともあり得ます。参照エラーにはならないので、超過になったら気づけるようにしておきましょう。

欄外まで上記の数式で埋めてもよいですし、メッセージを表示するよう加工してもよいです。

集計行の参照に注意

テーブルは、見出し行と集計行の表示/非表示をスイッチできます。

見出し行を非表示にしても、=入力テーブル[@品番] の参照式はリンク切れにはなりません。

問題は集計行のほうです。=入力テーブル[[#集計],[在庫数]] の参照式は、集計行を非表示にすると #REF! エラーになります。
集計行を再度表示すればエラーは解消できる（数式は維持される）ので、非表示のままにしないよう注意しましょう。場合によっては、集計行の値は参照しないで、参照元の表で別途集計するほうが無難かもしれません。