Excelの構造化参照が横にずれる理由と2つの対処法

LWP | Excelの構造化参照が横にずれる理由と2つの対処法

Excelの構造化参照が横にずれる理由と2つの対処法

図解1|列名を書いたのに、横へずれた?

売上データの一行は売上明細、集計表の一セルは支店と商品の組合せの合計。東京の商品Bは200のはずが横フィル後は0となり、売上金額の列参照が支店へ変わっている

図1|数式が参照するテーブル列と、集計表の支店・商品を分けて見ます。

解説1|集計する列と、集計の条件がある

ストーリー

「東京の商品Bは200のはずですが、0になっています」。フナさんが見ているのは、支店を縦、商品を横に並べた集計表です。コイ丸先輩は、左上の式をフィルハンドルで右へ広げたところでした。

左上の結果は合っています。右隣の式を見ると、売上金額を参照していたはずの箇所が、支店へ変わっています。

入力例

元のテーブル名を「売上データ」とします。一行は1件の売上明細で、列の並びは次のとおりです。金額は説明用の数値です。

売上金額

支店

商品

備考

100

東京

商品A

x

200

東京

商品B

x

50

東京

商品A

x

300

大阪

商品A

x

400

大阪

商品B

x

50

大阪

商品B

x

集計表との関係

J23:J24に東京・大阪、K22:L22に商品A・商品Bを置き、K23:L24へ合計を求めます。K23は東京×商品Aなので100+50=150、L23は東京×商品Bなので200です。

「売上データ[売上金額]」は、テーブル名と列名で参照先を示す構造化参照です。列名が書かれていても、横へフィルするときに維持されるとは限りません。

図解2|コピーと横フィルは、同じではない

K23からL23へ通常のコピーと貼り付けをすると売上金額列を維持するが、フィルハンドルで右へ広げると支店列へ変わる。通常セル参照のK$22はどちらもL$22になる

図2|見た目はどちらも数式を広げる操作ですが、構造化参照の扱いが違います。

解説2|式を配置した操作を確かめる

開始セルの数式

K23へ、支店と商品を条件にしたSUMIFSを入力します。合計範囲と二つの条件範囲は、同じテーブルの列です。

01

=SUMIFS(

02

    売上データ[売上金額],

03

    売上データ[支店], $J23,

04

    売上データ[商品], K$22

05

)

売上金額を合計し、支店がJ23、商品がK22と一致する明細だけを対象にします。どの行でも、この3列の対応を維持する必要があります。

コピーとフィルの違い

K23をCtrl+Cでコピーし、L23へ貼り付けると、構造化参照の列名は維持されます。一方、通常のフィルハンドルで右へ広げると、完全修飾された構造化参照の列指定子が列の並びに沿って変わります。

今回の列順では、売上金額→支店、支店→商品、商品→備考となります。合計する列も条件に使う列も変わるため、商品見出しだけが変わったつもりでも、同じ集計にはなりません。

読むポイント

「コピーしたときは変わらなかったよ」。コイ丸先輩の記憶も、この操作なら合っています。SUMIFSの引数だけでなく、式をコピーしたのか、フィルしたのかを確かめます。

この違いはMicrosoftの構造化参照の説明に記載されています。上下へのフィルや修飾キー付きの操作には別の規則があり、ここでは通常の横フィルを扱います。

図解3|方法1 同じ列から同じ列までを指定する

売上データの売上金額列を、開始列も終了列も売上金額とする一列の範囲で指定する。二重角括弧とコロンで書いた参照は横フィル後も同じ列を維持する

図3|「売上金額列から売上金額列まで」という、一列の範囲にします。

解説3|数式側で、横へ動かさない列を指定する

一列の範囲として書く

構造化参照には、列から列までをコロンで結ぶ範囲指定があります。開始列と終了列を同じ名前にすると、一列だけの範囲になります。

01

売上データ[売上金額]

02

売上データ[[売上金額]:[売上金額]]

上が通常の一列参照、下が今回使う一列の範囲指定です。下の記法では、外側の角括弧が範囲全体を囲み、その内側で二つの列名をコロンで結んでいます。

どこを変えるか

「売上金額列から、売上金額列までですね」。フナさんが指したのは、繰り返して書いた二つの列名です。参照するデータが二列に増えるわけではありません。

SUMIFSでは、合計範囲だけでなく、支店と商品の条件範囲も維持したい列です。三つとも同じ形へ直します。構造化参照の列名へ、通常のセル参照のように$を付ける方法ではありません。

結果

この範囲指定で右へフィルすると、売上金額・支店・商品の列を維持できます。普段どおりフィルハンドルで表を広げるなら、数式の側にこの指定を持たせます。

列範囲の構文はMicrosoftの構造化参照の説明を参照できます。

図解4|固定する列と、動かす条件を分ける

テーブル側の三列は同一列範囲指定で維持し、集計表側では$J23の行を下へ、K$22の列を右へ動かす。右下L24の条件は$J24とL$22になる

図4|テーブルの列参照と、集計表のセル参照を、それぞれの書き方で調整します。

解説4|二次元の集計表を、複合参照で広げる

横フィルに対応した数式

テーブル側の三列を固定し、集計表側は支店と商品の位置に応じて動く式にします。K23へ次の式を入力します。

01

=SUMIFS(

02

    売上データ[[売上金額]:[売上金額]],

03

    売上データ[[支店]:[支店]], $J23,

04

    売上データ[[商品]:[商品]], K$22

05

)

関数の役割と、条件の動き

SUMIFSの第1引数は合計範囲です。その後に、支店の条件範囲と条件、商品の条件範囲と条件が続きます。三つのテーブル列は維持し、条件セルだけを必要な方向へ動かします。

参照

固定する部分

動かす部分

$J23

支店名があるJ列

下へ広げると行23→24

K$22

商品名がある22行

右へ広げると列K→L

「全部を固定したら、同じ集計になるね」。コイ丸先輩の問いに、フナさんは表の見出しを指しました。支店は下へ、商品は右へ変えたいのです。

結果と読むポイント

K23を右へ広げ、続いて下へ広げると、L24の条件は$J24とL$22になります。大阪×商品Bの明細400と50が対象となり、合計は450です。

通常のセル参照の規則はMicrosoftの相対参照・絶対参照・複合参照の説明にあります。

図解5|方法2 範囲を選び、Ctrl+Enterで入力する

K23:L24を選択し、左上K23をアクティブセルにして通常の構造化参照を使うSUMIFSを入力し、CtrlとEnterで一括確定する。セル参照は位置に応じて調整される

図5|横へドラッグする代わりに、最初から選んだ範囲へまとめて入力します。

解説5|入力範囲が決まっているなら、一括入力する

選択範囲とアクティブセル

もう一つの方法は、横フィルを使わずに数式を配置することです。今回はK23:L24を選び、左上のK23をアクティブセルにします。式の相対的な位置は、このセルを基準にします。

入力する数式

構造化参照は、通常の一列指定のままで使います。

01

=SUMIFS(

02

    売上データ[売上金額],

03

    売上データ[支店], $J23,

04

    売上データ[商品], K$22

05

)

選択したK23:L24を保ったまま式を入力し、Ctrl+Enterで確定します。すでにK23へ式を入れてある場合は、K23をアクティブにして範囲を選び、F2で編集状態にしてからCtrl+Enterで確定する方法もあります。

結果と読むポイント

この操作では、構造化参照の列名を横フィルで連続的に変える処理を使いません。一方、$J23やK$22は、それぞれのセル位置に合わせて調整されます。L24の式では$J24とL$22になり、結果は450です。

「先に全体を選ぶんですね」。コイ丸先輩は、ドラッグする前にマウスを止めました。全セルへ同じ数式文字列を入れる操作と、相対参照を保った一括入力は異なります。

Ctrl+Enterによる一括入力と相対参照の調整は、Microsoftの数式入力のヒントに記載されています。操作はWindowsデスクトップ版Excelを対象とします。

図解6|一括入力しても、参照の性質は変わらない

CtrlとEnterは横フィルをせず数式を配置する操作であり、通常の構造化参照を固定参照へ変換しない。その後に横フィルすれば売上金額列が支店列へ変わる場合がある

図6|一括入力で列名が保たれたことと、参照を固定したことを分けて考えます。

解説6|後からフィルするなら、式の指定を見直す

今回避けた操作

「これで、後から右へ引っ張っても平気かな?」。コイ丸先輩がマウスへ手を伸ばすと、フナさんは列名を示しました。

Ctrl+Enterを使っても、「売上データ[売上金額]」という参照自体の性質は変わりません。今回は横方向のオートフィルを使わなかったので、列名を維持できたのです。

後の編集で起こること

通常の一列指定の式を、後からフィルハンドルで右へ広げれば、構造化参照の列が再び変わることがあります。Ctrl+Enterは、通常参照を絶対参照へ変換する機能ではありません。

方法

何を変えるか

後から横フィルするとき

同じ列を範囲指定

数式の列参照の書き方

指定した列を維持する

Ctrl+Enter

数式を配置する操作

通常の構造化参照は変わり得る

読むポイント

今月の範囲が決まっていて一度だけ入力するのか、来月も担当者が横へ広げるのかで、使いやすい方法は変わります。完成した表だけでなく、その後に誰がどう編集するかも考えて選びます。

文字列で指定した参照を返すINDIRECTを使う方法も考えられますが、今回の横ずれ対策だけなら必須ではありません。まず二つの方法のどちらが合うかを検討できます。

図解7|これから横フィルするかで、方法を選ぶ

今後も横フィルするなら同一列の範囲指定、入力範囲が決まるならCtrlとEnterを選ぶ。完成後は右隣と下と右下の式、支店と商品の条件、総額1100を照合する

図7|方法を選んだら、参照先と集計金額の両方を確かめます。

解説7|式を広げた後の確認まで含めて使う

まとめ

今後もフィルハンドルで横へ広げる表なら、同じ列名を両端に置いた範囲指定が使えます。今回の範囲が決まっているなら、通常の構造化参照を使い、Ctrl+Enterで一括入力する方法も選べます。

「来月も引っ張る表なら、式を固定しておこう」。コイ丸先輩は、完成した表と次の担当者の操作を思い浮かべました。

結果の確認

右隣、下、右下の式を開き、テーブルの三列は維持され、支店・商品の条件だけが意図した方向へ動いているか確かめます。今回の集計結果は次のとおりです。

支店

商品A

商品B

合計

東京

150

200

350

大阪

300

450

750

合計

450

650

1100

元データの売上金額も合計1100です。総額だけでは条件の取り違えを見落とすことがあるため、東京×商品Bなど個別の組合せも照合します。

今回の仕掛け

同じ式を広げるように見えても、コピー、横フィル、一括入力では参照の扱いが異なります。数式と、その式をどの操作で配置したかを一緒に見ると、今回の違いを確かめられます。

出典

Microsoft Supportの構造化参照、複合参照、Ctrl+Enterによる数式入力を2026年10月28日に確認。図の人物と出来事は説明用の創作です。

利用について

Copyright © 2026 LWP 山中 一弘

本資料は、出典を明記いただければ、商用・非商用を問わず、ご自由に複製・改変・再配布していただけます。なお、著作権表示は改変せず、そのまま記載してご利用くださいますようお願いいたします。