【Excel】構造化参照が列名変更・横フィルで崩れたときの確認

Excelのテーブルで列名を変えたら、一部の集計だけが#REF!になった。数式を右へ広げたら、エラーは出ないのに合計が変わった。このようなときは、変更前後の式を比べ、名前を直接参照しているのか、文字列から参照を作っているのかを確認します。

ここでは、既に使っているブックをWindows版Excelで点検します。テーブルを最初から作る手順や@の読み方は、構造化参照の基礎と計算列の作り方を参照してください。

1. 変更した操作と、合うはずの結果を控える

まずブックのコピーを保存し、正常だった時点から何を変えたかを確認します。「見出しの文字だけを変更した」「元の列を削除して新しい列を作った」「式を右へドラッグした」は別の操作です。元に戻す前に、問題のセル番地、数式バーの式、現在の結果、期待する結果を控えておきます。

例として、既存の「売上表」がA1:C3にあり、次のデータを持つ場合を考えます。B1の見出しだけを「金額」から「売上額」へ変更し、明細の数値は変えていません。この変更で売上合計2000や原価合計1200が変わる理由はありません。

表の外にあるF2は通常の構造化参照、F3はINDIRECTを使う集計セルとします。変更前には両方とも2000でした。同じ数値を表示していた二つの式が変更後に分かれることを手掛かりに、元データと数式のどちらを直すか判断します。

2. 列名変更に追従した式と、旧名が残った式を比べる

同じブック内でテーブルの見出しを変更すると、その列を直接使う構造化参照も更新されます。F2の=SUM(売上表[金額])は、見出し変更後に=SUM(売上表[売上額])となり、1200と800を合計する2000のままです。表示値だけでなく、角かっこ内が新しい列名になったことも確認します。

一方、F3が=SUM(INDIRECT("売上表[金額]"))なら、引用符で囲んだ「売上表[金額]」は文字列です。B1を変えてもこの文字列の旧名は残ります。「金額」という列がなくなったため、INDIRECTが参照を作れず#REF!になります。F3がエラーでも、B2:B3の数値が失われたわけではありません。

この例では、売上表のB2:B3を集計したいことを確かめてから、F3の文字列を「売上表[売上額]」へ直します。修正後の式は=SUM(INDIRECT("売上表[売上額]"))で、結果は2000です。列を選び分ける必要がない式なら、F2と同じ直接参照へ置き換える方法もあります。

見出しの改名と、元の列を削除して同名の列を作り直す操作は同じではありません。式自体に#REF!が残っている場合は、削除前のコピーと比べ、どのデータを参照すべきだったかを確認します。名前だけ合わせて、削除された値や参照関係まで戻ったと判断しないでください。

3. 参照文字列を作るセルにも旧名が残っていないか確認する

INDIRECTの引数に旧名が見当たらない場合は、文字列を作る元のセルをたどります。例えば=SUM(INDIRECT("売上表["&H1&"]"))は、H1の文字を列名として使う式です。H1が「金額」のままなら、完成する文字列も「売上表[金額]」のままです。

コピーしたブックの空きセルで="売上表["&H1&"]"を確認すると、実際にINDIRECTへ渡している文字列が見えます。見出しと一文字ずつ比べ、末尾の空白、全角・半角、かっこの不足も点検してください。この例ではH1を「売上額」にすると参照文字列が一致します。

ただし、H1が複数の集計で共有されているなら、その変更はF3以外にも影響します。H1を使う式を確認し、選択肢から列を切り替える用途を残すのか、特定列の集計へ固定するのかを決めます。旧名の一括置換で説明文や別の表まで変えず、参照元を特定してから直しましょう。

4. 右へのコピーと横フィルで、列名が変わったかを見る

構造化参照は、コピー・貼り付けとフィルハンドルでの展開で挙動が違います。テーブル名を含む列参照の式をコピーすると、その構造化参照は保たれます。右または左へ通常のフィルをすると、列の指定が隣の列へ変わります。どちらも見た目は「式を横へ複製する」操作なので、結果が変わったセルの数式バーで区別します。

先ほどのF2は=SUM(売上表[売上額])です。売上額の右隣に原価列がある状態で、空いているG2へ展開した結果を比べます。次の二つは同時に行わず、コピーしたブックで一方を確認して元に戻してから、もう一方を試してください。

G2にも売上合計を置きたいなら、原価へ変わった式をそのまま採用しません。元のF2をコピー・貼り付けし、G2が売上額を参照していることを確認します。反対に、G2が原価合計の欄なら1200で正しい結果です。「横へ展開したから誤り」ではなく、集計欄の見出しと参照列が合っているかで判断します。

この比較は、表の外にあるテーブル名付きの列参照を対象にしています。同じ式に通常のセル番地が含まれていれば、その部分はコピー先に応じて動くことがあります。構造化参照が保たれていても、式のほかの部分まで固定されるわけではありません。

5. 一部の行だけ違う場合は、列全体へ上書きする前に調べる

横展開した集計ではなく、テーブル内部の一部の行だけ値がおかしい場合は、そのセルと同じ列の正常なセルを比べます。計算列には、手入力の数値や別の式を入れた例外が残ることがあります。見出し変更では、その固定値まで数式へ戻りません。

正常な行の式を貼り付ける前に、異なる式が誤操作なのか、返品・値引きなどの理由で使い分けたものなのかを確認します。例外を含む計算列を変更すると、ほかの行へ式が広がることもあるため、変更範囲と元の式を控えてから作業してください。

一行分を計算する箇所では、@の有無も見比べます。合計セル用の列全体の式を、行ごとの式の代わりに入れないようにします。記号の使い分けを確認したい場合は、基礎記事の「列全体とこの行」の例に戻ると判断できます。

6. 別ブックの開閉で変わる症状は、列名変更と切り分ける

同じブック内では直っても、別ブックのテーブルを参照する式だけ#REF!になるなら、参照元を開いて再計算した場合と比べます。Microsoftは、別ブックのExcelテーブルへのリンクでは、#REF!を避けるため参照元ブックを開いておく必要があると案内しています。INDIRECTによる外部参照にも、参照元を開く必要があります。

列名が正しいのに閉じると失敗する場合は、名前を繰り返し置換しても開閉の条件は解消しません。INDIRECTで別ブックを閉じると#REF!になる場合の対処で、参照元を開いて使うか、データを取り込む構成へ変えるかを確認してください。エラーを消すためだけにリンク解除や値貼り付けをすると、以後の更新が止まります。

7. 修正した式を、元データの変更と追加行で確かめる

エラー表示が消えたら、修正したコピーで数値の動きを確かめます。今回の例ではF2と修正後のF3が同じ売上額列を合計するため、元データを一つ変えれば両方が同じだけ変わるはずです。数式を値に置き換えてしまったセルも、この確認で見つけられます。

追加行の結果だけ合わなければ、その行がテーブル範囲へ含まれているかを確認します。計算列があるブックでは、新しい行にも意図した式が入ったかを見ます。ここまで確認したら、試験で追加したテーブル行を取り除き、元の2件の合計へ戻ったことを確かめてください。

最後に、修正前後の式と、列名・コピー方法・文字列のどこを直したかを残します。IFERRORで#REF!を0に隠すだけでは、売上が0の明細と壊れた参照を区別できません。報告に使う前に、列名が合うこと、集計対象が合うこと、元の値に追従することを確認して保存します。

📊

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

🏆

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