- Home »
- エクセル関数の技 »
- INDIRECT関数を使って別シートのセルを参照する
スポンサードリンク
(追記) (追記ここまで)
(追記) (追記ここまで)
更新:2025年9月28日;作成:2016年4月6日
INDIRECT関数を使って別シートのセルを参照する topへ
- 別のシートのセルを参照したいときのINDIRECT関数の使い方です。
ここでは下図のように月別のシートに日付ごとのデータが入力されている例を使って説明します。
シート名は 4月、5月、6月としています。
- 各月のシートには1行目に日付データが入力されています。
-
- ここではSheet5で各日付の合計値を取り出そうとしています。
B2セルに日付を入力したらその日付の合計値をB3セルに表示したいということです。
(追記) (追記ここまで)
他のシートを直接参照する数式を作成するときの注意点
- 直接シート名を指定するときはちょっと注意が必要です。
INDIRECT関数では「’」(シングルクォーテーションマーク)は不要ですが、注意点として書いておきます。
「2016年5月3日」の合計値を表示するには シート名が「5月」のシートの「5月3日」の列の12行目を参照すればよいので表引きの関数HLOOKUPが利用できることに気付きます。
=HLOOKUP(B2, と入力して、シート「5月」のB1:H12セルを選択すると、
=HLOOKUP(B2,'5月'!B1:H12 と入力されます。
シート名が「’」(シングルクォーテーションマーク)で囲まれています。
- シート名にスペース(半角または全角、あるいは両方)が入っている場合、
シート名が数字で始まっている場合、
シート名をシングルクォーテーション「 ' 」で囲わないと、シート名がスペースで分断されてしまい、シート名の参照エラー( #REF! )となります。
↓
数式は =HLOOKUP(B2,'5月'!B1:H12,12,FALSE) とします。
ほかのシートを参照する数式をINDIRECT関数を使って作成する
- 日付が「2025年4月5日」となったら数式は =HLOOKUP(B2,'4月'!B1:H12,12,FALSE) と修正する必要があります。
- 参照するシートが変わるたびに数式を修正するのは面倒ですので、どうにかしたい・・・・
入力される日付の月とシート名の数字が同じですので、MONTH(B)&"月" とすると参照するシートを指定できることに気付きます。
数式内では INDIRECT関数で INDIRECT(MONTH(B2)&"月!B1:H12") と指定することができます。
=HLOOKUP(B2,INDIRECT(MONTH(B2)&"月!B1:H12"),12,FALSE)
となりました。
- XLOOKUP関数で数式を作成すると、
=XLOOKUP(B2,INDIRECT(MONTH(B2)&"月!B1:H1"),
INDIRECT(MONTH(B2)&"月!B12:H12"),"-")
となります。
XLOOKUP関数はExcel2021以降で使用できます。
- INDIRECTとは離れてしまいますが・・・
実は、データの位置が3つのシートとも同じなので、3-D参照が可能なことがわかります。
3つのシートを横に結合して、XLOOKUP関数で取り出すことも可能だと気付きます。
=XLOOKUP(B2,
TAKE(HSTACK('4月:6月'!B1:H12),1),
TAKE(HSTACK('4月:6月'!B1:H12),-1),"-")
とすることも可能です。
TAKE関数、HSTACK関数はExcel for Microsoft365, Excel2024以降のバージョンで使用できます。
ハイフンを含むシート名をINDIRECT関数で使用するには?
- 下図のようにシート名が「2025-7」とハイフンが含まれているときは?
日付に「2025年7月5日」と入力されているときに「2025-7」とするには TEXT関数を使うことが考えられます。
TEXT(B2,"yyyy-m") とすればよいと思われます。
- ところが、そのまま利用して =HLOOKUP(B2,INDIRECT(TEXT(B2,"yyyy-m")&"!B1:H12"),12,FALSE) とすると・・・ #REF!
エラーとなってしまいます。
- シート名をシングルクォーテーションマーク で囲む必要があります。
TEXT関数で前後に ’ (シングルクォーテーションマーク)を付けるようにします。
数式を =HLOOKUP(B2,INDIRECT(TEXT(B2,"'yyyy-m'")&"!B1:H12"),12,FALSE) とします。
スポンサードリンク
(追記) (追記ここまで)
(追記) (追記ここまで)
Home »
エクセル関数の技 »
INDIRECT関数を使って別シートのセルを参照する
PageViewCounter
Counter
Since2006/2/27