【EXCEL】セルの参照 シートやブックを自由に替える方法

【EXCEL】セルの参照 シートやブックを自由に替える方法

【EXCEL】セルの参照 シートやブックを自由に替える方法 EXCEL
【EXCEL】セルの参照 シートやブックを自由に替える方法

本ページはプロモーションが含まれています

仕事でEXCELを使っていると
「毎年毎月同じ書式のブックやシートを使っている」
って事多いですよね。
そのデータを参照する時に便利な方法を紹介します。

セルの参照を楽にする方法 【INDIRECT関数】

サンプルシートの紹介

複数のブックやシートのセルを簡単に参照する方法、
答えは「INDIRECT関数を使用する」です。

順を追ってみていきましょう。
まずはサンプルデータから。

参照先となるデータです。
✓年ごとにブックが分かれている(2021~2023)
✓月ごとにシートが分かれている(1~4

参照先のデータの一部を参考に。
ファイル名:「2021」
シート名:「1」~「4」
です。

【EXCEL】セルの参照 シートやブックを自由に替える方法 サンプルシート参照先
サンプルシート参照先


これを「まとめ」のブックで参照します。

【EXCEL】セルの参照 シートやブックを自由に替える方法 サンプルシート参照元
サンプルシート参照元

Cドライブに「EXCEL」というフォルダを作成。
そこにすべてのブックを保存しました。

INDIRECT関数の基礎

まとめのシートを紹介します。

【EXCEL】セルの参照 シートやブックを自由に替える方法 INDIRECT関数
INDIRECT関数

C4のセルに入力したのは
=INDIRECT(“‘C:\EXCEL\[“&$C$2&”.xlsx]”&C$3&”‘!C2″)

INDIRECT関数は指定したセルの内容を返す関数です。

簡単な例です。
=INDIRECT(C3)
の場合はC3のセルに入力されている値を返します。

これだと
=C3
と同じですよね。

INDIRECT関数のいいところは
文字を結合して「文字列」で参照先を指定できるところです。

セルの参照先を指定する「記号の意味を知ろう」

「セルの参照先のブックやシートを自由に変更する」
が目的です。
だからこんなややこしい表記になってるんです。

=INDIRECT(“‘C:\EXCEL\[“&$C$2&”.xlsx]”&C$3&”‘!C2″)
記号がいっぱい出てきましたね。
それを説明します。

下の表に書いた記号は2つ1組で使います。
2つの記号ではさんだ部分が「意味」の欄と認識されます。

記号読み方意味
ダブルクォーテーション文字列を表す
シングルクォーテーションブック名、シート名を表す
[ , ]角カッコ , 角カッコ閉じブック名を表す
記号の意味

他にも記号があります。
こちらは1個だけの単独で使います。

記号読み方意味
&アンド文字列との結合
$ドル絶対参照
!エクスクラメーションマークシート名と参照セルの区切り
記号の意味

ブック名、シート名、参照先セルの指定

わかりにくいですね。

ダブルクォーテーションで説明します。
‘C:\EXCEL\[&$C$2&.xlsx]&C$3&‘!C2
赤、黄、青それぞれが1セットです。
つまり下の3つを文字列として扱ってくださいって事。
‘C:\EXCEL\[
.xlsx]
‘!C2

ブック名とシート名の指定の解説です。
C:\EXCEL\[“&$C$2&”.xlsx]”&C$3&”!C2″
シングルクォーテーションで
ブック名、シート名を表してます。
ブック名とシート名は自由に変更したいので
ブック名はC2のセルで指定(内容は2021)、
シート名はC3のセルで指定(内容は1)するようにしています。
つまりこのINDIRECT関数はこうなってます。

“‘C:\EXCEL\[“&$C$2&”.xlsx]”&C$3&”‘!C2

ブックの保存先C:\EXCEL
ブック名2021.xlsx
シート名1
参照先のセルC2
指定している内容

下のセルにもコピーする

まとめのシートでC4のセルを
C5~C8にコピーします。

結果はこちら。

【EXCEL】セルの参照 シートやブックを自由に替える方法 全部同じ値
全部同じ値

全部同じ値が入りました。
C8のセルの関数が表示されてます。
参照先セルが「C2」のままコピーされてます。
本当はC6になってほしいのですが。

理由は文字列として扱われているからです。
“‘C:\EXCEL\[“&$C$2&”.xlsx]”&C$3&‘!C2
残念ですが、手入力で修正して下さい。

【EXCEL】セルの参照 シートやブックを自由に替える方法 結果
結果

使いやすくするためにブックとシートを
プルダウンで簡単に選択できるようにしました。

プルダウンの設定方法はこちらをご覧ください。

【EXCEL】セルの参照 シートやブックを自由に替える方法 プルダウンでブックとシートの選択
プルダウンでブックとシートの選択

INDIRECT関数を使う時の注意点

INDIRECT関数で他のブックを参照する場合、
参照先のブックを開いていないと
「#REF」
とエラーが出ます。
参照先のブックを必ず開くようにして下さい。

Excelの魔術師へ! さいしょの一歩

まとめ

【EXCEL】セルの参照 シートやブックを自由に変える方法
を紹介しました。
データを多く扱うスタッフに
「めちゃくちゃ助かりました~♪」
と喜んでもらえたネタです。
ぜひご活用ください。

ほかにもいろいろ書いてます
↓ 下記からご覧ください
サイトマップ
ブックマークして頂けると嬉しいです
よろしくお願いいたします

コメント

タイトルとURLをコピーしました