見出し画像

第1回：ExcelのINDIRECT関数は便利だけど危険？ ― 再計算地獄の正体と軽くする初手

はんちゃんのITカフェnote

こんにちは、はんちゃんです。(^^ゞ

今回は、Excel　INDIRECT関数について語ります。

この記事でできるようになること
・INDIRECT/OFFSETが“重くなる理由”を直感で理解
・テーブルの計算列（＝構造化参照）を体験してコピペ卒業
・INDEX:INDEX / XMATCH / SUMIFSのどれを“いつ”選ぶかがわかる


行を1つ足したら、SUMが途中で止まっていた。
式をコピペしたら参照がズレていた。



INDIRECT関数（インダイレクト）＝文字列からセル参照を作る関数。とても便利だけれど、実は“毎回再計算（volatile）”されるため、シートが重くなる原因になりがちです。

この記事では、なぜ重くなるのかと、今日からできる軽量化の初手をまとめます。


まずは30秒セルフチェック

  • INDIRECT( / OFFSET( を検索して3件以上ヒットする

  • A:A や 1:1048576 の全列/全行参照を多用している

  • 月別シートが横一列にズラッと並び、式で参照先を切り替えている
    → 1つでも当てはまるなら、この先の“初手”だけで体感が変わります。


ミニ体験：テーブルの魔法（やさしめ解説版）

まずは準備（サンプル表）
先頭行に列名を入れて、以下のような表を作ります。

商品    数量    単価
りんご  2      120
みかん  3      100

Step 1｜テーブル化する
表全体を選択 → Ctrl + T。
「先頭行をテーブルとして使用する」にチェック → OK。
（これで“構造化リスト”として認識され、自動拡張や計算列が使えるようになります）

※Excel for Mac は ⌘ + T でテーブル化できます（うまくいかない場合は［挿入］→［テーブル］）。

Step 2｜“計算列”を作る
テーブルの右端の見出しセルをクリックし、列名に 金額 と入力（新しい列を追加）。

Step 3｜式を入れる（構造化参照）
金額列の一番上のセルに、次の式を入力します。

```
=[@数量]*[@単価]
```

  • [@数量] … “この行の『数量』列”

  • [@単価] … “この行の『単価』列”
    Enterで列全体に自動適用されます（= 計算列）。A1参照のコピペは不要に。

Step 4｜“勝手に伸びる”を体験
最下行に新しい明細を1行追加してみてください。
→ 数式も合計も自動で追随。参照ズレが起こりにくくなります。

末尾のセルで Tab キーを押すと、新しい行が自動で追加されます。
手で行挿入しなくてOK。

📝 よくある戸惑い
・「=[@…] が出ない」→ テーブルの中で式を入れているか確認（帯状のデザインになっていればOK）。
・列名が違う場合は自分の列名に読み替えてOK（例：=[@Qty]*[@UnitPrice]）。
・列名にスペースがあると =[@[受注 数]] のように @[] で表示されます（正しい挙動です）。


ミニ辞書（保存推奨）

小ワザ：テーブル名が Table1 のままだと式が読みにくいので、
［テーブルデザイン］→テーブル名を「売上表」などに変更すると、
例：SUM(売上表[金額]) のように読める式になります。

  • [@列名] … この行の「列名」

  • テーブル名[列名] … テーブル全体の「列名」

  • テーブル名[#データ] … 見出しを除くデータ範囲

  • テーブル名[#すべて] … 見出し＋データ全体

例：SUM(売上表[金額]) は「売上表テーブルの金額列を全部合計」。

用語：構造化参照 = Structured References（英語UIでの表記）

なぜ重い？（volatileの正体）

Excelは通常、変化したセルに関連する式だけを再計算します。
一方 INDIRECT や OFFSET、TODAY、RAND などはvolatile関数。ブックの再計算が走るたびに必ず評価されます。

1000行×複数列×複数シートで多用すると、他の軽い式まで巻き添えになり、操作のたびに“くるくる待ち”が発生します。

初手：置き換えの鉄板（まず1つだけ）

① 列が可変 → INDEX + XMATCH（or XLOOKUP）

ヘッダー名で列を選ぶ典型形。可読性◎／非volatile／速い。

=INDEX(tbl[[#All],[売上]:[利益]],
       MATCH([@商品], tbl[商品], 0),
       XMATCH($F$1, tbl[[#Headers],[売上]:[利益]])
)

※ F1 に「売上／数量／利益」などの見出し名

対応バージョン：Microsoft 365 / Excel 2021 以降（XMATCH/TAKE対応）
※2019以前の方は下の「旧バージョン式」を使ってください。

旧バージョン式（XMATCHの代わりにMATCH）

=INDEX(tbl[[#All],[売上]:[利益]],
       MATCH([@商品], tbl[商品], 0),
       MATCH($F$1, tbl[[#Headers],[売上]:[利益]], 0)
)


② 末尾N件 → INDEX:INDEX（Microsoft 365なら TAKE）

=SUM( INDEX(tbl[金額], ROWS(tbl[金額])-$G$1+1)
   :  INDEX(tbl[金額], ROWS(tbl[金額])) )

365なら：

=SUM(TAKE(tbl[金額], -$G$1))

③ 期間が可変 → 範囲は固定、条件で切る（SUMIFS）

=SUMIFS(tbl[金額],
        tbl[日付], ">="&EOMONTH(TODAY(),-1)+1,
        tbl[日付], "<="&EOMONTH(TODAY(),0))

範囲を動かさず条件だけ可変にするのが軽快です。

④ 可変シート名問題 → Power Queryで縦持ち統合

“月別シートを増やして式で参照切替”は非効率。
データの取得 → ブック → 追加（Append）で1表に集約し、以後は SUMIFS やピボットで集計。設計ごと重さの根を断てます。

さらに軽くする小ワザ

  • 同じ参照を式中で何度も使うなら LET で一度だけ評価

=LET(rng, FILTER(tbl[金額], tbl[部門]=$H$1), SUM(rng))

  • 全列/全行参照は最小限に。表はテーブル化＋構造化参照へ

  • やむを得ず INDIRECT を使う場合はヘルパー1セルだけに限定し、下の式は非volatileで受ける

今日の課題（1セルでOK）

あなたのファイルで INDIRECT( を1箇所だけ INDEX/XMATCH に置換してみてください。

F9（再計算）の待ち時間が短くなれば、勝ち筋は見えています。あとは横に増やさない・使い所を限定する――これだけで“再計算地獄”からかなり解放されます。

まとめ

INDIRECT は悪者ではありません。
ただし大量展開には向かない。
“列はXMATCH、範囲はINDEX、期間はSUMIFS、
シートはPower Query”――この4つを軸にすれば、速くて壊れにくいExcelに近づきます。

次回は 「OFFSETも仲間!? INDEXで作る“軽い動的範囲”」 を掘り下げます。

困りポイント募集：あなたのファイルで「ここが遅い/重い」を教えてください。次回の記事で実例ベースで補足します🙏


読んでくれてありがとうございます！

“再計算地獄”からの脱出をテーマに、実務で使える置換レシピを連載します。わからないところは遠慮なくコメントください。
いただいた質問を元に記事を育てていきます🙌


連載：壊れないExcel（INDIRECT編）
第1回：INDIRECTは便利だけど危険？（本記事）
第2回：OFFSETも仲間!? INDEXで作る“軽い動的範囲”【10/10(金)予定】
第3回：XMATCH＋INDEXで“可変列”を安全実装
第4回：Power Queryで“可変シート問題”を終わらせる
※公開後にリンク差し替えます

いいなと思ったら応援しよう！

はんちゃんのITカフェnote よろしければ応援お願い致します！ いただいたチップはクリエイターとしての活動費に使わせていただきます！

この記事が参加している募集

#

最近の学び

31万件