【Excel】構造化参照の読み方と使い方|列全体と「この行」の違い

=[@単価]*[@数量]は、「この行の単価と数量を掛ける」という数式です。Excelのテーブルでは、セル番地の代わりにテーブル名と列見出しを使って参照できます。この書き方が構造化参照です。

最初に区別したいのは、売上表[金額]が金額列のデータ全体を指し、[@金額]が同じ行の金額を指すことです。以下ではWindows版Excelで初めて構造化参照を使う人向けに、単価と数量から金額を出す小さな表を作り、行の追加や集計まで確認します。

構造化参照を使えるのは「テーブル」にした範囲

罫線や背景色を付けただけの表は、Excelのテーブルとは限りません。表の中をクリックし、「テーブルデザイン」タブが出るか確認してください。テーブルにすると、列見出しを使った数式や、同じ式を列へ展開する計算列を利用できます。セル番地を使う数式も引き続き使えます。

練習用にA1:D4を用意し、1行目に「商品」「単価」「数量」「金額」と入れます。2行目は「ノート・120・3」、3行目は「ペン・80・5」、4行目は「封筒・200・2」と入力し、金額列は空けておきます。この段階で既存の合計行や注釈を混ぜず、1行に1件の商品を置く形にします。

A1:D4を選択してCtrl+Tを押し、範囲と「先頭行をテーブルの見出しとして使用する」を確認して確定します。「テーブルデザイン」のテーブル名を「売上表」に変更してください。既に同名のテーブルがある場合は別名にし、以下の式にもその名前を使います。見出しは「数量」と「個数」のように表記を混ぜず、何を入力する列か分かる名前にします。

列全体の参照と「この行」の参照を読み分ける

売上表[単価]は単価列のデータ部分、この例ではB2:B4です。見出しの「単価」や、後から表示する集計行は通常の列参照に含みません。一方、金額列のD2に書く[@単価]はB2だけを指します。D3で同じ表記を使えばB3を指すので、式の見た目が同じでも行ごとに別の商品を計算できます。

角かっこは列名などを囲み、@はこの行を表します。同じテーブル内ではテーブル名を省略できますが、テーブル外の合計セルでは名前を付けた参照にすると対象が明確です。[@金額]を表の外へそのまま貼り付けて、表全体の合計になると考えないでください。

セルをクリックして計算列を作る

1. 金額列の最初のデータセルD2を選び、半角の「=」を入力します。
2. 同じ行のB2をクリックし、「*」を入力してC2をクリックします。
3. 数式バーが=[@単価]*[@数量]になっていることを確認し、Enterで確定します。
4. D2が360、D3が400、D4が400になったか確かめます。

空のテーブル列へ式を入れると、通常は列全体に式が展開されます。ただし、既に値や別の数式が入っている列、計算列の自動入力を無効にした環境では、同じ結果にならないことがあります。先頭セルに結果が出ただけで完了とせず、下の行の数式バーも見てください。

クリックしてもB2やC2という番地が入る場合は、テーブルの内部を選んでいるかを確認します。Windows版の「ファイル」→「オプション」→「数式」には「数式でテーブル名を使用する」の設定もあります。番地で計算できている式を慌てて壊す必要はありません。構造化参照で統一したいときに、設定と対象の範囲を確認してから入力し直します。

単価120、数量3の結果が360になっていても、たまたま同じ数値の別セルを参照している可能性があります。計算結果と参照する列名の両方を確認することが大切です。

テーブルの外で合計し、追加した行も確かめる

テーブルから離れたF2に=SUM(売上表[金額])と入力します。3商品の合計は1160です。この合計式を金額列のデータ行へ入れると自分自身を集計する循環参照になるため、入力場所も確認します。次に売上表へデータ行を追加し、「付箋・150・2」を入力します。新しい行がテーブルに入り、金額が300、F2が1460になれば、追加行まで計算と集計が届いています。

テーブルのすぐ下へ入力すると自動拡張されることがありますが、空白行を挟んで貼り付けたデータまで必ず取り込むわけではありません。「テーブルデザイン」の「テーブルのサイズ変更」で対象範囲を確認できます。集計が増えないときは、SUM関数を書き直す前に、新しい行がテーブルの外に残っていないか調べます。

テーブル内に集計行を表示する方法もあります。「テーブルデザイン」で「集計行」をオンにし、金額列の一覧から「合計」を選びます。ここでは通常SUBTOTAL関数が使われ、フィルターで非表示にした行を除いて集計できます。対して先ほどのSUMは、フィルターで隠れた行も含む合計です。絞り込んだ後に数字が違っても、すぐに計算ミスと判断せず、どちらの合計が必要か確認してください。

参照がおかしいときは名前・範囲・例外行を調べる

以下は、初めて作った計算列で結果が合わない場合の確認です。既に使っている表の列名を変えた後や、数式を右へ広げた後だけ結果が崩れる場合は、構造化参照の列名変更・横フィル後の診断手順を参照してください。変更前後の式を比べ、文字列に残った旧名や、横へ動いた参照列を調べます。

一部の行だけ結果が違う場合は、その金額セルを選びます。式を固定値で上書きした行、他の式を入れた行がないかを見てください。構造化参照は入力内容が正しいことまで保証しません。「特別価格なのでこの行だけ変更した」という処理なら、金額を直接上書きするより、単価や値引き用の列で理由を追えるようにすると後から確認できます。

列全体が必要な場所に@を付けたり、1行の計算に列全体を指定したりすると、意図と違う計算になります。式を短くするために記号を削らず、「この行か、列全体か」を先に決めます。空白や記号を含む列名ではかっこが増える場合があるため、セルの選択や数式の入力候補を利用し、Excelが作った表記を確かめてください。

見出し・データ・集計行を明示して参照する指定もあります。集計行がないのに集計行の値を参照する、といった指定の食い違いを避けるため、まず基本の列参照と@を使い分け、特殊な指定は入力候補と実際の表の構成を照合して使います。

通常の範囲に戻す場合と保存前の確認

テーブル機能が不要になった場合は、テーブル内を選び、「テーブルデザイン」→「範囲に変換」を使います。構造化参照は通常のセル参照へ変わり、テーブルの自動拡張などの機能はなくなります。見た目の書式は残るので、色が付いているからまだテーブルだとは判断できません。変換直後ならCtrl+Zで戻せます。

また、普通の範囲をテーブルへ変換しただけでは、既存の番地参照の式がすべて構造化参照へ書き換わるわけではありません。旧来の集計式が混在するブックでは、合計セルごとに範囲を確認してください。

保存前は、行ごとの360・400・400、合計1160という最初の例と、追加後の1460を照合します。練習で足した行を削除したら、合計が1160に戻ることも確認しましょう。実務では、最新行がテーブル内にあり、式が途中で固定値に変わっていないことまで確認すると、構造化参照を使う効果が得られます。

📊

Excelトラブル完全解決データベースこの記事以外にも、様々なエラー解決策をまとめています。困った時の逆引きに活用してください。

🏆

超解決 Excel検定 あなたのExcel実務能力を3分で測定！【1級・2級・3級】