テーブルの計算列で、別テーブルの設定値を掛けたいときがあります。たとえば、会社ごとの倍率を入れた1行だけの表を作り、社員の表から引く場合です。
同じテーブルの値は [@Pay] のように @ を付けて書くので、別テーブルにも @ を付けて tblCompany[@Multiplier] と書きたくなります。ところが、1人目は正しい値になるのに、2人目から #VALUE! になります。
原因は、@ が「数式を置いたセルと同じ行」を指すことです。別テーブルの1行マスタには @ を付けずに書きます。この記事では、@ が実際にどの行を指すのかを数式の位置ごとに確かめた結果と、列名に記号が入ったときの書き方を、実測でまとめます。
先に結論
- 別テーブルの1行マスタは
tblCompany[Multiplier]と書く（@なし） - 同じテーブルの同じ行は
[@Pay]と書く - 列名に記号が入るときは
[@[Rate_1]]のように二重の角かっこにする - VBAの
Names.Addで名前に登録するときはtblCompany[[#Data],[Multiplier]]の形にする
構造化参照の書き方の基本
テーブルのセルは、テーブル名と列名で指せます。これを構造化参照と呼びます（Microsoft の説明はこちら）。この記事で使う書き方は、次の2つです。
| 書き方 | 指すもの | 例 |
|---|---|---|
テーブル名[列名] | その列のデータ全体 | tblCompany[Multiplier] |
[@列名] | 数式を置いた行の、その列のセル（[[#This Row],[列名]] の短縮形） | [@Pay] |
tblCompany[@Multiplier] のように、テーブル名を付けたうえで @ を使う形もあります。この形が何を指すのかを、次の節から確かめます。
検証した環境とサンプル
Excel 16.0（ビルド19127）、Windows 64bit、日本語版で確認しました。動的配列の関数が使える環境です。他のバージョンやMac版では確認していません。
サンプルは次の2つのテーブルです。同じシートに置き、見出しは1行目、データは2行目からです。
tblCompany（1行マスタ）：CompanyName、Multiplierの2列。データはAlpha、3の1行だけtblStaff（社員）：StaffId、Payの2列に、計算列を3つ足したもの。データは P1（100）、P2（200）、P3（300）の3行。計算列の名前はWithAt、NoAt、ByIndex
数式はセルに入力して、表示された値を見比べました。入力と読み取りはVBAで自動化しています。VBAで数式を設定したときの「実行時エラー1004」は、この記事では「その書き方は数式として受け付けられなかった」という意味で使います。
@が指すのは、数式と同じシート行
テーブルの外のセルに =tblCompany[@Multiplier] と入れて、置く位置を変えました。tblCompany のデータは2行目にあります。
| 数式を置いた位置 | 結果 | 補足 |
|---|---|---|
| 2行目 | 3 | tblCompany のデータ行と同じ行番号 |
| 3行目 | #VALUE! | 同じ行番号のデータ行がない |
| 1行目（見出しと同じ行） | #VALUE! | 見出しはデータ行ではない |
| 6行目（テーブルより下） | #VALUE! | 同じ行番号のデータ行がない |
| 別シートの2行目 | 3 | 別シートでも、行番号が同じなら値が返る |
| 別シートの3行目、10行目 | #VALUE! | 同じ行番号のデータ行がない |
結果は、数式を置いた行の番号が、相手テーブルのデータ行の番号と一致するかどうかで決まっていました。エラー値は #REF! ではなく #VALUE! です。
数式として受け付けられない場合もありました。存在しない列名（tblCompany[@Nope]）と、テーブルの外で表名を付けない [@Multiplier] は、VBAで設定すると実行時エラー1004になりました。
計算列で起きること
社員テーブルに、Pay に倍率を掛ける計算列を3つ並べて比べました。
| 社員（シートの行） | WithAt=[@Pay]*tblCompany[@Multiplier] | NoAt=[@Pay]*tblCompany[Multiplier] | ByIndex=[@Pay]*INDEX(tblCompany[Multiplier],1) |
|---|---|---|---|
| P1（2行目） | 300 | 300 | 300 |
| P2（3行目） | #VALUE! | 600 | 600 |
| P3（4行目） | #VALUE! | 900 | 900 |
シートの行番号で見ると、tblCompany のデータ（Alpha）は2行目です。P1 も2行目にいるので、tblCompany[@Multiplier] が Alpha の行に当たり、300 になります。P2 は3行目、P3 は4行目にいますが、tblCompany にその行番号のデータはありません。当たる行がないため、#VALUE! になります。
1人目だけ正しい値が出るので、テスト用に1行だけ入れたときは気づきにくい書き方です。
マスタを別のシートに置いた場合も、マスタのデータが2行目なら、WithAt の式は 300、#VALUE!、#VALUE!、NoAt の式は 300、600、900 でした。
@なしで引く書き方
1行マスタを @ なしで引く書き方は、次の形がすべて 3 を返しました。
=tblCompany[Multiplier]=INDEX(tblCompany[Multiplier],1)=SUM(tblCompany[Multiplier])=tblCompany[[#Data],[Multiplier]]（見出しを除いたデータ部分の列）
マスタが1行だけなので、列全体を指しても値は1つで、そのまま 3 が返ります。
1行マスタが2行になったとき
tblCompany に Beta（5）の行を足して、2行にしました。@ なしの式も、行が増えると結果が変わります。
| 式 | 2行になったあと |
|---|---|
セルに入力した =tblCompany[Multiplier] | 3 と 5 が下の行にあふれる（スピル） |
=INDEX(tblCompany[Multiplier],1) | 3のまま |
計算列 =[@Pay]*tblCompany[Multiplier] | 2行目が #SPILL!（行を足す前は300） |
計算列には、あふれた値を並べる場所がないため、#SPILL! になりました。INDEX(…,1) は先頭行を使い続けるので、結果は変わりません。ただし、増えた行は黙って無視されます。行が増えるかもしれないマスタは、1行マスタとして扱わず、キーで検索する設計にします。手入力の値を別テーブルに分けてキーで引く設計は、【Excel】手入力と自動計算を共存させる上書き優先列の設計にまとめています。
なお、VBAの .Formula で入れた式は、数式バーに =@tblCompany[Multiplier] と表示され、2行になると #VALUE! になりました。
1行だけのテーブルの計算列に、自分の行の列を [[#This Row],[Multiplier]] の形で入れると、数式バーには [@Multiplier] と表示されました。そのあと行を足すと、新しい行にも式が入り、値は 3 と 5 になりました。
列名の文字種ごとに切り分ける
実務メモには「列名にアンダースコアが入ると二重角かっこが必須」と書いていました。本当にアンダースコアだけの話なのかを確かめます。
[@列名] を短縮形、[@[列名]] を二重の角かっこの形と呼びます。たとえば、列名が Rate_1 のときは、短縮形が受け付けられません。
| 列名 | [@列名]（短縮形） | [@[列名]]（二重の角かっこ） |
|---|---|---|
Rate | ○ | ○ |
Rate_1 | × | ○ |
○は入力できたこと、×は数式として受け付けられなかったこと（VBAでは実行時エラー1004）を表します。
列名に1文字ずつ記号を入れた76通りを、テーブルの外から引く T[列名] も含めた3つの書き方で試しました。
| 列名の例 | T[列名]（テーブルの外から） | [@列名] | [@[列名]] |
|---|---|---|---|
英字、数字、日本語（Rate、列名、1列名） | ○ | ○ | ○ |
セル参照に見える語（A1、R1C1、COL1、TRUE） | ○ | ○ | ○ |
全角の記号（（ ） ＿ ％ ・ ー 、 。 など20種） | ○ | ○ | ○ |
半角スペース、全角スペース、| | ○ | ○ | ○ |
アンダースコア（A_B、Rate_1、_A、列名_名） | ○ | × | ○ |
ピリオド、ハイフン（A.B、列.名、列-名） | ○ | × | ○ |
半角の記号（, : " { } $ % & * + = < > / \ ! ? ; ~ など）、タブ、改行 | ○ | × | ○ |
[@列名] が通らなかったのは、76通りのうち40通りです。内訳は次のとおりです。
- 半角の記号とタブ・改行の33種（アンダースコア、ピリオド、ハイフンを含む）
- 日本語と組み合わせたアンダースコア・ピリオド・ハイフンの3種（
列名_名、列.名、列-名） - 英字と組み合わせたアンダースコアの4種（
_A、A_、A__B、Rate_1）
アンダースコアだけが特別なのではありません。今回試した半角の記号は、半角スペースと | を除いて、すべて短縮形では書けませんでした。[@[列名]] は、76通りすべてで通りました。
ほかに分かったことです。
- 半角スペース入りの列名は、短縮形でも入力できました
- テーブルの外から引く
T[列名]は、二重の角かっこなしで76通りすべて通りました []#'@を列名に含むとき、T[列名]にそのまま書くと受け付けられず、前に'を付けると通りました（例：列名A#BはT[A'#B]）[@[列名]]で入れた数式は、短縮形が使えない列名と半角スペース入りの列名では、数式バーでも二重の角かっこのまま表示されました。ほかの列名では[@列名]に直されました- 迷ったら
[@[列名]]と書けば、どの列名でも通りました
名前に入れるときは形が変わる
【Excel】引き継ぎブックで先に見る外部リンク・名前・接続の検証では、VBAの Names.Add に構造化参照を渡すと、実行時エラー1004になりました。名前（数式タブの「名前の定義」で作るもの）に入れる式は、セルに入れる式と、通る形が違います。Names.Add の RefersTo に渡して試しました。
RefersTo | 結果 |
|---|---|
=tblCompany[Multiplier] | × 登録できない |
=SUM(tblCompany[Multiplier]) | × 登録できない |
=tblCompany[@Multiplier] | × 登録できない |
=tblCompany[[#This Row],[Multiplier]] | × 登録できない |
=tblCompany[[#Data],[Multiplier]] | ○ 登録できた |
=INDEX(tblCompany[[#Data],[Multiplier]],1) | ○ 登録できた |
登録できた名前の RefersTo は、=tblCompany[[#Data],[Multiplier]] のままでした。その名前を使ったセルは 3 を返しました。アクティブなシートを別のシートにしても、=tblCompany[Multiplier] は登録できないままでした。
VBAの Range("tblCompany[Multiplier]") は B2 を返し、Worksheet.Evaluate("tblCompany[Multiplier]") は 3 を返しました。
まとめ
@は「数式を置いた行」と同じ行番号のデータ行を指す。別テーブルに付けると、行が合わないところで#VALUE!になる- 別テーブルの1行マスタは、
tblCompany[Multiplier]と@なしで引く。行が増える可能性があるなら、INDEX(…,1)か、キーで引く設計にする - 短縮形
[@列名]が通らないのは、アンダースコアに限らず、半角の記号の大半。迷ったら[@[列名]] - 名前には
tblCompany[[#Data],[Multiplier]]の形で入れる
コメント